Page Comparison

forum discussion.

0. Set the path such that the older postgresql version is default.

[root@d_server pbspro]# psql -V
psql (PostgreSQL) 9.2.23
[root@d_server pbspro]# /usr/pgsql-9.3/bin/psql -V
psql (PostgreSQL) 9.3.23
[root@d_server pbspro]# cat $PBS_HOME/datastore/PG_VERSION
9.2

1. Stop PBS and verify if the dataservice has stopped

[root@d_server pbspro]# /etc/init.d/pbs stop
Stopping PBS
Shutting server down with qterm.
PBS server - was pid: 1792
PBS sched - was pid: 1499
PBS comm - was pid: 1493
Waiting for shutdown to complete
[root@d_server pbspro]# ps -elf | grep pbs
0 S root 2528 1 0 80 0 - 2264 pipe_w 06:53 ? 00:00:00 grep --color=auto pbs
[root@d_server pbspro]# ps -elf | grep post
0 S root 2530 1 0 80 0 - 2264 pipe_w 06:53 ? 00:00:00 grep --color=auto post
[root@d_server pbspro]#

2. Change pg_hba.conf in the existing DB to enable passwordless operations.

[root@d_server pbspro]# cp $PBS_HOME/datastore/pg_hba.conf $PBS_HOME/datastore/pg_hba.conf.orig
[root@d_server pbspro]# chown postgres $PBS_HOME/datastore/pg_hba.conf.orig
[root@d_server pbspro]# sed 's/md5/trust/g' $PBS_HOME/datastore/pg_hba.conf > $PBS_HOME/datastore/pg_hba.conf.new
[root@d_server pbspro]# mv -f $PBS_HOME/datastore/pg_hba.conf.new $PBS_HOME/datastore/pg_hba.conf
[root@d_server pbspro]#

3. Find the encoding and locale settings used in the existing cluster

[root@d_server pbspro]# /opt/pbs/sbin/pbs_dataservice start
Starting PBS Data Service..
[root@d_server pbspro]# enc_type=`psql -A -t -p 15007 -d pbs_datastore -U postgres -c "select pg_encoding_to_char(encoding) from pg_database where datname = 'pbs_datastore'"`
[root@d_server pbspro]# echo $enc_type
SQL_ASCII
[root@d_server pbspro]# lc_collate=`psql -A -t -p 15007 -d pbs_datastore -U postgres -c "SHOW LC_COLLATE"`
[root@d_server pbspro]# echo $lc_collate
C
[root@d_server pbspro]# lc_ctype=`psql -A -t -p 15007 -d pbs_datastore -U postgres -c "SHOW LC_COLLATE"`
[root@d_server pbspro]# echo $lc_ctype
C
[root@d_server pbspro]# /opt/pbs/sbin/pbs_dataservice stop
Stopping PBS Data Service..
waiting for server to shut down.... done
server stopped
[root@d_server pbspro]#

4. Rename the datastore folder

[root@d_server pbspro]# mv $PBS_HOME/datastore/ $PBS_HOME/datastore.old

5. Create the new datastore directory, set ownership and permissions

mkdir -p $PBS_HOME/datastore
chown postgres $PBS_HOME/datastore
chmod 700 $PBS_HOME/datastore
cd $PBS_HOME//datastore

6. Create the new PBS database cluster using the encoding and locale settings found in step 3.

[root@d_server datastore]# su postgres -c "/usr/pgsql-9.3/bin/initdb -D /var/spool/pbs/datastore -U postgres -E SQL_ASCII --lc-collate=C --lc-ctype=C"
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/spool/pbs/datastore ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
creating configuration files ... ok
creating template1 database in /var/spool/pbs/datastore/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

/usr/pgsql-9.3/bin/postgres -D /var/spool/pbs/datastore
or
/usr/pgsql-9.3/bin/pg_ctl -D /var/spool/pbs/datastore -l logfile start

[root@d_server datastore]#

7. Change postgresql.conf in the new cluster to use settings below.
checkpoint_segments = 20
port = 15007
listen_addresses = '*'
standard_conforming_strings = on
standard_conforming_strings = on
logging_collector = on
log_directory = 'pg_log'
log_filename = 'pbs_dataservice_log.%a'
log_truncate_on_rotation = on
log_rotation_age = 1440
log_line_prefix = '%t'

