Horton

Thread

  1. Backing up multiple databases

    Jacob Atzen <jaa@interflow.dk> — 2005-06-17T11:52:08Z

    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

  2. Re: Backing up multiple databases

    Raymond O'Donnell <rod@iol.ie> — 2005-06-17T12:13:16Z

    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
-------------------------------------------------------------

  3. Re: Backing up multiple databases

    Jacob Atzen <jaa@interflow.dk> — 2005-06-17T12:18:45Z

    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

  4. Re: Backing up multiple databases

    Richard Huxton <dev@archonet.com> — 2005-06-17T12:21:13Z

    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

  5. Re: Backing up multiple databases

    Chris Browne <cbbrowne@acm.org> — 2005-06-17T12:43:21Z

    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.

  6. Re: Backing up multiple databases

    Jacob Atzen <jaa@interflow.dk> — 2005-06-17T12:45:02Z

    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

  7. Re: Backing up multiple databases

    Jacob Atzen <jaa@interflow.dk> — 2005-06-17T13:40:07Z

    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

  8. Re: Backing up multiple databases

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-06-17T13:57:33Z

    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

  9. Re: Backing up multiple databases

    Gavin Love <gavin@aardvarkmedia.co.uk> — 2005-06-17T14:52:34Z

    >>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

  10. Re: Backing up multiple databases

    Berend Tober <btober@seaworthysys.com> — 2005-06-17T17:45:34Z

    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)";

  11. Re: Backing up multiple databases

    Jacob Atzen <jaa@interflow.dk> — 2005-06-20T08:54:52Z

    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