Project

General

Profile

PostgreSQL work on stkensrv0n for 2015-08

Prepare

# Stop enstore (as needed)
[enstore] EPS | grep -v postgres | grep -v sshd
[enstore] enstore stop  # only if needed

# Identify running postmaster processes (shouldn't be any)
[enstore] ps aux | grep [p]ostmaster
products 28047  0.0  0.3 9042544 468428 ?      S    Mar19   3:15 /usr/bin/postmaster -p 8888 -D /db/enstore/databases/enstore-db -i

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

Upgrade database software

# Inventory installed versions of PostgreSQL
[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@stkensrv0n ~]$ enstore config --show database dbserverowner
enstore

# Change and confirm owner of data directory recursively
[root@stkensrv0n ~]$ chown -R enstore:enstore /db/enstore/databases/enstore-db
[enstore@stkensrv0n ~]$ ll -R /db/enstore/databases/enstore-db | less

Confirm backup directories

# Identify local backup root directory
[enstore@stkensrv0n ~]$ enstore config --show database PITR_area
/srv0/enstore/pg_pitr

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

# Identify remote backup host and directory
[enstore@stkensrv0n ~]$ 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@stkensrv0n ~]$ ssh enstore@stkensrv3n stat /srv3/enstore/backups | grep Uid
Access: (0777/drwxrwxrwx)  Uid: ( 5744/ enstore)   Gid: ( 6209/ enstore)

Update database configuration

# Backup existing config iles
[enstore] cp -av /db/enstore/databases/enstore-db/pg_hba.conf /db/enstore/databases/enstore-db/pg_hba.conf.old
[enstore] cp -av /db/enstore/databases/enstore-db/postgresql.conf /db/enstore/databases/enstore-db/postgresql.conf.old

# Wipe preexisting custom configuration to prevent residual files
[enstore] cp /db/enstore/databases/enstore-db/postgresql.conf.original /db/enstore/databases/enstore-db/postgresql.conf
[enstore] echo "include_dir 'conf.d'" >> /db/enstore/databases/enstore-db/postgresql.conf
[enstore] rm -irv /db/enstore/databases/enstore-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 enstore/* enstore@stkensrv0n:/db/enstore/databases/enstore-db/

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

Restart database

Before proceeding with this section, first obtain confirmation.

# Start enstore and databases
[root] service enstore-boot start
[enstore] ps aux | grep [p]ostmaster
enstore 28047  0.0  0.3 9042544 468428 ?      S    Mar19   3:15 /usr/bin/postmaster -p 8888 -D /db/enstore/databases/enstore-db -i
[enstore] ls -tr /db/enstore/databases/enstore-db/pg_log/* | tail -1 | xargs tail -vn +1

# Confirm databases
[enstore] psql -qt -p 8888 -d postgres -c '\l'
 enstoredb | 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 | =c/products          +
           |          |          |             |             | products=CTc/products