Re: Most efficient way to initialize a standby server

Em 27/05/2013 21:43, Sergey Konoplev escreveu:
> Try this step-by-step instruction
> https://code.google.com/p/pgcookbook/wiki/Streaming_Replication_Setup.
> I constantly update it when discovering useful things, including low
> bandwidth issues.

Thanks. This is a good idea, of course!
I also have a lot of lessons learned, I think I should write down somewhere.

>
> On Mon, May 27, 2013 at 5:08 PM, Edson Richter <edsonrichter(at)hotmail(dot)com> wrote:
>> Since 9.0 days I do use script with rsync for transfer. And sometimes the
>> servers get out of sync (due large processing in master database and huge
>> network latency), and I have to reinitialize the standby server.
> WAL stream is not compressed and quite bloated by its nature. You can
> use SSH tunnel with compression, described in the mentioned above
> instruction, and redirect your replication through it.

Ok, I've setup a compressed VPN secure tunnel that I use for replication.
Is very stable and is compressed. This should be enough.

>
>> Lately , this script take about an hour to copy all data (23GB) over the
>> standby server, and I would like to know if there is a more efficient way
>> (perhaps, using pg_basebackup?) to reinitilize the standby server.
> AFAIK pg_basebackup does not use compression either when transferring
> data. In this case you can also use compressed SSH tunnel with
> pg_basebackup or rsync with compression enabled.
>
> I would also like to recommend not to set the compression level too
> high, because your CPU might be a bottleneck in this case, and it
> might lead to even worth transfer speed that without compression. I
> usually set compression level to 1 and it works quite good.

Good to know. I was thinking in using 9 - I've decent 2 Xeon processors
with 8 cores each - but I think only one is used by gzip algorithm.

Thanks for all your tips, I'll make some testing. If I discover anything
useful, I'll share as well.

Regards,

Edson

>
> --
> Kind regards,
> Sergey Konoplev
> PostgreSQL Consultant and DBA
>
> Profile: http://www.linkedin.com/in/grayhemp
> Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
> Skype: gray-hemp
> Jabber: gray(dot)ru(at)gmail(dot)com
>
>