Re: Question about wal files / pg_xlogs

Alex Ignatov <a.ignatov@xxxxxxxxxxxxxx> · Fri, 5 Aug 2016 16:30:02 +0300



    Hello!
    As I can see files is delivered not with delay but with
      timeshift.
    1. Can you show me restore_command on slave? 

    
    2. Also can you check archived WAL creation time on slaves in
      archive location after you copied them with archive_command? Is in
      near WAL creation time in pg_xlogs? Or different?
    3. How do you check timezone equivalence between master and
      slave? What

    
    Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


    On 04.08.2016 05:21, Patrick B wrote:

    
      Hi all,
        

        I'm currently using PostgreSQL 9.2. I noticed that the
          wal_files are being generated by the master well, no problems.
          But on the slaves, it seems to be a delay to the delivery of
          those wal_files.
        

        I got two slaves using streaming replication and wal files
          shipment from Master.
        

        On the master:
        

        ls
          -ltr /var/lib/pgsql/9.2/data/pg_xlogs/

          Aug  4 02:18 000000020000159D000000D1

          Aug  4 02:18 000000020000159D000000D2

          Aug  4 02:18 000000020000159D000000D3
        
          
        select
          * from pg_current_xlog_location();

          159D/D6C8DAF8
        

        So, seems to be ok.
        

          On the slave:
        

        ls
          -ltr /var/lib/pgsql/9.2/wal_archive:

          Aug  4 00:58 000000020000159C00000071

          Aug  4 00:58 000000020000159C00000072

          Aug  4 00:58 000000020000159C00000073
        

        See the time difference? 2 hours? It seems the files are
          being delivered with 2 hours delay.
        

        The streaming replication is working fine... But if that
          goes down, I'll need the wal_files up to date to recover the
          database.
        

        How can I see what's going on? What would be the steps? any
          tips?
        

        Cheers
        Patrick.