problems with pg_dump and pg_restore

From: admin(at)usachurch(dot)com
To: pgsql-admin(at)postgresql(dot)org
Subject: problems with pg_dump and pg_restore
Date: 2004-10-07 03:52:17
Message-ID: p06110400bd8a69770b18@[192.168.123.110]
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Michael Kleiser 2004-10-07 08:38:09 Re: Multiple WALs for PITR ?
Previous Message Goulet, Dick 2004-10-07 01:56:51 Re: Multiple WALs for PITR ?