pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedproblems with pg_dump and pg_restore
3+ messages / 2 participants
[nested] [flat]
* problems with pg_dump and pg_restore
@ 2004-10-07 03:52 admin@usachurch.com
2004-10-07 15:17 ` Re: problems with pg_dump and pg_restore Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: admin@usachurch.com @ 2004-10-07 03:52 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: problems with pg_dump and pg_restore
2004-10-07 03:52 problems with pg_dump and pg_restore admin@usachurch.com
@ 2004-10-07 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-07 18:44 ` Re: problems with pg_dump and pg_restore admin@usachurch.com
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2004-10-07 15:17 UTC (permalink / raw)
To: admin@usachurch.com; +Cc: pgsql-admin
admin@usachurch.com writes:
> 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.
The default behavior of pg_restore is to emit SQL commands onto stdout,
which of course is not going to do a lot for your backup DB. What you
probably want is to include a "-d database" switch to tell it to connect
to a database and send the commands there.
> So, I tried doing this variation:
> pg_restore -U admin -C -d staging /home/postgres/staging-dump
> pg_restore: [archiver (db)] could not execute query: ERROR: database
> "staging" already exists
-C tells it to create (and then connect to) the target DB, rather than
connect to an existing DB and restore into that. So generally you want
to say "-d template1" (or some other existing DB) when you use -C.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: problems with pg_dump and pg_restore
2004-10-07 03:52 problems with pg_dump and pg_restore admin@usachurch.com
2004-10-07 15:17 ` Re: problems with pg_dump and pg_restore Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-10-07 18:44 ` admin@usachurch.com
0 siblings, 0 replies; 3+ messages in thread
From: admin@usachurch.com @ 2004-10-07 18:44 UTC (permalink / raw)
To: pgsql-admin
> > pg_restore -U admin -C -d staging /home/postgres/staging-dump
>> pg_restore: [archiver (db)] could not execute query: ERROR: database
>> "staging" already exists
>
>-C tells it to create (and then connect to) the target DB, rather than
>connect to an existing DB and restore into that. So generally you want
>to say "-d template1" (or some other existing DB) when you use -C.
Tom,
But if I first "dropdb -U admin staging", and then issue:
pg_restore -U admin -C -d staging /home/postgres/staging-dump
I get the error:
pg_restore: [archiver (db)] connection to database "staging" failed:
FATAL: database "staging" does not exist
Yet the docs state that the above command should Create the database
named by the "-d staging"
part of the command. I'm confused as to why that doesn't work. I
also am not sure what the
template1 and template0 are there for.
Regards,
Devin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-10-07 18:44 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-07 03:52 problems with pg_dump and pg_restore admin@usachurch.com
2004-10-07 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-07 18:44 ` admin@usachurch.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox