consistent cloning

[herwigg]

Hi,

Currently pgclone cannot be used to create a consistent copy of a live database/schema since the transaction isolation level is set to read only on the source system, which does not guarantee a consistent copy on a live database. pg_dump does take a consistent dump and it uses transaction ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE. I have had foreign key violations becuase the isolation level is not set properly.
There is much less use case for pgclone when it can only produce a consistent copy on a not live database/schema. Do you agree and would you like a consistent copy to be default ? Also enable consistency for parallel copy or copy via background workers.

[valehdba]

Hi, thanks for the careful diagnosis.
Fully agree, the current behavior (just READ ONLY) doesn't guarantee consistency on a live database, because the default isolation level remains READ COMMITTED and each statement takes its own snapshot. As a result, different tables are read at different points in time, leading to FK violations and similar issues. pg_dump's approach (SERIALIZABLE READ ONLY DEFERRABLE + pg_export_snapshot() for parallel workers) is the right reference here.
I think applying default to REPEATABLE READ READ ONLY, with an opt-in flag ({"isolation": "serializable"}) to switch to SERIALIZABLE READ ONLY DEFERRABLE.
Why REPEATABLE READ as the default rather than SERIALIZABLE? In PostgreSQL, REPEATABLE READ is effectively snapshot isolation, snapshot taken at transaction start remains stable across all reads, so cross-table FK consistency is fully guaranteed for a single-transaction dump (or a multi-worker dump using a shared exported snapshot). SERIALIZABLE only adds protection against serialization anomalies on top of that, which is unnecessary overhead for a pure read-only dump. Worse, DEFERRABLE may wait for concurrent serializable transactions to finish, making dump start latency unpredictable. For 99% of use cases (routine backup/clone), REPEATABLE READ is sufficient, fast, and predictable. The remaining 1% (where the source application relies on SERIALIZABLE invariants that must be preserved in the clone) is covered by the opt-in flag.

[herwigg]

ok, I tried it for a schema clone and it works, except that I got an ERROR: pgclone: could not import snapshot 000000A6-000C4299-1 on source: ERROR: invalid snapshot identifier: "000000A6-000C4299-1"
but all data seems to be copied. I will do some more tests.

[valehdba]

thanks again for reporting issues. I have fixed it please check again

[herwigg]

Hi, I am on holiday , will test next week Thanks for the quick response. Herwig Op wo 13 mei 2026, 22:28 schreef Valeh Agayev ***@***.***>:

…

thanks again for reporting issues. I have fixed it please check again — Reply to this email directly, view it on GitHub <#5 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACR3SXU2PKP4CKM64SLZKSL42TLFHAVCNFSM6AAAAACYRGHNK6VHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTMOJQHE3TOMQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[herwigg]

Hi, I tested it again, but same error: could not import snapshot ... on source error: invalid snapshot identifier , after 2h15m20s. Herwig Op wo 13 mei 2026 om 22:28 schreef Valeh Agayev ***@***.***>:

…

thanks again for reporting issues. I have fixed it please check again — Reply to this email directly, view it on GitHub <#5 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACR3SXU2PKP4CKM64SLZKSL42TLFHAVCNFSM6AAAAACYRGHNK6VHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTMOJQHE3TOMQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[valehdba]

Hi,

Are you connecting to the database directly, or are you routing through a proxy like PgBouncer or HAProxy? Also, could you confirm if your source database is a standby node?

I made some code changes and tested this directly on a standby database, and it cloned the schema successfully. However, when I try connecting through HAProxy/PgBouncer, I get an error. Are you also connecting through HAProxy on your end?

ERROR: pgclone: could not import snapshot 00000008-0000064A-1 on source: ERROR: snapshot "00000008-0000064A-1" does not exist

Please try check again I did some fixes

[herwigg]

Hi, I am connecting directly to the database. No pgbouncer or any other tool. But I connect to the master node, the primary , not to a standby instance. if you connect to the standby you can run into problems with wal files that cannot be applied or transaction aborted because the wal file applied and the snapshot is simply not there any more. I will check with the new version. Herwig Op wo 20 mei 2026 om 19:55 schreef Valeh Agayev ***@***.***>:

…

Hi, Are you connecting to the database directly, or are you routing through a proxy like PgBouncer or HAProxy? Also, could you confirm if your source database is a standby node? I made some code changes and tested this directly on a standby database, and it cloned the schema successfully. However, when I try connecting through HAProxy/PgBouncer, I get an error. Are you also connecting through HAProxy on your end? ERROR: pgclone: could not import snapshot 00000008-0000064A-1 on source: ERROR: snapshot "00000008-0000064A-1" does not exist Please try check again I did some fixes — Reply to this email directly, view it on GitHub <#5 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACR3SXXSHDQKLRVQSIBME2343XWQBAVCNFSM6AAAAACYRGHNK6VHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTMOJZGM4TEMA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. You are receiving this because you authored the thread.Message ID: ***@***.***>

[valehdba]

And how big is the size of the database or schema you want to clone? Which function do you use: pgclone.schema or pgclone.database ?