0

I run pg_dump last nite to backup some 111Gb database : time pg_dump -j 5 -Fd -Z 1 db_profans -f /backup-nfs/

I monitor the size growth here

But, suddenly this morning, the database is down. I check the system directory. Root is full. PS : I don't have any access to the server, we have different guys for managing it.

What is going on ? Why all the backup files moving to root ? Or something else causing the root to fill up, maybe like WAL files created during backup or Log files piling up ? I do have replication for this database but it was a really minimal, just synchronizing 300 records daily. And I don't turn on any logging too..

How to delete all the backup files manually ?

Here is the pic for the whole directory.

Updates:

I find out the cause of the problem is actually the archiving process is not written to an external disk. That folder "/Archive", which is supposedly hold the archived WALs, is actually created inside the "root" ! I did not know it because it was not like what I requested the other day before. Now as I delete the archived WALs, the server is up and running again. So the cause is not from pg_dump process.

1 Answer 1

2

It looks like "the other guys" didn't set up the database very well. You don't have the database files in the root file system. Ideally, you create three file systems: one for the data files, one for pg_wal and one for the log files. That way, database data cannot fill up your root file system.

Since you perform pg_dump on the database machine (why??), your backup ends up in the same (and only) file system, and you happened to fill the disk. Don't do that!

5
  • I have WAL archiving folder outside the system and the pg_wal is hardly full, when I do SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}'; it always stays at 100 for weeks (I didn't check prior to this error though..). Are you suggesting that I should create pg_wal directory on a mounted drive ? thanks
    – padjee
    Sep 13, 2022 at 2:52
  • Oh, I see now that you ran the pg_dump on the database server machine. So it was you who filled up the only file system after all. Sep 13, 2022 at 2:56
  • I run the pg_dum with -f option. That option is pointing out to some NFS outside. Prior to issuing pg_dump, I check the /backup-nfs/ too. Seems working fine that time because the directory's size is increasing overtime .
    – padjee
    Sep 13, 2022 at 3:02
  • I run this backup twice... The first one for a database with size only 8 Mb. The directory backup-nfs got filled up. Fine, no problem. The second one, for the 111Gb, fails. I use the exact same script. I also happened to test it on different machine too where the backup location is on the same server but on different drive. Of course this caused hdd to fill up and eventually I end up with "can not create .s.PGSQL.5432" error. I got out of this error easily ; just delete whatever in the backup folder and restart the server. @LaurenzAlbe
    – padjee
    Sep 13, 2022 at 3:09
  • Hi @LaurenzAlbe , your hunch is correct. It was not caused by pg_dump. Actually the pg_wal directory is full. I guess the archiving folder is full too so that the WALs can not be copied there.
    – padjee
    Sep 13, 2022 at 5:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.