Cray System Management Documentation > Cray System Management (CSM) Administration Guide > hardware state manager > Restore Hardware State Manager (HSM) Postgres Database from Backup

Restore Hardware State Manager (HSM) Postgres Database from Backup

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

Prerequisites

Healthy System Layout Service (SLS). Recover this first, if it is also affected.

Healthy HSM Postgres cluster.

(ncn-mw#) Determine the current state of the cluster.

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

A healthy cluster will look similar to the following:

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

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

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

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

Example output:

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

Procedure

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

This can be either a previously taken manual HSM 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 backup 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.
```
export POSTGRES_SQL_FILE=/root/cray-smd-postgres-backup_2021-07-07_16-39-44/cray-smd-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.
```
export POSTGRES_SECRET_MANIFEST=/root/cray-smd-postgres-backup_2021-07-07_16-39-44/cray-smd-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("smd"))'

Example output:

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

Set the following environment variables for the name of the files in the backup.

export POSTGRES_SECRET_MANIFEST_NAME=cray-smd-postgres-2021-07-11T23:10:08.manifest
export POSTGRES_SQL_FILE_NAME=cray-smd-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 HSM backup.

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

Setup 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 that the POSTGRES_SQL_FILE environment variable is set correctly.

echo "$POSTGRES_SQL_FILE"

Example output:

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

