"Read-Only" Mode for PostgreSQL
There are a variety of methods available to route connections with known read-only queries (i.e. queries with
SELECT statements…that are not calling
VOLATILE functions that modify data). This includes connection proxy software like Pgpool-II or framework mechanisms such as Django’s database router.
However, there are situations where you might need to force read-only connections to your primary (read-write) Postgres instance. Some examples include putting your application into a degraded state to perform a database move or upgrade, or allowing an administrator to inspect a system that may be accumulating write-ahead logs that track all changes to the system.
PostgreSQL has a configuration parameter call
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.
Here is a quick example of how
default_transaction_read_only works. First, ensure your system does not have
postgres=# SHOW default_transaction_read_only ; default_transaction_read_only ------------------------------- off (1 row) postgres=# CREATE TABLE IF NOT EXISTS abc (id int); INSERT INTO abc VALUES (1) RETURNING id; CREATE TABLE id ---- 1 (1 row) INSERT 0 1
This works as expected: we’re able to create a table and insert data. Now let’s put the system into
default_transaction_read_only mode (note that I am running this on PostgreSQL 14)
ALTER SYSTEM SET default_transaction_read_only TO on; SELECT pg_reload_conf(); SHOW default_transaction_read_only;
default_transaction_read_only is enabled:
postgres=# SHOW default_transaction_read_only; default_transaction_read_only ------------------------------- on (1 row)
Now verify that writes are disallowed:
postgres=# INSERT INTO abc VALUES (2) RETURNING id; ERROR: cannot execute INSERT in a read-only transaction
default_transaction_read_only is not a panacea: there are some caveats that you should be aware of.
default_transaction_read_only can be overriden in a session, even if the value is set database-wide. For example:
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
Second, 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.
That said, if you have a situation where you need to put a PostgreSQL primary instance into a “read-only” mode temporarily, you can use
default_transaction_read_only to prevent write transactions.