Re: Synchronization Master -> Slave (on slow connetion)

On Thu, Jul 19, 2012 at 3:45 PM, Edson Richter <edsonrichter(at)hotmail(dot)com> wrote:
> The rsync above can be optimized? Both servers are CentOS 5 with OpenVPN

Yes, it can be optimized. You can turn compression on by specifying
-z. The compression level 1 is the one that performs best for my
needs. You can find out yours best by experimenting.

rsync -av --delete -z --progress --compress-level=1 \
--exclude pg_xlog --exclude *.conf --exclude postgresql.pid \
/db/data db2:/db/

But it is not the end. The replication's stream is not compressed
itself and because of the nature of xlog it is quite bloat (if it can
be said so). If you have a problem with the bandwidth it can be a
reason of replication lagging and breaking.

I usually start the following sniplet via screen utility on a master
db. It will forward the localhost:5432 on the master db to the
localhost:2345 on the replica.

while [ ! -f /tmp/stop ]; do
ssh -C -o ExitOnForwardFailure=yes -R 2345:localhost:5432 replica_address \
"while nc -zv localhost 2345; do sleep 5; done";
sleep 5;
done

The stream will be forwarded then. Direct your replica to
localhost:2345 in the recovery.conf file.

--
Sergey Konoplev

a database architect, software developer at PostgreSQL-Consulting.com
http://www.postgresql-consulting.com

Jabber: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204