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