Important directories:

/var/lib/postgresql/8.4/main
/etc/postgresql/8.4/main

To dump a postgresql database you can use the “pg_dumpall” command which dumps ALL databases.

Example:

su - postgres -c "pg_dumpall > /path/to/backup/`hostname`.`date +%d.%m.%Y_%T`"
su - postgres -c "pg_dumpall > /opt/portalu/backup/dumpall_`hostname`_`date +%a`"

Script:

 
#!/bin/bash
#Variables
BACKUPDIR="/backup"
LOGFILE=${BACKUPDIR}/`date +%d.%m.%y`.log
LOGERR=${BACKUPDIR}/ERROR_`date +%d.%m.%y`.log
MAILADDR="test@test.de"
HOST=`hostname`
DUMPALL=dumpall_`hostname`_`date +%a`
DATE=`date +%a`

# IO redirection for logging.
touch ${LOGFILE}
exec 6>&1           # Link file descriptor #6 with stdout.
                    # Saves stdout.
exec > ${LOGFILE}     # stdout replaced with file ${LOGFILE}.

touch ${LOGERR}
exec 7>&2           # Link file descriptor #7 with stderr.
                    # Saves stderr.
exec 2> ${LOGERR}     # stderr replaced with file ${LOGERR}.
#

# Dumping global data (for example roles)
pg_dumpall -U postgres -g > ${BACKUPDIR}/globals.sql
pg_dumpall -U postgres > ${BACKUPDIR}/$DUMPALL.dump
pg_dumpall -U postgres -l testdb > ${BACKUPDIR}/testdb.dump

#Dumping indidual databases in tar (uncompressed binary) format
for dbname in `psql -qXtc "select datname from pg_catalog.pg_database where datname<>'template0'" template1`;

do
pg_dump -U postgres -b "$dbname" > "${BACKUPDIR}/$dbname.dump"
echo "$dbname" >> ${LOGFILE}
done

if [ -s "${LOGERR}" ]
        then
                cat "${LOGERR}" | mail -s "ERRORS REPORTED: Postgresql Backup error Log for ${HOST} - `date`" ${MAILADDR}
        else
                cat "${LOGFILE}" | mail -s "Postgresql Backup Log for ${HOST} - `date`" ${MAILADDR}
fi

cd ${BACKUPDIR}/

tar -pczf ${HOST}_${DATE}.tar.gz *.dump *.sql *.log
rm *.log *.dump *.sql

This command will dump all db´s to a file like

hostname.date_hh:mm:ss

or

hostname.DAY_OF_WEEK

The following command shows all db´s:

psql -qXtc "select datname from pg_catalog.pg_database where datname<>'template0'" template1

or

psql -l

To restore a DB (created with “pg_dumpall”) you have:

dropdb database name;
dropdb -U postgresql [Database]
createdb --owner=db_owner --encoding=UTF-8 database_name

Afterwards you have to invoke:

psql -f dumpfile postgres               #"postgres" is the owner of the db!

http://opensourcedbms.com/dbms/how-to-do-point-in-time-recovery-with-postgresql-9-2-pitr-3/

To create a DB as postgres:

createdb demo
createdb -p 5000 -h eden -E LATIN1 -e demo    #To create the database demo using the server on host eden, port 5000, using the LATIN1 encoding scheme
createdb --owner=test --encoding=UTF-8 test

To create a DB as user test via psql:

psql -U test -d postgres -h localhost -c "CREATE DATABASE test OWNER test"

To create a DB within psql-shell:

CREATE DATABASE test OWNER test

To add role “CREATEDB” to user test:

ALTER USER test CREATEDB;

or

sudo -u postgres psql -c 'alter user test with createdb' postgres

\connect database                                                        #Use Database "database"
\c database                                                              #Use Database "database"
\du                                                                      #List of roles/ attributes
\l                                                                       #List all databases
\dt                                                                      #Would return the list of all tables in the database you're connected to.
SELECT * FROM table_name;                                                #Replace "table_name" to a table listed by "\dt" and you get info
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';"      #Set db-password for user postgres