Mar 042016
 

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 devnull@mielnet.pl
#############################################################################

#------------ 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"
Feb 102016
 

4x_NvidiaGTX780 GPU

I’ve got Centos 7 based Bacula installation with storage daemon writing to file volumes located on ZFS filesystem. Chown’ing filesystem to user bacula was not enough, SElinux being SElinux didn’t particularly like bacula writing to location chosen by me (/tank/backup) as it expects Bacula to write to /bacula by default.

Lets identify available Bacula contexts and re-label /tank/backup accordingly

# semanage fcontext -l | grep bacula
 /bacula(/.*)? all files system_u:object_r:bacula_store_t:s0
 /etc/bacula.* all files system_u:object_r:bacula_etc_t:s0
 /var/bacula(/.*)? all files system_u:object_r:bacula_store_t:s0
 /var/lib/bacula.* all files system_u:object_r:bacula_var_lib_t:s0
 /var/log/bacula.* all files system_u:object_r:bacula_log_t:s0
 /var/run/bacula.* regular file system_u:object_r:bacula_var_run_t:s0
 /usr/sbin/bacula.* regular file system_u:object_r:bacula_exec_t:s0
 /var/spool/bacula.* all files system_u:object_r:bacula_spool_t:s0
 /var/spool/bacula/log(/.*)? all files system_u:object_r:var_log_t:s0
 /etc/rc\.d/init\.d/bacula.* regular file system_u:object_r:bacula_initrc_exec_t:s0
 /usr/sbin/bat regular file system_u:object_r:bacula_admin_exec_t:s0
 /usr/sbin/bconsole regular file system_u:object_r:bacula_admin_exec_t:s0

Ahh OK, so it’s called “system_u:object_r:bacula_store_t:s0” – lets apply it

chcon system_u:object_r:bacula_store_t:s0 /tank/backup
semanage fcontext -a -t bacula_store_t "/tank/backup(/.*)?"
restorecon -R -v /tank/backup

Same will work if your Centos 7 client will refuse to restore data to /bacula-restores, with message in server log:

26-Sep 14:40 death-star JobId 24822: Error: mkpath.c:138 Cannot create directory /bacula-restores/backup: ERR=Permission denied

and message in client log:

type=AVC msg=audit(1474897201.721:307): avc:  denied  { write } for  pid=26477 comm="bacula-fd" name="bacula-restores" dev="vda1" ino=159551617 scontext=system_u:system_r:bacula_t:s0 tcontext=unconfined_u:object_r:default_t:s0 tclass=dir

Simply run:

chcon system_u:object_r:bacula_store_t:s0 /bacula-restores
semanage fcontext -a -t bacula_store_t "/bacula-restores(/.*)?"
restorecon -R -v /bacula-restores
ls -lZ /

and now your restore job will run just fine. Magic.