On Fri, Apr 10, 2009 at 4:34 AM, Kevin Bailey <kbailey@xxxxxxxxxxxxxxxxxxx> wrote: > We're trying to get a script to backup and restore a whole database server. > > So far we're looking at something like > ... > which is obviously a rough-cut - and the main problem we have is that a > client has set up a DB where the owner names do not match up with the DB > names. You should consider adding a few different flags. Here's what I do to dump/restore a specific database. pg_dump -h [oldhost] -p 5432 -C -Fc [database] | pg_restore -h [newhost] -p 5432 -C -d template1 The -C flag in pg_dump adds a command to create the database in the dumpfile (and connect to it immediately afterward). -Fc dumps the output in a custom, compressed format, but that output isn't readable by psql. Then, the -C flag in pg_restore says to read and execute that create command we dumped, but we have to give it a different database to initially connect to. Of course, depending on how you have your network and pg_hba.conf set up, you might not be able to use a direct pipe. If you can't, then you'll have to add the steps of dumping the output to a file and transferring said file before restoring. No big deal, I'm sure you can interpolate for what you need. > Just to make thigs more interesting, we have to do this for a version 8.1 > server and an older 7.4 server which has blobs in it. If I remember correctly, pg_dumpall only allows text format dumps, and blobs need to be dumped in non-text formats. This might have changed since 7.4, but I couldn't find any source either way. Hope this helps. Peter -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
