Managing OCI Database with PostgreSQL Configurations
Create and use lists of database configuration variables for an OCI Database with PostgreSQL database system.
Configurations are lists of variables that you can set to tune or optimize a database. When you create a database system, a set of default variables that are optimized based on the database system configuration are applied. You can adjust the default values, save them as a custom configuration list, and apply them to other database systems.
Because the OCI Database with PostgreSQL service optimizes configuration variables for you, only advanced users might need to change and manage them. For more information, see PostgreSQL reference manual.
Manage configuration lists using the following tasks:
Variables
Each configuration contains a list of variables. Variable types include:
-
User variables, which can be changed in a custom configuration list. You can edit user variables when you create or copy a configuration. Some user variables are default user variables. You can't remove these default user variables from a configuration.
- Dynamic variables, which can be changed without restarting the database.
- Static variables, which require PostgreSQL to be restarted to be changed.
- System variables, which can't be changed. System variables are defined according to the shape or requirements of the database.
To view the values associated with a specific configuration variable, see Getting Default Configuration Details and Getting Custom Configuration Details.
More Information for Configuration Variables
The community version of PostgreSQL relies on the Linux OS to manage memory on the system. Among other items, it uses Linux kernel page cache to avoid going to the disk for most database pages. Linux page cache is necessarily very general and isn't customized for PostgreSQL.
In contrast, OCI Database with PostgreSQL does its own memory management. OCI Database with PostgreSQL implemented a custom page cache and doesn't rely solely on the Linux page cache. The custom page cache is implemented fully in user land, and is more effective for PostgreSQL workloads (such as avoiding double buffering). OCI Database with PostgreSQL pre-allocates a fixed portion of memory for this custom page cache and will not use it for any other purposes.
OCI Database with PostgreSQL is, like the community version, setup to allocate some amount of memory for shared_buffers
.
Both the oci.pagecache
and shared_buffers
can be controlled by using configuration variables.
oci.pagecache_size
controls the amount of memory allocated to the OCI Database with PostgreSQL custom page cache. This variable is defined in MiBytes.shared_buffers
controls the amount of memory allocated to PostgreSQL shared memory for data pages. This variable is defined in block size units (8KiB).
The default values are approximately 50% memory for oci.pagecache_size
and 25% memory for shared_buffers
. With default settings, about 75% of the total memory of the database system is always allocated. Metrics show this amount as used, but this memory is used for PostgreSQL data buffers as intended and doesn't result in any negative effects.
Flexible Configurations and Integer Variables
Some user variables are specified by integer values. If you're using a fixed configuration, you might set a variable such as max_connections
to a specific value. That value can't be changed later, and might make the configuration incompatible with other database systems.
A flexible configuration lets you use integer expressions for variable values. Variables using integer expressions can flexibly scale along with database systems of differing hardware configurations. For example, instead of a set value for max_connections
, you can tie it to the memory of the instance:
min(DB_INSTANCE_MEMORY_IN_BYTES/76251136,5000)
OCI Database with PostgreSQL supports the following variables in integer expressions:
- DB_INSTANCE_OCPU_COUNT
- DB_INSTANCE_MEMORY_IN_BYTES
- DB_INSTANCE_MEMORY_IN_KILOBYTES
- DB_INSTANCE_MEMORY_IN_MEGABYTES
- DB_INSTANCE_MEMORY_IN_GIGABYTES
- DB_INSTANCE_MEMORY_IN_TERABYTES
Required IAM Policy
The following policy statements allow a group of administrators to manage OCI Database with PostgreSQL resources:
Allow group <postgresql-admin-group> to manage postgres-db-systems in compartment <database_compartment>
Allow group <postgresql-admin-group> to manage postgres-backups in compartment <database_compartment>
Allow group <postgresql-admin-group> to manage postgres-configurations in compartment <database_compartment>
Allow group <postgresql-admin-group> to read postgres-work-requests in compartment <database_compartment>
For more information, see OCI Database with PostgreSQL Policies.