Performing a major version upgrade of Postgres on BigAnimal
Using logical replication
Note
This procedure doesn't work with distributed high-availability BigAnimal instances.
Logical replication is a common method for upgrading the Postgres major version on BigAnimal instances, enabling a transition with minimal downtime.
By replicating changes in real time from an older version (source instance) to a newer one (target instance), this method provides a reliable upgrade path while maintaining database availability.
Important
Depending on where your older and newer versioned BigAnimal instances are located, this procedure can accrue ingress and egress costs from your cloud service provider (CSP) for the migrated data. Consult your CSP's pricing documentation to see how ingress and egress fees are calculated to determine any extra costs.
Overview of upgrading
To perform a major version upgrade:
- Create a BigAnimal instance.
- Gather instance information.
- Confirm the Postgres versions before migration.
- Migrate the database schema.
- Create a publication.
- Create a logical replication slot.
- Create a subscription.
- Validate the migration.
Create a BigAnimal instance
To perform a major version upgrade, create a BigAnimal instance with your desired version of Postgres. This is your target instance.
Ensure your target instance is provisioned with a storage size equal to or greater than your source instance.
For details on creating a BigAnimal instance, see Creating a cluster.
Gather instance information
Use the BigAnimal console to obtain the following information for your source and target instance:
- Read/write URI
- Database name
- Username
- Read/write host
Using the BigAnimal console:
- Select the Clusters tab.
- Select your source instance.
- From the Connect tab, obtain the information from Connection Info.
Confirm the Postgres versions before migration
Confirm the Postgres version of your source and target BigAnimal instances:
Output using Postgres 16:
Migrate the database schema
On your source instance, use the dt
command to view the details of the schema to be migrated:
Here's a sample database schema for this example:
Use pg_dump with the --schema-only
flag to copy the schema from your source to your target instance. For more information on using pg_dump, see the Postgres documentation.
On the target instance, confirm the schema was migrated:
Note
A successful schema-only copy shows the tables with zero bytes.
Create a publication
Use the CREATE PUBLICATION
command to create a publication on your source instance. For more information on using CREATE PUBLICATION
, see the Posgres documentation.
In this example:
The expected output is: CREATE PUBLICATION
.
Add tables that you want to replicate to your target instance:
The expected output is: ALTER PUBLICATION
.
Create the logical replication slot
On the source instance, create a replication slot using the pgoutput
plugin:
In the current example:
The expected output returns the slot_name
and lsn
.
The replication slot tracks changes to the published tables from the source instance and replicates changes to the subscriber on the target instance.
Create a subscription
Use the CREATE SUBSCRIPTION
command to create a subscription on your target instance. For more information on using CREATE SUBSCRIPTION
, see the Postgres documentation.
This example creates a subscription on a Postgres 16 instance to a publication on a Postgres 12 instance:
The expected output is: CREATE SUBSCRIPTION
.
In this example, the subscription uses a connection string to specify the source database and includes options to copy existing data and to follow the publication identified by v12_pub
.
The subscriber pulls schema changes (with some exceptions, as noted in the PostgreSQL documentation on Limitations of Logical Replication) and data from the source to the target database, effectively replicating the data.
Validate the migration
To validate the progress of the data migration, use dt+
from the source and target BigAnimal instances to compare the size of each table.
If logical replication is running correctly, each time you run \dt+;
you see that more data was migrated:
Note
You can optionally use LiveCompare to generate a comparison report of the source and target databases to validate that all database objects and data are consistent.