Using functions from orafce Using pgactive to create active-active replication Reducing bloat with the pg_repack extension Upgrading and using PLV8 Using PL/Rust to write functions in the Rust language

Using PostgreSQL extensions with Amazon RDS for PostgreSQL

You can extend the functionality of PostgreSQL by installing a variety of extensions and modules. For example, to work with spatial data you can install and use the PostGIS extension. For more information, see Managing spatial data with the PostGIS extension. As another example, if you want to improve data entry for very large tables, you can consider partitioning your data by using the pg_partman extension. To learn more, see Managing PostgreSQL partitions with the pg_partman extension.

Note

As of RDS for PostgreSQL 14.5, RDS for PostgreSQL supports Trusted Language Extensions for PostgreSQL. This feature is implemented as the extension pg_tle, which you can add to your RDS for PostgreSQL DB instance. By using this extension, developers can create their own PostgreSQL extensions in a safe environment that simplifies the setup and configuration requirements. For more information, see Working with Trusted Language Extensions for PostgreSQL.

In some cases, rather than installing an extension, you might add a specific module to the list of shared_preload_libraries in your RDS for PostgreSQL DB instance's custom DB parameter group. Typically, the default DB cluster parameter group loads only the pg_stat_statements, but several other modules are available to add to the list. For example, you can add scheduling capability by adding the pg_cron module, as detailed in Scheduling maintenance with the PostgreSQL pg_cron extension. As another example, you can log query execution plans by loading the auto_explain module. To learn more, see Logging execution plans of queries in the AWS knowledge center.

Depending on your version of RDS for PostgreSQL, installing an extension might require rds_superuser permissions, as follows:

For RDS for PostgreSQL versions 12 and earlier versions, installing extensions requires rds_superuser privileges.
For RDS for PostgreSQL version 13 and higher versions, users (roles) with create permissions on a given database instance can install and use any trusted extensions. For a list of trusted extensions, see PostgreSQL trusted extensions.

You can also specify precisely which extensions can be installed on your RDS for PostgreSQL DB instance, by listing them in the rds.allowed_extensions parameter. For more information, see Restricting installation of PostgreSQL extensions.

To learn more about the rds_superuser role, see Understanding PostgreSQL roles and permissions.

Topics

Using functions from the orafce extension

The orafce extension provides functions and operators that emulate a subset of functions and packages from an Oracle database. The orafce extension makes it easier for you to port an Oracle application to PostgreSQL. RDS for PostgreSQL versions 9.6.6 and higher support this extension. For more information about orafce, see orafce on GitHub.

Note

RDS for PostgreSQL doesn't support the utl_file package that is part of the orafce extension. This is because the utl_file schema functions provide read and write operations on operating-system text files, which requires superuser access to the underlying host. As a managed service, RDS for PostgreSQL doesn't provide host access.

To use the orafce extension

Connect to the DB instance with the primary user name that you used to create the DB instance.

If you want to turn on orafce for a different database in the same DB instance, use the /c dbname psql command. Using this command, you change from the primary database after initiating the connection.
Turn on the orafce extension with the CREATE EXTENSION statement.
```
CREATE EXTENSION orafce;
```
Transfer ownership of the oracle schema to the rds_superuser role with the ALTER SCHEMA statement.
```
ALTER SCHEMA oracle OWNER TO rds_superuser;
```
If you want to see the list of owners for the oracle schema, use the \dn psql command.

Using pgactive to support active-active replication

The pgactive extension uses active-active replication to support and coordinate write operations on multiple RDS for PostgreSQL databases. Amazon RDS for PostgreSQL supports the pgactive extension on the following versions:

RDS for PostgreSQL 16.1 and higher 16 versions
RDS for PostgreSQL 15.4-R2 and higher 15 versions
RDS for PostgreSQL 14.10 and higher 14 versions
RDS for PostgreSQL 13.13 and higher 13 versions
RDS for PostgreSQL 12.17 and higher 12 versions
RDS for PostgreSQL 11.22

Note

When there are write operations on more than one database in a replication configuration, conflicts are possible. For more information, see Handling conflicts in active-active replication

Topics

Initializing the pgactive extension capability
Setting up active-active replication for RDS for PostgreSQL DB instances
Handling conflicts in active-active replication
Handling sequences in active-active replication
Parameter reference for the pgactive extension
Measuring replication lag among pgactive members
Limitations for the pgactive extension

Initializing the pgactive extension capability

