Using functions from orafce Logging at the session and object level Reducing bloat with the pg_repack extension Upgrading and using PLV8

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.

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. By default, this parameter isn't set, so any supported extension can be added if the user has permissions to do so. By adding a list of extensions to this parameter, you explicitly identify the extensions that your RDS for PostgreSQL DB instance can use. Any extensions not listed can't be installed. This capability is available for the following versions:

RDS for PostgreSQL 14.1 and all higher versions
RDS for PostgreSQL 13.2 and higher minor versions
RDS for PostgreSQL 12.6 and higher minor versions

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 master 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.

Logging at the session and object level with the pgAudit extension

You can log activity at the session level or at the object level by installing the PostgreSQL Audit (pgAudit) extension on your RDS for PostgreSQL DB instance. This extension is supported for all available RDS for PostgreSQL versions. It uses the underlying native PostgreSQL logging mechanism.

To learn more about the pgAudit extension, see pgAudit on GitHub.

With session auditing, you can log audit events from various sources and include the fully qualified command text when available. Modify the custom parameter group that is associated with your DB instance so that shared_preload_libraries contains pgaudit. Then set the pgaudit.log parameter to log any of the following types of events:

READ – Logs SELECT and COPY when the source is a relation or a query.
WRITE – Logs INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.
FUNCTION – Logs function calls and DO blocks.
ROLE – Logs statements related to roles and privileges, such as GRANT, REVOKE, CREATE ROLE, ALTER ROLE, and DROP ROLE.
DDL – Logs all data definition language (DDL) statements that aren't included in the ROLE class.
MISC – Logs miscellaneous commands, such as DISCARD, FETCH, CHECKPOINT, VACUUM, and SET.

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 a specific number of tables.

To use the pgAudit extension, add pgaudit to the shared_preload_libraries parameter on the RDS for PostgreSQL DB instance. You can't edit values in the default DB parameter groups, so that means you need to use a custom DB parameter group for the DB instance. For more information about parameter groups, see Working with parameter groups.

To use object auditing with the pgAudit extension

Create a database role called rds_pgaudit using the following command.
```
CREATE ROLE rds_pgaudit;
```

Modify the DB custom parameter group that is associated with your DB instance as follows:

Add pgaudit to the shared_preload_libraries parameter list. Using the AWS CLI, run the following.


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

Set pgaudit.role to the role rds_pgaudit. Using the AWS CLI, run the following.


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

Reboot the DB instance so that the changes to the parameter group take effect.


aws rds reboot-db-instance \
    --db-instance-identifier your-RDS-db-instance \
    --region aws-region

Run the following command to confirm that pgaudit has been initialized.


SHOW shared_preload_libraries;
shared_preload_libraries 
--------------------------
rdsutils,pgaudit
(1 row)

Run the following command to create the pgaudit extension.
```
CREATE EXTENSION pgaudit;
```
Run the following command to confirm pgaudit.role is set to rds_pgaudit.
```
SHOW pgaudit.role;
pgaudit.role 
------------------
rds_pgaudit 
```

To test pgAudit logging, you can run several example commands that you want to audit. For example, you might run the following commands.


CREATE TABLE t1 (id int);
GRANT SELECT ON t1 TO rds_pgaudit;
SELECT * FROM t1;
id 
----
(0 rows)

The database logs should contain an entry similar to the following.


...
2017-06-12 19:09:49 UTC:...:rds_test@postgres:[11701]:LOG: AUDIT:
OBJECT,1,1,READ,SELECT,TABLE,public.t1,select * from t1;
...

For information on viewing the logs, see Monitoring Amazon RDS log files.

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. This extension is supported on RDS for PostgreSQL versions 9.6.3 and higher. For more information on the pg_repack extension, see the GitHub project documentation.

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 repack 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 command syntax is shown following.
```
pg_repack -h db-instance-name.111122223333.aws-region.rds.amazonaws.com -U rds_test -k postgres
```
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"

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.

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 pvl8 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

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

Working with parameters

Managing partitions with the pg_partman extension