vshn / appcat-service-postgresql

AppCat Service Provider for PostgreSQL
https://vshn.github.io/appcat-service-postgresql/
BSD 3-Clause "New" or "Revised" License
0 stars 0 forks source link

Use DB dumps for K8up backups #80

Closed ccremer closed 2 years ago

ccremer commented 2 years ago

Summary

Checklist

For Code changes

ccremer commented 2 years ago

For review: If you want to verify that restoration works, follow roughly this procedure:

  1. make clean -> ensure that there's no existing backups in Minio
  2. makelocal-install install-samples s3-credentials
  3. Patch the generated K8up Schedule so it does a backup every 3 minutes (*/3 * * * *) (e.g. with kubectl edit)
  4. Wait until a backup succeeds
  5. Follow the how-to until/including step 4 (just before restoring data)
  6. Insert some fake data:
    $ kubectl exec -i -t -n sv-postgresql-s-evident-yellowjacket-f9f2 postgresql-0 -c postgresql -- sh -c "clear; (bash || ash || sh)"
    $ (inside container) PGPASSWORD=$POSTGRES_PASSWORD psql -U $POSTGRES_USER
    $ (inside psql) CREATE TABLE phonebook(phone VARCHAR(32), firstname VARCHAR(32), lastname VARCHAR(32), address VARCHAR(64));
    $ (inside psql) INSERT INTO phonebook(phone, firstname, lastname, address) VALUES('+1 123 456 7890', 'John', 'Doe', 'North America');
    $ (inside psql) SELECT * FROM phonebook ORDER BY lastname;
          phone      | firstname | lastname |    address    
    -----------------+-----------+----------+---------------
     +1 123 456 7890 | John      | Doe      | North America
  7. Now, wait until backup happens again using the newly-added data
  8. Select the newest snapshot ID, and verify that the dump contains your data: restic dump --quiet ${SNAPSHOT_ID} /${deployment_namespace}-postgresql.sql
  9. Insert more data:
    $ (inside psql) INSERT INTO phonebook(phone, firstname, lastname, address) VALUES('+1 123 456 7890', 'Hans', 'Muster', 'Europe');
    $ (inside psql) SELECT * FROM phonebook ORDER BY lastname;
          phone      | firstname | lastname |    address    
    -----------------+-----------+----------+---------------
     +1 123 456 7890 | John      | Doe      | North America
     +1 123 456 7890 | Hans      | Muster   | Europe
  10. Restore the backup from dump (step 5). This should remove the line with "Hans Muster"
  11. Verify that there's only the data from the backup:
    $ (inside psql) SELECT * FROM phonebook ORDER BY lastname;
          phone      | firstname | lastname |    address    
    -----------------+-----------+----------+---------------
     +1 123 456 7890 | John      | Doe      | North America
ccremer commented 2 years ago

Converting back to draft. I noticed that as of now, only the primary database (as defined by $POSTGRES_DB) is going to be backed up (or restored). I'll first investigate a bit into possibilities that all DBs are going to be backed up and restored.

ccremer commented 2 years ago

I was able to come up with a solution that is able to backup and restore all databases. This PR is now reviewable again.