Date: Wed, 27 Aug 2025 18:10:58 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: Adrian Klaver <adrian.klaver@aklaver.com>
cc: pgsql-general@lists.postgresql.org
Subject: Re: In-order pg_dump (or in-order COPY TO)
In-Reply-To: <c8d89b11-bada-4f23-9ff4-a8ed966f81a2@aklaver.com>
Message-ID: <4ss66r31-558o-qq24-332q-no351p7n5osr@tzk.arg>
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>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="0-1727961628-1756311073=:1535142"
UI-OutboundReport: notjunk:1;M01:P0:YExK8kCMPM4=;379AORNeOo8SraD5jDh39rpXLe6
 kLDSUQmyE2BhivMtwa+aRnJSyAnrwKe5uKkgf+KKWW4pVay9IfM8QSvYfHz+TnwJ6j/j+nroN
 tJBnnEA2F456ujE8UrEcl5SR35jT/zsHlDKRhYc7RGC/iFnNVD/O13B4sNJalw0MlNeOHiVM/
 WtB3p0YPzco7OlWv2eTz18GwjuV7mxpzPxcvqOBLoSp/HLTsz5B3O/58NH5CPrAcVk+K3vp+X
 fXzr+BRWngugSM2A/g3QYjnspVHcbFwmXlWTm1aTF3Hq3x0EKqzC3fTm9OPpz+SV7KndYQiXn
 T/Yr4ceIGv9M3mfOIqeU0whmQnjdfEZwMxZ/h6r4J5JKh9PKenafWVcjIXkdP49vQK3cwIhQ7
 D3wM6uuU9Ah7bWy1uajTXdwOw/3RmvryPnNu+36NIwbQ8wI57hBikNRnbYNsaFKt8yBHncrNX
 35c5uB7cdIaBt+XPBBesZVI17qbJ1zBYwa3lvEQSDnVcbfU5HaLrSMoKGivHGcpxrAsHCvBnV
 S04FQJrWmxoPEx4wVbQYT9lK36GNq+XKd0O97as4N7MUmdyQw9y2v3lQVTQ2HgAcTZGPzEzV0
 OxZUr5bw3PjcBcKqqGBzsEd8if5PSJE4xndWB2gdOHy5JXkcvDUKpNopURwWESa5E4TN6LzIk
 uKnIihFDpoACsBLEh5xdpLh2RWhoMto2JNpIzyKt9JSfOBO3NSxffJGabv/0WgZX0Q+bmE34r
 GNvInU+mOAoeAabnELxaCYVnMwNxzjz52EBihjo3XRErYXOY4IxdagtbI0E0E87PGu2IFbtf7
 LFAo8eb20TsyNTRZg1SL0fgIMEOTm4TskF5ps8Vw/ybgLSL4TEURhvbaXX/XN09RURMRpFuS0
 Ekg86Qs4pvplEt+WYAtQwViRyP9R5L4xBIQfJNF8KFFnKPg4tGVkQ4ax29Y65+JIHzk8gu7jx
 Ac47WVwWdY93u+zH91++Gdp3PGuW+EUQFOtk2cmN02Q9jKFbfGaVt+bhG/X60ykN7pzI27VDz
 MVzxi/59ICDJV/XOHlG6SifsAX3xW7VGGtPVRWMQnWhOfD5LANxd9fRpd/R7rygkJLhuXiNl7
 TbBWFedKCtn+sNinoo71Bz1IihQRlRjjmtxqXWA6vTZrM6KcWH2DWHaNt4yI/qwzSx3iVf/rA
 WUo0RJNw3YR4rec0WTmUfLAjEljmd1/vwfpm54NzGJVtLMCLueN5DOq0+su4mhjE8SFV+uSNk
 btUAlBL6u2NFsnGfl4ShmkI8ei/rjRgLohl/sK/xnJEsYyCO9HghzpEwz2QgvAZYcKDW2jlfU
 8JgUb03/iBHhJV3c8t6Q2jhhLiMgDPk2O9cI/xyj4yLjVNwga3teBGnQY7jWp+TD8dTiYz6pH
 AeFu/aocm4rBrEzGPIPicWEileAEeYZJxsVtLXfMUwmCdVpmhuEQP9+Sv6yJXOqbO/yOccsL1
 Fe9pNgOSs1C6JAWkMiZnfXGq5ktGtmVqcgvmtbyDwCexpJ4cKI07PidkrZ5JJq3zcoy9mYK+S
 /D/GReLdiDG8yueFU3uAXHSbKXKSVPph8aE8o1QCnYV/NQP01+ubDai7o5s4FYTG7iG1QTZog
 gMvlwzzFv6iqOjgjTU4P+nvvegWhrxp60tw/wPgojuHyKyATA0IhM/avU1cYQaGwG/Y3EA+E9
 S2UsDrQkzYkI2eGczl6aLBmz8IGfwNqJkTciFrUQN8NX/yG8xCmL6b0d3ucfg0IBUw8cVxTiC
 HkBO7vci4x3J7yVzlDJWmw9GNErUpp5ur67pE27EXJfoLlqHj02P50MFldUDjYbENPMrExQzf
 L909gvoOugxVgvu1socKZBFyFIm6iuTXKGo3YqOU81uL/JEFzF6wMj5g5Ih0/TkH7COY1T4C7
 ojemEl6SJHNxNtTpqleVkVZ6cpwHHloBsJQ+OXOge6gDIxJoKg77/ENHYiNu6dtmqwQbXH5Jz
 LXqbrM0O3pUvEla6uv55dO70/mxnvQ9NVFBf+/nwQ2nE5aJYxgHiFJ7PpYoWTmqNTlUGPm4n9
 47jDR0idJ0jJs79AGvemiws+hsR8KolMLTqBuuyI4XjqmvJ4zd9OolMLi6f9z3JAWRpXgp68i
 OR3bb/wuSehZ6WffZ62FAkY4dIDhfUhI2hD8A3ga9PX9zEVjTfoEXyWWIUArtzLGg7RgT2ANV
 28zEDO9faw36pu4TY1DzAPra6etz29hEfgBueS34Vw8yCQsuGT1vvEKBkAuG8meRRUSab2v9N
 39AEBVPDH37Wzpl6CmFxpZO+7NpvW+BjCVzTgwQZchRPSXyRfNBqaOEzDBoHDKnWdg2lV3gb8
 vMFRj+y+9lwz72jTtjk+hyKSmAQvGnfhFcDLnpVekOeDcv4Lifm9vOf3g0HBtS24s9rhmbt3r
 dpgzdwHkOLFcEd+IW8CUAHXnLHpfEKr+/IzGBqSx43V60/GHNxnmsZOLjEtx+0xlFi4MfqHm1
 +YWj2ItLNHB4p4RZCZaVpSyA1qdI3m2pNwVrzVeyOpjEAlF+qnj65wMCotLTXRAQjHAI8r6zQ
 js4xx1TGFN4quYcMHYxArKE9eBOrNiHopsVIPmDEqUhrSXC9LaJr5iMF3RxQTuSd5+HCG4t2Q
 KhFWX7XNKLQWL7TCItJxKs7EC7fDTjIJfiQ7sMlaV720sjWk/WVEl5ZPKchXeootVYghbn6BT
 YTKufZ/wWPOnI/EH97pt20tMeYzi8AhdVQOpPAcn7FcZ8oZHoB7qSiJscuS57/t33/eqMcS2d
 TSKeRhrOzQ46HrLM7d7onYPrXv3CB1k2yEwc4ioKiP51MoUI+/G0l3ZEuNpiUioOc3W1FVGS/
 yvKfx15MIxZ7MUOnHjxSkywOpviX3urt286We2FzgjR28hHkBYSnc+KvqC2ulSRPqQfxqR+qb
 mDWjbwWPCngmnLLEwI8nYd9IoXf6DpQnWLlQQmPKTzTZpv/UhXipIan0hiOK457Bqbb0Ym0rP
 p4nq6ZVLd18rbzo+2PrKHIqkBT6QsFGeGkgl5OThF8EjZfyYK9+UKFV7ctPJQ73deyZm6Ga1/
 XWDoWWHrMT32rAiE2ExqneiV6Co1UHadSy9J415F6o3wnn80/ItlqvSGTlj7NHnvpd/zUe6nd
 9oWjeyAJ//G+dcyq1ZkCjDH1oqWMIcBQUP0RNTBIahujsyO4i44Tzn3QISpsZjaGKtd50NxWk
 uVgB2KNGYCVg+VpRFkdfr0QkEYn7Yq2rnN1naWmUGKuiN/r7hMMx/dN6TqpRw2NPk6FgOGZco
 VmNGPFoJz54zid0e/xpo/ICUBkxmMTG4Tbyj6WpOjuH8E9dbUYmsyFTsXCkk+dfpg7FAd5fXv
 VmlaEimCYEjmcmZWfO55Y71476zPMXS/UL8PAqw9vKOq4sHlW+j3t8sI9lVodOkj1OLUvjvlm
 E9GFKthqEbUS1s00S6Gw51ts4hn/Q92X6QK2KDZeplr34jJYONrE7G+bXWbp7Ugct6NYLg78M
 MkEK+bhro9aSKeVIBLND0ylF3LZW6AU2vh1HER5znJODl3AzUsMOYkp9H2MXNxpSHEzWSTC/U
 LL6keOEwhb/YRzyYtp/x9JGzsNDhngbJDCxy9tBifnPq3B6TuIsbK56kiBG1SaROjRBNtB9xb
 60FhhUSLJQWpsDw9oFzHQaUyox4+kvszJLa0cVg0QgnPknCuPg+ZqtJFbVF8+iebWoFQ3R1Ms
 0L2z28CQcBBIhTblO56gd8foHeaAIPLJ4sHWiU1uSyJHk1bN3QGUTMGvRauouNIFT2J/2fzkd
 wUPiHtuTtssWagm4OsqwANnzpiRB/E2O+HtIrx5JI3lIGdh09KafVFcTqSLWDteTInCPYrwJQ
 zMUUikcK/N8kaD3ZNpIbgauAHLsECCQP8eUgjOnEH9oBYk6pD7nrBZ0oaiFWqyzw5nxupAOzH
 RiTapvzKWbG6NBUijAaOlDtC3sDqOvwKmvhG0DbyS2++8Mez56IergDwK1ntetugEt9e1CIt8
 exajXdC66Fwm5iZgDsSGfMcXz0lZtKwS7zlh+eoWQfGhAqCqeU+qOTda35InF0pzoP2Axls2g
 5AsqzTRcWCIAEqlszu8bpPYH3WH4ma0f9VBtZAifqRiGlkKQqohnr3s+pU4/FcKAc2JyQpEPi
 PPyi//6DDeZiBacmqzspE4xd3cFM+/0FihRD68kzQutEWY+kxzUu3qywhhkWeko9BVb65tMhG
 Bb1M+D42UozY5NDBE9sZfYszRa/7M1nv8lebF5iJO9Vfiag4bU9xNXVswKsV4SWXVNW4yx7B7
 yNFEbKxgWirnUmO2hdnf5apYtTgs25WVNasptUg83YEO4ZAVpeDIj/gxcMV7ooEGBGQp/anjy
 3MZ7duJNJ8+K5DqYqgaY+BiwsUg8F6CQ1MDxf45ii7JENucOxgT+y+WXhc6K+nCJBxonMSF3l
 sPyUGDLFZq8JgTdv04imprBB1oX1Ss2d/HVvwFUtqpL5gW8tai/lvZSiF8cDY7TxoxaNcWITq
 XVJR9ckCefkaYCYx9+XSS7/LYDdiABLqpXJGMJNCHDf7L7xFWgNEarD65y+jpDzLBgvpk8lPO
 vuBNF8at4Jvf8FM2nJzgV6XGv3spwb2+8ELEAksah1iQK1b7yynkAgd6WW2kU2oxxB1dLLC7l
 N7N/X+Gh1uR2rjklD+I74w/mfN2YTAuOZfH/rDZRjg9ZDt2Prd0Ato3pVdmjjl8O5qy99E3po
 fV3Rpr0TgJRadkAR+2MxCxTawiC3O2G8d1Q3CsFyM01XSatJVG9IyxjKYRCy4pUh7Q5x/IyT7
 lVuHPMKwV533w7lhmfr
Archived-At: <https://www.postgresql.org/message-id/4ss66r31-558o-qq24-332q-no351p7n5osr%40tzk.arg>
Precedence: bulk

  This message is in MIME format.  The first part should be readable text,
  while the remaining parts are likely unreadable without MIME-aware tools.

--0-1727961628-1756311073=:1535142
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable


On Wednesday 2025-08-27 17:25, Adrian Klaver wrote:

> Comments in line below.
>
>>  Dump is from PostgreSQL 16, it's pg_dump writing to stdout:
>>
>>  pg_dump -v --format=3Dcustom --compress=3Dnone --no-toast-compression =
=2D-
>>  serializable-deferrable db_name=C2=A0 |=C2=A0 borg create ...
>>
>>
>>  As you can see the backup (and deduplicating) program is borgbackup.
>
> Ok, I use BorgBackup and it is fairly forgiving of normal changes.
>
> FYI, if you ever want to use compression check out gzip --rsyncable, I h=
ave=20
> found it plays well with Borg. For more  information see:
>
> https://beeznest.wordpress.com/2005/02/03/rsyncable-gzip/

Yes, zstd has also --rsyncable. In this case I let borg do per-chunk=20
compression after deduplication, it has worked well so far.

>>  Restore is in PostgreSQL 17:
>>
>>  I first create the empty tables by running the DDL commands in version
>>  control to setup the database. And then I do pg_restore --data-only:
>>
>>  pg_restore -vvvv -j 8 -U db_owner -d db_name --schema=3Dpublic --
>>  section=3Ddata=C2=A0 dump_file
>
> If you are using only the --data section why not --data-only in the pg_d=
ump?

I want the dump to be as complete as possible. Didn't think it would=20
create issues.

>
> Or is the pg_dump output used for other purposes?

It has happened that I have selectively restored user schemas from that=20
dump.

>>  Worth noting is that the above pg_restore goes through the WAL, i.e. a=
ll
>>  writes are done by walwriter, not the backend directly.
>
> Please explain the above further.

The COPY FROM data is going through the WAL, as usual INSERTS do. The=20
writes to disk happen by the walwriter process.

OTOH, If you have configured the server with wal_level=3Dminimal and=20
BEGIN a transaction, CREATE or TRUNCATE a table, and then COPY FROM into=
=20
that table, then the backend process writes directly to the table=20
without logging to the WAL.

This can be much faster, but most importantly it avoids situations of=20
WAL overflow that are very difficult to predict and can mess your server=
=20
up completely. [1]

[1] https://www.postgresql.org/message-id/flat/076464ad-3d70-dd25-9e8f-e84=
f27decfba%40gmx.net

My patches are for activating that codepath in pg_restore, but they were=
=20
not used on purpose and I took notice that the writes went via WAL.

>
> The problem occurs when you do the pg_dump after this restore, correct?

Correct. The first pg_dump from the restored pg17 is not deduplicated at=
=20
all. Most of the tables have not changed (logically at least; apparently=
=20
they have changed physically).

>
> Is it the same pg_dump command as you show above?

Yes.

>
>>
>>  Postgres is standard open source running on own server. It has a coupl=
e of
>>  custom patches that shouldn't matter in this codepath.
>
> For completeness and just in case they may affect the output what do the=
=20
> patches do?

Two patches for speeding up scanning an archive without TOC, like the=20
one I'm having (because it is piped into borg, instead of written to=20
file). These were activated, but shouldn't matter. They only build the=20
TOC in pg_restore's memory.

https://commitfest.postgresql.org/patch/5809/
https://commitfest.postgresql.org/patch/5817/

And two patches for speeding up pg_restore like mentioned above, under=20
specific arguments that I didn't provide. (one speedup needs --clean,=20
and the other needs --freeze).

https://commitfest.postgresql.org/patch/5821/
https://commitfest.postgresql.org/patch/5826/

IIRC I did not activate them (via --clean) because TRUNCATE fails when=20
foreign keys exist. See the discussion threads.


Dimitris

--0-1727961628-1756311073=:1535142--