how to restore only one db from a pg_dumpall type dump

From: Edan Gavish <Edan(dot)Gavish(at)ogilvy(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: how to restore only one db from a pg_dumpall type dump
Date: 2005-05-06 19:54:27
Message-ID: OF06BE4D15.5A2620F7-ON85256FF9.006A91D2-85256FF9.006D5AEC@ogilvy.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

This is a multi-part message in MIME format...

------------=_1115409263-27750-10
Content-type: text/plain; charset=US-ASCII
Content-Disposition: inline

My first challange with restoring from dumps across different versions...

in pgsql 7.4.2:
I use pg_dumpall > $BACK_DIR/pgdumpall_test$TSTAMP.dmp.
I than copy the dump file over to another machine with pgsql 7.2.2.
I want to pg_restore just one database (i.e. wxsdb) without having to
manually clear data and tables, also ignore the original owner of tables.

Is it possible with opne pg_restore (pgsql 7.2.2)?
What are the 'default' values for pg_dumpall on pgsql 7.4.2 that i may have
missed?
What pg_restore flags to use on pg_restore ?

I tried this: pg_restore -O -i -c -d wxsdb --format=c (for some reason
format=p does not exist)...
...getting constrints violations
...or users doesnt exist
...I tried to manually delete all tables, but than there are many 'types'
and 'sequences' that wont go away...

Any hints and tips will b eappreciated.

eg

Edan Gavish
Oracle DBA
WWIT
Ogilvy & Mather Worldwide, New York
Office: +1 212 237 5759
Fax: +1 212 237 4060
E-mail: edan(dot)gavish(at)ogilvy(dot)com

Privileged/Confidential Information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply email. Please advise immediately if you or your employer does not consent to email or messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of the Ogilvy Group shall be understood as neither given nor endorsed by it.

------------=_1115409263-27750-10--

Browse pgsql-admin by date

  From Date Subject
Next Message Enrico Weigelt 2005-05-06 20:05:28 Re: A nightmare
Previous Message Scott Marlowe 2005-05-06 18:38:03 Re: memory allocation ; postgresql-8.0