Backup PostgreSQL¶

This operation can be executed from any macOS or GNU/Linux machine that has enough space to store the backup.

Prerequisites¶

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

Considerations¶

As the database size increases, the execution time of pg_dump also increases. This can reach impractical durations in certain scenarios, potentially spanning days. We recommend using managed services (external PCS).

Create backup¶

We recommend using managed services (external PCS) and scheduling backups simultaneously for managed Postgres, Redis, and Mongo.

If you are using pcs-ha charts, you can use the script below to create a backup. 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 where the backup is created. This example uses ~/datarobot-backups/, but you can choose a different location:

export BACKUP_LOCATION=~/datarobot-backups/pgsql
mkdir -p $BACKUP_LOCATION

The backup 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 &

List databases for backup

dbs=$(psql -Upostgres -hlocalhost -p $LOCAL_PGSQL_PORT -t -c "SELECT datname FROM pg_database;" \
| grep -vE 'template|repmgr|postgres' \
| sed 's/\r//g')

cd ${BACKUP_LOCATION}/; mkdir -p $dbs

Backup the database one-by-one:

for db in $dbs; do
  pg_dump -Upostgres -hlocalhost -p$LOCAL_PGSQL_PORT -Fd -j4 "$db" -f "$BACKUP_LOCATION/$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>