could not write to file "pg_wal/xlogtemp.2399" no space left on device

Question

I am generating dummy data with this query :

INSERT INTO tbltest1 (id, kol1, kol2)
SELECT generate_series(1,10000), substr(md5(random()::text), 1, 25), substr(md5(random()::text), 1, 45)
FROM generate_series(1, 1000000);

Then, in the middle of the process, this error comes up : could not write to file "pg_wal/xlogtemp.2399" no space left on the device.

Obviously, that indicates that I don't have the space anymore.

Then I want to go into the database by issuing "psql" command. But this error ".s.PGSQL.5432" comes up : connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed. no such file or directory pops up.

I see that some error will cause this ".s.PGSQL.5432" error. This really a dead end for me.

Why no space left error is issued ? Why WAL files not recycled ? I have the archive_mode = off, wal_level = logical. The WAL should be recycled in this settings. What do I have to do to get the database ON again ?

I see that I still have lots of spaces available (around 20GB more) I am running on VM running on Win 10 host.

It is really haunting me. Please help

Update

Seems that the postgre can not write to pg_wal due to no space left.

1. I check using $root df -h
2. Then I increase the space : Boot from live USB (Ubuntu ISO). Choose "Try Ubuntu" Open gparted Move the HDD space where postgre resides to the end of the disk Expand to consume the free space Reboot the VM, without using the ISO Full instruction here and here too
3. Do Memory Test (memtest86+) using the boot cd (Ubuntu ISO above). I don't think this step is necessary.
4. Reebot the guest OS (I am running the server on VMWare Workstation)
5. Make sure Ubuntu consumes all the space allocated. Full instruction here. Adjust to your need.
6. Reboot again.
7. Login to Ubuntu and start postgres as usual

Now postgres can run normally. No more ".s.PGSQL.5432" error.

So far, I conclude that the no space left on pg_wal can cause the ".s.PGSQL.5432" error.

Now I am facing another error. I can do "Select * from table Limit 10" but I can not do query like "Select count(id) From table".There is error about page block : invalid page in block 62052 of relation base /13462/16387

Any comments or help is really appreciated.

Ubuntu 22.04 running on VM, Postgres 12.

[4

Answer 1

When PostgreSQL creates a WAL segment, it first creates it with the name xlogtemp.<process id> and writes it full of zeros. Only then the file is renamed to its eventual name.

If there is not enough disk space for PostgreSQL to create and fill a new WAL segment, the server will crash. The only way to get it running again is to increase the file system and start PostgreSQL.

If you encounter that problem while bulk loading into the database, that need not mean that something is wrong with WAL archival or WAL recycling. It could also mean that pg_wal is on the same filesystem as the data directory. For production systems, it is a good idea to put pg_wal on a different file system and have a symbolic link pg_wal in the data directory.