How To Backup a Remote PostgreSQL Database and Run it Locally With Docker

You probably access remote databases all the time, however, if you want to work offline it could be cumbersome not to have access to your database. This is a simple method to back it up and run it locally using Docker.

Scroll down to the bottom for a script you can use instead.

Get started

To begin, we need to install psql as we are going to do this using the CLI. We will also be using pg_dump and pg_restore which often come installed with psql or postgresql.

# Using apt-get
sudo apt-get -y install postgresql

# using brew
brew tap homebrew/services
brew install postgres

Create a dump to a local file

We need to back up the data into a local dump file using the pg_dump command. In this case, it will save the data to a file called database.dump.

You need to replace the <values with the actual remote database values, like host and username. This might take a while.

# Might be needed if you have SSL
export PGSSLMODE=require

pg_dump -h <host-name> -d <database-name> -U <user> -Fc -b -f database.dump

Create database in Docker

We need to create the Postgres database in Docker.

docker run -d --rm 
    -e POSTGRES_PASSWORD=secret
    -e POSTGRES_USER=user 
    -p 5432:5432 
    --name database postgres

This command will start the Docker container in detached mode, meaning it will run in the background. Once the container has stopped, it will be deleted as we added the --rm flag. We added the environment variables for the port, user and password:

• Username: user
• Password: secret
• Port (default): 5432

Populate database

To populate the database from the dump file, we first need to create an actual database. In this case, I will just name it db.

echo "create database db;" | psql -h localhost -p 5432 -U user -W

The database will be created with localhost as host. The user and port are the same as above. A prompt will appear to enter your password to be able to continue.

Finally, we need to restore the database data from the dump file.

pg_restore --no-acl --no-owner -h localhost -p 5432 -d db database.dump -U user -W

We use the pg_restore command with some flags. In this case, the most important ones are referring to the port we defined, the database file, the user and what the local database name is.

Script

In case you want to run everything with a script, you can copy it from below. This will look for a local dump file, if it cannot find it, it will download it, and then create the local database and populate it.

#! /bin/bash
PG_USER=user
PG_PASSWORD=secret
PG_PORT=5432
PG_DATABASE=db

REMOTE_HOST=<remote-host>
REMOTE_USER=<remote-user>
REMOTE_DATABASE=<remote-database>

FILE=./database.dump

if [ ! -f "$FILE" ]; then
    echo "Database dump does not exist, will download. Enter password for database on prompt and wait, it might take some time..."

    export PGSSLMODE=require
    pg_dump -h $REMOTE_HOST -d $REMOTE_DATABASE -U $REMOTE_USER -Fc -b -f $FILE
fi

echo "Starting Postgres container..."
docker run -d --rm 
    -e POSTGRES_PASSWORD=$PG_PASSWORD -e POSTGRES_USER=$PG_USER 
    -p $PG_PORT:5432 
    --name database postgres

echo "Waiting for startup..."
sleep 3

echo "Creating database..."
echo "create database $PG_DATABASE;" | psql -h localhost -p $PG_PORT -U $PG_USER -W

echo "Restoring data to database..."
pg_restore --no-acl --no-owner -h localhost -p $PG_PORT -d $PG_DATABASE $FILE -U $PG_USER -W