Re: Sequence last value is different among primary and secondary

Paul Smith <paul@xxxxxxxxxx> · Tue, 15 Nov 2022 08:48:09 +0000



    On 15/11/2022 08:24, Kenny Bachman
      wrote:

    
      Hello Team,
        

        I got a weird situation about the sequence value in PG 14.5
          (rhel 8.6). The sequence's last value is different between
          primary and secondary nodes.
        

        This sequence output belongs to the PRIMARY node:
        

        SELECT * FROM "ACCOUNT_ITEM_SEQ" ;

           last_value | log_cnt | is_called 

          ------------+---------+-----------

               105485 |      31 | t

        
        This output belongs to the SECONDARY node:
        

        SELECT * FROM "ACCOUNT_ITEM_SEQ" ;

           last_value | log_cnt | is_called 

          ------------+---------+-----------

               105516 |       0 | t

        
        There is no replication gap or lag between the nodes.
        What is the reason behind this difference?
      
    
    I believe this is to do with the way PostgreSQL does sequences.
      It doesn't update the WAL every time a sequence increments, just
      every 32 times to avoid writing lots of unnecessary entries given
      that the sequence generally updates in a predictable manner. The
      'log_cnt' keeps track of how many increments are to go before the
      next WAL update.

      
      I guess the secondary is just updated with the next 'step', so
      that if there's a failover, the sequence number is at a 'safe'
      position without causing duplicates. (note that the primary
      'last_value + log_cnt' = secondary 'last_value')

      
      Eg, see
https://stackoverflow.com/questions/66456952/what-does-log-cnt-mean-in-the-postgres-sequence

      
      Paul

    
-- 


Paul Smith Computer Services

Tel: 01484 855800

Vat No: GB 685 6987 53


Sign up for news & updates