How to configure Aptible PostgreSQL Databases
Learn how to configure PostgreSQL Databases on Aptible
Overview
This guide will walk you through the steps of changing, applying, and checking settings, in addition to configuring access control, for an Aptible PostgreSQL](/core-concepts/managed-databases/supported-databases/postgresql) database.
Changing Settings
As described in Aptible’s PostgreSQL Configuration documentation, the ALTER SYSTEM
command can be used to make persistent, global changes to pg_settings
.
-
ALTER SYSTEM SET
changes a setting to a specified value. For example,ALTER SYSTEM SET max_connections = 500;
. -
ALTER SYSTEM RESET
resets a setting to the default value set inpostgresql.conf
i.e. the Aptible default setting. For example,ALTER SYSTEM RESET max_connections
.
Applying Settings
Changes to settings are not necessarily applied immediately. The setting’s context
determines when the change is applied. The current contexts for settings that can be changed with ALTER SYSTEM
are:
-
postmaster
- Server settings that cannot be changed after the Database starts. Restarting the Database is required to apply these settings. -
backend
andsuperuser-backend
- Connection settings that cannot be changed after the connection is established. New connections will use the updated settings. -
sighup
- Server settings that can be changed at runtime. The Database’s configuration must be reloaded in order to apply these settings. -
user
andsuperuser
- Session settings that can be changed withSET
. New sessions will use the updated settings by default and reloading the configuration will apply it to all existing sessions that have not changed the setting.
Any time the Database container restarts including when it crashes or when the aptible db:reload
or aptible db:restart
CLI commands are run will apply any pending changes. aptible db:reload
is recommended as it incurs the least amount of downtime. Restarting the Database is the only way to apply postmaster
settings. It will also ensure that all backend
and superuser-backend
settings are being used by all open connections since restarting the Database will terminate all connections, forcing clients to establish new connections.
For settings that can be changed at runtime, the pg_reload_conf
function (i.e. running SELECT pg_reload_conf();
) will apply the changes to the Database and existing sessions. This is required to apply sighup
settings without restarting the Database. user
and superuser
settings don’t require the configuration to be reloaded but, if it isn’t, the changes will only apply to new sessions so it’s recommended in order to ensure all sessions are using the same default configuration.
Checking Setting Values and Contexts
Show pg_settings
The pg_settings
view contains information on the current settings being used by the Database. The following query selects the relevant columns from pg_settings
for a single setting:
SELECT name, setting, context, pending_restart
FROM pg_settings
WHERE name = 'max_connections';
Note that setting
is the current value for the session and does not reflect changes that have not yet been applied. The pending_restart
column indicates if a setting has been changed that cannot be applied until the Database is restarted. Running SELECT pg_reload_conf();
, will update this column and if it’s TRUE
(t
) you know that the Database needs to be restarted.
Show pending restarts
Using this, you can reload the config and then query if any settings have been changed that require the Database to be restarted.
SELECT name, setting, context, pending_restart
FROM pg_settings
WHERE pending_restart IS TRUE;
Show non-default Settings:
Using this, you can show all non-default settings:
SELECT name, current_setting(name), source, sourcefile, sourceline
FROM pg_settings
WHERE(source <> 'default' OR name = 'server_version')
AND name NOT IN('config_file', 'data_directory', 'hba_file', 'ident_file');
Show all settings
Using this, you can show all non-default settings:
SHOW ALL;
Configuring Access Control
The pg_hba.conf
file (host-based authentication) controls where the PostgreSQL database can be accessed from and is traditionally the way you would restrict access. However, Aptible PostgreSQL Databases configure pg_hba.conf
to allow access from any source and it cannot be modified. Instead, access is controlled by the Aptible infrastructure. By default, Databases are only accessible from within the Stack that they run on but they can be exposed to external sources via Database Endpoints or Network Integrations.
Was this page helpful?