Skip to content
For Managed AI Platform users, see the cloud platform documentation
Self-managed installation and maintenance > Backup and restore > Database restore procedure > Restore PostgreSQL

Restore PostgreSQL

This operation should be executed from any macOS or GNU/Linux machine where the previously taken PostgreSQL backup is located.

Warning

If the DataRobot application is configured to use managed services (external PCS), refer to the Backing up and restoring guide for Amazon RDS for PostgreSQL instead of this guide.

Note

When restoring backed up PostgreSQL databases, skip the following databases (see the Restore backup section below for more):

  • identityresourceservice
  • sushihydra If DR version is >= 10.1.0:
  • cnshydra

Prerequisites

  • Utility pg_restore:
  • DataRobot 11.0: Use version 12 of pg_restore on the host where the backup is created.
  • DataRobot 11.1 or newer: Use version 14 of pg_restore on the host where the backup is created.
  • Utility kubectl version 1.23 is installed on the host from where the backup is restored.
  • Utility kubectl is configured to access the Kubernetes cluster where the DataRobot application is running. Verify this with the kubectl cluster-info command.

First, export name of DataRobot application Kubernetes namespace in DR_CORE_NAMESPACE variable: 

export DR_CORE_NAMESPACE=<namespace>

Define where the backups are stored on the host from where the backup will be restored: 

export BACKUP_LOCATION=~/datarobot-backups

The restore process requires you to forward a local port to the remote PostgreSQL service. Define which local port you use. This example uses port 54321, but you can use another: 

export LOCAL_PGSQL_PORT=54321

Obtain the PostgreSQL admin user password: 

export PGPASSWORD=$(kubectl -n $DR_CORE_NAMESPACE get secret pcs-postgresql -o jsonpath='{.data.postgres-password}' | base64 -d)
echo ${PGPASSWORD}

Forward local port to remote PostgreSQL service deployed in the Kubernetes: 

kubectl -n $DR_CORE_NAMESPACE port-forward svc/pcs-postgresql --address 127.0.0.1 $LOCAL_PGSQL_PORT:5432 &

Restore backup

Note

When restoring 10.1.0 and above, omit the db cnshydra by including ! -name cnshydra in the find statement below.

Restore all databases from $BACKUP_LOCATION/pgsql directory: 

for db in $(find $BACKUP_LOCATION/pgsql -mindepth 1 -maxdepth 1 -type d ! -name postgres ! -name sushihydra ! -name identityresourceservice); do
  pg_restore -v -Upostgres -hlocalhost -p$LOCAL_PGSQL_PORT -cC -j4 -d postgres "$db";
done

After the backup completes, find the process ID of the port-forwarding process: 

ps aux | grep -E "port-forwar[d].*$LOCAL_PGSQL_PORT"

Then stop it: 

kill <pid_of_the_kubectl_port-forward>

Restore PostgreSQL credentials. The following script will patch existing secrets with data exported form the old DataRobot deployment

kubectl -n $DR_CORE_NAMESPACE patch secrets/pcs-postgresql --patch-file="$BACKUP_LOCATION/secrets/dr/pcs-postgresql.json"