To initialize the pgactive extension capability on your RDS for PostgreSQL DB instance, set the value of the rds.enable_pgactive parameter to 1 and then create the extension in the database. Doing so automatically turns on the parameters rds.logical_replication and track_commit_timestamp and sets the value of wal_level to logical.

You must have permissions as the rds_superuser role to perform these tasks.

You can use the AWS Management Console or the AWS CLI to create the required RDS for PostgreSQL DB instances. The steps following assume that your RDS for PostgreSQL DB instance is associated with a custom DB parameter group. For information about creating a custom DB parameter group, see Working with parameter groups.

To initialize the pgactive extension capability

Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
In the navigation pane, choose your RDS for PostgreSQL DB instance.
Open the Configuration tab for your RDS for PostgreSQL DB instance. In the instance details, find the DB instance parameter group link.
Choose the link to open the custom parameters associated with your RDS for PostgreSQL DB instance.
Find the rds.enable_pgactive parameter, and set it to 1 to initialize the pgactive capability.
Choose Save changes.
In the navigation pane of the Amazon RDS console, choose Databases.
Select your RDS for PostgreSQL DB instance, and then choose Reboot from the Actions menu.
Confirm the DB instance reboot so that your changes take effect.
When the DB instance is available, you can use psql or any other PostgreSQL client to connect to the RDS for PostgreSQL DB instance.

The following example assumes that your RDS for PostgreSQL DB instance has a default database named postgres.
```
psql --host=mydb.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=master username --password --dbname=postgres
```
To verify that pgactive is initialized, run the following command.
```
postgres=>SELECT setting ~ 'pgactive' 
FROM pg_catalog.pg_settings
WHERE name = 'shared_preload_libraries';
```
If pgactive is in shared_preload_libraries, the preceding command will return the following:
```
?column? 
----------
 t
```
Create the extension, as follows.
```
postgres=> CREATE EXTENSION pgactive;
```

To initialize the pgactive extension capability

To initialize the pgactive using the AWS CLI, call the modify-db-parameter-group operation to modify certain parameters in your custom parameter group as shown in the following procedure.

Use the following AWS CLI command to set rds.enable_pgactive to 1 to initialize the pgactive capability for the RDS for PostgreSQL DB instance.


postgres=>aws rds modify-db-parameter-group \
   --db-parameter-group-name custom-param-group-name \
   --parameters "ParameterName=rds.enable_pgactive,ParameterValue=1,ApplyMethod=pending-reboot" \
   --region aws-region

Use the following AWS CLI command to reboot the RDS for PostgreSQL DB instance so that the pgactive library is initialized.
```
aws rds reboot-db-instance \
    --db-instance-identifier your-instance \
    --region aws-region
```

When the instance is available, use psql to connect to the RDS for PostgreSQL DB instance.


psql --host=mydb.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=master user --password --dbname=postgres

Create the extension, as follows.
```
postgres=> CREATE EXTENSION pgactive;
```

Setting up active-active replication for RDS for PostgreSQL DB instances

The following procedure shows you how to start active-active replication between two RDS for PostgreSQL DB instances running PostgreSQL 15.4 or higher in the same region. To run the multi-region high availability example, you need to deploy Amazon RDS for PostgreSQL instances in two different regions and set up VPC Peering. For more information, see VPC peering.

Note

Sending traffic between multiple regions may incur additional costs.

These steps assume that the RDS for PostgreSQL DB instance has been setup with the pgactive extension. For more information, see Initializing the pgactive extension capability.

To configure the first RDS for PostgreSQL DB instance with the `pgactive` extension

The following example illustrates how the pgactive group is created, along with other steps required to create the pgactive extension on the RDS for PostgreSQL DB instance.

Use psql or another client tool to connect to your first RDS for PostgreSQL DB instance.


psql --host=firstinstance.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=master username --password --dbname=postgres

Create a database on the RDS for PostgreSQL instance using the following command:
```
postgres=> CREATE DATABASE app;
```
Switch connection to the new database using the following command:
```
\c app
```

To check if the shared_preload_libraries parameter contains pgactive, run the following command:


app=>SELECT setting ~ 'pgactive' FROM pg_catalog.pg_settings WHERE name = 'shared_preload_libraries';



 ?column? 
----------
 t

Create and populate a sample table using the following SQL statements:

Create an example table using the following SQL statement.


app=> CREATE SCHEMA inventory;
CREATE TABLE inventory.products (
id int PRIMARY KEY, product_name text NOT NULL,
created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP);

Populate the table with some sample data by using the following SQL statement.


app=> INSERT INTO inventory.products (id, product_name)
VALUES (1, 'soap'), (2, 'shampoo'), (3, 'conditioner');

Verify that data exists in the table by using the following SQL statement.
```
 app=>SELECT count(*) FROM inventory.products;

 count
-------
 3
```

Create pgactive extension on the existing database.
```
app=> CREATE EXTENSION pgactive;
```
Create and initialize the pgactive group using the following commands:
```
app=> SELECT pgactive.pgactive_create_group(
    node_name := 'node1-app',
    node_dsn := 'dbname=app host=firstinstance.111122223333.aws-region.rds.amazonaws.com user=master username password=PASSWORD');
```
node1-app is the name that you assign to uniquely identify a node in the pgactive group.

Note
To perform this step successfully on a DB instance that is publicly accessible, you must turn on the rds.custom_dns_resolution parameter by setting it to 1.

To check if the DB instance is ready, use the following command:


app=> SELECT pgactive.pgactive_wait_for_node_ready();

If the command succeeds, you can see the following output:



pgactive_wait_for_node_ready 
------------------------------ 
(1 row)

To configure the second RDS for PostgreSQL instance and join it to the `pgactive` group

The following example illustrates how you can join an RDS for PostgreSQL DB instance to the pgactive group, along with other steps that are required to create the pgactive extension on the DB instance.

These steps assume that another RDS for PostgreSQL DB instances has been set up with the pgactive extension. For more information, see Initializing the pgactive extension capability.

Use psql to connect to the instance that you want to receive updates from the publisher.


psql --host=secondinstance.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=master username --password --dbname=postgres

Create a database on the second RDS for PostgreSQL DB instance using the following command:
```
postgres=> CREATE DATABASE app;
```
Switch connection to the new database using the following command:
```
\c app
```
Create the pgactive extension on the existing database.
```
app=> CREATE EXTENSION pgactive;
```

Join the RDS for PostgreSQL second DB instance to the pgactive group as follows.


app=> SELECT pgactive.pgactive_join_group(
node_name := 'node2-app',
node_dsn := 'dbname=app host=secondinstance.111122223333.aws-region.rds.amazonaws.com user=master username password=PASSWORD',
join_using_dsn := 'dbname=app host=firstinstance.111122223333.aws-region.rds.amazonaws.com user=postgres password=PASSWORD');

node2-app is the name that you assign to uniquely identify a node in the pgactive group.

To check if the DB instance is ready, use the following command:


app=> SELECT pgactive.pgactive_wait_for_node_ready();

If the command succeeds, you can see the following output:



pgactive_wait_for_node_ready 
------------------------------ 
(1 row)

If the first RDS for PostgreSQL database is relatively large, you can see pgactive.pgactive_wait_for_node_ready() emitting the progress report of the restore operation. The output looks similar to the following:


NOTICE:  restoring database 'app', 6% of 7483 MB complete
NOTICE:  restoring database 'app', 42% of 7483 MB complete
NOTICE:  restoring database 'app', 77% of 7483 MB complete
NOTICE:  restoring database 'app', 98% of 7483 MB complete
NOTICE:  successfully restored database 'app' from node node1-app in 00:04:12.274956
 pgactive_wait_for_node_ready 
------------------------------ 
(1 row)

From this point forward, pgactive synchronizes the data between the two DB instances.

You can use the following command to verify if the database of the second DB instance has the data:
```
app=> SELECT count(*) FROM inventory.products;
```
If the data is successfully synchronized, you’ll see the following output:
```
 count
-------
 3
```

Run the following command to insert new values:


app=> INSERT INTO inventory.products (id, product_name) VALUES ('lotion');

Connect to the database of the first DB instance and run the following query:
```
app=> SELECT count(*) FROM inventory.products;
```
If the active-active replication is initialized, the output is similar to the following:
```
count
-------
 4
```

To detach and remove a DB instance from the `pgactive` group

You can detach and remove a DB instance from the pgactive group using these steps:

You can detach the second DB instance from the first DB instance using the following command:
```
app=> SELECT * FROM pgactive.pgactive_detach_nodes(ARRAY[‘node2-app']);
```
Remove the pgactive extension from the second DB instance using the following command:
```
app=> SELECT * FROM pgactive.pgactive_remove();
```
To forcefully remove the extension:
```
app=> SELECT * FROM pgactive.pgactive_remove(true);
```
Drop the extension using the following command:
```
app=> DROP EXTENSION pgactive;
```

Handling conflicts in active-active replication

