# Using PostgreSQL extensions with Amazon RDS for PostgreSQL
You can extend the functionality of PostgreSQL by installing a variety of extensions and modules. For example, to work with spatial data you can install and use the PostGIS extension. For more information, see [Managing spatial data with the PostGIS extension](Appendix.PostgreSQL.CommonDBATasks.PostGIS.md). As another example, if you want to improve data entry for very large tables, you can consider partitioning your data by using the `pg_partman` extension. To learn more, see [Managing PostgreSQL partitions with the pg\$1partman extension](PostgreSQL_Partitions.md).
**Note**
RDS for PostgreSQL supports Trusted Language Extensions for PostgreSQL through the `pg_tle` extension, which you can add to your DB instance. By using this extension, developers can create their own PostgreSQL extensions in a safe environment that simplifies the setup and configuration requirements. To learn about RDS for PostgreSQL versions supporting `pg_tle` extension and for more information, see [Working with Trusted Language Extensions for PostgreSQL](PostgreSQL_trusted_language_extension.md).
In some cases, rather than installing an extension, you might add a specific module to the list of `shared_preload_libraries` in your RDS for PostgreSQL DB instance's custom DB parameter group. Typically, the default DB cluster parameter group loads only the `pg_stat_statements`, but several other modules are available to add to the list. For example, you can add scheduling capability by adding the `pg_cron` module, as detailed in [Scheduling maintenance with the PostgreSQL pg\$1cron extension](PostgreSQL_pg_cron.md). As another example, you can log query execution plans by loading the `auto_explain` module. To learn more, see [Logging execution plans of queries](https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-tune-query-performance/#) in the AWS knowledge center.
Depending on your version of RDS for PostgreSQL, installing an extension might require `rds_superuser` permissions, as follows:
+ For RDS for PostgreSQL versions 12 and earlier versions, installing extensions requires `rds_superuser` privileges.
+ For RDS for PostgreSQL version 13 and higher versions, users (roles) with create permissions on a given database instance can install and use any *trusted extensions*. For a list of trusted extensions, see [PostgreSQL trusted extensions](PostgreSQL.Concepts.General.FeatureSupport.Extensions.md#PostgreSQL.Concepts.General.Extensions.Trusted).
You can also specify precisely which extensions can be installed on your RDS for PostgreSQL DB instance, by listing them in the `rds.allowed_extensions` parameter. For more information, see [Restricting installation of PostgreSQL extensions](PostgreSQL.Concepts.General.FeatureSupport.Extensions.md#PostgreSQL.Concepts.General.FeatureSupport.Extensions.Restriction).
To learn more about the `rds_superuser` role, see [Understanding PostgreSQL roles and permissions](Appendix.PostgreSQL.CommonDBATasks.Roles.md).
**Topics**
+ [
# Using functions from the orafce extension
](Appendix.PostgreSQL.CommonDBATasks.orafce.md)
+ [
# Using Amazon RDS delegated extension support for PostgreSQL
](RDS_delegated_ext.md)
+ [
# Managing PostgreSQL partitions with the pg\$1partman extension
](PostgreSQL_Partitions.md)
+ [
# Using pgAudit to log database activity
](Appendix.PostgreSQL.CommonDBATasks.pgaudit.md)
+ [
# Scheduling maintenance with the PostgreSQL pg\$1cron extension
](PostgreSQL_pg_cron.md)
+ [
# Using pglogical to synchronize data across instances
](Appendix.PostgreSQL.CommonDBATasks.pglogical.md)
+ [
# Using pgactive to support active-active replication
](Appendix.PostgreSQL.CommonDBATasks.pgactive.md)
+ [
# Reducing bloat in tables and indexes with the pg\$1repack extension
](Appendix.PostgreSQL.CommonDBATasks.pg_repack.md)
+ [
# Upgrading and using the PLV8 extension
](PostgreSQL.Concepts.General.UpgradingPLv8.md)
+ [
# Using PL/Rust to write PostgreSQL functions in the Rust language
](PostgreSQL.Concepts.General.Using.PL_Rust.md)
+ [
# Managing spatial data with the PostGIS extension
](Appendix.PostgreSQL.CommonDBATasks.PostGIS.md)
# Using functions from the orafce extension
The orafce extension provides functions and operators that emulate a subset of functions and packages from an Oracle database. The orafce extension makes it easier for you to port an Oracle application to PostgreSQL. RDS for PostgreSQL versions 9.6.6 and higher support this extension. For more information about orafce, see [orafce](https://github.com/orafce/orafce) on GitHub.
**Note**
RDS for PostgreSQL doesn't support the `utl_file` package that is part of the orafce extension. This is because the `utl_file` schema functions provide read and write operations on operating-system text files, which requires superuser access to the underlying host. As a managed service, RDS for PostgreSQL doesn't provide host access.
**To use the orafce extension**
1. Connect to the DB instance with the primary user name that you used to create the DB instance.
If you want to turn on orafce for a different database in the same DB instance, use the `/c dbname` psql command. Using this command, you change from the primary database after initiating the connection.
1. Turn on the orafce extension with the `CREATE EXTENSION` statement.
```
CREATE EXTENSION orafce;
```
1. Transfer ownership of the oracle schema to the rds\$1superuser role with the `ALTER SCHEMA` statement.
```
ALTER SCHEMA oracle OWNER TO rds_superuser;
```
If you want to see the list of owners for the oracle schema, use the `\dn` psql command.
# Using Amazon RDS delegated extension support for PostgreSQL
Using Amazon RDS delegated extension support for PostgreSQL, you can delegate the extension management to a user who need not be an `rds_superuser`. With this delegated extension support, a new role called `rds_extension` is created and you must assign this to a user to manage other extensions. This role can create, update, and drop extensions.
You can specify the extensions that can be installed on your RDS DB instance, by listing them in the `rds.allowed_extensions` parameter. For more information, see [Using PostgreSQL extensions with Amazon RDS for PostgreSQL](https://docs.aws.amazon.com//AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Extensions.html).
You can restrict the list of extensions available that can be managed by the user with the `rds_extension` role using `rds.allowed_delegated_extensions` parameter.
The delegated extension support is available in the following versions:
+ All higher versions
+ 16.4 and higher 16 versions
+ 15.8 and higher 15 versions
+ 14.13 and higher 14 versions
+ 13.16 and higher 13 versions
+ 12.20 and higher 12 versions
**Topics**
+ [
## Turning on delegate extension support to a user
](#RDSPostgreSQL.delegated_ext_mgmt)
+ [
## Configuration used in RDS delegated extension support for PostgreSQL
](#RDSPostgreSQL.delegated_ext_config)
+ [
## Turning off the support for the delegated extension
](#RDSPostgreSQL.delegated_ext_disable)
+ [
## Benefits of using Amazon RDS delegated extension support
](#RDSPostgreSQL.delegated_ext_benefits)
+ [
## Limitation of Amazon RDS delegated extension support for PostgreSQL
](#RDSPostgreSQL.delegated_ext_limit)
+ [
## Permissions required for certain extensions
](#RDSPostgreSQL.delegated_ext_perm)
+ [
## Security Considerations
](#RDSPostgreSQL.delegated_ext_sec)
+ [
## Drop extension cascade disabled
](#RDSPostgreSQL.delegated_ext_drop)
+ [
## Example extensions that can be added using delegated extension support
](#RDSPostgreSQL.delegated_ext_support)
## Turning on delegate extension support to a user
You must perform the following to enable delegate extension support to a user:
1. **Grant `rds_extension` role to a user** – Connect to the database as `rds_superuser` and execute the following command:
```
Postgres => grant rds_extension to user_name;
```
1. **Set the list of extensions available for delegated users to manage** – The `rds.allowed_delegated_extensions` allows you to specify a subset of the available extensions using `rds.allowed_extensions` in the DB cluster parameter. You can perform this at one of the following levels:
+ In the cluster or the instance parameter group, through the AWS Management Console or API. For more information, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md).
+ Use the following command at the database level:
```
alter database database_name set rds.allowed_delegated_extensions = 'extension_name_1,
extension_name_2,...extension_name_n';
```
+ Use the following command at the user level:
```
alter user user_name set rds.allowed_delegated_extensions = 'extension_name_1,
extension_name_2,...extension_name_n';
```
**Note**
You need not restart the database after changing the `rds.allowed_delegated_extensions` dynamic parameter.
1. **Allow access to the delegated user to objects created during the extension creation process** – Certain extensions create objects that require additional permissions to be granted before the user with `rds_extension` role can access them. The `rds_superuser` must grant the delegated user access to those objects. One of the options is to use an event trigger to automatically grant permission to the delegated user.
**Example of event trigger**
If you want to allow a delegated user with `rds_extension` to use extensions that require setting permissions on their objects created by the extension creation, you can customize the below example of an event trigger and add only the extensions for which you want the delegated users to have access to the full functionality. This event trigger can be created on template1 (the default template), therefore all database created from template1 will have that event trigger. When a delegated user installs the extension, this trigger will automatically grant ownership on the objects created by the extension.
```
CREATE OR REPLACE FUNCTION create_ext()
RETURNS event_trigger AS $$
DECLARE
schemaname TEXT;
databaseowner TEXT;
r RECORD;
BEGIN
IF tg_tag = 'CREATE EXTENSION' and current_user != 'rds_superuser' THEN
RAISE NOTICE 'SECURITY INVOKER';
RAISE NOTICE 'user: %', current_user;
FOR r IN SELECT * FROM pg_catalog.pg_event_trigger_ddl_commands()
LOOP
CONTINUE WHEN r.command_tag != 'CREATE EXTENSION' OR r.object_type != 'extension';
schemaname = (
SELECT n.nspname
FROM pg_catalog.pg_extension AS e
INNER JOIN pg_catalog.pg_namespace AS n
ON e.extnamespace = n.oid
WHERE e.oid = r.objid
);
databaseowner = (
SELECT pg_catalog.pg_get_userbyid(d.datdba)
FROM pg_catalog.pg_database d
WHERE d.datname = current_database()
);
RAISE NOTICE 'Record for event trigger %, objid: %,tag: %, current_user: %, schema: %, database_owenr: %', r.object_identity, r.objid, tg_tag, current_user, schemaname, databaseowner;
IF r.object_identity = 'address_standardizer_data_us' THEN
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_gaz TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_lex TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.us_rules TO %I WITH GRANT OPTION;', schemaname, databaseowner);
ELSIF r.object_identity = 'dict_int' THEN
EXECUTE pg_catalog.format('ALTER TEXT SEARCH DICTIONARY %I.intdict OWNER TO %I;', schemaname, databaseowner);
ELSIF r.object_identity = 'pg_partman' THEN
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.part_config TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.part_config_sub TO %I WITH GRANT OPTION;', schemaname, databaseowner);
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE %I.custom_time_partitions TO %I WITH GRANT OPTION;', schemaname, databaseowner);
ELSIF r.object_identity = 'postgis_topology' THEN
EXECUTE pg_catalog.format('GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
EXECUTE pg_catalog.format('GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
EXECUTE pg_catalog.format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
EXECUTE pg_catalog.format('GRANT USAGE ON SCHEMA topology TO %I WITH GRANT OPTION;', databaseowner);
END IF;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE EVENT TRIGGER log_create_ext ON ddl_command_end EXECUTE PROCEDURE create_ext();
```
## Configuration used in RDS delegated extension support for PostgreSQL
| Configuration Name | Description | Default Value | Notes | Who can modify or grant permission |
| --- | --- | --- | --- | --- |
| `rds.allowed_delegated_extensions` | This parameter limits the extensions a rds\$1extension role can manage in a database. It must be a subset of rds.allowed\$1extensions. | empty string | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/RDS_delegated_ext.html) To learn more about setting up this parameter, see [Turning on delegate extension support to a user](#RDSPostgreSQL.delegated_ext_mgmt). | rds\$1superuser |
| `rds.allowed_extensions` | This parameter lets the customer limit the extensions that can be installed in the RDS DB instance. For more information, see [Restricting installation of PostgreSQL extensions ](https://docs.aws.amazon.com//AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions.Restriction) | "\$1" | By default, this parameter is set to "\$1", which means that all extensions supported on RDS for PostgreSQL and Aurora PostgreSQL are allowed to be created by users with necessary privileges. Empty means no extensions can be installed in the RDS DB instance. | administrator |
| `rds-delegated_extension_allow_drop_cascade` | This parameter controls the ability for user with `rds_extension` to drop the extension using a cascade option. | off | By default, `rds-delegated_extension_allow_drop_cascade` is set to `off`. This means that users with `rds_extension` are not allowed to drop an extension using the cascade option. To grant that ability, the `rds.delegated_extension_allow_drop_cascade` parameter should be set to `on`. | rds\$1superuser |
## Turning off the support for the delegated extension
**Turning off partially**
The delegated users can’t create new extensions but can still update existing extensions.
+ Reset `rds.allowed_delegated_extensions` to the default value in the DB cluster parameter group.
+ Use the following command at the database level:
```
alter database database_name reset rds.allowed_delegated_extensions;
```
+ Use the following command at the user level:
```
alter user user_name reset rds.allowed_delegated_extensions;
```
**Turning off fully**
Revoking `rds_extension` role from a user will revert the user to standard permissions. The user can no longer create, update, or drop extensions.
```
postgres => revoke rds_extension from user_name;
```
## Benefits of using Amazon RDS delegated extension support
By using Amazon RDS delegated extension support for PostgreSQL, you securely delegate the extension management to users who do not have the `rds_superuser` role. This feature provides the following benefits:
+ You can easily delegate extension management to users of your choice.
+ This doesn’t require `rds_superuser` role.
+ Provides ability to support different set of extensions for different databases in the same DB cluster.
## Limitation of Amazon RDS delegated extension support for PostgreSQL
+ Objects created during the extension creation process may require additional privileges for the extension to function properly.
+ Some extensions can't be managed by the delegated extension user by default, including the following: `log_fdw`, `pg_cron`, `pg_tle`, `pgactive`, `pglogical`, `postgis_raster`, `postgis_tiger_geocoder`, `postgis_topology`.
## Permissions required for certain extensions
In order to create, use, or update the following extensions, the delegated user should have the necessary privileges on the following functions, tables, and schema.
| Extensions that need ownership or permissions | Function | Tables | Schema | Text Search Dictionary | Comment |
| --- | --- | --- | --- | --- | --- |
| address\$1standardizer\$1data\$1us | none | us\$1gaz, us\$1lex, us\$1lex, I.us\$1rules | none | none | none |
| amcheck | bt\$1index\$1check, bt\$1index\$1parent\$1check | none | none | none | none |
| dict\$1int | none | none | none | intdict | none |
| pg\$1partman | none | custom\$1time\$1partitions, part\$1config, part\$1config\$1sub | none | none | none |
| pg\$1stat\$1statements | none | none | none | none | none |
| PostGIS | st\$1tileenvelope | spatial\$1ref\$1sys | none | none | none |
| postgis\$1raster | none | none | none | none | none |
| postgis\$1topology | none | topology, layer | topology | none | the delegated user Must be the database owner |
| log\$1fdw | create\$1foreign\$1table\$1for\$1log\$1file | none | none | none | none |
| rds\$1tools | role\$1password\$1encryption\$1type | none | none | none | none |
| postgis\$1tiger\$1geocoder | none | geocode\$1settings\$1default, geocode\$1settings | tiger | none | none |
| pg\$1freespacemap | pg\$1freespace | none | none | none | none |
| pg\$1visibility | pg\$1visibility | none | none | none | none |
## Security Considerations
Keep in mind that a user with `rds_extension` role will be able to manage extensions on all databases they have the connect privilege on. If the intention is to have a delegated user manage extension on a single database, a good practice is to revoke all privileges from public on each database, then explicitly grant the connect privilege for that specific database to the delegate user.
There are several extensions that can allow a user to access information from multiple database. Ensure the users you grant `rds_extension` has cross database capabilities before adding these extensions to `rds.allowed_delegated_extensions`. For example, `postgres_fdw` and `dblink` provide functionality to query across databases on the same instance or remote instances. `log_fdw` reads the postgres engine log files, which are for all databases in the instance, potentially containing slow queries or error messages from multiple databases. `pg_cron` enables running scheduled background jobs on the DB instance and can configure jobs to run in a different database.
## Drop extension cascade disabled
The ability to drop the extension with cascade option by a user with the `rds_extension` role is controlled by `rds.delegated_extension_allow_drop_cascade` parameter. By default, `rds-delegated_extension_allow_drop_cascade` is set to `off`. This means that users with the `rds_extension` role are not allowed to drop an extension using the cascade option as shown in the below query.
```
DROP EXTENSION CASCADE;
```
As this will automatically drop objects that depend on the extension, and in turn all objects that depend on those objects. Attempting to use the cascade option will result in an error.
To grant that ability, the `rds.delegated_extension_allow_drop_cascade` parameter should be set to `on`.
Changing the `rds.delegated_extension_allow_drop_cascade` dynamic parameter doesn't require a database restart. You can do this at one of the following levels:
+ In the cluster or the instance parameter group, through the AWS Management Console or API.
+ Using the following command at the database level:
```
alter database database_name set rds.delegated_extension_allow_drop_cascade = 'on';
```
+ Using the following command at the user level:
```
alter role tenant_user set rds.delegated_extension_allow_drop_cascade = 'on';
```
## Example extensions that can be added using delegated extension support
+ `rds_tools`
```
extension_test_db=> create extension rds_tools;
CREATE EXTENSION
extension_test_db=> SELECT * from rds_tools.role_password_encryption_type() where rolname = 'pg_read_server_files';
ERROR: permission denied for function role_password_encryption_type
```
+ `amcheck`
```
extension_test_db=> CREATE TABLE amcheck_test (id int);
CREATE TABLE
extension_test_db=> INSERT INTO amcheck_test VALUES (generate_series(1,100000));
INSERT 0 100000
extension_test_db=> CREATE INDEX amcheck_test_btree_idx ON amcheck_test USING btree (id);
CREATE INDEX
extension_test_db=> create extension amcheck;
CREATE EXTENSION
extension_test_db=> SELECT bt_index_check('amcheck_test_btree_idx'::regclass);
ERROR: permission denied for function bt_index_check
extension_test_db=> SELECT bt_index_parent_check('amcheck_test_btree_idx'::regclass);
ERROR: permission denied for function bt_index_parent_check
```
+ `pg_freespacemap`
```
extension_test_db=> create extension pg_freespacemap;
CREATE EXTENSION
extension_test_db=> SELECT * FROM pg_freespace('pg_authid');
ERROR: permission denied for function pg_freespace
extension_test_db=> SELECT * FROM pg_freespace('pg_authid',0);
ERROR: permission denied for function pg_freespace
```
+ `pg_visibility`
```
extension_test_db=> create extension pg_visibility;
CREATE EXTENSION
extension_test_db=> select * from pg_visibility('pg_database'::regclass);
ERROR: permission denied for function pg_visibility
```
+ `postgres_fdw`
```
extension_test_db=> create extension postgres_fdw;
CREATE EXTENSION
extension_test_db=> create server myserver foreign data wrapper postgres_fdw options (host 'foo', dbname 'foodb', port '5432');
ERROR: permission denied for foreign-data wrapper postgres_fdw
```
# Managing PostgreSQL partitions with the pg\$1partman extension
PostgreSQL table partitioning provides a framework for high-performance handling of data input and reporting. Use partitioning for databases that require very fast input of large amounts of data. Partitioning also provides for faster queries of large tables. Partitioning helps maintain data without impacting the database instance because it requires less I/O resources.
By using partitioning, you can split data into custom-sized chunks for processing. For example, you can partition time-series data for ranges such as hourly, daily, weekly, monthly, quarterly, yearly, custom, or any combination of these. For a time-series data example, if you partition the table by hour, each partition contains one hour of data. If you partition the time-series table by day, the partitions holds one day's worth of data, and so on. The partition key controls the size of a partition.
When you use an `INSERT` or `UPDATE` SQL command on a partitioned table, the database engine routes the data to the appropriate partition. PostgreSQL table partitions that store the data are child tables of the main table.
During database query reads, the PostgreSQL optimizer examines the `WHERE` clause of the query and, if possible, directs the database scan to only the relevant partitions.
Starting with version 10, PostgreSQL uses declarative partitioning to implement table partitioning. This is also known as native PostgreSQL partitioning. Before PostgreSQL version 10, you used triggers to implement partitions.
PostgreSQL table partitioning provides the following features:
+ Creation of new partitions at any time.
+ Variable partition ranges.
+ Detachable and reattachable partitions using data definition language (DDL) statements.
For example, detachable partitions are useful for removing historical data from the main partition but keeping historical data for analysis.
+ New partitions inherit the parent database table properties, including the following:
+ Indexes
+ Primary keys, which must include the partition key column
+ Foreign keys
+ Check constraints
+ References
+ Creating indexes for the full table or each specific partition.
You can't alter the schema for an individual partition. However, you can alter the parent table (such as adding a new column), which propagates to partitions.
**Topics**
+ [
## Overview of the PostgreSQL pg\$1partman extension
](#PostgreSQL_Partitions.pg_partman)
+ [
## Enabling the pg\$1partman extension
](#PostgreSQL_Partitions.enable)
+ [
## Configuring partitions using the create\$1parent function
](#PostgreSQL_Partitions.create_parent)
+ [
## Configuring partition maintenance using the run\$1maintenance\$1proc function
](#PostgreSQL_Partitions.run_maintenance_proc)
## Overview of the PostgreSQL pg\$1partman extension
You can use the PostgreSQL `pg_partman` extension to automate the creation and maintenance of table partitions. For more general information, see [PG Partition Manager](https://github.com/pgpartman/pg_partman) in the `pg_partman` documentation.
**Note**
The `pg_partman` extension is supported on RDS for PostgreSQL versions 12.5 and higher.
Instead of having to manually create each partition, you configure `pg_partman` with the following settings:
+ Table to be partitioned
+ Partition type
+ Partition key
+ Partition granularity
+ Partition precreation and management options
After you create a PostgreSQL partitioned table, you register it with `pg_partman` by calling the `create_parent` function. Doing this creates the necessary partitions based on the parameters you pass to the function.
The `pg_partman` extension also provides the `run_maintenance_proc` function, which you can call on a scheduled basis to automatically manage partitions. To ensure that the proper partitions are created as needed, schedule this function to run periodically (such as hourly). You can also ensure that partitions are automatically dropped.
## Enabling the pg\$1partman extension
If you have multiple databases inside the same PostgreSQL DB instance for which you want to manage partitions, enable the `pg_partman` extension separately for each database. To enable the `pg_partman` extension for a specific database, create the partition maintenance schema and then create the `pg_partman` extension as follows.
```
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman WITH SCHEMA partman;
```
**Note**
To create the `pg_partman` extension, make sure that you have `rds_superuser` privileges.
If you receive an error such as the following, grant the `rds_superuser` privileges to the account or use your superuser account.
```
ERROR: permission denied to create extension "pg_partman"
HINT: Must be superuser to create this extension.
```
To grant `rds_superuser` privileges, connect with your superuser account and run the following command.
```
GRANT rds_superuser TO user-or-role;
```
For the examples that show using the pg\$1partman extension, we use the following sample database table and partition. This database uses a partitioned table based on a timestamp. A schema `data_mart` contains a table named `events` with a column named `created_at`. The following settings are included in the `events` table:
+ Primary keys `event_id` and `created_at`, which must have the column used to guide the partition.
+ A check constraint `ck_valid_operation` to enforce values for an `operation` table column.
+ Two foreign keys, where one (`fk_orga_membership)` points to the external table `organization` and the other (`fk_parent_event_id`) is a self-referenced foreign key.
+ Two indexes, where one (`idx_org_id`) is for the foreign key and the other (`idx_event_type`) is for the event type.
The following DDL statements create these objects, which are automatically included on each partition.
```
CREATE SCHEMA data_mart;
CREATE TABLE data_mart.organization ( org_id BIGSERIAL,
org_name TEXT,
CONSTRAINT pk_organization PRIMARY KEY (org_id)
);
CREATE TABLE data_mart.events(
event_id BIGSERIAL,
operation CHAR(1),
value FLOAT(24),
parent_event_id BIGINT,
event_type VARCHAR(25),
org_id BIGSERIAL,
created_at timestamp,
CONSTRAINT pk_data_mart_event PRIMARY KEY (event_id, created_at),
CONSTRAINT ck_valid_operation CHECK (operation = 'C' OR operation = 'D'),
CONSTRAINT fk_orga_membership
FOREIGN KEY(org_id)
REFERENCES data_mart.organization (org_id),
CONSTRAINT fk_parent_event_id
FOREIGN KEY(parent_event_id, created_at)
REFERENCES data_mart.events (event_id,created_at)
) PARTITION BY RANGE (created_at);
CREATE INDEX idx_org_id ON data_mart.events(org_id);
CREATE INDEX idx_event_type ON data_mart.events(event_type);
```
## Configuring partitions using the create\$1parent function
After you enable the `pg_partman` extension, use the `create_parent` function to configure partitions inside the partition maintenance schema. The following example uses the `events` table example created in [Enabling the pg\$1partman extensionConfiguring partition maintenance using the run\$1maintenance\$1proc function](#PostgreSQL_Partitions.enable). Call the `create_parent` function as follows.
```
SELECT partman.create_parent(
p_parent_table => 'data_mart.events',
p_control => 'created_at',
p_type => 'range',
p_interval => '1 day',
p_premake => 30);
```
The parameters are as follows:
+ `p_parent_table` – The parent partitioned table. This table must already exist and be fully qualified, including the schema.
+ `p_control` – The column on which the partitioning is to be based. The data type must be an integer or time-based.
+ `p_type` – The type is either `'range'` or `'list'`.
+ `p_interval` – The time interval or integer range for each partition. Example values include `1 day`, `1 hour`, and so on.
+ `p_premake` – The number of partitions to create in advance to support new inserts.
For a complete description of the `create_parent` function, see [Creation Functions](https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#user-content-creation-functions) in the `pg_partman` documentation.
## Configuring partition maintenance using the run\$1maintenance\$1proc function
You can run partition maintenance operations to automatically create new partitions, detach partitions, or remove old partitions. Partition maintenance relies on the `run_maintenance_proc` function of the `pg_partman` extension and the `pg_cron` extension, which initiates an internal scheduler. The `pg_cron` scheduler automatically executes SQL statements, functions, and procedures defined in your databases.
The following example uses the `events` table example created in [Enabling the pg\$1partman extensionConfiguring partition maintenance using the run\$1maintenance\$1proc function](#PostgreSQL_Partitions.enable) to set partition maintenance operations to run automatically. As a prerequisite, add `pg_cron` to the `shared_preload_libraries` parameter in the DB instance's parameter group.
```
CREATE EXTENSION pg_cron;
UPDATE partman.part_config
SET infinite_time_partitions = true,
retention = '3 months',
retention_keep_table=true
WHERE parent_table = 'data_mart.events';
SELECT cron.schedule('@hourly', $$CALL partman.run_maintenance_proc()$$);
```
Following, you can find a step-by-step explanation of the preceding example:
1. Modify the parameter group associated with your DB instance and add `pg_cron` to the `shared_preload_libraries` parameter value. This change requires a DB instance restart for it to take effect. For more information, see [Modifying parameters in a DB parameter group in Amazon RDS](USER_WorkingWithParamGroups.Modifying.md).
1. Run the command `CREATE EXTENSION pg_cron;` using an account that has the `rds_superuser` permissions. Doing this enables the `pg_cron` extension. For more information, see [Scheduling maintenance with the PostgreSQL pg\$1cron extension](PostgreSQL_pg_cron.md).
1. Run the command `UPDATE partman.part_config` to adjust the `pg_partman` settings for the `data_mart.events` table.
1. Run the command `SET` . . . to configure the `data_mart.events` table, with these clauses:
1. `infinite_time_partitions = true,` – Configures the table to be able to automatically create new partitions without any limit.
1. `retention = '3 months',` – Configures the table to have a maximum retention of three months.
1. `retention_keep_table=true `– Configures the table so that when the retention period is due, the table isn't deleted automatically. Instead, partitions that are older than the retention period are only detached from the parent table.
1. Run the command `SELECT cron.schedule` . . . to make a `pg_cron` function call. This call defines how often the scheduler runs the `pg_partman` maintenance procedure, `partman.run_maintenance_proc`. For this example, the procedure runs every hour.
For a complete description of the `run_maintenance_proc` function, see [Maintenance Functions](https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#maintenance-functions) in the `pg_partman` documentation.
# Using pgAudit to log database activity
Financial institutions, government agencies, and many industries need to keep *audit logs* to meet regulatory requirements. By using the PostgreSQL Audit extension (pgAudit) with your RDS for PostgreSQL DB instance, you can capture the detailed records that are typically needed by auditors or to meet regulatory requirements. For example, you can set up the pgAudit extension to track changes made to specific databases and tables, to record the user who made the change, and many other details.
The pgAudit extension builds on the functionality of the native PostgreSQL logging infrastructure by extending the log messages with more detail. In other words, you use the same approach to view your audit log as you do to view any log messages. For more information about PostgreSQL logging, see [ RDS for PostgreSQL database log files](USER_LogAccess.Concepts.PostgreSQL.md).
The pgAudit extension redacts sensitive data such as cleartext passwords from the logs. If your RDS for PostgreSQL DB instance is configured to log data manipulation language (DML) statements as detailed in [Turning on query logging for your RDS for PostgreSQL DB instance](USER_LogAccess.Concepts.PostgreSQL.Query_Logging.md), you can avoid the cleartext password issue by using the PostgreSQL Audit extension.
You can configure auditing on your database instances with a great degree of specificity. You can audit all databases and all users. Or, you can choose to audit only certain databases, users, and other objects. You can also explicitly exclude certain users and databases from being audited. For more information, see [Excluding users or databases from audit logging](Appendix.PostgreSQL.CommonDBATasks.pgaudit.exclude-user-db.md).
Given the amount of detail that can be captured, we recommend that if you do use pgAudit, you monitor your storage consumption.
The pgAudit extension is supported on all available RDS for PostgreSQL versions. For a list of pgAudit versions supported by available RDS for PostgreSQL versions, see [Extension versions for Amazon RDS for PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html) in the *Amazon RDS for PostgreSQL Release Notes.*
**Topics**
+ [
# Setting up the pgAudit extension
](Appendix.PostgreSQL.CommonDBATasks.pgaudit.basic-setup.md)
+ [
# Auditing database objects
](Appendix.PostgreSQL.CommonDBATasks.pgaudit.auditing.md)
+ [
# Excluding users or databases from audit logging
](Appendix.PostgreSQL.CommonDBATasks.pgaudit.exclude-user-db.md)
+ [
# Reference for the pgAudit extension
](Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.md)
# Setting up the pgAudit extension
To set up the pgAudit extension on your RDS for PostgreSQL DB instance , you first add pgAudit to the shared libraries on the custom DB parameter group for your RDS for PostgreSQL DB instance. For information about creating a custom DB parameter group, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md). Next, you install the pgAudit extension. Finally, you specify the databases and objects that you want to audit. The procedures in this section show you how. You can use the AWS Management Console or the AWS CLI.
You must have permissions as the `rds_superuser` role to perform all these tasks.
The steps following assume that your RDS for PostgreSQL DB instance is associated with a custom DB parameter group.
## Console
**To set up the pgAudit extension**
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. Among the Instance details, find the **Parameter group** link.
1. Choose the link to open the custom parameters associated with your RDS for PostgreSQL DB instance.
1. In the **Parameters** search field, type `shared_pre` to find the `shared_preload_libraries` parameter.
1. Choose **Edit parameters** to access the property values.
1. Add `pgaudit` to the list in the **Values** field. Use a comma to separate items in the list of values.
![\[Image of the shared_preload_libaries parameter with pgAudit added.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/apg_rpg_shared_preload_pgaudit.png)
1. Reboot the RDS for PostgreSQL DB instance so that your change to the `shared_preload_libraries` parameter takes effect.
1. When the instance is available, verify that pgAudit has been initialized. Use `psql` to connect to the RDS for PostgreSQL DB instance, and then run the following command.
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)
```
1. With pgAudit initialized, you can now create the extension. You need to create the extension after initializing the library because the `pgaudit` extension installs event triggers for auditing data definition language (DDL) statements.
```
CREATE EXTENSION pgaudit;
```
1. Close the `psql` session.
```
labdb=> \q
```
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. Find the `pgaudit.log` parameter in the list and set to the appropriate value for your use case. For example, setting the `pgaudit.log` parameter to `write` as shown in the following image captures inserts, updates, deletes, and some other types changes to the log.
![\[Image of the pgaudit.log parameter with setting.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/rpg_set_pgaudit-log-level.png)
You can also choose one of the following values for the `pgaudit.log` parameter.
+ none – This is the default. No database changes are logged.
+ all – Logs everything (read, write, function, role, ddl, misc).
+ ddl – Logs all data definition language (DDL) statements that aren't included in the `ROLE` class.
+ function – Logs function calls and `DO` blocks.
+ misc – Logs miscellaneous commands, such as `DISCARD`, `FETCH`, `CHECKPOINT`, `VACUUM`, and `SET`.
+ read – Logs `SELECT` and `COPY` when the source is a relation (such as a table) or a query.
+ role – Logs statements related to roles and privileges, such as `GRANT`, `REVOKE`, `CREATE ROLE`, `ALTER ROLE`, and `DROP ROLE`.
+ write – Logs `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, and `COPY` when the destination is a relation (table).
1. Choose **Save changes**.
1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. Choose your RDS for PostgreSQL DB instance from the Databases list.
## AWS CLI
**To setup pgAudit**
To setup pgAudit using the AWS CLI, you call the [modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) operation to modify the audit log parameters in your custom parameter group, as shown in the following procedure.
1. Use the following AWS CLI command to add `pgaudit` to the `shared_preload_libraries` parameter.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot" \
--region aws-region
```
1. Use the following AWS CLI command to reboot the RDS for PostgreSQL DB instance so that the pgaudit library is initialized.
```
aws rds reboot-db-instance \
--db-instance-identifier your-instance \
--region aws-region
```
1. When the instance is available, you can verify that `pgaudit` has been initialized. Use `psql` to connect to the RDS for PostgreSQL DB instance, and then run the following command.
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pgaudit
(1 row)
```
With pgAudit initialized, you can now create the extension.
```
CREATE EXTENSION pgaudit;
```
1. Close the `psql` session so that you can use the AWS CLI.
```
labdb=> \q
```
1. Use the following AWS CLI command to specify the classes of statement that want logged by session audit logging. The example sets the `pgaudit.log` parameter to `write`, which captures inserts, updates, and deletes to the log.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=pgaudit.log,ParameterValue=write,ApplyMethod=pending-reboot" \
--region aws-region
```
You can also choose one of the following values for the `pgaudit.log` parameter.
+ none – This is the default. No database changes are logged.
+ all – Logs everything (read, write, function, role, ddl, misc).
+ ddl – Logs all data definition language (DDL) statements that aren't included in the `ROLE` class.
+ function – Logs function calls and `DO` blocks.
+ misc – Logs miscellaneous commands, such as `DISCARD`, `FETCH`, `CHECKPOINT`, `VACUUM`, and `SET`.
+ read – Logs `SELECT` and `COPY` when the source is a relation (such as a table) or a query.
+ role – Logs statements related to roles and privileges, such as `GRANT`, `REVOKE`, `CREATE ROLE`, `ALTER ROLE`, and `DROP ROLE`.
+ write – Logs `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, and `COPY` when the destination is a relation (table).
Reboot the RDS for PostgreSQL DB instance using the following AWS CLI command.
```
aws rds reboot-db-instance \
--db-instance-identifier your-instance \
--region aws-region
```
# Auditing database objects
With pgAudit set up on your RDS for PostgreSQL DB instance and configured for your requirements, more detailed information is captured in the PostgreSQL log. For example, while the default PostgreSQL logging configuration identifies the date and time that a change was made in a database table, with the pgAudit extension the log entry can include the schema, user who made the change, and other details depending on how the extension parameters are configured. You can set up auditing to track changes in the following ways.
+ For each session, by user. For the session level, you can capture the fully qualified command text.
+ For each object, by user and by database.
The object auditing capability is activated when you create the `rds_pgaudit` role on your system and then add this role to the `pgaudit.role` parameter in your custom parameter parameter group. By default, the `pgaudit.role` parameter is unset and the only allowable value is `rds_pgaudit`. The following steps assume that `pgaudit` has been initialized and that you have created the `pgaudit` extension by following the procedure in [Setting up the pgAudit extension](Appendix.PostgreSQL.CommonDBATasks.pgaudit.basic-setup.md).
![\[Image of the PostgreSQL log file after setting up pgAudit.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/pgaudit-log-example.png)
As shown in this example, the "LOG: AUDIT: SESSION" line provides information about the table and its schema, among other details.
**To set up object auditing**
1. Use `psql` to connect to the RDS for PostgreSQL DB instance.
```
psql --host=your-instance-name.aws-region.rds.amazonaws.com --port=5432 --username=postgrespostgres --password --dbname=labdb
```
1. Create a database role named `rds_pgaudit` using the following command.
```
labdb=> CREATE ROLE rds_pgaudit;
CREATE ROLE
labdb=>
```
1. Close the `psql` session.
```
labdb=> \q
```
In the next few steps, use the AWS CLI to modify the audit log parameters in your custom parameter group.
1. Use the following AWS CLI command to set the `pgaudit.role` parameter to `rds_pgaudit`. By default, this parameter is empty, and `rds_pgaudit` is the only allowable value.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot" \
--region aws-region
```
1. Use the following AWS CLI command to reboot the RDS for PostgreSQL DB instance so that your changes to the parameters take effect.
```
aws rds reboot-db-instance \
--db-instance-identifier your-instance \
--region aws-region
```
1. Run the following command to confirm that the `pgaudit.role` is set to `rds_pgaudit`.
```
SHOW pgaudit.role;
pgaudit.role
------------------
rds_pgaudit
```
To test pgAudit logging, you can run several example commands that you want to audit. For example, you might run the following commands.
```
CREATE TABLE t1 (id int);
GRANT SELECT ON t1 TO rds_pgaudit;
SELECT * FROM t1;
id
----
(0 rows)
```
The database logs should contain an entry similar to the following.
```
...
2017-06-12 19:09:49 UTC:...:rds_test@postgres:[11701]:LOG: AUDIT:
OBJECT,1,1,READ,SELECT,TABLE,public.t1,select * from t1;
...
```
For information on viewing the logs, see [Monitoring Amazon RDS log files](USER_LogAccess.md).
To learn more about the pgAudit extension, see [pgAudit](https://github.com/pgaudit/pgaudit/blob/master/README.md) on GitHub.
# Excluding users or databases from audit logging
As discussed in [ RDS for PostgreSQL database log files](USER_LogAccess.Concepts.PostgreSQL.md), PostgreSQL logs consume storage space. Using the pgAudit extension adds to the volume of data gathered in your logs to varying degrees, depending on the changes that you track. You might not need to audit every user or database in your RDS for PostgreSQL DB instance.
To minimize impacts to your storage and to avoid needlessly capturing audit records, you can exclude users and databases from being audited. You can also change logging within a given session. The following examples show you how.
**Note**
Parameter settings at the session level take precedence over the settings in the custom DB parameter group for the RDS for PostgreSQL DB instance. If you don't want database users to bypass your audit logging configuration settings, be sure to change their permissions.
Suppose that your RDS for PostgreSQL DB instance is configured to audit the same level of activity for all users and databases. You then decide that you don't want to audit the user `myuser`. You can turn off auditing for `myuser` with the following SQL command.
```
ALTER USER myuser SET pgaudit.log TO 'NONE';
```
Then, you can use the following query to check the `user_specific_settings` column for `pgaudit.log` to confirm that the parameter is set to `NONE`.
```
SELECT
usename AS user_name,
useconfig AS user_specific_settings
FROM
pg_user
WHERE
usename = 'myuser';
```
You see output such as the following.
```
user_name | user_specific_settings
-----------+------------------------
myuser | {pgaudit.log=NONE}
(1 row)
```
You can turn off logging for a given user in the midst of their session with the database with the following command.
```
ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'none';
```
Use the following query to check the settings column for pgaudit.log for a specific user and database combination.
```
SELECT
usename AS "user_name",
datname AS "database_name",
pg_catalog.array_to_string(setconfig, E'\n') AS "settings"
FROM
pg_catalog.pg_db_role_setting s
LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase
LEFT JOIN pg_catalog.pg_user r ON r.usesysid = setrole
WHERE
usename = 'myuser'
AND datname = 'mydatabase'
ORDER BY
1,
2;
```
You see output similar to the following.
```
user_name | database_name | settings
-----------+---------------+------------------
myuser | mydatabase | pgaudit.log=none
(1 row)
```
After turning off auditing for `myuser`, you decide that you don't want to track changes to `mydatabase`. You turn off auditing for that specific database by using the following command.
```
ALTER DATABASE mydatabase SET pgaudit.log to 'NONE';
```
Then, use the following query to check the database\$1specific\$1settings column to confirm that pgaudit.log is set to NONE.
```
SELECT
a.datname AS database_name,
b.setconfig AS database_specific_settings
FROM
pg_database a
FULL JOIN pg_db_role_setting b ON a.oid = b.setdatabase
WHERE
a.datname = 'mydatabase';
```
You see output such as the following.
```
database_name | database_specific_settings
---------------+----------------------------
mydatabase | {pgaudit.log=NONE}
(1 row)
```
To return settings to the default setting for myuser, use the following command:
```
ALTER USER myuser RESET pgaudit.log;
```
To return settings to their default setting for a database, use the following command.
```
ALTER DATABASE mydatabase RESET pgaudit.log;
```
To reset user and database to the default setting, use the following command.
```
ALTER USER myuser IN DATABASE mydatabase RESET pgaudit.log;
```
You can also capture specific events to the log by setting the `pgaudit.log` to one of the other allowed values for the `pgaudit.log` parameter. For more information, see [List of allowable settings for the `pgaudit.log` parameter](Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.md#Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.pgaudit-log-settings).
```
ALTER USER myuser SET pgaudit.log TO 'read';
ALTER DATABASE mydatabase SET pgaudit.log TO 'function';
ALTER USER myuser IN DATABASE mydatabase SET pgaudit.log TO 'read,function'
```
# Reference for the pgAudit extension
You can specify the level of detail that you want for your audit log by changing one or more of the parameters listed in this section.
## Controlling pgAudit behavior
You can control the audit logging by changing one or more of the parameters listed in the following table.
| Parameter | Description |
| --- | --- |
| `pgaudit.log` | Specifies the statement classes that will be logged by session audit logging. Allowable values include ddl, function, misc, read, role, write, none, all. For more information, see [List of allowable settings for the `pgaudit.log` parameter](#Appendix.PostgreSQL.CommonDBATasks.pgaudit.reference.pgaudit-log-settings). |
| `pgaudit.log_catalog` | When turned on (set to 1), adds statements to audit trail if all relations in a statement are in pg\$1catalog. |
| `pgaudit.log_level` | Specifies the log level to use for log entries. Allowed values: debug5, debug4, debug3, debug2, debug1, info, notice, warning, log |
| `pgaudit.log_parameter` | When turned on (set to 1), parameters passed with the statement are captured in the audit log. |
| `pgaudit.log_relation` | When turned on (set to 1), the audit log for the session creates a separate log entry for each relation (TABLE, VIEW, and so on) referenced in a SELECT or DML statement. |
| `pgaudit.log_statement_once` | Specifies whether logging will include the statement text and parameters with the first log entry for a statement/substatement combination or with every entry. |
| `pgaudit.role` | Specifies the master role to use for object audit logging. The only allowable entry is `rds_pgaudit`. |
## List of allowable settings for the `pgaudit.log` parameter
| Value | Description |
| --- | --- |
| none | This is the default. No database changes are logged. |
| all | Logs everything (read, write, function, role, ddl, misc). |
| ddl | Logs all data definition language (DDL) statements that aren't included in the `ROLE` class. |
| function | Logs function calls and `DO` blocks. |
| misc | Logs miscellaneous commands, such as `DISCARD`, `FETCH`, `CHECKPOINT`, `VACUUM`, and `SET`. |
| read | Logs `SELECT` and `COPY` when the source is a relation (such as a table) or a query. |
| role | Logs statements related to roles and privileges, such as `GRANT`, `REVOKE`, `CREATE ROLE`, `ALTER ROLE`, and `DROP ROLE`. |
| write | Logs `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, and `COPY` when the destination is a relation (table). |
To log multiple event types with session auditing, use a comma-separated list. To log all event types, set `pgaudit.log` to `ALL`. Reboot your DB instance to apply the changes.
With object auditing, you can refine audit logging to work with specific relations. For example, you can specify that you want audit logging for `READ` operations on one or more tables.
# Scheduling maintenance with the PostgreSQL pg\$1cron extension
You can use the PostgreSQL `pg_cron` extension to schedule maintenance commands within a PostgreSQL database. For more information about the extension, see [What is pg\$1cron?](https://github.com/citusdata/pg_cron) in the pg\$1cron documentation.
The `pg_cron` extension is supported on RDS for PostgreSQL engine versions 12.5 and higher.
To learn more about using `pg_cron`, see [Schedule jobs with pg\$1cron on your RDS for PostgreSQL or your Aurora PostgreSQL-Compatible Edition databases](https://aws.amazon.com/blogs/database/schedule-jobs-with-pg_cron-on-your-amazon-rds-for-postgresql-or-amazon-aurora-for-postgresql-databases/).
**Note**
The `pg_cron` extension version is displayed as a two digit version, for example, 1.6, in the pg\$1available\$1extensions view. While you might see three digit versions, for example, 1.6.4 or 1.6.5, listed in some contexts, you must specify the two digit version when performing an extension upgrade.
**Topics**
+ [
## Setting up the pg\$1cron extension
](#PostgreSQL_pg_cron.enable)
+ [
## Granting database users permissions to use pg\$1cron
](#PostgreSQL_pg_cron.permissions)
+ [
## Scheduling pg\$1cron jobs
](#PostgreSQL_pg_cron.examples)
+ [
## Reference for the pg\$1cron extension
](#PostgreSQL_pg_cron.reference)
## Setting up the pg\$1cron extension
Set up the `pg_cron` extension as follows:
1. Modify the custom parameter group associated with your PostgreSQL DB instance by adding `pg_cron` to the `shared_preload_libraries` parameter value.
+ If your RDS for PostgreSQL DB instance uses the `rds.allowed_extensions` parameter to explicitly list extensions that can be installed, you need to add the `pg_cron` extension to the list. Only certain versions of RDS for PostgreSQL support the `rds.allowed_extensions` parameter. By default, all available extensions are allowed. For more information, see [Restricting installation of PostgreSQL extensions](PostgreSQL.Concepts.General.FeatureSupport.Extensions.md#PostgreSQL.Concepts.General.FeatureSupport.Extensions.Restriction).
Restart the PostgreSQL DB instance to have changes to the parameter group take effect. To learn more about working with parameter groups, see [Modifying parameters in a DB parameter group in Amazon RDS](USER_WorkingWithParamGroups.Modifying.md).
1. After the PostgreSQL DB instance has restarted, run the following command using an account that has `rds_superuser` permissions. For example, if you used the default settings when you created your RDS for PostgreSQL DB instance, connect as user `postgres` and create the extension.
```
CREATE EXTENSION pg_cron;
```
The `pg_cron` scheduler is set in the default PostgreSQL database named `postgres`. The `pg_cron` objects are created in this `postgres` database and all scheduling actions run in this database.
1. You can use the default settings, or you can schedule jobs to run in other databases within your PostgreSQL DB instance. To schedule jobs for other databases within your PostgreSQL DB instance, see the example in [Scheduling a cron job for a database other than the default database](#PostgreSQL_pg_cron.otherDB).
## Granting database users permissions to use pg\$1cron
Installing the `pg_cron` extension requires the `rds_superuser` privileges. However, permissions to use the `pg_cron` can be granted (by a member of the `rds_superuser` group/role) to other database users, so that they can schedule their own jobs. We recommend that you grant permissions to the `cron` schema only as needed if it improves operations in your production environment.
To grant a database user permission in the `cron` schema, run the following command:
```
postgres=> GRANT USAGE ON SCHEMA cron TO db-user;
```
This gives *db-user* permission to access the `cron` schema to schedule cron jobs for the objects that they have permissions to access. If the database user doesn't have permissions, the job fails after posting the error message to the `postgresql.log` file, as shown in the following:
```
2020-12-08 16:41:00 UTC::@:[30647]:ERROR: permission denied for table table-name
2020-12-08 16:41:00 UTC::@:[27071]:LOG: background worker "pg_cron" (PID 30647) exited with exit code 1
```
In other words, make sure that database users that are granted permissions on the `cron` schema also have permissions on the objects (tables, schemas, and so on) that they plan to schedule.
The details of the cron job and its success or failure are also captured in the `cron.job_run_details` table. For more information, see [Tables for scheduling jobs and capturing status](#PostgreSQL_pg_cron.tables).
## Scheduling pg\$1cron jobs
The following sections show how you can schedule various management tasks using `pg_cron` jobs.
**Note**
When you create `pg_cron` jobs, check that the `max_worker_processes` setting is larger than the number of `cron.max_running_jobs`. A `pg_cron` job fails if it runs out of background worker processes. The default number of `pg_cron` jobs is `5`. For more information, see [Parameters for managing the pg\$1cron extension](#PostgreSQL_pg_cron.parameters).
**Topics**
+ [
### Vacuuming a table
](#PostgreSQL_pg_cron.vacuum)
+ [
### Purging the pg\$1cron history table
](#PostgreSQL_pg_cron.job_run_details)
+ [
### Logging errors to the postgresql.log file only
](#PostgreSQL_pg_cron.log_run)
+ [
### Scheduling a cron job for a database other than the default database
](#PostgreSQL_pg_cron.otherDB)
### Vacuuming a table
Autovacuum handles vacuum maintenance for most cases. However, you might want to schedule a vacuum of a specific table at a time of your choosing.
See also, [Working with PostgreSQL autovacuum on Amazon RDS for PostgreSQL](Appendix.PostgreSQL.CommonDBATasks.Autovacuum.md).
Following is an example of using the `cron.schedule` function to set up a job to use `VACUUM FREEZE` on a specific table every day at 22:00 (GMT).
```
SELECT cron.schedule('manual vacuum', '0 22 * * *', 'VACUUM FREEZE pgbench_accounts');
schedule
----------
1
(1 row)
```
After the preceding example runs, you can check the history in the `cron.job_run_details` table as follows.
```
postgres=> SELECT * FROM cron.job_run_details;
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
-------+-------+---------+----------+----------+--------------------------------+-----------+----------------+-------------------------------+-------------------------------
1 | 1 | 3395 | postgres | adminuser| vacuum freeze pgbench_accounts | succeeded | VACUUM | 2020-12-04 21:10:00.050386+00 | 2020-12-04 21:10:00.072028+00
(1 row)
```
Following is a query of the `cron.job_run_details` table to see the failed jobs.
```
postgres=> SELECT * FROM cron.job_run_details WHERE status = 'failed';
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
------+-------+---------+----------+----------+-------------------------------+--------+--------------------------------------------------+-------------------------------+------------------------------
5 | 4 | 30339 | postgres | adminuser| vacuum freeze pgbench_account | failed | ERROR: relation "pgbench_account" does not exist | 2020-12-04 21:48:00.015145+00 | 2020-12-04 21:48:00.029567+00
(1 row)
```
For more information, see [Tables for scheduling jobs and capturing status](#PostgreSQL_pg_cron.tables).
### Purging the pg\$1cron history table
The `cron.job_run_details` table contains a history of cron jobs that can become very large over time. We recommend that you schedule a job that purges this table. For example, keeping a week's worth of entries might be sufficient for troubleshooting purposes.
The following example uses the [cron.schedule](#PostgreSQL_pg_cron.schedule) function to schedule a job that runs every day at midnight to purge the `cron.job_run_details` table. The job keeps only the last seven days. Use your `rds_superuser` account to schedule the job such as the following.
```
SELECT cron.schedule('0 0 * * *', $$DELETE
FROM cron.job_run_details
WHERE end_time < now() - interval '7 days'$$);
```
For more information, see [Tables for scheduling jobs and capturing status](#PostgreSQL_pg_cron.tables).
### Logging errors to the postgresql.log file only
To prevent writing to the `cron.job_run_details` table, modify the parameter group associated with the PostgreSQL DB instance and set the `cron.log_run` parameter to off. The `pg_cron` extension no longer writes to the table and captures errors to the `postgresql.log` file only. For more information, see [Modifying parameters in a DB parameter group in Amazon RDS](USER_WorkingWithParamGroups.Modifying.md).
Use the following command to check the value of the `cron.log_run` parameter.
```
postgres=> SHOW cron.log_run;
```
For more information, see [Parameters for managing the pg\$1cron extension](#PostgreSQL_pg_cron.parameters).
### Scheduling a cron job for a database other than the default database
The metadata for `pg_cron` is all held in the PostgreSQL default database named `postgres`. Because background workers are used for running the maintenance cron jobs, you can schedule a job in any of your databases within the PostgreSQL DB instance:
**Note**
Only users with `rds_superuser` role or `rds_superuser` privileges can list all cron jobs in the database. Other users can view only their own jobs in the `cron.job` table.
1. In the cron database, schedule the job as you normally do using the [cron.schedule](#PostgreSQL_pg_cron.schedule).
```
postgres=> SELECT cron.schedule('database1 manual vacuum', '29 03 * * *', 'vacuum freeze test_table');
```
1. As a user with the `rds_superuser` role, update the database column for the job that you just created so that it runs in another database within your PostgreSQL DB instance.
```
postgres=> UPDATE cron.job SET database = 'database1' WHERE jobid = 106;
```
1. Verify by querying the `cron.job` table.
```
postgres=> SELECT * FROM cron.job;
jobid | schedule | command | nodename | nodeport | database | username | active | jobname
------+-------------+--------------------------------+-----------+----------+----------+-----------+--------+-------------------------
106 | 29 03 * * * | vacuum freeze test_table | localhost | 8192 | database1| adminuser | t | database1 manual vacuum
1 | 59 23 * * * | vacuum freeze pgbench_accounts | localhost | 8192 | postgres | adminuser | t | manual vacuum
(2 rows)
```
**Note**
In some situations, you might add a cron job that you intend to run on a different database. In such cases, the job might try to run in the default database (`postgres`) before you update the correct database column. If the user name has permissions, the job successfully runs in the default database.
## Reference for the pg\$1cron extension
You can use the following parameters, functions, and tables with the `pg_cron` extension. For more information, see [What is pg\$1cron?](https://github.com/citusdata/pg_cron) in the pg\$1cron documentation.
**Topics**
+ [
### Parameters for managing the pg\$1cron extension
](#PostgreSQL_pg_cron.parameters)
+ [
### Function reference: cron.schedule
](#PostgreSQL_pg_cron.schedule)
+ [
### Function reference: cron.unschedule
](#PostgreSQL_pg_cron.unschedule)
+ [
### Tables for scheduling jobs and capturing status
](#PostgreSQL_pg_cron.tables)
### Parameters for managing the pg\$1cron extension
Following is a list of parameters that control the `pg_cron` extension behavior.
| Parameter | Description |
| --- | --- |
| cron.database\$1name | The database in which `pg_cron` metadata is kept. |
| cron.host | The hostname to connect to PostgreSQL. You can't modify this value. |
| cron.log\$1run | Log every job that runs in the `job_run_details` table. Values are `on` or `off`. For more information, see [Tables for scheduling jobs and capturing status](#PostgreSQL_pg_cron.tables). |
| cron.log\$1statement | Log all cron statements before running them. Values are `on` or `off`. |
| cron.max\$1running\$1jobs | The maximum number of jobs that can run concurrently. |
| cron.use\$1background\$1workers | Use background workers instead of client sessions. You can't modify this value. |
Use the following SQL command to display these parameters and their values.
```
postgres=> SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE 'cron.%' ORDER BY name;
```
### Function reference: cron.schedule
This function schedules a cron job. The job is initially scheduled in the default `postgres` database. The function returns a `bigint` value representing the job identifier. To schedule jobs to run in other databases within your PostgreSQL DB instance, see the example in [Scheduling a cron job for a database other than the default database](#PostgreSQL_pg_cron.otherDB).
The function has two syntax formats.
**Syntax**
```
cron.schedule (job_name,
schedule,
command
);
cron.schedule (schedule,
command
);
```
**Parameters**
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html)
**Examples**
```
postgres=> SELECT cron.schedule ('test','0 10 * * *', 'VACUUM pgbench_history');
schedule
----------
145
(1 row)
postgres=> SELECT cron.schedule ('0 15 * * *', 'VACUUM pgbench_accounts');
schedule
----------
146
(1 row)
```
### Function reference: cron.unschedule
This function deletes a cron job. You can specify either the `job_name` or the `job_id`. A policy makes sure that you are the owner to remove the schedule for the job. The function returns a Boolean indicating success or failure.
The function has the following syntax formats.
**Syntax**
```
cron.unschedule (job_id);
cron.unschedule (job_name);
```
**Parameters**
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html)
**Examples**
```
postgres=> SELECT cron.unschedule(108);
unschedule
------------
t
(1 row)
postgres=> SELECT cron.unschedule('test');
unschedule
------------
t
(1 row)
```
### Tables for scheduling jobs and capturing status
The following tables are used to schedule the cron jobs and record how the jobs completed.
| Table | Description |
| --- | --- |
| cron.job | Contains the metadata about each scheduled job. Most interactions with this table should be done by using the `cron.schedule` and `cron.unschedule` functions. We recommend that you don't give update or insert privileges directly to this table. Doing so would allow the user to update the `username` column to run as `rds-superuser`. |
| cron.job\$1run\$1details | Contains historic information about past scheduled jobs that ran. This is useful to investigate the status, return messages, and start and end time from the job that ran. To prevent this table from growing indefinitely, purge it on a regular basis. For an example, see [Purging the pg\$1cron history table](#PostgreSQL_pg_cron.job_run_details). |
# Using pglogical to synchronize data across instances
All currently available RDS for PostgreSQL versions support the `pglogical` extension. The pglogical extension predates the functionally similar logical replication feature that was introduced by PostgreSQL in version 10. For more information, see [Performing logical replication for Amazon RDS for PostgreSQL](PostgreSQL.Concepts.General.FeatureSupport.LogicalReplication.md).
The `pglogical` extension supports logical replication between two or more RDS for PostgreSQL DB instances. It also supports replication between different PostgreSQL versions, and between databases running on RDS for PostgreSQL DB instances and Aurora PostgreSQL DB clusters. The `pglogical` extension uses a publish-subscribe model to replicate changes to tables and other objects, such as sequences, from a publisher to a subscriber. It relies on a replication slot to ensure that changes are synchronized from a publisher node to a subscriber node, defined as follows.
+ The *publisher node* is the RDS for PostgreSQL DB instance that's the source of data to be replicated to other nodes. The publisher node defines the tables to be replicated in a publication set.
+ The *subscriber node* is the RDS for PostgreSQL DB instance that receives WAL updates from the publisher. The subscriber creates a subscription to connect to the publisher and get the decoded WAL data. When the subscriber creates the subscription, the replication slot is created on the publisher node.
Following, you can find information about setting up the `pglogical` extension.
**Topics**
+ [
## Requirements and limitations for the pglogical extension
](#Appendix.PostgreSQL.CommonDBATasks.pglogical.requirements-limitations)
+ [
# Setting up the pglogical extension
](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md)
+ [
# Setting up logical replication for RDS for PostgreSQL DB instance
](Appendix.PostgreSQL.CommonDBATasks.pglogical.setup-replication.md)
+ [
# Reestablishing logical replication after a major upgrade
](Appendix.PostgreSQL.CommonDBATasks.pglogical.recover-replication-after-upgrade.md)
+ [
# Managing logical replication slots for RDS for PostgreSQL
](Appendix.PostgreSQL.CommonDBATasks.pglogical.handle-slots.md)
+ [
# Parameter reference for the pglogical extension
](Appendix.PostgreSQL.CommonDBATasks.pglogical.reference.md)
## Requirements and limitations for the pglogical extension
All currently available releases of RDS for PostgreSQL support the `pglogical` extension.
Both the publisher node and the subscriber node must be set up for logical replication.
The tables that you want to replicate from a publisher to a subscriber must have the same names and the same schema. These tables must also contain the same columns, and the columns must use the same data types. Both publisher and subscriber tables must have the same primary keys. We recommend that you use only the PRIMARY KEY as the unique constraint.
The tables on the subscriber node can have more permissive constraints than those on the publisher node for CHECK constraints and NOT NULL constraints.
The `pglogical` extension provides features such as two-way replication that aren't supported by the logical replication feature built into PostgreSQL (version 10 and higher). For more information, see [PostgreSQL bi-directional replication using pglogical](https://aws.amazon.com/blogs/database/postgresql-bi-directional-replication-using-pglogical/).
# Setting up the pglogical extension
To set up the `pglogical` extension on your RDS for PostgreSQL DB instance , you add `pglogical` to the shared libraries on the custom DB parameter group for your RDS for PostgreSQL DB instance. You also need to set the value of the `rds.logical_replication` parameter to `1`, to turn on logical decoding. Finally, you create the extension in the database. You can use the AWS Management Console or the AWS CLI for these tasks.
You must have permissions as the `rds_superuser` role to perform these tasks.
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 set up the pglogical extension**
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. Among the Instance details, find the **Parameter group** link.
1. Choose the link to open the custom parameters associated with your RDS for PostgreSQL DB instance.
1. In the **Parameters** search field, type `shared_pre` to find the `shared_preload_libraries` parameter.
1. Choose **Edit parameters** to access the property values.
1. Add `pglogical` to the list in the **Values** field. Use a comma to separate items in the list of values.
![\[Image of the shared_preload_libraries parameter with pglogical added.\]](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/images/apg_rpg_shared_preload_pglogical.png)
1. Find the `rds.logical_replication` parameter and set it to `1`, to turn on logical replication.
1. Reboot the RDS for PostgreSQL DB instance so that your changes take effect.
1. When the instance is available, you can use `psql` (or pgAdmin) to connect to the RDS for PostgreSQL DB instance.
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. To verify that pglogical is initialized, run the following command.
```
SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
rdsutils,pglogical
(1 row)
```
1. Verify the setting that enables logical decoding, as follows.
```
SHOW wal_level;
wal_level
-----------
logical
(1 row)
```
1. Create the extension, as follows.
```
CREATE EXTENSION pglogical;
EXTENSION CREATED
```
1. Choose **Save changes**.
1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. Choose your RDS for PostgreSQL DB instance from the Databases list to select it, and then choose **Reboot** from the Actions menu.
## AWS CLI
**To setup the pglogical extension**
To setup pglogical using the AWS CLI, you 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 add `pglogical` to the `shared_preload_libraries` parameter.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=shared_preload_libraries,ParameterValue=pglogical,ApplyMethod=pending-reboot" \
--region aws-region
```
1. Use the following AWS CLI command to set `rds.logical_replication` to `1` to turn on the logical decoding capability for the RDS for PostgreSQL DB instance.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=rds.logical_replication,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 pglogical 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=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. Create the extension, as follows.
```
CREATE EXTENSION pglogical;
EXTENSION CREATED
```
1. Reboot the RDS for PostgreSQL DB instance using the following AWS CLI command.
```
aws rds reboot-db-instance \
--db-instance-identifier your-instance \
--region aws-region
```
# Setting up logical replication for RDS for PostgreSQL DB instance
The following procedure shows you how to start logical replication between two RDS for PostgreSQL DB instances. The steps assume that both the source (publisher) and the target (subscriber) have the `pglogical` extension set up as detailed in [Setting up the pglogical extension](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md).
**Note**
The `node_name` of a subscriber node can't start with `rds`.
**To create the publisher node and define the tables to replicate**
These steps assume that your RDS for PostgreSQL DB instance has a database that has one or more tables that you want to replicate to another node. You need to recreate the table structure from the publisher on the subscriber, so first, get the table structure if necessary. You can do that by using the `psql` metacommand `\d tablename` and then creating the same table on the subscriber instance. The following procedure creates an example table on the publisher (source) for demonstration purposes.
1. Use `psql` to connect to the instance that has the table you want to use as a source for subscribers.
```
psql --host=source-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
If you don't have an existing table that you want to replicate, you can create a sample table as follows.
1. Create an example table using the following SQL statement.
```
CREATE TABLE docs_lab_table (a int PRIMARY KEY);
```
1. Populate the table with generated data by using the following SQL statement.
```
INSERT INTO docs_lab_table VALUES (generate_series(1,5000));
INSERT 0 5000
```
1. Verify that data exists in the table by using the following SQL statement.
```
SELECT count(*) FROM docs_lab_table;
```
1. Identify this RDS for PostgreSQL DB instance as the publisher node, as follows.
```
SELECT pglogical.create_node(
node_name := 'docs_lab_provider',
dsn := 'host=source-instance.aws-region.rds.amazonaws.com port=5432 dbname=labdb');
create_node
-------------
3410995529
(1 row)
```
1. Add the table that you want to replicate to the default replication set. For more information about replication sets, see [Replication sets](https://github.com/2ndQuadrant/pglogical/tree/REL2_x_STABLE/docs#replication-sets) in the pglogical documentation.
```
SELECT pglogical.replication_set_add_table('default', 'docs_lab_table', 'true', NULL, NULL);
replication_set_add_table
---------------------------
t
(1 row)
```
The publisher node setup is complete. You can now set up the subscriber node to receive the updates from the publisher.
**To set up the subscriber node and create a subscription to receive updates**
These steps assume that the RDS for PostgreSQL DB instance has been set up with the `pglogical` extension. For more information, see [Setting up the pglogical extension](Appendix.PostgreSQL.CommonDBATasks.pglogical.basic-setup.md).
1. Use `psql` to connect to the instance that you want to receive updates from the publisher.
```
psql --host=target-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. On the subscriber RDS for PostgreSQL DB instance,create the same table that exists on the publisher. For this example, the table is `docs_lab_table`. You can create the table as follows.
```
CREATE TABLE docs_lab_table (a int PRIMARY KEY);
```
1. Verify that this table is empty.
```
SELECT count(*) FROM docs_lab_table;
count
-------
0
(1 row)
```
1. Identify this RDS for PostgreSQL DB instance as the subscriber node, as follows.
```
SELECT pglogical.create_node(
node_name := 'docs_lab_target',
dsn := 'host=target-instance.aws-region.rds.amazonaws.com port=5432 sslmode=require dbname=labdb user=postgres password=********');
create_node
-------------
2182738256
(1 row)
```
1. Create the subscription.
```
SELECT pglogical.create_subscription(
subscription_name := 'docs_lab_subscription',
provider_dsn := 'host=source-instance.aws-region.rds.amazonaws.com port=5432 sslmode=require dbname=labdb user=postgres password=*******',
replication_sets := ARRAY['default'],
synchronize_data := true,
forward_origins := '{}' );
create_subscription
---------------------
1038357190
(1 row)
```
When you complete this step, the data from the table on the publisher is created in the table on the subscriber. You can verify that this has occurred by using the following SQL query.
```
SELECT count(*) FROM docs_lab_table;
count
-------
5000
(1 row)
```
From this point forward, changes made to the table on the publisher are replicated to the table on the subscriber.
# Reestablishing logical replication after a major upgrade
Before you can perform a major version upgrade of an RDS for PostgreSQL DB instance that's set up as a publisher node for logical replication, you must drop all replication slots, even those that aren't active. We recommend that you temporarily divert database transactions from the publisher node, drop the replication slots, upgrade the RDS for PostgreSQL DB instance, and then re-establish and restart replication.
The replication slots are hosted on the publisher node only. The RDS for PostgreSQL subscriber node in a logical replication scenario has no slots to drops, but it can't be upgraded to a major version while it's designated as a subscriber node with a subscription to the publisher. Before upgrading the RDS for PostgreSQL subscriber node, drop the subscription and the node. For more information, see [Managing logical replication slots for RDS for PostgreSQL](Appendix.PostgreSQL.CommonDBATasks.pglogical.handle-slots.md).
## Determining that logical replication has been disrupted
You can determine that the replication process has been disrupted by querying either the publisher node or the subscriber node, as follows.
**To check the publisher node**
+ Use `psql` to connect to the publisher node, and then query the `pg_replication_slots` function. Note the value in the active column. Normally, this will return `t` (true), showing that replication is active. If the query returns `f` (false), it's an indication that replication to the subscriber has stopped.
```
SELECT slot_name,plugin,slot_type,active FROM pg_replication_slots;
slot_name | plugin | slot_type | active
-------------------------------------------+------------------+-----------+--------
pgl_labdb_docs_labcb4fa94_docs_lab3de412c | pglogical_output | logical | f
(1 row)
```
**To check the subscriber node**
On the subscriber node, you can check the status of replication in three different ways.
+ Look through the PostgreSQL logs on the subscriber node to find failure messages. The log identifies failure with messages that include exit code 1, as shown following.
```
2022-07-06 16:17:03 UTC::@:[7361]:LOG: background worker "pglogical apply 16404:2880255011" (PID 14610) exited with exit code 1
2022-07-06 16:19:44 UTC::@:[7361]:LOG: background worker "pglogical apply 16404:2880255011" (PID 21783) exited with exit code 1
```
+ Query the `pg_replication_origin` function. Connect to the database on the subscriber node using `psql` and query the `pg_replication_origin` function, as follows.
```
SELECT * FROM pg_replication_origin;
roident | roname
---------+--------
(0 rows)
```
The empty result set means that replication has been disrupted. Normally, you see output such as the following.
```
roident | roname
---------+----------------------------------------------------
1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
(1 row)
```
+ Query the `pglogical.show_subscription_status` function as shown in the following example.
```
SELECT subscription_name,status,slot_name FROM pglogical.show_subscription_status();
subscription_name | status | slot_name
---====----------------+--------+-------------------------------------
docs_lab_subscription | down | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
(1 row)
```
This output shows that replication has been disrupted. Its status is `down`. Normally, the output shows the status as `replicating`.
If your logical replication process has been disrupted, you can re-establish replication by following these steps.
**To reestablish logical replication between publisher and subscriber nodes**
To re-establish replication, you first disconnect the subscriber from the publisher node and then re-establish the subscription, as outlined in these steps.
1. Connect to the subscriber node using `psql` as follows.
```
psql --host=222222222222.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
```
1. Deactivate the subscription by using the `pglogical.alter_subscription_disable` function.
```
SELECT pglogical.alter_subscription_disable('docs_lab_subscription',true);
alter_subscription_disable
----------------------------
t
(1 row)
```
1. Get the publisher node's identifier by querying the `pg_replication_origin`, as follows.
```
SELECT * FROM pg_replication_origin;
roident | roname
---------+-------------------------------------
1 | pgl_labdb_docs_labcb4fa94_docs_lab3de412c
(1 row)
```
1. Use the response from the previous step with the `pg_replication_origin_create` command to assign the identifier that can be used by the subscription when re-established.
```
SELECT pg_replication_origin_create('pgl_labdb_docs_labcb4fa94_docs_lab3de412c');
pg_replication_origin_create
------------------------------
1
(1 row)
```
1. Turn on the subscription by passing its name with a status of `true`, as shown in the following example.
```
SELECT pglogical.alter_subscription_enable('docs_lab_subscription',true);
alter_subscription_enable
---------------------------
t
(1 row)
```
Check the status of the node. Its status should be `replicating` as shown in this example.
```
SELECT subscription_name,status,slot_name
FROM pglogical.show_subscription_status();
subscription_name | status | slot_name
-------------------------------+-------------+-------------------------------------
docs_lab_subscription | replicating | pgl_labdb_docs_lab98f517b_docs_lab3de412c
(1 row)
```
Check the status of the subscriber's replication slot on the publisher node. The slot's `active` column should return `t` (true), indicating that replication has been re-established.
```
SELECT slot_name,plugin,slot_type,active
FROM pg_replication_slots;
slot_name | plugin | slot_type | active
-------------------------------------------+------------------+-----------+--------
pgl_labdb_docs_lab98f517b_docs_lab3de412c | pglogical_output | logical | t
(1 row)
```
# Managing logical replication slots for RDS for PostgreSQL
Before you can perform a major version upgrade on an RDS for PostgreSQL DB instance that's serving as a publisher node in a logical replication scenario, you must drop the replication slots on the instance. The major version upgrade pre-check process notifies you that the upgrade can't proceed until the slots are dropped.
To drop slots from your RDS for PostgreSQL DB instance, first drop the subscription and then drop the slot.
To identify replication slots that were created using the `pglogical` extension, log in to each database and get the name of the nodes. When you query the subscriber node, you get both the publisher and the subscriber nodes in the output, as shown in this example.
```
SELECT * FROM pglogical.node;
node_id | node_name
------------+-------------------
2182738256 | docs_lab_target
3410995529 | docs_lab_provider
(2 rows)
```
You can get the details about the subscription with the following query.
```
SELECT sub_name,sub_slot_name,sub_target
FROM pglogical.subscription;
sub_name | sub_slot_name | sub_target
----------+--------------------------------+------------
docs_lab_subscription | pgl_labdb_docs_labcb4fa94_docs_lab3de412c | 2182738256
(1 row)
```
You can now drop the subscription, as follows.
```
SELECT pglogical.drop_subscription(subscription_name := 'docs_lab_subscription');
drop_subscription
-------------------
1
(1 row)
```
After dropping the subscription, you can delete the node.
```
SELECT pglogical.drop_node(node_name := 'docs-lab-subscriber');
drop_node
-----------
t
(1 row)
```
You can verify that the node no longer exists, as follows.
```
SELECT * FROM pglogical.node;
node_id | node_name
---------+-----------
(0 rows)
```
# Parameter reference for the pglogical extension
In the table you can find parameters associated with the `pglogical` extension. Parameters such as `pglogical.conflict_log_level` and `pglogical.conflict_resolution` are used to handle update conflicts. Conflicts can emerge when changes are made locally to the same tables that are subscribed to changes from the publisher. Conflicts can also occur during various scenarios, such as two-way replication or when multiple subscribers are replicating from the same publisher. For more information, see [ PostgreSQL bi-directional replication using pglogical](https://aws.amazon.com/blogs/database/postgresql-bi-directional-replication-using-pglogical/).
| Parameter | Description |
| --- | --- |
| pglogical.batch\$1inserts | Batch inserts if possible. Not set by default. Change to '1' to turn on, '0' to turn off. |
| pglogical.conflict\$1log\$1level | Sets the log level to use for logging resolved conflicts. Supported string values are debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, panic. |
| pglogical.conflict\$1resolution | Sets method to use to resolve conflicts when conflicts are resolvable. Supported string values are error, apply\$1remote, keep\$1local, last\$1update\$1wins, first\$1update\$1wins. |
| pglogical.extra\$1connection\$1options | Connection options to add to all peer node connections. |
| pglogical.synchronous\$1commit | pglogical specific synchronous commit value |
| pglogical.use\$1spi | Use SPI (server programming interface) instead of low-level API to apply changes. Set to '1' to turn on, '0' to turn off. For more information about SPI, see [Server Programming Interface](https://www.postgresql.org/docs/current/spi.html) in the PostgreSQL documentation. |
# 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);
```
# Reducing bloat in tables and indexes with the pg\$1repack extension
You can use the `pg_repack` extension to remove bloat from tables and indexes as an alternative to `VACUUM FULL`. This extension is supported on RDS for PostgreSQL versions 9.6.3 and higher. For more information on the `pg_repack` extension and the full table repack, see the [GitHub project documentation](https://reorg.github.io/pg_repack/).
Unlike `VACUUM FULL`, the `pg_repack` extension requires an exclusive lock (AccessExclusiveLock) only for a short period of time during the table rebuild operation in the following cases:
+ Initial creation of log table – A log table is created to record changes that occur during initial copy of the data, as shown in the following example:
```
postgres=>\dt+ repack.log_*
List of relations
-[ RECORD 1 ]-+----------
Schema | repack
Name | log_16490
Type | table
Owner | postgres
Persistence | permanent
Access method | heap
Size | 65 MB
Description |
```
+ Final swap-and-drop phase.
For the rest of the rebuild operation, it only needs an `ACCESS SHARE` lock on the original table to copy rows from it to the new table. This helps the INSERT, UPDATE, and DELETE operations to proceed as usual.
## Recommendations
The following recommendations apply when you remove bloat from the tables and indexes using the `pg_repack` extension:
+ Perform repack during non-business hours or over a maintenance window to minimize its impact on performance of other database activities.
+ Closely monitor blocking sessions during the rebuild activity and ensure that there is no activity on the original table that could potentially block `pg_repack`, specifically during the final swap-and-drop phase when it needs an exclusive lock on the original table. For more information, see [Identifying what is blocking a query](https://repost.aws/knowledge-center/rds-aurora-postgresql-query-blocked).
When you see a blocking session, you can terminate it using the following command after careful consideration. This helps in the continuation of `pg_repack` to finish the rebuild:
```
SELECT pg_terminate_backend(pid);
```
+ While applying the accrued changes from the `pg_repack's` log table on systems with a very high transaction rate, the apply process might not be able to keep up with the rate of changes. In such cases, `pg_repack` would not be able to complete the apply process. For more information, see [Monitoring the new table during the repack](#Appendix.PostgreSQL.CommonDBATasks.pg_repack.Monitoring). If indexes are severely bloated, an alternative solution is to perform an index only repack. This also helps VACUUM's index cleanup cycles to finish faster.
You can skip the index cleanup phase using manual VACUUM from PostgreSQL version 12, and it is skipped automatically during emergency autovacuum from PostgreSQL version 14. This helps VACUUM complete faster without removing the index bloat and is only meant for emergency situations such as preventing wraparound VACUUM. For more information, see [ Avoiding bloat in indexes](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.diag-table-ind-bloat.html#AuroraPostgreSQL.diag-table-ind-bloat.AvoidinginIndexes) in the Amazon Aurora User Guide.
## Pre-requisites
+ The table must have PRIMARY KEY or not-null UNIQUE constraint.
+ The extension version must be the same for both the client and the server.
+ Ensure that the RDS instance has more `FreeStorageSpace` than the total size of the table without the bloat. As an example, consider the total size of the table including TOAST and indexes as 2TB, and total bloat in the table as 1TB. The required `FreeStorageSpace` must be more than value returned by the following calculation:
`2TB (Table size)` - `1TB (Table bloat)` = `1TB`
You can use the following query to check the total size of the table and use `pgstattuple` to derive bloat. For more information, see [Diagnosing table and index bloat](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.diag-table-ind-bloat.html) in the Amazon Aurora User Guide
```
SELECT pg_size_pretty(pg_total_relation_size('table_name')) AS total_table_size;
```
This space is reclaimed after the completion of the activity.
+ Ensure that the RDS instance has enough compute and IO capacity to handle the repack operation. You might consider to scale up the instance class for optimal balance of performance.
**To use the `pg_repack` extension**
1. Install the `pg_repack` extension on your RDS for PostgreSQL DB instance by running the following command.
```
CREATE EXTENSION pg_repack;
```
1. Run the following commands to grant write access to temporary log tables created by `pg_repack`.
```
ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT INSERT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;
```
1. Connect to the database using the `pg_repack` client utility. Use an account that has `rds_superuser` privileges. As an example, assume that `rds_test` role has `rds_superuser` privileges. The following syntax performs `pg_repack` for full tables including all the table indexes in the `postgres` database.
```
pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U rds_test -k postgres
```
**Note**
You must connect using the -k option. The -a option is not supported.
The response from the `pg_repack` client provides information on the tables on the DB instance that are repacked.
```
INFO: repacking table "pgbench_tellers"
INFO: repacking table "pgbench_accounts"
INFO: repacking table "pgbench_branches"
```
1. The following syntax repacks a single table `orders` including indexes in `postgres` database.
```
pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U rds_test --table orders -k postgres
```
The following syntax repacks only indexes for `orders` table in `postgres` database.
```
pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U rds_test --table orders --only-indexes -k postgres
```
## Monitoring the new table during the repack
+ The size of the database is increased by the total size of the table minus bloat, until swap-and-drop phase of repack. You can monitor the growth rate of the database size, calculate the speed of the repack, and roughly estimate the time it takes to complete initial data transfer.
As an example, consider the total size of the table as 2TB, the size of the database as 4TB, and total bloat in the table as 1TB. The database total size value returned by the calculation at the end of the repack operation is the following:
`2TB (Table size)` \$1 `4 TB (Database size)` - `1TB (Table bloat)` = `5TB`
You can roughly estimate the speed of the repack operation by sampling the growth rate in bytes between two points in time. If the growth rate is 1GB per minute, it can take 1000 minutes or 16.6 hours approximately to complete the initial table build operation. In addition to the initial table build, `pg_repack` also needs to apply accrued changes. The time it takes depends on the rate of applying ongoing changes plus accrued changes.
**Note**
You can use `pgstattuple` extension to calculate the bloat in the table. For more information, see [pgstattuple ](https://www.postgresql.org/docs/current/pgstattuple.html).
+ The number of rows in the `pg_repack's` log table, under the repack schema represents the volume of changes pending to be applied to the new table after the initial load.
You can check the `pg_repack's` log table in `pg_stat_all_tables` to monitor the changes applied to the new table. `pg_stat_all_tables.n_live_tup` indicates the number of records that are pending to be applied to the new table. For more information, see [pg\$1stat\$1all\$1tables](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW).
```
postgres=>SELECT relname,n_live_tup FROM pg_stat_all_tables WHERE schemaname = 'repack' AND relname ILIKE '%log%';
-[ RECORD 1 ]---------
relname | log_16490
n_live_tup | 2000000
```
+ You can use the `pg_stat_statements` extension to find out the time taken by each step in the repack operation. This is helpful in preparation for applying the same repack operation in a production environment. You may adjust the `LIMIT` clause for extending the output further.
```
postgres=>SELECT
SUBSTR(query, 1, 100) query,
round((round(total_exec_time::numeric, 6) / 1000 / 60),4) total_exec_time_in_minutes
FROM
pg_stat_statements
WHERE
query ILIKE '%repack%'
ORDER BY
total_exec_time DESC LIMIT 5;
query | total_exec_time_in_minutes
-----------------------------------------------------------------------+----------------------------
CREATE UNIQUE INDEX index_16493 ON repack.table_16490 USING btree (a) | 6.8627
INSERT INTO repack.table_16490 SELECT a FROM ONLY public.t1 | 6.4150
SELECT repack.repack_apply($1, $2, $3, $4, $5, $6) | 0.5395
SELECT repack.repack_drop($1, $2) | 0.0004
SELECT repack.repack_swap($1) | 0.0004
(5 rows)
```
Repacking is completely an out-of-place operation so the original table is not impacted and we do not anticipate any unexpected challenges that require recovery of the original table. If repack fails unexpectedly, you must inspect the cause of the error and resolve it.
After the issue is resolved, drop and recreate the `pg_repack` extension in the database where the table exists, and retry the `pg_repack` step. In addition, the availability of compute resources and concurrent accessibility of the table plays a crucial role in the timely completion of the repack operation.
# Upgrading and using the PLV8 extension
PLV8 is a trusted Javascript language extension for PostgreSQL. You can use it for stored procedures, triggers, and other procedural code that's callable from SQL. This language extension is supported by all current releases of PostgreSQL.
If you use [PLV8](https://plv8.github.io/) and upgrade PostgreSQL to a new PLV8 version, you immediately take advantage of the new extension. Take the following steps to synchronize your catalog metadata with the new version of PLV8. These steps are optional, but we highly recommend that you complete them to avoid metadata mismatch warnings.
The upgrade process drops all your existing PLV8 functions. Thus, we recommend that you create a snapshot of your RDS for PostgreSQL DB instance before upgrading. For more information, see [Creating a DB snapshot for a Single-AZ DB instance for Amazon RDS](USER_CreateSnapshot.md).
**Important**
Starting with PostgreSQL version 18, Amazon RDS for PostgreSQL will deprecate the `plcoffee` and `plls` PostgreSQL extensions. We recommend that you stop using CoffeeScript and LiveScript in your applications to ensure you have an upgrade path for future engine version upgrades.
**To synchronize your catalog metadata with a new version of PLV8**
1. Verify that you need to update. To do this, run the following command while connected to your instance.
```
SELECT * FROM pg_available_extensions WHERE name IN ('plv8','plls','plcoffee');
```
If your results contain values for an installed version that is a lower number than the default version, continue with this procedure to update your extensions. For example, the following result set indicates that you should update.
```
name | default_version | installed_version | comment
--------+-----------------+-------------------+--------------------------------------------------
plls | 2.1.0 | 1.5.3 | PL/LiveScript (v8) trusted procedural language
plcoffee| 2.1.0 | 1.5.3 | PL/CoffeeScript (v8) trusted procedural language
plv8 | 2.1.0 | 1.5.3 | PL/JavaScript (v8) trusted procedural language
(3 rows)
```
1. Create a snapshot of your RDS for PostgreSQL DB instance if you haven't done so yet. You can continue with the following steps while the snapshot is being created.
1. Get a count of the number of PLV8 functions in your DB instance so you can validate that they are all in place after the upgrade. For example, the following SQL query returns the number of functions written in plv8, plcoffee, and plls.
```
SELECT proname, nspname, lanname
FROM pg_proc p, pg_language l, pg_namespace n
WHERE p.prolang = l.oid
AND n.oid = p.pronamespace
AND lanname IN ('plv8','plcoffee','plls');
```
1. Use pg\$1dump to create a schema-only dump file. For example, create a file on your client machine in the `/tmp` directory.
```
./pg_dump -Fc --schema-only -U master postgres >/tmp/test.dmp
```
This example uses the following options:
+ `-Fc` – Custom format
+ --schema-only – Dump only the commands necessary to create schema (functions in this case)
+ `-U` – The RDS master user name
+ `database` – The database name for our DB instance
For more information on pg\$1dump, see [pg\$1dump](https://www.postgresql.org/docs/current/static/app-pgdump.html ) in the PostgreSQL documentation.
1. Extract the "CREATE FUNCTION" DDL statement that is present in the dump file. The following example uses the `grep` command to extract the DDL statement that creates the functions and save them to a file. You use this in subsequent steps to recreate the functions.
```
./pg_restore -l /tmp/test.dmp | grep FUNCTION > /tmp/function_list
```
For more information on pg\$1restore, see [pg\$1restore](https://www.postgresql.org/docs/current/static/app-pgrestore.html) in the PostgreSQL documentation.
1. Drop the functions and extensions. The following example drops any PLV8 based objects. The cascade option ensures that any dependent are dropped.
```
DROP EXTENSION plv8 CASCADE;
```
If your PostgreSQL instance contains objects based on plcoffee or plls, repeat this step for those extensions.
1. Create the extensions. The following example creates the plv8, plcoffee, and plls extensions.
```
CREATE EXTENSION plv8;
CREATE EXTENSION plcoffee;
CREATE EXTENSION plls;
```
1. Create the functions using the dump file and "driver" file.
The following example recreates the functions that you extracted previously.
```
./pg_restore -U master -d postgres -Fc -L /tmp/function_list /tmp/test.dmp
```
1. Verify that all your functions have been recreated by using the following query.
```
SELECT * FROM pg_available_extensions WHERE name IN ('plv8','plls','plcoffee');
```
The PLV8 version 2 adds the following extra row to your result set:
```
proname | nspname | lanname
---------------+------------+----------
plv8_version | pg_catalog | plv8
```
# Using PL/Rust to write PostgreSQL functions in the Rust language
PL/Rust is a trusted Rust language extension for PostgreSQL. You can use it for stored procedures, functions, and other procedural code that's callable from SQL. The PL/Rust language extension is available in the following versions:
+ RDS for PostgreSQL 17.1 and higher 17 versions
+ RDS for PostgreSQL 16.1 and higher 16 versions
+ RDS for PostgreSQL 15.2-R2 and higher 15 versions
+ RDS for PostgreSQL 14.9 and higher 14 versions
+ RDS for PostgreSQL 13.12 and higher 13 versions
For more information, see [PL/Rust](https://github.com/tcdi/plrust#readme) on GitHub.
**Topics**
+ [
## Setting up PL/Rust
](#PL_Rust-setting-up)
+ [
## Creating functions with PL/Rust
](#PL_Rust-create-function)
+ [
## Using crates with PL/Rust
](#PL_Rust-crates)
+ [
## PL/Rust limitations
](#PL_Rust-limitations)
## Setting up PL/Rust
To install the plrust extension on your DB instance, add plrust to the `shared_preload_libraries` parameter in the DB parameter group associated with your DB instance. With the plrust extension installed, you can create functions.
To modify the `shared_preload_libraries` parameter, your DB instance must be associated with a custom parameter group. For information about creating a custom DB parameter group, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md).
You can install the plrust extension using the AWS Management Console or the AWS CLI.
The following steps assume that your DB instance is associated with a custom DB parameter group.
### Console
**Install the plrust extension in the `shared_preload_libraries` parameter**
Complete the following steps using an account that is a member of the `rds_superuser` group (role).
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 **Databases**.
1. Choose the name of your DB instance to display its details.
1. Open the **Configuration** tab for your DB instance and find the DB instance parameter group link.
1. Choose the link to open the custom parameters associated with your DB instance.
1. In the **Parameters** search field, type `shared_pre` to find the **`shared_preload_libraries`** parameter.
1. Choose **Edit parameters** to access the property values.
1. Add plrust to the list in the **Values** field. Use a comma to separate items in the list of values.
1. Reboot the DB instance so that your change to the `shared_preload_libraries` parameter takes effect. The initial reboot may require additional time to complete.
1. When the instance is available, verify that plrust has been initialized. Use `psql` to connect to the DB instance, and then run the following command.
```
SHOW shared_preload_libraries;
```
Your output should look similar to the following:
```
shared_preload_libraries
--------------------------
rdsutils,plrust
(1 row)
```
### AWS CLI
**Install the plrust extension in the shared\$1preload\$1libraries parameter**
Complete the following steps using an account that is a member of the `rds_superuser` group (role).
1. Use the [modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) AWS CLI command to add plrust to the `shared_preload_libraries` parameter.
```
aws rds modify-db-parameter-group \
--db-parameter-group-name custom-param-group-name \
--parameters "ParameterName=shared_preload_libraries,ParameterValue=plrust,ApplyMethod=pending-reboot" \
--region aws-region
```
1. Use the [reboot-db-instance](https://docs.aws.amazon.com/cli/latest/reference/rds/reboot-db-instance) AWS CLI command to reboot the DB instance and initialize the plrust library. The initial reboot may require additional time to complete.
```
aws rds reboot-db-instance \
--db-instance-identifier your-instance \
--region aws-region
```
1. When the instance is available, you can verify that plrust has been initialized. Use `psql` to connect to the DB instance, and then run the following command.
```
SHOW shared_preload_libraries;
```
Your output should look similar to the following:
```
shared_preload_libraries
--------------------------
rdsutils,plrust
(1 row)
```
## Creating functions with PL/Rust
PL/Rust will compile the function as a dynamic library, load it, and execute it.
The following Rust function filters multiples out of an array.
```
postgres=> CREATE LANGUAGE plrust;
CREATE EXTENSION
```
```
CREATE OR REPLACE FUNCTION filter_multiples(a BIGINT[], multiple BIGINT) RETURNS BIGINT[]
IMMUTABLE STRICT
LANGUAGE PLRUST AS
$$
Ok(Some(a.into_iter().filter(|x| x.unwrap() % multiple != 0).collect()))
$$;
WITH gen_values AS (
SELECT ARRAY(SELECT * FROM generate_series(1,100)) as arr)
SELECT filter_multiples(arr, 3)
from gen_values;
```
## Using crates with PL/Rust
In RDS for PostgreSQL versions 16.3-R2 and higher, 15.7-R2 and higher 15 versions, 14.12-R2 and higher 14 versions, and 13.15-R2 and higher 13 versions, PL/Rust supports additional crates:
+ `url`
+ `regex`
+ `serde`
+ `serde_json`
In RDS for PostgreSQL versions 15.5-R2 and higher, 14.10-R2 and higher 14 versions, and 13.13-R2 and higher 13 versions, PL/Rust supports two additional crates:
+ `croaring-rs`
+ `num-bigint`
Starting with Amazon RDS for PostgreSQL versions 15.4, 14.9, and 13.12, PL/Rust supports the following crates:
+ `aes`
+ `ctr`
+ `rand`
Only the default features are supported for these crates. New RDS for PostgreSQL versions might contain updated versions of crates, and older versions of crates may no longer be supported.
Follow the best practices for performing a major version upgrade to test whether your PL/Rust functions are compatible with the new major version. For more information, see the blog [Best practices for upgrading Amazon RDS to major and minor versions of PostgreSQL](https://aws.amazon.com/blogs/database/best-practices-for-upgrading-amazon-rds-to-major-and-minor-versions-of-postgresql/) and [Upgrading the PostgreSQL DB engine for Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html) in the Amazon RDS User Guide.
Examples of using dependencies when creating a PL/Rust function are available at [Use dependencies](https://tcdi.github.io/plrust/use-plrust.html#use-dependencies).
## PL/Rust limitations
By default, database users can't use PL/Rust. To provide access to PL/Rust, connect as a user with rds\$1superuser privilege, and run the following command:
```
postgres=> GRANT USAGE ON LANGUAGE PLRUST TO user;
```
# Managing spatial data with the PostGIS extension
PostGIS is an extension to PostgreSQL for storing and managing spatial information. To learn more about PostGIS, see [PostGIS.net](https://postgis.net/).
Starting with version 10.5, PostgreSQL supports the libprotobuf 1.3.0 library used by PostGIS for working with map box vector tile data.
Setting up the PostGIS extension requires `rds_superuser` privileges. We recommend that you create a user (role) to manage the PostGIS extension and your spatial data. The PostGIS extension and its related components add thousands of functions to PostgreSQL. Consider creating the PostGIS extension in its own schema if that makes sense for your use case. The following example shows how to install the extension in its own database, but this isn't required.
**Topics**
+ [
## Step 1: Create a user (role) to manage the PostGIS extension
](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Connect)
+ [
## Step 2: Load the PostGIS extensions
](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.LoadExtensions)
+ [
## Step 3: Transfer ownership of the extension schemas
](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferOwnership)
+ [
## Step 4: Transfer ownership of the PostGIS tables
](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.TransferObjects)
+ [
## Step 5: Test the extensions
](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Test)
+ [
## Step 6: Upgrade the PostGIS extension
](#Appendix.PostgreSQL.CommonDBATasks.PostGIS.Update)
+ [PostGIS extension versions](#CHAP_PostgreSQL.Extensions.PostGIS)
+ [Upgrading PostGIS 2 to PostGIS 3](#PostgreSQL.Extensions.PostGIS.versions.upgrading.2-to-3)
## Step 1: Create a user (role) to manage the PostGIS extension
First, connect to your RDS for PostgreSQL DB instance as a user that has `rds_superuser` privileges. If you kept the default name when you set up your instance, you connect as `postgres`.
```
psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
Create a separate role (user) to administer the PostGIS extension.
```
postgres=> CREATE ROLE gis_admin LOGIN PASSWORD 'change_me';
CREATE ROLE
```
Grant this role `rds_superuser` privileges, to allow the role to install the extension.
```
postgres=> GRANT rds_superuser TO gis_admin;
GRANT
```
Create a database to use for your PostGIS artifacts. This step is optional. Or you can create a schema in your user database for the PostGIS extensions, but this also isn't required.
```
postgres=> CREATE DATABASE lab_gis;
CREATE DATABASE
```
Give the `gis_admin` all privileges on the `lab_gis` database.
```
postgres=> GRANT ALL PRIVILEGES ON DATABASE lab_gis TO gis_admin;
GRANT
```
Exit the session and reconnect to your RDS for PostgreSQL DB instance as `gis_admin`.
```
postgres=> psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=gis_admin --password --dbname=lab_gis
Password for user gis_admin:...
lab_gis=>
```
Continue setting up the extension as detailed in the next steps.
## Step 2: Load the PostGIS extensions
The PostGIS extension includes several related extensions that work together to provide geospatial functionality. Depending on your use case, you might not need all the extensions created in this step.
Use `CREATE EXTENSION` statements to load the PostGIS extensions.
```
CREATE EXTENSION postgis;
CREATE EXTENSION
CREATE EXTENSION postgis_raster;
CREATE EXTENSION
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION
CREATE EXTENSION postgis_topology;
CREATE EXTENSION
CREATE EXTENSION address_standardizer_data_us;
CREATE EXTENSION
```
You can verify the results by running the SQL query shown in the following example, which lists the extensions and their owners.
```
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
List of schemas
Name | Owner
--------------+-----------
public | postgres
tiger | rdsadmin
tiger_data | rdsadmin
topology | rdsadmin
(4 rows)
```
## Step 3: Transfer ownership of the extension schemas
Use the ALTER SCHEMA statements to transfer ownership of the schemas to the `gis_admin` role.
```
ALTER SCHEMA tiger OWNER TO gis_admin;
ALTER SCHEMA
ALTER SCHEMA tiger_data OWNER TO gis_admin;
ALTER SCHEMA
ALTER SCHEMA topology OWNER TO gis_admin;
ALTER SCHEMA
```
You can confirm the ownership change by running the following SQL query. Or you can use the `\dn` metacommand from the psql command line.
```
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
List of schemas
Name | Owner
--------------+---------------
public | postgres
tiger | gis_admin
tiger_data | gis_admin
topology | gis_admin
(4 rows)
```
## Step 4: Transfer ownership of the PostGIS tables
**Note**
Do not change ownership of the PostGIS functions. Proper operation and future upgrades of PostGIS require these functions to retain original ownership. For more information about PostGIS permissions, see [PostgreSQL Security](https://postgis.net/workshops/postgis-intro/security.html).
Use the following function to transfer ownership of the PostGIS tables to the `gis_admin` role. Run the following statement from the psql prompt to create the function.
```
CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
CREATE FUNCTION
```
Next, run the following query to run the `exec` function that in turn runs the statements and alters the permissions.
```
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO gis_admin;')
FROM (
SELECT nspname, relname
FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname in ('tiger','topology') AND
relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;
```
## Step 5: Test the extensions
To avoid needing to specify the schema name, add the `tiger` schema to your search path using the following command.
```
SET search_path=public,tiger;
SET
```
Test the `tiger` schema by using the following SELECT statement.
```
SELECT address, streetname, streettypeabbrev, zip
FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
address | streetname | streettypeabbrev | zip
---------+------------+------------------+-------
1 | Devonshire | Pl | 02109
(1 row)
```
To learn more about this extension, see [Tiger Geocoder](https://postgis.net/docs/Extras.html#Tiger_Geocoder) in the PostGIS documentation.
Test access to the `topology` schema by using the following `SELECT` statement. This calls the `createtopology` function to register a new topology object (my\$1new\$1topo) with the specified spatial reference identifier (26986) and default tolerance (0.5). To learn more, see [CreateTopology](https://postgis.net/docs/CreateTopology.html) in the PostGIS documentation.
```
SELECT topology.createtopology('my_new_topo',26986,0.5);
createtopology
----------------
1
(1 row)
```
## Step 6: Upgrade the PostGIS extension
Each new release of PostgreSQL supports one or more versions of the PostGIS extension compatible with that release. Upgrading the PostgreSQL engine to a new version doesn't automatically upgrade the PostGIS extension. Before upgrading the PostgreSQL engine, you typically upgrade PostGIS to the newest available version for the current PostgreSQL version. For details, see [PostGIS extension versions](#CHAP_PostgreSQL.Extensions.PostGIS).
After the PostgreSQL engine upgrade, you then upgrade the PostGIS extension again, to the version supported for the newly upgraded PostgreSQL engine version. For more information about upgrading the PostgreSQL engine, see [How to perform a major version upgrade for RDS for PostgreSQL](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Process.md).
You can check for available PostGIS extension version updates on your RDS for PostgreSQL DB instance at any time. To do so, run the following command. This function is available with PostGIS 2.5.0 and higher versions.
```
SELECT postGIS_extensions_upgrade();
```
If your application doesn't support the latest PostGIS version, you can install an older version of PostGIS that's available in your major version as follows.
```
CREATE EXTENSION postgis VERSION "2.5.5";
```
If you want to upgrade to a specific PostGIS version from an older version, you can also use the following command.
```
ALTER EXTENSION postgis UPDATE TO "2.5.5";
```
Depending on the version that you're upgrading from, you might need to use this function again. The result of the first run of the function determines if an additional upgrade function is needed. For example, this is the case for upgrading from PostGIS 2 to PostGIS 3. For more information, see [Upgrading PostGIS 2 to PostGIS 3](#PostgreSQL.Extensions.PostGIS.versions.upgrading.2-to-3).
If you upgraded this extension to prepare for a major version upgrade of the PostgreSQL engine, you can continue with other preliminary tasks. For more information, see [How to perform a major version upgrade for RDS for PostgreSQL](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Process.md).
## PostGIS extension versions
We recommend that you install the versions of all extensions such as PostGIS as listed in [Extension versions for Amazon RDS for PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html) in the *Amazon RDS for PostgreSQL Release Notes.* To get a list of versions that are available in your release, use the following command.
```
SELECT * FROM pg_available_extension_versions WHERE name='postgis';
```
You can find version information in the following sections in the *Amazon RDS for PostgreSQL Release Notes*:
+ [ PostgreSQL version 16 extensions supported on Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-16x)
+ [ PostgreSQL version 15 extensions supported on Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-15x)
+ [ PostgreSQL version 14 extensions supported on Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-14x)
+ [ PostgreSQL version 13 extensions supported on Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-13x)
+ [ PostgreSQL version 12 extensions supported on Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-12x)
+ [ PostgreSQL version 11 extensions supported on Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-11x)
+ [ PostgreSQL version 10 extensions supported on Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-101x)
+ [ PostgreSQL version 9.6.x extensions supported on Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-96x)
## Upgrading PostGIS 2 to PostGIS 3
Starting with version 3.0, the PostGIS raster functionality is now a separate extension, `postgis_raster`. This extension has its own installation and upgrade path. This removes dozens of functions, data types, and other artifacts required for raster image processing from the core `postgis` extension. That means that if your use case doesn't require raster processing, you don't need to install the `postgis_raster` extension.
In the following upgrade example, the first upgrade command extracts raster functionality into the `postgis_raster` extension. A second upgrade command is then required to upgrade `postgis_raster` to the new version.
**To upgrade from PostGIS 2 to PostGIS 3**
1. Identify the default version of PostGIS that's available to the PostgreSQL version on your RDS for PostgreSQL DB instance. To do so, run the following query.
```
SELECT * FROM pg_available_extensions
WHERE default_version > installed_version;
name | default_version | installed_version | comment
---------+-----------------+-------------------+------------------------------------------------------------
postgis | 3.1.4 | 2.3.7 | PostGIS geometry and geography spatial types and functions
(1 row)
```
1. Identify the versions of PostGIS installed in each database on your RDS for PostgreSQL DB instance. In other words, query each user database as follows.
```
SELECT
e.extname AS "Name",
e.extversion AS "Version",
n.nspname AS "Schema",
c.description AS "Description"
FROM
pg_catalog.pg_extension e
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
e.extname LIKE '%postgis%'
ORDER BY
1;
Name | Version | Schema | Description
---------+---------+--------+---------------------------------------------------------------------
postgis | 2.3.7 | public | PostGIS geometry, geography, and raster spatial types and functions
(1 row)
```
This mismatch between the default version (PostGIS 3.1.4) and the installed version (PostGIS 2.3.7) means that you need to upgrade the PostGIS extension.
```
ALTER EXTENSION postgis UPDATE;
ALTER EXTENSION
WARNING: unpackaging raster
WARNING: PostGIS Raster functionality has been unpackaged
```
1. Run the following query to verify that the raster functionality is now in its own package.
```
SELECT
probin,
count(*)
FROM
pg_proc
WHERE
probin LIKE '%postgis%'
GROUP BY
probin;
probin | count
--------------------------+-------
$libdir/rtpostgis-2.3 | 107
$libdir/postgis-3 | 487
(2 rows)
```
The output shows that there's still a difference between versions. The PostGIS functions are version 3 (postgis-3), while the raster functions (rtpostgis) are version 2 (rtpostgis-2.3). To complete the upgrade, you run the upgrade command again, as follows.
```
postgres=> SELECT postgis_extensions_upgrade();
```
You can safely ignore the warning messages. Run the following query again to verify that the upgrade is complete. The upgrade is complete when PostGIS and all related extensions aren't marked as needing upgrade.
```
SELECT postgis_full_version();
```
1. Use the following query to see the completed upgrade process and the separately packaged extensions, and verify that their versions match.
```
SELECT
e.extname AS "Name",
e.extversion AS "Version",
n.nspname AS "Schema",
c.description AS "Description"
FROM
pg_catalog.pg_extension e
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace
LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid
AND c.classoid = 'pg_catalog.pg_extension'::pg_catalog.regclass
WHERE
e.extname LIKE '%postgis%'
ORDER BY
1;
Name | Version | Schema | Description
----------------+---------+--------+---------------------------------------------------------------------
postgis | 3.1.5 | public | PostGIS geometry, geography, and raster spatial types and functions
postgis_raster | 3.1.5 | public | PostGIS raster types and functions
(2 rows)
```
The output shows that the PostGIS 2 extension was upgraded to PostGIS 3, and both `postgis` and the now separate `postgis_raster` extension are version 3.1.5.
After this upgrade completes, if you don't plan to use the raster functionality, you can drop the extension as follows.
```
DROP EXTENSION postgis_raster;
```