Re: search_path when restoring to new db

The standard method is to use a pg_dumpall for the initial copy and whenever globals or the schema changes, and use pg_dump when you just want to get the data from a single database.

Globals and schema should not change very often. In fact, they should be fixed except between software revisions. If they aren't, you might be doing something wrong.

What you can do is pg_dumpall --schema-only or pg_dumpall --globals-only (I'm not sure if one of those is a subset of the other) and then use pg_dump --data-only for the databases you want data for. I'm not sure offhand which options you'll need to use to be sure of getting what you want. Double check the docs and be sure to test it a few times.

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of SunWuKung
Sent: Tuesday, December 12, 2006 3:19 AM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] search_path when restoring to new db

Ok, I understand this difference now.
Knowing that, what is the standard way to copy a single database to
another server?

- can I use pg_dumpall to dump a single db? - or
- I have to use pg_dump and there is a procedure to ensure that old and
new dbs are the same, like

1. create new db
2. check or create user
3. run pg_restore
4. do alter db

and is there a way to automate this? If there is could you point me
there?

If there isn't:
Do you think that automating the complete backup and restore of a
single database would be database specific or it could work with
different databases? Don't you think that this is something that would
be generally useful?

Thank you for the help.
Balázs

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster