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"