From: | Peter Koczan <pjkoczan(at)gmail(dot)com> |
---|---|
To: | Kevin Bailey <kbailey(at)freewayprojects(dot)com> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: backing and restoring whole database server - how does this script look |
Date: | 2009-04-10 19:31:49 |
Message-ID: | 4544e0330904101231w733e0af6we803e4684be36b87@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tena Sakai | 2009-04-10 19:39:47 | connectivity problem |
Previous Message | P Kapat | 2009-04-10 19:01:49 | Re: backing and restoring whole database server - how does this script look |