Query a database specific configuration parameter The 2019 Stack Overflow Developer Survey Results Are InDatabase Exceptions with Jetty 8.1.0 with BTMMigrating a database from one PostgreSQL server to another cleanlypostgres query performance: view vs functionpostgres_fdw and postgres version upgradeHow do I write IPv4 slash notation in IPV6How do I prevent changes to my database?PgBouncer cannot connect to serverChanging Postgres 9.6 to behave more like 9.5How does Postgresql determine sort keys into the sort node?Npgsql Timeout for simple query when run in parallel

Getting crown tickets for Statue of Liberty

Straighten subgroup lattice

Ubuntu Server install with full GUI

Are spiders unable to hurt humans, especially very small spiders?

Why not take a picture of a closer black hole?

Geography at the pixel level

Is it okay to consider publishing in my first year of PhD?

Why “相同意思的词” is called “同义词” instead of "同意词"?

How do you keep chess fun when your opponent constantly beats you?

Mathematics of imaging the black hole

How did passengers keep warm on sail ships?

Is it correct to say the Neural Networks are an alternative way of performing Maximum Likelihood Estimation? if not, why?

If I can cast sorceries at instant speed, can I use sorcery-speed activated abilities at instant speed?

Output the Arecibo Message

Can a flute soloist sit?

Why couldn't they take pictures of a closer black hole?

Why isn't the circumferential light around the M87 black hole's event horizon symmetric?

Why didn't the Event Horizon Telescope team mention Sagittarius A*?

Slides for 30 min~1 hr Skype tenure track application interview

How to obtain a position of last non-zero element

What is the meaning of Triage in Cybersec world?

Is it safe to harvest rainwater that fell on solar panels?

How to translate "being like"?

Did any laptop computers have a built-in 5 1/4 inch floppy drive?



Query a database specific configuration parameter



The 2019 Stack Overflow Developer Survey Results Are InDatabase Exceptions with Jetty 8.1.0 with BTMMigrating a database from one PostgreSQL server to another cleanlypostgres query performance: view vs functionpostgres_fdw and postgres version upgradeHow do I write IPv4 slash notation in IPV6How do I prevent changes to my database?PgBouncer cannot connect to serverChanging Postgres 9.6 to behave more like 9.5How does Postgresql determine sort keys into the sort node?Npgsql Timeout for simple query when run in parallel



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








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.










share|improve this question




























    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.










    share|improve this question
























      3












      3








      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.










      share|improve this question














      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.







      postgresql postgresql-9.6 read-only-database






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked 2 days ago









      spookypeanutspookypeanut

      1235




      1235




















          1 Answer
          1






          active

          oldest

          votes


















          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';





          share|improve this answer























            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/3.0/"u003ecc by-sa 3.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


















            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234280%2fquery-a-database-specific-configuration-parameter%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            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';





            share|improve this answer



























              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';





              share|improve this answer

























                3












                3








                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';





                share|improve this answer













                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';






                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered 2 days ago









                dezsodezso

                22.6k116097




                22.6k116097



























                    draft saved

                    draft discarded
















































                    Thanks for contributing an answer to Database Administrators Stack Exchange!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid


                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.

                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function ()
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234280%2fquery-a-database-specific-configuration-parameter%23new-answer', 'question_page');

                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    Tamil (spriik) Luke uk diar | Nawigatjuun

                    Align equal signs while including text over equalitiesAMS align: left aligned text/math plus multicolumn alignmentMultiple alignmentsAligning equations in multiple placesNumbering and aligning an equation with multiple columnsHow to align one equation with another multline equationUsing \ in environments inside the begintabularxNumber equations and preserving alignment of equal signsHow can I align equations to the left and to the right?Double equation alignment problem within align enviromentAligned within align: Why are they right-aligned?

                    Training a classifier when some of the features are unknownWhy does Gradient Boosting regression predict negative values when there are no negative y-values in my training set?How to improve an existing (trained) classifier?What is effect when I set up some self defined predisctor variables?Why Matlab neural network classification returns decimal values on prediction dataset?Fitting and transforming text data in training, testing, and validation setsHow to quantify the performance of the classifier (multi-class SVM) using the test data?How do I control for some patients providing multiple samples in my training data?Training and Test setTraining a convolutional neural network for image denoising in MatlabShouldn't an autoencoder with #(neurons in hidden layer) = #(neurons in input layer) be “perfect”?