Create new PostgreSQL database for local development using Docker

Depending on the type of project, you might need a PostgreSQL database for your app's local development. There are a couple of options available from running the Postgres.app Mac app or through homebrew. But one of the easiest ones is to use Docker.

Docker command for starting PostgreSQL instance

Assuming you've got Docker Desktop installed, enter the following one-liner into the terminal:

$ docker run --name app-postgres \
             --env POSTGRES_USER=app_dev \
             --env POSTGRES_PASSWORD=e1bc9e7f864d \
             --publish 127.0.0.1:5432:5432 \
             --detach \
             --restart unless-stopped \
             postgres:13

Here's what the switches do:

we'll name the container app-postgres
ask PostgreSQL to name both the superuser account and the default database as app_dev
give the user account app_dev a password
we publish the container's 5432 port to the host machine, but only to the localhost interface 127.0.0.1 and not the whole network
we let the container run in the background with --detach
normally Docker containers won't last over machine restarts, but we specifically ask for it with --restart unless-stopped
we also specifically bolt down database version with postgres:13 to avoid major version release changing from under us

You've got `app_dev` database

In the PostgreSQL instance, you now have

a database named app_dev
a user called app_dev with superuser rights with password set to the one you provided
you can connect to it at localhost:5432

Run command in psql

You can hit the psql console and inspect the database contents:

$ docker exec -it app-postgres psql -U app_dev

Here's what the switches do:

use -it so docker will simulate a terminal, and we can send commands to psql
you don't need to enter the password since you're running the command inside the container, and PostgreSQL is set up liberally

Load schemas and database dumps

Now you're ready to load a database schema:

docker exec -i app-postgres psql -U app_dev < schema/schema.sql

This time we strip the -t argument since we're piping input data from a file.

Or, you can insert a whole database dump created with pg_dump:

bzcat dump_202010291148.bz2 | docker exec -i app-postgres psql -U app_dev

Semantic Versioning Cheatsheet

Learn the difference between caret (^) and tilde (~) in package.json.

Get Cheatsheet

Loading Comments