Postgresql backup bash script.

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(dot)schuller(at)infidyne(dot)com>'
Key retrival: Send an E-Mail to getpgpkey(at)scode(dot)infidyne(dot)com
E-Mail: peter(dot)schuller(at)infidyne(dot)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(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of Peter Schuller
Sent: Saturday, March 17, 2001 9:39 PM
To: pgsql-admin(at)postgresql(dot)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(dot)schuller(at)infidyne(dot)com>'
Key retrival: Send an E-Mail to getpgpkey(at)scode(dot)infidyne(dot)com
E-Mail: peter(dot)schuller(at)infidyne(dot)com Web: http://scode.infidyne.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)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(dot)schuller(at)infidyne(dot)com>'
Key retrival: Send an E-Mail to getpgpkey(at)scode(dot)infidyne(dot)com
E-Mail: peter(dot)schuller(at)infidyne(dot)com Web: http://scode.infidyne.com

Peter Schuller <peter(dot)schuller(at)infidyne(dot)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(dot)schuller(at)infidyne(dot)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(dot)schuller(at)infidyne(dot)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(dot)schuller(at)infidyne(dot)com>'
> Key retrival: Send an E-Mail to getpgpkey(at)scode(dot)infidyne(dot)com
> E-Mail: peter(dot)schuller(at)infidyne(dot)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(at)postgresql(dot)org)

Dixit Tim Frank <tfrank(at)registrar(dot)uoguelph(dot)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

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(dot)Besancon(at)prism(dot)uvsq(dot)fr>
wrote regarding Re: [ADMIN] Backing up postgresql databases:

> Dixit Tim Frank <tfrank(at)registrar(dot)uoguelph(dot)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

2001/3/19 Grant <grant(at)conprojan(dot)com(dot)au>

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