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.klaver@aklaver.com