Connect PostgreSQL in Docker from Your Local Host
Tags: docker, postgres, container, database
Published: 2020-12-22 13:28:16

---
title: Connect PostgreSQL in Docker from Your Local Host
published: true
date: 2020-10-16 00:00:00 UTC
tags: docker, postgresql, container, database
 
canonical_url: https://tech.serhatteker.com/post/2020-09/connect-postgres-docker-in-local-host/
---

## Prerequisites

- Docker
- Docker Compose (Optional)

If you didn't install them already look at these links:

- [Install Docker]
- [Install Docker Compose]

You can check them via:

```bash
$ docker -v
Docker version 19.03.13, build 4484c46d9d

$ docker-compose -v
docker-compose version 1.23.2, build 1110ad01
```

## Build Container

Execute below command to create a PostgreSQL container:

```bash
$ docker run -d -p 5432:5432 --name localpostgres -e POSTGRES_PASSWORD=secretpassword postgres:11.6
```

This command will download __Postgres 11.6__ from [Docker Postgre Hub].

If we look in detail:

- `-d` : means run in _detached_ mode,
- `--name` : we give container a name : _localpostgres_,
- `-e` : environment variable;
- `postgres:11.6` : is our main _image_ that we create the container upon

>**INFO**
> 
> I use PostgreSQL version __11.6__ since I have it already but you can use whatever version you want.
> 
> Also if you leave __tag__ _blank_  docker will fetch the __latest__ version.
> 
> However I definitely don't recommend that since it may break your _software stack_.



## Get Into the Container

After creating the container we can access it on our host:

```bash
$ docker exec -it localpostgres bash

root@6542951f176a:/# psql -U postgres
psql (11.6 (Debian 11.6-1.pgdg90+1))
Type "help" for help.

postgres=#
```

## Use the Container

__Create a Database__

We will create a database inside our PostgreSQL container:

```bash
root@cb9222b1f718:/# psql -U postgres
psql (11.6 (Debian 11.6-1.pgdg90+1))
Type "help" for help.

postgres=# CREATE DATABASE testdb;
CREATE DATABASE
postgres=#
```

__Create a Table__

Let's create an example table inside our _testdb_:

```bash
postgres=# CREATE TABLE accounts (
 user_id serial PRIMARY KEY,
 username VARCHAR ( 50 ) UNIQUE NOT NULL,
 password VARCHAR ( 50 ) NOT NULL,
 email VARCHAR ( 255 ) UNIQUE NOT NULL,
 created_on TIMESTAMP NOT NULL,
        last_login TIMESTAMP
);
CREATE TABLE
```

__Check Table/Relations__

To check your table:

```bash
postgres=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | accounts | table | postgres
(1 row)

postgres=#
```


## Use Docker Compose

Instead of raw docker command we can use __docker compose__ to create and access to our PostgreSQL database.

Create a file named `docker-compose.yml` with below code:

```yaml
version: '3.5'

volumes:
  local_postgres_data: {}

services:
  postgres:
    build: ./
    image: postgres:11.6
    container_name: localpostgres
    environment:
      POSTGRES_DB: ${POSTGRES_DB:-testdb}
      POSTGRES_USER: ${POSTGRES_USER:-debug}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-debug}
      POSTGRES_PORT: ${POSTGRES_PORT:-5432}
      POSTGRES_HOST: ${POSTGRES_HOST:-postgres}
    volumes:
      - local_postgres_data:/var/lib/postgresql/data
    ports:
      - "127.0.0.1:5432:5432"
```

Spin up our container:

```bash
$ docker-compose up -d

Creating localpostgres ... done
```

This command builds, (re)creates, starts, and attaches to containers for a service.

Now we can directly connect the database -`testdb`, with the user -`debug`:

```bash
$ docker exec it localpostgres psql -U debug -d testdb

psql (11.6 (Debian 11.6-1.pgdg90+1))
Type "help" for help.

testdb=#
```

All done!




[Install Docker]: https://docs.docker.com/get-docker/
[Install Docker Compose]: https://docs.docker.com/compose/install/
[Docker Postgre Hub]: https://hub.docker.com/_/postgres