search

stephane-klein / backlog

My public backlog
30 stars 1 forks source link

Implémenter un POC de pgBackRest #322

Open stephane-klein opened 1 year ago

stephane-klein commented 1 year ago

Via cette issue, je souhaite faire un POC de pgBackRest, dans la situation suivante :

Dans un premier temps je souhaite explorer l'option b.

Détail d'implémentation :

stephane-klein commented 1 year ago

Je souhaite lire :

stephane-klein commented 1 year ago

Je me demande si quelqu'un a déjà configuré un container Docker pgbackrest en sidecar d'un container Docker postgres.

J'ai déjà passé du temps à chercher cela, sans succès, je vais essayer à nouveau.

stephane-klein commented 1 year ago

J'ai trouvé ceci d'intéressant https://github.com/pellepelster/hetzner-rds-postgres

stephane-klein commented 1 year ago

Je lis :

stephane-klein commented 1 year ago

Je lis :

stephane-klein commented 1 year ago

En lisant cette section https://pgbackrest.org/user-guide.html#backup 

 archive_command = 'pgbackrest --stanza=demo archive-push %p'

je pense que pgbackrest doit directement être installé dans le container Docker PostgreSQL, sinon, PostgreSQL ne pourra pas exécuter la commande pgbackrest --stanza=demo archive-push %p.

stephane-klein commented 1 year ago

Je viens de poster https://github.com/pgbackrest/pgbackrest/issues/2198

stephane-klein commented 1 year ago

J'ai bien lu les différences entre Full Backup, Differential Backup, Incremental Backup.

stephane-klein commented 1 year ago

Je relis l'article https://ahmet.im/blog/minimal-init-process-for-containers/ pour lancer supercronic + postgres dans le même container.

stephane-klein commented 1 year ago

Je n'ai pas réussi à setup https://github.com/just-containers/s6-overlay, j'ai beaucoup de difficulté à comprendre la documentation. Je trouve le projet trop gros, trop compliqué pour ce qu'il fait.

Je vais essayer une autre solution : https://ahmet.im/blog/cloud-run-multiple-processes-easy-way/

stephane-klein commented 1 year ago

Pour le moment j'ai publié ceci https://github.com/stephane-klein/poc-pgbackrest

