# Using pgactive to support active-active replication
The `pgactive` extension uses active-active replication to support and coordinate write operations on multiple RDS for PostgreSQL databases. Amazon RDS for PostgreSQL supports the `pgactive` extension on the following versions:
+ RDS for PostgreSQL 17.0 and all higher versions
+ RDS for PostgreSQL 16.1 and higher 16 versions
+ RDS for PostgreSQL 15.4-R2 and higher 15 versions
+ RDS for PostgreSQL 14.10 and higher 14 versions
+ RDS for PostgreSQL 13.13 and higher 13 versions
+ RDS for PostgreSQL 12.17 and higher 12 versions
+ RDS for PostgreSQL 11.22
**Note**
When there are write operations on more than one database in a replication configuration, conflicts are possible. For more information, see [Handling conflicts in active-active replication](Appendix.PostgreSQL.CommonDBATasks.pgactive.handle-conflicts.md)
**Topics**
+ [Limitations for the pgactive extension](#Appendix.PostgreSQL.CommonDBATasks.pgactive.requirements-limitations)
+ [Initializing the pgactive extension capability](Appendix.PostgreSQL.CommonDBATasks.pgactive.basic-setup.md)
+ [Setting up active-active replication for RDS for PostgreSQL DB instances](Appendix.PostgreSQL.CommonDBATasks.pgactive.setup-replication.md)
+ [Measuring replication lag among pgactive members](Appendix.PostgreSQL.CommonDBATasks.pgactive.replicationlag.md)
+ [Configuring parameter settings for the pgactive extension](Appendix.PostgreSQL.CommonDBATasks.pgactive.parameters.md)
+ [Understanding active-active conflicts](Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.replication.md)
+ [Understanding the pgactive schema](Appendix.PostgreSQL.CommonDBATasks.pgactive.schema.md)
+ [pgactive functions reference](pgactive-functions-reference.md)
+ [Handling conflicts in active-active replication](Appendix.PostgreSQL.CommonDBATasks.pgactive.handle-conflicts.md)
+ [Handling sequences in active-active replication](Appendix.PostgreSQL.CommonDBATasks.pgactive.handle-sequences.md)
## Limitations for the pgactive extension
+ All tables require a Primary Key, otherwise Update's and Delete's aren't allowed. The values in the Primary Key column shouldn't be updated.
+ Sequences may have gaps and sometimes might not follow an order. Sequences are not replicated. For more information, see [Handling sequences in active-active replication](Appendix.PostgreSQL.CommonDBATasks.pgactive.handle-sequences.md).
+ DDL and large objects are not replicated.
+ Secondary unique indexes can cause data divergence.
+ Collation needs to be identical on all node in the group.
+ Load balancing across nodes is an anti-pattern.
+ Large transactions can cause replication lag.
# Initializing the pgactive extension capability
To initialize the `pgactive` extension capability on your RDS for PostgreSQL DB instance, set the value of the `rds.enable_pgactive` parameter to `1` and then create the extension in the database. Doing so automatically turns on the parameters `rds.logical_replication` and `track_commit_timestamp` and sets the value of `wal_level` to `logical`.
You must have permissions as the `rds_superuser` role to perform these tasks.
You can use the AWS Management Console or the AWS CLI to create the required RDS for PostgreSQL DB instances. The steps following assume that your RDS for PostgreSQL DB instance is associated with a custom DB parameter group. For information about creating a custom DB parameter group, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md).
## Console
**To initialize the pgactive extension capability**
1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the navigation pane, choose your RDS for PostgreSQL DB instance.
1. Open the **Configuration** tab for your RDS for PostgreSQL DB instance. In the instance details, find the **DB instance parameter group** link.
1. Choose the link to open the custom parameters associated with your RDS for PostgreSQL DB instance.
1. Find the `rds.enable_pgactive` parameter, and set it to `1` to initialize the `pgactive` capability.
1. Choose **Save changes**.
1. In the navigation pane of the Amazon RDS console, choose **Databases**.
1. Select your RDS for PostgreSQL DB instance, and then choose **Reboot** from the **Actions** menu.
1. Confirm the DB instance reboot so that your changes take effect.
1. When the DB instance is available, you can use `psql` or any other PostgreSQL client to connect to the RDS for PostgreSQL DB instance.
The following example assumes that your RDS for PostgreSQL DB instance has a default database named *postgres*.
```
psql --host=mydb.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password=PASSWORD --dbname=postgres
```
1. To verify that pgactive is initialized, run the following command.
```
postgres=>SELECT setting ~ 'pgactive'
FROM pg_catalog.pg_settings
WHERE name = 'shared_preload_libraries';
```
If `pgactive` is in `shared_preload_libraries`, the preceding command will return the following:
```
?column?
----------
t
```
## AWS CLI
**To initialize the pgactive extension capability**
To initialize the `pgactive` using the AWS CLI, call the [modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) operation to modify certain parameters in your custom parameter group as shown in the following procedure.
1. Use the following AWS CLI command to set `rds.enable_pgactive` to `1` to initialize the `pgactive` capability for the RDS for PostgreSQL DB instance.
```
postgres=>aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=rds.enable_pgactive,ParameterValue=1,ApplyMethod=pending-reboot" \
--region aws-region
```
1. Use the following AWS CLI command to reboot the RDS for PostgreSQL DB instance so that the `pgactive` library is initialized.
```
aws rds reboot-db-instance \
--db-instance-identifier your-instance \
--region aws-region
```
1. When the instance is available, use `psql` to connect to the RDS for PostgreSQL DB instance.
```
psql --host=mydb.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=master user --password=PASSWORD --dbname=postgres
```
1. To verify that pgactive is initialized, run the following command.
```
postgres=>SELECT setting ~ 'pgactive'
FROM pg_catalog.pg_settings
WHERE name = 'shared_preload_libraries';
```
If `pgactive` is in `shared_preload_libraries`, the preceding command will return the following:
```
?column?
----------
t
```
# Setting up active-active replication for RDS for PostgreSQL DB instances
The following procedure shows you how to start active-active replication between two RDS for PostgreSQL DB instances where `pgactive` is available. To run the multi-region high availability example, you need to deploy Amazon RDS for PostgreSQL instances in two different regions and set up VPC Peering. For more information, see [VPC peering](https://docs.aws.amazon.com/vpc/latest/peering/what-is-vpc-peering.html).
**Note**
Sending traffic between multiple regions may incur additional costs.
These steps assume that the RDS for PostgreSQL DB instance has been enabled with the `pgactive` extension. For more information, see [Initializing the pgactive extension capability](Appendix.PostgreSQL.CommonDBATasks.pgactive.basic-setup.md).
**To configure the first RDS for PostgreSQL DB instance with the `pgactive` extension**
The following example illustrates how the `pgactive` group is created, along with other steps required to create the `pgactive` extension on the RDS for PostgreSQL DB instance.
1. Use `psql` or another client tool to connect to your first RDS for PostgreSQL DB instance.
```
psql --host=firstinstance.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password=PASSWORD --dbname=postgres
```
1. Create a database on the RDS for PostgreSQL instance using the following command:
```
postgres=> CREATE DATABASE app;
```
1. Switch connection to the new database using the following command:
```
\c app
```
1. Create and populate a sample table using the following SQL statements:
1. Create an example table using the following SQL statement.
```
app=> CREATE SCHEMA inventory;
CREATE TABLE inventory.products (
id int PRIMARY KEY, product_name text NOT NULL,
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP);
```
1. Populate the table with some sample data by using the following SQL statement.
```
app=> INSERT INTO inventory.products (id, product_name)
VALUES (1, 'soap'), (2, 'shampoo'), (3, 'conditioner');
```
1. Verify that data exists in the table by using the following SQL statement.
```
app=>SELECT count(*) FROM inventory.products;
count
-------
3
```
1. Create `pgactive` extension on the existing database.
```
app=> CREATE EXTENSION pgactive;
```
1. To securely create and initialize the pgactive group use the following commands:
```
app=>
-- connection info for endpoint1
CREATE SERVER pgactive_server_endpoint1
FOREIGN DATA WRAPPER pgactive_fdw
OPTIONS (host '', dbname 'app');
CREATE USER MAPPING FOR postgres
SERVER pgactive_server_endpoint1
OPTIONS (user 'postgres', password '');
-- connection info for endpoint2
CREATE SERVER pgactive_server_endpoint2
FOREIGN DATA WRAPPER pgactive_fdw
OPTIONS (host '', dbname 'app');
CREATE USER MAPPING FOR postgres
SERVER pgactive_server_endpoint2
OPTIONS (user 'postgres', password '');
```
Now you can initialize the replication group and add this first instance:
```
SELECT pgactive.pgactive_create_group(
node_name := 'endpoint1-app',
node_dsn := 'user_mapping=postgres pgactive_foreign_server=pgactive_server_endpoint1'
);
```
Use the following commands as an alternate but less secure method to create and initialize the pgactive group:
```
app=> SELECT pgactive.pgactive_create_group(
node_name := 'node1-app',
node_dsn := 'dbname=app host=firstinstance.111122223333.aws-region.rds.amazonaws.com user=postgres password=PASSWORD');
```
node1-app is the name that you assign to uniquely identify a node in the `pgactive` group.
**Note**
To perform this step successfully on a DB instance that is publicly accessible, you must turn on the `rds.custom_dns_resolution` parameter by setting it to `1`.
1. To check if the DB instance is ready, use the following command:
```
app=> SELECT pgactive.pgactive_wait_for_node_ready();
```
If the command succeeds, you can see the following output:
```
pgactive_wait_for_node_ready
------------------------------
(1 row)
```
**To configure the second RDS for PostgreSQL instance and join it to the `pgactive` group**
The following example illustrates how you can join an RDS for PostgreSQL DB instance to the `pgactive` group, along with other steps that are required to create the `pgactive` extension on the DB instance.
These steps assume that another RDS for PostgreSQL DB instances has been set up with the `pgactive` extension. For more information, see [Initializing the pgactive extension capability](Appendix.PostgreSQL.CommonDBATasks.pgactive.basic-setup.md).
1. Use `psql` to connect to the instance that you want to receive updates from the publisher.
```
psql --host=secondinstance.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password=PASSWORD --dbname=postgres
```
1. Create a database on the second RDS for PostgreSQL DB instance using the following command:
```
postgres=> CREATE DATABASE app;
```
1. Switch connection to the new database using the following command:
```
\c app
```
1. Create the `pgactive` extension on the existing database.
```
app=> CREATE EXTENSION pgactive;
```
1. Join the RDS for PostgreSQL second DB instance to the `pgactive` group in a more secure way using the following commands:
```
-- connection info for endpoint1
CREATE SERVER pgactive_server_endpoint1
FOREIGN DATA WRAPPER pgactive_fdw
OPTIONS (host '', dbname 'app');
CREATE USER MAPPING FOR postgres
SERVER pgactive_server_endpoint1
OPTIONS (user 'postgres', password '');
-- connection info for endpoint2
CREATE SERVER pgactive_server_endpoint2
FOREIGN DATA WRAPPER pgactive_fdw
OPTIONS (host '', dbname 'app');
CREATE USER MAPPING FOR postgres
SERVER pgactive_server_endpoint2
OPTIONS (user 'postgres', password '');
```
```
SELECT pgactive.pgactive_join_group(
node_name := 'endpoint2-app',
node_dsn := 'user_mapping=postgres pgactive_foreign_server=pgactive_server_endpoint2',
join_using_dsn := 'user_mapping=postgres pgactive_foreign_server=pgactive_server_endpoint1'
);
```
Use the following commands as an alternate but less secure method to join the RDS for PostgreSQL second DB instance to the `pgactive` group
```
app=> SELECT pgactive.pgactive_join_group(
node_name := 'node2-app',
node_dsn := 'dbname=app host=secondinstance.111122223333.aws-region.rds.amazonaws.com user=postgres password=PASSWORD',
join_using_dsn := 'dbname=app host=firstinstance.111122223333.aws-region.rds.amazonaws.com user=postgres password=PASSWORD');
```
node2-app is the name that you assign to uniquely identify a node in the `pgactive` group.
1. To check if the DB instance is ready, use the following command:
```
app=> SELECT pgactive.pgactive_wait_for_node_ready();
```
If the command succeeds, you can see the following output:
```
pgactive_wait_for_node_ready
------------------------------
(1 row)
```
If the first RDS for PostgreSQL database is relatively large, you can see `pgactive.pgactive_wait_for_node_ready()` emitting the progress report of the restore operation. The output looks similar to the following:
```
NOTICE: restoring database 'app', 6% of 7483 MB complete
NOTICE: restoring database 'app', 42% of 7483 MB complete
NOTICE: restoring database 'app', 77% of 7483 MB complete
NOTICE: restoring database 'app', 98% of 7483 MB complete
NOTICE: successfully restored database 'app' from node node1-app in 00:04:12.274956
pgactive_wait_for_node_ready
------------------------------
(1 row)
```
From this point forward, `pgactive` synchronizes the data between the two DB instances.
1. You can use the following command to verify if the database of the second DB instance has the data:
```
app=> SELECT count(*) FROM inventory.products;
```
If the data is successfully synchronized, you’ll see the following output:
```
count
-------
3
```
1. Run the following command to insert new values:
```
app=> INSERT INTO inventory.products (id, product_name) VALUES (4, 'lotion');
```
1. Connect to the database of the first DB instance and run the following query:
```
app=> SELECT count(*) FROM inventory.products;
```
If the active-active replication is initialized, the output is similar to the following:
```
count
-------
4
```
**To detach and remove a DB instance from the `pgactive` group**
You can detach and remove a DB instance from the `pgactive` group using these steps:
1. You can detach the second DB instance from the first DB instance using the following command:
```
app=> SELECT * FROM pgactive.pgactive_detach_nodes(ARRAY[‘node2-app']);
```
1. Remove the `pgactive` extension from the second DB instance using the following command:
```
app=> SELECT * FROM pgactive.pgactive_remove();
```
To forcefully remove the extension:
```
app=> SELECT * FROM pgactive.pgactive_remove(true);
```
1. Drop the extension using the following command:
```
app=> DROP EXTENSION pgactive;
```
# Measuring replication lag among pgactive members
You can use the following query to view the replication lag among the `pgactive` members. Run this query on every `pgactive` node to get the full picture.
```
app=> SELECT * FROM pgactive.pgactive_get_replication_lag_info();
│-[ RECORD 1 ]--------+---------------------------------------------
│node_name | node2-app
│node_sysid | 7481018224801653637
│application_name | pgactive:7481018224801653637:send
│slot_name | pgactive_16385_7481018224801653637_0_16385__
│active | t
│active_pid | 783486
│pending_wal_decoding | 0
│pending_wal_to_apply | 0
│restart_lsn | 0/2108150
│confirmed_flush_lsn | 0/2154690
│sent_lsn | 0/2154690
│write_lsn | 0/2154690
│flush_lsn | 0/2154690
│replay_lsn | 0/2154690
│-[ RECORD 2 ]--------+---------------------------------------------
│node_name | node1-app
│node_sysid | 7481018033434600853
│application_name | pgactive:7481018033434600853:send
│slot_name | pgactive_16385_7481018033434600853_0_16385__
│active | t
│active_pid | 783488
│pending_wal_decoding | 0
│pending_wal_to_apply | 0
│restart_lsn | 0/20F5AD0
│confirmed_flush_lsn | 0/214EF68
│sent_lsn | 0/214EF68
│write_lsn | 0/214EF68
│flush_lsn | 0/214EF68
│replay_lsn | 0/214EF68
```
Monitor the following diagnostics at a minimum:
active
Set up alerts when active is false, which indicates that the slot isn't currently in use (the subscriber instance has disconnected from the publisher).
pending\$1wal\$1decoding
In PostgreSQL's logical replication, WAL files are stored in binary format. The publisher must decode these WAL changes and convert them into logical changes (such as insert, update, or delete operations).
The metric pending\$1wal\$1decoding shows the number of WAL files waiting to be decoded on the publisher side.
This number can increase due to these factors:
+ When the subscriber isn't connected, active status will be false and pending\$1wal\$1decoding will increase
+ The slot is active, but the publisher can't keep up with the volume of WAL changes
pending\$1wal\$1to\$1apply
The metric pending\$1wal\$1apply indicates the number of WAL files waiting to be applied on the subscriber side.
Several factors can prevent the subscriber from applying changes and potentially cause a disk full scenario:
+ Schema differences - for example, when you have changes in the WAL stream for a table named sample, but that table doesn't exist on the subscriber side
+ Values in the primary key columns were updated
+ Secondary unique indexes can cause data divergence
# Configuring parameter settings for the pgactive extension
You can use the following query to view all the parameters associated with `pgactive` extension.
```
app=> SELECT * FROM pg_settings WHERE name LIKE 'pgactive.%';
```
You can configure the `pgactive` extension using various parameters. These parameters can be set through either the AWS Management Console or the AWS CLI interface.
## Main pgactive extension parameters
The following table provides a reference for the main parameters of the `pgactive` extension:
| Parameter | Unit | Default | Description |
| --- | --- | --- | --- |
| pgactive.conflict\$1logging\$1include\$1tuples | `boolean` | – | Logs complete tuple information for the `pgactive` extension. A server restart is required for changes to take effect. |
| pgactive.log\$1conflicts\$1to\$1table | `boolean` | – | Determines whether the `pgactive` extension logs the detected conflicts to the `pgactive.pgactive_conflict_history` table. For more information, see Conflict logging for details. A server restart is required for changes to take effect. |
| pgactive.log\$1conflicts\$1to\$1logfile | `boolean` | – | Determines whether the `pgactive` extension logs the detected conflicts to the PostgreSQL log file. For more information, see Conflict logging for details. A server restart is required for changes to take effect. |
| pgactive.synchronous\$1commit | `boolean` | off | Determines the commit behavior for pgactive apply workers. When disabled(off), apply workers perform asynchronous commits, which improves PostgreSQL throughput during apply operations but delays replay confirmations to the upstream. Setting it to `off` is always safe and won't cause transaction loss or skipping. This setting only affects the timing of disk flushes on the downstream node and when confirmations are sent upstream. The system delays sending replay flush confirmations until commits are flushed to disk through unrelated operations like checkpoints or periodic work. However, if the upstream has the downstream listed in `synchronous_standby_names`, setting it to `off` causes synchronous commits on the upstream to take longer to report success to the client. In this case, set the parameter to `on`. Even when this parameter is set to `on` with nodes listed in `synchronous_standby_names`, replication conflicts can still occur in active-active configurations. This is because the system lacks inter-node locking and global snapshot management, allowing concurrent transactions on different nodes to modify the same tuple. Additionally, transactions only begin replication after committing on the upstream node. Enabling synchronous commit doesn't transform the pgactive extension into an always-consistent system. |
| pgactive.temp\$1dump\$1directory | `string` | – | Defines the temporary storage path required for database cloning operations during initial setup. This directory must be writable by the postgres user and have sufficient storage space to contain a complete database dump. The system uses this location only during initial database setup with logical copy operations. This parameter isn't used by the `pgactive_init_copy command`. |
| pgactive.max\$1ddl\$1lock\$1delay | `milliseconds` | `-1` | Specifies the maximum wait time for DDL lock before forcibly aborting concurrent write transactions. The default value is `-1`, which adopts the value set in `max_standby_streaming_delay`. This parameter accepts time units. For example, you can set it to 10s for 10 seconds. During this wait period, the system attempts to acquire DDL locks while waiting for ongoing write transactions to either commit or roll back. For more information, see the DDL Locking. |
| pgactive.ddl\$1lock\$1timeout | `milliseconds` | `-1` | Specifies how long a DDL lock attempt waits to obtain the lock. The default value is `-1`, which uses the value specified in lock\$1timeout. You can set this parameter using time units such as 10s for 10 seconds. This timer only controls the waiting period for obtaining a DDL lock. Once the system obtains the lock and begins the DDL operation, the timer stops. This parameter doesn't limit the total duration a DDL lock can be held or the overall DDL operation time. To control the total duration of the operation, use `statement_timeout` instead. For more information, see DDL Locking. |
| pgactive.debug\$1trace\$1ddl\$1locks\$1level | `boolean` | – | Overrides the default debug log level for DDL locking operations in the `pgactive` extension. When configured, this setting causes DDL lock-related messages to be emitted at the LOG debug level instead of their default level. Use this parameter to monitor DDL locking activity without enabling the verbose `DEBUG1` or `DEBUG2` log levels across your entire server. Available log levels, in increasing order of verbosity: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pgactive.parameters.html) For more information about monitoring options, see Monitoring global DDL locks. Changes to this setting take effect when you reload the configuration. You don't need to restart the server. |
## Additional pgactive extension parameters
The following table presents less frequently used and internal configuration options available for the `pgactive` extension.
| Parameter | Unit | Default | Description |
| --- | --- | --- | --- |
| pgactive.debug\$1apply\$1delay | `integer` | – | Sets an apply delay (in milliseconds) for configured connections that don't have an explicit apply delay in their `pgactive.pgactive_connections` entry. This delay is set during node creation or join time, and pgactive won't replay a transaction on peer nodes until at least the specified number of milliseconds have elapsed since it was committed. Primarily used to simulate high-latency networks in testing environments to make it easier to create conflicts. For example, with a 500ms delay on nodes A and B, you have at least 500ms to perform a conflicting insert on node B after inserting a value on node A. Requires a server reload or restart of apply workers to take effect. |
| pgactive.connectability\$1check\$1duration | `integer` | – | Specifies the duration (in seconds) that a database worker attempts to establish connections during failed attempts. The worker makes one connection attempt per second until it succeeds or reaches this timeout value. This setting is useful when the database engine starts before the worker is ready to establish connections. |
| pgactive.skip\$1ddl\$1replication | `boolean` | `on` | Controls how DDL changes are replicated or handled in Amazon RDS with `pgactive` enabled. When set to `on`, the node processes DDL changes like a non-pgcctive node. The following requirements apply when working with this parameter: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pgactive.parameters.html) You can modify this parameter in two ways with super user privileges: globally, locally (session level). Changing this parameter incorrectly can break your replication setups. |
| pgactive.do\$1not\$1replicate | `boolean` | – | This parameter is for internal use only. When you set this parameter in a transaction, the changes are not replicated to other nodes in your DB cluster. Changing this parameter incorrectly can break your replication setups. |
| pgactive.discard\$1mismatched\$1row\$1attributes | `boolean` | – | This parameter is intended for specialist use only. We recommend using this parameter only when troubleshooting specific replication issues. Use this parameter when: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pgactive.parameters.html) This setting overrides the following error message and allows data divergence to arise to let replication continue: `cannot right-pad mismatched attributes; attno %u is missing in local table and remote row has non-null, non-dropped value for this attribute` Changing this parameter incorrectly can break your replication setups. |
| pgactive.debug\$1trace\$1replay | `boolean` | – | When set to `on`, it emits a log message for each remote action that downstream apply workers process. The logs include: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pgactive.parameters.html) The logs also capture queued DDL commands and table drops.para> By default, the logs do not include row field contents. To include row values in the logs, you must recompile with the following flags enabled: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pgactive.parameters.html) Enabling this logging setting can impact performance. We recommend enabling it only when needed for troubleshooting. Changes to this setting take effect when you reload the configuration. You don't need to restart the server. |
| pgactive.extra\$1apply\$1connection\$1options | | – | You can configure connection parameters for all peer node connections with pgactive nodes. These parameters control settings such as keepalives and SSL modes. By default, pgactive uses the following connection parameters: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pgactive.parameters.html) To override the default parameters, use the following similar command: pgactive.extra\$1apply\$1connection\$1options = 'keepalives=0' Individual node connection strings take precedence over both these settings and pgactive's built-in connection options. For more information about connection string formats, see [libpq connection strings](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING). We recommend keeping the default keepalive settings enabled. Only disable keepalives if you experience issues with large transactions completing over unreliable networks. We recommend keeping the default keepalive settings enabled. Only disable keepalives if you experience issues with large transactions completing over unreliable networks. Changes to this setting take effect when you reload the configuration. You don't need to restart the server. |
| pgactive.init\$1node\$1parallel\$1jobs (int) | | – | Specifies the number of parallel jobs that `pg_dump` and `pg_restore` can use during logical node joins with the `pgactive.pgactive_join_group` function. Changes to this setting take effect when you reload the configuration. You don't need to restart the server. |
| pgactive.max\$1nodes | `int` | 4 | Specifies the maximum number of nodes allowed in a pgactive extension group. The default value is 4 nodes. You must consider the following when setting the value of this parameter: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pgactive.parameters.html) You can set this parameter in two ways: in the configuration file, using the `ALTER SYSTEM SET` command Default value for this parameter is `4`, meaning, there can be maximum of 4 nodes allowed in the `pgactive` extension group at any point of time. The change takes effect after you restart the server. |
| pgactive.permit\$1node\$1identifier\$1getter\$1function\$1creation | `boolean` | – | This parameter is intended for internal use only. When enabled, `pgactive` extension allows creation of pgactive node identifier getter function. |
# Understanding active-active conflicts
When you use pgactive in active-active mode, writing to the same tables from multiple nodes can create data conflicts. While some clustering systems use distributed locks to prevent concurrent access, pgactive takes an optimistic approach that's better suited for geographically distributed applications.
Some database clustering systems prevent concurrent data access by using distributed locks. While this approach works when servers are in close proximity, it doesn't support geographically distributed applications because it requires extremely low latency for good performance. Instead of using distributed locks (a pessimistic approach), the pgactive extension uses an optimistic approach. This means it:
+ Helps you avoid conflicts when possible.
+ Allows certain types of conflicts to occur.
+ Provides conflict resolution when conflicts happen.
This approach gives you more flexibility when building distributed applications.
## How conflicts happen
Inter-node conflicts arise from sequences of events that could not happen if all the involved transactions occurred concurrently on the same node. Because the nodes only exchange changes after transactions commit, each transaction is individually valid on the node it committed on but would not be valid if run on another node that has done other work in the meantime. Since pgactive apply essentially replays the transaction on the other nodes, the replay operation can fail if there is a conflict between a transaction being applied and a transaction that was committed on the receiving node.
The reason most conflicts can't happen when all transactions run on a single node is that PostgreSQL has inter-transaction communication mechanisms to prevent it, including:
+ UNIQUE indexes
+ SEQUENCEs
+ Row and relation locking
+ SERIALIZABLE dependency tracking
All of these mechanisms are ways to communicate between transactions to prevent undesirable concurrency issues
pgactive achieves low latency and handles network partitions well because it doesn't use a distributed transaction manager or lock manager. However, this means transactions on different nodes run in complete isolation from each other. While isolation typically improves database consistency, in this case, you need to reduce isolation to prevent conflicts.
## Types of conflicts
Conflicts that can occur include:
**Topics**
+ [PRIMARY KEY or UNIQUE conflicts](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict1)
+ [INSERT/INSERT conflicts](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict2)
+ [INSERTs that violate multiple UNIQUE constraints](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict3)
+ [UPDATE/UPDATE conflicts](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict4)
+ [UPDATE conflicts on the PRIMARY KEY](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict5)
+ [UPDATEs that violate multiple UNIQUE constraints](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict6)
+ [UPDATE/DELETE conflicts](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict7)
+ [INSERT/UPDATE conflicts](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict8)
+ [DELETE/DELETE conflicts](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict9)
+ [Foreign Key Constraint conflicts](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict10)
+ [Exclusion constraint conflicts](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict11)
+ [Global data conflicts](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict12)
+ [Lock conflicts and deadlock aborts](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict13)
+ [Divergent conflicts](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict14)
### PRIMARY KEY or UNIQUE conflicts
Row conflicts occur when multiple operations attempt to modify the same row key in ways not possible on a single node. These conflicts represent the most common type of data conflicts.
pgactive resolves detected conflicts through last-update-wins handling or your custom conflict handler.
Row conflicts include:
+ INSERT vs INSERT
+ INSERT vs UPDATE
+ UPDATE vs DELETE
+ INSERT vs DELETE
+ DELETE vs DELETE
+ INSERT vs DELETE
### INSERT/INSERT conflicts
This most common conflict occurs when INSERTs on two different nodes create a tuple with the same PRIMARY KEY values (or identical UNIQUE constraint values when no PRIMARY KEY exists).
pgactivelink resolves INSERT conflicts by using the timestamp from the originating host to keep the most recent tuple. You can override this default behavior with your custom conflict handler. While this process requires no special administrator action, be aware that pgactivelink discards one of the INSERT operations across all nodes. No automatic data merging occurs unless your custom handler implements it.
The pgactivelink can only resolve conflicts involving a single constraint violation. If an INSERT violates multiple UNIQUE constraints, you must implement additional conflict resolution strategies.
### INSERTs that violate multiple UNIQUE constraints
An INSERT/INSERT conflict can violate multiple UNIQUE constraints, including the PRIMARY KEY. pgactivelink can only handle conflicts that involve a single UNIQUE constraint. When conflicts violate multiple UNIQUE constraints, the apply worker fails and returns the following error:
`multiple unique constraints violated by remotely INSERTed tuple.`
In older versions, this situation generated a 'diverging uniqueness conflict' error instead.
To resolve these conflicts, you must take manual action. Either DELETE the conflicting local tuples or UPDATE them to remove conflicts with the new remote tuple. Be aware that you might need to address multiple conflicting tuples. Currently, pgactivelink provides no built-in functionality to ignore, discard, or merge tuples that violate multiple unique constraints.
**Note**
For more information, see UPDATEs that violate multiple UNIQUE constraints.
### UPDATE/UPDATE conflicts
This conflict occurs when two nodes concurrently modify the same tuple without changing its PRIMARY KEY. pgactivelink resolves these conflicts using last-update-wins logic or your custom conflict handler, if defined. A PRIMARY KEY is essential for tuple matching and conflict resolution. For tables without a PRIMARY KEY, pgactivelink rejects UPDATE operations with the following error:
`Cannot run UPDATE or DELETE on table (tablename) because it does not have a primary key.`
### UPDATE conflicts on the PRIMARY KEY
pgactive has limitations when handling PRIMARY KEY updates. While you can perform UPDATE operation on a PRIMARY KEY, pgactive can't automatically resolve conflicts using last-update-wins logic for these operations. You must ensure that your PRIMARY KEY updates don't conflict with existing values. If conflicts occur during PRIMARY KEY updates, they become divergent conflicts that require your manual intervention. For more information about handling these situations, see [Divergent conflicts](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict14).
### UPDATEs that violate multiple UNIQUE constraints
pgactivelink cannot apply last-update-wins conflict resolution when an incoming UPDATE violates multiple UNIQUE constraints or PRIMARY KEY values. This behavior is similar to INSERT operations with multiple constraint violations. These situations create divergent conflicts that require your manual intervention. For more information, see [Divergent conflicts](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict14).
### UPDATE/DELETE conflicts
This conflicts occur when one node UPDATEs a row while another node simultaneously DELETEs it. In this case a UPDATE/DELETE conflict occurs on replay. The resolution is to discard any UPDATE that arrives after a DELETE, unless your custom conflict handler specifies otherwise.
pgactivelink requires a PRIMARY KEY to match tuples and resolve conflicts. For tables without a PRIMARY KEY, it rejects DELETE operations with the following error:
`Cannot run UPDATE or DELETE on table (tablename) because it does not have a primary key.`
**Note**
pgactivelink cannot distinguish between UPDATE/DELETE and INSERT/UPDATE conflicts. In both cases, an UPDATE affects a nonexistent row. Due to asynchronous replication and lack of replay ordering between nodes, pgactivelink can't determine if the UPDATE is for a new row (INSERT not yet received) or a deleted row. In both scenarios, pgactivelink discards the UPDATE.
### INSERT/UPDATE conflicts
This conflict can occur in multi-node environments. It happens when one node INSERTs a row, a second node UPDATEs it, and a third node receives the UPDATE before the original INSERT. By default, pgactivelink resolves these conflicts by discarding the UPDATE, unless your custom conflict trigger specifies otherwise. Be aware that this resolution method can result in data inconsistencies across nodes. For more information about similar scenarios and their handling, see [UPDATE/DELETE conflicts](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict7).
### DELETE/DELETE conflicts
This conflict occurs when two different nodes concurrently delete the same tuple. pgactivelink considers these conflicts harmless because both DELETE operations have the same end result. In this scenario, pgactivelink safely ignores one of the DELETE operations without affecting data consistency.
### Foreign Key Constraint conflicts
FOREIGN KEY constraints can cause conflicts when applying remote transactions to existing local data. These conflicts typically occur when transactions are applied in a different sequence than their logical order on the originating nodes.
By default, pgactive applies changes with session\$1replication\$1role as `replica`, which bypasses foreign key checks during replication. In active-active configurations, this can lead to foreign key violations. Most violations are temporary and resolve once replication catches up. However, dangling foreign keys can occur because pgactive doesn't support cross-node row locking.
This behavior is inherent to partition-tolerant asynchronous active-active systems. For example, node A might insert a new child row while node B simultaneously deletes its parent row. The system can't prevent this type of concurrent modification across nodes.
To minimize foreign key conflicts, we recommend the following:
+ Limit foreign key relationships to closely related entities.
+ Modify related entities from a single node when possible.
+ Choose entities that rarely require modification.
+ Implement application-level concurrency control for modifications.
### Exclusion constraint conflicts
pgactive link doesn’t support exclusion constraints and restricts their creation.
**Note**
If you convert an existing standalone database to a pgactivelink database, manually drop all exclusion constraints.
In a distributed asynchronous system, it's not possible to guarantee that no set of rows violates the constraint. This is because all transactions on different nodes are fully isolated. Exclusion constraints can lead to replay deadlocks, where replay can't progress from any node to another due to exclusion constraint violations.
If you force pgactive Link to create an exclusion constraint, or if you don't drop existing ones when converting a standalone database to pgactive Link, replication is likely to break. To restore replication progress, remove or alter the local tuples that conflict with an incoming remote tuple so that the remote transaction can be applied.
### Global data conflicts
When using pgactivelink, conflicts can occur when nodes have different global PostgreSQL system-wide data, such as roles. These conflicts can cause operations—primarily DDL—to succeed and commit on one node but fail to apply to other nodes.
If a user exists on one node but not another, replication issues can occur:
+ Node1 has a user named `fred`, but this user doesn't exist on Node2
+ When `fred` creates a table on Node1, the table is replicated with `fred` as the owner
+ When this DDL command is applied to Node2, it fails because user `fred` doesn't exist
+ This failure generates an ERROR in the PostgreSQL logs on Node2 and increments the `pgactive.pgactive_stats.nr_rollbacks` counter
**Resolution:** Create the user `fred` on Node2. The user doesn't need identical permissions but must exist on both nodes.
If a table exists on one node but not another, data modification operations will fail:
+ Node1 has a table named `foo` that doesn't exist on Node2
+ Any DML operations on the `foo` table on Node1 will fail when replicated to Node2
**Resolution:** Create the table `foo` on Node2 with the same structure.
**Note**
pgactivelink doesn't currently replicate CREATE USER commands or DDL operations. DDL replication is planned for a future release.
### Lock conflicts and deadlock aborts
Because pgactive apply processes operate like normal user sessions, they follow standard row and table locking rules. This can result in pgactivelink apply processes waiting on locks held by user transactions or by other apply processes.
The following types of locks can affect apply processes:
+ Explicit table-level locking (LOCK TABLE ...) by user sessions
+ Explicit row-level locking (SELECT ... FOR UPDATE/FOR SHARE) by user sessions
+ Locking from foreign keys
+ Implicit locking due to row UPDATEs, INSERTs, or DELETEs, either from local activity or apply from other servers
Deadlocks can occur between:
+ A pgactivelink apply process and a user transaction
+ Two apply processes
When deadlocks occur, PostgreSQL's deadlock detector terminates one of the problem transactions. If the pgactivelink apply worker's process is terminated, it automatically retries and typically succeeds.
**Note**
These issues are temporary and generally don't require administrator intervention. If an apply process is blocked for an extended period by a lock on an idle user session, you can terminate the user session to resume replication. This situation is similar to when a user holds a long lock that affects another user session.
To identify locking-related replay delays, enable the `log_lock_waits` facility in PostgreSQL.
### Divergent conflicts
Divergent conflicts occur when data that should be identical across nodes differs unexpectedly. While these conflicts shouldn't happen, not all can be reliably prevented in the current implementation.
**Note**
Modifying a row's PRIMARY KEY can cause divergent conflicts if another node changes the same row's key before all nodes process the change. Avoid changing primary keys, or restrict changes to one designated node. For more information, see [UPDATE conflicts on the PRIMARY KEY](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflict5).
Divergent conflicts involving row data typically require administrator intervention. To resolve these conflicts, you must manually adjust data on one node to match another while temporarily disabling replication using `pgactive.pgactive_do_not_replicate`. These conflicts shouldn't occur when you use pgactive as documented and avoid settings or functions marked as unsafe.
As an administrator, you must manually resolve these conflicts. Depending on the conflict type, you'll need to use advanced options like `pgactive.pgactive_do_not_replicate`. Use these options with caution, as improper use can worsen the situation. Due to the variety of possible conflicts, we can't provide universal resolution instructions.
Divergent conflicts occur when data that should be identical across different nodes unexpectedly differs. While these conflicts shouldn't happen, not all such conflicts can be reliably prevented in the current implementation.
## Avoiding or tolerating conflicts
In most cases, you can use appropriate application design to avoid conflicts or make your application tolerant of conflicts.
Conflicts only occur when simultaneous operations happen on multiple nodes. To avoid conflicts:
+ Write to only one node
+ Write to independent database subsets on each node (for example, assign each node a separate schema)
For INSERT vs INSERT conflicts, use Global sequences to prevent conflicts entirely.
If conflicts are not acceptable for your use case, consider implementing distributed locking at the application level. Often, the best approach is to design your application to work with pgactive's conflict resolution mechanisms rather than trying to prevent all conflicts. For more information, see [Types of conflicts](#Appendix.PostgreSQL.CommonDBATasks.pgactive.actact.conflicttypes).
## Conflict logging
pgactivelink logs conflict incidents in the `pgactive.pgactive_conflict_history` table to help you diagnose and handle active-active conflicts. Conflict logging to this table only occurs when you set `pgactive.log_conflicts_to_table` to true. The pgactive extension also logs conflicts to the PostgreSQL log file when log\$1min\$1messages is set to `LOG` or `lower`, regardless of the `pgactive.log_conflicts_to_table` setting.
Use the conflict history table to:
+ Measure how frequently your application creates conflicts
+ Identify where conflicts occur
+ Improve your application to reduce conflict rates
+ Detect cases where conflict resolutions don't produce desired results
+ Determine where you need user-defined conflict triggers or application design changes
For row conflicts, you can optionally log row values. This is controlled by the `pgactive.log_conflicts_to_table` setting. Note that:
+ This is a global database-wide option
+ There is no per-table control over row value logging
+ No limits are applied to field numbers, array elements, or field lengths
+ Enabling this feature may not be advisable if you work with multi-megabyte rows that might trigger conflicts
Since the conflict history table contains data from every table in the database (each with potentially different schemas), logged row values are stored as JSON fields. The JSON is created using `row_to_json`, similar to calling it directly from SQL. PostgreSQL doesn't provide a `json_to_row` function, so you'll need table-specific code (in PL/pgSQL, PL/Python, PL/Perl, etc.) to reconstruct a composite-typed tuple from the logged JSON.
**Note**
Support for user-defined conflicts is planned as a future extension feature.
# Understanding the pgactive schema
The pgactive schema manages active-active replication in RDS for PostgreSQL. This schema contains tables that store replication configuration and status information.
**Note**
The pgactive schema is evolving and subject to change. Don't modify the data in these tables directly.
The key tables in the pgactive schema include:
+ `pgactive_nodes` – Stores information about nodes in the active-active replication group.
+ `pgactive_connections` – Stores connection details for each node.
## pgactive\$1nodes
The pgactive\$1nodes stores information about the nodes participating in the active-active replication group.
| Column | Type | Collation | Nullable | Default |
| --- | --- | --- | --- | --- |
| node\$1sysid | text | – | not null | – |
| node\$1timeline | oid | – | not null | – |
| node\$1dboid | oid | – | not null | – |
| node\$1status | char | – | not null | – |
| node\$1name | text | – | not null | – |
| node\$1dsn | text | – | not null | – |
| node\$1init\$1from\$1dsn | text | – | not null | – |
| node\$1read\$1only | boolean | – | – | false |
| node\$1seq\$1id | smallint | – | not null | – |
**node\$1sysid**
Unique ID for a node, generated during `pgactive_create_group` or `pgactive_join_group`
**node\$1status**
Readiness of the node:
+ **b** - beginning setup
+ **i** - initializing
+ **c** - catchup
+ **o** - creating outbound slots
+ **r** - ready
+ **k** - killed
This column doesn't indicate if a node is connected or disconnected.
**node\$1name**
User-provided unique node name.
**node\$1dsn**
Connection string or user mapping name
**node\$1init\$1from\$1dsn**
DSN from which this node was created.
## pgactive\$1connection
The pgactive\$1connections stores connection details for each node.
| Column | Type | Collation | Nullable | Default |
| --- | --- | --- | --- | --- |
| conn\$1sysid | text | none | not null | none |
| conn\$1timeline | oid | none | not null | none |
| conn\$1dboid | oid | none | not null | none |
| conn\$1dsn | text | none | not null | none |
| conn\$1apply\$1delay | integer | none | none | none |
| conn\$1replication\$1sets | text | none | none | none |
conn\$1sysid
Node identifier for the node this entry refers to.
conn\$1dsn
Same as pgactive.pgactive\$1nodes `node_dsn`.
conn\$1apply\$1delay
If set, milliseconds to wait before applying each transaction from the remote node. Mainly for debugging. If null, the global default applies.
## Working with replication sets
Replication sets determine which tables to include or exclude from replication operations. By default, all tables are replicated unless you specify otherwise using the following functions:
+ `pgactive_exclude_table_replication_set()` - Excludes specified tables from replication
+ `pgactive_include_table_replication_set()` - Includes specified tables in replication
**Note**
Before you configure replication sets, consider the following:
You can configure table inclusion or exclusion only after running `pgactive_create_group()` but before `pgactive_join_group()`.
After you use `pgactive_exclude_table_replication_set()`, you can't use `pgactive_include_table_replication_set()`.
After you use `pgactive_include_table_replication_set()`, you can't use `pgactive_exclude_table_replication_set()`.
The system handles newly created tables differently based on your initial configuration:
+ If you excluded tables: Any new tables created after `pgactive_join_group()` are automatically included in replication
+ If you included tables: Any new tables created after `pgactive_join_group()` are automatically excluded from replication.
To view the replication set configuration for a specific table, use the `pgactive.pgactive_get_table_replication_sets()` function.
# pgactive functions reference
Following, you can find a list of pgactive functions with their parameters, return values, and practical usage notes to help you effectively use them:
## get\$1last\$1applied\$1xact\$1info
Retrieves the last applied transaction information for a specified node.
**Arguments**
+ sysid (text) - timeline OID
+ dboid (OID)
**Return type**
It records the following:
+ last\$1applied\$1xact\$1id (OID)
+ last\$1applied\$1xact\$1committs (timestamp with time zone)
+ last\$1applied\$1xact\$1at (timestamp with time zone)
**Usage notes**
Use this function to retrieve the last applied transaction information for a specified node.
## pgactive\$1apply\$1pause
Pauses the replication apply process.
**Arguments**
None
**Return type**
boolean
**Usage notes**
Call this function to pause the replication apply process.
## pgactive\$1apply\$1resume
Resumes the replication apply process.
**Arguments**
None
**Return type**
void
**Usage notes**
Call this function to resume the replication apply process.
## pgactive\$1is\$1apply\$1paused
Checks if replication apply is currently paused.
**Arguments**
None
**Return type**
boolean
**Usage notes**
Use this function to check if replication apply is currently paused.
## pgactive\$1create\$1group
Creates a pgactive group by converting a standalone database into the initial node.
**Arguments**
+ node\$1name (text)
+ node\$1dsn (text)
+ apply\$1delay integer DEFAULT NULL::integer - replication\$1sets text[] DEFAULT ARRAY[‘default’::text]
**Return type**
void
**Usage notes**
Creates a pgactive group by converting a standalone database into the initial node. The function performs sanity checks before transforming the node into a pgactive node. Before using this function, ensure that your PostgreSQL cluster has sufficient `max_worker_processes` available to support pgactive background workers.
## pgactive\$1detach\$1nodes
Removes specified nodes from the pgactive group.
**Arguments**
+ p\$1nodes (text[])
**Return type**
void
**Usage notes**
Use this function to remove specified nodes from the pgactive group.
## pgactive\$1exclude\$1table\$1replication\$1set
Excludes a specific table from replication.
**Arguments**
+ p\$1relation (regclass)
**Return type**
void
**Usage notes**
Use this function to exclude a specific table from replication.
## pgactive\$1get\$1replication\$1lag\$1info
Retrieves detailed replication lag information, including node details, WAL status, and LSN values.
**Arguments**
None
**Return type**
SETOF record - node\$1name text - node\$1sysid text - application\$1name text - slot\$1name text - active boolean - active\$1pid integer - pending\$1wal\$1decoding bigint - Approximate size of WAL in bytes to be decoded on the sender node - pending\$1wal\$1to\$1apply bigint - Approximate size of WAL in bytes to be applied on receiving node - restart\$1lsn pg\$1lsn - confirmed\$1flush\$1lsn pg\$1lsn - sent\$1lsn pg\$1lsn - write\$1lsn pg\$1lsn - flush\$1lsn pg\$1lsn - replay\$1lsn pg\$1lsn
**Usage notes**
Call this function to retrieve replication lag information, including node details, WAL status, and LSN values.
## pgactive\$1get\$1stats
Retrieves pgactive replication statistics.
**Arguments**
None
**Return type**
SETOF record - rep\$1node\$1id oid - rilocalid oid - riremoteid text - nr\$1commit bigint - nr\$1rollback bigint - nr\$1insert bigint - nr\$1insert\$1conflict bigint - nr\$1update bigint - nr\$1update\$1conflict bigint - nr\$1delete bigint - nr\$1delete\$1conflict bigint - nr\$1disconnect bigint
**Usage notes**
Use this function to retrieve pgactive replication statistics.
## pgactive\$1get\$1table\$1replication\$1sets
Gets replication set configuration for a specific relation.
**Arguments**
+ relation (regclass)
**Return type**
SETOF record
**Usage notes**
Call this function to get replication set configuration for a specific relation.
## pgactive\$1include\$1table\$1replication\$1set
Includes a specific table in replication.
**Arguments**
+ p\$1relation (regclass)
**Return type**
void
**Usage notes**
Use this function to include a specific table in replication.
## pgactive\$1join\$1group
Adds a node to an existing pgactive group.
**Arguments**
+ node\$1name (text)
+ node\$1dsn (text)
+ join\$1using\$1dsn (text)
+ apply\$1delay (integer, optional)
+ replication\$1sets (text[], default: ['default'])
+ bypass\$1collation\$1check (boolean, default: false)
+ bypass\$1node\$1identifier\$1creation (boolean, default: false)
+ bypass\$1user\$1tables\$1check (boolean, default: false)
**Return type**
void
**Usage notes**
Call this function to add a node to an existing pgactive group. Ensure your PostgreSQL cluster has sufficient max\$1worker\$1processes for pgactive background workers.
## pgactive\$1remove
Removes all pgactive components from the local node.
**Arguments**
+ force (boolean, default: false)
**Return type**
void
**Usage notes**
Call this function to remove all pgactive components from the local node.
## pgactive\$1snowflake\$1id\$1nextval
Generates node-specific unique sequence values.
**Arguments**
+ regclass
**Return type**
bigint
**Usage notes**
Use this function to generate node-specific unique sequence values.
## pgactive\$1update\$1node\$1conninfo
Updates connection information for a pgactive node.
**Arguments**
+ node\$1name\$1to\$1update (text)
+ node\$1dsn\$1to\$1update (text)
**Return type**
void
**Usage notes**
Use this function to update connection information for a pgactive node.
## pgactive\$1wait\$1for\$1node\$1ready
Monitors the progress of group creation or joining operations.
**Arguments**
+ timeout (integer, default: 0)
+ progress\$1interval (integer, default: 60)
**Return type**
void
**Usage notes**
Call this function to monitor the progress of group creation or joining operations.
# Handling conflicts in active-active replication
The `pgactive` extension works per database and not per cluster. Each DB instance that uses `pgactive` is an independent instance and can accept data changes from any source. When a change is sent to a DB instance, PostgreSQL commits it locally and then uses `pgactive` to replicate the change asynchronously to other DB instances. When two PostgreSQL DB instances update the same record at nearly the same time, a conflict can occur.
The `pgactive` extension provides mechanisms for conflict detection and automatic resolution. It tracks the time stamp when the transaction was committed on both the DB instances and automatically applies the change with the latest time stamp. The `pgactive` extension also logs when a conflict occurs in the `pgactive.pgactive_conflict_history` table.
The `pgactive.pgactive_conflict_history` will keep growing. You may want to define a purging policy. This can be done by deleting some records on a regular basis or defining a partitioning scheme for this relation (and later detach, drop, truncate partitions of interest). To implement the purging policy on a regular basis, one option is to use the `pg_cron` extension. See the following information of an example for the `pg_cron` history table, [Scheduling maintenance with the PostgreSQL pg\$1cron extension](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html).
# Handling sequences in active-active replication
An RDS for PostgreSQL DB instance with the `pgactive` extension uses two different sequence mechanisms to generate unique values.
**Global Sequences**
To use a global sequence, create a local sequence with the `CREATE SEQUENCE` statement. Use `pgactive.pgactive_snowflake_id_nextval(seqname)` instead of `usingnextval(seqname)` to get the next unique value of the sequence.
The following example creates a global sequence:
```
app=> CREATE TABLE gstest (
id bigint primary key,
parrot text
);
```
```
app=>CREATE SEQUENCE gstest_id_seq OWNED BY gstest.id;
```
```
app=> ALTER TABLE gstest \
ALTER COLUMN id SET DEFAULT \
pgactive.pgactive_snowflake_id_nextval('gstest_id_seq');
```
**Partitioned sequences**
In split-step or partitioned sequences, a normal PostgreSQL sequence is used on each node. Each sequence increments by the same amount and starts at different offsets. For example, with step 100, the node 1 generates sequence as 101, 201, 301, and so on and the node 2 generates sequence as 102, 202, 302, and so on. This scheme works well even if the nodes can't communicate for extended periods, but requires that the designer specify a maximum number of nodes when establishing the schema and requires per-node configuration. Mistakes can easily lead to overlapping sequences.
It is relatively simple to configure this approach with `pgactive` by creating the desired sequence on a node as follows:
```
CREATE TABLE some_table (generated_value bigint primary key);
```
```
app=> CREATE SEQUENCE some_seq INCREMENT 100 OWNED BY some_table.generated_value;
```
```
app=> ALTER TABLE some_table ALTER COLUMN generated_value SET DEFAULT nextval('some_seq');
```
Then call `setval` on each node to give a different offset starting value as follows.
```
app=>
-- On node 1
SELECT setval('some_seq', 1);
-- On node 2
SELECT setval('some_seq', 2);
```