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@xxxxxxxxxxx
--Dump from Postgres 17 instance and pipe to Borg create.
pg_dump -Fc --compress=none --no-toast-compression -d pp_archive -U postgres |
borg create --stats --stdin-name pg_file --stdin-user aklaver --stdin-group aklaver borg_test/::PgTest_17 -
--Dump from 17 instance and then restore to 18b3 instance
pg_dump -Fc -d pp_archive -U postgres -f pp_archive_17.out
pg_restore -c -C -d postgres -U postgres -p 5434 pp_archive_17.out
--Initial Postgres 18 dump after restore from Postgres 17 dump
pg_dump -Fc --compress=none --no-toast-compression -d pp_archive -U postgres -p 5434 |
borg create --stats --stdin-name pg_file --stdin-user aklaver --stdin-group aklaver borg_test/::PgTest_18_1 -
--Second Postgres 18 dump.
pg_dump -Fc --compress=none --no-toast-compression -d pp_archive -U postgres -p 5434 |
borg create --stats --stdin-name pg_file --stdin-user aklaver --stdin-group aklaver borg_test/::PgTest_18_2 -
--Update single field across 644129 rows of a table.
pg_dump -Fc --compress=none --no-toast-compression -d pp_archive -U postgres -p 5434 |
borg create --stats --stdin-name pg_file --stdin-user aklaver --stdin-group aklaver borg_test/::PgTest_18_3 -
borg info --last 4 borg_test/
Archive name: PgTest_17
Archive fingerprint: 14163251a84f93e992b8304bbeac15056daa45ce3c8c5f76655af9ee1ef641de
Comment:
Hostname: mauradog
Username: aklaver
Time (start): Mon, 2025-09-01 10:07:20
Time (end): Mon, 2025-09-01 10:07:23
Duration: 2.80 seconds
Number of files: 1
Command line: borg create --stats --stdin-name pg_file --stdin-user aklaver --stdin-group aklaver borg_test/::PgTest_17 -
Utilization of maximum supported archive size: 0%
------------------------------------------------------------------------------
Original size Compressed size Deduplicated size
This archive: 208.87 MB 42.00 MB 6.92 MB
All archives: 835.47 MB 168.09 MB 84.90 MB
Unique chunks Total chunks
Chunk index: 162 324
Archive name: PgTest_18_1
Archive fingerprint: 5fd88a99b5765d0af71d450b917157fa1d682691db33306e550ff7d0597fb05f
Comment:
Hostname: mauradog
Username: aklaver
Time (start): Mon, 2025-09-01 10:08:15
Time (end): Mon, 2025-09-01 10:08:17
Duration: 2.24 seconds
Number of files: 1
Command line: borg create --stats --stdin-name pg_file --stdin-user aklaver --stdin-group aklaver borg_test/::PgTest_18_1 -
Utilization of maximum supported archive size: 0%
------------------------------------------------------------------------------
Original size Compressed size Deduplicated size
This archive: 208.87 MB 42.00 MB 1.58 MB
All archives: 835.47 MB 168.09 MB 84.90 MB
Unique chunks Total chunks
Chunk index: 162 324
Archive name: PgTest_18_2
Archive fingerprint: fcd113299afd6673c10e44dcd621722a46e35871d7171b24a3d7d61e3f48d1da
Comment:
Hostname: mauradog
Username: aklaver
Time (start): Mon, 2025-09-01 10:08:43
Time (end): Mon, 2025-09-01 10:08:45
Duration: 2.17 seconds
Number of files: 1
Command line: borg create --stats --stdin-name pg_file --stdin-user aklaver --stdin-group aklaver borg_test/::PgTest_18_2 -
Utilization of maximum supported archive size: 0%
------------------------------------------------------------------------------
Original size Compressed size Deduplicated size
This archive: 208.87 MB 42.00 MB 1.58 MB
All archives: 835.47 MB 168.09 MB 84.90 MB
Unique chunks Total chunks
Chunk index: 162 324
Archive name: PgTest_18_3
Archive fingerprint: dbc0ddcfdad566d38ab3e210e376490e3e5cd1510c9cd45861b9ab550a04913f
Comment:
Hostname: mauradog
Username: aklaver
Time (start): Mon, 2025-09-01 10:09:33
Time (end): Mon, 2025-09-01 10:09:36
Duration: 2.65 seconds
Number of files: 1
Command line: borg create --stats --stdin-name pg_file --stdin-user aklaver --stdin-group aklaver borg_test/::PgTest_18_3 -
Utilization of maximum supported archive size: 0%
------------------------------------------------------------------------------
Original size Compressed size Deduplicated size
This archive: 208.87 MB 42.09 MB 34.40 MB
All archives: 835.47 MB 168.09 MB 84.90 MB
Unique chunks Total chunks
Chunk index: 162 324