stephane-klein commented 1 year ago 
root@4e4f24b7077e:/# su postgres -p -c 'pgbackrest --stanza=instance1 --log-level-console=info info'
stanza: instance1
    status: ok
    cipher: none

    db (current)
        wal archive min/max (15): 000000010000000000000004/000000010000000000000021

        full backup: 20231004-232941F
            timestamp start/stop: 2023-10-04 23:29:41+00 / 2023-10-04 23:29:44+00
            wal start/stop: 000000010000000000000004 / 000000010000000000000005
            database size: 22.0MB, database backup size: 22.0MB
            repo1: backup set size: 2.9MB, backup size: 2.9MB

        incr backup: 20231004-232941F_20231004-233000I
            timestamp start/stop: 2023-10-04 23:30:00+00 / 2023-10-04 23:30:02+00
            wal start/stop: 000000010000000000000007 / 000000010000000000000007
            database size: 22.0MB, database backup size: 8.3KB
            repo1: backup set size: 2.9MB, backup size: 431B
            backup reference list: 20231004-232941F

        incr backup: 20231004-232941F_20231004-233047I
            timestamp start/stop: 2023-10-04 23:30:47+00 / 2023-10-04 23:30:49+00
            wal start/stop: 000000010000000000000009 / 00000001000000000000000A
            database size: 22.0MB, database backup size: 24.3KB
            repo1: backup set size: 2.9MB, backup size: 524B
            backup reference list: 20231004-232941F, 20231004-232941F_20231004-233000I

        incr backup: 20231004-232941F_20231004-233100I
            timestamp start/stop: 2023-10-04 23:31:00+00 / 2023-10-04 23:31:02+00
            wal start/stop: 00000001000000000000000C / 00000001000000000000000C
            database size: 22.0MB, database backup size: 8.3KB
            repo1: backup set size: 2.9MB, backup size: 430B
            backup reference list: 20231004-232941F, 20231004-232941F_20231004-233000I, 20231004-232941F_20231004-233047I

        incr backup: 20231004-232941F_20231004-233200I
            timestamp start/stop: 2023-10-04 23:32:00+00 / 2023-10-04 23:32:07+00
            wal start/stop: 00000001000000000000000E / 00000001000000000000000E
            database size: 22MB, database backup size: 1.7MB
            repo1: backup set size: 2.9MB, backup size: 176.8KB
            backup reference list: 20231004-232941F, 20231004-232941F_20231004-233000I, 20231004-232941F_20231004-233047I, 20231004-232941F_20231004-233100I

        incr backup: 20231004-232941F_20231004-233300I
            timestamp start/stop: 2023-10-04 23:33:00+00 / 2023-10-04 23:33:04+00
            wal start/stop: 000000010000000000000010 / 000000010000000000000010
            database size: 22MB, database backup size: 672.3KB
            repo1: backup set size: 2.9MB, backup size: 54.6KB
            backup reference list: 20231004-232941F, 20231004-232941F_20231004-233000I, 20231004-232941F_20231004-233047I, 20231004-232941F_20231004-233100I, 20231004-232941F_20231004-233200I

        incr backup: 20231004-232941F_20231004-233400I
            timestamp start/stop: 2023-10-04 23:34:00+00 / 2023-10-04 23:34:02+00
            wal start/stop: 000000010000000000000012 / 000000010000000000000012
            database size: 22MB, database backup size: 8.3KB
            repo1: backup set size: 2.9MB, backup size: 432B
            backup reference list: 20231004-232941F, 20231004-232941F_20231004-233000I, 20231004-232941F_20231004-233047I, 20231004-232941F_20231004-233100I, 20231004-232941F_20231004-233200I, 20231004-232941F_20231004-233300I

        incr backup: 20231004-232941F_20231004-233500I
            timestamp start/stop: 2023-10-04 23:35:00+00 / 2023-10-04 23:35:02+00
            wal start/stop: 000000010000000000000014 / 000000010000000000000014
            database size: 22MB, database backup size: 8.3KB
            repo1: backup set size: 2.9MB, backup size: 433B
            backup reference list: 20231004-232941F, 20231004-232941F_20231004-233000I, 20231004-232941F_20231004-233047I, 20231004-232941F_20231004-233100I, 20231004-232941F_20231004-233200I, 20231004-232941F_20231004-233300I, 20231004-232941F_20231004-233400I

        incr backup: 20231004-232941F_20231004-233600I
            timestamp start/stop: 2023-10-04 23:36:00+00 / 2023-10-04 23:36:02+00
            wal start/stop: 000000010000000000000016 / 000000010000000000000016
            database size: 22MB, database backup size: 8.3KB
            repo1: backup set size: 2.9MB, backup size: 432B
            backup reference list: 20231004-232941F, 20231004-232941F_20231004-233000I, 20231004-232941F_20231004-233047I, 20231004-232941F_20231004-233100I, 20231004-232941F_20231004-233200I, 20231004-232941F_20231004-233300I, 20231004-232941F_20231004-233400I, 20231004-232941F_20231004-233500I

        incr backup: 20231004-232941F_20231004-233631I
            timestamp start/stop: 2023-10-04 23:36:31+00 / 2023-10-04 23:36:32+00
            wal start/stop: 000000010000000000000018 / 000000010000000000000019
            database size: 22MB, database backup size: 24.3KB
            repo1: backup set size: 2.9MB, backup size: 525B
            backup reference list: 20231004-232941F, 20231004-232941F_20231004-233000I, 20231004-232941F_20231004-233047I, 20231004-232941F_20231004-233100I, 20231004-232941F_20231004-233200I, 20231004-232941F_20231004-233300I, 20231004-232941F_20231004-233400I, 20231004-232941F_20231004-233500I, 20231004-232941F_20231004-233600I

        incr backup: 20231004-232941F_20231004-233700I
            timestamp start/stop: 2023-10-04 23:37:00+00 / 2023-10-04 23:37:02+00
            wal start/stop: 00000001000000000000001B / 00000001000000000000001B
            database size: 22MB, database backup size: 8.3KB
            repo1: backup set size: 2.9MB, backup size: 432B
            backup reference list: 20231004-232941F, 20231004-232941F_20231004-233000I, 20231004-232941F_20231004-233047I, 20231004-232941F_20231004-233100I, 20231004-232941F_20231004-233200I, 20231004-232941F_20231004-233300I, 20231004-232941F_20231004-233400I, 20231004-232941F_20231004-233500I, 20231004-232941F_20231004-233600I, 20231004-232941F_20231004-233631I

        incr backup: 20231004-232941F_20231004-233800I
            timestamp start/stop: 2023-10-04 23:38:00+00 / 2023-10-04 23:38:02+00
            wal start/stop: 00000001000000000000001D / 00000001000000000000001D
            database size: 22MB, database backup size: 8.3KB
            repo1: backup set size: 2.9MB, backup size: 433B
            backup reference list: 20231004-232941F, 20231004-232941F_20231004-233000I, 20231004-232941F_20231004-233047I, 20231004-232941F_20231004-233100I, 20231004-232941F_20231004-233200I, 20231004-232941F_20231004-233300I, 20231004-232941F_20231004-233400I, 20231004-232941F_20231004-233500I, 20231004-232941F_20231004-233600I, 20231004-232941F_20231004-233631I, 20231004-232941F_20231004-233700I

        diff backup: 20231004-232941F_20231004-233900D
            timestamp start/stop: 2023-10-04 23:39:00+00 / 2023-10-04 23:39:02+00
            wal start/stop: 00000001000000000000001F / 00000001000000000000001F
            database size: 22MB, database backup size: 1.7MB
            repo1: backup set size: 2.9MB, backup size: 176.9KB
            backup reference list: 20231004-232941F

        full backup: 20231004-234000F
            timestamp start/stop: 2023-10-04 23:40:00+00 / 2023-10-04 23:40:04+00
            wal start/stop: 000000010000000000000021 / 000000010000000000000021
            database size: 22MB, database backup size: 22MB
            repo1: backup set size: 2.9MB, backup size: 2.9MB
