pg_readonly

pg_readonly : cluster database read only

Overview

Packages

Source

pig build pkg pg_readonly;		# build deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pg_readonly;		# install via package name, for the active PG version

pig install pg_readonly -v 18;   # install for PG 18
pig install pg_readonly -v 17;   # install for PG 17
pig install pg_readonly -v 16;   # install for PG 16
pig install pg_readonly -v 15;   # install for PG 15
pig install pg_readonly -v 14;   # install for PG 14

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_readonly';

Create this extension with:

CREATE EXTENSION pg_readonly;

Usage

pg_readonly: cluster database read only

pg_readonly sets all databases in a PostgreSQL cluster to read-only mode at the SQL level. It must be loaded via shared_preload_libraries. The read-only status is managed in shared memory with a global flag (not persisted across restarts).

Check Read-Only Status

SELECT get_cluster_readonly();
-- Returns false (read-write) or true (read-only)

Set Cluster Read-Only

SELECT set_cluster_readonly();

In read-only mode, SELECT statements are allowed (unless they call writing functions), but DML (INSERT, UPDATE, DELETE), DDL (including TRUNCATE), and DCL (GRANT, REVOKE) are blocked:

SELECT * FROM t;              -- OK
UPDATE t SET x = 33;          -- ERROR: pg_readonly: invalid statement because cluster is read-only
CREATE TABLE tmp(c text);     -- ERROR: pg_readonly: invalid statement because cluster is read-only

Note: set_cluster_readonly() terminates all open transactions.

Set Cluster Read-Write

SELECT unset_cluster_readonly();

Note: background processes (checkpointer, bgwriter, walwriter, autovacuum) continue running in read-only mode – the restriction is at the SQL statement level only.