Date: Mon, 24 Mar 2025 16:51:30 +0100 (CET)
From: Dimitrios Apostolou <jimis@gmx.net>
To: Adrian Klaver <adrian.klaver@aklaver.com>
cc: Laurenz Albe <laurenz.albe@cybertec.at>, 
    pgsql-general@lists.postgresql.org
Subject: Re: Experience and feedback on pg_restore --data-only
In-Reply-To: <832c1cdd-c0fe-464b-b4b9-f9d0482b9b78@aklaver.com>
Message-ID: <7e990eae-e55c-0d04-1be8-f49bb3251073@gmx.net>
References: <53760c70-4a87-a453-9e02-57abc9cb2e54@gmx.net> <455d28421ae33c73b73a6f527d2f72816ca5dd29.camel@cybertec.at> <5f1ebeda-f080-cb31-75c0-ce2211ea348f@gmx.net> <832c1cdd-c0fe-464b-b4b9-f9d0482b9b78@aklaver.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="0-1272647902-1742831491=:1515035"
UI-OutboundReport: notjunk:1;M01:P0:y95aa+ws8Mc=;VMnXMCq9SnykLRk4NKmwRtGCBOr
 BniIqa/4MOnigXPmm6S2inaL1QtR+r9xxtFECxlid8K4cOCkxeLDCkHfSZ7kP14NtVuInQnvv
 5bzAZ8dLiyV4CWprFmP8mGMu8U/rtUSL2arOiHQaVEWSJeBsK6fui+DKnLmt12XJVIfLaBCLO
 DcdDzA8wHtGLRZIs6RELlLVtTVCNuZfeenIwSXfAyFUNbkfWsDwZ5LwLFMaNDjWWtpv5MX6ya
 4OOFw8V68jlPQ9vOSwyTAs/0LfPzLYkoQJYpa6a8srfrWt76bmherzdHdwY1SfwUcHECnuQo9
 8HYwDwtWwS9HZlwIpC4XLCB4C04TahEWUDV4VB6+ji1SHGsVPcaUAZZB9qhvnfCsH2FiI4mi0
 aGpo/yb5kdc5SCAkRapQyK35/ntXsKJxauXJWYB/YXY14sz6rLc9nJCA4wn4707TXLP5xjPNp
 8RmfKqMgOfDGe8fr4jMbTu66T0zxbVpSocgbw3dcgehrOC5plFGoE7ouN9yE6Tl8/MgUYw+GF
 no9h/dg9FwhXWf/696xh55feGUGNBg3INmeGKU4UP8g26QumkNq7iZjPPEzA1oMsxefJy9OzW
 AWFuLo1XGtAwz8FtdMj7FEaUHQrJNJr8DoVDkOJW953b08sV47iUYekSJ7ODt09Mn2xeNZNmE
 Ey8WyZBUcGweTLBfjvTsILQ9+QuofHoIXmpfzp5y2g47IdsPFLm/9+BoVzVD2q1fqOkut1xHv
 uERdJJh8oT6xOek963QU/n2JB8XDxOe99/XjNIfyVSjr27L7p5f3Kbrh8orVOASN95b/7m8Tj
 kiOMRe0GXjva/9H5SIujhVbZsQoyNK34xks8lTVaLOniPc+pcTziu30cl9ifRn/7YtUkxokkp
 agwg5NByT5VVddvodMVkQFYmkYspitwi6OltSJWooWM8SnK8+9MaYZWpBVCYdDRGPvKm3L5uG
 toqVSL/3XxswuoEFKe1BtK/reSRRRJf5688ct6DonA1gIFHTy6WVKdWmBAVacu0ej1dC0BmVg
 obbp9wILoAharQPG1AJbiMXD5P6VhIXXUwKuvreohuCRFRXxJpVeBkGzxZlmCuI2Ip38yO7tW
 QJFsk7mlBwVsO4TYMAKQc4nrR8TjFMwiiMplmlM//g7R709nRrUF7jRVyVvFTcFKpjM5KqhNr
 xOqaC7XUwE+jXF7YRhZgB1RY1qcuKN7XxsUzDBX0dSF9lvkK/jw0lhu88Od6zm77A2Ua6XnQE
 yNimu0eH8butIyEiVvWkLXv1dIZ/9qXHK7a3m2woAdw82cB3CY341/i5H/Y2OGOo2Jt60dhai
 BzpS4a6uEyp5Ysoi/7dtbrtwFP6IY8+dxueAsDVlJTFAJN766JnyPgvX0mDzjwkU84srF14O3
 A4BAFFlPRVV2jw2xv49rJ7l/Knq/pFVVvNDlpAxPu5dr2ZTYkft3dMxIKLx7UjzRe+xoRoZu/
 jUkA9wQ==
Archived-At: <https://www.postgresql.org/message-id/7e990eae-e55c-0d04-1be8-f49bb3251073%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-1272647902-1742831491=:1515035
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable

On Mon, 24 Mar 2025, Adrian Klaver wrote:

> On 3/24/25 07:24, Dimitrios Apostolou wrote:
>>  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
>>>>  having
>>>>  =C2=A0=C2=A0 setting wal_level=3Dminimal. I even wrote my own functi=
on to ALTER all
>>>>  =C2=A0=C2=A0 tables to UNLOGGED, but failed with "could not change t=
able T to
>>>>  =C2=A0=C2=A0 unlogged because it references logged table".=C2=A0 I'm=
 out 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.
>>
>>  That would restore the schema from the dump, while I want to create th=
e
>>  schema from the SQL code in version control.
>
>
> I am not following, from your original post:
>
> "
> ... create a
> clean database by running the SQL schema definition from version control=
, and
> then copy the data for only the tables created.
>
> For this case, I choose to run pg_restore --data-only, and run it as the=
 user
> who owns the database (dbowner), not as a superuser, in order to avoid
> changes being introduced under the radar.
> "
>
> You are running the process in two steps, where the first does not invol=
ve
> pg_restore. Not sure why doing the pg_restore --data-only portion in sin=
gle
> transaction is not possible?

Laurenz informed me that I could avoid writing to the WAL if I "create and
load the table in a single transaction".
I haven't tried, but here is what I would do to try --single-transaction:

Transaction 1: manually issuing all of CREATE TABLE etc.

Transaction 2: pg_restore --single-transaction --data-only

The COPY command in transaction 2 would still need to write to WAL, since
it's separate from the CREATE TABLE.

Am I wrong somewhere?

>>  Something that might work, would be for pg_restore to issue a TRUNCATE
>>  before the COPY. I believe this would require superuser privelege thou=
gh,
>>  that I would prefer to avoid. Currently I issue TRUNCATE for all table=
s
>>  manually before running pg_restore, but of course this is in a differe=
nt
>>  transaction so it doesn't help.
>>
>>  By the way do you see potential problems with using --single-transacti=
on
>>  to restore billion-rows tables?
>
> COPY is all or none(version 17+ caveat(see
> https://www.postgresql.org/docs/current/sql-copy.html  ON_ERROR)), so if=
 the
> data dump fails in --single-transaction everything rolls back.

So if I restore all tables, then an error about a "table not found" would
not roll back already copied tables, since it's not part of a COPY?


Thank you for the feedback,
Dimitris


--0-1272647902-1742831491=:1515035--