Restoring data from pg_dump snapshot

TL;DR: Building on the previous post about capturing "snapshots" of PostgreSQL databases and their benefits for testing and debugging, this post dives into the practical side: how to leverage these snapshots and restore your database to a specific state with ease.

Prerequisite

1. For linux OS, we need to add some environment variables on our rc files: bashrc or zshrc:

export PGHOST=${postgres_host}
export PGPORT=${postgres_port}
export PGUSER=${postgres_username}
export PGPASSWORD=${postgres_password}

We'll be dependent on the values of these environment variables to be able to effectively use the next steps.

1. If your dev environment is based on container technologies like docker, make sure the container with the database is running.
2. Since we are restoring the data of an existing database, drop the database on the postgre server if it exists otherwise create the database.

Bash script for restoring snapshot data

Create a .sh file on the directory where your service's repository is stored and then paste the contents of the script below.

I'll leave some comments to further explain the details of what the script does so that users can understand it better.

#!/usr/bin/bash

# HOW TO USE?
# 1. this must be used after the pg_dump data has been successfully created.
# 2. update the value of the "parent_directory".
# 3. also update the value of the "target_database" array.

# ---
# this variables refers to the directory level where the "database_dump" folder is located
parent_directory="home/development-environments/service-1"

cd ~/
cd "$parent_directory/database_dump"

# ---
# list your databases that you want to have a restore here.
# This variable refers to the databases that you want to restore. Make sure that all the database you wish to restore are all newly created ones.
target_database=("database_1" "database_2" "database_3")

# ---
# iterates throught the databases to be restored. Then prints a message to indicate if the procedure was a success or fail.
for database_name in "${target_database[@]}"; 
do
  psql -h $PGHOST -U $PGUSER "$database_name" < "$database_name.sql"

  if [ $? -eq 0 ]; then
   echo "${database_name} data has been restored successfully."
  else
    echo "Error: Data restoration attempt for ${database_name} has failed."
  fi
done

Conclusion

Restoring data from a single .sql database snapshot is a straightforward process. However, when dealing with multiple snapshots, automation becomes essential for efficiency and consistency. To optimize the restoration of multiple snapshots, consider utilizing Bash scripts. These scripts automate the process, significantly reducing manual effort and potential errors.

When paired with the pg_dump method for creating database snapshots, the restoration procedure via bash scripts create a powerful combination that empowers developers to focus on core tasks while streamlining testing and debugging experiences.