Upgrade the database major version by migrating data

This page describes how to migrate your data to an instance running a later version of PostgreSQL. Before proceeding with this method, consider upgrading the database major version in-place.

There are two ways to upgrade the database major version of your Cloud SQL for PostgreSQL instance by migrating your data.

  • Option 1. Use the Database Migration Service (DMS). DMS supports migrating from a Cloud SQL for PostgreSQL instance. You can also use it to upgrade your database version.
  • Option 2. Move your data from one version of PostgreSQL to another by exporting data from the current instance and importing that data into a new instance running the later version of PostgreSQL. This process involves downtime; you put the current instance into read-only mode before starting the export.

The rest of this page discusses the second option.

Plan a major version upgrade

  1. Choose a target major version.

    See the list of versions that Cloud SQL supports.

  2. Consider the features offered in each PostgreSQL version and address incompatibilities.

    New major versions introduce incompatible changes that might require you to modify the application code, the schema, or the database settings before you can upgrade your database instance. Review the release notes of your target major version to determine the incompatibilities that you need to address. If you're skipping major versions, address the incompatibilities listed for each version that you're skipping.

  3. Test the upgrade with a dry run.

    Perform a dry run of the end-to-end upgrade process with a test instance before you upgrade the production database. You might want to test the upgrade using a clone instead of your production database to avoid any performance impact from the export on your production workload.

    In addition to validating that the upgrade completes successfully, run tests to understand the expected downtime of the upgrade, confirm your upgrade workflow, and ensure that the application behaves as expected on the upgraded database. If you haven't enabled automatic storage increases, take note of the disk storage used by the upgraded dry run instance to determine whether you need to increase the storage capacity for the production instance before upgrading.

  4. Decide when you want to upgrade.

Migrate your data

Migrating entails using the pg_dump command to export the data from the source instance and the pg_restore command to import the data into the target instance.

To migrate your data to a Cloud SQL instance running a more recent database version, follow these steps:

  1. Create the target instance with the desired PostgreSQL version.

    Make sure the target instance has:

    • Sufficient storage to hold all of the current instance's data.
    • The same authorized networks as the current instance. Learn more.
    • The same user accounts, with the same PostgreSQL privileges and passwords.

    For more information, see Creating an instance.

  2. Confirm that you can connect to the new instance with your local PostgreSQL tools and update them, if necessary.

  3. Export the current instance's data to a PostgreSQL dump file, following the instructions in Exporting data for Import into Cloud SQL.

    Do not export the postgres system database. The postgres database is the default database you connect to before you have created any other databases. Once you create another database, switch to it in order to create tables and insert data. Don't use the postgres database for your application's data.

  4. Create a Cloud Storage bucket if needed, and upload your SQL dump file to the bucket.

  5. Import the data to the target instance, following the instructions in Importing PostgreSQL databases from Cloud Storage.

  6. Optional. After completing the upgrade process, set up replication between the source and target instances using pglogical to update the target with all changes that have occurred since the initial dump was started. Once the two instances are in sync, you can promote the target instance.

    For information about using pglogical to implement continuous replication, see Configure your source.

  7. Update your applications to connect to the new instance.

  8. When you're confident that your new instance is operating successfully, delete the old instance.

What's next