📘 The main benefit of using logical replication is that the replica can be created beforehand and will stay up-to-date with the source Database until it’s time to cut over to the new Database. This allows for upgrades to be performed with minimal downtime.
psql
.
SOURCE_HANDLE
- The handle (i.e. name) of the Database.
ENVIRONMENT
- The handle of the Environment the Database belongs to.
REPLICA_HANDLE
- The handle (i.e., name) for the Database.
REPLICA_VERSION
- The desired PostgreSQL version. Run aptible db:versions
to see a full list of options.
REPLICA_CONTAINER_SIZE
(Optional) - The size of the replica’s container in MB. Having more memory and CPU available speeds up the initialization process up to a certain point. See the Database Scaling documentation for a full list of supported container sizes.
aptible db:tunnel
command.
Example:
aptible db:tunnel
, and store it in the SOURCE_URL
environment variable in the original terminal.
Example:
pglogical
node. If there’s already an existing node, the replica will fail setup. The following script will check for existing pglogical nodes.
aptible db:replicate --logical
, or the node will have to be dropped.
Note that if logical replication was previously attempted, but failed, then the node could be left behind from the previous attempt. See the Cleanup section and follow the instructions for cleaning up the source Database.
(0 rows)
then no action is necessary.
Example output:
ALTER TABLE
.
📘 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 convenientreplicate_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:
❗️pglogical
creates temporary replication slots that may show up inactive at times, theses temporary slots must not be deleted. Deleting these slots will disruptpglogical
aptible db:tunnel
, and store it in the REPLICA_URL
environment variable in the original terminal.
Example:
pglogical
in which, during replica initialization, replication may pause until the next time the source Database is written to. For production Databases, this usually isn’t an issue since it’s being actively used, but for Databases that aren’t used much, like Databases that may have been restored to test logical replication, this issue can arise.
The following script works similarly to the one above, but it also creates a table, writes to it, then drops the table in order to ensure that initialization continues even if the source Database is idle:
CONCURRENTLY
this keyword can be removed, but note that when not indexing concurrently, the table the index belongs to will be locked, which will prevent writes while indexing.
❗️ Performance Alert: synchronous replication ensures that transactions are committed on both the primary and replica databases simultaneously, which can introduce noticable latency on commit times, especially on databases with higher relative volumes of changes. In this case, you may want to ensure that you wait to enable synchronous replication until you are close to performing the cutover in order to minimize the impact of slower commits on the primary database.
APTIBLE_OUTPUT_FORMAT=json aptible apps
.
Example scale command:
aptible config:set
command. This step is also usually easiest to complete by preparing a script that updates all relevant Apps.
Example config command:
pglogical
subscriptions, nodes, and extensions from the replica:
synchronous_standby_names
on the source Database:
🚧 Caution: If you’re cleaning up from a failed replication attempt and you’re not sure ifDrop thepglogical
was being used previously, check with other members of your organization before performing cleanup as this may break existingpglogical
subscribers.
pglogical
replication slots (if they exist), nodes, and extensions:
REPLICA_ID
into the script for it to properly run! If you don’t remember what it is, you can always also run:
psql
client to discover what the pglogical publisher is named.
If the script above raises errors about replication slots being active, then replication was not stopped properly. Ensure that the instructions in the Stop replication section have been completed.
aptible db:replicate --logical
may have increased the max_worker_processes
on the replica to ensure that it has enough to support replication. Now that replication has been terminated, the setting can be set back to the default by running the following command: