Cray System Management Documentation > Cray System Management (CSM) Administration Guide > system layout service > Restore SLS Postgres Database from Backup

Restore SLS Postgres Database from Backup

This procedure can be used to restore the SLS Postgres database from a previously taken backup. This can be a manual backup created by the Create a Backup of the SLS Postgres Database procedure, or an automatic backup created by the cray-sls-postgresql-db-backup Kubernetes cronjob.

Prerequisites

(ncn-mw#) Healthy Postgres cluster.

Determine the current state of the cluster.

kubectl exec cray-sls-postgres-0 -n services -c postgres -it -- patronictl list

A healthy cluster will look similar to the following:

+ Cluster: cray-sls-postgres (6975238790569058381) ---+----+-----------+
|        Member       |    Host    |  Role  |  State  | TL | Lag in MB |
+---------------------+------------+--------+---------+----+-----------+
| cray-sls-postgres-0 | 10.44.0.40 | Leader | running |  1 |           |
| cray-sls-postgres-1 | 10.36.0.37 |        | running |  1 |         0 |
| cray-sls-postgres-2 | 10.42.0.42 |        | running |  1 |         0 |
+---------------------+------------+--------+---------+----+-----------+

Previously taken backup of the SLS Postgres cluster (either a manual or automatic backup).

(ncn-mw#) Check for any available automatic SLS Postgres backups:

cray artifacts list postgres-backup --format json | jq -r '.artifacts[].Key | select(contains("sls"))'

Example output:

cray-sls-postgres-2021-07-11T23:10:08.manifest
cray-sls-postgres-2021-07-11T23:10:08.psql

Procedure

(ncn-mw#) Retrieve a previously taken SLS Postgres backup.

This can be either a previously taken manual SLS backup or an automatic Postgres backup in the postgres-backup S3 bucket.

From a previous manual backup:
1. Copy over the folder or tarball containing the Postgres back up to be restored. If it is a tarball extract it.
2. Set the environment variable POSTGRES_SQL_FILE to point toward the .psql file in the backup folder:
```
POSTGRES_SQL_FILE=/root/cray-sls-postgres-backup_2021-07-07_16-39-44/cray-sls-postgres-backup_2021-07-07_16-39-44.psql
```
3. Set the environment variable POSTGRES_SECRET_MANIFEST to point toward the .manifest file in the backup folder:
```
POSTGRES_SECRET_MANIFEST=/root/cray-sls-postgres-backup_2021-07-07_16-39-44/cray-sls-postgres-backup_2021-07-07_16-39-44.manifest
```

From a previous automatic Postgres backup:

Check for available backups:

cray artifacts list postgres-backup --format json | jq -r '.artifacts[].Key | select(contains("sls"))'
cray-sls-postgres-2021-07-11T23:10:08.manifest
cray-sls-postgres-2021-07-11T23:10:08.psql

Then set the following environment variables for the name of the files in the backup:

POSTGRES_SECRET_MANIFEST_NAME=cray-sls-postgres-2021-07-11T23:10:08.manifest
POSTGRES_SQL_FILE_NAME=cray-sls-postgres-2021-07-11T23:10:08.psql

Download the .psql file for the postgres backup:

cray artifacts get postgres-backup "$POSTGRES_SQL_FILE_NAME" "$POSTGRES_SQL_FILE_NAME"

Download the .manifest file for the SLS backup:

cray artifacts get postgres-backup "$POSTGRES_SECRET_MANIFEST_NAME" "$POSTGRES_SECRET_MANIFEST_NAME"

Set environment variables pointing to the full path of the .psql and .manifest files:

export POSTGRES_SQL_FILE=$(realpath "$POSTGRES_SQL_FILE_NAME")
export POSTGRES_SECRET_MANIFEST=$(realpath "$POSTGRES_SECRET_MANIFEST_NAME")

(ncn-mw#) Verify the POSTGRES_SQL_FILE and POSTGRES_SECRET_MANIFEST environment variables are set correctly:

echo "$POSTGRES_SQL_FILE"
echo "$POSTGRES_SECRET_MANIFEST"

Example output:

/root/cray-sls-postgres-backup_2021-07-07_16-39-44/cray-sls-postgres-backup_2021-07-07_16-39-44.psql
/root/cray-sls-postgres-backup_2021-07-07_16-39-44/cray-sls-postgres-backup_2021-07-07_16-39-44.manifest

(ncn-mw#) Re-run the SLS loader job:

kubectl -n services get job cray-sls-init-load -o json | jq 'del(.spec.selector)' | jq 'del(.spec.template.metadata.labels."controller-uid")' | jq 'del(.spec.template.metadata.labels."batch.kubernetes.io/controller-uid")' | kubectl replace --force -f -

(ncn-mw#) Wait for the job to complete:

kubectl wait -n services job cray-sls-init-load --for=condition=complete --timeout=5m

(ncn-mw#) Determine leader of the Postgres cluster:

POSTGRES_LEADER=$(kubectl exec cray-sls-postgres-0 -n services -c postgres -t -- patronictl list -f json | jq  -r '.[] | select(.Role == "Leader").Member')

(ncn-mw#) Check the environment variable to see the current leader of the Postgres cluster:
```
echo $POSTGRES_LEADER
```
Example output:
```
cray-sls-postgres-0
```
(ncn-mw#) Determine the database schema version of the currently running SLS database and verify that it matches the database schema version from the Postgres backup:
1. Database schema of the currently running SLS Postgres instance.
```
kubectl exec $POSTGRES_LEADER -n services -c postgres -it -- bash -c "psql -U slsuser -d sls -c 'SELECT * FROM schema_migrations'"
```
  Example output:
```
version | dirty
---------+-------
       3 | f
(1 row)
```
  The output above shows the database schema is at version 3.
2. Database schema version from the Postgres backup:
```
cat "$POSTGRES_SQL_FILE" | grep "COPY public.schema_migrations" -A 2
```
  Example output:
```
COPY public.schema_migrations (version, dirty) FROM stdin;
3       f
\.
```
  The output above shows the database schema is at version 3.
3. If the database schema versions match, proceed to the next step. Otherwise, the Postgres backup taken is not applicable to the currently running instance of SLS.
  
  WARNING: If the database schema versions do not match, then SLS will need to be either upgraded or downgraded to a version with a compatible database schema version, ideally to the same version of SLS that was used to create the Postgres backup.
(ncn-mw#) Restore the database from the backup using the restore_sls_postgres_from_backup.sh script.

This script requires the POSTGRES_SQL_FILE and POSTGRES_SECRET_MANIFEST environment variables to be set.

THIS WILL DELETE AND REPLACE THE CURRENT CONTENTS OF THE SLS DATABASE
```
/usr/share/doc/csm/scripts/operations/system_layout_service/restore_sls_postgres_from_backup.sh
```
(ncn-mw#) Verify the health of the SLS Postgres cluster by running the following scripts.
- If there are issues with run_hms_ct_tests.sh, then see Interpreting HMS Health Check Results.
- If there are issues with verify_hsm_discovery.py, then see Interpreting HSM discovery results.
```
/opt/cray/csm/scripts/hms_verification/run_hms_ct_tests.sh -t sls
/opt/cray/platform-utils/ncnPostgresHealthChecks.sh
/opt/cray/csm/scripts/hms_verification/verify_hsm_discovery.py
```

(ncn-mw#) Verify that the service is functional:

cray sls version list --format toml

Example output:

Counter = 5
LastUpdated = "2021-04-05T22:51:36.575276Z"

(ncn-mw#) Get the number of hardware objects stored in SLS:

cray sls hardware list --format json | jq .[].Xname | wc -l

(ncn-mw#) Get the name of networks stored in SLS:

If the system does not have liquid cooled hardware, the HMN_MTN and NMN_MTN networks may not be present.
```
cray sls networks list --format json | jq -r .[].Name
```
Example output:
```
HMN_MTN
HMN_RVR
NMNLB
NMN
NMN_MTN
NMN_RVR
CAN
HMN
HMNLB
HSN
MTL
```