Managing and backing up PostgreSQL

Installing on Centos 6

I need PostgreSQL 9.3 on my Centos 6 box

yum localinstall https://download.postgresql.org/pub/repos/yum/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-2.noarch.rpm

yum list postgres*
yum install postgresql93-server
service postgresql-9.3 initdb

chkconfig postgresql-9.3 
service postgresql-9.3 start
service postgresql-9.3 status

Login to database

sudo -i -u postgres
psql --username=postgres -l 
# list all databases on psql prompt
\list or \l
# list all tables in the current database
\dt

Enabling password auth

sudo vi /var/lib/pgsql/data/pg_hba.conf

Find the lines that looks like this, near the bottom of the file:

# pg_hba.conf excerpt (original)
host    all             all             127.0.0.1/32            ident
host    all             all             ::1/128                 ident

Then replace “ident” with “md5”, so they look like this:

# pg_hba.conf excerpt (updated)
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Save and exit. PostgreSQL is now configured to allow password authentication.

Extensions

IF you need extensions, like RegExp-optimized index extension pg_trgm, you are going to need postgresql-contrib package. Once you have it installed, run:

$ sudo -u postgres sh
$ psql puppetdb -c 'create extension pg_trgm'
$ exit

File based backup

service postgresql stop
tar -czf /backup/full_postgres_backup-`date +%Y%m%d`.tar.gz' /var/lib/pgsql/data
service postgresql start

Single DB dump, for example spacewalk database

su - postgres -c 'pg_dump rhnschema |gzip -c > /backup/rhnschema_postgres_backup-`date +%Y%m%d`.sql.gz'

Backing up from cron

Moar scripts here https://github.com/zmielna/backup_scripts

# crontab -l
01 3 * * * /root/bin/postgresql_dump.sh 2>&1 |logger

vim /root/bin/postgresql_dump.sh

#!/bin/bash
#############################################################################
# Simple script to create a snapshot of a PostgreSQL databases.
# Can be run from cron like that
# 01 3 * * * /root/bin/postgresql_dump.sh 2>&1 |logger
# 
# Send bugreports, fixes, enhancements, t-shirts, money, beer & pizza to [email protected]
#############################################################################

#------------ variables
# Directory to store backups in
DST=/backup/dbback_postgresql
# DATABASES="postgres rhnschema"
DATABASES=`su - postgres -c "psql --username=postgres -l -x"|grep Name|grep -v template|cut -d"|" -f2|xargs`

# Any backups older than this will be deleted first
KEEPDAYS=7
DATE=$(date  +%Y-%m-%d)
#------------ code
/bin/logger "Starting PostgreSQL Dump....."
# cd $DST
find ${DST} -type f -mtime +${KEEPDAYS} -exec rm -f {} \;
rmdir $DST/* 2>/dev/null
mkdir -p ${DST}/${DATE}
chown postgres. ${DST}/${DATE}
for db in $DATABASES ; do
        echo -n "Backing up ${db}... " | logger
	su - postgres -c  "pg_dump ${db} |gzip -c > ${DST}/${DATE}/${db}-`date +%Y%m%d`.sql.gz"
        echo -n "Done with $db." | logger
done
/bin/logger "OK, all PostgreSQL dumps done in $DST"