What I have:

Database: PostgreSQL 9.3

Table T,

  • Structure: 10 integers/bools and 1 text field
  • Size: Table 89 GB / Toast 1046 GB
  • Usage: about 10 inserts / minute
  • Other: reltuples 59913608 / relpages 11681783

Running cascading replication: Master -> Slave 1 -> Slave 2

  • Replication Master -> Slave 1 is quite fast, a good channel.
  • Replication Slave 1 -> Slave 2 is slow, cross-continent, about 10 Mbit/s.

This is a live, used database with about 1.5TB more data in it.

What's needed to be done:

  • Drop all data to start with a fresh setup (to do constant cleanups and not allow it to grow this big).

Question: What would be the most efficient way to achieve this:

  • without causing huge lags between Master and Slave 1
  • without causing Slave 2 to get irreversibly lagged to a state where catching up is not possible

As I see it:

  • Safe way - do a copy, swap places, DELETE data constantly watching lag
  • Other way - do a copy, swap places, DROP table - but this would cause enormous amounts of data at once and Slave 2 would get lost?
  • How clean? Can't you pg_dump the structure of the tables, then drop the entire database and recreate everything again? May 21 '21 at 10:29
  • @Colin'tHart I don't get what you mean, this is one of the 400 tables, and database is live. The problem is with dropping one table that causes too much load on replica networks May 21 '21 at 12:42

DROP TABLE sends very little information over the WAL stream, and the information it does send is independent of how many rows the table has in it.

  • Can you elaborate on it? What kind of information does it send? How to estimate the payload that will go through WAL stream? Maybe some links May 21 '21 at 6:23

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.