Re: backing and restoring whole database server - how does this script look

On Fri, Apr 10, 2009 at 4:34 AM, Kevin Bailey
<kbailey(at)freewayprojects(dot)com> 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