X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 42D2132AFCD for ; Thu, 7 Oct 2004 05:53:23 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 48496-07 for ; Thu, 7 Oct 2004 04:52:42 +0000 (GMT) Received: from lakemtao08.cox.net (lakemtao08.cox.net [68.1.17.113]) by svr1.postgresql.org (Postfix) with ESMTP id 94BA532AD9B for ; Thu, 7 Oct 2004 05:50:56 +0100 (BST) Received: from [192.168.123.110] (really [68.225.208.56]) by lakermmtao11.cox.net (InterMail vM.6.01.03.04 201-2131-111-106-20040729) with ESMTP id <20041007035219.OULH5156.lakermmtao11.cox.net@[192.168.123.110]> for ; Wed, 6 Oct 2004 23:52:19 -0400 Mime-Version: 1.0 X-Sender: usachurch@mail.absolutemotion.com Message-Id: In-Reply-To: <1790.1097106631@sss.pgh.pa.us> References: <1790.1097106631@sss.pgh.pa.us> Date: Wed, 6 Oct 2004 20:52:17 -0700 To: pgsql-admin@postgresql.org From: admin@usachurch.com Subject: problems with pg_dump and pg_restore Content-Type: text/plain; charset="us-ascii" ; format="flowed" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=NO_REAL_NAME X-Spam-Level: X-Archive-Number: 200410/110 X-Sequence-Number: 15103 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