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

On 8/27/25 05:09, Dimitrios Apostolou wrote:
> On Wednesday 2025-08-27 00:54, Adrian Klaver wrote:
>

>> What are the pg_dump/pg_restore commands?
>>
>> What are the Postgres versions involved?
>>
>> Are they community versions of Postgres or something else?
>>
>> What is the depduplication program?
>>
>>
>
>

Comments in line below.

> Dump is from PostgreSQL 16, it's pg_dump writing to stdout:
>
> pg_dump -v --format=custom --compress=none --no-toast-compression --
> serializable-deferrable db_name  |  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
have found it plays well with Borg. For more information see:

https://beeznest.wordpress.com/2005/02/03/rsyncable-gzip/

>
>
> 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=public --
> section=data  dump_file

If you are using only the --data section why not --data-only in the pg_dump?

Or is the pg_dump output used for other purposes?

>
>
> Worth noting is that the above pg_restore goes through the WAL, i.e. all
> writes are done by walwriter, not the backend directly.

Please explain the above further.

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

Is it the same pg_dump command as you show above?

>
> Postgres is standard open source running on own server. It has a couple
> of custom patches that shouldn't matter in this codepath.

For completeness and just in case they may affect the output what do the
patches do?

>
>
>>>  Thanks in advance,
>>>  Dimitris

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com