nixCraft

chiku · #1 (**permalink**) 03-01-2007, 05:45 PM

Hai,

How do I make a backup under postgres database server? Under mysql I can use mysqldump.. what about pgsql ??? any idea what command i need to use. Also specify script if possible.

TIA

rockdalinux · #2 (**permalink**) 03-01-2007, 06:31 PM

Here is my postgres (pgsql) backup script:

Code:

#!/bin/sh
# Bak dump dir - Change me
BACKUP="/backup"
# get list of dbs
PGDBS=$(psql -h localhost -U postgres -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'})

for d in $PGDBS; do
        NOW=`date '+%T %x'`
        vacuumdb -z -h localhost -U postgres $d
        pg_dump $d -h localhost | gzip > "$BACKUP/postgresql-$d-$NOW.gz"
done

Run script as postgres or root user. Cron job:

Code:

03 05 * * * /path/to/pgsql.backup.sh > /dev/null 2>&1

nixcraft · #3 (**permalink**) 03-01-2007, 09:43 PM

Also see Howto Backup PostgreSQL Databases for more info about commands.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Postgres idle connections	kasimani	Databases servers	1	14-05-2007 05:58 PM
Postgres vacuumdb usage	kasimani	Databases servers	2	06-03-2007 07:30 AM

nixCraft

Postgres backup

Postgres backup

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Similar Threads