Proposal for Read-Only transactions support in Pgloader for PostgreSQL sources

[alexispires]

Hi 👋

I would like to propose a PR to add support for read-only transactions when using PostgreSQL as source. Currently, Pgloader utilizes read-write transactions by default, which, while necessary for some operations like materialized views, may not always be required for other scenarios.

In my local tests (by modifying pomo configuration), I found that there are situations where read-only transactions would be sufficient and could offer significant benefits. For example, this feature would enable Pgloader to perform migrations from a standby server. Since standby servers are typically used for read-only purposes and can’t handle write operations, having support for read-only transactions would facilitate migrations from such sources without requiring additional setup or configuration.

Introducing an option to specify the type of transaction—read-only or read-write—would provide users with more flexibility and control over their migration processes. This change could enhance performance, reduce the risk of unintended data modifications, and simplify migrations from standby replicas.

Could you please provide your feedback on this proposal? Specifically, I would appreciate your thoughts on the best way to integrate this feature into the main branch, and whether you have any additional considerations or suggestions for the implementation.

Alexis

[alexispires]

Thought something like this :

LOAD DATABASE
    FROM postgresql://user:password@source_host/source_db WITH read-only
    INTO postgresql://user:password@dest_host/dest_db
    ...

or this :

LOAD DATABASE
     FROM postgresql://user:password@source_host/source_db
     INTO postgresql://user:password@dest_host/dest_db

 WITH include drop, create tables, create indexes, ro src transaction

cc @dimitri

[dimitri]

The use-case is clear: running pgloader against a hot-standby / read replica
requires that every connection opened on the source side uses a read-only
transaction, since standbys reject any attempt to write (including implicit
advisory locks that some drivers acquire).

Design

A new WITH option on the source side — read only — would set
SET default_transaction_read_only = on immediately after opening each source
connection, before any catalog query or data read is issued.

LOAD DATABASE
     FROM postgresql://user:pass@replica-host/db
     INTO postgresql://user:pass@primary-host/db
WITH read only, include drop, create tables, create indexes;

All catalog queries and data reads in the pipeline are already plain SELECTs
against the source, so enabling read only there is mostly a pure connection
flag — with the following features disabled:

MATERIALIZE VIEWS … AS <sql> — both v3 and v4 implement this by issuing
CREATE VIEW … AS <user-sql> on the source connection so that pgloader
can introspect its column types from the source catalog, then reads the rows
and drops the view afterwards. That CREATE VIEW / DROP VIEW pair is the
only DDL pgloader ever issues against a PostgreSQL source. When read only
is active, this path must be rejected at parse time with a clear error:

"MATERIALIZE VIEWS with an explicit SQL definition requires a writable
source connection — it creates and drops a temporary view on the source.
Omit read only, or use MATERIALIZE ALL VIEWS to work with views that
already exist on the source."

Everything else is unaffected:

Feature	Works with read only
Full database load (tables, indexes, FKs, sequences)	Yes — catalog queries and data reads are plain SELECTs
MATERIALIZE ALL VIEWS	Yes — reads existing views, no writes to source
ONLY TABLE / EXCLUDING TABLE filters	Yes
CAST / TRANSFORM rules	Yes — applied on the pgloader side
BEFORE LOAD / AFTER LOAD SQL	Yes — those run on the target, not the source

All writes happen on the target connection, which is unaffected.

---

If you'd like to see this feature prioritised, sponsoring its development is
possible through The Art of PostgreSQL OSS sponsorship
program. Sponsored features get
dedicated implementation time and are tracked to completion; the design above
is already in place and ready to go.