stephane-klein commented 10 months ago

J'ai intégré l'exécutable tiny mais je ne me souviens plus trop pour quelle raison :thinking:.

stephane-klein commented 10 months ago

J'ai relus :

stephane-klein commented 10 months ago

J'ai relu :

stephane-klein commented 10 months ago

Todo :

stephane-klein commented 10 months ago

Après lecture de Write Ahead Log, je pense avoir compris :

stephane-klein commented 10 months ago

Question que je me pose : quand archive_timeout est à 0, à quel moment PostgreSQL change le fichier wal destination dans lequel il écrit ?

stephane-klein commented 10 months ago

Question que je me pose : quand archive_timeout est à 0, à quel moment PostgreSQL change le fichier wal destination dans lequel il écrit ?

Je me lance dans une nouvelle lecture de https://www.postgresql.org/docs/16/continuous-archiving.html, j'espère y trouver une réponse à ma question.

stephane-klein commented 10 months ago

J'ai aussi compris qu'en fonction de la valeur de wal_level PostgreSQL écrit plus ou moins de chose dans les fichiers wal.

stephane-klein commented 10 months ago

Requête intéressante :

postgres@127:postgres> select * from pg_catalog.pg_stat_archiver;
+----------------+--------------------------+-------------------------------+--------------+-----------------+------------------+-------------------------------+
| archived_count | last_archived_wal        | last_archived_time            | failed_count | last_failed_wal | last_failed_time | stats_reset                   |
|----------------+--------------------------+-------------------------------+--------------+-----------------+------------------+-------------------------------|
| 9              | 00000001000000000000000F | 2023-12-29 00:03:14.828937+00 | 0            | <null>          | <null>           | 2023-12-28 23:54:36.958482+00 |
+----------------+--------------------------+-------------------------------+--------------+-----------------+------------------+-------------------------------+
SELECT 1
Time: 0.005s
stephane-klein commented 10 months ago

Question que je me pose : quand archive_timeout est à 0, à quel moment PostgreSQL change le fichier wal destination dans lequel il écrit ?

Je pense que j'ai ma réponse ici :

