Upgrade PostgreSQL 13 to 17
search cancel

Upgrade PostgreSQL 13 to 17

book

Article ID: 410074

calendar_today

Updated On:

Products

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

Issue/Introduction

You can find official documentation how to do upgrade of PostgreSQL website . Following procedure of upgrade is based on mentioned document.

Environment

  • DX APM 10.8.*
  • PostgreSQL

Resolution

Table of Contents

Linux

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

 

1. check that PG 13 is running

sudo systemctl status postgresql-13

or

./pg_ctl status

 

2. remove adminpack extension from postgres 13 database
Postgres DB extension adminpack was removed in PG 14.x and newer, so it is necessary to remove the extension from the DB before upgrade.
note: you will need password of postgres db user in psql command

su - postgres
cd /usr/pgsql-13/bin
./psql -U postgres postgres -c "DROP EXTENSION adminpack CASCADE;"

 

3. stop postgres 13 database

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

or
./pg_ctl stop
./pg_ctl status

 

4. install postgres 17 database

note: see postgresql website  for you correct URLs based on your distribution

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

NOTE: If you find that version 16 is the latest available version in your OS:  sudo dnf module list postgresql

a) Copy manually the packages from /pub/repos/yum/17/redhat/rhel-8-x86_64/
            postgresql17-libs-17.1-1PGDG.rhel8.x86_64.rpm
            postgresql17-17.1-1PGDG.rhel8.x86_64.rpm
            postgresql17-server-17.1-1PGDG.rhel8.x86_64.rpm
            postgresql17-contrib-17.1-1PGDG.rhel8.x86_64.rpm

b) sudo dnf install ./postgresql17-libs-17.1-1PGDG.rhel8.x86_64.rpm ./postgresql17-17.1-1PGDG.rhel8.x86_64.rpm ./postgresql17-server-17.1-1PGDG.rhel8.x86_64.rpm ./postgresql17-contrib-17.1-1PGDG.rhel8.x86_64.rpm

c) Ensure the postgres env variables have been updated in .bash_profile and ensure the paths are correct and valid, for example:

export PGDATA=/var/lib/pgsql/17/data
export PGPORT=5432
export PGLOG=/var/log/postgres/pgsql-17

d) Check postgres service is running: ps -ef | grep pg

5. as postgres user init DB of new PostgreSQL 17 (/usr/pgsql-17/ is location of new installed DB)

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

and try to start it:

sudo systemctl start postgresql-17
sudo systemctl status postgresql-17
sudo systemctl stop postgresql-17

OR

./pg_ctl start
./pg_ctl status
./pg_ctl stop

 

6. adjust authentication on old DB.

In /var/lib/pgsql/13/data/pg_hba.conf set "method" on "trust".

 

7. 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:

cd /var/lib/pgsql

/usr/pgsql-17/bin/pg_upgrade -b /usr/pgsql-13/bin -B /usr/pgsql-17/bin -d /var/lib/pgsql/13/data -D /var/lib/pgsql/17/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-17/bin/pg_upgrade -b /usr/pgsql-13/bin -B /usr/pgsql-17/bin -d /var/lib/pgsql/13/data -D /var/lib/pgsql/17/data

 

8. restore authentication on old DB.
In /var/lib/pgsql/13/data/pg_hba.conf set "method" back to "password".


9. update configuration of PostgresSQL 17

Replace pg_hba.conf and postgresql.conf in /var/lib/pgsql/17/data with same files from /var/lib/pgsql/13/data


10) start PostgreSQL 17

sudo systemctl start postgresql-17

or
./pg_ctl start

Windows

1. check that PG 13 is running

  • via windows services
    note: make sure, that correct user (i.e., postgres) is defined in windows service's LogOn tab

    sc query postgresql-x64-13

  • via cmd prompt
    open cmd console as postgres user 

    RUNAS /USER:postgres "CMD.EXE"

     

    run following commands in postgres user cmd prompt to check if PG 13 is running

    cd "C:\Program Files\PostgreSQL\13\bin"

    bin\pg_ctl.exe status -D data

2. remove adminpack extension from postgres 13 database
Postgres DB extension adminpack was removed in PG 14.x and newer, so it is necessary to remove the extension from the DB before upgrade.
note: you will need password of postgres db user in psql command

cd "C:\Program Files\PostgreSQL\13\bin"
psql -c "DROP EXTENSION adminpack CASCADE;" postgres postgres

 

3. stop postgres 13 database
NET STOP postgresql-x64-13

4. install postgres 17 database

  • Download installer of postgres, for example  17.6  
  • Run installer with following settings
    • Select Components - uncheck Stack Builder component
    • You will need password of postgres user

5. stop installed postgres 17 database

net stop postgresql-x64-17

6. adjust authentication on old (13) and new (17) DB.

  • edit C:\Program Files\PostgreSQL\13\data\pg_hba.conf 
    set method to "trust"
  • edit C:\Program Files\PostgreSQL\17\data\pg_hba.conf
    set method to "trust"

7. add user postgres for folders of Postgres 17

using windows Properties/Security dialog on folder C:\Program Files\PostgreSQL\17 

  • set full control permissions to user postgres
  • grant it to all all subfolders (Advanced, check Replace All child object permissions...)

8. run DB upgrade check

  • open cmd console as postgres user 

    RUNAS /USER:postgres "CMD.EXE"

  • run db upgrade check
    cd "C:\Program Files\PostgreSQL\17"
    bin\pg_upgrade.exe -b "C:\Program Files\PostgreSQL\13\bin" -B "C:\Program Files\PostgreSQL\17\bin" -d "C:\Program Files\PostgreSQL\13\data" -D "C:\Program Files\PostgreSQL\17\data" -c
  • run upgrade check
    cd "C:\Program Files\PostgreSQL\17"
    bin\pg_upgrade.exe -b "C:\Program Files\PostgreSQL\13\bin" -B "C:\Program Files\PostgreSQL\17\bin" -d "C:\Program Files\PostgreSQL\13\data" -D "C:\Program Files\PostgreSQL\17\data"

9. set method back to password  in C:\Program Files\PostgreSQL\13\data\pg_hba.conf 

10.  replace pg_hba.conf and postgresql.conf in C:\Program Files\PostgreSQL\17\data with same files from C:\Program Files\PostgreSQL\13\data

11. start posgres 17

note: make sure, that correct user (i.e., postgres) is defined in windows service's LogOn tab

NET START postgresql-x64-17

Additional Information

Powered by Wolken Software