# Working with extensions and foreign data wrappers
To extend the functionality to your Aurora PostgreSQL-Compatible Edition DB cluster, you can install and use various PostgreSQL *extensions*. For example, if your use case calls for intensive data entry across very large tables, you can install the `[pg\$1partman](https://pgxn.org/dist/pg_partman/doc/pg_partman.html)` extension to partition your data and thus spread the workload.
**Note**
As of Aurora PostgreSQL 14.5, Aurora PostgreSQL supports Trusted Language Extensions for PostgreSQL. This feature is implemented as the extension `pg_tle`, which you can add to your Aurora PostgreSQL. By using this extension, developers can create their own PostgreSQL extensions in a safe environment that simplifies the setup and configuration requirements, as well as much of the preliminary testing for new extensions. 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 Aurora PostgreSQL DB cluster's custom DB cluster 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.
An extension that provides access to external data is more specifically known as a *foreign data wrapper* (FDW). As one example, the `oracle_fdw` extension allows your Aurora PostgreSQL DB cluster to work with Oracle databases.
You can also specify precisely which extensions can be installed on your Aurora PostgreSQL DB instance, by listing them in the `rds.allowed_extensions` parameter. 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.html).
Following, you can find information about setting up and using some of the extensions, modules, and FDWs available for Aurora PostgreSQL. For simplicity's sake, these are all referred to as "extensions." You can find listings of extensions that you can use with the currently available Aurora PostgreSQL versions, see [Extension versions for Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html) in the *Release Notes for Aurora PostgreSQL*.
+ [Managing large objects with the lo module](PostgreSQL_large_objects_lo_extension.md)
+ [Managing spatial data with the PostGIS extension](Appendix.PostgreSQL.CommonDBATasks.PostGIS.md)
+ [Managing PostgreSQL partitions with the pg\$1partman extension](PostgreSQL_Partitions.md)
+ [Scheduling maintenance with the PostgreSQL pg\$1cron extension](PostgreSQL_pg_cron.md)
+ [Using pgAudit to log database activity](Appendix.PostgreSQL.CommonDBATasks.pgaudit.md)
+ [Using pglogical to synchronize data across instances](Appendix.PostgreSQL.CommonDBATasks.pglogical.md)
+ [Working with Oracle databases by using the oracle\$1fdw extension](postgresql-oracle-fdw.md)
+ [Working with SQL Server databases by using the tds\$1fdw extension](postgresql-tds-fdw.md)
# Using Amazon Aurora delegated extension support for PostgreSQL
Using Amazon Aurora 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 Aurora PostgreSQL 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
+ 15.5 and higher 15 versions
+ 14.10 and higher 14 versions
+ 13.13 and higher 13 versions
+ 12.17 and higher 12 versions
**Topics**
+ [Turning on delegate extension support to a user](#AuroraPostgreSQL.delegated_ext_mgmt)
+ [Configuration used in Aurora delegated extension support for PostgreSQL](#AuroraPostgreSQL.delegated_ext_config)
+ [Turning off the support for the delegated extension](#AuroraPostgreSQL.delegated_ext_disable)
+ [Benefits of using Amazon Aurora delegated extension support](#AuroraPostgreSQL.delegated_ext_benefits)
+ [Limitation of Aurora delegated extension support for PostgreSQL](#AuroraPostgreSQL.delegated_ext_limit)
+ [Permissions required for certain extensions](#AuroraPostgreSQL.delegated_ext_perm)
+ [Security Considerations](#AuroraPostgreSQL.delegated_ext_sec)
+ [Drop extension cascade disabled](#AuroraPostgreSQL.delegated_ext_drop)
+ [Example extensions that can be added using delegated extension support](#AuroraPostgreSQL.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 Aurora](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 Aurora 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/AuroraUserGuide/Aurora_delegated_ext.html) To learn more about setting up this parameter, see [Turning on delegate extension support to a user](#AuroraPostgreSQL.delegated_ext_mgmt). | rds\$1superuser |
| `rds.allowed_extensions` | This parameter lets the customer limit the extensions that can be installed in the Aurora PostgreSQL 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 Aurora PostgreSQL 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 Aurora delegated extension support
By using Amazon Aurora 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 Aurora delegated extension support for PostgreSQL
+ Objects created during the extension creation process may require additional privileges for the extension to function properly.
## 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 | | us\$1gaz, us\$1lex, us\$1lex, I.us\$1rules | | | |
| amcheck | bt\$1index\$1check, bt\$1index\$1parent\$1check | | | | |
| dict\$1int | | | | intdict | |
| pg\$1partman | | custom\$1time\$1partitions, part\$1config, part\$1config\$1sub | | | |
| pg\$1stat\$1statements | | | | | |
| PostGIS | st\$1tileenvelope | spatial\$1ref\$1sys | | | |
| postgis\$1raster | | | | | |
| postgis\$1topology | | topology, layer | topology | | the delegated user Must be the database owner |
| log\$1fdw | create\$1foreign\$1table\$1for\$1log\$1file | | | | |
| rds\$1tools | role\$1password\$1encryption\$1type | | | | |
| postgis\$1tiger\$1geocoder | | geocode\$1settings\$1default, geocode\$1settings | tiger | | |
| pg\$1freespacemap | pg\$1freespace | | | | |
| pg\$1visibility | pg\$1visibility | | | | |
## 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 large objects with the lo module
The lo module (extension) is for database users and developers working with PostgreSQL databases through JDBC or ODBC drivers. Both JDBC and ODBC expect the database to handle deletion of large objects when references to them change. However, PostgreSQL doesn't work that way. PostgreSQL doesn't assume that an object should be deleted when its reference changes. The result is that objects remain on disk, unreferenced. The lo extension includes a function that you use to trigger on reference changes to delete objects if needed.
**Tip**
To determine if your database can benefit from the lo extension, use the `vacuumlo` utility to check for orphaned large objects. To get counts of orphaned large objects without taking any action, run the utility with the `-n` option (no-op). To learn how, see [vacuumlo utility](#vacuumlo-utility) following.
The lo module is available for Aurora PostgreSQL 13.7, 12.11, 11.16, 10.21 and higher minor versions.
To install the module (extension), you need `rds_superuser` privileges. Installing the lo extension adds the following to your database:
+ `lo` – This is a large object (lo) data type that you can use for binary large objects (BLOBs) and other large objects. The `lo` data type is a domain of the `oid` data type. In other words, it's an object identifier with optional constraints. For more information, see [Object identifiers](https://www.postgresql.org/docs/14/datatype-oid.html) in the PostgreSQL documentation. In simple terms, you can use the `lo` data type to distinguish your database columns that hold large object references from other object identifiers (OIDs).
+ `lo_manage` – This is a function that you can use in triggers on table columns that contain large object references. Whenever you delete or modify a value that references a large object, the trigger unlinks the object (`lo_unlink`) from its reference. Use the trigger on a column only if the column is the sole database reference to the large object.
For more information about the large objects module, see [lo](https://www.postgresql.org/docs/current/lo.html) in the PostgreSQL documentation.
## Installing the lo extension
Before installing the lo extension, make sure that you have `rds_superuser` privileges.
**To install the lo extension**
1. Use `psql` to connect to the primary DB instance of your Aurora PostgreSQL DB cluster.
```
psql --host=your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password
```
When prompted, enter your password. The `psql` client connects and displays the default administrative connection database, `postgres=>`, as the prompt.
1. Install the extension as follows.
```
postgres=> CREATE EXTENSION lo;
CREATE EXTENSION
```
You can now use the `lo` data type to define columns in your tables. For example, you can create a table (`images`) that contains raster image data. You can use the `lo` data type for a column `raster`, as shown in the following example, which creates a table.
```
postgres=> CREATE TABLE images (image_name text, raster lo);
```
## Using the lo\$1manage trigger function to delete objects
You can use the `lo_manage` function in a trigger on a `lo` or other large object columns to clean up (and prevent orphaned objects) when the `lo` is updated or deleted.
**To set up triggers on columns that reference large objects**
+ Do one of the following:
+ Create a BEFORE UPDATE OR DELETE trigger on each column to contain unique references to large objects, using the column name for the argument.
```
postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OR DELETE ON images
FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
```
+ Apply a trigger only when the column is being updated.
```
postgres=> CREATE TRIGGER t_raster BEFORE UPDATE OF images
FOR EACH ROW EXECUTE FUNCTION lo_manage(raster);
```
The `lo_manage` trigger function works only in the context of inserting or deleting column data, depending on how you define the trigger. It has no effect when you perform a `DROP` or `TRUNCATE` operation on a database. That means that you should delete object columns from any tables before dropping, to prevent creating orphaned objects.
For example, suppose that you want to drop the database containing the `images` table. You delete the column as follows.
```
postgres=> DELETE FROM images COLUMN raster
```
Assuming that the `lo_manage` function is defined on that column to handle deletes, you can now safely drop the table.
## Removing orphaned large objects using `vacuumlo`
The `vacuumlo` utility identifies and removes orphaned large objects from databases. This utility has been available since PostgreSQL 9.1.24. If your database users routinely work with large objects, we recommend that you run `vacuumlo` occasionally to clean up orphaned large objects.
Before installing the lo extension, you can use `vacuumlo` to assess whether your Aurora PostgreSQL DB cluster can benefit. To do so, run `vacuumlo` with the `-n` option (no-op) to show what would be removed, as shown in the following:
```
$ vacuumlo -v -n -h your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com -p 5433 -U postgres docs-lab-spatial-db
Password:*****
Connected to database "docs-lab-spatial-db"
Test run: no large objects will be removed!
Would remove 0 large objects from database "docs-lab-spatial-db".
```
As the output shows, orphaned large objects aren't a problem for this particular database.
For more information about this utility, see [https://www.postgresql.org/docs/current/vacuumlo.html](https://www.postgresql.org/docs/current/vacuumlo.html) in the PostgreSQL documentation.
## Understanding how `vacuumlo` works
The `vacuumlo` command removes orphaned large objects (LOs) from your PostgreSQL database without affecting or conflicting with your user tables.
The command works as follows:
1. `vacuumlo` starts by creating a temporary table containing all the Object IDs (OIDs) of the large objects in your database.
1. `vacuumlo` then scans through every column in the database that uses the data types `oid` or `lo`. If `vacuumlo` finds a matching OID in these columns, it removes the OID from the temporary table. `vacuumlo` checks only columns specifically named `oid` or `lo`, not domains based on these types.
1. The remaining entries in the temporary table represent orphaned LOs, which `vacuumlo` then safely removes.
## Improving `vacuumlo` performance
You can potentially improve the performance of `vacuumlo` by increasing the batch size using the `-l` option. This allows `vacuumlo` to process more LOs at once.
If your system has sufficient memory and you can accommodate the temporary table completely in memory, increasing the `temp_buffers` setting at the database level may improve performance. This allows the table to reside entirely in memory, which can enhance the overall performance.
Following query estimates the size of the temporary table:
```
SELECT
pg_size_pretty(SUM(pg_column_size(oid))) estimated_lo_temp_table_size
FROM
pg_largeobject_metadata;
```
## Considerations for large objects
Following you can find some important considerations to note when working with large objects:
+ `Vacuumlo` is the only solution as there is currently no other method to remove orphaned LOs.
+ Tools like pglogical, native logical replication, and AWS DMS that use replication technologies do not support replicating large objects.
+ When designing your database schema, avoid using large objects when possible and consider using alternative data types like `bytea` instead.
+ Run `vacuumlo` regularly, at least weekly, to prevent issues with orphaned LOs.
+ Use a trigger with the `lo_manage` function on tables that store large objects to help prevent orphaned LOs from being created.
# 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 [Testing an upgrade of your production DB cluster to a new major version](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Upgrade.preliminary).
You can check for available PostGIS extension version updates on your Aurora PostgreSQL DB cluster 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 [Testing an upgrade of your production DB cluster to a new major version](USER_UpgradeDBInstance.PostgreSQL.MajorVersion.md#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Upgrade.preliminary).
## PostGIS extension versions
We recommend that you install the versions of all extensions such as PostGIS as listed in [Extension versions for Aurora PostgreSQL-Compatible Edition](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html) in the *Release Notes for Aurora PostgreSQL*. 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 *Release Notes for Aurora PostgreSQL*:
+ [Extension versions for Aurora PostgreSQL 14](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.14)
+ [Extension versions for Aurora PostgreSQL-Compatible Edition 13](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.13)
+ [Extension versions for Aurora PostgreSQL-Compatible Edition 12](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.12)
+ [Extension versions for Aurora PostgreSQL-Compatible Edition 11](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.11)
+ [Extension versions for Aurora PostgreSQL-Compatible Edition 10](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.10)
+ [Extension versions for Aurora PostgreSQL-Compatible Edition 9.6](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html#AuroraPostgreSQL.Extensions.96)
## 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 Aurora PostgreSQL DB cluster. 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 the writer instance of your Aurora PostgreSQL DB cluster. 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;
```
# 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 Aurora PostgreSQL versions 12.6 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 Aurora](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.
# 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 Aurora PostgreSQL engine versions 12.6 and higher version
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.
Restart the PostgreSQL DB instance to have changes to the parameter group take effect. To learn more about working with parameter groups, see [Amazon Aurora PostgreSQL parameters](AuroraPostgreSQL.Reference.ParameterGroups.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 Aurora PostgreSQL DB cluster, 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.
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 Aurora](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/AuroraUserGuide/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/AuroraUserGuide/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 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 Aurora PostgreSQL DB cluster, 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 [Aurora PostgreSQL database log files](USER_LogAccess.Concepts.PostgreSQL.md).
The pgAudit extension redacts sensitive data such as cleartext passwords from the logs. If your Aurora PostgreSQL DB cluster is configured to log data manipulation language (DML) statements as detailed in [Turning on query logging for your Aurora PostgreSQL DB cluster ](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 Aurora PostgreSQL versions. For a list of pgAudit versions supported by Aurora PostgreSQL version, see [Extension versions for Amazon Aurora PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraPostgreSQLReleaseNotes/AuroraPostgreSQL.Extensions.html) in the *Release Notes for Aurora PostgreSQL*.
**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 Aurora PostgreSQL DB cluster, you first add pgAudit to the shared libraries on the custom DB cluster parameter group for your Aurora PostgreSQL DB cluster. For information about creating a custom DB cluster parameter group, see [Parameter groups for Amazon Aurora](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 Aurora PostgreSQL DB cluster is associated with a custom DB cluster 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 Aurora PostgreSQL DB cluster's Writer instance .
1. Open the **Configuration** tab for your Aurora PostgreSQL DB cluster writer instance. Among the Instance details, find the **Parameter group** link.
1. Choose the link to open the custom parameters associated with your Aurora PostgreSQL DB cluster.
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/AuroraUserGuide/images/apg_rpg_shared_preload_pgaudit.png)
1. Reboot the writer instance of your Aurora PostgreSQL DB cluster 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 writer instance of your Aurora PostgreSQL DB cluster, 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/AuroraUserGuide/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 Aurora PostgreSQL DB cluster's writer 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 writer instance of your Aurora PostgreSQL DB cluster so that the pgaudit library is initialized.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. When the instance is available, you can verify that `pgaudit` has been initialized. Use `psql` to connect to the writer instance of your Aurora PostgreSQL DB cluster, 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 writer instance of your Aurora PostgreSQL DB cluster using the following AWS CLI command.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
# Auditing database objects
With pgAudit set up on your Aurora PostgreSQL DB cluster 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/AuroraUserGuide/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 writer instance of your Aurora PostgreSQL DB cluster.
```
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 writer instance of your Aurora PostgreSQL DB cluster so that your changes to the parameters take effect.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-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 Aurora 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 [Aurora 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 Aurora PostgreSQL DB cluster.
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 cluster parameter group for the Aurora PostgreSQL DB cluster's writer instance. If you don't want database users to bypass your audit logging configuration settings, be sure to change their permissions.
Suppose that your Aurora PostgreSQL DB cluster 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.
# Using pglogical to synchronize data across instances
All currently available Aurora 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 [Overview of PostgreSQL logical replication with Aurora](AuroraPostgreSQL.Replication.Logical.md).
The `pglogical` extension supports logical replication between two or more Aurora PostgreSQL DB clusters. 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 Aurora PostgreSQL DB cluster 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 Aurora PostgreSQL DB cluster 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 Aurora PostgreSQL DB cluster](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 Aurora 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 Aurora 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 Aurora PostgreSQL DB cluster, you add `pglogical` to the shared libraries on the custom DB cluster parameter group for your Aurora PostgreSQL DB cluster. 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 Aurora PostgreSQL DB cluster is associated with a custom DB cluster parameter group. For information about creating a custom DB cluster parameter group, see [Parameter groups for Amazon Aurora](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 Aurora PostgreSQL DB cluster's Writer instance .
1. Open the **Configuration** tab for your Aurora PostgreSQL DB cluster writer instance. Among the Instance details, find the **Parameter group** link.
1. Choose the link to open the custom parameters associated with your Aurora PostgreSQL DB cluster.
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/AuroraUserGuide/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 writer instance of your Aurora PostgreSQL DB cluster so that your changes take effect.
1. When the instance is available, you can use `psql` (or pgAdmin) to connect to the writer instance of your Aurora PostgreSQL DB cluster.
```
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 Aurora PostgreSQL DB cluster's writer 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 writer instance of the Aurora PostgreSQL DB cluster.
```
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 writer instance of your Aurora PostgreSQL DB cluster so that the pglogical library is initialized.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
1. When the instance is available, use `psql` to connect to the writer instance of your Aurora PostgreSQL DB cluster.
```
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 writer instance of your Aurora PostgreSQL DB cluster using the following AWS CLI command.
```
aws rds reboot-db-instance \
--db-instance-identifier writer-instance \
--region aws-region
```
# Setting up logical replication for Aurora PostgreSQL DB cluster
The following procedure shows you how to start logical replication between two Aurora PostgreSQL DB clusters. 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 Aurora PostgreSQL DB cluster has a writer instance with 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 Aurora PostgreSQL DB cluster 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 Aurora PostgreSQL DB cluster 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 Aurora PostgreSQL DB cluster, ,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 Aurora PostgreSQL DB cluster 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 Aurora PostgreSQL DB cluster 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 Aurora PostgreSQL DB cluster, and then re-establish and restart replication.
The replication slots are hosted on the publisher node only. The Aurora PostgreSQL subscriber node in a logical replication scenario has no slots to drop. The Aurora PostgreSQL major version upgrade process supports upgrading the subscriber to a new major version of PostgreSQL independent of the publisher node. However, the upgrade process does disrupt the replication process and interferes with the synchronization of WAL data between publisher node and subscriber node. You need to re-establish logical replication between publisher and subscriber after upgrading the publisher, the subscriber, or both. The following procedure shows you how to determine that replication has been disrupted and how to resolve the issue.
## 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 Aurora PostgreSQL
Before you can perform a major version upgrade on an Aurora PostgreSQL DB cluster's writer 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 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. |
# Working with the supported foreign data wrappers for Amazon Aurora PostgreSQL
A foreign data wrapper (FDW) is a specific type of extension that provides access to external data. For example, the `oracle_fdw` extension allows your Aurora PostgreSQL DB instance to work with Oracle databases.
Following, you can find information about several supported PostgreSQL foreign data wrappers.
**Topics**
+ [Using the log\$1fdw extension to access the DB log using SQL](CHAP_PostgreSQL.Extensions.log_fdw.md)
+ [Using the postgres\$1fdw extension to access external data](postgresql-commondbatasks-fdw.md)
+ [Working with MySQL databases by using the mysql\$1fdw extension](postgresql-mysql-fdw.md)
+ [Working with Oracle databases by using the oracle\$1fdw extension](postgresql-oracle-fdw.md)
+ [Working with SQL Server databases by using the tds\$1fdw extension](postgresql-tds-fdw.md)
# Using the log\$1fdw extension to access the DB log using SQL
Aurora PostgreSQL DB cluster supports the `log_fdw` extension, which you can use to access your database engine log using a SQL interface. The `log_fdw` extension provides two functions that make it easy to create foreign tables for database logs:
+ `list_postgres_log_files` – Lists the files in the database log directory and the file size in bytes.
+ `create_foreign_table_for_log_file(table_name text, server_name text, log_file_name text)` – Builds a foreign table for the specified file in the current database.
All functions created by `log_fdw` are owned by `rds_superuser`. Members of the `rds_superuser` role can grant access to these functions to other database users.
By default, the log files are generated by Amazon Aurora in `stderr` (standard error) format, as specified in `log_destination` parameter. There are only two options for this parameter, `stderr` and `csvlog` (comma-separated values, CSV). If you add the `csvlog` option to the parameter, Amazon Aurora generates both `stderr` and `csvlog` logs. This can affect the storage capacity on your DB cluster, so you need to be aware of the other parameters that affect log handling. For more information, see [Setting the log destination (`stderr`, `csvlog`)](USER_LogAccess.Concepts.PostgreSQL.overview.parameter-groups.md#USER_LogAccess.Concepts.PostgreSQL.Log_Format).
One benefit of generating `csvlog` logs is that the `log_fdw` extension lets you build foreign tables with the data neatly split into several columns. To do this, your instance needs to be associated with a custom DB parameter group so that you can change the setting for `log_destination`. For more information about how to do so, see [Parameter groups for Amazon Aurora](USER_WorkingWithParamGroups.md).
The following example assumes that the `log_destination` parameter includes `cvslog`.
**To use the log\$1fdw extension**
1. Install the `log_fdw` extension.
```
postgres=> CREATE EXTENSION log_fdw;
CREATE EXTENSION
```
1. Create the log server as a foreign data wrapper.
```
postgres=> CREATE SERVER log_server FOREIGN DATA WRAPPER log_fdw;
CREATE SERVER
```
1. Select all from a list of log files.
```
postgres=> SELECT * FROM list_postgres_log_files() ORDER BY 1;
```
A sample response is as follows.
```
file_name | file_size_bytes
------------------------------+-----------------
postgresql.log.2023-08-09-22.csv | 1111
postgresql.log.2023-08-09-23.csv | 1172
postgresql.log.2023-08-10-00.csv | 1744
postgresql.log.2023-08-10-01.csv | 1102
(4 rows)
```
1. Create a table with a single 'log\$1entry' column for the selected file.
```
postgres=> SELECT create_foreign_table_for_log_file('my_postgres_error_log',
'log_server', 'postgresql.log.2023-08-09-22.csv');
```
The response provides no detail other than that the table now exists.
```
-----------------------------------
(1 row)
```
1. Select a sample of the log file. The following code retrieves the log time and error message description.
```
postgres=> SELECT log_time, message FROM my_postgres_error_log ORDER BY 1;
```
A sample response is as follows.
```
log_time | message
----------------------------------+---------------------------------------------------------------------------
Tue Aug 09 15:45:18.172 2023 PDT | ending log output to stderr
Tue Aug 09 15:45:18.175 2023 PDT | database system was interrupted; last known up at 2023-08-09 22:43:34 UTC
Tue Aug 09 15:45:18.223 2023 PDT | checkpoint record is at 0/90002E0
Tue Aug 09 15:45:18.223 2023 PDT | redo record is at 0/90002A8; shutdown FALSE
Tue Aug 09 15:45:18.223 2023 PDT | next transaction ID: 0/1879; next OID: 24578
Tue Aug 09 15:45:18.223 2023 PDT | next MultiXactId: 1; next MultiXactOffset: 0
Tue Aug 09 15:45:18.223 2023 PDT | oldest unfrozen transaction ID: 1822, in database 1
(7 rows)
```
# Using the postgres\$1fdw extension to access external data
You can access data in a table on a remote database server with the [postgres\$1fdw](https://www.postgresql.org/docs/current/static/postgres-fdw.html) extension. If you set up a remote connection from your PostgreSQL DB instance, access is also available to your read replica.
**To use postgres\$1fdw to access a remote database server**
1. Install the postgres\$1fdw extension.
```
CREATE EXTENSION postgres_fdw;
```
1. Create a foreign data server using CREATE SERVER.
```
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'xxx.xx.xxx.xx', port '5432', dbname 'foreign_db');
```
1. Create a user mapping to identify the role to be used on the remote server.
**Important**
To redact the password so that it doesn't appear in the logs, set `log_statement=none` at the session level. Setting at the parameter level doesn't redact the password.
```
CREATE USER MAPPING FOR local_user
SERVER foreign_server
OPTIONS (user 'foreign_user', password 'password');
```
1. Create a table that maps to the table on the remote server.
```
CREATE FOREIGN TABLE foreign_table (
id integer NOT NULL,
data text)
SERVER foreign_server
OPTIONS (schema_name 'some_schema', table_name 'some_table');
```
# Working with MySQL databases by using the mysql\$1fdw extension
To access a MySQL-compatible database from your Aurora PostgreSQL DB cluster, you can install and use the `mysql_fdw` extension. This foreign data wrapper lets you work with RDS for MySQL, Aurora MySQL, MariaDB, and other MySQL-compatible databases. The connection from Aurora PostgreSQL DB cluster to the MySQL database is encrypted on a best-effort basis, depending on the client and server configurations. However, you can enforce encryption if you like. For more information, see [Using encryption in transit with the extension](#postgresql-mysql-fdw.encryption-in-transit).
The `mysql_fdw` extension is supported on Amazon Aurora PostgreSQL version 15.4, 14.9, 13.12, 12.16, and higher releases. It supports selects, inserts, updates, and deletes from an RDS for PostgreSQL DB to tables on a MySQL-compatible database instance.
**Topics**
+ [Setting up your Aurora PostgreSQL DB to use the mysql\$1fdw extension](#postgresql-mysql-fdw.setting-up)
+ [Example: Working with an Aurora MySQL database from Aurora PostgreSQL](#postgresql-mysql-fdw.using-mysql_fdw)
+ [Using encryption in transit with the extension](#postgresql-mysql-fdw.encryption-in-transit)
## Setting up your Aurora PostgreSQL DB to use the mysql\$1fdw extension
Setting up the `mysql_fdw` extension on your Aurora PostgreSQL DB cluster involves loading the extension in your DB cluster and then creating the connection point to the MySQL DB instance. For that task, you need to have the following details about the MySQL DB instance:
+ Hostname or endpoint. For an Aurora MySQL DB cluster, you can find the endpoint by using the Console. Choose the Connectivity & security tab and look in the "Endpoint and port" section.
+ Port number. The default port number for MySQL is 3306.
+ Name of the database. The DB identifier.
You also need to provide access on the security group or the access control list (ACL) for the MySQL port, 3306. Both the Aurora PostgreSQL DB cluster and the Aurora MySQL DB cluster need access to port 3306. If access isn't configured correctly, when you try to connect to MySQL-compatible table you see an error message similar to the following:
```
ERROR: failed to connect to MySQL: Can't connect to MySQL server on 'hostname.aws-region.rds.amazonaws.com:3306' (110)
```
In the following procedure, you (as the `rds_superuser` account) create the foreign server. You then grant access to the foreign server to specific users. These users then create their own mappings to the appropriate MySQL user accounts to work with the MySQL DB instance.
**To use mysql\$1fdw to access a MySQL database server**
1. Connect to your PostgreSQL DB instance using an account that has the `rds_superuser` role. If you accepted the defaults when you created your Aurora PostgreSQL DB cluster , the user name is `postgres`, and you can connect using the `psql` command line tool as follows:
```
psql --host=your-DB-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres –-password
```
1. Install the `mysql_fdw` extension as follows:
```
postgres=> CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
```
After the extension is installed on your Aurora PostgreSQL DB cluster , you set up the foreign server that provides the connection to a MySQL database.
**To create the foreign server**
Perform these tasks on the Aurora PostgreSQL DB cluster . The steps assume that you're connected as a user with `rds_superuser` privileges, such as `postgres`.
1. Create a foreign server in the Aurora PostgreSQL DB cluster :
```
postgres=> CREATE SERVER mysql-db FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'db-name.111122223333.aws-region.rds.amazonaws.com', port '3306');
CREATE SERVER
```
1. Grant the appropriate users access to the foreign server. These should be non-administrator users, that is, users without the `rds_superuser` role.
```
postgres=> GRANT USAGE ON FOREIGN SERVER mysql-db to user1;
GRANT
```
PostgreSQL users create and manage their own connections to the MySQL database through the foreign server.
## Example: Working with an Aurora MySQL database from Aurora PostgreSQL
Suppose that you have a simple table on an Aurora PostgreSQL DB instance . Your Aurora PostgreSQL users want to query (`SELECT`), `INSERT`, `UPDATE`, and `DELETE` items on that table. Assume that the `mysql_fdw` extension was created on your RDS for PostgreSQL DB instance, as detailed in the preceding procedure. After you connect to the RDS for PostgreSQL DB instance as a user that has `rds_superuser` privileges, you can proceed with the following steps.
1. On the Aurora PostgreSQL DB instance, create a foreign server:
```
test=> CREATE SERVER mysqldb FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'your-DB.aws-region.rds.amazonaws.com', port '3306');
CREATE SERVER
```
1. Grant usage to a user who doesn't have `rds_superuser` permissions, for example, `user1`:
```
test=> GRANT USAGE ON FOREIGN SERVER mysqldb TO user1;
GRANT
```
1. Connect as *user1*, and then create a mapping to the MySQL user:
```
test=> CREATE USER MAPPING FOR user1 SERVER mysqldb OPTIONS (username 'myuser', password 'mypassword');
CREATE USER MAPPING
```
1. Create a foreign table linked to the MySQL table:
```
test=> CREATE FOREIGN TABLE mytab (a int, b text) SERVER mysqldb OPTIONS (dbname 'test', table_name '');
CREATE FOREIGN TABLE
```
1. Run a simple query against the foreign table:
```
test=> SELECT * FROM mytab;
a | b
---+-------
1 | apple
(1 row)
```
1. You can add, change, and remove data from the MySQL table. For example:
```
test=> INSERT INTO mytab values (2, 'mango');
INSERT 0 1
```
Run the `SELECT` query again to see the results:
```
test=> SELECT * FROM mytab ORDER BY 1;
a | b
---+-------
1 | apple
2 | mango
(2 rows)
```
## Using encryption in transit with the extension
The connection to MySQL from Aurora PostgreSQL uses encryption in transit (TLS/SSL) by default. However, the connection falls back to non-encrypted when the client and server configuration differ. You can enforce encryption for all outgoing connections by specifying the `REQUIRE SSL` option on the RDS for MySQL user accounts. This same approach also works for MariaDB and Aurora MySQL user accounts.
For MySQL user accounts configured to `REQUIRE SSL`, the connection attempt fails if a secure connection can't be established.
To enforce encryption for existing MySQL database user accounts, you can use the `ALTER USER` command. The syntax varies, depending on the MySQL version, as shown in the following table. For more information, see [ALTER USER](https://dev.mysql.com/doc/refman/8.0/en/alter-user.html) in *MySQL Reference Manual*.
| MySQL 5.7, MySQL 8.0 | MySQL 5.6 |
| --- | --- |
| `ALTER USER 'user'@'%' REQUIRE SSL;` | `GRANT USAGE ON *.* to 'user'@'%' REQUIRE SSL;` |
For more information about the `mysql_fdw` extension, see the [mysql\$1fdw](https://github.com/EnterpriseDB/mysql_fdw) documentation.
# Working with Oracle databases by using the oracle\$1fdw extension
To access an Oracle database from your Aurora PostgreSQL DB cluster you can install and use the `oracle_fdw` extension. This extension is a foreign data wrapper for Oracle databases. To learn more about this extension, see the [oracle\$1fdw](https://github.com/laurenz/oracle_fdw) documentation.
The `oracle_fdw` extension is supported on Aurora PostgreSQL 12.7 (Amazon Aurora release 4.2) and higher versions.
**Topics**
+ [Turning on the oracle\$1fdw extension](#postgresql-oracle-fdw.enabling)
+ [Example: Using a foreign server linked to an Amazon RDS for Oracle database](#postgresql-oracle-fdw.example)
+ [Working with encryption in transit](#postgresql-oracle-fdw.encryption)
+ [Understanding the pg\$1user\$1mappings view and permissions](#postgresql-oracle-fdw.permissions)
## Turning on the oracle\$1fdw extension
To use the oracle\$1fdw extension, perform the following procedure.
**To turn on the oracle\$1fdw extension**
+ Run the following command using an account that has `rds_superuser` permissions.
```
CREATE EXTENSION oracle_fdw;
```
## Example: Using a foreign server linked to an Amazon RDS for Oracle database
The following example shows the use of a foreign server linked to an Amazon RDS for Oracle database.
**To create a foreign server linked to an RDS for Oracle database**
1. Note the following on the RDS for Oracle DB instance:
+ Endpoint
+ Port
+ Database name
1. Create a foreign server.
```
test=> CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//endpoint:port/DB_name');
CREATE SERVER
```
1. Grant usage to a user who doesn't have `rds_superuser` privileges, for example `user1`.
```
test=> GRANT USAGE ON FOREIGN SERVER oradb TO user1;
GRANT
```
1. Connect as `user1`, and create a mapping to an Oracle user.
```
test=> CREATE USER MAPPING FOR user1 SERVER oradb OPTIONS (user 'oracleuser', password 'mypassword');
CREATE USER MAPPING
```
1. Create a foreign table linked to an Oracle table.
```
test=> CREATE FOREIGN TABLE mytab (a int) SERVER oradb OPTIONS (table 'MYTABLE');
CREATE FOREIGN TABLE
```
1. Query the foreign table.
```
test=> SELECT * FROM mytab;
a
---
1
(1 row)
```
If the query reports the following error, check your security group and access control list (ACL) to make sure that both instances can communicate.
```
ERROR: connection for foreign table "mytab" cannot be established
DETAIL: ORA-12170: TNS:Connect timeout occurred
```
## Working with encryption in transit
PostgreSQL-to-Oracle encryption in transit is based on a combination of client and server configuration parameters. For an example using Oracle 21c, see [About the Values for Negotiating Encryption and Integrity](https://docs.oracle.com/en/database/oracle/oracle-database/21/dbseg/configuring-network-data-encryption-and-integrity.html#GUID-3A2AF4AA-AE3E-446B-8F64-31C48F27A2B5) in the Oracle documentation. The client used for oracle\$1fdw on Amazon RDS is configured with `ACCEPTED`, meaning that the encryption depends on the Oracle database server configuration and it uses Oracle Security Library (libnnz) for encryption.
If your database is on RDS for Oracle, see [Oracle native network encryption](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.NetworkEncryption.html) to configure the encryption.
## Understanding the pg\$1user\$1mappings view and permissions
The PostgreSQL catalog `pg_user_mapping` stores the mapping from an Aurora PostgreSQL user to the user on a foreign data (remote) server. Access to the catalog is restricted, but you use the `pg_user_mappings` view to see the mappings. In the following, you can find an example that shows how permissions apply with an example Oracle database, but this information applies more generally to any foreign data wrapper.
In the following output, you can find roles and permissions mapped to three different example users. Users `rdssu1` and `rdssu2` are members of the `rds_superuser` role, and `user1` isn't. The example uses the `psql` metacommand `\du` to list existing roles.
```
test=> \du
List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------------------+-------------------------------------------------------------
rdssu1 | | {rds_superuser}
rdssu2 | | {rds_superuser}
user1 | | {}
```
All users, including users that have `rds_superuser` privileges, are allowed to view their own user mappings (`umoptions`) in the `pg_user_mappings` table. As shown in the following example, when `rdssu1` tries to obtain all user mappings, an error is raised even though `rdssu1``rds_superuser` privileges:
```
test=> SELECT * FROM pg_user_mapping;
ERROR: permission denied for table pg_user_mapping
```
Following are some examples.
```
test=> SET SESSION AUTHORIZATION rdssu1;
SET
test=> SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+------------+----------------------------------
16414 | 16411 | oradb | 16412 | user1 |
16423 | 16411 | oradb | 16421 | rdssu1 | {user=oracleuser,password=mypwd}
16424 | 16411 | oradb | 16422 | rdssu2 |
(3 rows)
test=> SET SESSION AUTHORIZATION rdssu2;
SET
test=> SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+------------+----------------------------------
16414 | 16411 | oradb | 16412 | user1 |
16423 | 16411 | oradb | 16421 | rdssu1 |
16424 | 16411 | oradb | 16422 | rdssu2 | {user=oracleuser,password=mypwd}
(3 rows)
test=> SET SESSION AUTHORIZATION user1;
SET
test=> SELECT * FROM pg_user_mappings;
umid | srvid | srvname | umuser | usename | umoptions
-------+-------+---------+--------+------------+--------------------------------
16414 | 16411 | oradb | 16412 | user1 | {user=oracleuser,password=mypwd}
16423 | 16411 | oradb | 16421 | rdssu1 |
16424 | 16411 | oradb | 16422 | rdssu2 |
(3 rows)
```
Because of implementation differences between `information_schema._pg_user_mappings` and `pg_catalog.pg_user_mappings`, a manually created `rds_superuser` requires additional permissions to view passwords in `pg_catalog.pg_user_mappings`.
No additional permissions are required for an `rds_superuser` to view passwords in `information_schema._pg_user_mappings`.
Users who don't have the `rds_superuser` role can view passwords in `pg_user_mappings` only under the following conditions:
+ The current user is the user being mapped and owns the server or holds the `USAGE` privilege on it.
+ The current user is the server owner and the mapping is for `PUBLIC`.
# Working with SQL Server databases by using the tds\$1fdw extension
You can use the PostgreSQL `tds_fdw` extension to access databases that support the tabular data stream (TDS) protocol, such as Sybase and Microsoft SQL Server databases. This foreign data wrapper lets you connect from your Aurora PostgreSQL DB cluster to databases that use the TDS protocol, including Amazon RDS for Microsoft SQL Server. For more information, see [tds-fdw/tds\$1fdw](https://github.com/tds-fdw/tds_fdw) documentation on GitHub.
The `tds_fdw` extension is supported on Amazon Aurora PostgreSQL version 13.6 and higher releases.
## Setting up your Aurora PostgreSQL DB to use the tds\$1fdw extension
In the following procedures, you can find an example of setting up and using the `tds_fdw` with an Aurora PostgreSQL DB cluster. Before you can connect to a SQL Server database using `tds_fdw`, you need to get the following details for the instance:
+ Hostname or endpoint. For an RDS for SQL Server DB instance, you can find the endpoint by using the Console. Choose the Connectivity & security tab and look in the "Endpoint and port" section.
+ Port number. The default port number for Microsoft SQL Server is 1433.
+ Name of the database. The DB identifier.
You also need to provide access on the security group or the access control list (ACL) for the SQL Server port, 1433. Both the Aurora PostgreSQL DB cluster and the RDS for SQL Server DB instance need access to port 1433. If access isn't configured correctly, when you try to query the Microsoft SQL Server you see the following error message:
```
ERROR: DB-Library error: DB #: 20009, DB Msg: Unable to connect:
Adaptive Server is unavailable or does not exist (mssql2019.aws-region.rds.amazonaws.com), OS #: 0, OS Msg: Success, Level: 9
```
**To use tds\$1fdw to connect to a SQL Server database**
1. Connect to your Aurora PostgreSQL DB cluster's primary instance using an account that has the `rds_superuser` role:
```
psql --host=your-cluster-name-instance-1.aws-region.rds.amazonaws.com --port=5432 --username=test –-password
```
1. Install the `tds_fdw` extension:
```
test=> CREATE EXTENSION tds_fdw;
CREATE EXTENSION
```
After the extension is installed on your Aurora PostgreSQL DB cluster , you set up the foreign server.
**To create the foreign server**
Perform these tasks on the Aurora PostgreSQL DB cluster using an account that has `rds_superuser` privileges.
1. Create a foreign server in the Aurora PostgreSQL DB cluster:
```
test=> CREATE SERVER sqlserverdb FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019.aws-region.rds.amazonaws.com', port '1433', database 'tds_fdw_testing');
CREATE SERVER
```
To access non-ASCII data on the SQLServer side, create a server link with the character\$1set option in the Aurora PostgreSQL DB cluster:
```
test=> CREATE SERVER sqlserverdb FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019.aws-region.rds.amazonaws.com', port '1433', database 'tds_fdw_testing', character_set 'UTF-8');
CREATE SERVER
```
1. Grant permissions to a user who doesn't have `rds_superuser` role privileges, for example, `user1`:
```
test=> GRANT USAGE ON FOREIGN SERVER sqlserverdb TO user1;
```
1. Connect as user1 and create a mapping to a SQL Server user:
```
test=> CREATE USER MAPPING FOR user1 SERVER sqlserverdb OPTIONS (username 'sqlserveruser', password 'password');
CREATE USER MAPPING
```
1. Create a foreign table linked to a SQL Server table:
```
test=> CREATE FOREIGN TABLE mytab (a int) SERVER sqlserverdb OPTIONS (table 'MYTABLE');
CREATE FOREIGN TABLE
```
1. Query the foreign table:
```
test=> SELECT * FROM mytab;
a
---
1
(1 row)
```
### Using encryption in transit for the connection
The connection from Aurora PostgreSQL to SQL Server uses encryption in transit (TLS/SSL) depending on the SQL Server database configuration. If the SQL Server isn't configured for encryption, the RDS for PostgreSQL client making the request to the SQL Server database falls back to unencrypted.
You can enforce encryption for the connection to RDS for SQL Server DB instances by setting the `rds.force_ssl` parameter. To learn how, see [Forcing connections to your DB instance to use SSL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Concepts.General.SSL.Using.html#SQLServer.Concepts.General.SSL.Forcing). For more information about SSL/TLS configuration for RDS for SQL Server, see [Using SSL with a Microsoft SQL Server DB instance](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Concepts.General.SSL.Using.html).