PostgreSQL work on stkensrv6n for 2015-08¶
- Table of contents
- 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/