Обсуждение: [GENERA]: Postgresql-9.1.1 synchronous replication issue

On Wednesday, February 01, 2012 10:51:44 pm Venkat Balaji wrote:
> Hello,
>
> I was testing the Postgres-9.1.1 synchronous streaming replication on our
> UAT system.
>
> Without synchronous replication, everything was working fine.
>
> But, when i enabled synchronous_replication_names='*', the "create table"
> started hanging for long time.

Only the CREATE TABLE statement or all statements?
In general terms synchronous replication moves at the speed of the connection
between the primary and standby or does not occur if the standby can not be
found. So what is the state of the connection between the primary and standby?

>
> When i pressed "Ctrl+C" i got the following message -
>
> Cancel request sent
> WARNING:  canceling wait for synchronous replication due to user request
> DETAIL:  The transaction has already committed locally, but might not have
> been replicated to the standby.
> CREATE TABLE
>
> Can someone please help us ?
>
> Thanks
> VB

--
Adrian Klaver
adrian.klaver@gmail.com

On Thursday, February 02, 2012 10:21:28 pm Venkat Balaji wrote:

>
> Connection is working fine between primary and standby, ping is working
> fine and wal archive file transfer is working without any issues.
>
> I tried CREATE TABLE and CREATE DATABASE, both were hanging.
>
> Apart from regular streaming replication settings, I did the following on
> primary to enable synchronous replication -
>
> synchronous_standby_names='*'
>
> Commands started hanging after that. Is there anything else i need to do.

From here:
http://www.postgresql.org/docs/9.1/interactive/runtime-config-replication.html

"
synchronous_standby_names (string)
... The synchronous standby will be the first standby named in this list that is
both currently connected and streaming data in real-time (as shown by a state of
streaming in the pg_stat_replication view). Other standby servers appearing
later in this list represent potential synchronous standbys....

The name of a standby server for this purpose is the application_name setting of
the standby, as set in the primary_conninfo of the standby's walreceiver. There
is no mechanism to enforce uniqueness. In case of duplicates one of the matching
standbys will be chosen to be the synchronous standby, though exactly which one
is indeterminate. The special entry * matches any application_name, including
the default application name of walreceiver.

"

So I would check the pg_stat_replication view to see if Postgres is seeing the
standby as streaming.


>
> Thanks
> VB

--
Adrian Klaver
adrian.klaver@gmail.com

On Tuesday, February 14, 2012 4:21:22 am Venkat Balaji wrote:
> Hello,
>
> Disaster Recovery testing for Synchronous replication setup -
>
> When the standby site is down, transactions at the production site started
> hanging (this is after the successful setup of synchronous replication).
>
> We changed synchronous_commit to 'local' to over-come this situation.
>
>  - No transactions are hanging at the production site even when the standby
> is down
>  - Standby is automatically getting synced when it is back up again.
>
> Can someone let us know if there are any "-ve" effects of putting
> synchronous_commit='local' ??
>
> I am assuming that this as good as putting "synchronous_commit=on" on an
> stand-alone system.

It would seem you are really after streaming replication(which is asynchronous)
more than synchronous replication. I have not used synchronous replication
enough to be  sure, but I think by setting synchronous_commit='local' you are
basically turning the system into a straight streaming(asynchronous) system
anyway.

>
> We need to get this setup live on production shortly.
>
> Thanks
> VB

--
Adrian Klaver
adrian.klaver@gmail.com

