Re: postgres large database backup

Ron <ronljohnsonjr@xxxxxxxxx> · Wed, 30 Nov 2022 13:06:53 -0600



    On 11/30/22 11:41, Vijaykumar Jain wrote:

    
          On Wed, Nov 30, 2022, 9:10
            PM Atul Kumar <akumar14871@xxxxxxxxx>
            wrote:

          
            Hi, 
              

              I have a 10TB database running on postgres 11 version
                running on centos 7 "on premises", I need to schedule
                the backup of this database in a faster way.
              

              The scheduled backup will be used for PITR purposes.
              

              So please let me know how I should do it in a quicker
                backup for my 10TB database ? Is there any tool to
                take backups and subsequently incremental backups in a
                faster way and restore it for PITR in a faster way when
                required.
              

              What should be the exact approach for scheduling such
                backups so that it can be restored in a faster way ?
            
          
        We had a 96 cpu, 385gb ram, nvme storage and 10g
          network baremetal server.
        We used pgbackrest for full backup.
        It supports pitr and differential backup.
        

        28tb db took 2.5 hours for backup on remote
          storage, and restore from the remote storage  took 3.5 hours
          when immediately restored (a lot of time is later due to wal
          replay to catch up) 

        
    Another vote for pgbackrest.  It supports AES encryption, and
    multiple forms of compression.

    
    -- 

      Angular momentum makes the world go 'round.