Hello list,
I am having problems getting pg_dump and pg_restore to work the way they are supposed to work - or at least the way I imagine they should work!
I have a primary DB server, and a backup DB server. The point is to use pg_dump
every 5 minutes to have the current postgeSQL db available on the backup DB box.
btw, these are both running version 7.4.2.
Here's the command I am issuing on the primary DB server:
pg_dump -U admin staging -Fc -b -f /home/postgres/staging-dump
And, it seems to work - creating a 20MB archive that has some readable text in it near the top, and a bunch of random characters to the end. According to the docs, this file should be able to used in many different ways by the pg_restore command. So, on the backup DB server, I am trying to completely rebuild the staging database to the exact state of the primary server with:
pg_restore -U admin -c /home/postgres/staging-dump
Now, although tons of data is now streaming to my monitor, when it is done, none
of the new data is in the backup's staging db.
So, I tried deleting "staging" on the backup server with: dropdb -U admin staging
and then create it again: createdb -U dba staging -O admin
And when I run the above pg_restore command, none of the data or schema is in the newly created staging db.
So, I tried doing this variation:
pg_restore -U admin -C -d staging /home/postgres/staging-dump
but I get this error:
pg_restore: [archiver (db)] could not execute query: ERROR: database "staging" already exists
and if i don't recreate the staging db with the createdb command, I get this error:
pg_restore: [archiver (db)] connection to database "staging" failed: FATAL: database "staging" does not exist
I'm sure that I'm close to getting this to work - any advise?
Thanks, Devin
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
