Re: In-order pg_dump (or in-order COPY TO)

Ron Johnson <ronljohnsonjr(at)gmail(dot)com> writes:
> On Tue, Aug 26, 2025 at 4:31 PM David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>> The logical dump has no ordering - it will come out however it comes out.
>> "COPY <table> TO ..." doesn't have an order by clause - there is no way to
>> make or communicate to it that ordering is important.

> Doesn't COPY TO copy out records in the order they appeared in the physical
> files?

It emits whatever a sequential-scan plan would emit. If you set
synchronize_seqscans = off (which pg_dump does), that will match
physical row order.

At least with our standard table AM. If you're using Aurora or
one of those other PG forks with proprietary storage layers,
you'd have to ask them.

I suspect the OP's problem is not row order per se, but differing
TIDs or XIDs, which are things pg_dump does not endeavor to
replicate. Or, given that he said something about blocks, maybe
he's actually sensitive to where the free space is.

regards, tom lane