postgres read‐only - ghdrako/doc_snipets GitHub Wiki

SET default_transaction_read_only TO off;   -- current session
ALTER USER [username] SET default_transaction_only TO off;  -- user level
ALTER DATABASE [dbname] SET default_transaction_only TO off; -- database level
ALTER SYSTEM SET default_transaction_read_only TO off;  -- system level This will add or update the setting in data/postgresql/postgresql.auto.conf
SELECT * from pg_settings WHERE name = 'default_transaction_read_only';

PostgreSQL has a configuration parameter call default_transaction_read_only. Setting default_transaction_read_only globally to on forces all connections to disallow writes to the database. default_transaction_read_only is a reloadable parameter, so you do not need to restart your Postgres instance to use it.

ALTER SYSTEM SET default_transaction_read_only TO on;
SELECT pg_reload_conf();
SHOW default_transaction_read_only;

postgres=# INSERT INTO abc VALUES (2) RETURNING id;
ERROR:  cannot execute INSERT in a read-only transaction

Cavits default_transaction_read_only

  • default_transaction_read_only can be overriden in a session, even if the value is set database-wide
postgres=# SHOW default_transaction_read_only ;
 default_transaction_read_only
-------------------------------
 on
(1 row)

postgres=# SET default_transaction_read_only TO off;
SET

postgres=# INSERT INTO abc VALUES (2) RETURNING id;

 id
----
  2
(1 row)

INSERT 0 1
  • when utilizing default_transaction_read_only with an application, you must also ensure your app can be configured to send only read queries to the database, ensuring a smooth user experience.

The Strategy

  • Disable connections to the database (not the cluster).
alter database <dbname> with allow_connections = off;
  • Set the database's default_transaction_read_only setting to true.
alter database <dbname> set default_transaction_read_only = true; --ALTER DATABASE name_of_database SET default_transaction_read_only to ON;
  • Terminate the existing connections to that database.
DO $X$ BEGIN
        -- kill open idle connections, try up to 9x. Last time, kill regardless
        FOR i IN 1..10 LOOP
          PERFORM pg_terminate_backend(pid) from pg_stat_activity where usename = 'gitlab'
            and (i >= 10 OR state in ('idle', 'disabled' ));
          PERFORM pg_stat_clear_snapshot();
          EXIT WHEN NOT EXISTS ( select pid from pg_stat_activity where usename = 'gitlab' );
          RAISE NOTICE 'pg backends still open: sleeping 2 seconds';
          PERFORM pg_sleep(2);
          PERFORM pg_stat_clear_snapshot();
        END LOOP;
        -- send notice if still open connections
        IF EXISTS ( select pid from pg_stat_activity where usename = 'gitlab' ) THEN
            RAISE NOTICE 'Hung backends. Backup might not be 100%% consistent';
        END IF;
    END;$X$;
  • Re-enable (read-only) connections.
alter database gitlabhq with allow_connections = on;
⚠️ **GitHub.com Fallback** ⚠️