Run PostgreSQL Database With Docker

Running PostgreSQL with docker is very useful for testing purposes. You can run it everywhere. You can create any testing data, use it then destroy it after testing is done. Integration testing and automation testing are made easy by it.

How to run PostgreSQL with Docker

To run Postgres with Docker, we can use the postgres Docker official image. We can use this command to run it.

docker run -d \
    -e POSTGRES_PASSWORD=mypassword \
    -e POSTGRES_USER=myuser \
    -p 5432:5432 \
    --name mypostgres postgres

The parameter POSTGRES_PASSWORD is mandatory. It defines the password for the user in the POSTGRES_USER parameter. The parameter POSTGRES_USER is optional. If it is not defined, the default user of postgres will be used. You can use the user and password to access the database. The parameter -p 5432:5432 is to publish container port 5432 to host port 5432, which is the default port of Postgres.

Now, your Postgres should already be running in docker. To see it, you can use the command docker ps or docker container ls.

You can try accessing the database using psql psql -h localhost -p 5432 -U myuser. The database created is empty. If you want to initialize the database in the container, you can put SQL files in directory /docker-entrypoint-initdb.d/ in the Docker image.

Run with data initialization

The files that are in directory /docker-entrypoint-initdb.d/ will be executed when the container start. The files can be *.sql, *.sql.gz, or *.sh. We will use the SQL below to initialize our database. We saved the SQL as a file in a directory, then bind the directory where the file is to directory /docker-entrypoint-initdb.d/ in the container.

CREATE DATABASE mydb;

\c mydb;

CREATE TABLE city (
  id serial PRIMARY KEY,
  name VARCHAR
);

INSERT INTO city (name) VALUES ('Jakarta');
INSERT INTO city (name) VALUES ('Bandung');
INSERT INTO city (name) VALUES ('Bandar Lampung');

The SQL is to create a database, connect to it, create a table, then insert some data into the table.

When running the container, we add -v parameter to bind the initialization directory. See the command below.

1
2
3
4
5
6
docker run -d \
    -e POSTGRES_PASSWORD=mypassword \
    -e POSTGRES_USER=myuser \
    -p 5432:5432 \
    -v /your/directory/initdb:/docker-entrypoint-initdb.d \
    --name mypostgres postgres 

You need to adjust the source directory to your source path.
Now because we already created a database in the PostgreSQL container, we can directly connect to the database.

psql -h localhost -p 5432 -U myuser mydb

Try to select the data.

We can see that the data has been initialized.

Persisting data

We know that the data in PostgreSQL in Docker is gone when the container is removed. But if you want to persist the data, we can create a docker volume and bind it to /var/lib/postgresql/data directory.
Use this command to create docker volume.

docker volume create postgresdata

We can see the volume created using the command docker volume ls.

We need to bind it to make PostgreSQL data persistent.

1
2
3
4
5
6
7
docker run -d \
    -e POSTGRES_PASSWORD=mypassword \
    -e POSTGRES_USER=myuser \
    -p 5432:5432 \
    -v postgresdata:/var/lib/postgresql/data \
    -v ${PWD}/initdb:/docker-entrypoint-initdb.d \
    --name mypostgres postgres 

Try modifying the data then remove the container and run it again. You will see that the data persist.

Conclusion

We can run PostgreSQL easily with Docker. It is very helpful for development and testing. We can create scripts to initialize our data. We can also persist the data of PostgreSQL if we want.

How to run PostgreSQL with Docker

Run with data initialization

Persisting data

Conclusion

See also