PostgreSQL Question posted in
The official documentation can be found here.

How to set specific config file for each database in Postgresql

I have setup separate config file and run
postgres=# ALTER DATABASE s2409 SET config_file = ‘E:/App/PostgreSQL/16/data/s2409/s2409_postgresql.conf’ ;
ERROR: parameter "config_file" cannot be changed without restarting the server.

The same error coming even after restart.

Can somebody suggest

I have done lot of restarts and included hba.conf & ident.conf files location in s2409_postgresql.conf

Tags:

2

Answers


  1. 0

    I believe this parameter is set at the time the PostgreSQL server starts, and it cannot be changed via SQL commands after the server has started.

    There are several ways to start the server with the new config. You could try something like this for example:

    pg_ctl start -D "E:/App/PostgreSQL/16/data/s2409" -o "-c config_file='E:/App/PostgreSQL/16/data/s2409/s2409_postgresql.conf'"
    Login or Signup to reply.
  2. 0

    You cannot have a unique config file per database. At least not the way you trying to do so. The config files you are trying to set are per-server files. Some of which have options that allow per-database settings. So you will need to write shared config files with settings for each database in them.

    For instance, pg_hba.conf allows you to specify which databases a connection parameter applies to. eg.

    # example, per-db, settings
host             foodb  foouser  127.0.0.1               trust
hostssl          bardb  baruser  barservice.example.com  scram-sha-256

    For your other per-database settings you will want to use a SQL script with ALTER DATABASE commands, and then run them against the postgres server once it is up and running. For example:

    ALTER DATABASE foodb SET work_mem TO '16MB';
ALTER DATABASE bardb SET maintenance_work_mem TO '32MB';

    These settings are then stored and set as the default setting when connecting to that database. For example:

    postgres=# x
Expanded display is on.
postgres=# c foodb
...
foodb=# select name, setting, unit from pg_settings where name = 'work_mem';
-[ RECORD 1 ]-----
name    | work_mem
setting | 8192      -- database default
unit    | kB

foodb=# c bardb
...
bardb=# select name, setting, unit from pg_settings where name = 'work_mem';
-[ RECORD 1 ]-----
name    | work_mem
setting | 4096      -- server-wide default, set from the config file
unit    | kB

    These settings are not temporary, and will survive server restarts as they are stored in internal postgres databases.

    NB. These settings will only take effect for new connections to a database and not any current connections.

    You could, in theory, have unique config files per-database if you ran a distinct server per database. But I do not think that would be advisable to do so on the same host. For instance, if you knew a single server host could successfully manage n simultaneous connections, then each per-db server would have to limit its number of simultaneous connections to n/m (where m is the number of database-servers). Otherwise the host might end up with more connections than it could handle and run out of RAM whilst working multiple, parallel queries.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search