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
Seems that the postgre can not write to pg_wal due to no space left.
- I check using
$root df -h
- 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
Memory Test(memtest86+) using the boot cd (Ubuntu ISO above). I don't think this step is necessary.
- Reebot the guest OS (I am running the server on VMWare Workstation)
- Make sure Ubuntu consumes all the space allocated. Full instruction here. Adjust to your need.
- Reboot again.
- 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 "
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.