Logical Replication

The Aptible CLI supports creating logical replicas of PostgreSQL Databases using the aptible db:replicate CLI command with the --logical flag and specifying the Database --version the replica should use. The only supported use is for Database upgrades so if you'd like to use logical replication for another purpose, it is recommended that you set it up yourself.

There are multiple ways for Aptible users to upgrade a Database:

  • Dumping data and restoring using a tool such as pgdump.

    This approach is generally simplest, but requires a long downtime window.

  • Physical replication of the Database disk, followed by local upgrade.

    This approach has downtime but is generally quicker than pgdump especially for larger Databases. This process requires access to the Database container and disk, so it must be done through Aptible Support and scheduled during business hours.

  • Logical replication to an upgraded follower, then promotion.

    This approach allows for a near-zero downtime upgrade since the replica can be set up in advance and will stay up-to-date until the cutover. This is the method supported by the CLI command discussed here.

    Everything that aptible db:replicate --logical does can be done without any special access to the Database's container. Therefore, any Aptible user with access to the source Database can set up logical replication to another PostgreSQL database either on or off of Aptible.

Perfoming a Zero-Downtime Upgrade

It is recommended that you use a staging or development Database or restore a backup of your production Database to test the upgrade before doing so against your production Database. This allows you to practice performing the upgrade, catch any unexpected issues, and test against an upgraded Database before touching the production Database.

The process for upgrading a Database using aptible db:replicate --logical is as follows:

Preparation

The upgraded replica can be created and synced with the existing Database without disruption, before the cutover.

🚧

Caution

pglogical will do its best to replicate the structure from the source Database. However, extension versions may be different. Therefore, extensions that create additional tables or are not backwards compatible, notably postgis, may not work properly when replicated this way. Be sure to test your upgrade before performing it on your production Databases if using an extension like this.

  1. If there are existing pglogical nodes on the source Database's PostgreSQL databases, they must be dropped. This node can exist as the result of a failed previous aptible db:replicate command. Dropping the extension will remove the node:

    DROP EXTENSION pglogical;
    

    Be sure to run this on each PostgreSQL database on the source Database except for template databases.

  2. Create a replica with the desired version. The new version must be >= than the version of the existing Database.

    aptible db:replicate $DB_HANDLE $REPLICA_HANDLE --logical --version $NEW_VERSION
    

πŸ“˜

Note

pglogical will copy the source Database's structure at the time the subscription is created. However, subsequent changes to the Database structure, a.k.a. Data Definition Language (DDL) commands, are not included in logical replication. These commands need to be applied to the replica as well as the source Database to ensure that changes to the data are properly replicated.

pglogical provides a convenient replicate_ddl_command function that, when run on the source Database, applies a DDL command to the source Database then queues the statement to be applied to the replica. For example, to add a column to a table:

SELECT pglogical.replicate_ddl_command('ALTER TABLE public.foo ADD COLUMN bar TEXT;');

Cutover

Once the upgraded replica is in place, you can transition to using the new Database with minimal downtime to reconfigure Apps and ensure data is synced during the transition.

  1. Ensure the replica has finished the initial data sync. i.e. this query returns no rows on the replica:

    SELECT * FROM pglogical.local_sync_status WHERE NOT sync_status = 'r';
    
  2. (Optional) Convert from asynchronous to synchronous replication on the source Database.

    This ensures the new Database can't be behind the source when the cutover starts.

    ALTER SYSTEM SET synchronous_standby_names=aptible_subscription;
    

    Restart the Database using aptible db:reload to apply the change.

  3. (Optional) Scale Services to 0 containers.

    Scale all Services that use the source Database so they can’t write to the Database during the cutover. This will result in some downtime in exchange for preventing replication conflicts that can be caused by services writing to both the source and replica Databases at the same time.

    aptible apps:scale --app $APP_HANDLE $SERVICE --container-count 0
    
  4. Update all of your Apps to use the new Database.

  5. Force sync of sequences from the source Database.

    SELECT pglogical.synchronize_sequence( seqoid ) FROM pglogical.sequence_state;
    
  6. Drop the subscription and subscriber node on the new Database.

    DROP EXTENSION pglogical;
    

    Be sure to run this on each PostgreSQL database on the new Database except for template databases.

  7. Scale Services back up to their original number of containers if they were scaled down.

  8. (Optionally) Set max_worker_processes back to the default on the new Database. See How It Works below for details.

     ALTER SYSTEM RESET max_worker_processes;
    

How It Works

aptible db:replicate --logical should work in most cases. This section provides additional details details on how the CLI command works for debugging or if you'd like to know more about what the command does for you.

The CLI command uses the pglogical extension to set up logical replication between the existing Database and the new replica Database. At a high level, these are the steps the CLI command takes to setup logical replication for you:

  1. Update max_worker_processes on the replica based on the number of PostgreSQL databases being replicated. pglogical uses several worker processes per database so it can easily exhaust the default max_worker_processes if replicating more than a couple databases.
  2. Recreate all roles (users) on the replica. pglogical's copy of the source database structure includes assigning the same owner to each table and granting the same permissions. The roles must exist on the replica in order for this to work.
  3. For each PostgreSQL database on the source Database, excluding those that beginning with template:
    1. Create the database on the replica with the aptible user as the owner.
    2. Enable the pglogical extension on the source and replica database.
    3. Create a pglogical subscription between the source and replica database. This will copy the source database's structure (e.g. schemas, tables, permissions, extensions, etc.).
    4. Start the initial data sync. This will truncate and sync data for all tables in all schemas except for the information_schema, pglogical, and pglogical_origin schemas and schemas that begin with pg_ (system schemas).

The replica does not wait for the initial data sync to complete before coming online. The time it takes to sync all of the data from the source Database depends on the size of the Database.

When run on the replica, the following query will list all tables that are not in the replicating state and, therefore, have not finished syncing the initial data from the source Database.

SELECT * FROM pglogical.local_sync_status WHERE NOT sync_status = 'r';

Did this page help you?