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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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/s...g-dumpall.html

--Ray O'Donnell

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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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/s...g-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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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/s...g-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 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly