All we need is an easy explanation of the problem, so here it is.
What I have:
Database: PostgreSQL 9.3
- 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).
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 to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