Project

General

Profile

PostgreSQL upgrade on cdfensrv0n of enstore-db accounting-db and drivestat-db

# Access host
kinit
ssh enstore@cdfensrv0n

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

# 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

# Login as products
[root] su - products -s /bin/bash
# If the "id: cannot find name for group ID 4525" error is returned, run "[root] groupadd --gid 4525 products" and retry.
[products] source ~enstore/.bashrc

# Identify running postmaster processes (shouldn't be any)
[products] ps aux | grep [p]ostmaster
products  5962  0.0  0.1 2306520 85032 ?       S    Feb10   0:26 /usr/bin/postmaster -p 8888 -D /srv0/enstore/databases/enstore-db -i
products  5978  0.0  0.3 8745480 232544 ?      S    Feb10   0:42 /usr/bin/postmaster -p 8800 -D /srv0/enstore/databases/accounting-db -i
products  5994  0.0  0.1 2306520 85024 ?       S    Feb10   0:58 /usr/bin/postmaster -p 8802 -D /srv0/enstore/databases/drivestat-db -i

# Stop running postmaster processes (shouldn't be any)
[products] /usr/pgsql-9.2/bin/pg_ctl stop --mode fast --pgdata /srv0/enstore/databases/enstore-db
[products] /usr/pgsql-9.2/bin/pg_ctl stop --mode fast --pgdata /srv0/enstore/databases/accounting-db
[products] /usr/pgsql-9.2/bin/pg_ctl stop --mode fast --pgdata /srv0/enstore/databases/drivestat-db

# Initialize 9.3 clusters
[root] mv /srv0/enstore/databases/enstore-db /srv0/enstore/databases/enstore-db-9.2
[root] mv /srv0/enstore/databases/accounting-db /srv0/enstore/databases/accounting-db-9.2
[root] mv /srv0/enstore/databases/drivestat-db /srv0/enstore/databases/drivestat-db-9.2

[root] mkdir -m 0700 /srv0/enstore/databases/enstore-db
[root] mkdir -m 0700 /srv0/enstore/databases/accounting-db
[root] mkdir -m 0700 /srv0/enstore/databases/drivestat-db

[root] chown products:products /srv0/enstore/databases/enstore-db
[root] chown products:products /srv0/enstore/databases/accounting-db
[root] chown products:products /srv0/enstore/databases/drivestat-db

[products] /usr/pgsql-9.3/bin/initdb --pgdata=/srv0/enstore/databases/enstore-db
[products] /usr/pgsql-9.3/bin/initdb --pgdata=/srv0/enstore/databases/accounting-db
[products] /usr/pgsql-9.3/bin/initdb --pgdata=/srv0/enstore/databases/drivestat-db

# Run pg_upgrade
[products] mkdir -p /tmp/pg_upgrade_run/enstore
[products] cd /tmp/pg_upgrade_run/enstore
[products] time /usr/pgsql-9.3/bin/pg_upgrade --link --old-datadir=/srv0/enstore/databases/enstore-db-9.2 --new-datadir=/srv0/enstore/databases/enstore-db --old-bindir=/usr/pgsql-9.2/bin/ --new-bindir=/usr/pgsql-9.3/bin/

[products] mkdir -p /tmp/pg_upgrade_run/accounting
[products] cd /tmp/pg_upgrade_run/accounting
[products] time /usr/pgsql-9.3/bin/pg_upgrade --link --old-datadir=/srv0/enstore/databases/accounting-db-9.2 --new-datadir=/srv0/enstore/databases/accounting-db --old-bindir=/usr/pgsql-9.2/bin/ --new-bindir=/usr/pgsql-9.3/bin/

[products] mkdir -p /tmp/pg_upgrade_run/drivestat
[products] cd /tmp/pg_upgrade_run/drivestat
[products] time /usr/pgsql-9.3/bin/pg_upgrade --link --old-datadir=/srv0/enstore/databases/drivestat-db-9.2 --new-datadir=/srv0/enstore/databases/drivestat-db --old-bindir=/usr/pgsql-9.2/bin/ --new-bindir=/usr/pgsql-9.3/bin/

# Configure postgresql.conf
[products] cp -av /srv0/enstore/databases/enstore-db/postgresql.conf /srv0/enstore/databases/enstore-db/postgresql.conf.original
[products] cp -av /srv0/enstore/databases/accounting-db/postgresql.conf /srv0/enstore/databases/accounting-db/postgresql.conf.original
[products] cp -av /srv0/enstore/databases/drivestat-db/postgresql.conf /srv0/enstore/databases/drivestat-db/postgresql.conf.original

[products] echo "include_dir 'conf.d'" >> /srv0/enstore/databases/enstore-db/postgresql.conf
[products] echo "include_dir 'conf.d'" >> /srv0/enstore/databases/accounting-db/postgresql.conf
[products] echo "include_dir 'conf.d'" >> /srv0/enstore/databases/drivestat-db/postgresql.conf

