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