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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of SunWuKung Sent: Tuesday, December 12, 2006 3:19 AM To: pgsql-general@postgresql.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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
