Date: Thu, 10 Apr 2025 13:19:17 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: pgsql-general@lists.postgresql.org
Subject: pg_restore causing ENOSPACE on the WAL partition
Message-ID: <076464ad-3d70-dd25-9e8f-e84f27decfba@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset=US-ASCII
UI-OutboundReport: notjunk:1;M01:P0:xSD5B8/NnU4=;C7qzp6gyvndr0A+o0f9U+Q/3j3N
 5yIObfU2ejuGgkuQ3vset7gblNZQc06GOIrTYH9CS7CK0VZNDzmCnUf5ZkBC1v0zQ+VpMcipF
 pgI5R7w1iCnFKNAsPSxBWfV7jhmv86tKhJuPnjRAt3KkYDgb/kjfet3Blx0/l/7BGhH9Turyb
 2g7dww002fal10pOeHwAQKm2lCB/JIX/TdaRKu17YKEV2Y0xZq79eAiYc3sMMScpfJ5rJA4WP
 jLS3Ux8VbIuClFddA+fLSSIDWncZKsJD3TTn9GiGcNpW6pl201hHMa51GQ+15MtzOYViukA1e
 OA50QXpHAFuzkDLdGKLOt08hCPfLuNTa7udc0GnEerZMhT8n50jPE6wNF6n9Eg8bbRappUr0F
 KMJIQRJ1mgDtc63kDHfb67zs9njmMuPRMlPlxT0ZdFuQwPIvJUi7Ia0LPxjC+Sc9aUrJNCce/
 5aiFvIsgfSBSNhp3FKHVn00B0htgOvH0psMCbx0df2HhCW/hqWZjzAJemtQrLvI/8567An/xB
 u0tjkaKN3d3FoHnsWpABnqVK5CmpByyCR9t+ecw5pEDUogYT4j2DR9MgcggAQ2kwktygxFbna
 rz4yBC3+V8uRSVdpe1l/LF4H09VdtSnKvRcPsQibKMXL4ULepWATYfHw7LZIzyR/sNczdIRIE
 AY0JKmKZsXrRO246PCOO2qjTaNQ/Cfcz2+VkaerYlS9p8Q7J54yQNDyMlG3j4uzXm0TsmMI9U
 VS6kVlwBwigFFk2uMgCQzDPoTDKT6HmdmJR+c9N82dzenx7d1JbxgKK84Ncs21ZRcguKHcLNW
 ZV2M7xECCU4bU7SeW79tIoL+kxeDwk1dTHqwy1Tz8XniKuzg69PAOFcEVroLekcNiZral2FAt
 HzoS12OUcgiGW5kHcGh2/aagugEyl1XHltHffp1DZ5wfE+hDCV1KqbfauE4zhNzmftplynN+x
 XDm/dcJzxuc5zNQ3P9VX6LAXs6kIDsMqyj3jAiZKoC5nCg1PputCQz12pq3vsiHSp3VQdGQmP
 0gwLFNBUqAVkNCNHhgInGES7PKCemAqhABCHaoMYtM8T7CgUuev9hLj64qdm+CN+jHhD4GFzr
 KDr+IKKcxY4XmY+IoCy4OULJWuXMzat1IwSOboBGIbzUVJKUWiORyKFskLB7ljOXf49XwYV2I
 ICdYbPzzHYnRJ++RrL9vSC8gqzP31Qqj4hWZbw5Xw3RXbJJ/XiawCvi0phvBikGVxp/OoFoqb
 Bb05YbYP66GlVZ6bINUr0ca2jHFEQoBCSitXANdTp/YUwsgabsTxkVgxQvfBG7EKVQ/uavTKi
 LJhgZTcQaBJ9gxYSDRLkG3VdnPy7tS1VGJcAz8x0Wie4CfojUzq2GzlsewQGO/85/sIi8MtvY
 8QvN4AK4FhIjBRjMOqApvg49sWzORuOTEeU/aCWdPhQRpxmUA34M0yaoED9LK5jq3ZOB/AR5H
 q6Fo6FuP0tMJRtNXrsbsf+Y8QGSg=
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/076464ad-3d70-dd25-9e8f-e84f27decfba%40gmx.net>
Precedence: bulk

Hello list,

last night I got ENOSPACE on the WAL partition while running a huge
pg_restore on an empty and idle database.
The checkpoint that started 7 minutes earlier never finished:

   04:31:09 LOG:  checkpoint starting: wal
   ...
   04:38:04 PANIC:  could not write to file "pg_wal/xlogtemp.546204": No s=
pace left on device
            :CONTEXT:  COPY table_partition_123, line 120872534

This is most likely because most of the data is written to a very slow
"archival" tablespace. No I/O errors on the system logs, I know things go
slow on that device (iSCSI drive over a shared spinning-disks pool and
busy network).

This happened even though I'm keeping the WAL in its own partition (128GB
partition size), with a lot of free space above max_wal_size (64GB).
Somehow it managed to grow above max_wal_size and fill 100% of the
partition.

I'm running latest PostgreSQL 17 and the settings have been temporarily
tweaked for fastest pg_restore:

max_wal_size=3D64GB
max_replication_slots =3D 0
max_logical_replication_workers =3D 0
max_wal_senders =3D 0
wal_level =3D minimal
autovacuum =3D off

Several things seem to have gone wrong here. Questions:

+ The WAL partition is much faster than the archival tablespace. Am I in
   constant danger of overruning max_wal_size? How to make 100% sure this
   never happens again?

+ After recovery, with the database idling, I notice that WAL space usage
   is constant at 64GB. Why doesn't it free up space down to min_wal_size =
(1GB)?

+ I just created a 13GB zstd-compressed tarball of those 64GB WAL
   files. This indicates that the files are compressible despite using
   wal_compression=3Dzstd setting. Could it be that postgres ignores the f=
lag
   and does not compress the WAL? How to check?

+ I'm using parallel pg_restore --data-only, can't avoid that for now.
   Even though all the tables are empty (I truncated everything before
   starting pg_restore), I can't find a way to avoid going through the WAL=
.
   Ideas?


Thanks in advance,
Dimitris