[postgis-users] problem with restoring database postgis 2.0

[Stephen Woodbridge]

This is why I ALWAYS build my databases and create a data schema than 
set the search path to "data, public". Then all my functions and data go 
into "data" and I can pg_dump -N public to leave behind all the postgis 
stuff.

createdb -T template_postgis mydb
psql mydb -c "create schema data; alter database mydb set search_path 
to data, public"
...
pg_dump ... -f mydb.dump -N public mydb

Never have to worry about that again.

-Steve

On 3/22/2012 8:02 PM, John Callahan wrote:
> Well, that makes sense.  I was using the --data-only option because I
> did not want to bring over the functions (since the functions already
> exist in the new database through the postgis creation script.)
>
> Is there a way to dump/restore only the data tables (including create
> statements, indexes,  sequences, privileges) and not the functions or
> function comments?  I ran into a problem recently while upgrading when I
> had too many similar functions and postgis couldn't determine a unique
> function at times.
>
> - John
>
> ***********************************************
> John Callahan, Research Scientist
> Delaware Geological Survey, University of Delaware
> URL: http://www.dgs.udel.edu
> *************************************************
>
>
>
> On Thu, Mar 22, 2012 at 7:17 PM, Bborie Park <bkpark at ucdavis.edu
> <mailto:bkpark at ucdavis.edu>> wrote:
>
>     The pg_dump call is called with "--data-only" so you're not getting
>     any table creation information.  And since the new DB doesn't have
>     any tables, restoring the data will result in an error.
>
>     -bborie
>
>
>     On 03/22/2012 04:14 PM, John Callahan wrote:
>
>         I having a problem with something that is usually straight
>         forward. I am
>         moving a database from a Windows 2003 Server box to Windows 2008
>         Server.
>           Both are x64 but running 32bit Postgres 9.0.7.  The
>         new/destination
>         database runs the latest postgis 2.0 binaries (from March 19 or
>         so) and the
>         source/older database runs postgis 2.0 libraries from a few
>         months ago.
>
>         On the new db, I installed postgres 9.0.7, then postgis 2.0
>         latest build,
>         which created my database.  Of course, the database does not
>         have any
>         tables at this point (just the functions, a few views.)
>
>         I run pg_dump on the source db as so:
>         pg_dump.exe --host localhost --port 5432 --username "username"
>         --role
>         "myrole"  --format plain --data-only --verbose --file
>         "C:\temp\webdata_20120315.sql" "mydb"
>
>         When I try to run the resulting sql on the new server, I get the
>         following
>         error:
>         ERROR: relation<table_name>  does not exist
>
>         If I use --inserts options in pg_dump, I still get the "relation
>         does not
>         exist error"   If I use pgAdmin to backup and restore (using the tar
>         format), the error is the same.
>
>         What am I missing?   Thanks.
>
>         - John
>
>         ****************************** *****************
>         John Callahan, Research Scientist
>         Delaware Geological Survey, University of Delaware
>         URL: http://www.dgs.udel.edu
>         ****************************** *******************
>
>
>
>
>         ______________________________ _________________
>         postgis-users mailing list
>         postgis-users at postgis. refractions.net
>         <mailto:postgis-users at postgis.refractions.net>
>         http://postgis.refractions. net/mailman/listinfo/postgis- users
>         <http://postgis.refractions.net/mailman/listinfo/postgis-users>
>
>
>     --
>     Bborie Park
>     Programmer
>     Center for Vectorborne Diseases
>     UC Davis
>     530-752-8380 <tel:530-752-8380>
>     bkpark at ucdavis.edu <mailto:bkpark at ucdavis.edu>
>     ______________________________ _________________
>     postgis-users mailing list
>     postgis-users at postgis. refractions.net
>     <mailto:postgis-users at postgis.refractions.net>
>     http://postgis.refractions. net/mailman/listinfo/postgis- users
>     <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