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

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

Start by reviewing how Pgloader configures PostgreSQL source transactions and the pomo configuration referenced in the issue. Determine where a read-only versus read-write transaction option belongs, then define tests covering migrations from read-only or standby sources. Done means users can select the transaction mode without breaking operations that require read-write access.