Bsrgvty

Why does the single dot entry exist in file systems?

Multiple processes redirecting to the same file with >

Should plywood be missing on my roof?

What is the purpose behind a glass nozzle?

Why don't miners charge more for high value transactions?

Everybody knows how to make magical items. What about magical "places"?

Was the whistle-blower's (12 Aug 2019) complaint deemed credible?

Will a nuclear country use nuclear weapons if attacked by conventional means by another nuclear country?

Hoping for a satisfying conclusion (a musical connect wall)

Possible executive assistant job scam

Swap M-x and M-q

Do rainbows show spectral lines from the sun?

How do I most effectively serve as group treasurer?

Why is it possible to teach real numbers before even rigorously defining them?

Morphing between two functions

Monoidal category that is not spacial

How do I obtain the debian-installer for Ubuntu Server 18.04.3 LTS?

Effects of quantum computing on parallel universes

How do I protect myself from bad contracting jobs?

Short story trilogy about a human whose parents were missionaries to a planet of cat people

MS in Mathematics, having trouble finding work outside teaching algebra

ssh login using public keys for users that do not (yet) exist

What would make the internet go away?

Ask Google to remove thousands of pages from its index after cleaning up from hacked site

Where are database specific settings stored?

TimeZone parameter in postgresql.confCan't start postgres server, SHMMAX too smallHow to determine the sweet spot between pool size and database connections in PostgreSQLDoes PostgreSQL write first to WAL and then to data files?Setting PostgreSQL data_directory in postgresql.conf not workingPostgreSQL 9.4.6 under RHEL 7 - Extensive logfile because of log_statement_stats - Can't turn offPostgres ignoring wal_level setting in confHow are Postgres database files updated?Query a database specific configuration parameterwhere does pg_dumpall store *.bak file?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty
margin-bottom:0;

6

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.

postgresql

share|improve this question

edited Jul 15 at 15:01

a_horse_with_no_name

47.7k99 gold badges9696 silver badges128128 bronze badges

asked Jul 15 at 6:57

prichapricha

3144 bronze badges

• 
  
  
  

  
  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 at 10:21
  

  
  
  
  

add a comment
 | 

6

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.

postgresql

share|improve this question

edited Jul 15 at 15:01

a_horse_with_no_name

47.7k99 gold badges9696 silver badges128128 bronze badges

asked Jul 15 at 6:57

prichapricha

3144 bronze badges

• 
  
  
  

  
  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 at 10:21
  

  
  
  
  

add a comment
 | 

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.

postgresql

share|improve this question

edited Jul 15 at 15:01

a_horse_with_no_name

47.7k99 gold badges9696 silver badges128128 bronze badges

asked Jul 15 at 6:57

prichapricha

3144 bronze badges

share|improve this question

edited Jul 15 at 15:01

a_horse_with_no_name

47.7k99 gold badges9696 silver badges128128 bronze badges

asked Jul 15 at 6:57

prichapricha

3144 bronze badges

share|improve this question

edited Jul 15 at 15:01

a_horse_with_no_name

47.7k99 gold badges9696 silver badges128128 bronze badges

asked Jul 15 at 6:57

prichapricha

3144 bronze badges

edited Jul 15 at 15:01

a_horse_with_no_name

47.7k99 gold badges9696 silver badges128128 bronze badges

edited Jul 15 at 15:01

a_horse_with_no_name

47.7k99 gold badges9696 silver badges128128 bronze badges

asked Jul 15 at 6:57

prichapricha

3144 bronze badges

asked Jul 15 at 6:57

prichapricha

3144 bronze badges

1 Answer
1

active

oldest

votes

9

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

share|improve this answer

answered Jul 15 at 14:55

dezsodezso

23.7k1212 gold badges6565 silver badges104104 bronze badges

add a comment
 | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f242858%2fwhere-are-database-specific-settings-stored%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

9

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

share|improve this answer

answered Jul 15 at 14:55

dezsodezso

23.7k1212 gold badges6565 silver badges104104 bronze badges

add a comment
 | 

9

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

share|improve this answer

answered Jul 15 at 14:55

dezsodezso

23.7k1212 gold badges6565 silver badges104104 bronze badges

add a comment
 | 

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

share|improve this answer

answered Jul 15 at 14:55

dezsodezso

23.7k1212 gold badges6565 silver badges104104 bronze badges

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

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;

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

share|improve this answer

answered Jul 15 at 14:55

dezsodezso

23.7k1212 gold badges6565 silver badges104104 bronze badges

answered Jul 15 at 14:55

dezsodezso

23.7k1212 gold badges6565 silver badges104104 bronze badges

answered Jul 15 at 14:55

dezsodezso

23.7k1212 gold badges6565 silver badges104104 bronze badges