Postgresql change configuration parameters

Mustafa Bektaş Tepe

4 years ago

This article contains information about change postgresql configuration in postgresql. If you find to learn more about postgresql.conf file you should read below articles.

postgresql.conf settings in PostgreSQL

Postgresql change configuration parameters

We can change postgresql.conf parameter directy from postgresql.conf file or we can change same settings from postgresql.auto.conf file via ALTER system command.

Change postgresql.auto.conf file parameters via SQL

PostgreSQL provides three SQL commands for changing parameters.

Change postgresql configuration by using ALTER SYSTEM

Used to change cluster-level parameters, equivalent to editing postgresql.conf. We said that the changes made with this parameter are written to the postgresql.auto.conf file.

Examples;

ALTER SYSTEM SET wal_level = replica;
ALTER DATABASE test SET timezone = 'UTC-5';
ALTER SYSTEM RESET wal_level;
ALTER SYSTEM RESET ALL;

ALTER SYSTEM SET wal_level = replica;

ALTER DATABASE test SET timezone = 'UTC-5';

ALTER SYSTEM RESET wal_level;

ALTER SYSTEM RESET ALL;

Change postgresql configuration by using ALTER DATABASE

Global parameters are overridden on a database basis.

Examples;

ALTER DATABASE db_test SET escape_string_warning = off;
ALTER DATABASE db_test SET enable_indexscan TO off;
ALTER DATABASE test SET timezone = 'UTC-5';
ALTER DATABASE db_test RESET escape_string_warning;
ALTER DATABASE db_test RESET ALL;

ALTER DATABASE db_test SET escape_string_warning = off;

ALTER DATABASE db_test SET enable_indexscan TO off;

ALTER DATABASE test SET timezone = 'UTC-5';

ALTER DATABASE db_test RESET escape_string_warning;

ALTER DATABASE db_test RESET ALL;

Change postgresql configuration by using ALTER ROLE

It allows passing user-specific parameters.

Examples;

ALTER ROLE test_user SET client_min_messages  = DEBUG;
ALTER ROLE test_user SET maintenance_work_mem = 100000;
ALTER ROLE test_user IN DATABASE test_db SET maintenance_work_mem = 100000;
ALTER USER test_user IN DATABASE test_db RESET maintenance_work_mem;
ALTER USER test_user IN DATABASE test_db RESET ALL;
ALTER USER test_user IN DATABASE test_db SET timezone = 'UTC-7';

ALTER ROLE test_user SET client_min_messages = DEBUG;

ALTER ROLE test_user SET maintenance_work_mem = 100000;

ALTER ROLE test_user IN DATABASE test_db SET maintenance_work_mem = 100000;

ALTER USER test_user IN DATABASE test_db RESET maintenance_work_mem;

ALTER USER test_user IN DATABASE test_db RESET ALL;

ALTER USER test_user IN DATABASE test_db SET timezone = 'UTC-7';

The values set with ALTER DATABASE and ALTER ROLE are applied only when starting the new database session. They override values obtained from configuration files or the server command line and set defaults for the remainder of the session. Each parameter cannot be applied on database and session basis. Therefore, ALTER DATABASE and ALTER ROLE commands cannot be used for some parameters.

When a client connects to the database, PostgreSQL provides two additional SQL commands (and equivalent function) to interact with session-local configuration settings:

SHOW command allows viewing the current value of all parameters. The corresponding function is current_setting.

SHOW ALL;
SHOW DateStyle;
select current_setting('datestyle');

SHOW ALL;

SHOW DateStyle;

select current_setting('datestyle');

SET command allows to change the current value of parameters that can be set locally to a session; It has no effect on other sessions. The corresponding function is set_config.

SET search_path TO my_schema, public;
SET datestyle TO postgres, dm
SET TIME ZONE 'PST8PDT';
SET TIME ZONE 'Europe/Rome';
select set_config('log_statement_stats', 'off', false);

SET search_path TO my_schema, public;

SET datestyle TO postgres, dm

SET TIME ZONE 'PST8PDT';

SET TIME ZONE 'Europe/Rome';

select set_config('log_statement_stats', 'off', false);