Updating Postgres Versions for Retool on Docker

💬 Self Hosted Retool
1

Hi all,

Our current deployment of Retool is using Postgres 9.6 and we want to upgrade to 14.3.
We used docker to install Retool on our VM.

We are running into a lot of issues, so I was wondering if there is a retool specific way to upgrade postgres. This has been a frustrating endeavor.

-Bob

2

Took some effort and a lot of reverting, but we got there.

3

@dlbhilbig Can you give any details? Currently migrating from Postgres 11 to 16.

4

Managed to perform the upgrade. We're using Azure Managed Postgres. Someone mentioned using DB snapshots, but I wasn't sure that would work based on some of the issues we saw. Ended up using the following steps:

  • Create empty hammerhead_test database in new server
  • Add to following extensions in the server parameters in Azure Portal
    • PGCRYPTO
    • UUID-OSSP
  • Shut down test-retool cluster
  • Change connection string to new empty database
  • Start test-retool cluster and wait for all migrations to complete (view logs). This can take a while.
  • Shut down test-retool cluster again
  • Shut down production retool cluster
  • Use instructions at Configure and migrate to an external database | Retool Docs
    #+begin_src sh
    pg_dump --no-acl --no-owner --clean --host=<oldDB>.postgres.database.azure.com --dbname=hammerhead_production --username=<username> --file=retool_db_dump.sql;
    #+end_src
  • Restart prod retool cluster
    #+begin_src sh
    psql "postgres://<username>:<password>@<newDB>.postgres.database.azure.com:5432/hammerhead_test?sslmode=require" --file=retool_db_dump.sql;
    #+end_src
  • Start test-retool cluster
  • Test that things work
  • Shut down test-retool cluster again
  • Copy hammerhead_test to hammerhead_production
    #+begin_src sql
    DROP DATABASE IF EXISTS hammerhead_production;
    CREATE DATABASE hammerhead_production WITH TEMPLATE hammerhead_test;
    #+end_src
  • Shut down production retool cluster again
  • Change connection string
  • Restart production retool cluster
1 Like