[products] chmod a-w /srv0/enstore/databases/enstore-db/postgresql.conf
[products] chmod a-w /srv0/enstore/databases/accounting-db/postgresql.conf
[products] chmod a-w /srv0/enstore/databases/drivestat-db/postgresql.conf

# Copy used config files from git
[products] cp -av /srv0/enstore/databases/enstore-db/pg_hba.conf /srv0/enstore/databases/enstore-db/pg_hba.conf.original
[products] cp -av /srv0/enstore/databases/accounting-db/pg_hba.conf /srv0/enstore/databases/accounting-db/pg_hba.conf.original
[products] cp -av /srv0/enstore/databases/drivestat-db/pg_hba.conf /srv0/enstore/databases/drivestat-db/pg_hba.conf.original

[user@localhost] cd ~/workspace/enstore-config
[user@localhost] git checkout cdfen
[user@localhost] git pull
[user@localhost] cd ./database/

[user@localhost] rsync -av enstoredb/* root@cdfensrv0n:/srv0/enstore/databases/enstore-db/
[user@localhost] rsync -av accounting/* root@cdfensrv0n:/srv0/enstore/databases/accounting-db/
[user@localhost] rsync -av drivestat/* root@cdfensrv0n:/srv0/enstore/databases/drivestat-db/

[root] chown -v products:products /srv0/enstore/databases/enstore-db/pg_hba.conf
[root] chown -v products:products /srv0/enstore/databases/accounting-db/pg_hba.conf
[root] chown -v products:products /srv0/enstore/databases/drivestat-db/pg_hba.conf

[root] chown -Rv products:products /srv0/enstore/databases/enstore-db/conf.d
[root] chown -Rv products:products /srv0/enstore/databases/accounting-db/conf.d
[root] chown -Rv products:products /srv0/enstore/databases/drivestat-db/conf.d

[products] less /srv0/enstore/databases/enstore-db/pg_hba.conf
[products] less /srv0/enstore/databases/accounting-db/pg_hba.conf
[products] less /srv0/enstore/databases/drivestat-db/pg_hba.conf

[products] more /srv0/enstore/databases/enstore-db/conf.d/* | column
[products] more /srv0/enstore/databases/accounting-db/conf.d/* | column
[products] more /srv0/enstore/databases/drivestat-db/conf.d/* | column

# Start enstore and databases
[root] /etc/init.d/enstore-boot start

# Confirm startup
[products] ps aux | grep [p]ostmaster
products  5962  0.0  0.1 2306520 85032 ?       S    Feb10   0:26 /usr/bin/postmaster -p 8888 -D /srv0/enstore/databases/enstore-db -i
products  5978  0.0  0.3 8745480 232544 ?      S    Feb10   0:42 /usr/bin/postmaster -p 8800 -D /srv0/enstore/databases/accounting-db -i
products  5994  0.0  0.1 2306520 85024 ?       S    Feb10   0:58 /usr/bin/postmaster -p 8802 -D /srv0/enstore/databases/drivestat-db -i

# Check startup logs
[products] ls -tr /srv0/enstore/databases/enstore-db/pg_log/* | tail -1 | xargs tail -vn +1
[products] ls -tr /srv0/enstore/databases/accounting-db/pg_log/* | tail -1 | xargs tail -vn +1
[products] ls -tr /srv0/enstore/databases/drivestat-db/pg_log/* | tail -1 | xargs tail -vn +1

# Confirm version
[products] /usr/pgsql-9.3/bin/psql -qt -p 8888 -d postgres -c 'SHOW server_version;'
 9.3.6
[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

# Confirm databases
[products] /usr/pgsql-9.3/bin/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

[products] /usr/pgsql-9.3/bin/psql -qt -p 8800 -d postgres -c '\l'
 accounting | 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

[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 | =c/products          +
           |          |          |             |             | products=CTc/products

# Analyze
[products] sed -i 's/--analyze-only/--analyze-only --port=8888/g' /tmp/pg_upgrade_run/enstore/analyze_new_cluster.sh
[products] sed -i 's/--analyze-only/--analyze-only --port=8800/g' /tmp/pg_upgrade_run/accounting/analyze_new_cluster.sh
[products] sed -i 's/--analyze-only/--analyze-only --port=8802/g' /tmp/pg_upgrade_run/drivestat/analyze_new_cluster.sh

[products] grep port= /tmp/pg_upgrade_run/*/analyze_new_cluster.sh

[products] time /tmp/pg_upgrade_run/enstore/analyze_new_cluster.sh
[products] time /tmp/pg_upgrade_run/accounting/analyze_new_cluster.sh
[products] time /tmp/pg_upgrade_run/drivestat/analyze_new_cluster.sh

# Cleanup
[root] yum remove pgdg-sl92 postgresql92 postgresql92-libs
[products] rm -rf /tmp/pg_upgrade_run
[root] rm -rf /srv0/enstore/databases/enstore-db-9.2
[root] rm -rf /srv0/enstore/databases/accounting-db-9.2
[root] rm -rf /srv0/enstore/databases/drivestat-db-9.2