<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">Hi guys,<div><br></div><div>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:</div><div><br></div><div>1. Dump a working ~88GB database from old server. (Built by the Tiger Geocoder scripts.)</div><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div><br></div><div>pg_dump -U postgres -F c -b -f /Volumes/Extra/gc4-20111109.backup gc4</div><div><br></div></blockquote>2. On new server, create database. <div><br></div><div><span class="Apple-tab-span" style="white-space:pre"> </span>CREATE DATABASE gc4;</div><div><br></div><div><span class="Apple-tab-span" style="white-space:pre"> </span>(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?)</div><div><br>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)<div><br></div></div><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div><div>Wed Nov 09 15:06:44 -- Baby-Irmo ~ :: pg_restore -U postgres -C -d gc4 /Volumes/Extra/gc4-20111109.backup</div></div><div><div>pg_restore: [archiver (db)] Error while PROCESSING TOC:</div></div><div><div>pg_restore: [archiver (db)] Error from TOC entry 11448; 1262 921794 DATABASE gc4 postgres</div></div><div><div>pg_restore: [archiver (db)] could not execute query: ERROR: database "gc4" already exists</div></div><div><div> Command was: CREATE DATABASE gc4 WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_CA.UTF-8' LC_CTYPE = 'en_CA.UTF-8';</div></div><div>[ ...10 hours elapse…]</div></blockquote><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div><div>WARNING: errors ignored on restore: 1</div></div><div><div>Thu Nov 10 01:12:20 -- Baby-Irmo ~ :: </div></div></blockquote><div><div><br></div><div>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). </div></div><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div><div><div><br></div></div></div><div><div><div><div>gc4=# \l+</div></div></div></div><div><div><div><div> List of databases</div></div></div></div><div><div><div><div> Name | Owner | Encoding | Collation | Ctype | Access privileges | Size | Tablespace | Description </div></div></div></div><div><div><div><div>------------------+----------+----------+-------------+-------------+-----------------------+---------+------------+---------------------------</div></div></div></div><div><div><div><div> gc4 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | | 88 GB | pg_default | </div></div></div></div><div><div><div><div> postgres | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | | 5369 kB | pg_default | </div></div></div></div><div><div><div><div> template0 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres +| 5273 kB | pg_default | </div></div></div></div><div><div><div><div> | | | | | postgres=CTc/postgres | | | </div></div></div></div><div><div><div><div> template1 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres +| 5273 kB | pg_default | default template database</div></div></div></div><div><div><div><div> | | | | | postgres=CTc/postgres | | | </div></div></div></div><div><div><div><div> template_postgis | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | | 9241 kB | pg_default | </div></div></div></div><div><div><div><div>(5 rows)</div></div></div></div><div><div><div><div><br></div></div></div></div><div><br></div></blockquote><div><div>Then I ran the test query:</div></div><div><br></div><div><blockquote class="webkit-indent-blockquote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 40px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; "><div><div>SELECT g.rating, <span class="Apple-tab-span" style="white-space: pre; "> </span></div></div><div>ST_X(geomout) As lon, </div><div>ST_Y(geomout) As lat, (addy).* </div><div>FROM geocode('1731 New Hampshire Avenue Northwest, Washington, DC 20010', 1) As g;</div><div><br></div></blockquote></div><blockquote class="webkit-indent-blockquote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 40px; border-top-style: none; border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; "><div><div>ERROR: function geocode(unknown, integer) does not exist at character 79</div></div><div><div>HINT: No function matches the given name and argument types. You might need to add explicit type casts.</div></div><div><div>STATEMENT: <span class="Apple-tab-span" style="white-space: pre; "> </span></div></div></blockquote><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div><div><div><div><br></div></div></div></div></blockquote>Hmm.. I wondered if maybe the tables and functions are there, just not visible. So I ran:<div><div><div><br></div></div></div><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div><div><div>ALTER DATABASE gc4 SET search_path=public, tiger;</div></div></div></blockquote><div><div><div><br></div><div>Later, when I checked again, I saw the tables in the \d+ and then tried running the same query:</div><div><br></div></div></div><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div><div><div><div>gc4=# SELECT g.rating, </div></div></div></div><div><div><div><div>ST_X(geomout) As lon, </div></div></div></div><div><div><div><div>ST_Y(geomout) As lat, (addy).* </div></div></div></div><div><div><div><div>FROM geocode('1731 New Hampshire Avenue Northwest, Washington, DC 20010', 1) As g;</div></div></div></div><div><div><div><div> rating | lon | lat | address | predirabbrev | streetname | streettypeabbrev | postdirabbrev | internal | location | stateabbrev | zip | parsed </div></div></div></div><div><div><div><div>--------+-------------------+------------------+---------+--------------+---------------+------------------+---------------+----------+------------+-------------+-------+--------</div></div></div></div><div><div><div><div> 1 | -77.0398083968318 | 38.9133648716727 | 1731 | | New Hampshire | Ave | NW | | Washington | DC | 20009 | t</div></div></div></div><div><div><div><div>(1 row)</div></div></div></div><div><div><div><div><br></div></div></div></div></blockquote><div><br></div>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.<br><div><div><div><br></div><div>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.</div><div><br></div><div><div>Regards,</div></div><div>Rene</div><div><br></div><div><br><div><div>On 2011-11-09, at 3:20 PM, Paragon Corporation wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><div>Another tip. for restore you want to use<br>the --jobs option<br><br>e.g. <br><br>--jobs=3<br><br>to have 3 parallel loads happening.<br><br>(forgot that in our cheatsheet)<br><br>It will do restore of several tables at once thus running faster if you have<br>the processors to support.<br><br>Another reason to not bother with sql backups since you can't take advantage<br>of parallel restore with sql backups.<br><br><br><br><blockquote type="cite">-----Original Message-----<br></blockquote><blockquote type="cite">From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a> <br></blockquote><blockquote type="cite">[mailto:postgis-users-bounces@postgis.refractions.net] On <br></blockquote><blockquote type="cite">Behalf Of Paragon Corporation<br></blockquote><blockquote type="cite">Sent: Wednesday, November 09, 2011 5:07 PM<br></blockquote><blockquote type="cite">To: 'PostGIS Users Discussion'<br></blockquote><blockquote type="cite">Subject: Re: [postgis-users] Problems with backing up and <br></blockquote><blockquote type="cite">restoringTiger Geocoder database<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">Rene,<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">My guess is you probably did an SQL backup instead of a <br></blockquote><blockquote type="cite">compressed or tar backup.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">I've had this issue before when backing up with pgAdmin and <br></blockquote><blockquote type="cite">the reason is because it defaults to sql backup I think. I <br></blockquote><blockquote type="cite">forget what the switch is you can check our backup /restore <br></blockquote><blockquote type="cite">cheatsheet for reference.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><a href="http://www.postgresonline.com/downloads/special_feature/postgr">http://www.postgresonline.com/downloads/special_feature/postgr</a><br></blockquote><blockquote type="cite">esql90_pg_dump<br></blockquote><blockquote type="cite">restore_cheatsheet.pdf<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">(you want -F c -b<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">pg_restore can only be used to restore tar or compressed backups.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">We restore the tiger data all the time to various servers so <br></blockquote><blockquote type="cite">no issue there.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">Leo and Regina<br></blockquote><blockquote type="cite"><a href="http://www.postgis.us">http://www.postgis.us</a><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><blockquote type="cite">-----Original Message-----<br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite">From: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite">[mailto:postgis-users-bounces@postgis.refractions.net] On Behalf Of <br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite">René Fournier<br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite">Sent: Wednesday, November 09, 2011 2:10 PM<br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite">To: PostGIS Users Discussion<br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite">Subject: [postgis-users] Problems with backing up and <br></blockquote></blockquote><blockquote type="cite">restoring Tiger <br></blockquote><blockquote type="cite"><blockquote type="cite">Geocoder database<br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite">Since building the Tiger Geocoder database from the source <br></blockquote></blockquote><blockquote type="cite">data is so <br></blockquote><blockquote type="cite"><blockquote type="cite">time-consuming, I'm trying to capture the result (the ~90GB <br></blockquote></blockquote><blockquote type="cite">spatially <br></blockquote><blockquote type="cite"><blockquote type="cite">aware database) so that I can simply restore, in case I need to <br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite">rebuild the server. Should be simple, but every time I pg_dump and <br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite">attempt to pg_restore, I get<br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><span class="Apple-tab-span" style="white-space:pre"> </span>pg_restore: [archiver] input file does not appear to be a valid <br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite">archive<br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite">So, just wondering what I'm doing wrong? Or, put another <br></blockquote></blockquote><blockquote type="cite">way, what are <br></blockquote><blockquote type="cite"><blockquote type="cite">the steps to dump the given database and restore it to <br></blockquote></blockquote><blockquote type="cite">another server <br></blockquote><blockquote type="cite"><blockquote type="cite">running Postgresql90 and PostGIS 1.5.3? Thanks!<br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite">…Rene<br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite">_______________________________________________<br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite">postgis-users mailing list<br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></blockquote></blockquote><blockquote type="cite"><blockquote type="cite"><br></blockquote></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">_______________________________________________<br></blockquote><blockquote type="cite">postgis-users mailing list<br></blockquote><blockquote type="cite"><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br></blockquote><blockquote type="cite"><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></blockquote><blockquote type="cite"><br></blockquote><br><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></div></blockquote></div><br></div></div></div></body></html>