How to restore a PostgreSQL database with stash saved previously with auto-backup

[hpannetier]

Hello,
I'm currently using the stash-entreprise licence with the trial license.
I followed your guide https://stash.run/docs/v2021.08.02/addons/postgres/auto-backup/
and successfully backuped my posgresql database.

The question is, how do I proceed to restore a snapshot of my DB.
I do not find any similar user's guide describing the restore session in case of database.

Could you point me out an example?
Thanks a lot

[hossainemruz]

https://stash.run/docs/v2021.08.02/addons/postgres/standalone/#restore-postgresql

[hpannetier]

Hello,
Thank you for your reply.
I followed the steps you pointed out.
I have no error message and the restore session seems successful.
However when I check the postgres db content it looks like that the backup has not been applied.

$ kubectl describe restoresession sample-postgres-restore -n dev-project1
Name:         sample-postgres-restore
Namespace:    dev-project1
Labels:       <none>
Annotations:  <none>
API Version:  stash.appscode.com/v1beta1
Kind:         RestoreSession
Metadata:
  Creation Timestamp:  2021-08-31T12:47:00Z
  Finalizers:
    stash.appscode.com
  Generation:  1
…
Spec:
  Driver:  Restic
  Repository:
    Name:  app-postgresql
  Runtime Settings:
  Target:
    Ref:
      API Version:  appcatalog.appscode.com/v1alpha1
      Kind:         AppBinding
      Name:         postgresql
    Rules:
      Snapshots:
        latest
  Task:
    Name:  postgres-restore-13.1
  Temp Dir:
Status:
  Conditions:
    Last Transition Time:  2021-08-31T12:47:00Z
    Message:               Repository dev-project1/app-postgresql exist.
    Reason:                RepositoryAvailable
    Status:                True
    Type:                  RepositoryFound
    Last Transition Time:  2021-08-31T12:47:00Z
    Message:               Backend Secret dev-project1/s3-secret-bkp-pg-echopen exist.
    Reason:                BackendSecretAvailable
    Status:                True
    Type:                  BackendSecretFound
    Last Transition Time:  2021-08-31T12:47:00Z
    Message:               Restore target appcatalog.appscode.com/v1alpha1 appbinding/postgresql found.
    Reason:                TargetAvailable
    Status:                True
    Type:                  RestoreTargetFound
    Last Transition Time:  2021-08-31T12:47:01Z
    Message:               Successfully created restore job.
    Reason:                RestoreJobCreationSucceeded
    Status:                True
    Type:                  RestoreJobCreated
  Phase:                   Succeeded
  Session Duration:        13s
  Stats:
    Duration:   5.328333361s
    Phase:      Succeeded
  Total Hosts:  1
Events:         <none>

Here is the content of the restoresessions.yaml

$ cat restoresessions.yaml
apiVersion: stash.appscode.com/v1beta1
kind: RestoreSession
metadata:
  name: sample-postgres-restore
  namespace: dev-project1
spec:
  task:
    name: postgres-restore-13.1
  repository:
    name: app-postgresql
  target:
    ref:
      apiVersion: appcatalog.appscode.com/v1alpha1
      kind: AppBinding
      name: postgresql
  rules:
  - snapshots: [latest]

I 'm puzzled and don't know how to proceed to understand why actually the restore has not been applied despite the success messages.

One more basic question:
I have 2 DBs, echopenhapidb and keycloakdb, hold by the same postgres pod. Is there an issue related with the fact that I backuped both of them with a single auto-backup job and wants to restore them with a single restoresession?

root@postgresql-56b788bd8f-qp8mf:/# psql -U postgres
psql (12.6 (Debian 12.6-1.pgdg100+1))
Type "help" for help.

postgres=# \l
                                   List of databases
     Name      |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
---------------+----------+----------+------------+------------+-----------------------
 echopenhapidb | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 keycloakdb    | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0     | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
               |          |          |            |            | postgres=CTc/postgres
 template1     | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
               |          |          |            |            | postgres=CTc/postgres

Thanks again for your help.

[hossainemruz]

Can you please share the log from the restore job? Use --all-containers while viewing the log.

[hossainemruz]

I have 2 DBs, echopenhapidb and keycloakdb, hold by the same postgres pod. Is there an issue related with the fact that I backuped both of them with a single auto-backup job and wants to restore them with a single restoresession?

No. By default Stash takes backup of all databases and will restore all databases unless you specify a specific database.

[hpannetier]

Please find attached the logs
logs-restore.txt

It seems that the restore is attempted on the right target.
In the last attempt I just modified the value of two parameters within a table.
After the restore I still read the last values not the one that should come from the restore.
However, I'm wondering if my procedure is correct:
1 - I paused the backup job
2 - I stopped all pods accessing the DB, except the postgresql pod
3 - I altered 2 parameters of the keycloakdb DB
4 - I run the restore session

Is there some specific postgres commands to be run before applying the restore?
Regards

[hossainemruz]

You have to cleanup the old database before restore. By default Stash does not overwrite any existing data.