ALTER SYSTEM SETchanges a setting to a specified value. For example,
ALTER SYSTEM SET max_connections = 500;.
ALTER SYSTEM RESETresets a setting to the default value set in
postgresql.confi.e. the Aptible default setting. For example,
ALTER SYSTEM RESET max_connections.
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.
superuser-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.
superuser- Session settings that can be changed with
SET. 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
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.
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.
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';
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
t) you know that the Database needs to be restarted.
Using this you can reload the config 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;
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.