Re: Primary DB stuck becuase of unavailable standby (synchronized streaming) - please help

Hi

Sincerely, if you are missing tcp packets you first should recheck the environment because this issue is not acceptable in any cases, but less if we are talking about HA databases.

If you are cofiguring this with remote machines or similar, try to use async and pgwall recovery system.

If this is a datacenter or central network, the synchronized streaming replication should not fail it some packet are bad or similar, TCP level fix this issues and the streaming client-server syncs their time mark automatically.

If you miss one server (slave of course) in some cases, and can not fix it, maybe you should use pgpool-II for the sync system and prepare the scripts of re-synchronize server so, you can recover a slave machine using the pgwall files.

Shortly, if the system is off, you will do: new pitr on master; rsync between machines; copy pg_wall files to repository; end pitr; prepare script on client machine (server conecction); relauch :)

You can do it also without pgpool-II, but it has its own structure and sync system so, if fail, you can use the prepared scripts (and let the replica system on postgresql)

Good luck :)

El 18/04/2013, a las 16:39, "Sofer, Yuval" <Yuval_Sofer(at)bmc(dot)com> escribió:

> Hi,
>
> I am using Postgres DB with stand by database, configured with streaming in synchronized mode (each commit on primary DB waits for commit on secondary DB).
>
> Sometimes we suffer from network issues and as consequences, secondary machine is not available.
>
> In these situations, our application is stuck and do not respond anymore, since no confirmation comes from the secondary Postgres (obviously… it is down or unavailable….).
>
> Is there any parameter that I can use, to enable recovering from hanging on until someone of us re-establish connection to secondary DB?
> Maybe some configuration parameter that sets the time interval for being stuck because of unavailable secondary Postgres?
>
> Please help,
>
> Thanks,
>
>
> Yuval Sofer
> BMC Software
> CTM&D Business Unit
> DBA Team
> 972-52-4286-282
> yuval_sofer(at)bmc(dot)com
>
>
>
>
>
>

Alfonso Afonso
(personal)