Thread: PostgreSQL 9.0 and asynchronous replication through VPN

Dear friends,

I have an somewhat unstable link between two different locations with OpenVPN established and working.

Now, I've configured PostgreSQL 9.0.5 for asynchronous replication. This morning I got the following message on Slave PostgreSQL log:

------------------------------------------------------------------------------------------------------------------------------
2011-11-30 07:52:23 BRST % - FATAL:  could not connect to the primary server: could not connect to server: Connection timed out
        Is the server running on host "10.68.73.5" and accepting
        TCP/IP connections on port 5432?
   
2011-11-30 07:55:33 BRST % - FATAL:  could not connect to the primary server: could not connect to server: Connection timed out
        Is the server running on host "10.68.73.5" and accepting
        TCP/IP connections on port 5432?
-------------------------------------------------------------------------------------------------------------------------------

Detailed network configuration: PostgreSQL [Master = 10.68.73.5, Slave = 10.68.73.1]; OpenVPN [Server = 10.68.73.1; Client=10.68.73.5; both static IP]

I assume that the OpenVPN got disconnected for a few seconds, and came back again.

My question is: assuming I have enough wal segments on Master side, does the Slave get synchronized automatically after the connection is reestablished, or I'll need to restart Slave PostgreSQL to put it in sync again?

If I restart Slave PostgreSQL, I get:

------------------------------------------------------------------------------------------------------------------------------------------------------
2011-11-30 08:01:09 BRST % - LOG:  received fast shutdown request
2011-11-30 08:01:09 BRST % - FATAL:  terminating walreceiver process due to administrator command
2011-11-30 08:01:09 BRST % - LOG:  shutting down
2011-11-30 08:01:09 BRST % - LOG:  database system is shut down
2011-11-30 08:01:18 BRST % - LOG:  database system was shut down in recovery at 2011-11-30 08:01:09 BRST
2011-11-30 08:01:18 BRST % - LOG:  entering standby mode
2011-11-30 08:01:18 BRST % - LOG:  redo starts at A/4000020
2011-11-30 08:01:18 BRST % - LOG:  record with zero length at A/40000B0
------------------------------------------------------------------------------------------------------------------------------------------------------

Thanks for your help,

On Nov 30, 2011, at 5:02 AM, Edson Richter wrote:

> I assume that the OpenVPN got disconnected for a few seconds, and came back again.
>
> My question is: assuming I have enough wal segments on Master side, does the Slave get synchronized automatically
afterthe connection is reestablished, or I'll need to restart Slave PostgreSQL to put it in sync again? 
>
> If I restart Slave PostgreSQL, I get:


Yes, it automatically catches up when the connection is working again. You should not have to restart the slave.

John DeSoi, Ph.D.

Em 30-11-2011 11:17, John DeSoi escreveu:
> On Nov 30, 2011, at 5:02 AM, Edson Richter wrote:
>
>> I assume that the OpenVPN got disconnected for a few seconds, and came back again.
>>
>> My question is: assuming I have enough wal segments on Master side, does the Slave get synchronized automatically
afterthe connection is reestablished, or I'll need to restart Slave PostgreSQL to put it in sync again? 
>>
>> If I restart Slave PostgreSQL, I get:
>
> Yes, it automatically catches up when the connection is working again. You should not have to restart the slave.
Thanks! Would be a nice improvement if when replication is restablished,
then a log message occur.

Regards,

Edson.
>
> John DeSoi, Ph.D.
>
>

Indeed, there is a log message. My problem was that I missed to add "listen_address='*'" to my postgresql.conf!

Hope this helps others in future.

Regards,

Em 30-11-2011 14:41, Edson Richter escreveu:

Em 30-11-2011 11:17, John DeSoi escreveu:

On Nov 30, 2011, at 5:02 AM, Edson Richter wrote:

I assume that the OpenVPN got disconnected for a few seconds, and came back again.

My question is: assuming I have enough wal segments on Master side, does the Slave get synchronized automatically after the connection is reestablished, or I'll need to restart Slave PostgreSQL to put it in sync again?

If I restart Slave PostgreSQL, I get:

Yes, it automatically catches up when the connection is working again. You should not have to restart the slave.

Thanks! Would be a nice improvement if when replication is restablished, then a log message occur.

Regards,

Edson.

John DeSoi, Ph.D.