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

Not really sure of your exact situation, if your network is really flaky
you really need to fix that.... and if your trying to do streaming over a
WAN, you might be better off doing log shipping instead.

But what we do here with one live and one hot-standby via streaming is
synchronous_commit = off
(Alongside of the synchronous_standby_names = '*', In addition to
log_shipping

What this does is allow the client to get a success before transaction is
flushed to disk, and flushed to the standby server.

This does not create a case where in a crash you have corrupted data, but
you may miss the last few transactions on a restore. But, at least, local
recovery can be 'set right... from the docs:

"However, the special value local is available for transactions that
wish to wait for local flush to disk, but not synchronous replication."

That means Postgres will flush to disk before returning to the client, but
the streaming replication will be async.

Check out synchronous_commit in:
http://www.postgresql.org/docs/9.1/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS

On Thu, Apr 18, 2013 at 8:39 AM, Sofer, Yuval <Yuval_Sofer(at)bmc(dot)com> wrote:

> 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* <yuval_sofer(at)bmc(dot)com>
>
>
>
>
>
>
>

--
Virtually, Ned Wolpert

"Settle thy studies, Faustus, and begin..." --Marlowe