The pgactive extension works per database and not per cluster. Each DB instance that uses pgactive is an independent instance and can accept data changes from any source. When a change is sent to a DB instance, PostgreSQL commits it locally and then uses pgactive to replicate the change asynchronously to other DB instances. When two PostgreSQL DB instances update the same record at nearly the same time, a conflict can occur.

The pgactive extension provides mechanisms for conflict detection and automatic resolution. It tracks the time stamp when the transaction was committed on both the DB instances and automatically applies the change with the latest time stamp. The pgactive extension also logs when a conflict occurs in the pgactive.pgactive_conflict_history table.

The pgactive.pgactive_conflict_history will keep growing. You may want to define a purging policy. This can be done by deleting some records on a regular basis or defining a partitioning scheme for this relation (and later detach, drop, truncate partitions of interest). To implement the purging policy on a regular basis, one option is to use the pg_cron extension. See the following information of an example for the pg_cron history table, Scheduling maintenance with the PostgreSQL pg_cron extension.

Handling sequences in active-active replication

An RDS for PostgreSQL DB instance with the pgactive extension uses two different sequence mechanisms to generate unique values.

Global Sequences

To use a global sequence, create a local sequence with the CREATE SEQUENCE statement. Use pgactive.pgactive_snowflake_id_nextval(seqname) instead of usingnextval(seqname) to get the next unique value of the sequence.

The following example creates a global sequence:


postgres=> CREATE TABLE gstest (
      id bigint primary key,
      parrot text
    );


postgres=>CREATE SEQUENCE gstest_id_seq OWNED BY gstest.id;


postgres=> ALTER TABLE gstest \
      ALTER COLUMN id SET DEFAULT \
      pgactive.pgactive_snowflake_id_nextval('gstest_id_seq');

Partitioned sequences

In split-step or partitioned sequences, a normal PostgreSQL sequence is used on each node. Each sequence increments by the same amount and starts at different offsets. For example, with step 100, the node 1 generates sequence as 101, 201, 301, and so on and the node 2 generates sequence as 102, 202, 302, and so on. This scheme works well even if the nodes can't communicate for extended periods, but requires that the designer specify a maximum number of nodes when establishing the schema and requires per-node configuration. Mistakes can easily lead to overlapping sequences.

It is relatively simple to configure this approach with pgactive by creating the desired sequence on a node as follows:



CREATE TABLE some_table (generated_value bigint primary key);


postgres=> CREATE SEQUENCE some_seq INCREMENT 100 OWNED BY some_table.generated_value;


postgres=> ALTER TABLE some_table ALTER COLUMN generated_value SET DEFAULT nextval('some_seq');

Then call setval on each node to give a different offset starting value as follows.


postgres=>
-- On node 1
SELECT setval('some_seq', 1);

-- On node 2
SELECT setval('some_seq', 2);

Parameter reference for the pgactive extension

You can use the following query to view all the parameters associated with pgactive extension.


postgres=> SELECT * FROM pg_settings WHERE name LIKE 'pgactive.%';

Measuring replication lag among pgactive members

You can use the following query to view the replication lag among the pgactive members. Run this query on every pgactive node to get the full picture.



      postgres=# SELECT *, (last_applied_xact_at - last_applied_xact_committs) AS lag FROM pgactive.pgactive_node_slots;
-{ RECORD 1 ]----------------+----------------------------------------------------------------- 
node_name                    | node2-app
slot_name                    | pgactive_5_7332551165694385385_0_5__
slot_restart_lsn             | 0/1A898A8
slot_confirmed_lsn           | 0/1A898E0
walsender_active             | t
walsender_pid                | 69022
sent_lsn                     | 0/1A898E0 
write_lsn                    | 0/1A898E0
flush_lsn                    | 0/1A898E0  
replay_lsn                   | 0/1A898E0
last_sent_xact_id            | 746
last_sent_xact_committs      | 2024-02-06 18:04:22.430376+00
last_sent_xact_at            | 2024-02-06 18:04:22.431359+00
last_applied_xact_id         | 746
last_applied_xact_committs   | 2024-02-06 18:04:22.430376+00
last_applied_xact_at         | 2024-02-06 18:04:52.452465+00
lag                          | 00:00:30.022089

Limitations for the pgactive extension

All tables require a Primary Key, otherwise Update's and Delete's aren't allowed. The values in the Primary Key column shouldn't be updated.
Sequences may have gaps and sometimes might not follow an order. Sequences are not replicated. For more information, see Handling sequences in active-active replication.
DDL and large objects are not replicated.
Secondary unique indexes can cause data divergence.
Collation needs to be identical on all node in the group.
Load balancing across nodes is an anti-pattern.
Large transactions can cause replication lag.

