APM 10.x - How to manual upgrade PostgreSQL 9.6.2 to 13.4 and higher versions?

Products

• DX Application Performance Management
• CA Application Performance Management (APM / Wily / Introscope)

Issue/Introduction

The following procedure of Postgres upgrade is based on the official documentation https://www.postgresql.org/docs/13/pgupgrade.html.

Environment

DX APM 10.7 and 10.8

Resolution

You can download PostgreSQL 13 from https://www.postgresql.org/download/

Upgrade has been tested against version PostgreSQL13.4

NOTE: All paths in described procedure are illustrative and can vary in diff environment .

Table of Contents

• •  
• Installation steps for Red Hat Linux
• Installation steps for Windows

Installation steps for Red Hat Linux

Following installation steps are valid for Red Hat Linux, it can vary for other OS.

1)  install PostgreSQL 13 : 

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql13 postgresql13-server postgresql13-contrib

2)  stop previous PostgreSQL 9.6 :

sudo systemctl stop postgresql-9.6
sudo systemctl status postgresql-9.6

 

3)  as postgres user init DB of new PostgreSQL 13 (/usr/pgsql-13/ is location of new installed DB):

su - postgres
cd /usr/pgsql-13/bin
./initdb -D /var/lib/pgsql/13/data

 

 

and try to start it:

sudo systemctl start postgresql-13
sudo systemctl status postgresql-13
sudo systemctl stop postgresql-13

4)  adjust authentication on old DB. In /opt/PostgreSQL-9.6.2/data/pg_hba.conf set "method" on "trust".

 

5)  switch to postgres user and from some folder with right for postgres start upgrade (for example from /var/lib/pgsql/) :

su - postgres

first do check if DB clusters are compatible and upgrade is possible:

/usr/pgsql-13/bin/pg_upgrade -b /opt/PostgreSQL-9.6.2/bin -B /usr/pgsql-13/bin -d /opt/PostgreSQL-9.6.2/data -D /var/lib/pgsql/13/data -c

 

run upgrade itself (you can use option --link if you want just do hard links instead of copying files to the new cluster) :

/usr/pgsql-13/bin/pg_upgrade -b /opt/PostgreSQL-9.6.2/bin -B /usr/pgsql-13/bin -d /opt/PostgreSQL-9.6.2/data -D /var/lib/pgsql/13/data

6)  restore settings in /opt/PostgreSQL-9.6.2/data/pg_hba.conf by setting "method" back on "password"

7)  replace pg_hba.conf and postgresql.conf in /var/lib/pgsql/13/data with same files from /opt/PostgreSQL-9.6.2/data

8) start PostgreSQL 13 :

sudo systemctl start postgresql-13

 

 

Installation steps for Windows

1) download PostgreSQL 13.4 installer from : https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

2) stop previous PostgreSQL 9.6 Cmd:

NET STOP pgsql-9.6

3) go throw install process and install everything except Stack Builder

click through installation process, set DBUSER password same as for Administrator in previous PostgreSQL 9.6 DB

 

4) stop just installed PostgreSQL 13.4 :

NET STOP postgresql-x64-13

5) adjust authentication on old and new DB.

In C:\Program Files\CA APM\PostgreSQL-9.6.2\data\pg_hba.conf set "method" on "trust"

 

For new DB in C:\Program Files\PostgreSQL\13\data\pg_hba.conf set "method" on "trust"

 

6) add user postgres for folder C:\Program Files\PostgreSQL\13 and set full control permissions

 

We need to set rights down into subfolder data. In Advanced tab set permissions recursively for postgres user

 

7) login as postgres user with Postgres admin password:
RUNAS /USER:postgres "CMD.EXE"

8) check if rights on folder are correctly set by doing start/stop test of new PostgreSQL 13.4 DB:

 

9) run db_upgrade check from folder C:\Program Files\PostgreSQL\13 as postgres user:

bin\pg_upgrade.exe -b "C:\Program Files\CA APM\PostgreSQL-9.6.2\bin" -B "C:\Program Files\PostgreSQL\13\bin" -d "C:\Program Files\CA APM\PostgreSQL-9.6.2\data" -D "C:\Program Files\PostgreSQL\13\data" -c

 

10) if previous commad run successfully than run upgrade itself:

bin\pg_upgrade.exe -b "C:\Program Files\CA APM\PostgreSQL-9.6.2\bin" -B "C:\Program Files\PostgreSQL\13\bin" -d "C:\Program Files\CA APM\PostgreSQL-9.6.2\data" -D "C:\Program Files\PostgreSQL\13\data"

11) set set pg_hba.conf "method" back on "password" in old DB data folder

12) replace pg_hba.conf and postgresql.conf in C:\Program Files\PostgreSQL\13\data with same files from C:\Program Files\CA APM\PostgreSQL-9.6.2\data

13) start PostgreSQL 13.4 DB :