5.4 Backup & Restore

Dump a single table including complete SQL statements:

pg_dump --inserts -t your_table -f foo.dump dbname

Backup / DB Dump Bash Script

#!/bin/bash

# -- VERSION 0.80.02

DBNAME=auctionline2002

BINPATH=/usr/local/pgsql/bin
PGHOMEPATH=/home/postgres

#BINPATH=/usr/bin
#PGHOMEPATH=/var/lib/pgsql

echo `/bin/date`

# -- YYYYMMDD:
mydate=`date +%Y%m%d`

mkdir $PGHOMEPATH/$DBNAME/$mydate-all

echo -n "DB Dump all to $PGHOMEPATH/$DBNAME/$mydate-all/$DBNAME.dump ... "
# -Z 5  =  compression; use .gz for file extension
$BINPATH/pg_dump -f "$PGHOMEPATH/$DBNAME/$mydate-all/$DBNAME.dump" $DBNAME

echo -n "done at "
echo `/bin/date`

cd $PGHOMEPATH/$DBNAME

echo -n "Dump was created on "  >> $mydate-all/readme.txt
echo `hostname`                 >> $mydate-all/readme.txt
echo -n "at "                   >> $mydate-all/readme.txt
echo `/bin/date`                >> $mydate-all/readme.txt

/bin/tar -czf $PGHOMEPATH/$DBNAME/$mydate-all.tar.gz $mydate-all
/bin/rm -Rf $PGHOMEPATH/$DBNAME/$mydate-all

# -- List last created backups
ls -lt $PGHOMEPATH/$DBNAME/*.tar.gz|head -5

Full Story: