[[http://tmade.de|Home tmade.de]]

[[http://wiki.tmade.de|Home Wiki]]

===== Postgresql =====

Important directories:

  /var/lib/postgresql/8.4/main
  /etc/postgresql/8.4/main
==== DUMP ====

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:
<code> 
#!/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

</code>
 
This command will dump all db´s to a file like

  hostname.date_hh:mm:ss
or
  hostname.DAY_OF_WEEK

==== RESTORE ====

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!
==== POINT IN TIME RECOVERY ====

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

==== CREATE DB ====

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

==== Roles and Grants ====

To add role "CREATEDB" to user test:
  
  ALTER USER test CREATEDB;
or
  sudo -u postgres psql -c 'alter user test with createdb' postgres



  
==== Commands ====

  \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