How to upgrade PostgreSQL from version 9.4 to version 11

These guide is primary targeted for external PostgreSQL installation. Embedded PostgreSQL upgrade is included in YSoft SafeQ server installer.

Requirements

I assume default PostgreSQL binary folder is: "C:\SafeQ6\Management\PGSQL" and PostgreSQL data folder is: "C:\SafeQ6\Management\PGSQL-data", same as default installation of YSoft SafeQ Management Server with embedded PostgreSQL database. Please change these folders according to your deployment options.

  1. Download PostgreSQL 11 binaries from https://www.enterprisedb.com/download-postgresql-binaries. Select PostgreSQL 11 version according to your operation system.

  2. Backup PostgreSQL 9.4 data folder "C:\SafeQ6\Management\PGSQL-data".

A Step-by-step Guide

Follow next steps to upgrade PostgreSQL installation to version 11.

  1. Stop PostgreSQL service and all dependent services.

  2. Copy downloaded PostgreSQL 11 binaries into new folder, at example "C:\SafeQ6\Management\PGSQL_11".

  3. Create postgres_password.txt file in current directory and put postgresql user password into the file.
    images/download/attachments/164536177/image2019-7-15_13-45-3.png

  4. Initialize new data folder (such as C:\SafeQ6\Management\PGSQL_11-data), run initdb:

    c:\SafeQ6\Management\PGSQL_11\bin\initdb -D "c:/SafeQ6/Management/PGSQL_11-data/" -E utf-8 --auth=md5 -U "postgres" --pwfile="postgres_password.txt"

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

    C:\SafeQ6\Management\PGSQL_11\bin\pg_upgrade -U postgres -b "C:\SafeQ6\Management\PGSQL/bin" -B "C:\SafeQ6\Management\PGSQL_11/bin" -d "C:\SafeQ6\Management\PGSQL-data" -D "C:\SafeQ6\Management\PGSQL_11-data"

  6. Change configuration in postgresql.conf which you required. List of changes to default configuration by default installation of YSoft SafeQ 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:

    "C:\SafeQ6\Management\PGSQL_11\bin\pg_ctl.exe" start -D "C:\SafeQ6\Management\PGSQL_11-data" -w

    and stop the service after check:

    "C:\SafeQ6\Management\PGSQL_11\bin\pg_ctl.exe" stop -D "C:\SafeQ6\Management\PGSQL_11-data" -w

  8. Remove old PostgreSQL 9.4 folders: "C:\SafeQ6\Management\PGSQL" and "C:\SafeQ6\Management\PGSQL-data".

  9. Rename "C:\SafeQ6\Management\PGSQL_11" to "C:\SafeQ6\Management\PGSQL" and "C:\SafeQ6\Management\PGSQL_11-data" to "C:\SafeQ6\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:

images/download/attachments/164536177/image2019-9-5_10-16-59.png


images/download/attachments/164536177/image2019-9-5_10-19-28.png

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

images/download/attachments/164536177/scr3.png

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.


images/download/attachments/164536177/scr4-permission.png

Check Full control checkbox → click OK

images/download/attachments/164536177/scr5-permission-after.png

pg_upgrade fails with fe_sendauth no password supplied error message

images/download/attachments/164536177/image2019-9-11_12-29-37.png

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_11-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 11 installation.