Reducing bloat in tables and indexes with the pg_repack extension

You can use the pg_repack extension to remove bloat from tables and indexes as an alternative to VACUUM FULL. This extension is supported on RDS for PostgreSQL versions 9.6.3 and higher. For more information on the pg_repack extension and the full table repack, see the GitHub project documentation.

Unlike VACUUM FULL, the pg_repack extension requires an exclusive lock (AccessExclusiveLock) only for a short period of time during the table rebuild operation in the following cases:

Initial creation of log table – A log table is created to record changes that occur during initial copy of the data, as shown in the following example:


postgres=>\dt+ repack.log_*
List of relations
-[ RECORD 1 ]-+----------
Schema        | repack
Name          | log_16490
Type          | table
Owner         | postgres
Persistence   | permanent
Access method | heap
Size          | 65 MB
Description   |

Final swap-and-drop phase.

For the rest of the rebuild operation, it only needs an ACCESS SHARE lock on the original table to copy rows from it to the new table. This helps the INSERT, UPDATE, and DELETE operations to proceed as usual.

Recommendations

The following recommendations apply when you remove bloat from the tables and indexes using the pg_repack extension:

Perform repack during non-business hours or over a maintenance window to minimize its impact on performance of other database activities.
Closely monitor blocking sessions during the rebuild activity and ensure that there is no activity on the original table that could potentially block pg_repack, specifically during the final swap-and-drop phase when it needs an exclusive lock on the original table. For more information, see Identifying what is blocking a query.

When you see a blocking session, you can terminate it using the following command after careful consideration. This helps in the continuation of pg_repack to finish the rebuild:
```
SELECT pg_terminate_backend(pid);
```
While applying the accrued changes from the pg_repack's log table on systems with a very high transaction rate, the apply process might not be able to keep up with the rate of changes. In such cases, pg_repack would not be able to complete the apply process. For more information, see Monitoring the new table during the repack. If indexes are severely bloated, an alternative solution is to perform an index only repack. This also helps VACUUM's index cleanup cycles to finish faster.

You can skip the index cleanup phase using manual VACUUM from PostgreSQL version 12, and it is skipped automatically during emergency autovacuum from PostgreSQL version 14. This helps VACUUM complete faster without removing the index bloat and is only meant for emergency situations such as preventing wraparound VACUUM. For more information, see Avoiding bloat in indexes in the Amazon Aurora User Guide.

Pre-requisites

The table must have PRIMARY KEY or not-null UNIQUE constraint.
The extension version must be the same for both the client and the server.
Ensure that the RDS instance has more FreeStorageSpace than the total size of the table without the bloat. As an example, consider the total size of the table including TOAST and indexes as 2TB, and total bloat in the table as 1TB. The required FreeStorageSpace must be more than value returned by the following calculation:

2TB (Table size) - 1TB (Table bloat) = 1TB

You can use the following query to check the total size of the table and use pgstattuple to derive bloat. For more information, see Diagnosing table and index bloat in the Amazon Aurora User Guide
```
SELECT pg_size_pretty(pg_total_relation_size('table_name')) AS total_table_size;
```
This space is reclaimed after the completion of the activity.
Ensure that the RDS instance has enough compute and IO capacity to handle the repack operation. You might consider to scale up the instance class for optimal balance of performance.

To use the `pg_repack` extension

Install the pg_repack extension on your RDS for PostgreSQL DB instance by running the following command.
```
CREATE EXTENSION pg_repack;
```

Run the following commands to grant write access to temporary log tables created by pg_repack.


ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT INSERT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;

Connect to the database using the pg_repack client utility. Use an account that has rds_superuser privileges. As an example, assume that rds_test role has rds_superuser privileges. The following syntax performs pg_repack for full tables including all the table indexes in the postgres database.
```
pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U rds_test -k postgres
```
Note
You must connect using the -k option. The -a option is not supported.

The response from the pg_repack client provides information on the tables on the DB instance that are repacked.
```
INFO: repacking table "pgbench_tellers"
INFO: repacking table "pgbench_accounts"
INFO: repacking table "pgbench_branches"
```

The following syntax repacks a single table orders including indexes in postgres database.


pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U rds_test --table orders -k postgres

The following syntax repacks only indexes for orders table in postgres database.


pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U rds_test --table orders --only-indexes -k postgres

