Feedback-ID: i76984098:Fastmail
Content-Type: multipart/mixed; boundary="------------8R3siDWgkN3T51dQMKqb73Pt"
Message-ID: <d6390ed7-771d-4ebb-bea1-749883b25d00@aklaver.com>
Date: Mon, 1 Sep 2025 10:24:15 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: In-order pg_dump (or in-order COPY TO)
To: Dimitrios Apostolou <jimis@gmx.net>
Cc: pgsql-general@lists.postgresql.org
References: <s0491qrn-343s-0757-8sn5-120rr8610qqq@tzk.arg>
 <daf47435-539f-4368-869c-dd19a341399b@aklaver.com>
 <3541781s-75o7-26pp-46pp-qs54o4406192@tzk.arg>
 <c8d89b11-bada-4f23-9ff4-a8ed966f81a2@aklaver.com>
 <4ss66r31-558o-qq24-332q-no351p7n5osr@tzk.arg>
 <0dc17a73-2372-4613-a50e-610ae7d02b93@aklaver.com>
 <E48B611D-7D61-4575-A820-B2C3EC2E0551@gmx.net>
 <ccf26ec1-4fdf-496f-b222-de700071bdc1@aklaver.com>
 <84F8AAF7-8FC6-463C-827F-5D09E24EA911@gmx.net>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <84F8AAF7-8FC6-463C-827F-5D09E24EA911@gmx.net>
Archived-At: <https://www.postgresql.org/message-id/d6390ed7-771d-4ebb-bea1-749883b25d00%40aklaver.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------8R3siDWgkN3T51dQMKqb73Pt
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 8/31/25 10:52, Dimitrios Apostolou wrote:
> When I first said "dump file without TOC" I actually meant "without offsets in the TOC".
> 
> The fact that you get a TOC printed does not prove that the dump file includes a TOC with offsets.

I did some digging in the code and see that the TOC is more then that, 
it stores a range of data. Still have not part where the offsets are 
ignored for writes to stdout, but will keep on digging.

Getting back to your OP:

"Unfortunately after I did pg_restore to a new server, I notice that the
dumps from the new server are not being de-duplicated, all blocks are
considered new."

I ran a test on a much smaller database and I do not see the above. The 
commands and the Borg info for the archive are in attached file.
> 
> All pg_dump -Fc commands that write to stdout, produce a file without offsets in the TOC. It has nothing to do with borg. ToC offsets must be filled in right before streaming each table, but this is impossible when the whole TOC has already been written to stdout in the beginning.
> 
> Dimitris


-- 
Adrian Klaver
adrian.klaver@aklaver.com
--------------8R3siDWgkN3T51dQMKqb73Pt
Content-Type: text/plain; charset=UTF-8; name="pg_borg_test.txt"
Content-Disposition: attachment; filename="pg_borg_test.txt"
Content-Transfer-Encoding: base64