The archive command or function is only invoked on completed WAL segments. Hence, if your server generates only little WAL traffic (or has slack periods where it does so), there could be a long delay between the completion of a transaction and its safe recording in archive storage. To put a limit on how old unarchived data can be, you can set archive_timeout to force the server to switch to a new WAL segment file at least that often. Note that archived files that are archived early due to a forced switch are still the same length as completely full files. It is therefore unwise to set a very short archive_timeout — it will bloat your archive storage. archive_timeout settings of a minute or so are usually reasonable.

stephane-klein commented 10 months ago

Je peux avec la function pg_switch_wal forcer l'exécution de l'archivage d'un fichier wal.

postgres@127:postgres> select pg_switch_wal();
+---------------+
| pg_switch_wal |
|---------------|
| 0/10000160    |
+---------------+
SELECT 1
Time: 0.028s
stephane-klein commented 10 months ago

Prochaine étape :

stephane-klein commented 10 months ago

Prochaine étape :

stephane-klein commented 10 months ago

Todo :

stephane-klein commented 10 months ago

Configurer pgBackRest pour qu'il écrive dans ce Minio précédemment créé

Pour le moment, je n'arrive pas à configurer pgbackrest avec Minio, j'ai l'erreur suivante quand j'essaie de créer un stanza :

$ su postgres -p -c 'pgbackrest --stanza=instance1 --log-level-console=debug stanza-create'
2023-12-31 12:15:14.636 P00  DEBUG:     storage/storage::storageInfo: => {StorageInfo}
2023-12-31 12:15:14.636 P00  DEBUG:     storage/storage::storageExists: => false
2023-12-31 12:15:14.636 P00  DEBUG:     storage/storage::storageExists: (this: {type: s3, path: /repo, write: false}, pathExp: {"<REPO:BACKUP>/backup.info.copy"}, param.timeout: 0)
2023-12-31 12:15:14.636 P00  DEBUG:     storage/storage::storageInfo: (this: {type: s3, path: /repo, write: false}, fileExp: {"<REPO:BACKUP>/backup.info.copy"}, param.level: 3, param.ignoreMissing: true, param.followLink: true, param.noPathEnforce: false)
2023-12-31 12:15:14.637 P00  DEBUG:     storage/storage::storageInfo: => {StorageInfo}
2023-12-31 12:15:14.637 P00  DEBUG:     storage/storage::storageExists: => false
2023-12-31 12:15:14.637 P00  DEBUG:     storage/storage::storageList: (this: {type: s3, path: /repo, write: false}, pathExp: {"<REPO:ARCHIVE>"}, param.errorOnMissing: false, param.nullOnMissing: false, param.expression: null)
2023-12-31 12:15:14.637 P00  DEBUG:     storage/storage::storageNewItr: (this: {type: s3, path: /repo, write: false}, pathExp: {"<REPO:ARCHIVE>"}, param.level: 0, param.errorOnMissing: false, param.recurse: false, param.nullOnMissing: false, param.sortOrder: 0, param.expression: null, param.recurse: false)
2023-12-31 12:15:14.637 P00  DEBUG:     storage/iterator::storageItrNew: (driver: *void, path: {"/repo/archive/instance1"}, level: 3, errorOnMissing: false, nullOnMissing: false, recurse: false, sortOrder: 0, expression: null)
2023-12-31 12:15:14.637 P00  DEBUG:     command/exit::exitSafe: (result: 0, error: true, signalType: 0)
ERROR: [029]: unable to find child 'IsTruncated':0 in node 'ListAllMyBucketsResult'
       --------------------------------------------------------------------
       If SUBMITTING AN ISSUE please provide the following information:

       version: 2.49
       command: stanza-create
       options: --exec-id=14556-9b46ce3b --log-level-console=debug --log-level-file=debug --pg1-path=/var/lib/postgresql/data --repo1-path=/repo --repo1-s3-bucket=pgbackrest --repo1-s3-endpoint=minio/pgbackrest --repo1-s3-key=<redacted> --repo1-s3-key-secret=<redacted> --repo1-s3-region=us-east-1 --no-repo1-storage-verify-tls --repo1-type=s3 --stanza=instance1

       stack trace:
       common/type/xml.c:xmlNodeChildN:239:(test build required for parameters)
           ... function(s) omitted ...
       storage/s3/storage.c:storageS3ListInternal:(trace log level required for parameters)
       storage/s3/storage.c:storageS3List:(trace log level required for parameters)
       storage/iterator.c:storageItrPathAdd:(trace log level required for parameters)
       storage/iterator.c:storageItrNew:(driver: *void, path: {"/repo/archive/instance1"}, level: 3, errorOnMissing: false, nullOnMissing: false, recurse: false, sortOrder: 0, expression: null)
       storage/storage.c:storageNewItr:(this: {type: s3, path: /repo, write: false}, pathExp: {"<REPO:ARCHIVE>"}, param.level: 0, param.errorOnMissing: false, param.recurse: false, param.nullOnMissing: false, param.sortOrder: 0, param.expression: null, param.recurse: false)
       storage/storage.c:storageList:(this: {type: s3, path: /repo, write: false}, pathExp: {"<REPO:ARCHIVE>"}, param.errorOnMissing: false, param.nullOnMissing: false, param.expression: null)
       command/stanza/create.c:cmdStanzaCreate:(void)
       main.c:main:(debug log level required for parameters)
       --------------------------------------------------------------------
