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.