Thread: Postgres replication question :- One master 2 slaves 9.0.10
Hi all -
Currently we have set up one master one slave , which working fine. Now we need to replicate to an other slave. The problem we have , the port that we use on primary can not be reached from the new slave. We can't the change the primary port also, because many applications using it. I can't reach out to my primary using the recovery.conf on the new slave.
Can you suggest how I can handle this? Appreciate your help.On Mon, Sep 30, 2013 at 7:14 PM, akp geek <akpgeek@gmail.com> wrote:
Hi all -Currently we have set up one master one slave , which working fine. Now we need to replicate to an other slave. The problem we have , the port that we use on primary can not be reached from the new slave. We can't the change the primary port also, because many applications using it.I can't reach out to my primary using the recovery.conf on the new slave.Can you suggest how I can handle this? Appreciate your help.
Why can't you reach it? Is it a firewall?
if so basically you have two options. The first is you can configure your firewall to allow the connection. The second is you can tunnel through using another port/service like SSH or IPSec ESP.
Best Wishes,
Chris Travers
Regards
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
it is a firewall issue. they can't open the port that we requested it.
so as you mentioned tunnel to the primary via tunnel. will give that a tryregards
On Mon, Sep 30, 2013 at 11:10 PM, Chris Travers <chris.travers@gmail.com> wrote:
On Mon, Sep 30, 2013 at 7:14 PM, akp geek <akpgeek@gmail.com> wrote:Hi all -Currently we have set up one master one slave , which working fine. Now we need to replicate to an other slave. The problem we have , the port that we use on primary can not be reached from the new slave. We can't the change the primary port also, because many applications using it.I can't reach out to my primary using the recovery.conf on the new slave.Can you suggest how I can handle this? Appreciate your help.Why can't you reach it? Is it a firewall?if so basically you have two options. The first is you can configure your firewall to allow the connection. The second is you can tunnel through using another port/service like SSH or IPSec ESP.Best Wishes,Chris TraversRegards--Best Wishes,Chris TraversEfficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
One more thing.. pardon me for being dumb
I want to set the 2 nd slave as HOT STAND BY, not steaming ..On Tue, Oct 1, 2013 at 10:14 AM, akp geek <akpgeek@gmail.com> wrote:
it is a firewall issue. they can't open the port that we requested it.so as you mentioned tunnel to the primary via tunnel. will give that a try
regardsOn Mon, Sep 30, 2013 at 11:10 PM, Chris Travers <chris.travers@gmail.com> wrote:On Mon, Sep 30, 2013 at 7:14 PM, akp geek <akpgeek@gmail.com> wrote:Hi all -Currently we have set up one master one slave , which working fine. Now we need to replicate to an other slave. The problem we have , the port that we use on primary can not be reached from the new slave. We can't the change the primary port also, because many applications using it.I can't reach out to my primary using the recovery.conf on the new slave.Can you suggest how I can handle this? Appreciate your help.Why can't you reach it? Is it a firewall?if so basically you have two options. The first is you can configure your firewall to allow the connection. The second is you can tunnel through using another port/service like SSH or IPSec ESP.Best Wishes,Chris TraversRegards--Best Wishes,Chris TraversEfficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
On Tue, Oct 1, 2013 at 2:03 PM, akp geek <akpgeek@gmail.com> wrote: > One more thing.. pardon me for being dumb > > I want to set the 2 nd slave as HOT STAND BY, not steaming .. Hot standby assumes being streaming. You can not establish a hot standby without using streaming replication. What is the reason not to do it streaming? BTW, you will find the SSH tunnel instructions here http://code.google.com/p/pgcookbook/wiki/Streaming_Replication_Setup -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
thanks. I can try this. Any idea for the message below. Thanks for the patience
I tried tunneling this morning and it did not work. when tried the tunneling command in the url you mentioned getting following error. I will try to find what exactly this mean , but any help is appreciated.
command-line: line 0: Bad configuration option: ExitOnForwardFailure
command-line: line 0: Bad configuration option: ExitOnForwardFailure
command-line: line 0: Bad configuration option: ExitOnForwardFailure
Regards
On Tue, Oct 1, 2013 at 6:46 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
On Tue, Oct 1, 2013 at 2:03 PM, akp geek <akpgeek@gmail.com> wrote:Hot standby assumes being streaming. You can not establish a hot
> One more thing.. pardon me for being dumb
>
> I want to set the 2 nd slave as HOT STAND BY, not steaming ..
standby without using streaming replication. What is the reason not to
do it streaming?
BTW, you will find the SSH tunnel instructions here
http://code.google.com/p/pgcookbook/wiki/Streaming_Replication_Setup
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com
On Tue, Oct 1, 2013 at 5:46 PM, Sergey Konoplev <gray.ru@gmail.com> wrote: > On Tue, Oct 1, 2013 at 2:03 PM, akp geek <akpgeek@gmail.com> wrote: >> One more thing.. pardon me for being dumb >> >> I want to set the 2 nd slave as HOT STAND BY, not steaming .. > > Hot standby assumes being streaming. You can not establish a hot > standby without using streaming replication. What is the reason not to > do it streaming? > Ah! why? you don't need to use streaming replication for a hot standby, it works perfectly well even if you replay everything from archive and never do streaming. but it would be a good idea to set hot_standby_feedback to on and max_standby_archive_delay to something larger than 30s -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación Phone: +593 4 5107566 Cell: +593 987171157
On Tue, Oct 1, 2013 at 6:15 PM, Jaime Casanova <jaime@2ndquadrant.com> wrote: > you don't need to use streaming replication for a hot standby, it > works perfectly well even if you replay everything from archive and > never do streaming. Right, I mixed up a with the terms a bit. > but it would be a good idea to set hot_standby_feedback to on and > max_standby_archive_delay to something larger than 30s Doesn't replica need a connection to master for hot_standby_feedback? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
On Tue, Oct 1, 2013 at 5:30 PM, akp geek <akpgeek@gmail.com> wrote: > I tried tunneling this morning and it did not work. when tried the tunneling > command in the url you mentioned getting following error. I will try to find > what exactly this mean , but any help is appreciated. > > command-line: line 0: Bad configuration option: ExitOnForwardFailure > command-line: line 0: Bad configuration option: ExitOnForwardFailure It looks like your SSH version or implementation doesn't support ExitOnForwardFailure. Try to find an alternative. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com
On Tue, Oct 1, 2013 at 10:48 PM, Sergey Konoplev <gray.ru@gmail.com> wrote: > On Tue, Oct 1, 2013 at 6:15 PM, Jaime Casanova <jaime@2ndquadrant.com> wrote: > >> but it would be a good idea to set hot_standby_feedback to on and >> max_standby_archive_delay to something larger than 30s > > Doesn't replica need a connection to master for hot_standby_feedback? > doh! yes, it needs it... vacuum_defer_cleanup_age it's the one you should set if never do streaming... but, of course, that is not accurate enough -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación Phone: +593 4 5107566 Cell: +593 987171157
Thanks for all suggestions. based on the constraints I had with network, I could able to set up the warm stand by. I am seeing the following log file
I don't know to how to handle. 2013-10-03 17:52:00 GMT [27636]: [457-1] user=,db=LOG: restored log file "00000001000001F600000003" from archive
scp:/archive/00000001000001F600000004: No such file or directory
2013-10-03 17:52:02 GMT [27636]: [458-1] user=,db=LOG: unexpected pageaddr 1F5/34000000 in log file 502, segment 4, offset 0
Regards
On Thu, Oct 3, 2013 at 12:34 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote:
On Tue, Oct 1, 2013 at 10:48 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
> On Tue, Oct 1, 2013 at 6:15 PM, Jaime Casanova <jaime@2ndquadrant.com> wrote:
>>> but it would be a good idea to set hot_standby_feedback to on anddoh! yes, it needs it...
>> max_standby_archive_delay to something larger than 30s
>
> Doesn't replica need a connection to master for hot_standby_feedback?
>
vacuum_defer_cleanup_age it's the one you should set if never do
streaming... but, of course, that is not accurate enough
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157