Postgres documentation says ALTER SYSTEM writes the given parameter values to the postgresql.auto.conf file, which is read in addition to postgresql.conf.

I am curious about where the alter database set configuration parameter details are stored.

  • 2
    No. Role and database configurations are stored in pg_db_role_setting which is shared across all databases of a cluster. – Sahap Asci Jul 15 '19 at 10:21

Database-level settings are not stored in any configuration file, but the pg_db_role_setting catalog, the only question is how to get them out there.

As often in similar cases, psql can be of tremendous help here. The key is \set ECHO_HIDDEN on, which then shows the queries behind the different meta-commands.

In one of my sandbox DBs I see the following:

test=# \drds
                       List of settings
    Role     │ Database │              Settings               
 alice       │          │ log_statement=none                 ↵
             │          │ work_mem=12MB
 bob         │          │ log_statement=all                  ↵
             │          │ work_mem=64MB
 luser       │          │ log_statement=all
 tom         │          │ log_statement=all
             │ test     │ search_path=test, public
             │ wacky    │ default_transaction_read_only=true

We are interested in the rows where there is no user specified - these are exactly the settings you were asking about. With ECHO_HIDDEN on, you also get the following query (the formatting is mine):

SELECT rolname AS "Role", 
       datname AS "Database",
       pg_catalog.array_to_string(setconfig, E'\n') AS "Settings"
  FROM pg_catalog.pg_db_role_setting s
  LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase
  LEFT JOIN pg_catalog.pg_roles r ON r.oid = setrole
 ORDER BY 1, 2;

One can simplify this to get only the DB-specific settings:

SELECT datname AS database,
pg_catalog.array_to_string(setconfig, E'\n') AS settings
FROM pg_catalog.pg_db_role_setting s
JOIN pg_catalog.pg_database d ON d.oid = setdatabase ORDER BY 1, 2;

 database │              settings               
 test     │ search_path=test, public
 wacky    │ default_transaction_read_only=true
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.