Monitoring the new table during the repack

The size of the database is increased by the total size of the table minus bloat, until swap-and-drop phase of repack. You can monitor the growth rate of the database size, calculate the speed of the repack, and roughly estimate the time it takes to complete initial data transfer.

As an example, consider the total size of the table as 2TB, the size of the database as 4TB, and total bloat in the table as 1TB. The database total size value returned by the calculation at the end of the repack operation is the following:

2TB (Table size) + 4 TB (Database size) - 1TB (Table bloat) = 5TB

You can roughly estimate the speed of the repack operation by sampling the growth rate in bytes between two points in time. If the growth rate is 1GB per minute, it can take 1000 minutes or 16.6 hours approximately to complete the initial table build operation. In addition to the initial table build, pg_repack also needs to apply accrued changes. The time it takes depends on the rate of applying ongoing changes plus accrued changes.

Note
You can use pgstattuple extension to calculate the bloat in the table. For more information, see pgstattuple .
The number of rows in the pg_repack's log table, under the repack schema represents the volume of changes pending to be applied to the new table after the initial load.

You can check the pg_repack's log table in pg_stat_all_tables to monitor the changes applied to the new table. pg_stat_all_tables.n_live_tup indicates the number of records that are pending to be applied to the new table. For more information, see pg_stat_all_tables.
```
postgres=>SELECT relname,n_live_tup FROM pg_stat_all_tables WHERE schemaname = 'repack' AND relname ILIKE '%log%';
        
-[ RECORD 1 ]---------
relname    | log_16490
n_live_tup | 2000000
        
```

You can use the pg_stat_statements extension to find out the time taken by each step in the repack operation. This is helpful in preparation for applying the same repack operation in a production environment. You may adjust the LIMIT clause for extending the output further.


postgres=>SELECT
     SUBSTR(query, 1, 100) query,
     round((round(total_exec_time::numeric, 6) / 1000 / 60),4) total_exec_time_in_minutes
 FROM
     pg_stat_statements
 WHERE
     query ILIKE '%repack%'
 ORDER BY
     total_exec_time DESC LIMIT 5;
        
 query                                                                 | total_exec_time_in_minutes
-----------------------------------------------------------------------+----------------------------
 CREATE UNIQUE INDEX index_16493 ON repack.table_16490 USING btree (a) |                     6.8627
 INSERT INTO repack.table_16490 SELECT a FROM ONLY public.t1           |                     6.4150
 SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)                    |                     0.5395
 SELECT repack.repack_drop($1, $2)                                     |                     0.0004
 SELECT repack.repack_swap($1)                                         |                     0.0004
(5 rows)

Repacking is completely an out-of-place operation so the original table is not impacted and we do not anticipate any unexpected challenges that require recovery of the original table. If repack fails unexpectedly, you must inspect the cause of the error and resolve it.

After the issue is resolved, drop and recreate the pg_repack extension in the database where the table exists, and retry the pg_repack step. In addition, the availability of compute resources and concurrent accessibility of the table plays a crucial role in the timely completion of the repack operation.

Upgrading and using the PLV8 extension

PLV8 is a trusted Javascript language extension for PostgreSQL. You can use it for stored procedures, triggers, and other procedural code that's callable from SQL. This language extension is supported by all current releases of PostgreSQL.

If you use PLV8 and upgrade PostgreSQL to a new PLV8 version, you immediately take advantage of the new extension. Take the following steps to synchronize your catalog metadata with the new version of PLV8. These steps are optional, but we highly recommend that you complete them to avoid metadata mismatch warnings.

The upgrade process drops all your existing PLV8 functions. Thus, we recommend that you create a snapshot of your RDS for PostgreSQL DB instance before upgrading. For more information, see Creating a DB snapshot for a Single-AZ DB instance.

To synchronize your catalog metadata with a new version of PLV8

Verify that you need to update. To do this, run the following command while connected to your instance.


SELECT * FROM pg_available_extensions WHERE name IN ('plv8','plls','plcoffee');

If your results contain values for an installed version that is a lower number than the default version, continue with this procedure to update your extensions. For example, the following result set indicates that you should update.


name    | default_version | installed_version |                     comment
--------+-----------------+-------------------+--------------------------------------------------
plls    | 2.1.0           | 1.5.3             | PL/LiveScript (v8) trusted procedural language
plcoffee| 2.1.0           | 1.5.3             | PL/CoffeeScript (v8) trusted procedural language
plv8    | 2.1.0           | 1.5.3             | PL/JavaScript (v8) trusted procedural language
(3 rows)

