PostgreSQL upgrade on stken of accounting-db and drivestat-db¶
# Access host
kinit
ssh enstore@stkensrv6n
# Stop enstore (as needed)
[enstore] EPS | grep -v postgres | grep -v sshd
[enstore] enstore stop # only if needed
# Install PostgreSQL
[root] rpm --nodeps -ivh http://yum.postgresql.org/9.3/redhat/rhel-5-x86_64/pgdg-sl93-9.3-1.noarch.rpm
[root] yum -y install postgresql93 postgresql93-devel postgresql93-libs postgresql93-server postgresql93-contrib
# As a fallback, RPMs have been downloaded in ~enstore/pg_rpms/
# 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
# Login as products
[root] su - products -s /bin/bash
[products] source ~enstore/.bashrc
# Identify postmaster processes
[products] ps aux | grep [p]ostmaster
products 6268 0.0 0.0 998284 54588 ? S Jan15 0:57 /usr/bin/postmaster -p 8800 -D /srv6/enstore/databases/accounting-db -i
products 6284 0.0 0.0 998096 54488 ? S Jan15 0:46 /usr/bin/postmaster -p 8802 -D /srv6/enstore/databases/drivestat-db -i
# Stop postmaster proceses
[products] /usr/pgsql-9.2/bin/pg_ctl stop --mode fast --pgdata /srv6/enstore/databases/accounting-db
[products] /usr/pgsql-9.2/bin/pg_ctl stop --mode fast --pgdata /srv6/enstore/databases/drivestat-db
# Initialize 9.3 clusters
[products] mv /srv6/enstore/databases/accounting-db /srv6/enstore/databases/accounting-db-9.2
[products] mv /srv6/enstore/databases/drivestat-db /srv6/enstore/databases/drivestat-db-9.2
[products] mkdir -m 0700 /srv6/enstore/databases/accounting-db
[products] mkdir -m 0700 /srv6/enstore/databases/drivestat-db
[products] /usr/pgsql-9.3/bin/initdb --pgdata=/srv6/enstore/databases/accounting-db
[products] /usr/pgsql-9.3/bin/initdb --pgdata=/srv6/enstore/databases/drivestat-db
# Run pg_upgrade
[products] mkdir -p ~/pg_upgrade_run/accounting
[products] mkdir -p ~/pg_upgrade_run/drivestat
[products] cd ~/pg_upgrade_run/accounting
[products] time /usr/pgsql-9.3/bin/pg_upgrade --link --old-datadir=/srv6/enstore/databases/accounting-db-9.2 --new-datadir=/srv6/enstore/databases/accounting-db --old-bindir=/usr/pgsql-9.2/bin/ --new-bindir=/usr/pgsql-9.3/bin/
[products] cd ~/pg_upgrade_run/drivestat
[products] time /usr/pgsql-9.3/bin/pg_upgrade --link --old-datadir=/srv6/enstore/databases/drivestat-db-9.2 --new-datadir=/srv6/enstore/databases/drivestat-db --old-bindir=/usr/pgsql-9.2/bin/ --new-bindir=/usr/pgsql-9.3/bin/
# Configure pg_hba.conf for accounting-db
[products] cp -av /srv6/enstore/databases/accounting-db/pg_hba.conf /srv6/enstore/databases/accounting-db/pg_hba.conf.original
[products]
# Add to "IPv4 local connections" section:
host accounting enstore 192.168.89.12/24 trust
# stkensrv0n
host accounting enstore 131.225.13.59/32 trust
host accounting enstore_reader 131.225.13.59/32 trust
# stkensrv1n
host accounting enstore 131.225.13.53/32 trust
host accounting enstore_reader 131.225.13.53/32 trust
# stkensrv2n
host accounting enstore 131.225.13.58/32 trust
host accounting enstore_reader 131.225.13.58/32 trust
# stkensrv3n
host accounting enstore 131.225.13.51/32 trust
host accounting enstore_reader 131.225.13.51/32 trust
# stkensrv4n
host accounting enstore 131.225.13.4/32 trust
host accounting enstore_reader 131.225.13.4/32 trust
# stkensrv5n
host accounting enstore 131.225.13.3/32 trust
host accounting enstore_reader 131.225.13.3/32 trust
# stkensrv6n
host accounting enstore 131.225.13.92/32 trust
host accounting enstore_reader 131.225.13.92/32 trust
# stkenscan1 (tablePlotter)
host accounting enstore_reader 131.225.13.10/32 trust
# ensrv1
host accounting enstore_reader 131.225.13.16/32 trust
#
# stkensrv0n
host operation enstore 131.225.13.59/32 trust
host operation enstore_reader 131.225.13.59/32 trust
# stkensrv1n
host operation enstore 131.225.13.53/32 trust
host operation enstore_reader 131.225.13.53/32 trust
# stkensrv2n
host operation enstore 131.225.13.58/32 trust
host operation enstore_reader 131.225.13.58/32 trust
# stkensrv3n
host operation enstore 131.225.13.51/32 trust
host operation enstore_reader 131.225.13.51/32 trust
# stkensrv4n
host operation enstore 131.225.13.4/32 trust
host operation enstore_reader 131.225.13.4/32 trust
# stkensrv5n
host operation enstore 131.225.13.3/32 trust
host operation enstore_reader 131.225.13.3/32 trust
#
#Write access from other systems to operation:
host operation enstore 131.225.215.52/32 trust
host operation enstore 131.225.215.11/32 trust
host operation enstore 131.225.215.12/32 trust
host operation enstore 131.225.215.14/32 trust
host operation enstore 131.225.215.15/32 trust
host operation enstore 131.225.215.54/32 trust
host operation enstore 131.225.164.10/32 trust
host operation enstore 131.225.164.13/32 trust
host operation enstore 131.225.164.15/32 trust
host operation enstore 131.225.164.17/32 trust
host operation enstore 131.225.164.18/32 trust
host operation enstore 131.225.164.29/32 trust
#Read only other systems
host accounting enstore_reader 131.225.215.52/32 trust
host accounting enstore_reader 131.225.215.11/32 trust
host accounting enstore_reader 131.225.215.12/32 trust
host accounting enstore_reader 131.225.215.14/32 trust
host accounting enstore_reader 131.225.215.15/32 trust
host accounting enstore_reader 131.225.215.54/32 trust
host accounting enstore_reader 131.225.164.10/32 trust
host accounting enstore_reader 131.225.164.13/32 trust
host accounting enstore_reader 131.225.164.15/32 trust
host accounting enstore_reader 131.225.164.17/32 trust
host accounting enstore_reader 131.225.164.18/32 trust
host accounting enstore_reader 131.225.164.29/32 trust
#include cmsstor12.fnal.gov and cmsstor16.fnal.gov and cmssrv28.fnal.gov
#removed cmsstor12 and cmsstor16 on 05-15-2012 -yw: Chih-Hao said they
#are retired.
host accounting enstore_reader 131.225.206.33/32 trust
host accounting enstore_reader 131.225.204.164/32 trust
#include cmsdcam3
host accounting enstore_reader 131.225.189.91/32 trust
#include Dmitry's desktop
host accounting enstore_reader 131.225.84.42/32 trust
#added cmssrv147/148 per request from Chih-Hao (agreed by Stan)
host accounting enstore_reader 131.225.206.255/32 trust
host accounting enstore_reader 131.225.207.8/32 trust
host accounting enstore_reader 131.225.13.97/32 trust
# Configure pg_hba.conf for drivestat-db
[products] cp -av /srv6/enstore/databases/drivestat-db/pg_hba.conf /srv6/enstore/databases/drivestat-db/pg_hba.conf.original
[products]
# Add to "IPv4 local connections" section:
# stkensrv0n
host drivestat enstore 131.225.13.59/32 trust
host drivestat enstore_reader 131.225.13.59/32 trust
# stkensrv1n
host drivestat enstore 131.225.13.53/32 trust
host drivestat enstore_reader 131.225.13.53/32 trust
# stkensrv2n
host drivestat enstore 131.225.13.58/32 trust
host drivestat enstore_reader 131.225.13.58/32 trust
# stkensrv3n
host drivestat enstore 131.225.13.51/32 trust
host drivestat enstore_reader 131.225.13.51/32 trust
# stkensrv4n
host drivestat enstore 131.225.13.4/32 trust
host drivestat enstore_reader 131.225.13.4/32 trust
# stkensrv5n
host drivestat enstore 131.225.13.3/32 trust
host drivestat enstore_reader 131.225.13.3/32 trust
# stkensrv6n
host drivestat enstore 131.225.13.92/32 trust
host drivestat enstore_reader 131.225.13.92/32 trust
# stkenscan1 (tablePlotter)
host drivestat enstore_reader 131.225.13.10/32 trust
# ensrv1
host drivestat enstore_reader 131.225.13.16/32 trust
#Read only other systems
host drivestat enstore_reader 131.225.215.52/32 trust
host drivestat enstore_reader 131.225.215.11/32 trust
host drivestat enstore_reader 131.225.215.12/32 trust
host drivestat enstore_reader 131.225.215.14/32 trust
host drivestat enstore_reader 131.225.215.15/32 trust
host drivestat enstore_reader 131.225.215.54/32 trust
host drivestat enstore_reader 131.225.164.10/32 trust
host drivestat enstore_reader 131.225.164.13/32 trust
host drivestat enstore_reader 131.225.164.15/32 trust
host drivestat enstore_reader 131.225.164.17/32 trust
host drivestat enstore_reader 131.225.164.18/32 trust
host drivestat enstore_reader 131.225.164.29/32 trust
#include Dmitry's desktop
host drivestat enstore_reader 131.225.84.42/32 trust
#include cmsstor12.fnal.gov and cmsstor16.fnal.gov and cmssrv28.fnal.gov
host accounting enstore_reader 131.225.207.112/32 trust
host accounting enstore_reader 131.225.207.13/32 trust
host accounting enstore_reader 131.225.206.33/32 trust
#include cmsdcam3
host accounting enstore_reader 131.225.189.91/32 trust
# Configure postgresql.conf
[products] cp -av /srv6/enstore/databases/accounting-db/postgresql.conf /srv6/enstore/databases/accounting-db/postgresql.conf.original
[products] cp -av /srv6/enstore/databases/drivestat-db/postgresql.conf /srv6/enstore/databases/drivestat-db/postgresql.conf.original
[products] echo "include_dir 'conf.d'" >> /srv6/enstore/databases/accounting-db/postgresql.conf
[products] echo "include_dir 'conf.d'" >> /srv6/enstore/databases/drivestat-db/postgresql.conf
[products] chmod a-w /srv6/enstore/databases/accounting-db/postgresql.conf
[products] chmod a-w /srv6/enstore/databases/drivestat-db/postgresql.conf
[products] mkdir /srv6/enstore/databases/accounting-db/conf.d
[products] mkdir /srv6/enstore/databases/drivestat-db/conf.d
# Write same conf.d files for accounting-db and drivestat-db
[products]
# 1_tuned.conf
max_connections = 512
shared_buffers = 800MB
work_mem = 128MB
maintenance_work_mem = 256MB
checkpoint_segments = 10
checkpoint_timeout = 20min
checkpoint_warning = 0s
# 2_logging.conf
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = 100000
log_autovacuum_min_duration = 0
# 3_misc.conf
listen_addresses = '*' # Same as PGOPTS="-i"
# Start enstore and databases
[root] /etc/init.d/enstore-boot start
[products] ps aux | grep [p]ostmaster
products 1714 0.0 0.0 998032 54608 ? S 11:52 0:00 /usr/bin/postmaster -p 8800 -D /srv6/enstore/databases/accounting-db -i
products 1734 0.0 0.0 998028 54564 ? S 11:52 0:00 /usr/bin/postmaster -p 8802 -D /srv6/enstore/databases/drivestat-db -i
[products] ls -tr /srv6/enstore/databases/accounting-db/pg_log/* | tail -1 | xargs tail -vn +1
[products] ls -tr /srv6/enstore/databases/drivestat-db/pg_log/* | tail -1 | xargs tail -vn +1
# Check PostgreSQL
[products] /usr/pgsql-9.3/bin/psql -qt -p 8800 -d postgres -c 'SHOW server_version;'
9.3.6
[products] /usr/pgsql-9.3/bin/psql -qt -p 8802 -d postgres -c 'SHOW server_version;'
9.3.6
[products] /usr/pgsql-9.3/bin/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
[products] /usr/pgsql-9.3/bin/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
# Analyze
# Cleanup
[products] rm -rf ~/pg_upgrade_run
[root] yum remove pgdg-sl92 postgresql92 postgresql92-libs