Обсуждение: Synchronization Master -> Slave (on slow connetion)

I've serious problems with slow link between continents, and twice a
week I have to manually reestablish the slave, by running the following
script:


------------------------------------------------------------------------------------------------------------------------------------------

psql -c "SELECT pg_start_backup('standby_sync', true)"
rsync --progress -a ${PGDATA}/*
root@server2.mycompany.com:/opt/pgsql/slave1/ --exclude postmaster.pid
--exclude postgresql.conf --exclude pg_hba.conf --exclude pg_log
--exclude pg_xlog
psql -c "SELECT pg_stop_backup()"


------------------------------------------------------------------------------------------------------------------------------------------

Point is not to analyze the connection issues between the continents,
but if there is a way better to synchronize these servers, and avoid to
transport 8Gb of data between them when syncronizing.
The rsync above can be optimized? Both servers are CentOS 5 with OpenVPN
established between them (this is one of the problems: the VPN keep
falling several times a day, and then finally, the sender and receiver
process just vanish in the air - and I had no time to investigate the
vanishment).

Thanks in advance for your help,

On Thu, Jul 19, 2012 at 3:45 PM, Edson Richter <edsonrichter@hotmail.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.ru@gmail.com Skype: gray-hemp Phone: +79160686204

On Thu, Jul 19, 2012 at 4:27 PM, Sergey Konoplev
<sergey.konoplev@postgresql-consulting.com> wrote:
> On Thu, Jul 19, 2012 at 3:45 PM, Edson Richter <edsonrichter@hotmail.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.

Sorry, here I meant "the stream will be compressed then". See the -C
flag of the ssh utility.

>
> --
> Sergey Konoplev
>
> a database architect, software developer at PostgreSQL-Consulting.com
> http://www.postgresql-consulting.com
>
> Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204



--
Sergey Konoplev

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

Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204