pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
problems 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