Date: Wed, 2 Apr 2025 19:42:01 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: Laurenz Albe <laurenz.albe@cybertec.at>
cc: pgsql-general@lists.postgresql.org
Subject: Re: Experience and feedback on pg_restore --data-only
In-Reply-To: <455d28421ae33c73b73a6f527d2f72816ca5dd29.camel@cybertec.at>
Message-ID: <7e315548-805d-0c11-e143-acdcff8660e5@gmx.net>
References: <53760c70-4a87-a453-9e02-57abc9cb2e54@gmx.net> <455d28421ae33c73b73a6f527d2f72816ca5dd29.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="0-336073191-1743615722=:139110"
UI-OutboundReport: notjunk:1;M01:P0:yNMeB9KHsPA=;4L/6KuwGYMMYEab2OxeP4+VVHfk
 bfLnyPVH/NTi8yHjiPzOePFEczcoxB3J/E288ionepuyvhtOQbRmq8qQ1QRC1+TbExnV/tqLx
 96ZYv5Hz5BBpHguIBKIgSdNhEf2+/nNgDKlO/i87rwl4PM8CS9z4NK2Tvmfn9hhyAnrQRwjwM
 7jEl87xgAOamOqyfgOffPNXyEyjzAV82R8YNip+1kpjrlpNjl5NND+VtmEQ6Og7x8DTkbYBqm
 Ay27KAQ6p1nM/gAaHQGvPYFb7oL0L+N+BMBqOTgP2p8eoFNMzuoItS9zZSFSw1mThXsJm1+rK
 Anoy51SR3S6CECx4ku1NokBDbDOQHnJbWabtL+GjyrbZnraFyUaqBDdkjtmYxdz7EF80hOqy/
 ilO7ZBpcUUlrpnKDw1KusOZ5rgG1XhuizVD/KPODSFZImc890SoGDeHmGxLPYg9Z4bRevSb9b
 rvoWQYL8L2XhmQBvTDE0oJcBSaZE+HaaVnmQ/epY/3vcsPi1a+iKU0N6mAo4FIXMAyxdvE0ja
 6tDb8YNFFhFmZgmwqTKxjshcHDwHzZ2HRGY9bxsmcDyAtHdSERUQA7wSM1EvHFH4Vy6pjUcev
 uRe6aYguzZdtyogCMLh4PAE0KgJ5vZWOw3Nn3BkO+VOmlf9OGt28+PmB221eY7E+eXDQokDj9
 bj1IXsAaJBmvqpl1Ou2ZrMuJueJ5cIRrupnrX4EG5wWhvXggj2QKMZ3BU4rH2+RM8h1hrnMws
 FTAfOIXSqTpW3Ji+LZGGNBeQvcUVTxz71od4c5+xg+hRARxvH5RKgvJgGzuAIAH6TvJzXGrRE
 +e+cCfsM6/yvcQ5wkCZnxuw+f89ZG6XKU3QaqqAHuD6wyFs2vMb8xm14C0vZUY5348JpALSGn
 KLJwAoSpwp+Kt8FAwN9EKGAsSZVL2jsxfPQJ/QDGRMQCioFeqy0T7BOOWM3oLVYDWLo+A/YYD
 VoMSsj3Y62M8iFDf45sr8mo2/yK21mHTNeabi7VIUQ9xLU4pafQ+vUg48rBJBU8xu5eDprnHG
 Dh0nbO2NIORmXIIZ64HwrYXf8/DP2BBhmaQTnrSMP/KZnCLjDKJ8x52K8xOt84RI1WmSSUPQj
 zn096Jo08UV0EyFItVCgEivdZb9tsPPBFegknKDIGzICOWyMGEVvA4LSgBW31VOIcVvyDrsxa
 ufBu9WgiDSuWRRblQ6Uth6q8S82S9czt6QrOjE8q4BvGKwk6/125kIha+pBpk/zN8s37n1VTg
 LLoTML8o9gKzX5igLM//6WbntlqHZ3rRsH6YZASW1Gm+KSTHcpr4GACLF6h3bEsm4BG1iYCb8
 zRjXmYYqVlUiIHjTBoyawDQe878Lpk+P7D2l4IylvTIYBEgQH6IJxJWwvZv77Ari0BC4hzhQR
 ZvpEidzDQkB3fYhd9XKrH3b5EezOPLhbGMRbuBpMxQ96GAUz1Bl3p/8fO1ZYGhGy0EaZrSfPj
 0umSiLQ==
Archived-At: <https://www.postgresql.org/message-id/7e315548-805d-0c11-e143-acdcff8660e5%40gmx.net>
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-336073191-1743615722=:139110
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable

Hi Laurenz,

On Sun, 23 Mar 2025, Laurenz Albe wrote:

> On Thu, 2025-03-20 at 23:48 +0100, Dimitrios Apostolou wrote:
>> Performance issues: (important as my db size is >5TB)
>>
>> * WAL writes: I didn't manage to avoid writing to the WAL, despite havi=
ng
>> =C2=A0=C2=A0 setting wal_level=3Dminimal. I even wrote my own function =
to ALTER all
>> =C2=A0=C2=A0 tables to UNLOGGED, but failed with "could not change tabl=
e T to
>> =C2=A0=C2=A0 unlogged because it references logged table".=C2=A0 I'm ou=
t of ideas on this
>> =C2=A0=C2=A0 one.
>
> You'd have to create an load the table in the same transaction, that is,
> you'd have to run pg_restore with --single-transaction.

Do you know why --single-transaction can't be combined with -j ?
I'm thinking it's because every process would need to have its own
transaction, which apparently results to more than one transaction.

Do you think this could be alleviated somehow with subtransactions?

I'm trying to think of a way to enjoy the performance benefits of
=2D-single-transaction, while also parallelizing the pg_restore. For this
use case I don't care about the content appearing all in one transaction.
Would it be possible then to have dependency resolution in such a way that
we split the tasks into one transaction per worker process?

Just thinking out loud here, I'd be interested in feedback.

Thanks,
Dimitris

--0-336073191-1743615722=:139110--