[postgis-users] Problems with backing up and restoring Tiger Geocoder database
René Fournier
m5 at renefournier.com
Thu Nov 10 09:05:26 PST 2011
Hi guys,
Well, in the end the restore is working, although I ran into some non-fatal problems during the process. I would just like to understand what I'm not doing quite right. Anyway, here's what I did:
1. Dump a working ~88GB database from old server. (Built by the Tiger Geocoder scripts.)
pg_dump -U postgres -F c -b -f /Volumes/Extra/gc4-20111109.backup gc4
2. On new server, create database.
CREATE DATABASE gc4;
(Note, I didn't create it from the template_postgis since I figured that spatial data is already part of the dump file -- yes? Actually, maybe I didn't need to or shouldn't have created the database at all? Is it not necessary when restoring?)
3. Restore database to new server -- which is running the same versions of OS (10.7.2), Postgres (9.0.5) and PostGIS (1.5.3)
Wed Nov 09 15:06:44 -- Baby-Irmo ~ :: pg_restore -U postgres -C -d gc4 /Volumes/Extra/gc4-20111109.backup
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 11448; 1262 921794 DATABASE gc4 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: database "gc4" already exists
Command was: CREATE DATABASE gc4 WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_CA.UTF-8' LC_CTYPE = 'en_CA.UTF-8';
[ ...10 hours elapse…]
WARNING: errors ignored on restore: 1
Thu Nov 10 01:12:20 -- Baby-Irmo ~ ::
Hmm, it complained about gc4 already existing… Anyway, after the restore, the new server showed 88GB in gc4. But when I ran \d+, I didn't see any tables except the ones I added (not part of Tiger Geocoder).
gc4=# \l+
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges | Size | Tablespace | Description
------------------+----------+----------+-------------+-------------+-----------------------+---------+------------+---------------------------
gc4 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | | 88 GB | pg_default |
postgres | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | | 5369 kB | pg_default |
template0 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres +| 5273 kB | pg_default |
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres +| 5273 kB | pg_default | default template database
| | | | | postgres=CTc/postgres | | |
template_postgis | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | | 9241 kB | pg_default |
(5 rows)
Then I ran the test query:
SELECT g.rating,
ST_X(geomout) As lon,
ST_Y(geomout) As lat, (addy).*
FROM geocode('1731 New Hampshire Avenue Northwest, Washington, DC 20010', 1) As g;
ERROR: function geocode(unknown, integer) does not exist at character 79
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
STATEMENT:
Hmm.. I wondered if maybe the tables and functions are there, just not visible. So I ran:
ALTER DATABASE gc4 SET search_path=public, tiger;
Later, when I checked again, I saw the tables in the \d+ and then tried running the same query:
gc4=# SELECT g.rating,
ST_X(geomout) As lon,
ST_Y(geomout) As lat, (addy).*
FROM geocode('1731 New Hampshire Avenue Northwest, Washington, DC 20010', 1) As g;
rating | lon | lat | address | predirabbrev | streetname | streettypeabbrev | postdirabbrev | internal | location | stateabbrev | zip | parsed
--------+-------------------+------------------+---------+--------------+---------------+------------------+---------------+----------+------------+-------------+-------+--------
1 | -77.0398083968318 | 38.9133648716727 | 1731 | | New Hampshire | Ave | NW | | Washington | DC | 20009 | t
(1 row)
And it worked. Now… my question is, was my main problem the fact that I hadn't added the ALTER DATABASE command after restore? I didn't think I would need to, since the database on the old server had already been altered for search paths when built -- I figured that information was included in the dump.
Thanks again for entertaining these basic questions. It's just that even when I can get things working, I'm far more comfortable when I understand what's going on.
Regards,
Rene
On 2011-11-09, at 3:20 PM, Paragon Corporation wrote:
> Another tip. for restore you want to use
> the --jobs option
>
> e.g.
>
> --jobs=3
>
> to have 3 parallel loads happening.
>
> (forgot that in our cheatsheet)
>
> It will do restore of several tables at once thus running faster if you have
> the processors to support.
>
> Another reason to not bother with sql backups since you can't take advantage
> of parallel restore with sql backups.
>
>
>
>> -----Original Message-----
>> From: postgis-users-bounces at postgis.refractions.net
>> [mailto:postgis-users-bounces at postgis.refractions.net] On
>> Behalf Of Paragon Corporation
>> Sent: Wednesday, November 09, 2011 5:07 PM
>> To: 'PostGIS Users Discussion'
>> Subject: Re: [postgis-users] Problems with backing up and
>> restoringTiger Geocoder database
>>
>> Rene,
>>
>> My guess is you probably did an SQL backup instead of a
>> compressed or tar backup.
>>
>> I've had this issue before when backing up with pgAdmin and
>> the reason is because it defaults to sql backup I think. I
>> forget what the switch is you can check our backup /restore
>> cheatsheet for reference.
>>
>> http://www.postgresonline.com/downloads/special_feature/postgr
>> esql90_pg_dump
>> restore_cheatsheet.pdf
>>
>> (you want -F c -b
>>
>> pg_restore can only be used to restore tar or compressed backups.
>>
>> We restore the tiger data all the time to various servers so
>> no issue there.
>>
>>
>> Leo and Regina
>> http://www.postgis.us
>>
>>
>>
>>> -----Original Message-----
>>> From: postgis-users-bounces at postgis.refractions.net
>>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
>>> René Fournier
>>> Sent: Wednesday, November 09, 2011 2:10 PM
>>> To: PostGIS Users Discussion
>>> Subject: [postgis-users] Problems with backing up and
>> restoring Tiger
>>> Geocoder database
>>>
>>> Since building the Tiger Geocoder database from the source
>> data is so
>>> time-consuming, I'm trying to capture the result (the ~90GB
>> spatially
>>> aware database) so that I can simply restore, in case I need to
>>> rebuild the server. Should be simple, but every time I pg_dump and
>>> attempt to pg_restore, I get
>>>
>>> pg_restore: [archiver] input file does not appear to be a valid
>>> archive
>>>
>>> So, just wondering what I'm doing wrong? Or, put another
>> way, what are
>>> the steps to dump the given database and restore it to
>> another server
>>> running Postgresql90 and PostGIS 1.5.3? Thanks!
>>>
>>> …Rene
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111110/85725741/attachment.html>
More information about the postgis-users
mailing list