Re: Most efficient way to initialize a standby server

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.

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.

> 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.

--
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