Thread: Backing up multiple databases

Backing up multiple databases

From
Jacob Atzen
Date:
Hello list,

I have a setup with multiple databases running on one Postgres. Say,
db1, db2 and db3.

I have two problems with this setup, the first is how to restore one of
the databases and leave the other two intact. If for example somebody
accidentally deletes data from db1 which needs to be restored I would
need to restore db1 but not db2 and db3. As far as I can tell there is
no easy way to do this with the current tools. I could make a script to
clean out the unneeded parts of the dump but before I do that I want to
make sure, there's no easier way to do this.

The second problem is a matter of database ownership. Apparently
pg_dumpall will dump the owners of the database along with the data.
This is causing trouble when I try to restore the dump on a server where
the owner doesn't exist. At the moment I have the server running on a
machine where the default owner is "pgsql" but on my local machine the
name is "postgres". How do I get around this? Should I just abandon
pg_dumpall and use pg_dump instead or is there some other way?

--
Thanks,
Jacob Atzen

Re: Backing up multiple databases

From
"Raymond O'Donnell"
Date:
On 17 Jun 2005 at 13:52, Jacob Atzen wrote:

> The second problem is a matter of database ownership. Apparently
> pg_dumpall will dump the owners of the database along with the data.
> This is causing trouble when I try to restore the dump on a server where

There is an option to pg_dumpall, -O, which makes it dump stuff
without owners. See the following:

  http://www.postgresql.org/docs/8.0/static/app-pg-dumpall.html

--Ray O'Donnell

-------------------------------------------------------------
Raymond O'Donnell     http://www.galwaycathedral.org/recitals
rod@iol.ie                          Galway Cathedral Recitals
-------------------------------------------------------------


Re: Backing up multiple databases

From
Jacob Atzen
Date:
On Fri, Jun 17, 2005 at 01:13:16PM +0100, Raymond O'Donnell wrote:
> On 17 Jun 2005 at 13:52, Jacob Atzen wrote:

> > The second problem is a matter of database ownership. Apparently
> > pg_dumpall will dump the owners of the database along with the data.
> > This is causing trouble when I try to restore the dump on a server
> > where

> There is an option to pg_dumpall, -O, which makes it dump stuff
> without owners. See the following:

>   http://www.postgresql.org/docs/8.0/static/app-pg-dumpall.html

Ah yes, I should have told you that the server is running 7.4.5, sorry.
I will consider upgrading if there's no alternative way.

--
Thanks,
Jacob Atzen

Re: Backing up multiple databases

From
Richard Huxton
Date:
Jacob Atzen wrote:
> Hello list,
>
> I have a setup with multiple databases running on one Postgres. Say,
> db1, db2 and db3.
>
> I have two problems with this setup, the first is how to restore one of
> the databases and leave the other two intact. If for example somebody
> accidentally deletes data from db1 which needs to be restored I would
> need to restore db1 but not db2 and db3. As far as I can tell there is
> no easy way to do this with the current tools.

Eh?  pg_dump -U my_user my_db > dump_file


  I could make a script to
> clean out the unneeded parts of the dump but before I do that I want to
> make sure, there's no easier way to do this.

Course there is - you can restore a single table, or a single schema, or
even (with the --list option) a selected list of objects.

> The second problem is a matter of database ownership. Apparently
> pg_dumpall will dump the owners of the database along with the data.
> This is causing trouble when I try to restore the dump on a server where
> the owner doesn't exist. At the moment I have the server running on a
> machine where the default owner is "pgsql" but on my local machine the
> name is "postgres". How do I get around this? Should I just abandon
> pg_dumpall and use pg_dump instead or is there some other way?

What's the problem with creating a superuser called "postgres" on both
machines? Or you could choose not to dump (or restore) ownership
information (--no-owner). The section of the manuals you want is
"PostgreSQL Client Applications" - it covers all the options.

I'd use pg_dump anyway - unless you have hundreds of databases, it makes
it easier to keep by backups separate.

--
   Richard Huxton
   Archonet Ltd

Re: Backing up multiple databases

From
Jacob Atzen
Date:
On Fri, Jun 17, 2005 at 01:21:13PM +0100, Richard Huxton wrote:
> What's the problem with creating a superuser called "postgres" on both
> machines? Or you could choose not to dump (or restore) ownership
> information (--no-owner). The section of the manuals you want is
> "PostgreSQL Client Applications" - it covers all the options.

> I'd use pg_dump anyway - unless you have hundreds of databases, it makes
> it easier to keep by backups separate.

I will do that then. Thanks.

--
Cheers,
Jacob Atzen

Re: Backing up multiple databases

From
Christopher Browne
Date:
Martha Stewart called it a Good Thing when jaa@interflow.dk (Jacob Atzen) wrote:
> On Fri, Jun 17, 2005 at 01:13:16PM +0100, Raymond O'Donnell wrote:
>> On 17 Jun 2005 at 13:52, Jacob Atzen wrote:
>
>> > The second problem is a matter of database ownership. Apparently
>> > pg_dumpall will dump the owners of the database along with the data.
>> > This is causing trouble when I try to restore the dump on a server
>> > where
>
>> There is an option to pg_dumpall, -O, which makes it dump stuff
>> without owners. See the following:
>
>>   http://www.postgresql.org/docs/8.0/static/app-pg-dumpall.html
>
> Ah yes, I should have told you that the server is running 7.4.5, sorry.
> I will consider upgrading if there's no alternative way.

That option did exist in 7.4, so you're not being steered terribly
wrongly, although you may want to check the docs from v7.4 in case
there are any other differences relevant to you.
--
(format nil "~S@~S" "cbbrowne" "cbbrowne.com")
http://linuxfinances.info/info/slony.html
TTY Message from The-XGP at MIT-AI:
The-XGP@AI 02/59/69 02:59:69
Your XGP output is startling.

Re: Backing up multiple databases

From
Jacob Atzen
Date:
On Fri, Jun 17, 2005 at 08:43:21AM -0400, Christopher Browne wrote:
> >> There is an option to pg_dumpall, -O, which makes it dump stuff
> >> without owners. See the following:

> > Ah yes, I should have told you that the server is running 7.4.5,
> > sorry.  I will consider upgrading if there's no alternative way.

> That option did exist in 7.4, so you're not being steered terribly
> wrongly, although you may want to check the docs from v7.4 in case
> there are any other differences relevant to you.

It doesn't exist in pg_dumpall on 7.4.5:

% pg_dumpall -O
pg_dumpall: invalid option -- O

But I'll just use pg_dump where it does exist.

--
Thanks,
Jacob Atzen

Re: Backing up multiple databases

From
Tom Lane
Date:
Jacob Atzen <jaa@interflow.dk> writes:
> On Fri, Jun 17, 2005 at 01:21:13PM +0100, Richard Huxton wrote:
>> I'd use pg_dump anyway - unless you have hundreds of databases, it makes
>> it easier to keep by backups separate.

> I will do that then. Thanks.

Note that you probably also want to run "pg_dumpall -g" as part of that
set of backups, else you have no restorable record of your users and
groups.

            regards, tom lane

Re: Backing up multiple databases

From
Gavin Love
Date:
>>I'd use pg_dump anyway - unless you have hundreds of databases, it makes
>>it easier to keep by backups separate.
>
>
> I will do that then. Thanks.
>

Here is the script I use for my daily backups nothing special but it
works well. Just run it as a user with admin privs on the database. It
will pull the list of all your databases except templates and dump them out.

#!/bin/bash

export PG_BIN=/usr/local/pgsql/bin
export OUT_DIR=/db_backups/psql/
export TODAY=$(date "+%Y/%m/%d")
export BACKUP_DBS=`/usr/local/pgsql/bin/psql template1 -t -c "SELECT
datname FROM pg_database WHERE datname NOT LIKE 'template_' ORDER BY
datname"`

mkdir -p $OUT_DIR/$TODAY

echo "DataBase backup started at $(date)";

for i in $BACKUP_DBS
do
         echo -n "Backing up $i...."
         $PG_BIN/pg_dump -o -C $i > $OUT_DIR/$TODAY/$i
         echo -n "Compressing...."
         bzip2 -9 -f $OUT_DIR/$TODAY/$i
         echo "Done"
done
         echo -n "Backing up globals...."
         $PG_BIN/pg_dumpall -g > $OUT_DIR/$TODAY/global.sql
         echo "Done"

echo "DataBase ended at $(date)";

Gavin

Re: Backing up multiple databases

From
Berend Tober
Date:
Gavin Love wrote:

>
> Here is the script I use for my daily backups nothing special but it
> works well. Just run it as a user with admin privs on the database. It
> will pull the list of all your databases except templates and dump
> them out.
>

That is pretty neat! Here is Gavin's script slighty modified with some
extra features useful to me and maybe to you, too:

pg_backup.sh:

#!/bin/bash

# Subject:Re: [GENERAL] Backing up multiple databases
# From:   Gavin Love <gavin@aardvarkmedia.co.uk>
# Date:   Fri, 17 Jun 2005 15:52:34 +0100
# To:     Jacob Atzen <jaa@interflow.dk>
# CC:     pgsql-general@postgresql.org
#
# Modified by Berend Tober  2005-06-17 to:
#     a) include tcp port as command line parameter.
#     b) include syntax help.
#     c) include Postgresql version information in global.sql output file.
#     d) append ".sql" file name suffix to dump output file.
#     e) output to current directory.

SYNTAX="Usage: `basename $0` port"

if [ $# -ne 1 ]
then
    echo ${SYNTAX}
    exit 1
fi

PG_BIN=/usr/bin
OUT_DIR=.
PG_PORT=${1}
TODAY=$(date "+%Y/%m/%d")
BACKUP_DBS=`/usr/bin/psql -p ${PG_PORT} template1 -t -c "SELECT datname
FROM pg_database WHERE datname NOT LIKE 'template_' ORDER BY datname;"`
VERSION_DBS=`/usr/bin/psql -p ${PG_PORT} template1 -t -c "SELECT '--
'||version();"`

mkdir -p $OUT_DIR/$TODAY

echo "Data base backup started at $(date)";

for i in $BACKUP_DBS
do
     echo -n "Backing up $i...."
     $PG_BIN/pg_dump -p ${PG_PORT} -o -C $i > $OUT_DIR/$TODAY/$i.sql
     echo -n "Compressing...."
     bzip2 -9 -f $OUT_DIR/$TODAY/$i.sql
     echo "Done"
done

echo -n "Backing up globals...."
echo $VERSION_DBS > $OUT_DIR/$TODAY/global.sql
$PG_BIN/pg_dumpall -p ${PG_PORT} -g >> $OUT_DIR/$TODAY/global.sql
echo "Done"

echo "Data base ended at $(date)";


Re: Backing up multiple databases

From
Jacob Atzen
Date:
On Fri, Jun 17, 2005 at 01:45:34PM -0400, Berend Tober wrote:
> Gavin Love wrote:

> >Here is the script I use for my daily backups nothing special but it
> >works well. Just run it as a user with admin privs on the database.
> >It will pull the list of all your databases except templates and dump
> >them out.

> That is pretty neat! Here is Gavin's script slighty modified with some
> extra features useful to me and maybe to you, too:

Thanks a lot. This is really nice.

--
Cheers,
Jacob Atzen