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: [email protected]
>> [mailto:[email protected]] 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: [email protected]
>>> [mailto:[email protected]] 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
>>> [email protected]
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> [email protected]
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
>
> _______________________________________________
> postgis-users mailing list
> [email protected]
> http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
