Re: master and sync-replica diverging

On Thu, May 17, 2012 14:32, Joshua Berkus wrote:
> Erik,
>
> Are you taking the counts *while* the table is loading? In sync replication, it's possible for
> the counts to differ for a short time due to one of three things:
>
> * transaction has been saved to the replica and confirm message hasn't reached the master yet

The count(*) was done in the way that I showed, i.e. *after* psql had exited. My understanding is
that, with synchronous replication 'on' and configured properly, psql could only return *after*
the sync-replica had the data safely on disk. Either my understanding is not correct or there is
a bug in postgres sync-rep.

> * replica has synched the transaction to the WAL log, but due to wal_delay settings hasn't yet
> applied it to the tables in memory.

settings are untouched.

> * updating the master with synchronous_commit = local.

During my test: synchronous_commit = on

Thanks,

Erik Rijkers

> ----- Original Message -----
>> AMD FX 8120 / centos 6.2 / latest source (git head)
>>
>>
>> It seems to be quite easy to force a 'sync' replica to not be equal
>> to master by
>> recreating+loading a table in a while loop.
>>
>>
>> For this test I compiled+checked+installed three separate instances
>> on the same machine. The
>> replica application_name are names 'wal_receiver_$copy' where $copy
>> is 01, resp. 02.
>>
>> $ ./sync_state.sh
>> pid | application_name | state | sync_state
>> -------+------------------+-----------+------------
>> 19520 | wal_receiver_01 | streaming | sync
>> 19567 | wal_receiver_02 | streaming | async
>> (2 rows)
>>
>> port | synchronous_commit | synchronous_standby_names
>> ------+--------------------+---------------------------
>> 6564 | on | wal_receiver_01
>> (1 row)
>>
>> port | synchronous_commit | synchronous_standby_names
>> ------+--------------------+---------------------------
>> 6565 | off |
>> (1 row)
>>
>> port | synchronous_commit | synchronous_standby_names
>> ------+--------------------+---------------------------
>> 6566 | off |
>> (1 row)
>>
>>
>>
>> The test consists of creating a table and loading tab-separated data
>> from file with COPY and then
>> taking the rowcount of that table (13 MB, almost 200k rows) in all
>> three instances:
>>
>>
>> # wget
>> http://flybase.org/static_pages/downloads/FB2012_03/genes/fbgn_annotation_ID_fb_2012_03.tsv.gz
>>
>> slurp_file=fbgn_annotation_ID_fb_2012_03.tsv.gz
>>
>> zcat $slurp_file \
>> | grep -v '^#' \
>> | grep -Ev '^[[:space:]]*$' \
>> | psql -c "
>> drop table if exists $table cascade;
>> create table $table (
>> gene_symbol text
>> , primary_fbgn text
>> , secondary_fbgns text
>> , annotation_id text
>> , secondary_annotation_ids text
>> );
>> copy $table from stdin csv delimiter E'\t';
>> ";
>>
>> # count on master:
>> echo "select current_setting('port') port,count(*) from $table"|psql
>> -qtXp 6564
>>
>> # count on wal_receiver_01 (sync replica):
>> echo "select current_setting('port') port,count(*) from $table"|psql
>> -qtXp 6565
>>
>> # count on wal_receiver_02 (async replica):
>> echo "select current_setting('port') port,count(*) from $table"|psql
>> -qtXp 6566
>>
>>
>>
>> I expected the rowcounts from master and sync replica to always be
>> the same.
>>
>> Initially this seemed to be the case, but when I run the above
>> sequence in a while loop for a few
>> minutes about 10% of rowcounts from the sync-replica are not equal to
>> the master.
>>
>> Perhaps not a likely scenario, but surely such a deviating rowcount
>> on a sync replica should not
>> be possible?
>>
>>
>> thank you,
>>
>>
>> Erik Rijkers
>>
>>
>>
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>