pg_readonly

Overview

Version

Build

You can build the DEB packages for pg_readonly using pig build:

pig build pkg pg_readonly         # build DEB packages

Install

You can install pg_readonly directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pg_readonly;          # Install for current active PG version

pig ext install -y pg_readonly -v 18  # PG 18
pig ext install -y pg_readonly -v 17  # PG 17
pig ext install -y pg_readonly -v 16  # PG 16
pig ext install -y pg_readonly -v 15  # PG 15
pig ext install -y pg_readonly -v 14  # PG 14

dnf install -y pg_readonly_18       # PG 18
dnf install -y pg_readonly_17       # PG 17
dnf install -y pg_readonly_16       # PG 16
dnf install -y pg_readonly_15       # PG 15
dnf install -y pg_readonly_14       # PG 14

apt install -y postgresql-18-pg-readonly   # PG 18
apt install -y postgresql-17-pg-readonly   # PG 17
apt install -y postgresql-16-pg-readonly   # PG 16
apt install -y postgresql-15-pg-readonly   # PG 15
apt install -y postgresql-14-pg-readonly   # PG 14

Preload:

shared_preload_libraries = 'pg_readonly';

Create Extension:

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.