8. check if we can upgrade the cluster or not, using pg_upgrade -c from the newer version of postgresql

[root@d_server datastore]# su postgres -c "/usr/pgsql-9.3/bin/pg_upgrade -d /var/spool/pbs/datastore.old/ -D /var/spool/pbs/datastore -b /usr/bin -B /usr/pgsql-9.3/bin -p 15007 -P 15007 -c"
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is a superuser ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok

*Clusters are compatible*
[root@d_server datastore]#

9. If pg_upgrade -c reports clusters are compatible, perform pg_upgrade for real this time. If not, try resolving the incompatabilities reported. One such incompatibility is explained at the end of this document.

[root@d_server datastore]# su postgres -c "/usr/pgsql-9.3/bin/pg_upgrade -d /var/spool/pbs/datastore.old/ -D /var/spool/pbs/datastore -b /usr/bin -B /usr/pgsql-9.3/bin -p 15007 -P 15007"
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is a superuser ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows on the new cluster ok
Deleting files from new pg_clog ok
Copying old pg_clog to new server ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Setting oldest multixact ID on new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Adding support functions to new cluster ok
Restoring database schemas in the new cluster
ok
Setting minmxid counter in new cluster ok
Removing support functions from new cluster ok
Copying user relation files
ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to analyze new cluster ok
Creating script to delete old cluster ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
delete_old_cluster.sh
[root@d_server datastore]#

10. Set the path to use newer postgresql version to be default.

[root@d_server datastore]# export PATH=/usr/pgsql-9.3/bin/:$PATH
[root@d_server datastore]#

11. Start the PBS dataservice.

[root@d_server datastore]# /opt/pbs/sbin/pbs_dataservice start
Starting PBS Data Service..
[root@d_server datastore]#

12. Set PGPORT environment variable to use 15007 as the port.

[root@d_server datastore]# export PGPORT=15007
[root@d_server datastore]#

13. Analyze the new cluster

[root@d_server datastore]# su postgres -c "./analyze_new_cluster.sh"
This script will generate minimal optimizer statistics rapidly
so your system is usable, and then gather statistics twice more
with increasing accuracy. When it is done, your system will
have the default level of optimizer statistics.

If you have used ALTER TABLE to modify the statistics target for
any tables, you might want to remove them and restore them after
running this script because they will delay fast statistics generation.

If you would like default statistics as quickly as possible, cancel
this script and run:
"/usr/pgsql-9.3/bin/vacuumdb" --all --analyze-only

Generating minimal optimizer statistics (1 target)
--------------------------------------------------
vacuumdb: vacuuming database "pbs_datastore"
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"

The server is now available with minimal optimizer statistics.
Query performance will be optimal once this script completes.

Generating medium optimizer statistics (10 targets)
---------------------------------------------------
vacuumdb: vacuuming database "pbs_datastore"
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"

Generating default (full) optimizer statistics (100 targets?)
-------------------------------------------------------------
vacuumdb: vacuuming database "pbs_datastore"
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"

Done
[root@d_server datastore]#

14. Stop the PBS dataservice.

[root@d_server datastore]# /opt/pbs/sbin/pbs_dataservice stop
Stopping PBS Data Service..
waiting for server to shut down..... done
server stopped
[root@d_server datastore]#

15. Reset the PBS datauser password.

[root@d_server datastore]# /opt/pbs/sbin/pbs_ds_password -r
---> Updated user password
---> Success
[root@d_server datastore]#

16. Copy pg_hba.conf from the old cluster to the new cluster and revoke the passwordless settings.

[root@d_server datastore]# cp $PBS_HOME/datastore.old/pg_hba.conf $PBS_HOME/datastore/pg_hba.conf
[root@d_server datastore]#

17. Start PBS

