Project

General

Profile

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