Thread: Most efficient way to initialize a standby server
I've two distant servers I would like to configure async replication between. Servers are running 9.2.4. 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. 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. I've look in wiki pages and also pg_basebackup, but information on how to get data transfered to the remote standby server is vague. Or should I run pg_basebackup from standby server, and the PostgreSQL protocol is more efficient than rsync? Thanks, Edson
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@hotmail.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.ru@gmail.com
On 05/27/2013 05:43 PM, Sergey Konoplev wrote: > > 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@hotmail.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. I think the use of PITRTools is probably up your alley here. JD
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@hotmail.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.ru@gmail.com > >
Em 28/05/2013 00:03, Joshua D. Drake escreveu: > > On 05/27/2013 05:43 PM, Sergey Konoplev wrote: >> >> 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@hotmail.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. > > I think the use of PITRTools is probably up your alley here. > > JD > Assume I know nothing about PITRTools (which I really don't know!), can you elaborate a bit more your suggestion? Thanks, Edson
On 05/27/2013 08:13 PM, Edson Richter wrote: >> I think the use of PITRTools is probably up your alley here. >> >> JD >> > Assume I know nothing about PITRTools (which I really don't know!), can > you elaborate a bit more your suggestion? It is an open source tool specificaly for working with PITR/Streaming Replication/Hot Standby https://public.commandprompt.com/projects/pitrtools/wiki JD > > Thanks, > > Edson > >