{"id":4058,"date":"2025-09-02T20:31:50","date_gmt":"2025-09-03T01:31:50","guid":{"rendered":"https:\/\/promincproductions.com\/blog\/?p=4058"},"modified":"2025-09-02T20:32:21","modified_gmt":"2025-09-03T01:32:21","slug":"update-postgres-with-data-migration","status":"publish","type":"post","link":"https:\/\/promincproductions.com\/blog\/update-postgres-with-data-migration\/","title":{"rendered":"Update Postgres with Data Migration"},"content":{"rendered":"

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.<\/p>\n\n\n

\n
\"QNAP<\/figure>\n<\/div>\n\n\n

Background: Why the Upgrade Didn’t Work Directly<\/h2>\n\n\n\n

I went to update version of Mattermost installed via Container Station on a QNAP NAS<\/a> 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.<\/p>\n\n\n\n

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<\/a>. 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.<\/p>\n\n\n\n

How to Correctly Upgrade Postgres Installed via Containers<\/h2>\n\n\n\n

Research indicates that there is a pg_upgrade<\/code> 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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Postgres Installed via Containers Upgrade Process<\/h2>\n\n\n\n

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

Connect to QNAP NAS via SSH<\/h3>\n\n\n\n

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\u2019t have SSH access to the QNAP server, follow the\u00a0QNAP SSH tutorial<\/a>\u00a0for how to setup access.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Expose Docker Command<\/h3>\n\n\n\n

On a QNAP NAS the docker<\/code> 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<\/code> 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<\/a>, but simply run this command once connected to the QNAP NAS via SSH.<\/p>\n\n\n\n

export PATH=$PATH\":\/share\/CACHEDEV1_DATA\/.qpkg\/container-station\/bin\/:\"<\/code><\/pre>\n\n\n\n
Start the Old Version of Postgres<\/h3>\n\n\n\n
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<\/code> status.<\/p>\n\n\n\n
Obtain the ID of the Old Postgres Container<\/h3>\n\n\n\n
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:<\/p>\n\n\n\n
docker ps -a | grep postgres<\/code><\/pre>\n\n\n\n
This command will produce output similar to this:<\/p>\n\n\n\n
123456abcdef  postgres:11.6  \"docker-entrypoint.s\u2026\"   2 months ago  Up 5 hours                                     postgresql-postgres-1\n<\/code><\/pre>\n\n\n\n
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<\/code>.  That’s a good clue of what entry matters here.  Also notice Up 5 hours<\/code>.  Up<\/code> means that the container server is running.  Bingo – that’s what we are looking for!  The first column – 123456abcdef<\/code> 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.<\/p>\n\n\n\n
Create a Backup Dump of the Database from the Old Postgres Server<\/h3>\n\n\n\n
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.<\/p>\n\n\n\n
First pick a destination \/ create a directory to save the data to.  <\/p>\n\n\n\n
## create a directory to save the data to\nmkdir -p Backups\/postgresql\/<date>\/\n\n## Execute the export\ndocker exec -it 123456abcdef pg_dump -U <postgres_username> -d <database_name> -c -C > Backups\/postgresql\/<date>\/postgres-v11-mattermost.dump<\/code><\/pre>\n\n\n\n
pg_dump<\/code> arguments:<\/p>\n\n\n\n