PostgreSQL upgrade on stken of chimera¶

# Access host
kinit
ssh enstore@stkensrv1n

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

# Install PostgreSQL
[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
# 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

# Configure 9.3 service
[root] mv /etc/init.d/postgresql-9.3.rpmnew /etc/init.d/postgresql-9.3  # If applicable
[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=/db/database" > /etc/sysconfig/pgsql/postgresql-9.3
[root] /sbin/chkconfig postgresql-9.3 on
[root] /sbin/chkconfig --list postgresql-9.3

# Configure 9.2 service
[root] /sbin/service postgresql-9.2 stop
[root] /sbin/chkconfig postgresql-9.2 off
[root] /sbin/chkconfig --list postgresql-9.2

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

# 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=/db/database-9.2 --new-datadir=/db/database --old-bindir=/usr/pgsql-9.2/bin/ --new-bindir=/usr/pgsql-9.3/bin/

# Configure pg_hba.conf
[enstore] cp -av /db/database/pg_hba.conf /db/database/pg_hba.conf.original
[enstore]
# Add to "IPv4 local connections" section:
host    all         enstore_reader      131.225.13.97/32        trust
host    all         enstore_reader      131.225.13.45/32        trust
host    all         enstore             131.225.13.251/32       trust

# Configure postgresql.conf
[enstore] cp -av /db/database/postgresql.conf /db/database/postgresql.conf.original
[enstore] echo "include_dir 'conf.d'" >> /db/database/postgresql.conf
[enstore] chmod a-w /db/database/postgresql.conf
[enstore] mkdir /db/database/conf.d

# Copy remaining config files
[enstore] cp -av /db/database-9.2/recovery.conf.example  /db/database/
[enstore] cp -av /db/database-9.2/postgresql.conf.no.wal /db/database/postgresql92.conf.no.wal
[enstore] cp -av /db/database-9.2/postgresql.conf.wal    /db/database/postgresql92.conf.wal

# Write conf.d files
[enstore]
# 1_tuned.conf
max_connections = 512
shared_buffers = 8192MB
temp_buffers = 1024MB
work_mem = 512MB
maintenance_work_mem = 2048MB
max_stack_depth = 2MB
vacuum_cost_delay = 50ms
synchronous_commit = off
wal_buffers = 245MB
wal_writer_delay = 10s
checkpoint_segments = 64
checkpoint_completion_target = 0.9
random_page_cost = 2.0
effective_cache_size = 94GB
# 2_archive.conf
wal_level = archive
archive_mode = on
archive_command = '/opt/enstore/tools/wal_backup.sh %p %f'
# 3_logging.conf
log_directory = '/srv1/postgres-log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = 100
# 4_autovacuum.conf
log_autovacuum_min_duration = 0
autovacuum_vacuum_threshold = 100
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.05
# 5_misc.conf
listen_addresses = '*'  # Same as PGOPTS="-i" 
bytea_output = 'escape'

# Start service
[root] /sbin/service postgresql-9.3 start
[root] /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 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 monitor   | enstore | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 pgbench   | 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 | enstore=CTc/enstore+
           |         |          |             |             | =c/enstore
 test      | enstore | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

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

# Cleanup
[enstore] rm -rf ~/pg_upgrade_run
[root] yum remove pgdg-sl92 postgresql92 postgresql92-libs

# Mount namespace
[root] dcache start nfsDomain
[root] mount -v /pnfs/fs
[root] ls /pnfs/fs