Create a snapshot of your RDS for PostgreSQL DB instance if you haven't done so yet. You can continue with the following steps while the snapshot is being created.
Get a count of the number of PLV8 functions in your DB instance so you can validate that they are all in place after the upgrade. For example, the following SQL query returns the number of functions written in plv8, plcoffee, and plls.
```
SELECT proname, nspname, lanname 
FROM pg_proc p, pg_language l, pg_namespace n
WHERE p.prolang = l.oid
AND n.oid = p.pronamespace
AND lanname IN ('plv8','plcoffee','plls');
```
Use pg_dump to create a schema-only dump file. For example, create a file on your client machine in the /tmp directory.
```
./pg_dump -Fc --schema-only -U master postgres >/tmp/test.dmp
```
This example uses the following options:
- -Fc – Custom format
- --schema-only – Dump only the commands necessary to create schema (functions in this case)
- -U – The RDS master user name
- database – The database name for our DB instance
For more information on pg_dump, see pg_dump in the PostgreSQL documentation.
Extract the "CREATE FUNCTION" DDL statement that is present in the dump file. The following example uses the grep command to extract the DDL statement that creates the functions and save them to a file. You use this in subsequent steps to recreate the functions.
```
./pg_restore -l /tmp/test.dmp | grep FUNCTION > /tmp/function_list/
```
For more information on pg_restore, see pg_restore in the PostgreSQL documentation.
Drop the functions and extensions. The following example drops any PLV8 based objects. The cascade option ensures that any dependent are dropped.
```
DROP EXTENSION plv8 CASCADE;
```
If your PostgreSQL instance contains objects based on plcoffee or plls, repeat this step for those extensions.
Create the extensions. The following example creates the plv8, plcoffee, and plls extensions.
```
CREATE EXTENSION plv8;
CREATE EXTENSION plcoffee;
CREATE EXTENSION plls;
```
Create the functions using the dump file and "driver" file.

The following example recreates the functions that you extracted previously.
```
./pg_restore -U master -d postgres -Fc -L /tmp/function_list /tmp/test.dmp
```

Verify that all your functions have been recreated by using the following query.


SELECT * FROM pg_available_extensions WHERE name IN ('plv8','plls','plcoffee');

The PLV8 version 2 adds the following extra row to your result set:


    proname    |  nspname   | lanname
---------------+------------+----------
 plv8_version  | pg_catalog | plv8

Using PL/Rust to write PostgreSQL functions in the Rust language

PL/Rust is a trusted Rust language extension for PostgreSQL. You can use it for stored procedures, functions, and other procedural code that's callable from SQL. The PL/Rust language extension is available in the following versions:

RDS for PostgreSQL 16.1 and higher 16 versions
RDS for PostgreSQL 15.2-R2 and higher 15 versions
RDS for PostgreSQL 14.9 and higher 14 versions
RDS for PostgreSQL 13.12 and higher 13 versions

For more information, see PL/Rust on GitHub.

Setting up PL/Rust

To install the plrust extension on your DB instance, add plrust to the shared_preload_libraries parameter in the DB parameter group associated with your DB instance. With the plrust extension installed, you can create functions.

To modify the shared_preload_libraries parameter, your DB instance must be associated with a custom parameter group. For information about creating a custom DB parameter group, see Working with parameter groups.

You can install the plrust extension using the AWS Management Console or the AWS CLI.

The following steps assume that your DB instance is associated with a custom DB parameter group.

Install the plrust extension in the `shared_preload_libraries` parameter

Complete the following steps using an account that is a member of the rds_superuser group (role).

Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
In the navigation pane, choose Databases.
Choose the name of your DB instance to display its details.
Open the Configuration tab for your DB instance and find the DB instance parameter group link.
Choose the link to open the custom parameters associated with your DB instance.
In the Parameters search field, type shared_pre to find the shared_preload_libraries parameter.
Choose Edit parameters to access the property values.
Add plrust to the list in the Values field. Use a comma to separate items in the list of values.
Reboot the DB instance so that your change to the shared_preload_libraries parameter takes effect. The initial reboot may require additional time to complete.
When the instance is available, verify that plrust has been initialized. Use psql to connect to the DB instance, and then run the following command.
```
SHOW shared_preload_libraries;
```
Your output should look similar to the following:
```
shared_preload_libraries 
--------------------------
rdsutils,plrust
(1 row) 
```

Install the plrust extension in the shared_preload_libraries parameter

