3

I have a postgres 9.6 server with > 100 databases on it. When doing a mass-migrate, I use statements like this:

ALTER DATABASE mydatabase SET default_transaction_read_only = false;

Which works great. However, occasionally I get into a state where I'm not sure which databases are read only and which aren't. How do I query this parameter? I know how to query it server-wide:

SHOW default_transaction_read_only;

But not at a database specific level.

3

These settings are stored in the pg_catalog.pg_db_role_setting system catalog. You can search for the databases that have the above setting like

SELECT datname 
  FROM (SELECT datname, unnest(setconfig) AS setting
          FROM pg_db_role_setting 
          JOIN pg_database AS d ON setdatabase = d.oid) AS t 
 WHERE setting = 'default_transaction_read_only=false';

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.