CODEWITHSUNDEEP
databasepostgresqlconfiguration
Mayank Singh
Mayank Singh

Reputation: 11

Configure postgresql.conf file through psql command

I want to write a script to configure postgresql.conf file, in which for every parameter it'll show the existing value and after that asked for updated value, then user will enter the updated value.

I'm unable to update the postgresql.conf files parameter through the update command while i can do the alter.
suppose i want to update "shared_preload_libraries" parameter in postgresql.conf which has already some values like

shared_preload_libraries='$libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq '
Now i want to add 'pgaudit' like shared_preload_libraries='pgaudit,$libdir/dbms_pipe,$libdir/edb_gen,$libdir/dbms_aq '
i want to do the same through the command

I tried alter command only but it's not the way to update

Upvotes: 1

Views: 2633

Answers (2)

Vishesh Mangla
Vishesh Mangla

Reputation: 753

  1. First get a copy of postgresqlconf.sql.
  2. Edit it according to your needs.
  3. After building the container, use a shell script or just plain commands.
docker cp ./postgresql.conf
    my-postgres:/var/lib/postgresql/data/postgresql.auto.conf

docker restart my-postgres

NOTE: postgresql.auto.conf is what postgres updates it's postgresql.conf from on restart.

Upvotes: 0

Zegarek
Zegarek

Reputation: 25738

Documentation:

In addition to postgresql.conf, a PostgreSQL data directory contains a file postgresql.auto.conf, which has the same format as postgresql.conf but is intended to be edited automatically, not manually. This file holds settings provided through the ALTER SYSTEM command. This file is read whenever postgresql.conf is, and its settings take effect in the same way. Settings in postgresql.auto.conf override those in postgresql.conf.

You can interact with the config via SQL commands SHOW and SET or functions current_setting() and set_config():

select set_config('shared_preload_libraries',
                  'pgaudit,'||current_setting('shared_preload_libraries'),
                  false);

Unfortunately, in case of shared_preload_libraries this will fail with ERROR: parameter "shared_preload_libraries" cannot be changed without restarting the server. This type of setting can only be changed directly or through ALTER SYSTEM SET..., in case of which the new value will be just saved to postgresql.auto.conf until you restart the server because it only applies at startup. If you want to load a library after startup, use LOAD.

ALTER SYSTEM can't be issued dynamically from functions and prepared statements or given a function:

ALTER SYSTEM 
SET shared_preload_libraries = 'pgaudit'
                               || current_setting('shared_preload_libraries');
--ERROR:  syntax error at or near "||"
--LINE 3:                                || current_setting('shared_pr...
--                                       ^

prepare statement1 (text) as 
alter system set shared_preload_libraries to $1; 
execute statement1('pgaudit,'
                   ||current_setting('shared_preload_libraries'));
--ERROR:  syntax error at or near "alter"
--LINE 1: prepare statement1 (text) as alter system set shared_preload...
--                                     ^
do $$
declare current_setting text;
begin
select current_setting('shared_preload_libraries') into current_setting;
current_setting=rtrim(concat_ws(',','pgaudit','test',current_setting),',');
execute format ('alter system set shared_preload_libraries to %s',current_setting);
end $$;
--ERROR:  ALTER SYSTEM cannot be executed from a function
--CONTEXT:  SQL statement "alter system set shared_preload_libraries to pgaudit,test"
--PL/pgSQL function inline_code_block line 8 at EXECUTE

Upvotes: 1

Related Questions