Message-ID: <455d28421ae33c73b73a6f527d2f72816ca5dd29.camel@cybertec.at>
Subject: Re: Experience and feedback on  pg_restore --data-only
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Dimitrios Apostolou <jimis@gmx.net>, pgsql-general@lists.postgresql.org
Date: Sun, 23 Mar 2025 17:00:39 +0100
In-Reply-To: <53760c70-4a87-a453-9e02-57abc9cb2e54@gmx.net>
References: <53760c70-4a87-a453-9e02-57abc9cb2e54@gmx.net>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/455d28421ae33c73b73a6f527d2f72816ca5dd29.camel%40cybertec.at>
Precedence: bulk

On Thu, 2025-03-20 at 23:48 +0100, Dimitrios Apostolou wrote:
> Performance issues: (important as my db size is >5TB)
>=20
> * 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 function to=
 ALTER all
> =C2=A0=C2=A0 tables to UNLOGGED, but failed with "could not change table =
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.

> Any feedback for improving my process? Should I put these ideas somewhere
> as ideas for improvement on pg_restore?

You put your ideas in the right place.

Yours,
Laurenz Albe