On Wednesday, February 15, 2012 2:15:34 am Venkat Balaji wrote:
> In-short, I would like to understand if i am achieving the same
> asynchronous streaming replication by putting synchronous_commit='local' -
>
> I understand that streaming replication is record based log-shipping.
>
> Below is what shows up on our primary test server where we are testing
> synchronous replication -
>
> *1. Synchronous setup enabled with synchronous_commit='local'*
>
> postgres=# select * from pg_stat_replication ;
>
> procpid | usesysid | usename  | application_name | client_addr |
> client_hostname | client_port |        backend_start         |   state   |
> sent_location | write_locat
> ion | flush_location | replay_location | sync_priority | sync_state
> ---------+----------+----------+------------------+-------------+----------
> -------+-------------+------------------------------+-----------+----------
> -----+------------
> ----+----------------+-----------------+---------------+------------ 24099
> |       10 | postgres | walreceiver      |  <ip-address>  |
>
>           |       56432 | 2012-02-15 12:55:39.65663+03 | streaming |
>
> 0/E000078     | 0/E000078
>
>     | 0/E000078      | 0/E000078       |             1 | *sync*
>
> (1 row)
>
> postgres=# show synchronous_commit ;
>
> synchronous_commit
> --------------------
> * local*
> (1 row)
>
> postgres=# show synchronous_standby_names ;
>
> synchronous_standby_names
> ---------------------------------------------
>  *
> (1 row)
>
> Does this mean that the system is still replicating synchronously ? If yes,
> by what means ?
>
> *Below is our actual production setup in 9.1.1 with asynchronous
> replication setup -*
>
> *2. Asynchronous enabled with synchronous_commit='on'*
>
> psql (9.1.1)
> Type "help" for help.
>
> postgres=# select * from pg_stat_replication;
>
> procpid | usesysid | usename  | application_name | client_addr |
> client_hostname    | client_port |         backend_start         |   state
>
>   | sent_location | write
>
> _location | flush_location | replay_location | sync_priority | sync_state
> ---------+----------+----------+------------------+-------------+----------
> ------------+-------------+-------------------------------+-----------+----
> -----------+------
> ----------+----------------+-----------------+---------------+------------
> 3159 |       10 | postgres | walreceiver      | <ipaddress> |
> <hostname> |       40165 | 2012-02-08 12:41:51.858897+03 | streaming |
> 1/86F83B50    | 1/86F
> 83B50     | 1/86F83B50     | 1/86F83B50      |             0 | *async*
>
> (1 row)
>
> postgres=# show synchronous_commit ;
>  synchronous_commit
> --------------------
>  on
> (1 row)
>
> postgres=# show synchronous_standby_names ;
>
> synchronous_standby_names
> ---------------------------
>
> (1 row)
>
> Operation wise, I am not seeing much difference by inserting few 1000 rows.
> Its almost the same behavior both in asynch and sync rep.

First sync replication is just an advanced form of streaming replication.

http://www.postgresql.org/docs/9.1/interactive/warm-standby.html#STREAMING-
REPLICATION
"
Streaming replication allows a standby server to stay more up-to-date than is
possible with file-based log shipping. The standby connects to the primary, which
streams WAL records to the standby as they're generated, without waiting for the
WAL file to be filled.
"

In both cases WAL information is being used. Though just one record at a time,
so the entire WAL file does not have to be shipped over. In the case of sync
replication a commit on the master is not complete until it also completes on
the standby. This is for the default case where synchronous_commit=on and
synchronous_standby_names has valid names. In your case you are using
synchronous_commit=local  and per:

http://www.postgresql.org/docs/9.1/interactive/runtime-config-wal.html#RUNTIME-
CONFIG-WAL-SETTINGS

synchronous_commit

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


So in this case you are not waiting for confirmation of the commit being flushed
to disk on the standby.  It that case you are bypassing the primary reason for
sync replication. The plus is transactions on the master will complete faster
and do so in the absence of the standby. The minus is that you are in sort of an
in between state.

Personally, I take sync replication to be basically an all or nothing
proposition. By setting it up you are saying you want, at minimum, two database
clusters to be in sync at any point in time all the time (except for start up).
If that is not possible then you are really looking for async replication.

>
> Thanks,
> VB


--
Adrian Klaver
adrian.klaver@gmail.com

On Wednesday, February 15, 2012 10:21:02 pm Venkat Balaji wrote:
> Andrian,
>
> Thanks a lot !
>
> So in this case you are not waiting for confirmation of the commit being
>
> > flushed
> > to disk on the standby.  It that case you are bypassing the primary
> > reason for
> > sync replication. The plus is transactions on the master will complete
> > faster
> > and do so in the absence of the standby. The minus is that you are in
> > sort of an
> > in between state.
>
> I understand. My worry and requirement is to ensure master is not disturbed
> for any reason.
> In sync rep, the biggest worry is if standby server is unavailable and is
> down for longer time, master hangs and will be in the same state until
> standby comes back up or replication must be broken temporarily (until
> standby comes back up) so that master runs without interruption. This is a
> costly exercise on production from downtime perspective.

So just use regular streaming replication without sync rep. You get record based
transaction shipping without having to wait for the standby.  You will need to
make sure that wal_keep_segments is big enough to cover any down time on the
standby(you would need that for sync rep also).

>
> Personally, I take sync replication to be basically an all or nothing
>
> > proposition. By setting it up you are saying you want, at minimum, two
> > database
> > clusters to be in sync at any point in time all the time (except for
> > start up).
> > If that is not possible then you are really looking for async
> > replication.
>
> Yeah. We will need to make a decision accordingly.
>
> Thanks again,
> VB

--
Adrian Klaver
adrian.klaver@gmail.com