Project

General

Profile

PostgreSQL upgrade on cdfensrv1n of chimera

# Access host
kinit
ssh enstore@cdfensrv1n

# Dismount namespace
[enstore] mount | grep pnfs
[enstore] ls /pnfs/fs
[root] umount -fv /pnfs/fs
[enstore] mount | grep pnfs
[enstore] ls /pnfs/fs
[root] dcache stop nfsDomain

# Install PostgreSQL 9.2
# Note: As a fallback, RPMs for SLF6 are cached in gccensrv1:/home/enstore/pg_rpms/
[root] rpm --nodeps -iv http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-sl92-9.2-8.noarch.rpm
[root] yum -y install postgresql92 postgresql92-devel postgresql92-libs postgresql92-server postgresql92-contrib
[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/
[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

# Configure 9.3 service
[root] sed -i'-original' -e 's/postgres:postgres/enstore:enstore/g' -e 's/l postgres /l enstore /g' /etc/init.d/postgresql-9.3
[root] echo "PGDATA=/srv1/pnfs/db/psql-data" > /etc/sysconfig/pgsql/postgresql-9.3
[root] /sbin/chkconfig --list postgresql-9.3
[root] /sbin/chkconfig postgresql-9.3 on
[root] /sbin/chkconfig --list postgresql-9.3

# Initialize 9.3 cluster
[root] mv /srv1/pnfs/db/psql-data /srv1/pnfs/db/psql-data-9.2
[root] mkdir -m 0700 /srv1/pnfs/db/psql-data
[root] chown enstore:enstore /srv1/pnfs/db/psql-data
[enstore] /usr/pgsql-9.3/bin/initdb --pgdata=/srv1/pnfs/db/psql-data

# Run pg_upgrade
[enstore] mkdir -p ~/pg_upgrade_run
[enstore] cd ~/pg_upgrade_run/
[enstore] time /usr/pgsql-9.3/bin/pg_upgrade --link --old-datadir=/srv1/pnfs/db/psql-data-9.2 --new-datadir=/srv1/pnfs/db/psql-data --old-bindir=/usr/pgsql-9.2/bin/ --new-bindir=/usr/pgsql-9.3/bin/

# Configure postgresql.conf
[enstore] cp -av /srv1/pnfs/db/psql-data/postgresql.conf /srv1/pnfs/db/psql-data/postgresql.conf.original
[enstore] echo "include_dir 'conf.d'" >> /srv1/pnfs/db/psql-data/postgresql.conf
[enstore] chmod a-w /srv1/pnfs/db/psql-data/postgresql.conf

# Copy used config files from git
[enstore] cp -av /srv1/pnfs/db/psql-data/pg_hba.conf /srv1/pnfs/db/psql-data/pg_hba.conf.original
[user@localhost] cd ~/workspace/enstore-config
[user@localhost] git checkout cdfen
[user@localhost] git pull
[user@localhost] cd ./database/chimera
[user@localhost] rsync -av * enstore@cdfensrv1n:/srv1/pnfs/db/psql-data/
[enstore] less /srv1/pnfs/db/psql-data/pg_hba.conf
[enstore] more /srv1/pnfs/db/psql-data/conf.d/* | column

# Copy unused config files from 9.2
[enstore] cp -av /srv1/pnfs/db/psql-data-9.2/recovery.conf.example /srv1/pnfs/db/psql-data/
[enstore] cp -av /srv1/pnfs/db/psql-data-9.2/postgresql.conf.no.wal /srv1/pnfs/db/psql-data/postgresql92.conf.no.wal

# Start service
[root] /sbin/service postgresql-9.3 start
[enstore] /sbin/service postgresql-9.3 status
[enstore] ls -tr /srv1/postgres-log/* | tail -1 | xargs tail -vn +1

# Check PostgreSQL
[enstore] /usr/pgsql-9.3/bin/psql -qt -d postgres -c 'SHOW server_version;'
 9.3.6
[enstore] /usr/pgsql-9.3/bin/psql -qt -d postgres -c '\l'
 chimera   | enstore | SQL_ASCII | C           | C           | 
 monitor   | enstore | SQL_ASCII | C           | C           | 
 pgcheck   | enstore | UTF8      | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | enstore | UTF8      | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | enstore | UTF8      | en_US.UTF-8 | en_US.UTF-8 | =c/enstore         +
           |         |           |             |             | enstore=CTc/enstore
 template1 | enstore | UTF8      | en_US.UTF-8 | en_US.UTF-8 | =c/enstore         +
           |         |           |             |             | enstore=CTc/enstore

# Analyze
[enstore] time ~/pg_upgrade_run/analyze_new_cluster.sh

# Cleanup
[root] yum remove pgdg-sl92 postgresql92 postgresql92-libs
[enstore] rm -rf ~/pg_upgrade_run
[root] rm -rf /srv1/pnfs/db/psql-data-9.2