Site icon Brian Prom Blog

Update Postgres with Data Migration

Brian

A tutorial on how to upgrade Postgres containers installed on a QNAP NAS via Container Station 3. Some major version upgrades of Postgres aren’t directly possible as there are data structure and other critical changes that need to be handled via upgrade tools. This can cause additional steps and effort and complexity. It’s all possible to get around – as long as you know the correct sequence of steps.

Background: Why the Upgrade Didn’t Work Directly

I went to update version of Mattermost installed via Container Station on a QNAP NAS and ran into an issue. The version of the Postgres database server was too old, not supported by the new version of Mattermost. Thus triggering the need to update the Postgres version as well.

On the surface this didn’t seem like a big issue – just update the version of the container. Sure – that can be done. In fact I already have a whole guide on how to do this – Update Container Version in QNAP Container Station 3. But yet another issue ran into… I was upgrading from Postgres 11 to Postgres 17 isn’t directly possible. There were enough changes between these two versions that upgrade tools need to be run and so progress was halted here.

How to Correctly Upgrade Postgres Installed via Containers

Research indicates that there is a pg_upgrade utility for this purpose. However given that Postgres has been installed via Container Station (Docker containers) the upgrade doesn’t happen within the machine like a direct installation and thus this wasn’t a feasible option.

Instead an alternate approach is needed here. First the data from the older version needs to be exported and then imported into the new container. All in all, fairly straightforward – assuming you know the correct commands and procedures. Read on to see what worked for my upgrade.

Postgres Installed via Containers Upgrade Process

Follow these steps to upgrade major versions of Postgres installed via Container Station 3 on a QNAP NAS via a data export / import process.

Connect to QNAP NAS via SSH

This process will require SSH access to the QNAP NAS. SSH allows for command line (terminal) access directly to the server to run commands to process the upgrade. If you don’t have SSH access to the QNAP server, follow the QNAP SSH tutorial for how to setup access.

DISCLAIMER: SSH access does give you access to the server that if used incorrectly can cause damage to the system. Use at your own risk.

Expose Docker Command

On a QNAP NAS the docker command isn’t natively exposed which makes completing this task more difficult. For convenience you can expose this command to allow for ease of completing this project. Running this only exposes the docker command for this one SSH connection and will need to be re-run later on. More details on how to do this can be found at this blog post, but simply run this command once connected to the QNAP NAS via SSH.

export PATH=$PATH":/share/CACHEDEV1_DATA/.qpkg/container-station/bin/:"

Start the Old Version of Postgres

The current / old version of Postgres needs to be running so that the data can be exported from it. In most cases this is already happening. The easiest way to ensure the old Postgres server is running is using the QNAP web admin via Container Station. Find the Postgres Application / Container and ensure that it has the Running status.

Obtain the ID of the Old Postgres Container

Now is when it is required to connect to the QNAP NAS via SSH. The ID of the Postgres Docker container is needed to proceed. It can be obtained by running this command:

docker ps -a | grep postgres

This command will produce output similar to this:

123456abcdef  postgres:11.6  "docker-entrypoint.s…"   2 months ago  Up 5 hours                                     postgresql-postgres-1

There might be multiple rows depending on what containers have been installed on your server. Notice though the name and version of postgres:11.6. That’s a good clue of what entry matters here. Also notice Up 5 hours. Up means that the container server is running. Bingo – that’s what we are looking for! The first column – 123456abcdef is the ID we are after here. This is used in future commands to tell the command this is the container we want to act upon.

Create a Backup Dump of the Database from the Old Postgres Server

The data from the current/old Postgres database server needs to be saved out to a dump file. This will later be used to import into the server with the new version of Postgres.

First pick a destination / create a directory to save the data to. 

## create a directory to save the data to
mkdir -p Backups/postgresql/<date>/

## Execute the export
docker exec -it 123456abcdef pg_dump -U <postgres_username> -d <database_name> -c -C > Backups/postgresql/<date>/postgres-v11-mattermost.dump

pg_dump arguments:

The > then redirects the output of the pg_dump command to a file. Name the path and file accordingly to your needs.

[pg_dump reference]

Create an updated version of the Postgres Application

Moving back into Container Station 3 in the QNAP Web Admin it is time to create the Application for the updated Postgres server.

