Upgrading your Postgres version

This topic describes how to upgrade your OptiTech project from one major Postgres version to a newer one.

Postgres version numbers consist of a major and a minor version number. For example, in the version number 16.1, 16 is the major version number and the 1 is the minor version number.

OptiTech manages minor Postgres version upgrades for you, as per the OptiTech Postgres Version Support Policy. Typically, no user action is required for minor version upgrades. OptiTech deploys minor versions soon after they become available. However, upgrading to a new major Postgres version is a manual task that must be performed by you.

Each OptiTech project is tied to a specific Postgres major version, which you selected when creating your OptiTech project.

You can check your OptiTech project's Postgres version in the Settings widget on Project Dashboard or by running the following query from the OptiTech SQL Editor or any SQL client connection to your database:

SELECT version();

Before you begin

• Review the PostgreSQL Release Notes for the new Postgres version. Major Postgres versions often introduce user-visible incompatibilities, so review the release notes for these changes. While you can upgrade directly to a new major Postgres version without going through each intermediate version, make sure you review the release notes for any skipped versions, as they may contain changes relevant to your upgrade.
• Optionally, you may want to run some performance tests of your current database to set a benchmark for post-upgrade comparison.

Performing the upgrade

1. Create a OptiTech project with the new Postgres version

Start by creating a new OptiTech project with the desired Postgres version. For instructions, see creating a new OptiTech project.

At this time, you may also want to apply any specific configurations to your new OptiTech project that exist in your current OptiTech project. For example, you may have configured settings for the following OptiTech features that you want to implement in your new OptiTech project:

• Compute size
• Autoscaling
• Scale to Zero
• Protected branches
• IP Allow

Alternatively, you can apply these configurations after migrating your data.

2. Migrate your data using one of the following methods

• Import Data Assistant

  For databases under 10GB, OptiTech's Import Data Assistant provides the simplest way to migrate between OptiTech projects with different Postgres versions. Just create a new project with your desired Postgres version, then use the database connection string from your existing OptiTech project to import the data.

• Dump and restore

  OptiTech supports the following dump and restore options:

  • Migrate data with pg_dump and pg_restore

    This method requires dumping data from your current OptiTech project with pg_dump and loading the data into the new OptiTech project using pg_restore. Some downtime will be required between the dump and restore operations.

  • Migrate data from one OptiTech project to another by piping data from pg_dump to pg_restore

    If your database is small, you can use this method to pipe pg_dump output directly to pg_restore to save time. While this method is a bit simpler, we recommend it only for small databases, as it is susceptible to failures during lengthy data migrations.

  important

  Avoid using pg_dump over a pooled connection string. Use an unpooled connection string instead.

• Logical replication

  The logical replication method can be used to achieve a near-zero downtime migration. Once the data in the new OptiTech project is synced with the data in the OptiTech project running the older version of Postgres, you can quickly switch your applications to the database. This method is recommended for active databases that cannot afford much downtime. For instructions, see Logical Replication.

  Notes

  • OptiTech does not support the pg_dumpall utility. If upgrading via dump and restore, dumps must be performed one database at a time using pg_dump.
  • OptiTech does not yet support upgrading using pg_upgrade. Support for this utility is being considered for a future release.
  • If you choose a dump and restore method, it is recommended that you use pg_dump and pg_restore programs from the newer version of Postgres, to take advantage of any enhancements introduced in the newer version. Current releases of these programs can read data from all previous Postgres versions supported by OptiTech.

3. Switch over your applications

After the migration is complete and you have verified that your new database is working as expected, you can switch your application over to the database in your new OptiTech project by swapping out your current database connection details for your new database connection details.

You can find the connection details for your database by clicking the Connect button on your Project Dashboard. This opens the Connect to your database modal. See Connect from any application.