[postgis-users] problem with restoring database postgis 2.0

[John Callahan]

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
*************************************************

[Bborie Park]

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

> _______________________________________________
> postgis-users mailing list
> postgi...@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

--
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
_______________________________________________
postgis-users mailing list
postgi...@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[John Callahan]

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
*************************************************

postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

--
Bborie Park
Programmer
Center for Vectorborne Diseases
UC Davis
530-752-8380
bkp...@ucdavis.edu
_______________________________________________
postgis-users mailing list

postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[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

> John Callahan, Research Scientist
> Delaware Geological Survey, University of Delaware
> URL: http://www.dgs.udel.edu
> ****************************** *******************
>
>
>
>

> ______________________________ _________________
> postgis-users mailing list
> postgis-users@postgis. refractions.net
> <mailto:postgi...@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>
> bkp...@ucdavis.edu <mailto:bkp...@ucdavis.edu>
> ______________________________ _________________
> postgis-users mailing list
> postgis-users@postgis. refractions.net
> <mailto:postgi...@postgis.refractions.net>
> http://postgis.refractions. net/mailman/listinfo/postgis- users
> <http://postgis.refractions.net/mailman/listinfo/postgis-users>

[John Callahan]

Thanks Steve.  Great idea.  Unfortunately, I don't think it helps me now (unless I want to reload 70 or so datasets.)   I'll keep it in mind for other databases. 

I'm still learning postgis but how do you keep the functions in the data schema if the database is created (with functions) before that schema?  Or should all the functions stay in the public schema and datasets in the "data" schema?  Thanks for any help.

- John

       <mailto:postgis-users@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>
   bkp...@ucdavis.edu <mailto:bkp...@ucdavis.edu>
   ______________________________ _________________
   postgis-users mailing list
   postgis-users@postgis. refractions.net

   <mailto:postgis-users@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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users