Postgresql – Repairing Broken Binary Replication in PostgreSQL 9.0

postgresqlreplication

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.

Best Answer

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.

Related Question