Repairing Broken Binary Replication in PostgreSQL 9.0

Question

I have a binary replication that was stopped for so long that the WALs were removed and as a result, it ended up being old. I'm trying to reestablish the replication and the best way I've found so far is following the steps on the PostgreSQL wiki:

• Issue select pg_start_backup('clone',true); on master
• rsync everything except for pg_xlog from master to slave
• Issue select pg_stop_backup(); on master
• rsync pg_xlog

But the database is too big (300 GB), my connection is not really amazing (like 800 kB/s) and the files in base keep changing. So I was wondering if there's a more optimal way to do this.

• András Váczi: I would say that with the current setup you have no chance, to push 300 GB over that line takes more that 100 hours... Furthermore, if your WAL segments are generated more frequently than every 20 seconds, the connection simply can't keep up.

Answer 1

Database files and transaction logs tend to be very fat , so compression makes a big difference try rsync -z or compress the data directory beforehand. Try with xz (former lzma) you wont believe the compression ratio for postgres files.