Feedback-ID: i76984098:Fastmail
Message-ID: <c8d89b11-bada-4f23-9ff4-a8ed966f81a2@aklaver.com>
Date: Wed, 27 Aug 2025 08:25: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>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <3541781s-75o7-26pp-46pp-qs54o4406192@tzk.arg>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/c8d89b11-bada-4f23-9ff4-a8ed966f81a2%40aklaver.com>
Precedence: bulk

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