Date: Wed, 2 Apr 2025 19:46:43 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: Adrian Klaver <adrian.klaver@aklaver.com>
cc: pgsql-general@lists.postgresql.org
Subject: Re: Performance issues during pg_restore -j with big partitioned
 table
In-Reply-To: <7d3fc27a-1229-4739-8d15-f4005dfa2435@aklaver.com>
Message-ID: <422f9998-e190-895f-c1de-a8423cb4531d@gmx.net>
References: <84379bfb-bbda-84e6-cacc-e863ba9d6c37@gmx.net> <7d3fc27a-1229-4739-8d15-f4005dfa2435@aklaver.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="0-422439159-1743616004=:139110"
UI-OutboundReport: notjunk:1;M01:P0:6JluJVung+Q=;ZUPhT/+Ws8PqnP7ttCNw1C7qxZI
 MakN1jI0VKzcB7FxOQtphn04HlQvyF+m6kWxBwe314oAFVL/vfJYM2eCO0yfFLYh7R3b1Bnre
 i0woqLDi6mJK2BrqzWV3siRW8bNSexTH9jVwo6iWqvH8wd/kX7N/QVmzV9N8OZxKhdJBfLWW1
 JpdBOuTusYDnOwJfLPnJV+2B2e9zsbvkRKYss8J/3EM1vaChdSdS4OooliJuSs0UGVIb2gJvs
 vzIoAhufvXRx5zVZNoHne5BAMuB2he/cDpzA/AsyA64gCkuO7jbJ0pInc6/x0uEIxEOSzwxnO
 OpsJFMoiIXYi6yXaMxvAfHLfKBzZEKZv2TqW4JXvOxgnM0a7wts2dyFf6UHhbkRSzJm86eMZm
 Yb723gPcr2c+WIElWpgR2dnI7rQEzbD6tt53xRaCuoHrpe6PtTQeI8Q8r8G31m2hQ/w/trafz
 bgssngO3CXFGXfyyIxr1DDP0KizdafcSKYGyWVgc7PvP92aewm1Kt/OjuX19j89bowGV5bhDz
 nHs3kTAXGW2lAPY63BZcAK1ijvTbJhmQZPaH7zhtxOVtRU4bMXASUmuNP6hqNaA3g0314OZlg
 0bFXMg0GER/1iWJyU65ZIgYLufHYyuvxdPuQRxWJEJVZvMvaBqrBUpZm8ZXBFJym2mEDykZ4A
 yK0PGg9Ly7iis8U9w4ZBLzGCa/E4Aa3fq5bdDSmXYgeod+8BnhvpwIKtA1cGt2s5p/qdS84gc
 k6ezfid6LHE3ILLwm1+el+DCxssTrYTirs+vTiFkH4DtuJufTvryE85qXL5THeFr4jVBl+nPz
 OwB3vcGBhleDlvR9VHsqbJ5HDIL2LpseNCAip5qTgDkSZNDb48vfVK0No5AGtwWO5KmTq8rqm
 pc8+6ZRHNdG3meBxUbRIC8zLgazTpEskm2Qpv13EFa3BHHVOQmwQ5T5QYNGq0knMsP0TdXvws
 yNmX5cEGo05efaFP+au6cpFKCKrkTywAJ8TVFXplm35F0ISa5lhLfrUg4QO6zf+mRfJ8NcljJ
 r53DIcN7mwhrPWZA935ejtpv4eGPsTEAUknS1JfXxN+cek8XY97DmC3NfsL2T90n3FNgSh+2p
 WNC6oReEB37PgvpkTsUWw4kRIYWe/Oa8ChTRJ3otl75qKTSq+ZR0M18cfu8jemY8D3NeKcPMr
 wul3TXvU5adKiemkpehQpXYkZU+MHq8/wgYjsce+cIKI9oF2R4F69cCT8F4QR4XfZldyMv0JF
 s2MXz8SDswrze/G/IwH4jJWnYE+XMuq6+75dI51SS524PN4Or5PiyqVrHXM7j2eDrwAVDqIGC
 HTm8f9S19DA7IkkeJnlVxtMJbGtkoyXprnbx1I8ejCgIUnvOkHaU85i3fRzSQ8pFTp9gQn0XV
 i2U90AYsYs8YMtXZMoD4/yXXSujdqOt3h/G1nfVwSPFi3IdDbF5cRn+02A7qGp4PLi9Ucq0dw
 9FlsSMRp7Kyb6crNWmOrXMz5Tp/k=
Archived-At: <https://www.postgresql.org/message-id/422f9998-e190-895f-c1de-a8423cb4531d%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-422439159-1743616004=:139110
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable

On Wed, 2 Apr 2025, Adrian Klaver wrote:

>
>
> On 4/2/25 10:32 AM, Dimitrios Apostolou wrote:
>>  Hello list.
>>
>>  My database includes one table with 1000 partitions, all of them rathe=
r
>>  sizeable. I run:
>>
>>  =C2=A0 pg_restore -j12 --no-tablespaces --disable-triggers --exit-on-e=
rror
>>  --no-owner --no-privileges -n public -d newdb custom_format_dump.pgdum=
p
>>
>>  Right now after 24h of restore, I notice weird behaviour, so I have
>>  several questions about it:
>>
>>  + 11 postgres backend processes are sleeping as "TRUNCATE TABLE waitin=
g".
>>   =C2=A0 I see that they are waiting to issue a TRUNCATE for one of the
>>   =C2=A0 partitions and then COPY data to it.=C2=A0 Checking the log I =
see that
>>   =C2=A0 several partitions have already been copied finished, but many=
 more
>>   =C2=A0 are left to start.
>>
>>   =C2=A0 Why is a TRUNCATE needed at the start of a partition's COPY ph=
ase? I
>>   =C2=A0 didn't issue a --clean on the command line (I don't need it as=
 my
>>   =C2=A0 database is newly created), and I don't see a mention of relat=
ed
>>   =C2=A0 TRUNCATE in the pg_restore manual.
>
> --clean will drop the object entirely not TRUNCATE.
>
> I'm guessing that this is being done by you per:
>
> https://www.postgresql.org/message-id/53760c70-4a87-a453-9e02-57abc9cb2e=
54%40gmx.net
>
> "After each failed attempt, I need to issue a TRUNCATE table1,table2,...
> before I try again. "

Thanks Adrian. I'm now testing restore without --data-only. All I'm doing
prior to the above pg_restore command is "createdb -T template0 newdb".
It's possible though that I'm missing something here, the whole thing is
way more complicated than I expected...


Dimitris

--0-422439159-1743616004=:139110--