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"
I keep forgetting syntax for these commands, maybe simply because I’m not using them too often. We mainly use Horizon web interface, do we? Anyway, here we go: