Skip to content

PostgreSQL Version Upgrades

Jump to bottom
Leonard Carcaramo edited this page Apr 15, 2021 · 1 revision

This guide will show you how to perform a Major Version Upgrade on your containerized PostgreSQL database. Additionally, the Vault team has adapted some existing code that automates the process of performing major version upgrades for PostgreSQL on x86 to work on zCX/s390x to make the process easier and more repeatable. The Vault team has also made enhancements to that code, and created additional shell scripts to further automate the upgrade process.

Before you begin, ensure that you have the following:

  • A PostgreSQL database that has been initialized with a major version less than the latest version
  • Your PostgreSQL database should live in a Docker volume.
  • Have the PostgreSQL image that corresponds to the current version of your PostgreSQL database in your local zCX image registry. docker pull icr.io/ibmz/postgres:<old major version>
  • Have the PostgreSQL image that corresponds the version of PostgreSQL that you want to upgrade your PostgreSQL database to in your local zCX image registry. docker pull icr.io/ibmz/postgres:<new major version>

Note that this guide assumes that the Docker Volume that contains your database is the contents of your PostgreSQL data directory. /var/lib/postgresql/data

Also, note that this guide assumes that you are using your PostgreSQL database as part of your Artifactory solution.

 

Get The s390x Dockerfile/scripts

  • Get the zCX/s390x Dockerfile/scripts here. (You may need to use FTP to get them on to your zCX instance)

FTP:

$ cd <path to place where you downloaded these files to your workstation>
$ ls
build/  perform-db-upgrade.sh*  README.md  test-perform-db-upgrade.sh*
$ sftp -o Port=8022 <your username>@<host/ip of your zCX instance>
<your username>@<host/ip of your zCX instance>'s password:
Connected to <your username>@<host/ip of your zCX instance>.
s    cd <wherever you want to put the files on zCX>
s    put *
...
<output that indicates that files are being copied to your zCX instance>
...
s    exit

$

 

Build The pg_upgrade Image

  • Replace all <placeholders> in the Dockerfile and build.sh with the values needed to create an image that upgrades from the desired old database version to the desired new database version.

  • Build the pg_upgrade image.

$ ./build.sh
  • You should see the pg_upgrade image in your local image registry when the build is finished.
$ docker images
REPOSITORY                        TAG                  IMAGE ID            CREATED             SIZE
pg_upgrade                        <OLD>-to-<NEW>       <image id>          1 minute ago        355MB
...

 

Create a Docker Volume for The Upgraded Database

In order to upgrade your PostgreSQL database, you will need to create a new Docker Volume because the upgrade procedure will need to copy the original database to a new Docker Volume so that it can perform the upgrade on the copy.

  • Create a Docker Volume.
$ docker volume create <upgraded database volume name>

 

Test The pg_upgrade Image

  • Fill all of the <placeholders> in test-perform-db-upgrade.sh with the values that correspond to the following:
    • zCX instance you are running the test on.
    • Port bindings.
    • Artifactory version to test with (Only tested with version 6.x)
    • PostgreSQL version you want to upgrade from.
    • PostgreSQL version you want to upgrade to.
  • Execute test-perform-db-upgrade.sh to verify that the pg_upgrade image is working properly.
$ ./test-perform-db-upgrade.sh

 

Perform Major Version Upgrade on Your PostgreSQL Database

  • Execute the perform-db-upgrade.sh script with the following arguments to perform the major version upgrade on your PostgreSQL database:

⚠️ Ensure that you fill all <placeholders> in this command!

$ ./perform-db-upgrade.sh <original database volume> <new database volume> <old database version> <new database version>

 

Verify That The Upgrade was Successful

  • Start a new PostgreSQL container. Ensure that you are replacing the database volume in the command with the one you upgraded, and that you are using the PostgreSQL image that corresponds the version of PostgreSQL that you upgraded to.
$ docker run <same command you have been using except for the volume used and the postgres image version used>`
  • Verify that the container started successfully:
$ docker logs <new postgresql container name>
PostgreSQL Database directory appears to contain a database; Skipping initialization
2020-10-20 14:36:28.962 UTC [1] LOG:  starting PostgreSQL <version info> on s390x-ibm-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-10-20 14:36:28.963 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-10-20 14:36:28.963 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2020-10-20 14:36:28.978 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-10-20 14:36:28.983 UTC [12] LOG:  database system was shut down at 2020-10-20 14:35:20 UTC
2020-10-20 14:36:28.988 UTC [1] LOG:  database system is ready to accept connections
  • Bash into the PostgreSQL container and make sure all of the Artifactory tables are there.
$ docker exec -it <new postgresql container name> bash
postgres@<container id>:/$ psql -U artifactory
psql (<version information>)
Type "help" for help.

artifactory=# \dt
                       List of relations
 Schema |              Name              | Type  |    Owner
--------+--------------------------------+-------+-------------
 public | access_configs                 | table | artifactory
 public | access_federation_log          | table | artifactory
 public | access_federation_servers      | table | artifactory
 public | access_groups                  | table | artifactory
 public | access_groups_custom_data      | table | artifactory
 public | access_master_key_status       | table | artifactory
 public | access_nodes                   | table | artifactory
 public | access_permission_action       | table | artifactory
 public | access_permissions             | table | artifactory
 public | access_permissions_custom_data | table | artifactory
 public | access_schema_version          | table | artifactory
 public | access_servers                 | table | artifactory
 public | access_tokens                  | table | artifactory
 public | access_topology                | table | artifactory
 public | access_unique_ids              | table | artifactory
 public | access_users                   | table | artifactory
 public | access_users_custom_data       | table | artifactory
 public | access_users_groups            | table | artifactory
 public | aces                           | table | artifactory
 public | acls                           | table | artifactory
 public | archive_names                  | table | artifactory
 public | archive_paths                  | table | artifactory
 public | artifact_bundles               | table | artifactory
 public | artifactory_servers            | table | artifactory
 public | binaries                       | table | artifactory
 public | binary_blobs                   | table | artifactory
 public | blob_infos                     | table | artifactory
 public | build_artifacts                | table | artifactory
 public | build_dependencies             | table | artifactory
 public | build_modules                  | table | artifactory
 public | build_promotions               | table | artifactory
 public | build_props                    | table | artifactory
 public | builds                         | table | artifactory
 public | bundle_blobs                   | table | artifactory
 public | bundle_files                   | table | artifactory
 public | configs                        | table | artifactory
 public | db_properties                  | table | artifactory
 public | distributed_locks              | table | artifactory
 public | groups                         | table | artifactory
 public | indexed_archives               | table | artifactory
 public | indexed_archives_entries       | table | artifactory
 public | jobs                           | table | artifactory
 public | master_key_status              | table | artifactory
 public | migration_status               | table | artifactory
 public | module_props                   | table | artifactory
 public | node_event_cursor              | table | artifactory
 public | node_event_priorities          | table | artifactory
 public | node_events                    | table | artifactory
 public | node_meta_infos                | table | artifactory
 public | node_props                     | table | artifactory
 public | nodes                          | table | artifactory
 public | permission_target_repos        | table | artifactory
 public | permission_targets             | table | artifactory
 public | replication_errors             | table | artifactory
 public | stats                          | table | artifactory
 public | stats_remote                   | table | artifactory
 public | tasks                          | table | artifactory
 public | trusted_keys                   | table | artifactory
 public | ui_session                     | table | artifactory
 public | ui_session_attributes          | table | artifactory
 public | unique_ids                     | table | artifactory
 public | user_props                     | table | artifactory
 public | users                          | table | artifactory
 public | users_groups                   | table | artifactory
 public | watches                        | table | artifactory
(65 rows)

artifactory=# quit
postgres@d1f5b9cb9c9f:/$ exit
exit
$
  • Start your Artifactory container to make sure that the database still works with Artifactory.
$ docker run <same exact docker run command that you have been using to start artifactory>
  • Verify that Artifactory started successfully.
$ docker logs <artifactory conatiner name>
...
###########################################################
### Artifactory successfully started (57.684 seconds)   ###
###########################################################
...

  • Verify that the Artifactory UI works correctly by logging in.

    • https://<host/ip where Artifactory is hosted>:<port>/artifactory/webapp/#/home
Clone this wiki locally