NOTE: It is best to keep the same username/password as the prior Postgres application so that Mattermost (or other services that use Postgres) can connect seamlessly. The URL/IP address and port will be the same – just the container running Postgres and the image are what will be different.

  1. Login to the QNAP Web Admin
  2. Open Container Station 3
  3. Click on Applications in the left hand menu
  4. Click the + Create button
  5. Enter an Application Name (example: postgresql-17_6)
  6. Enter the following YAML configuration code:
version: '3'

services:
  postgres:
    image: postgres:17.6
    restart: on-failure
    ports:
      - 127.0.0.1:5432:5432
    volumes:
      - /share/Container/postgresdb_17:/var/lib/postgresql/data
    environment:
      - POSTGRES_USER=<postgres_username>
      - POSTGRES_PASSWORD=<postgres_password>

  pgadmin4:
    image: dpage/pgadmin4:latest
    restart: on-failure
    ports:
      - 7070:80
      - 7443:443
    depends_on:
      - postgres     
    environment:
      - PGADMIN_DEFAULT_EMAIL<your_email_for_admin_account>
      - PGADMIN_DEFAULT_PASSWORD=<your_password_for_admin_account>
  1. Click the Create button
  2. Wait for the container to start

Notes:

Obtain the ID of the New Postgres Container

Just like above, the container ID for the new Postgres container is needed. We again run this command:

docker ps -a | grep postgres

Here again, the alpha-numeric ID from the first column is what is needed but for the new container – the container that is running of course.

123456abcdef  postgres:11.6  "docker-entrypoint.s…"   2 months ago  Exited (0) 15 hours ago                                   postgresql-postgres-1
987654zyxwvu  postgres:17.6  "docker-entrypoint.s…"   3 minutes ago  Up 3 minutes                    127.0.0.1:5432->5432/tcp   postgresql-17_6-postgres-1

Import the Data Dump from the Old Postgres Server Into the New Postgres Server

It’s now time to take the data dump from the old Postgres server and import it into the new Postgres server. Since the import is being run on the new version any data structure and collation changes needed will be processed successfully at this time.

docker exec -i 987654zyxwvu psql -U <postgres_username> < Backups/postgresql/<date>/postgres-v11-mattermost.dump

Technically at this point all should be good. The new server is up and running and should have the old data imported and restructured as needed. There are a few more steps suggested however to ensure that all is running as expected.

Open Postgres Command Line Interface

Run Postgres via the command line with this command.

docker exec -it 987654zyxwvu psql -U <postgres_username> -d mattermost

Confirm Mattermost Tables Exist

A quick sanity check that the data imported is to see if tables exist. This command to list the tables should return ~20+ tables.

\dt # list tables

Confirm Past Mattermost Posts Exist

This query will show the last 5 posts made in the chat history. This can be used as a sanity check that the data has been imported as expected.

SELECT * FROM posts ORDER BY createat DESC LIMIT 5;

Database Maintenance

Assuming that the checks above look as expected, it’s time to do a bit of maintenance functions and then be done with this upgrade.

Switch to the Postgres Database

The mattermost database used above holds the data for the application. There is also a database that holds some of the root Postgres data – that database is named postgres. Switch to that database with the following command:

\c postgres

Upon switching to the postgres database there might be an error displayed such as:

WARNING: database "postgres" has a collation version mismatch
DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.41.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

The error message gives the clues as to how to resolve this hurdle. Run the query provided – ensure to add a ; at the end to tell the command line it is time to execute the command.

ALTER DATABASE postgres REFRESH COLLATION VERSION;

Reset Database User Password

In some cases the password for the database isn’t correct. This could be due to miss-configuration from the old to the new container. It could also be due to the encryption method of the password from the old to the new server changing.

ALTER USER <postgres_username> WITH PASSWORD '<postgres_password>';

Garbage Collection and Recollect Stats

With the data structure changing from the old to new version of Postgres, it’s good practice to run the VACUUM and ANALYZE commands. These clean up any dead space left behind by data manipulations and reset the stats tables in Postgres.

VACUUM ANALYZE;

Conclusion

Migrating between major versions of Postgres (ie. version 11 to version 17) using QNAP Container Station 3 (as well as Docker containers) is a straightforward and rather quick task assuming the correct steps are followed. The process involves exporting the desired data, shutting down the Docker container for the old version of Postgres, starting the Docker container for the new version of Postgres, and importing the data. Lastly a few Postgres maintenance tasks need to be run to ensure the transition is complete.

Exit mobile version