Thread: Backing up multiple databases
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
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 -------------------------------------------------------------
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
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
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
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.
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
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
>>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
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)";
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