(ncn-mw#) Verify that the POSTGRES_SECRET_MANIFEST environment variable is set correctly.

echo "$POSTGRES_SECRET_MANIFEST"

Example output:

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

(ncn-mw#) Scale HSM to 0 replicas.

CLIENT=cray-smd
POSTGRESQL=cray-smd-postgres
NAMESPACE=services

kubectl scale deployment ${CLIENT} -n ${NAMESPACE} --replicas=0

Expected output:

deployment.apps/cray-smd scaled

(ncn-mw#) Wait for the HSM pods to terminate.

while [ $(kubectl get pods -n ${NAMESPACE} -l app.kubernetes.io/name="${CLIENT}" | grep -v NAME | wc -l) != 0 ] ; do echo "  waiting for pods to terminate"; sleep 2; done

(ncn-mw#) Re-run the HSM loader job.

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

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

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

(ncn-mw#) Determine which Postgres member is the leader.

kubectl exec "${POSTGRESQL}-0" -n ${NAMESPACE} -c postgres -it -- patronictl list

Example output:

+-------------------+---------------------+------------+--------+---------+----+-----------+
|      Cluster      |        Member       |    Host    |  Role  |  State  | TL | Lag in MB |
+-------------------+---------------------+------------+--------+---------+----+-----------+
| cray-smd-postgres | cray-smd-postgres-0 | 10.42.0.25 | Leader | running |  1 |           |
| cray-smd-postgres | cray-smd-postgres-1 | 10.44.0.34 |        | running |    |         0 |
| cray-smd-postgres | cray-smd-postgres-2 | 10.36.0.44 |        | running |    |         0 |
+-------------------+---------------------+------------+--------+---------+----+-----------+

(ncn-mw#) Create a variable for the identified leader.
```
POSTGRES_LEADER=cray-smd-postgres-0
```

(ncn-mw#) Determine the database schema version of the currently running HSM database.

Database schema of the currently running HSM Postgres instance.

kubectl exec $POSTGRES_LEADER -n services -c postgres -it -- bash -c "psql -U hmsdsuser -d hmsds -c 'SELECT * FROM system'"

Example output:

 id | schema_version | system_info
----+----------------+-------------
  0 |             17 | {}
(1 row)

The output above shows the database schema is at version 17.

(ncn-mw#) Determine the database schema version from the Postgres backup.

cat "$POSTGRES_SQL_FILE" | grep "COPY public.system" -A 2

Example output:

COPY public.system (id, schema_version, dirty) FROM stdin;
0       17       f
\.

The output above shows the database schema is at version 17.

Verify that the two database schema versions match.

If the database schema versions match, then proceed to the next step. Otherwise, this Postgres backup is not applicable to the currently running instance of HSM.

WARNING: If the database schema versions do not match, then HSM will need to be either upgraded or downgraded to a version with a compatible database schema version. Ideally, it will be to the same version of HSM that was used to create the Postgres backup.

(ncn-mw#) Delete the postgresql resource (which includes the PVCs).

CLIENT=cray-smd
POSTGRESQL=cray-smd-postgres
NAMESPACE=services
kubectl get postgresql ${POSTGRESQL} -n ${NAMESPACE} -o json | jq 'del(.spec.selector)' | jq 'del(.spec.template.metadata.labels."controller-uid")' | jq 'del(.status)' > postgres-cr.yaml
kubectl delete -f postgres-cr.yaml

Expected output:

postgresql.acid.zalan.do "cray-smd-postgres" deleted

(ncn-mw#) Wait for the Postgres pods to terminate.

while [ $(kubectl get pods -l "application=spilo,cluster-name=${POSTGRESQL}" -n ${NAMESPACE} | grep -v NAME | wc -l) != 0 ] ; do echo "  waiting for pods to terminate"; sleep 2; done

(ncn-mw#) Re-create the postgresql resource.

kubectl create -f postgres-cr.yaml

Expected output:

postgresql.acid.zalan.do/cray-smd-postgres created

(ncn-mw#) Wait for the Postgres cluster to start running.

while [ $(kubectl get pods -l "application=spilo,cluster-name=${POSTGRESQL}" -n ${NAMESPACE} | grep -v NAME | wc -l) != 3 ] ; do echo "  waiting for pods to start running"; sleep 2; done

(ncn-mw#) Determine which Postgres member is the new leader.

kubectl exec "${POSTGRESQL}-0" -n ${NAMESPACE} -c postgres -it -- patronictl list

Example output:

+-------------------+---------------------+------------+--------+---------+----+-----------+
|      Cluster      |        Member       |    Host    |  Role  |  State  | TL | Lag in MB |
+-------------------+---------------------+------------+--------+---------+----+-----------+
| cray-smd-postgres | cray-smd-postgres-0 | 10.42.0.25 | Leader | running |  1 |           |
| cray-smd-postgres | cray-smd-postgres-1 | 10.44.0.34 |        | running |    |         0 |
| cray-smd-postgres | cray-smd-postgres-2 | 10.36.0.44 |        | running |    |         0 |
+-------------------+---------------------+------------+--------+---------+----+-----------+

(ncn-mw#) Set a variable for the new leader.
```
POSTGRES_LEADER=cray-smd-postgres-0
```
(ncn-mw#) Copy the dump taken above to the Postgres leader pod and restore the data.

If the dump exists in a different location, then adjust this example as needed.
```
cat ${POSTGRES_SQL_FILE} | kubectl exec ${POSTGRES_LEADER} -c postgres -n ${NAMESPACE} -it -- psql -U postgres
```
(ncn-mw#) Clear stale data from tables in Postgres.

The backup will have restored tables that may contain out of date information. To refresh this data, it must first be deleted.

Delete the entries in the Ethernet interfaces table. These will automatically get repopulated during rediscovery.
```
kubectl exec $POSTGRES_LEADER -n services -c postgres -it -- bash -c "psql -U hmsdsuser -d hmsds -c 'DELETE FROM comp_eth_interfaces'"
```

(ncn-mw#) Restore the secrets.

Once the dump has been restored onto the newly built Postgres cluster, the Kubernetes secrets need to match with the Postgres cluster, otherwise the service will experience readiness and liveness probe failures because it will be unable to authenticate to the database.

With secrets manifest from an existing backup:

If the Postgres secrets were automatically backed up, then re-create the secrets in Kubernetes.

Delete and re-create the four cray-smd-postgres secrets using the manifest saved in $POSTGRES_SECRET_MANIFEST.

kubectl delete secret postgres.cray-smd-postgres.credentials service-account.cray-smd-postgres.credentials hmsdsuser.cray-smd-postgres.credentials standby.cray-smd-postgres.credentials -n ${NAMESPACE}
kubectl apply -f ${POSTGRES_SECRET_MANIFEST}

Without the previous secrets from a backup:

If the Postgres secrets were not backed up, then update the secrets in Postgres.

Determine what secrets are associated with the Postgres credentials.

kubectl get secrets -n ${NAMESPACE} | grep "${POSTGRESQL}.credentials"

Example output:

services            hmsdsuser.cray-smd-postgres.credentials                       Opaque                                2      31m
services            postgres.cray-smd-postgres.credentials                        Opaque                                2      31m
services            service-account.cray-smd-postgres.credentials                 Opaque                                2      31m
services            standby.cray-smd-postgres.credentials                         Opaque                                2      31m

For each secret identified in the previous step, get the username and password from Kubernetes and update the Postgres database with this information.
1. Get the username and password for a secret (for example hmsdsuser.cray-smd-postgres.credentials):
```
kubectl get secret hmsdsuser.cray-smd-postgres.credentials -n ${NAMESPACE} -ojsonpath='{.data.username}' | base64 -d
kubectl get secret hmsdsuser.cray-smd-postgres.credentials -n ${NAMESPACE} -ojsonpath='{.data.password}'| base64 -d
```
2. Start an interactive shell with the leader pod.
```
kubectl exec ${POSTGRES_LEADER} -n ${NAMESPACE} -c postgres -it -- bash
```
3. (pod#) Open a Postgres console.
```
/usr/bin/psql postgres postgres
```
4. (postgres=#) Reset the user’s password.
```
ALTER USER hmsdsuser WITH PASSWORD 'ABCXYZ';
ALTER ROLE
```
Continue the above process until all ${POSTGRESQL}.credentials secrets have been updated in the database.

(ncn-mw#) Restart the Postgres cluster.

kubectl delete pod "${POSTGRESQL}-0" "${POSTGRESQL}-1" "${POSTGRESQL}-2" -n ${NAMESPACE}
while [ $(kubectl get postgresql ${POSTGRESQL} -n ${NAMESPACE} -o json | jq -r '.status.PostgresClusterStatus') != "Running" ]; do echo "waiting for ${POSTGRESQL} to start running"; sleep 2; done

(ncn-mw#) Scale the client service back to 3 replicas.

kubectl scale deployment ${CLIENT} -n ${NAMESPACE} --replicas=3
kubectl -n ${NAMESPACE} rollout status deployment ${CLIENT}

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

cray hsm service ready list --format toml

Example output:

code = 0
message = "HSM is healthy"

(ncn-mw#) Get the number of node objects stored in HSM.

cray hsm state components list --type Node --format json | jq .Components[].ID | wc -l

(ncn-mw#) Resynchronize the component state and inventory.

After restoring HSM’s Postgres from a back up, some of the transient data like component state and hardware inventory may be stale. Refreshing this involves kicking off an HSM rediscovery.
```
endpoints=$(cray hsm inventory redfishEndpoints list --format json | jq -r '.[]|.[]|.ID')
for e in $endpoints; do cray hsm inventory discover create --xnames ${e}; done
```
(ncn-mw#) Wait for discovery to complete.

Discovery is complete after there are no redfishEndpoints left in the DiscoveryStarted state. A value of 0 will be returned by the following command when this is the case.
```
cray hsm inventory redfishEndpoints list --format json | grep -c "DiscoveryStarted"
```
(ncn-mw#) Check for discovery errors.
```
cray hsm inventory redfishEndpoints list --format json | grep LastDiscoveryStatus | grep -v -c "DiscoverOK"
```
If any of the RedfishEndpoint entries have a LastDiscoveryStatus other than DiscoverOK after discovery has completed, then see Troubleshoot Issues with Redfish Endpoint Discovery.

(ncn-mw#) Perform this step only if the system has Intel management NCNs; for HPE or Gigabyte management NCNs skip this step. Because of firmware issues, Intel BMCs do not report the MAC addresses of the management NICs via Redfish. When an Intel BMC is discovered after restoring from a Postgres backup, the management NIC MAC addresses in HSM will have an empty component ID. The following script will correct any Ethernet interfaces for a Intel management NCN without a component ID.

UNKNOWN_NCN_MAC_ADDRESSES=$(cray hsm inventory ethernetInterfaces list --component-id "" --format json | jq '.[] | select(.Description == "- kea") | .MACAddress'  -r)

for UNKNOWN_MAC_ADDRESS in $UNKNOWN_NCN_MAC_ADDRESSES; do
    XNAME=$(cray bss bootparameters list --format json | jq --arg MAC "${UNKNOWN_MAC_ADDRESS}" '.[] | select(.params != null) | select(.params | test($MAC)) | .hosts[]' -r)


    if [[ $(wc -l <<< $(printf $XNAME)) -ne 1 ]]; then
        echo "MAC Address ${UNKNOWN_MAC_ADDRESS} unexpected number matches found. Expected 1 match, but found: $(wc -l <<< $(printf $XNAME))"
        continue
    fi


    echo "MAC: ${UNKNOWN_MAC_ADDRESS} is ${XNAME}"
    EI_ID=$(echo "$UNKNOWN_MAC_ADDRESS" | sed 's/://g')
    echo "Updating ${EI_ID} in HSM EthernetInterfaces with component ID ${XNAME}"
    cray hsm inventory ethernetInterfaces update ${EI_ID} --component-id ${XNAME}
done