LS1EdW1wIGZyb20gUG9zdGdyZXMgMTcgaW5zdGFuY2UgYW5kIHBpcGUgdG8gQm9yZyBjcmVh
dGUuCnBnX2R1bXAgLUZjIC0tY29tcHJlc3M9bm9uZSAtLW5vLXRvYXN0LWNvbXByZXNzaW9u
IC1kIHBwX2FyY2hpdmUgLVUgcG9zdGdyZXMgIHwKYm9yZyBjcmVhdGUgLS1zdGF0cyAtLXN0
ZGluLW5hbWUgcGdfZmlsZSAgLS1zdGRpbi11c2VyIGFrbGF2ZXIgLS1zdGRpbi1ncm91cCBh
a2xhdmVyIGJvcmdfdGVzdC86OlBnVGVzdF8xNyAtCgotLUR1bXAgZnJvbSAxNyBpbnN0YW5j
ZSBhbmQgdGhlbiByZXN0b3JlIHRvIDE4YjMgaW5zdGFuY2UKcGdfZHVtcCAtRmMgIC1kIHBw
X2FyY2hpdmUgLVUgcG9zdGdyZXMgLWYgcHBfYXJjaGl2ZV8xNy5vdXQKcGdfcmVzdG9yZSAt
YyAtQyAtZCBwb3N0Z3JlcyAtVSBwb3N0Z3JlcyAtcCA1NDM0IHBwX2FyY2hpdmVfMTcub3V0
CgotLUluaXRpYWwgUG9zdGdyZXMgMTggZHVtcCBhZnRlciByZXN0b3JlIGZyb20gUG9zdGdy
ZXMgMTcgZHVtcApwZ19kdW1wIC1GYyAtLWNvbXByZXNzPW5vbmUgLS1uby10b2FzdC1jb21w
cmVzc2lvbiAtZCBwcF9hcmNoaXZlIC1VIHBvc3RncmVzIC1wIDU0MzQgfApib3JnIGNyZWF0
ZSAtLXN0YXRzIC0tc3RkaW4tbmFtZSBwZ19maWxlICAtLXN0ZGluLXVzZXIgYWtsYXZlciAt
LXN0ZGluLWdyb3VwIGFrbGF2ZXIgYm9yZ190ZXN0Lzo6UGdUZXN0XzE4XzEgLQoKLS1TZWNv
bmQgUG9zdGdyZXMgMTggZHVtcC4KcGdfZHVtcCAtRmMgLS1jb21wcmVzcz1ub25lIC0tbm8t
dG9hc3QtY29tcHJlc3Npb24gLWQgcHBfYXJjaGl2ZSAtVSBwb3N0Z3JlcyAtcCA1NDM0IHwK
Ym9yZyBjcmVhdGUgLS1zdGF0cyAtLXN0ZGluLW5hbWUgcGdfZmlsZSAgLS1zdGRpbi11c2Vy
IGFrbGF2ZXIgLS1zdGRpbi1ncm91cCBha2xhdmVyIGJvcmdfdGVzdC86OlBnVGVzdF8xOF8y
IC0KCgotLVVwZGF0ZSBzaW5nbGUgZmllbGQgYWNyb3NzIDY0NDEyOSByb3dzIG9mIGEgdGFi
bGUuCnBnX2R1bXAgLUZjIC0tY29tcHJlc3M9bm9uZSAtLW5vLXRvYXN0LWNvbXByZXNzaW9u
IC1kIHBwX2FyY2hpdmUgLVUgcG9zdGdyZXMgLXAgNTQzNCB8CmJvcmcgY3JlYXRlIC0tc3Rh
dHMgLS1zdGRpbi1uYW1lIHBnX2ZpbGUgIC0tc3RkaW4tdXNlciBha2xhdmVyIC0tc3RkaW4t
Z3JvdXAgYWtsYXZlciBib3JnX3Rlc3QvOjpQZ1Rlc3RfMThfMyAtCgoKYm9yZyBpbmZvIC0t
bGFzdCA0IGJvcmdfdGVzdC8KCkFyY2hpdmUgbmFtZTogUGdUZXN0XzE3CkFyY2hpdmUgZmlu
Z2VycHJpbnQ6IDE0MTYzMjUxYTg0ZjkzZTk5MmI4MzA0YmJlYWMxNTA1NmRhYTQ1Y2UzYzhj
NWY3NjY1NWFmOWVlMWVmNjQxZGUKQ29tbWVudDoKSG9zdG5hbWU6IG1hdXJhZG9nClVzZXJu
YW1lOiBha2xhdmVyClRpbWUgKHN0YXJ0KTogTW9uLCAyMDI1LTA5LTAxIDEwOjA3OjIwClRp
bWUgKGVuZCk6IE1vbiwgMjAyNS0wOS0wMSAxMDowNzoyMwpEdXJhdGlvbjogMi44MCBzZWNv
bmRzCk51bWJlciBvZiBmaWxlczogMQpDb21tYW5kIGxpbmU6IGJvcmcgY3JlYXRlIC0tc3Rh
dHMgLS1zdGRpbi1uYW1lIHBnX2ZpbGUgLS1zdGRpbi11c2VyIGFrbGF2ZXIgLS1zdGRpbi1n
cm91cCBha2xhdmVyIGJvcmdfdGVzdC86OlBnVGVzdF8xNyAtClV0aWxpemF0aW9uIG9mIG1h
eGltdW0gc3VwcG9ydGVkIGFyY2hpdmUgc2l6ZTogMCUKLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tCiAgICAgICAgICAgICAgICAgICAgICAgT3JpZ2luYWwgc2l6ZSAgICAgIENvbXByZXNz
ZWQgc2l6ZSAgICBEZWR1cGxpY2F0ZWQgc2l6ZQpUaGlzIGFyY2hpdmU6ICAgICAgICAgICAg
ICAyMDguODcgTUIgICAgICAgICAgICAgNDIuMDAgTUIgICAgICAgICAgICAgIDYuOTIgTUIK
QWxsIGFyY2hpdmVzOiAgICAgICAgICAgICAgODM1LjQ3IE1CICAgICAgICAgICAgMTY4LjA5
IE1CICAgICAgICAgICAgIDg0LjkwIE1CCgogICAgICAgICAgICAgICAgICAgICAgIFVuaXF1
ZSBjaHVua3MgICAgICAgICBUb3RhbCBjaHVua3MKQ2h1bmsgaW5kZXg6ICAgICAgICAgICAg
ICAgICAgICAgMTYyICAgICAgICAgICAgICAgICAgMzI0CgpBcmNoaXZlIG5hbWU6IFBnVGVz
dF8xOF8xCkFyY2hpdmUgZmluZ2VycHJpbnQ6IDVmZDg4YTk5YjU3NjVkMGFmNzFkNDUwYjkx
NzE1N2ZhMWQ2ODI2OTFkYjMzMzA2ZTU1MGZmN2QwNTk3ZmIwNWYKQ29tbWVudDoKSG9zdG5h
bWU6IG1hdXJhZG9nClVzZXJuYW1lOiBha2xhdmVyClRpbWUgKHN0YXJ0KTogTW9uLCAyMDI1
LTA5LTAxIDEwOjA4OjE1ClRpbWUgKGVuZCk6IE1vbiwgMjAyNS0wOS0wMSAxMDowODoxNwpE
dXJhdGlvbjogMi4yNCBzZWNvbmRzCk51bWJlciBvZiBmaWxlczogMQpDb21tYW5kIGxpbmU6
IGJvcmcgY3JlYXRlIC0tc3RhdHMgLS1zdGRpbi1uYW1lIHBnX2ZpbGUgLS1zdGRpbi11c2Vy
IGFrbGF2ZXIgLS1zdGRpbi1ncm91cCBha2xhdmVyIGJvcmdfdGVzdC86OlBnVGVzdF8xOF8x
IC0KVXRpbGl6YXRpb24gb2YgbWF4aW11bSBzdXBwb3J0ZWQgYXJjaGl2ZSBzaXplOiAwJQot
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KICAgICAgICAgICAgICAgICAgICAgICBPcmlnaW5h
bCBzaXplICAgICAgQ29tcHJlc3NlZCBzaXplICAgIERlZHVwbGljYXRlZCBzaXplClRoaXMg
YXJjaGl2ZTogICAgICAgICAgICAgIDIwOC44NyBNQiAgICAgICAgICAgICA0Mi4wMCBNQiAg
ICAgICAgICAgICAgMS41OCBNQgpBbGwgYXJjaGl2ZXM6ICAgICAgICAgICAgICA4MzUuNDcg
TUIgICAgICAgICAgICAxNjguMDkgTUIgICAgICAgICAgICAgODQuOTAgTUIKCiAgICAgICAg
ICAgICAgICAgICAgICAgVW5pcXVlIGNodW5rcyAgICAgICAgIFRvdGFsIGNodW5rcwpDaHVu
ayBpbmRleDogICAgICAgICAgICAgICAgICAgICAxNjIgICAgICAgICAgICAgICAgICAzMjQK
CkFyY2hpdmUgbmFtZTogUGdUZXN0XzE4XzIKQXJjaGl2ZSBmaW5nZXJwcmludDogZmNkMTEz
Mjk5YWZkNjY3M2MxMGU0NGRjZDYyMTcyMmE0NmUzNTg3MWQ3MTcxYjI0YTNkN2Q2MWUzZjQ4
ZDFkYQpDb21tZW50OgpIb3N0bmFtZTogbWF1cmFkb2cKVXNlcm5hbWU6IGFrbGF2ZXIKVGlt
ZSAoc3RhcnQpOiBNb24sIDIwMjUtMDktMDEgMTA6MDg6NDMKVGltZSAoZW5kKTogTW9uLCAy
MDI1LTA5LTAxIDEwOjA4OjQ1CkR1cmF0aW9uOiAyLjE3IHNlY29uZHMKTnVtYmVyIG9mIGZp
bGVzOiAxCkNvbW1hbmQgbGluZTogYm9yZyBjcmVhdGUgLS1zdGF0cyAtLXN0ZGluLW5hbWUg
cGdfZmlsZSAtLXN0ZGluLXVzZXIgYWtsYXZlciAtLXN0ZGluLWdyb3VwIGFrbGF2ZXIgYm9y
Z190ZXN0Lzo6UGdUZXN0XzE4XzIgLQpVdGlsaXphdGlvbiBvZiBtYXhpbXVtIHN1cHBvcnRl
ZCBhcmNoaXZlIHNpemU6IDAlCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQogICAgICAgICAg
ICAgICAgICAgICAgIE9yaWdpbmFsIHNpemUgICAgICBDb21wcmVzc2VkIHNpemUgICAgRGVk
dXBsaWNhdGVkIHNpemUKVGhpcyBhcmNoaXZlOiAgICAgICAgICAgICAgMjA4Ljg3IE1CICAg
ICAgICAgICAgIDQyLjAwIE1CICAgICAgICAgICAgICAxLjU4IE1CCkFsbCBhcmNoaXZlczog
ICAgICAgICAgICAgIDgzNS40NyBNQiAgICAgICAgICAgIDE2OC4wOSBNQiAgICAgICAgICAg
ICA4NC45MCBNQgoKICAgICAgICAgICAgICAgICAgICAgICBVbmlxdWUgY2h1bmtzICAgICAg
ICAgVG90YWwgY2h1bmtzCkNodW5rIGluZGV4OiAgICAgICAgICAgICAgICAgICAgIDE2MiAg
ICAgICAgICAgICAgICAgIDMyNAoKQXJjaGl2ZSBuYW1lOiBQZ1Rlc3RfMThfMwpBcmNoaXZl
IGZpbmdlcnByaW50OiBkYmMwZGRjZmRhZDU2NmQzOGFiM2UyMTBlMzc2NDkwZTNlNWNkMTUx
MGM5Y2Q0NTg2MWI5YWI1NTBhMDQ5MTNmCkNvbW1lbnQ6Ckhvc3RuYW1lOiBtYXVyYWRvZwpV
c2VybmFtZTogYWtsYXZlcgpUaW1lIChzdGFydCk6IE1vbiwgMjAyNS0wOS0wMSAxMDowOToz
MwpUaW1lIChlbmQpOiBNb24sIDIwMjUtMDktMDEgMTA6MDk6MzYKRHVyYXRpb246IDIuNjUg
c2Vjb25kcwpOdW1iZXIgb2YgZmlsZXM6IDEKQ29tbWFuZCBsaW5lOiBib3JnIGNyZWF0ZSAt
LXN0YXRzIC0tc3RkaW4tbmFtZSBwZ19maWxlIC0tc3RkaW4tdXNlciBha2xhdmVyIC0tc3Rk
aW4tZ3JvdXAgYWtsYXZlciBib3JnX3Rlc3QvOjpQZ1Rlc3RfMThfMyAtClV0aWxpemF0aW9u
IG9mIG1heGltdW0gc3VwcG9ydGVkIGFyY2hpdmUgc2l6ZTogMCUKLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tCiAgICAgICAgICAgICAgICAgICAgICAgT3JpZ2luYWwgc2l6ZSAgICAgIENv
bXByZXNzZWQgc2l6ZSAgICBEZWR1cGxpY2F0ZWQgc2l6ZQpUaGlzIGFyY2hpdmU6ICAgICAg
ICAgICAgICAyMDguODcgTUIgICAgICAgICAgICAgNDIuMDkgTUIgICAgICAgICAgICAgMzQu
NDAgTUIKQWxsIGFyY2hpdmVzOiAgICAgICAgICAgICAgODM1LjQ3IE1CICAgICAgICAgICAg
MTY4LjA5IE1CICAgICAgICAgICAgIDg0LjkwIE1CCgogICAgICAgICAgICAgICAgICAgICAg
IFVuaXF1ZSBjaHVua3MgICAgICAgICBUb3RhbCBjaHVua3MKQ2h1bmsgaW5kZXg6ICAgICAg
ICAgICAgICAgICAgICAgMTYyICAgICAgICAgICAgICAgICAgMzI0Cgo=

--------------8R3siDWgkN3T51dQMKqb73Pt--