What is the best way to backup/restore postgres ?

[oroc...@gmail.com]

Hi,

I just start with velero. I see that there are pre/post hook command for backup, but not yet post hook for restore.

For postgres there are 3 backup method :  https://www.postgresql.org/docs/11/backup.html

1. SQL dump

2. File system level backup

3. Continuous archiving

1) SQL Dump 

pg_dumpall s allow to make a "hot" backup and  have a consistency backup.

2) File system level backup

The backup of file system  must  be done when the posgres is runing. Because there is no warranty of the  postgres data folder integrity

see https://www.postgresql.org/docs/11/backup-file.html

The doc say :

• An alternative file-system backup approach is to make a "consistent snapshot" of the data directory, if the file system supports that functionality ( atomic snapshot of the state of the file system)
  
• You can perform a CHECKPOINT before taking the snapshot to reduce recovery time
  
• However, a backup created in this way saves the database files in a state as if the database server was not properly shut down; therefore, when you start the database server on the backed-up data, it will think the previous server instance crashed and will replay the WAL log. This is not a problem; just be aware of it (and be sure to include the WAL files in your backup).
  

So my question are  : 

- If I want to use "File system level backup, Do you think that run a post hook backup (CHECKPOINT) is enough to warranty a restore without data corruption ? - is there a way to include WAL file ?

if I whant to use "SQL Dump"  there is no post hook restore annotation to play the dump file. see https://github.com/vmware-tanzu/velero/issues/2116

Anyway, if I need to play WAL file, the hook restore annotation could be usefull also

[Carlisia Campos]

Hi there,

I consulted with others on the Velero team. According to Ashish, the most intuitive way to do this is using the dump functionality, performing the dump in a pre backup hook.. and on the restore perform psql dbname < dumpfile on the post restore hook... this would require the restore hooks though. And replaying the WAL file is not something Velero would have to do: postgress should do that as part of its startup.

I going with a file level backup, according to Steve, it could be a block-level snapshot for the PG data volume if they have that option, and that snapshot could include the WAL file if it’s on the same volume. that would be crash-consistent. But you might also be able to figure out a way to accomplish it using a custom restore item action that adds an init container to their pod.

Hope this helps!

-Carlisia

[LUIZ CARLOS PeGo]

I'm doing it this way but the postgres-backup.sql file is not sent to the buckte. all cluster data is sent. minus the sql file. Would you have any tips for me?

apiVersion: velero.io/v1

kind: Backup

metadata:

  name: postgres-backup-file

  namespace: velero

spec:

  includedNamespaces:

  - default

  includedResources:

  - pods

  - persistentvolumeclaims

  - persistentvolumes

  hooks:

    resources:

      - name: postgres

        includedNamespaces:

        - default

        includedResources:

        - pods        

        labelSelector:

          matchLabels:

            app: postgres

        pre:

          - exec:

              container: postgres

              command:

                - /bin/sh

                - -c

                - pg_dumpall -U $POSTGRES_USER > /backup/postgres-backup.sql

              timeout: 10m

  storageLocation: default