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.
To begin, we need to install
psql as we are going to do this using the CLI. We will also be using
pg_restore which often come installed with
# 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
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:
- Port (default):
To populate the database from the dump file, we first need to create an actual database. In this case, I will just name it
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.
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