[root@d_server datastore]# /etc/init.d/pbs restart
Restarting PBS
Stopping PBS
Killing Server.
PBS server - was pid: 4572
PBS sched - was pid: 4064
PBS comm - was pid: 4049
Waiting for shutdown to complete
Starting PBS
/opt/pbs/sbin/pbs_comm ready (pid=5500), Proxy Name:d_server:17001, Threads:4
PBS comm
Creating usage database for fairshare.
PBS sched
Connecting to PBS dataservice....connected to PBS dataservice@d_server
Licenses valid for 10000000 Floating hosts
PBS server
[root@d_server datastore]#

18. Verify the PG_VERSION in the new datastore

[root@d_server datastore]# cat $PBS_HOME/datastore/PG_VERSION
9.3
[root@d_server datastore]#

19. If needed, remove the older postgresql.
[root@d_server datastore]# yum remove postgresql-server-9.2.23-3.el7_4.x86_64 postgresql-9.2.23-3.el7_4.x86_64 postgresql-libs-9.2.23-3.el7_4.x86_64
Loaded plugins: fastestmirror, ovl

...

...

...

Removed:
postgresql.x86_64 0:9.2.23-3.el7_4 postgresql-libs.x86_64 0:9.2.23-3.el7_4 postgresql-server.x86_64 0:9.2.23-3.el7_4

Complete!

20. Restart PBS
[root@d_server datastore]# psql -V
psql (PostgreSQL) 9.3.23
[root@d_server datastore]# /etc/init.d/pbs restart
Restarting PBS
Stopping PBS
Shutting server down with qterm.
PBS server - was pid: 5826
PBS sched - was pid: 5515
PBS comm - was pid: 5500
Waiting for shutdown to complete
Starting PBS
/opt/pbs/sbin/pbs_comm ready (pid=6189), Proxy Name:d_server:17001, Threads:4
PBS comm
Creating usage database for fairshare.
PBS sched
Connecting to PBS dataservice....connected to PBS dataservice@d_server
Licenses valid for 10000000 Floating hosts
PBS server
[root@d_server datastore]#

21. Remove the old cluster.

[root@d_server datastore]# ./delete_old_cluster.sh
[root@d_server datastore]#

Miscellaneous

Resolution of the issue reported by pg_upgrade while upgrading from 9.2.23 to 9.3.23 and it's resolution

[root@d_server datastore]# su postgres -c "/usr/pgsql-9.3/bin/pg_upgrade -d /var/spool/pbs/datastore.old/ -D /var/spool/pbs/datastore -b /usr/bin -B /usr/pgsql-9.3/bin -p 15007 -P 15007 -c"
Performing Consistency Checks
-----------------------------
Checking cluster versions ok

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/spool/pbs/datastore/.s.PGSQL.15007"?

...

[root@d_server datastore]# mv -f /usr/bin/pg_ctl{,-orig}
[root@d_server datastore]# echo '#!/bin/bash' > /usr/bin/pg_ctl
[root@d_server datastore]# echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' >> /usr/bin/pg_ctl
[root@d_server datastore]# chmod +x /usr/bin/pg_ctl
[root@d_server datastore]# su postgres -c "/usr/pgsql-9.3/bin/pg_upgrade -d /var/spool/pbs/datastore.old/ -D /var/spool/pbs/datastore -b /usr/bin -B /usr/pgsql-9.3/bin -p 15007 -P 15007 -c"
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is a superuser ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok

...

How to upgrade PBS datastore when PostgreSQL is upgraded.

If PostgreSQL is upgraded, PBS datastore would also need to be upgraded for PBS to work without errors. PBS datastore upgrades are tested to work well when the upgrades are performed using the pg_upgrade tool provided by PostgreSQL that allows database updates between major versions without data dump/reload.

While the steps needed for an upgrade using pg_upgrade do not change, it is advisable to consult the documentation for the newer version of PostgreSQL before performing the upgrade.

Points to remember while upgrading PBS database -

1) PBS datastore location - $PBS_HOME/datastore

2) default port used by PBS dataservice – 15007

3) Once the upgrade is successful, use pbs_ds_password to reset the PBS data user password.

As an example, logs from a database upgrade session is attached to this page.

Disclaimer:

The logs in the example provided are for the postresql upgrade from 9.2 to 9.3 on a centos7 machine. It can be used only as a reference and there is no guarantee that exact steps will work in a similar configuration.