Skip to content
For Self-Managed AI Platform users running v11.1, see the on-premise platform documentation

Restore PostgreSQL

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

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

ADDITIONAL NOTE: When restoring backed up postgres DBs, the following databases must be skipped (see Restore backup section below for more): * identityresourceservice * sushihydra If DR version is >= 10.1.0: * cnshydra

前提条件

  • Utility pg_restore:
  • DataRobot 11.0: Please use version 12 of pg_restore on the host where the backup will be created.
  • DataRobot 11.1 or newer: Please use version 14 of pg_restore on the host where the backup will be created.
  • Utility kubectl of version 1.23 is installed on the host from where the backup will be restored.
  • Utility kubectl is configured to access the Kubernetes cluster where DataRobot application is running, verify this with 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

Restore process will require you to forward local port to remote PostgreSQL service, please define which local port you will use. In the following example I use port 54321, but feel free to 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

Once backup complete, find process ID of the port-forwarding process: 

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

and 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"