public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: Dimitrios Apostolou <[email protected]>
Cc: [email protected]
Subject: Re: In-order pg_dump (or in-order COPY TO)
Date: Wed, 27 Aug 2025 08:25:15 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
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
[email protected]
view thread (22+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: In-order pg_dump (or in-order COPY TO)
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox