Project

General

Profile

PostgreSQL work on stkensrv6n for 2015-08

Prepare

# Identify OS version
[enstore]$ cat /etc/redhat-release 
Scientific Linux SLF release 6.5 (Lederman)

# Identify running postmaster processes (shouldn't be any)
[enstore]$ ps aux | grep [p]ostmaster
products  1714  0.0  0.0 998032 54672 ?        S    Mar19   2:23 /usr/bin/postmaster -p 8800 -D /srv6/enstore/databases/accounting-db -i
products  1734  0.0  0.0 998028 54564 ?        S    Mar19   0:09 /usr/bin/postmaster -p 8802 -D /srv6/enstore/databases/drivestat-db -i

# Stop running postmaster processes (shouldn't be any) 
[products] pg_ctl stop --mode fast --pgdata /srv6/enstore/databases/accounting-db
[products] pg_ctl stop --mode fast --pgdata /srv6/enstore/databases/drivestat-db

Install database software

# Inventory installed versions of PostgreSQL (shouldn't be any)
[enstore] rpm -qa | egrep '^(pgdg|postgresql)' | sort

# Install PostgreSQL 9.3
# Note: As a fallback, RPMs for SLF6 are cached in gccensrv1:/home/enstore/pg_rpms/9.3
[root] rpm --nodeps -ivh http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-sl93-9.3-1.noarch.rpm
[root] yum -y install postgresql93 postgresql93-devel postgresql93-libs postgresql93-server postgresql93-contrib
[enstore] rpm -qa | egrep '^(pgdg|postgresql)' | sort

# Setup alternatives
[root]
VER="9.3" 
for PROG in "initdb" "pg_ctl" "pg_config" "postmaster" "psql"; do
  # Configure program:
  rm -f /var/lib/alternatives/pgsql-${PROG};
  /usr/sbin/alternatives --install /usr/bin/${PROG} pgsql-${PROG} /etc/alternatives/pgsql-${PROG} 920 --slave /etc/alternatives/pgsql-${PROG} pgsql-${PROG} /usr/pgsql-${VER}/bin/${PROG};
  # Configure man page:
  rm -f /var/lib/alternatives/pgsql-${PROG}man;
  /usr/sbin/alternatives --install /usr/share/man/man1/${PROG}.1 pgsql-${PROG}man /etc/alternatives/pgsql-${PROG}man 920 --slave /etc/alternatives/pgsql-${PROG}man pgsql-${PROG}man /usr/pgsql-${VER}/share/man/man1/${PROG}.1;
done

Update data directory owner

# Confirm correct dbserverowner in enstore config
[enstore@stkensrv6n ~]$ enstore config --show accounting_server dbserverowner
enstore
[enstore@stkensrv6n ~]$ enstore config --show drivestat_server dbserverowner
enstore

# Change and confirm owner of data directories recursively
[root@stkensrv6n ~]$ chown -R enstore:enstore /srv6/enstore/databases/accounting-db
[root@stkensrv6n ~]$ chown -R enstore:enstore /srv6/enstore/databases/drivestat-db
[enstore@stkensrv6n ~]$ ll -R /srv6/enstore/databases/accounting-db | less
[enstore@stkensrv6n ~]$ ll -R /srv6/enstore/databases/drivestat-db | less

Confirm backup directories

# Identify local backup root directory
[enstore@stkensrv6n ~]$ enstore config --show accounting_server PITR_area
/srv6/enstore/pg_pitr
[enstore@stkensrv6n ~]$ enstore config --show drivestat_server PITR_area
/srv6/enstore/pg_pitr

# Confirm local backup directory exists and is owned by enstore
[enstore@stkensrv6n ~]$ stat /srv6/enstore/pg_pitr/ | grep Uid
Access: (0775/drwxrwxr-x)  Uid: ( 5744/ enstore)   Gid: ( 6209/ enstore)

# Identify remote backup host and directory
[enstore@stkensrv6n ~]$ enstore config --show crons | grep backup_
 'backup_dir': '/srv3/enstore/backups',
 'backup_node': 'stkensrv3n.fnal.gov',

# Confirm remote backup directory exists on remote host, and is owned by enstore
[enstore@stkensrv6n ~]$ ssh enstore@stkensrv3n stat /srv3/enstore/backups | grep Uid
Access: (0777/drwxrwxrwx)  Uid: ( 5744/ enstore)   Gid: ( 6209/ enstore)

