Thread: Using pg_dump and pg_restore

Using pg_dump and pg_restore

From
Mark
Date:
Hi

I am looking for some help here.

I have 2 servers, one as master and one as a "warm spare".  The master
server runs 4 postgres DBs.

Our intention (until we can get full replication and syncing  between
postgres DBs) is to regularly dump all DBs on the master, copy them to
the warm spare and then restore them.

The problem we have now is the restore function is not quite there yet.

I would prefer to use pg_restore as that can "drop" the DB prior to
restoration so that we definitely have the most up to date info and no
chance of corrupt or mixed data.

So far we have tried normal switches on the end of pg_dump, including
-Fc, -c, C, with corresponding switches on the pg_restore command.  When
the restore actually executes, it does not fully drop the DB prior to
restoration so any test tables added to the DB after a dump are still
there after a restore.

So, I am looking for a quick and easy solution from someone that has an
up and running dump and restore process.  What  I need is a set of
switches after the pg_dump and pg_restore command that will cleanly drop
the DB prior to restore.

Needless to say I have already checked out the postgres docs and tried
all the different switches that can be used so I know what they "should"
do, but for some reason, they do not work for me.

Server details:
Dell PE 2650
RHEL 3 U7
rhdb postgres server

 rpm -qa | grep post
rh-postgresql-libs-7.3.10-2
rh-postgresql-server-7.3.10-2
rh-postgresql-7.3.10-2

Any help will be gratefully accepted.

Ta in advance

Mark
--

This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the sender. Any
offers or quotation of service are subject to formal specification.
Errors and omissions excepted.  Please note that any views or opinions
presented in this email are solely those of the author and do not
necessarily represent those of Lumison, nplusone or lightershade ltd.
Finally, the recipient should check this email and any attachments for the
presence of viruses.  Lumison, nplusone and lightershade ltd accepts no
liability for any damage caused by any virus transmitted by this email.

--
--
Virus scanned by Lumison.

Re: Using pg_dump and pg_restore

From
Tom Lane
Date:
Mark <mark@lumison.net> writes:
> So far we have tried normal switches on the end of pg_dump, including
> -Fc, -c, C, with corresponding switches on the pg_restore command.  When
> the restore actually executes, it does not fully drop the DB prior to
> restoration so any test tables added to the DB after a dump are still
> there after a restore.

I think you want to drop the database explicitly.  Something like

    pg_dump -Fc mydb >mydb.dump
    ...
    dropdb mydb
    pg_restore -C -d template1 mydb.dump

            regards, tom lane