Date: Mon, 24 Mar 2025 15:24:17 +0100 (CET)
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: <5f1ebeda-f080-cb31-75c0-ce2211ea348f@gmx.net>
References: <53760c70-4a87-a453-9e02-57abc9cb2e54@gmx.net> <455d28421ae33c73b73a6f527d2f72816ca5dd29.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="0-390209832-1742826258=:1515035"
UI-OutboundReport: notjunk:1;M01:P0:J0pieDXZ1Mc=;AsjM4e9bt4YOCJaR/M6EdFIt+sA
 M+eoM1jiOKz+PkqcxX3JedzS92V8vPkKEi4Mgcit7vnYp5QHpoqqQhc0vA4JjSkqH81CHquN6
 XMaSvA8KNC8nSMmaJHVE408SSIAMD3ldTBqdNMGjCa7QOEvhHGJB3lGjPUFV8ruuuNX0GNB1i
 g0rii81qqepFqvFI3CXEpvCTRxViJCur7gOpq1DfSaGTOjuDCftdWmqNVKPxq6YgpHpwpX+xO
 vFjnSwTRWCYOjxLpaiQC+Vva+xWf+hfgI9NZccxOn7jJX7cOybok/yXNV43pf4WCegKOSQFw+
 4KRQnhfB4p+SXyPeqPCZiwXEviktXR0Had/XcL1dMX1jH1uqKraeYM5jTRw7v+jy2ANVUtIwK
 BRp/PXaB7TJ1rQ29goaY8Ez+c2u2KVREohMacXQUhhDdqqX2usnpfnivzgGjgkykWJQsYLya7
 20RXjFdTOCsNrPkD0CE/hQ7wBqgtLbCSycQ0XD8OF/eQ4nR6WHtZxdNoBh23cYaq7tAEwY1gY
 2FaS40Y6H7q/CoxHSDdEX9Qyq73ETlZQxPNepmduPY5wodoUY4VaY0a17Uppei3utkIEDZ2fx
 SoV+y2UXfZZpCMxGZydFeYJuIpBAFbE+vWmWJkaFtwS2bZjMjSCL1HMZugmQ+v4wPvsU8ri+Q
 CKBL/1LTsbBMxXVwrq4hfrycZmV7uxqBLUx3m2rsMHOt59HT2ID+frcKDCyc5xgztwtuzJI7x
 uzuZUSNL0e/jdBYvZ4z4QzriW4rGI2pNne3p0i1jlff8WKPeHLZBncShoYPoKdTLt8ikFLA0g
 7nSKcFa69wFhYzmWoj9CNEeuTREUyNCc8vOcnpOz0wSrG7PfXL3hzBp440LDAEMtT0ELv8zAy
 bGF22m90H5jmvDhsTETWxRyN0ax3nYah7/qr9JoRPMxO+fy//4TKCvh/z27NJlsM65yNH/Adz
 ouJLm8X1VrBVa6z93M4LEJWKHW3l8FJNCtt0EsMddAXXzuEioqHjx3BxH0nOjfTuszIt8dlkc
 /Y+dUb7WSOR5OnMojAc45OSoCoOSr3Vjbd0wnplyCjJaDyi3dPsDwUx6lHg9dyEPAbYEK+EFP
 tjdpjPe/UCgEtJIRWw4GzGr99+EKCEUq+zn3IL4GqF1z3Qacc+JvZvJyHjQJk7ADpxHeszh11
 ccUVoTWGm1mwdv0I2LbvQp29M7RIBcICesbePzeFm/GDDvwW4X2vmY1rtl0H0hPkX+38Fq/qS
 GNJcnm0IreXHY3emSFrsex2Y7J4mRhuBJzxKJp+aplsFHKTg32XCyrbD00HO9McuN4Kp0vJWo
 xA70m1LwQ/XPXuQdRBbeA7Ep+sBbczwHVvtd58m97+FYwC56vOFtJ5LPjYlaEAOqkd3VmGZ/Y
 QekmXSTQSAIg5Y0auTb52lmkGhIOQFamb/0KR0bUGIq7tzgBAj/cLN0hlcBVKSSuyDUBe+1Je
 I2/CxEnMhU2k09zHfR4rBOwFv5jg=
Archived-At: <https://www.postgresql.org/message-id/5f1ebeda-f080-cb31-75c0-ce2211ea348f%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-390209832-1742826258=:1515035
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable

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.

That would restore the schema from the dump, while I want to create the
schema from the SQL code in version control.

Something that might work, would be for pg_restore to issue a TRUNCATE
before the COPY. I believe this would require superuser privelege though,
that I would prefer to avoid. Currently I issue TRUNCATE for all tables
manually before running pg_restore, but of course this is in a different
transaction so it doesn't help.

By the way do you see potential problems with using --single-transaction
to restore billion-rows tables?


Thank you,
Dimitris

--0-390209832-1742826258=:1515035--