Update database configuration

# Backup existing pg_hba file
[enstore] cp -av /srv6/enstore/databases/accounting-db/pg_hba.conf /srv6/enstore/databases/accounting-db/pg_hba.conf.old
[enstore] cp -av /srv6/enstore/databases/drivestat-db/pg_hba.conf /srv6/enstore/databases/drivestat-db/pg_hba.conf.old

# Wipe preexisting custom configuration to prevent residual files
[enstore] rm -irv /srv6/enstore/databases/accounting-db/conf.d
[enstore] rm -irv /srv6/enstore/databases/drivestat-db/conf.d

# Copy config files from git
[user@localhost] cd ~/workspace/enstore-config
[user@localhost] git checkout stken
[user@localhost] git pull
[user@localhost] cd ./database/
[user@localhost] rsync -av accounting/* enstore@stkensrv6n:/srv6/enstore/databases/accounting-db/
[user@localhost] rsync -av drivestat/* enstore@stkensrv6n:/srv6/enstore/databases/drivestat-db/

# Configure owner of config files
[root] chown -v enstore:enstore /srv6/enstore/databases/accounting-db/pg_hba.conf
[root] chown -v enstore:enstore /srv6/enstore/databases/drivestat-db/pg_hba.conf
[root] chown -Rv enstore:enstore /srv6/enstore/databases/accounting-db/conf.d
[root] chown -Rv enstore:enstore /srv6/enstore/databases/drivestat-db/conf.d
[enstore] less /srv6/enstore/databases/accounting-db/pg_hba.conf
[enstore] less /srv6/enstore/databases/drivestat-db/pg_hba.conf
[enstore] more /srv6/enstore/databases/accounting-db/conf.d/* | less
[enstore] more /srv6/enstore/databases/drivestat-db/conf.d/* | less

Restart database

Before proceeding with this section, first obtain confirmation.

# Start databases
[root] service enstore-boot start

# Confirm startup
[enstore] ps aux | grep [p]ostmaster
enstore  1714  0.0  0.0 998032 54672 ?        S    Mar19   0:46 /usr/bin/postmaster -p 8800 -D /srv6/enstore/databases/accounting-db -i
enstore  1734  0.0  0.0 998028 54564 ?        S    Mar19   0:04 /usr/bin/postmaster -p 8802 -D /srv6/enstore/databases/drivestat-db -i

# Check startup logs
[enstore] ls -tr /srv6/enstore/databases/accounting-db/pg_log/* | tail -1 | xargs tail -vn +1
[enstore] ls -tr /srv6/enstore/databases/drivestat-db/pg_log/* | tail -1 | xargs tail -vn +1

# Confirm databases
[enstore] psql -qt -p 8800 -d postgres -c '\l'
 accounting | products | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 operation  | enstore  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres   | products | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0  | products | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/products          +
            |          |          |             |             | products=CTc/products
 template1  | products | UTF8     | en_US.UTF-8 | en_US.UTF-8 | products=CTc/products+
            |          |          |             |             | =c/products
[enstore] psql -qt -p 8802 -d postgres -c '\l'
 drivestat | enstore  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | products | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | products | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/products          +
           |          |          |             |             | products=CTc/products
 template1 | products | UTF8     | en_US.UTF-8 | en_US.UTF-8 | products=CTc/products+
           |          |          |             |             | =c/products

Test PITR backup

This test is conducted for the drivestat database cluster only because it is the smallest.

# Create backup
[enstore] $ENSTORE_DIR/tools/pg_base_backup.sh drivestatq
[enstore] echo $?
0

# Confirm presence of local and remote base backup
[enstore] ll -h $(enstore config --show drivestat_server PITR_area)/pg_base_backup/drivestat/
[enstore] ssh $(enstore config --show crons backup_node) ls -lh $(enstore config --show crons backup_dir)/pg_base_backup/drivestat/

# Confirm presence of new xlog file as triggered by backup
[enstore] ll -h $(enstore config --show drivestat_server PITR_area)/pg_xlog_archive/drivestat/
[enstore] ssh $(enstore config --show crons backup_node) ls -lh $(enstore config --show crons backup_dir)/pg_xlog_archive/drivestat/