Обсуждение: Postgres replication question :- One master 2 slaves 9.0.10

Поиск
Список
Период
Сортировка

Postgres replication question :- One master 2 slaves 9.0.10

От
akp geek
Дата:
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.

Regards

Re: Postgres replication question :- One master 2 slaves 9.0.10

От
Chris Travers
Дата:



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.

Re: Postgres replication question :- One master 2 slaves 9.0.10

От
akp geek
Дата:
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

regards


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 Travers

Regards




--
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor lock-in.

Re: Postgres replication question :- One master 2 slaves 9.0.10

От
akp geek
Дата:
One more thing.. pardon me for being dumb

I want to set the 2 nd slave as HOT STAND  BY,  not steaming ..

What would be steps.

on the primary I will have the archive_command

on the slave in the recover.conf , restore_command.

After I make my slave as exactly as master,  How can the slave gets the files from master ( HOT STAND BY )..  master has trusted connection(ssh ) with slave. 

Thanks a lot for the help.


 


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

regards


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 Travers

Regards




--
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor lock-in.


Re: Postgres replication question :- One master 2 slaves 9.0.10

От
Sergey Konoplev
Дата:
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


Re: Postgres replication question :- One master 2 slaves 9.0.10

От
akp geek
Дата:
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

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

Re: Postgres replication question :- One master 2 slaves 9.0.10

От
Jaime Casanova
Дата:
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


Re: Postgres replication question :- One master 2 slaves 9.0.10

От
Sergey Konoplev
Дата:
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


Re: Postgres replication question :- One master 2 slaves 9.0.10

От
Sergey Konoplev
Дата:
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


Re: Postgres replication question :- One master 2 slaves 9.0.10

От
Jaime Casanova
Дата:
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


Re: Postgres replication question :- One master 2 slaves 9.0.10

От
akp geek
Дата:
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 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