1

I have an architecture of cascading replication with 3 postgresql db

enter image description here

Because the master is becoming too big (~ 170Gig), I wanted to run a "cleaning" job over the week-end that would do multiple DELETE operations on Millions of rows by batch and VACUUM table right after. Unfortunately my cleaning script could not complete because disk of DB2 got filled (pg_xlogs?)

2019-06-21 17:41:08.770 UTC [1136] FATAL: could not extend file "base/34163166/44033600.20": No space left on device
2019-06-21 17:41:08.770 UTC [1136] HINT: Check free disk space. 2019-06-21 17:41:08.770 UTC [1136] CONTEXT: xlog redo at 662/6A087C30 for Heap/INSERT+INIT: off 1 2019-06-21 17:41:09.188 UTC [13036] FATAL: could not write to file "pg_xlog/xlogtemp.13036": No space left on device

My disks size are all the same (db1 is bigger because the server has other files on it):

DB1: /dev/sda1 496G 266G 210G 56% /

DB2: /dev/sda1 496G 177G 299G 38% /

DB3 /dev/sda1 496G 177G 299G 38% /

size of the main table is the same on master and replicas

Size | 173 GB

Databases are all running on different VMs. The commands I ran are simple, SELECT count(*) FROM TABLE1 WHERE X, then DELETE FROM TABLE1 WHERE X, then VACUUM TABLE1. And this script is iterating by batch of 5 days on a timestamp column. Hence my VACUUM command is split per table. I've been doing backups before starting the script (with a pg_backup that is then sending the back up files to another backup server)

I am actually not sure why the disk of db2 got filled :/ , would you have any idea ? I need to run my script so I think I might need to stop the replication to run it.

Thank you for your help, cheers

  • Welcome to DBA.SE and thank you for your participation. There are still some missing bits of information. E.g. How big are your disks? How big are the databases (all of them)? Are the database all located on one server/instance/disk/volue? What exact commands did you run? When did you perform backups? Did you perform backups? Was VACUUM performed before or after the backups? Did you split the VACUUM into multiple commands (for each table)? Edit your question and add as much details as possible. – hot2use Jun 24 at 8:51
  • Thank you for the hints, I edited my question – Sangui69 Jun 24 at 9:07
0

The standby server has to write the WAL that it gets from the primary server, it needs enough disk space for that.

Since DB2 is not only a standby, but also a primary server for DB3 and DB4, it might need to retain WAL for a while (I don't know how you configured that).

Just give all standby servers as much disk space as the primary has, then you should be good. Be generous with disk space!

It could be that extra disk space is used if you use replication slots and a standby cannot keep up, perhaps because of a slow network connection. That could be the cause of your out-of-disk condition.

Remember that DELETE and VACUUM do not free any disk space.

  • As I edited in my question, all disks have the same size (secondary and primary) hence it is not a question of disk space I think. Yes it is possible to configure a cascading replication for postgresql, you might be right though because the only bottleneck we have is our lease line between DB2 and DB3/4 which is a tiny 20Mbits/s. There are other data flow transiting on this line, so it's possible DB2 could not replicate fast enough to DB3/4 and then started to stack logs... – Sangui69 Jun 25 at 1:26
  • Are you using replication slots or something else to keep the standbys from falling behind? – Laurenz Albe Jun 25 at 5:59
  • yes a replication slot! – Sangui69 Jun 25 at 7:02
  • That replication slot will retain WAL until the standby has caught up. So that's probably the cause of your problem. – Laurenz Albe Jun 25 at 7:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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