How to setup a PostgreSQL database with Docker Compose
Tags: docker, postgres, tutorial
Published: 2023-09-22 14:07:59

When I started learning about databases, Docker didn't even exist yet. So I spent hours trying to make both the database engine and the workspace interface to work, and there are so many little details in the setup that I'm sure you'll find thousands of articles and videos teaching how to do it in your operating system.

But it's 2023 and containers are a common thing to developers, and honestly you don't even need to understand them to use them. All you need to know is how to install it (and for this, you can check the [Install Docker Engine tutorial from docker](https://docs.docker.com/engine/install/) themselves) and how to run it.

## Prerequisites

* Docker CLI
    
* Docker Compose
    
* a browser
    

It's supposed to be easy so that's all.

## Setting up

You'll need two files to make this work.

### docker-compose.yml

Docker Compose is a tool made to define and run multi-container applications. All you need to do is define your `services`, do some setups and run it.

```yaml
version: '3.8'
services:
    postgres:
        image: postgres:15
        environment:
            - POSTGRES_USER=postgres
            - POSTGRES_PASSWORD=password
        volumes:
            - postgres-db-volume:/var/lib/postgresql/data
        ports:
            - 5432:5432
        networks:
            - postgres-db-network
    pgadmin:
        image: dpage/pgadmin4
        environment:
            PGADMIN_DEFAULT_EMAIL: 'teste@teste.com'
            PGADMIN_DEFAULT_PASSWORD: 'teste'
        ports:
            - 16543:80
        volumes:
            - ./servers.json:/pgadmin4/servers.json
        networks:
            - postgres-db-network

volumes:
    postgres-db-volume:
        driver: local
        driver_opts:
            type: none
            o: bind
            device: ./data
networks:
    postgres-db-network:
        driver: bridge
```

Here we are using two containers:

* one for the database itself, which is based on the Postgres 15 image. We also define the port for the database, a *volume* and some environment variables to set the default user and password for our connection.
    
* the other is for pgAdmin, which is a Postgresql database administration tool. It is also open source, and this way we can manage and interact with our databases using the browser. You can see that we set the port to 16543. This means you'll access it by typing localhost:16543 in your browser. You can change this port, but I like to use a number that probably won't be used for anything else.
    

### servers.json

We don't actually need this file, because in pgAdmin you can use the interface to configure your connection (and set the host, port, user and password). But since I want to make this easier, we use this file to pass these options that will be loaded when our container starts.

```json
{
  "Servers": {
      "1": {
          "Name": "example",
          "Group": "Servers",
          "Host": "postgres",
          "Port": 5432,
          "MaintenanceDB": "postgres",
          "Username": "postgres",
          "PassFile": "/pgpass",
          "SSLMode": "prefer"
      }
  }
}
```

## Running it

With both of these files created, all you need to do is go to their directory and run

```sh
docker compose up
```

This will download the images and start both containers with everything as described in our docker-compose.yml, while logging everything in your terminal. If you don't want that, you can just run it detached with the `-d` flag

```sh
docker compose up -d
```

This way your containers will be running in background.

To stop the containers, you can use `Ctrl+C` in case it's running attached to your terminal session, or you can run

```sh
docker compose down
```

## Using it

While your containers are running, you'll go to `localhost:16543` in your browser and access the pgAdmin dashboard. The first time you open it you'll need to log in with the credentials we set up in our docker-compose file. If you didn't change it, it's `teste@teste.com` and the password is `teste`.

Once you're logged in, you can access the connections by clicking `Servers` at the menu on the right. The first time you open it, it will ask you for the password of the connection. This is also in the docker-compose, and in our example it is `password`.

![pgAdmin asking for password](https://cdn.hashnode.com/res/hashnode/image/upload/v1695390949301/438194dd-de50-49b0-8814-853fa787a371.png)

Once you are connected, you can right-click on Databases and create your own. In case you're not used to Postgresql, it has the concept of "schemas", and that's where you'll find your tables.

![pgAdmin interface after connected to the database](https://cdn.hashnode.com/res/hashnode/image/upload/v1695390954754/09897fc4-c3c2-4f57-b9fb-fb0a38ade11a.png)

After this point, you can just use it as you want. You can right-click your database and click on `Query Tool` to write and run your SQL scripts

![Context menu of the database, showing the "Query tool" option](https://cdn.hashnode.com/res/hashnode/image/upload/v1695391091557/8550bffc-fda8-4d39-826c-2341123fe384.png)

![pgAdmin query tool interface](https://cdn.hashnode.com/res/hashnode/image/upload/v1695391150663/e060a124-410a-4815-b3b0-d6fa36182730.png)

That's all, hope this helps someone and follow me for more content. If you like it, share it with someone you think might need this.