2023-12-31 12:15:14.638 P00 DETAIL: statistics: {"http.client":{"total":2},"http.request":{"total":5},"http.session":{"total":1},"socket.client":{"total":2},"socket.session":{"total":1},"tls.client":{"total":2},"tls.session":{"total":1}}
2023-12-31 12:15:14.638 P00   INFO: stanza-create command end: aborted with exception [029]
2023-12-31 12:15:14.638 P00  DEBUG:     command/exit::exitSafe: => 29
2023-12-31 12:15:14.638 P00  DEBUG:     main::main: => 29
...

(Log complet [ici](https://gist.github.com/stephane-klein/758bd00caeaad602f990113f5e001f35))

J'ai cru comprendre ici que les tests de pgbackrest utilisent Minio, conséquence : je suis en train de regarder ici si j'identifie quelque chose que j'ai mal configuré.

stephane-klein commented 10 months ago

je suis en train de regarder ici si j'identifie quelque chose que j'ai mal configuré

J'ai réussi 🙂 !

  minio:
    image: minio/minio:RELEASE.2023-09-30T07-02-29Z
    hostname: pgbackrest.minio
    ports:
    - "443:443"
    - "9001:9001"
    environment:
      MINIO_REGION: us-east-1 # <=== ce qui a corrigé mon problème
      MINIO_DOMAIN: minio 
    command: server /data/ --certs-dir /certs --address ':443' --console-address ':9001'
    volumes:
      - ./volumes/minio/data/:/data/
      - ./minio/certs/:/certs/

Je pense que c'est le paramètre MINIO_REGION: us-east-1 qui a fixé mon problème.

stephane-klein commented 10 months ago

Prochaine étape :

stephane-klein commented 10 months ago

J'ai enfin réussi à restaurer un backup !

Prochaines étapes :

stephane-klein commented 10 months ago

Tester une restauration sur le serveur postgres1 et vérifier qu'ensuite la sauvegarde continue à fonctionner

J'ai réussi, mais il faut que je documente cela.

J'ai dû :

      # PGBACKREST_STANZA_CREATE: 1
      # START_SUPERCRONIC: 1
stephane-klein commented 10 months ago

Après 10 jours, je reprends sur cette issue et je suis un peu perdu 😉.

stephane-klein commented 10 months ago

J'ai travaillé aujourd'hui 1h15 sur cette issue, c'était l'objectif que je m'étais fixé.

J'ai bien avancé, tout semble bien fonctionner.

Prochaines étapes :

stephane-klein commented 9 months ago

Prochaine étapes :

stephane-klein commented 9 months ago

J'ai ajouté un cas concret de production dans le dossier deploy-on-scaleway.

Voici le scénario que j'ai implémenté :

J'ai déjà passé 32h sur cette issue !!! Je pense vraiment que je ne suis pas doué !

Je ne suis pas du tout satisfait de mon code, par exemple :

Prochaines étapes :

Je pense que je suis encore loin de la publication du billet de blog 🙄.

stephane-klein commented 9 months ago

Je viens de créer https://github.com/stephane-klein/backlog/issues/365

stephane-klein commented 9 months ago

Todo :