The guide is primarily targeted for external PostgreSQL installation. Embedded PostgreSQL upgrade is included in Dispatcher Paragon server installer.

Requirements

It's assumed that the default PostgreSQL binary folder is: "<install_dir>\Management\PGSQL"  and PostgreSQL data folder is: "<install_dir>\Management\PGSQL-data", same as default installation of Dispatcher Paragon Management Server with embedded PostgreSQL database. Please change these folders according to your deployment options.

  1. Download PostgreSQL 16 binaries from https://www.enterprisedb.com/download-postgresql-binaries. Select PostgreSQL 16 version according to your operation system.
  2. Backup PostgreSQL 11 data folder "<install_dir>\Management\PGSQL-data".

A Step-by-step Guide

Follow next steps to upgrade PostgreSQL installation to version 16.

  1. Stop PostgreSQL service and all dependent services.

  2. Copy downloaded PostgreSQL 16 binaries into the new folder, at example "<install_dir>\Management\PGSQL_16".

  3. Create postgres_password.txt file in current directory and put postgresql user password into the file.

  4. Initialize new data folder (such as <install_dir>\Management\PGSQL_16-data), run initdb: 

    <install_dir>\Management\PGSQL_16\bin\initdb -D "c:/DispatcherParagon/Management/PGSQL_16-data/" -E utf-8 --auth=md5 -U "postgres" --pwfile="postgres_password.txt"

  5. Upgrade PostgreSQL data folder. Before executing pg_upgrade, make sure that the Administrator account has granted Full control permission for both old PGSQL and PGSQL-data directories and new PGSQL_16 and PGSQL_16-data directories.

    <install_dir>\Management\PGSQL_16\bin\pg_upgrade -U postgres -b "<install_dir>\Management\PGSQL/bin" -B "<install_dir>\Management\PGSQL_16/bin" -d "<install_dir>\Management\PGSQL-data" -D "<install_dir>\Management\PGSQL_16-data"

  6. Change configuration in postgresql.conf which you required. List of changes to default configuration by default installation of Dispatcher Paragon Management Server:

    listen_addresses = '*'
    port = 5433
    max_connections = 120
    shared_buffers = 512MB
    max_prepared_transactions = 20
    work_mem = 128MB
    maintenance_work_mem = 512MB
    effective_cache_size = 1024MB
    logging_collector = on
    log_directory = 'pg_log'
    autovacuum_naptime = 43min
    datestyle = 'iso, dmy'
    default_text_search_config = 'pg_catalog.simple'

  7. Test if PostgreSQL will start correctly: 

    "<install_dir>\Management\PGSQL_16\bin\pg_ctl.exe" start -D "<install_dir>\Management\PGSQL_16-data" -w

    and stop the service after check: 

    "<install_dir>\Management\PGSQL_16\bin\pg_ctl.exe" stop -D "<install_dir>\Management\PGSQL_16-data" -w

  8. Remove old PostgreSQL 11 folders: "<install_dir>\Management\PGSQL" and "<install_dir>\Management\PGSQL-data".

  9. Rename "<install_dir>\Management\PGSQL_16" to "<install_dir>\Management\PGSQL" and "<install_dir>\Management\PGSQL_16-data" to "<install_dir>\Management\PGSQL-data".

  10. Delete postgres_password.txt file.

  11. Start PostgreSQL service and all dependent services.

Troubleshooting

pg_upgrade fails with could not write to log file or could not connect to server errors

pg_upgrade can fail with could not write to log file error or that source database could not be started, see screenshots below:


Administrator account should have Full control permission set for both old PGSQL and PGSQL-data directories and new PGSQL_16 and PGSQL_16-data directories. On each directory: right click and select Properties → switch to Security tab → click Advanced → on Permissions tab → click Add 

click on Select a principal link → fill in Administrator account (in our example it is RND0171\Administrator, this might differ on production environment) → click OK. 


Check Full control checkbox → click OK

pg_upgrade fails with fe_sendauth no password supplied error message

Set PGPASSWORD environment variable to postgres' user password (previously provided via postgres_password.txt file.

cmd
set PGPASSWORD=<password>
powershell
$ENV:PGPASSWORD="<password>"


ALTERNATIVELY, temporarily change METHOD value from md5 (or whatever value you find there) to trust in pg_hba.conf for both PGSQL-data and PGSQL_16-data directories.

pg_hba.conf
host all all 0.0.0.0/0 trust

For easy migration do this for all records in both pg_hba.conf files, but do not forget to change it back after pg_upgrade successfully finishes.


If there will be some other problem during upgrade phase which you would not be able to solve you could provide complete new installation of PostgreSQL by installer(https://www.enterprisedb.com/downloads/postgres-postgresql-downloads). You will need to backup all necessary databases from old PostgreSQL version and restore databases into new PostgreSQL 16 installation.