Complete the following steps using an account that is a member of the rds_superuser group (role).

Use the modify-db-parameter-group AWS CLI command to add plrust to the shared_preload_libraries parameter.


aws rds modify-db-parameter-group \
   --db-parameter-group-name custom-param-group-name \
   --parameters "ParameterName=shared_preload_libraries,ParameterValue=plrust,ApplyMethod=pending-reboot" \
   --region aws-region

Use the reboot-db-instance AWS CLI command to reboot the DB instance and initialize the plrust library. The initial reboot may require additional time to complete.
```
aws rds reboot-db-instance \
    --db-instance-identifier your-instance \
    --region aws-region
```
When the instance is available, you can verify that plrust has been initialized. Use psql to connect to the DB instance, and then run the following command.
```
SHOW shared_preload_libraries;
```
Your output should look similar to the following:
```
shared_preload_libraries
--------------------------
rdsutils,plrust
(1 row) 
```

Creating functions with PL/Rust

PL/Rust will compile the function as a dynamic library, load it, and execute it.

The following Rust function filters multiples out of an array.


postgres=> CREATE LANGUAGE plrust;
CREATE EXTENSION


CREATE OR REPLACE FUNCTION filter_multiples(a BIGINT[], multiple BIGINT) RETURNS BIGINT[]
    IMMUTABLE STRICT
    LANGUAGE PLRUST AS
$$
    Ok(Some(a.into_iter().filter(|x| x.unwrap() % multiple != 0).collect()))
$$;
        
WITH gen_values AS (
SELECT ARRAY(SELECT * FROM generate_series(1,100)) as arr)
SELECT filter_multiples(arr, 3)
from gen_values;

Using crates with PL/Rust

Starting with Amazon RDS for PostgreSQL versions 15.4, 14.9, and 13.12, PL/Rust supports the following crates:

aes
ctr
rand

Starting with RDS for PostgreSQL versions 15.5-R2, 14.10-R2, and 13.13-R2, PL/Rust supports two additional crates:

croaring-rs
num-bigint

Only the default features are supported for these crates. New RDS for PostgreSQL versions might contain updated versions of crates, and older versions of crates may no longer be supported.

Follow the best practices for performing a major version upgrade to test whether your PL/Rust functions are compatible with the new major version. For more information, see the blog Best practices for upgrading Amazon RDS to major and minor versions of PostgreSQL and Upgrading the PostgreSQL DB engine for Amazon RDS in the Amazon RDS User Guide.

Examples of using dependencies when creating a PL/Rust function are available at Use dependencies.

PL/Rust limitations

By default, database users can't use PL/Rust. To provide access to PL/Rust, connect as a user with rds_superuser privilege, and run the following command:


postgres=> GRANT USAGE ON LANGUAGE PLRUST TO user;

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

Database has long running idle in transaction connection

Managing partitions with the pg_partman extension

Using PostgreSQL extensions with Amazon RDS for PostgreSQL

Note

Topics

Using functions from the orafce extension

Note

To use the orafce extension

Using pgactive to support active-active replication

Note

Topics

Initializing the pgactive extension capability

To initialize the pgactive extension capability

To initialize the pgactive extension capability

Setting up active-active replication for RDS for PostgreSQL DB instances

Note

To configure the first RDS for PostgreSQL DB instance with the pgactive extension

Note

To configure the second RDS for PostgreSQL instance and join it to the pgactive group

To detach and remove a DB instance from the pgactive group

Handling conflicts in active-active replication

Handling sequences in active-active replication

Global Sequences

Partitioned sequences

Parameter reference for the pgactive extension

Measuring replication lag among pgactive members

Limitations for the pgactive extension

Reducing bloat in tables and indexes with the pg_repack extension

Recommendations

Pre-requisites

To use the pg_repack extension

Note

Monitoring the new table during the repack

Note

Upgrading and using the PLV8 extension

To synchronize your catalog metadata with a new version of PLV8

Using PL/Rust to write PostgreSQL functions in the Rust language

Topics

Setting up PL/Rust

Install the plrust extension in the shared_preload_libraries parameter

Install the plrust extension in the shared_preload_libraries parameter

Creating functions with PL/Rust

Using crates with PL/Rust

PL/Rust limitations

To configure the first RDS for PostgreSQL DB instance with the `pgactive` extension

To configure the second RDS for PostgreSQL instance and join it to the `pgactive` group

To detach and remove a DB instance from the `pgactive` group

To use the `pg_repack` extension

Install the plrust extension in the `shared_preload_libraries` parameter