Working with parameters on your RDS for PostgreSQL DB instance

In some cases, you might create an RDS for PostgreSQL DB instance without specifying a custom parameter group. If so, your DB instance is created using the default parameter group for the version of PostgreSQL that you choose. For example, suppose that you create an RDS for PostgreSQL DB instance using PostgreSQL 13.3. In this case, the DB instance is created using the values in the parameter group for PostgreSQL 13 releases, default.postgres13.

You can also create your own custom DB parameter group. You need to do this if you want to modify any settings for the RDS for PostgreSQL DB instance from their default values. To learn how, see Parameter groups for Amazon RDS.

You can track the settings on your RDS for PostgreSQL DB instance in several different ways. You can use the AWS Management Console, the AWS CLI, or the Amazon RDS API. You can also query the values from the PostgreSQL pg_settings table of your instance, as shown following.

SELECT name, setting, boot_val, reset_val, unit
 FROM pg_settings
 ORDER BY name;

To learn more about the values returned from this query, see pg_settings in the PostgreSQL documentation.

Be especially careful when changing the settings for max_connections and shared_buffers on your RDS for PostgreSQL DB instance. For example, suppose that you modify settings for max_connections or shared_buffers and you use values that are too high for your actual workload. In this case, your RDS for PostgreSQL DB instance won't start. If this happens, you see an error such as the following in the postgres.log.

2018-09-18 21:13:15 UTC::@:[8097]:FATAL:  could not map anonymous shared memory: Cannot allocate memory
2018-09-18 21:13:15 UTC::@:[8097]:HINT:  This error usually means that PostgreSQL's request for a shared memory segment
exceeded available memory or swap space. To reduce the request size (currently 3514134274048 bytes), reduce 
PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.

However, you can't change any values of the settings contained in the default RDS for PostgreSQL DB parameter groups. To change settings for any parameters, first create a custom DB parameter group. Then change the settings in that custom group, and then apply the custom parameter group to your RDS for PostgreSQL DB instance. To learn more, see Parameter groups for Amazon RDS.

There are two types of parameters in RDS for PostgreSQL.

If you have privileges to do so, you can also change parameter values by using the ALTER DATABASE, ALTER ROLE, and SET commands.

RDS for PostgreSQL DB instance parameter list

The following table lists some (but not all) parameters available in an RDS for PostgreSQL DB instance. To view all available parameters, you use the describe-db-parameters AWS CLI command. For example, to get the list of all parameters available in the default parameter group for RDS for PostgreSQL version 13, run the following.

aws rds describe-db-parameters --db-parameter-group-name default.postgres13

You can also use the Console. Choose Parameter groups from the Amazon RDS menu, and then choose the parameter group from those available in your AWS Region.

SHOW max_wal_size;