Performing a major version upgrade of Postgres on BigAnimal
Using logical replication
Note
This procedure does not 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 may accrue ingress and egress costs from your cloud service provider (CSP) for the migrated data. Please 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, use the following steps, explained in further detail below:
- 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 will be your target instance.
Ensure your target instance is provisioned with a storage size equal to or greater than your source instance.
For detailed steps on creating a BigAnimal instance, see this guide.
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:
psql "<biganimal_read/write_uri>" -c "select version();"
Output using Postgres 16:
version ------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 16.2 (Debian 16.2.0-3.buster) (BigAnimal Edition) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit (1 row)
Migrate the database schema
On your source instance, use the dt command to view the details of the schema to be migrated:
/dt+;
Here is a sample database schema for this example:
List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+------------------+-------+-----------+-------------+---------------+------------+------------- public | pgbench_accounts | table | edb_admin | permanent | heap | 1572 MB | public | pgbench_branches | table | edb_admin | permanent | heap | 8192 bytes | public | pgbench_history | table | edb_admin | permanent | heap | 0 bytes | public | pgbench_tellers | table | edb_admin | permanent | heap | 120 kB |
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.
pg_dump --schema-only -h <source_biganimal_host> -U <source_biganimal_username> -d <source_biganimal_databasename> | psql -h <target_biganimal_host> -U <target_biganimal_username> -d <target_biganimal_databasename>
On the target instance, confirm the schema was migrated:
List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+------------------+-------+-----------+-------------+---------------+---------+------------- public | pgbench_accounts | table | edb_admin | permanent | heap | 0 bytes | public | pgbench_branches | table | edb_admin | permanent | heap | 0 bytes | public | pgbench_history | table | edb_admin | permanent | heap | 0 bytes | public | pgbench_tellers | table | edb_admin | permanent | heap | 0 bytes |
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.
CREATE PUBLICATION <pub_name>;
In this example:
CREATE PUBLICATION v12_pub;
The expected output is: CREATE PUBLICATION.
Add tables that you want to replicate to your target instance:
ALTER PUBLICATION <pub_name> ADD TABLE <table_name>;
ALTER PUBLICATION v12_pub ADD TABLE pgbench_accounts; ALTER PUBLICATION v12_pub ADD TABLE pgbench_branches; ALTER PUBLICATION v12_pub ADD TABLE pgbench_history; ALTER PUBLICATION v12_pub ADD TABLE pgbench_tellers;
The expected output is: ALTER PUBLICATION.
Create the logical replication slot
Then, on the source instance, create a replication slot using the pgoutput plugin:
SELECT pg_create_logical_replication_slot('<slot_name>','pgoutput');
In the current example:
SELECT pg_create_logical_replication_slot('v12_pub','pgoutput');
The expected output returns the slot_name and lsn.
pg_create_logical_replication_slot ------------------------------------ (v12_pub,0/AC003330)
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.
CREATE SUBSCRIPTION <name_of_subscription> CONNECTION 'user=<source_instance_username> host=<source_instance_read/write_host> sslmode=require port=<source_instance_port> dbname=<source_instance_dbname> password=<source_instance_password>' PUBLICATION <publication_name> WITH (enabled=true, copy_data = true, create_slot = false, slot_name=<slot_name>);
Creating a subscription on a Postgres 16 instance to a publication on a Postgres 12 instance:
CREATE SUBSCRIPTION v16_sub CONNECTION 'user=edb_admin host=p-x67kjhacc4.pg.biganimal.io sslmode=require port=5432 dbname=edb_admin password=XXX' PUBLICATION v12_pub WITH (enabled=true, copy_data = true, create_slot = false, slot_name=v12_pub);
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.
List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+------------------+-------+-----------+-------------+---------------+------------+------------- public | pgbench_accounts | table | edb_admin | permanent | heap | 1572 MB | public | pgbench_branches | table | edb_admin | permanent | heap | 8192 bytes | public | pgbench_history | table | edb_admin | permanent | heap | 0 bytes | public | pgbench_tellers | table | edb_admin | permanent | heap | 120 kB |
List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+------------------+-------+-----------+-------------+---------------+---------+------------- public | pgbench_accounts | table | edb_admin | permanent | heap | 0 bytes | public | pgbench_branches | table | edb_admin | permanent | heap | 0 bytes | public | pgbench_history | table | edb_admin | permanent | heap | 0 bytes | public | pgbench_tellers | table | edb_admin | permanent | heap | 0 bytes |
If logical replication is running correctly, each time you run \dt+; you see that more data has been migrated:
List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+------------------+-------+-----------+-------------+---------------+---------+------------- public | pgbench_accounts | table | edb_admin | permanent | heap | 344 MB | public | pgbench_branches | table | edb_admin | permanent | heap | 0 bytes | public | pgbench_history | table | edb_admin | permanent | heap | 0 bytes | public | pgbench_tellers | table | edb_admin | permanent | heap | 0 bytes |
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.