Re: Google Cloud Platform, snapshots and WAL

Moreno Andreo <moreno.andreo@xxxxxxxxxx> · Wed, 22 Mar 2017 15:40:39 +0100



    Il 20/03/2017 17:45, Ben Chobot ha
      scritto:

    
          On Mar 20, 2017, at 6:31 AM, Moreno Andreo <moreno.andreo@xxxxxxxxxx>
            wrote:
          

            Hi everyone,

              
                 I have my PostgreSQL 9.5 server running on a VM
              instance on Google Compute Engine (Google Cloud Platform)
              on Debian Jessie (8.3), and I have another dedicated VM
              instance that, every night at 3.00, takes a snapshot of
              the whole disk, without stopping the PG instance itself.

              Snapshots are stored and kept by Google in an incremental
              way, and we keep the last 2 weeks of history.

              The question is: Keeping all two weeks worth of pg_xlog
              files, I don't think I still need a periodic pg_basebackup
              to perform PITR, do I?

            
      You need a base backup to apply your wals to. So
        long as you have one from after the start of your wal stream,
        you should be good for PITR.
    
    Hmmm... I went back in the docs and noticed I missed something. To
    achieve PITR, the cluster needs to checkpoint, and this can be
    obtained with the pg_start_backup() function... so if I try to get a
    snapshot and start a recover (creating recovery.conf etc.) it will
    not even start recovery, right?

    
    Now I'm gonna try 2 approaches:

    1. (straightforward) barman with basebackup and WAL archiving

    2. (GoogleCloud-oriented) disk snapshot between pg_start_backup and
    pg_stop_backup (so the snapshot is taken just after the checkpoint),
    WAL archiving

    
    I will report the results.

    
       That said, replaying 2 weeks of wal files can take
        a long time. For that reason alone, it might well make sense to
        have more than a single basebackup snapshot.
    
    That's right, my (wrong) thought was to have a snapshot per day and
    all 14 days worth of WALs, but after the meeting with the Google
    Specialist, I'm oriented to make a base backup per day and then
    store it on Nearline, making it expire after 14 days. Same for WAL
    files.

    
      Also, I cannot stress enough how important it is to
        actually test your recovery strategy. 
    
    I totally agree... that's why I'm here. I don't want to prepare a
    backup strategy when I already need to recover....

    
    Thanks

    Moreno.