Обсуждение: Backing up postgresql databases

Hello,

How do people normally backup up postgresql databases? The obviously safe
solution is to shut down the database during backup, but is Postgresql
storage such that it's safe to backup a running DB? I.e., is the database
guaranteed to be in a consistent state after restoration?

I'm talking a standard backup procedure now; say a tar -czvf. So that
assuming certain procedures during storage, it should be possible to
guarantee this (although it wouldn't be easy). The questions is,
does Postgresql do that? I'm sceptical :)

Thanks!

--
/ Peter Schuller, InfiDyne Technologies HB

PGP userID: 0x5584BD98 or 'Peter Schuller <peter.schuller@infidyne.com>'
Key retrival: Send an E-Mail to getpgpkey@scode.infidyne.com
E-Mail: peter.schuller@infidyne.com Web: http://scode.infidyne.com

Peter,

From what I understand (and I'm still fairly new to Postgres) you won't get
a perfect copy while Postmaster is running, but there is a way to backup the
entire database while it's running: pg_dumpall.

pg_dumpall by default is in the /usr/bin directory, and they usage is
simple: /usr/bin/pg_dumpall > filename - this will create a backup that
restores using SQL statements.

regards,
-John

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Peter Schuller
Sent: Saturday, March 17, 2001 9:39 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Backing up postgresql databases


Hello,

How do people normally backup up postgresql databases? The obviously safe
solution is to shut down the database during backup, but is Postgresql
storage such that it's safe to backup a running DB? I.e., is the database
guaranteed to be in a consistent state after restoration?

I'm talking a standard backup procedure now; say a tar -czvf. So that
assuming certain procedures during storage, it should be possible to
guarantee this (although it wouldn't be easy). The questions is,
does Postgresql do that? I'm sceptical :)

Thanks!

--
/ Peter Schuller, InfiDyne Technologies HB

PGP userID: 0x5584BD98 or 'Peter Schuller <peter.schuller@infidyne.com>'
Key retrival: Send an E-Mail to getpgpkey@scode.infidyne.com
E-Mail: peter.schuller@infidyne.com Web: http://scode.infidyne.com


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

Hello, I have written a backup script that will vacuum, analyze and backup every postgresql database.

(1) Modify logfile and backup_dir variables to suite your needs.
(2) I have a trust relationship so I am never prompted for a password on connection.
(3) Add an entry to crontab to perform the backups nightly or whenever you wish.
(4) Have fun.

# Crontab starts here.
00 01 * * * /path/to/script/backup > /dev/null 2>&1
#--------------------------------------------------

# Backup script starts here.

#!/bin/bash
# Location of the backup logfile.
logfile="/path/to/logfile.log"
# Location to place backups.
backup_dir="/directory/to/place/backups"
touch $logfile
timeslot=`date +%H-%M`
databases=`psql -h localhost -U postgres -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'}`

for i in $databases; do
        timeinfo=`date '+%T %x'`
        echo "Backup and Vacuum complete at $timeinfo for time slot $timeslot on database: $i " >> $logfile
        /server/pgsql/bin/vacuumdb -z -h localhost -U postgres $i >/dev/null 2>&1
        /server/pgsql/bin/pg_dump $i -h 127.0.0.1 | gzip > "$backup_dir/postgresql-$i-$timeslot-database.gz"
done
#-------------------------------------------------

Hello,

> From what I understand (and I'm still fairly new to Postgres) you won't get
> a perfect copy while Postmaster is running, but there is a way to backup the
> entire database while it's running: pg_dumpall.

Ah, perfekt! And thanks to Grant for that bash script :)

I do have a question though. Contrary to Grant, I can't use a trust policy,
even on the local machine. As a result, I need to use password
authentication. However, pg_dumpall seems to generally screw up.

Wheather or not I do -u has no effect; in either case I get prompted to the
password (but not the username). And in either case, I get a buch of random
"Password:" type outputs at first (as if it's trying to authenticate
unsuccessfully several times) intermixed with the other output. When I
finally get the password prompt, I enter it and I seem to get logged in (as
postgres). Then I get another error (invalid script or sql query of some
sort it looks like).

Below is some example output; it looks really messy in general with a bunch
of failed authentications. Note that I didn't get to enter the password
until the very last Username:Password:

   defiant:~$ /usr/lib/postgresql/bin/pg_dumpall -u
   Password: psql: Password authentication failed for user 'postgres'
   \connect template1
   select datdba into table tmp_pg_shadow       from pg_database where datname
   = 'template1';
   delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba;
   drop table tmp_pg_shadow;
   copy pg_shadow from stdin;
   Password:
   psql: Password authentication failed for user 'postgres'
   \.
   delete from pg_group;
   copy pg_group from stdin;
   Password:
   psql: Password authentication failed for user 'postgres'
   \.
   Password: Password: psql: Password authentication failed for user 'postgres'
   \connect template1
   create database "postgres";
   \connect postgres
   Username: Password:

   Connection to database 'postgres' failed.
   ERROR: Missing '=' after '31' in conninfo

   pg_dump failed on postgres, exiting

Is there something obvious I'm doing wrong?

Thanks!

--
/ Peter Schuller, InfiDyne Technologies HB

PGP userID: 0x5584BD98 or 'Peter Schuller <peter.schuller@infidyne.com>'
Key retrival: Send an E-Mail to getpgpkey@scode.infidyne.com
E-Mail: peter.schuller@infidyne.com Web: http://scode.infidyne.com

Peter Schuller <peter.schuller@infidyne.com> wrote:
>Contrary to Grant, I can't use a trust policy, even on the local machine.
>As a result, I need to use password authentication. However, pg_dumpall
>seems to generally screw up.

>Is there something obvious I'm doing wrong?

No. pg_dumpall wasn't written with password authentication in mind. You can
find a patched pg_dumpall that supports password authentication at
    http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=87035&repeatmerged=yes

HTH,
Ray
--
Do Microsoft's TCO calculations include TC of downtime?

"Peter Schuller" <peter.schuller@infidyne.com> writes:
> I do have a question though. Contrary to Grant, I can't use a trust policy,
> even on the local machine. As a result, I need to use password
> authentication. However, pg_dumpall seems to generally screw up.

pg_dumpall is very unfriendly to password auth method :-(.  Even if it
worked better, would you really want to keep a password stored in a cron
script?

Consider using IDENT authentication on local TCP connections and having
the dumper connect to 127.0.0.1 instead of via Unix socket.  IDENT is
not real secure for remote connections but I don't see why you shouldn't
trust it for local.

Kerberos auth might be another answer, but I don't know enough about it
to comment intelligently.

            regards, tom lane

Peter,

    I agree with Tom that it isn't the best solution to have to store a
user/pass in a cron script, but sometimes things have to be done for a
greater purpose.  I still have to fiddle with how local ident would work
in my own situation, but the current solution I have is the following.
    Have your shell script do

export PGUSER=username
export PGPASSWORD=password

before you run pg_dumpall in the same script.  The user/pass would most
likely have to be a superuser to have access to all databases (this is
also not guaranteed depending on your pg_hba.conf).  Make the script
read/execute by root but not by anyone else and it will help a tiny bit
with security.

It's ugly but it works.  This was taken from some previous suggestions on
the mailing list to get around this same problem for other versions of
pg_dumpall.  I'm still hoping to find something better.

Hope that helps.

Tim Frank

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 19/03/01, 5:06:16 AM, peter.schuller@infidyne.com ("Peter Schuller")
wrote regarding Re: [ADMIN] Backing up postgresql databases:


> Hello,

> > From what I understand (and I'm still fairly new to Postgres) you won't
get
> > a perfect copy while Postmaster is running, but there is a way to backup
the
> > entire database while it's running: pg_dumpall.

> Ah, perfekt! And thanks to Grant for that bash script :)

> I do have a question though. Contrary to Grant, I can't use a trust
policy,
> even on the local machine. As a result, I need to use password
> authentication. However, pg_dumpall seems to generally screw up.

> Wheather or not I do -u has no effect; in either case I get prompted to
the
> password (but not the username). And in either case, I get a buch of
random
> "Password:" type outputs at first (as if it's trying to authenticate
> unsuccessfully several times) intermixed with the other output. When I
> finally get the password prompt, I enter it and I seem to get logged in
(as
> postgres). Then I get another error (invalid script or sql query of some
> sort it looks like).

> Below is some example output; it looks really messy in general with a
bunch
> of failed authentications. Note that I didn't get to enter the password
> until the very last Username:Password:

>    defiant:~$ /usr/lib/postgresql/bin/pg_dumpall -u
>    Password: psql: Password authentication failed for user 'postgres'
>    \connect template1
>    select datdba into table tmp_pg_shadow       from pg_database where
datname
>    = 'template1';
>    delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba;
>    drop table tmp_pg_shadow;
>    copy pg_shadow from stdin;
>    Password:
>    psql: Password authentication failed for user 'postgres'
>    \.
>    delete from pg_group;
>    copy pg_group from stdin;
>    Password:
>    psql: Password authentication failed for user 'postgres'
>    \.
>    Password: Password: psql: Password authentication failed for user
'postgres'
>    \connect template1
>    create database "postgres";
>    \connect postgres
>    Username: Password:

>    Connection to database 'postgres' failed.
>    ERROR: Missing '=' after '31' in conninfo

>    pg_dump failed on postgres, exiting

> Is there something obvious I'm doing wrong?

> Thanks!

> --
> / Peter Schuller, InfiDyne Technologies HB

> PGP userID: 0x5584BD98 or 'Peter Schuller <peter.schuller@infidyne.com>'
> Key retrival: Send an E-Mail to getpgpkey@scode.infidyne.com
> E-Mail: peter.schuller@infidyne.com Web: http://scode.infidyne.com


> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Dixit Tim Frank <tfrank@registrar.uoguelph.ca> (le Tue, 20 Mar 2001 00:14:11 GMT) :

»     Have your shell script do
»
» export PGUSER=username
» export PGPASSWORD=password
»
» before you run pg_dumpall in the same script.  The user/pass would most
» likely have to be a superuser to have access to all databases (this is
» also not guaranteed depending on your pg_hba.conf).  Make the script
» read/execute by root but not by anyone else and it will help a tiny bit
» with security.

Using something like "ps -e" shows the environment variables so it is
as unsecure as giving the password on the commande line.

        Thierry

Is there any reason why programs like this could not be given a simple properties file which contains the username and password.  This file could then be passed on the command line, but nobody (other than, say, root, or postgres) would have access to it at all.  I've seen a number of systems use this type of solution, and although it appears superficially useless (am I opening myself to be shot down or what ;-), the security of the file system creates (as far as I can see) reasonable safety.

Just my €25...

MikeA

>> -----Original Message-----
>> From: Thierry Besancon [mailto:Thierry.Besancon@prism.uvsq.fr]
>> Sent: 20 March 2001 08:34
>> To: Tim Frank
>> Cc: pgsql-admin@postgresql.org
>> Subject: Re: [ADMIN] Backing up postgresql databases
>>
>>
>> Dixit Tim Frank <tfrank@registrar.uoguelph.ca> (le Tue, 20
>> Mar 2001 00:14:11 GMT) :
>>
>> »    Have your shell script do
>> »
>> » export PGUSER=username
>> » export PGPASSWORD=password
>> »
>> » before you run pg_dumpall in the same script.  The
>> user/pass would most
>> » likely have to be a superuser to have access to all
>> databases (this is
>> » also not guaranteed depending on your pg_hba.conf).  Make
>> the script
>> » read/execute by root but not by anyone else and it will
>> help a tiny bit
>> » with security.
>>
>> Using something like "ps -e" shows the environment variables so it is
>> as unsecure as giving the password on the commande line.
>>
>>         Thierry
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>> http://www.postgresql.org/search.mpl
>>

_________________________________________________________________________
This e-mail and any attachments are confidential and may also be privileged and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an
intended or authorised recipient of this e-mail or have received it in error, please delete
it immediately and notify the sender by e-mail. In such a case, reading, reproducing,
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free
from computer viruses or other defects. The opinions expressed in this e-mail and any
attachments may be those of the author and are not necessarily those of Intec Telecom
Systems PLC.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
__________________________________________________________________________


Thierry,

    I think you meant to say "ps -e e" (at least that is what it is on my
RedHat Linux servers).  I also realise the security issue with this.
However, if you really HAVE to use password authentication and you really
HAVE to dump your data then you are already painted into a corner so to
speak.  I apologies for not mentioning the security issue in my post.
    The reason the "export" is needed for pg_dumpall is because it is not
very password friendly and calls a combination of psql and pg_dump to do
it's job.  I recall when looking at the pg_dumpall script that it doesn't
actually pass a username/password to psql, or if you do specify the -U
then you get an "unknown option" error when it tries to run pg_dump.

Tim Frank

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 20/03/01, 3:33:52 AM, Thierry Besancon <Thierry.Besancon@prism.uvsq.fr>
wrote regarding Re: [ADMIN] Backing up postgresql databases:


> Dixit Tim Frank <tfrank@registrar.uoguelph.ca> (le Tue, 20 Mar 2001
00:14:11 GMT) :

> »     Have your shell script do
> »
> » export PGUSER=username
> » export PGPASSWORD=password
> »
> » before you run pg_dumpall in the same script.  The user/pass would most
> » likely have to be a superuser to have access to all databases (this is
> » also not guaranteed depending on your pg_hba.conf).  Make the script
> » read/execute by root but not by anyone else and it will help a tiny bit
> » with security.

> Using something like "ps -e" shows the environment variables so it is
> as unsecure as giving the password on the commande line.

>         Thierry

Mike,
    From a different perspective I toyed around briefly with the idea of
creating a user called "backup" that would merely have SELECT permissions
on all tables in all databases in order to perform a pg_dump or
pg_dumpall.  This works fine for a pg_dump as it does a single database
at a time.  The problem was that I couldn't figure out a way to
automatically set SELECT permissions for the backup user when a new table
was created in the database.  You can't set a default value in the
pg_class table nor create a trigger on insert as it complains about
"can't modify a system catalogue".  Maybe there is some other way to get
around that, and I would be more than happy to hear comments on this.
    The other problem I found was that when using pg_dumpall it dumps all
the database usernames/passwords so the "backup" user would need select
permissions on pg_shadow which contains all of the usernames/passwords.
Well, I kinda quit right there as far as using this restricted "backup"
user for pg_dumpall because if it can select all users/passwords from the
database then storing this combination in a shell script/environment
variable isn't any more secure.  Sure, it takes one more step to get ALL
usernames/passwords, but that doesn't seem to be worth the effort.
    Thinking about it now and seeing a shell script somebody posted a little
while ago to do a vacuum and pg_dump it might not be such a bad idea to
go back to the "backup" user with SELECT only permissions on the tables.
Just not sure how to set the permissions on newly created tables by
default, maybe it just has to be manually.
    I would greatly appreciate comments on this idea and if it is worth
anything.  I've teetered back and forth for some time.
Tim Frank

Original Message dated 20/03/01, 6:48:08 AM
Author: Michael Ansley <Michael.Ansley@intec-telecom-systems.com>
Re: RE: [ADMIN] Backing up postgresql databases:


Is there any reason why programs like this could not be given a simple
properties file which contains the username and password.  This file
could then be passed on the command line, but nobody (other than, say,
root, or postgres) would have access to it at all.  I've seen a number of
systems use this type of solution, and although it appears superficially
useless (am I opening myself to be shot down or what ;-), the security of
the file system creates (as far as I can see) reasonable safety.
Just my ¬25...
MikeA

>> -----Original Message-----
>> From: Thierry Besancon [mailto:Thierry.Besancon@prism.uvsq.fr]
>> Sent: 20 March 2001 08:34
>> To: Tim Frank
>> Cc: pgsql-admin@postgresql.org
>> Subject: Re: [ADMIN] Backing up postgresql databases
>>
>>
>> Dixit Tim Frank <tfrank@registrar.uoguelph.ca> (le Tue, 20
>> Mar 2001 00:14:11 GMT) :
>>
>> »    Have your shell script do
>> »
>> » export PGUSER=username
>> » export PGPASSWORD=password
>> »
>> » before you run pg_dumpall in the same script.  The
>> user/pass would most
>> » likely have to be a superuser to have access to all
>> databases (this is
>> » also not guaranteed depending on your pg_hba.conf).  Make
>> the script
>> » read/execute by root but not by anyone else and it will
>> help a tiny bit
>> » with security.
>>
>> Using something like "ps -e" shows the environment variables so it is
>> as unsecure as giving the password on the commande line.
>>
>>         Thierry
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>> http://www.postgresql.org/search.mpl
>>


_________________________________________________________________________
This e-mail and any attachments are confidential and may also be
privileged and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If
you are not an
intended or authorised recipient of this e-mail or have received it in
error, please delete
it immediately and notify the sender by e-mail. In such a case, reading,
reproducing,
printing or further dissemination of this e-mail is strictly prohibited
and may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an
attachment hereto is free
from computer viruses or other defects. The opinions expressed in this
e-mail and any
attachments may be those of the author and are not necessarily those of
Intec Telecom
Systems PLC.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
________________________________________________________________________
__

Hi All,
I've changed a little bit the script and here it is now:

----------- cut --------------------
#!/bin/bash

set -e
set -b
# Location of the backup logfile.
logfile="/var/log/pg_sql_backup/bkp.log"

# Location to place backups.
backup_dir="/mnt/backup/Databases/pgsql/"
timeslot=`date +%Y-%m-%d-%H-%M-%S`

if [ $# -lt 1 ]; then
        echo "pg_backup help for more detailed help"
        exit 0
fi

if [ "$1" == "help" ]; then
        echo ""
        echo "Usage: pg_backup <hostname> show - shows all databases on pg_sql server"
        echo "Usage: pg_backup <hostname> <database_name> - backups specified database"
        echo "Usage: pg_backup <hostname> - backups all databases on pg_sql server"
        echo "Usage: pg_backup <hostname> db_size - show sizes of all databases on pg_sql server"
        exit 0
fi

if [ "$2" == "show" ]; then
        databases=$(psql -h "$1" -U backup -q -c '\l' template1 | sed -n '4,/\eof/p' | grep -v '(.*rows)' | awk {'print $1'} | grep -v "|" | grep -v template0 |grep -v ":")
        echo "Server $1 contains folloing databases:"
        echo "======================================"
        for i in $databases; do
          echo $i
        done
        echo "======================================"
        exit 0
fi

if [ "$2" == "db_size" ]; then
        databases=$(psql -h "$1" -U backup -q -c '\l' template1 | sed -n '4,/\eof/p' | grep -v '(.*rows)' | awk {'print $1'} | grep -v "|" |grep -v template0 |grep -v ":")
        echo "Server $1 contains folloing databases:"
        echo "======================================"
        for i in $databases; do
          psql -h "$1" -U backup -q -c "SELECT pg_size_pretty(pg_database_size('$i')) as \"$i\";" template1
        done
        echo "======================================"
        exit 0
fi

if [ -n "$2" ]; then
        databases="$2"
else
        databases=$(psql -h "$1" -U backup -q -c '\l' template1 | sed -n '4,/\eof/p' | grep -v '(.*rows)' | awk {'print $1'} | grep -v "|"  |grep -v template0 |grep -v ":")
fi

for i in $databases; do
        timeinfo=$(date '+%T %x')
        echo "[$1] [$timeinfo] Seshon Started" | tee -a "$logfile"
        echo "[$1] [$timeinfo] Vacuum started on database: $i" | tee -a "$logfile"
        vacuumdb -z -h "$1" -U backup "$i" >> "$logfile"
        timeinfo=$(date '+%T %x')
        echo "[$1] [$timeinfo] Vacuum complete on database: $i" | tee -a "$logfile"

        timeinfo=$(date '+%T %x')
        echo "[$1] [$timeinfo] Backup started on database: $i" | tee -a "$logfile"
        pg_dump "$i" -h "$1" -U backup | gzip > "$backup_dir/pgsql-$1-$i-$timeslot-database.gz"
        timeinfo=$(date '+%T %x')
        echo "[$1] [$timeinfo] Backup complete on database: $i" | tee -a "$logfile"
        echo "[$1] [$timeinfo] Seshon Ended" | tee -a "$logfile"

done

echo "All is OK"
----------- cut --------------------
in cron you can put the following script:

--------- cut ----------
#!/bin/bash

echo -e "============== DataBase Backups ==============="
echo -e "===============================================\n"
for i in host1 host2 host3 hostXXX; do
    echo -e "=== Connecting to $i ==="
    echo -e "=== Starting backup of $i ==="
    /etc/everbread/crons/pg_backup.sh $i show
    /etc/everbread/crons/pg_backup.sh $i db_size
    /etc/everbread/crons/pg_backup.sh $i
    echo -e "=== Backup of $i finished ==="
    echo -e "=== Disconnected from $i ====\n"
done

echo -e "==============================================="
echo -e "============= Data Base Backups Done ==========\n"


----------cut ----------
--
Regards,
Dimitar Atanasov