DockerでPostgreSQLコンテナを作成

docker-compose.yaml

version: "3"
services:
  sample-postgres:
    image: postgres:9.6

    # Queryログを出力する場合
    command: postgres -c log_destination=stderr -c log_statement=all -c log_connections=on -c log_disconnections=on
    logging:
      options:
        max-size: "10k"
        max-file: "5"
    # Queryログを出力する場合

    container_name: sample-postgres
    env_file:
      - .env
    ports:
      - "5432:5432"
    volumes:
      - database:/var/lib/postgresql/data
      - ./initdb:/docker-entrypoint-initdb.d

volumes:
  database:
    driver: local

.env

POSTGRES_USER=postgres
POSTGRES_PASSWORD=password
POSTGRES_DB=sample_db

Up

$ docker-compose up -d --build

Sign in to db

$ sudo docker exec -it sample-postgres /bin/bash -c \
    'psql -U postgres -W -d sample_db'
Password for user postgres:
psql (9.6.22)
Type "help" for help.

外部から

$ psql -U postgres -h example.com -W -d sample_db

pg_hba.conf

$ docker exec -it sample-postgres /bin/bash -c 'ls -l /var/lib/postgresql/data/pg_hba.conf'
-rw------- 1 postgres postgres 4490 Jun  7 14:15 /var/lib/postgresql/data/pg_hba.conf

$ docker exec -it sample-postgres /bin/bash -c 'cat /var/lib/postgresql/data/pg_hba.conf'

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
# local   replication     postgres                                trust
# host    replication     postgres        127.0.0.1/32            trust
# host    replication     postgres        ::1/128                 trust

host all all all md5

Databases

sample_db=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 sample_db | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

Schemas

sample_db=# \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)

Roles

sample_db=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Search path

sample_db=# show search_path;
   search_path
-----------------
 "$user", public
(1 row)

Create table

create table users
(
    id SERIAL NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

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

sample_db=# \d users;
                                     Table "public.users"
   Column   |            Type             |                     Modifiers
------------+-----------------------------+----------------------------------------------------
 id         | integer                     | not null default nextval('users_id_seq'::regclass)
 name       | character varying(255)      | not null
 created_at | timestamp without time zone |
 updated_at | timestamp without time zone |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)