Add PostgreSQL Container

PostgreSQL

https://www.postgresql.org

infra/docker/php/Dockerfile

  apt-get -y install ... libpq-dev && \
  ...
  docker-php-ext-install ... pdo_pgsql && \

• Add libpq-dev to apt-get -y install
  • https://packages.debian.org/ja/sid/libpq-dev
• Add pdo_pgsql to docker-php-ext-install
  • https://www.php.net/manual/en/ref.pdo-pgsql.php

Build app service & restart

$ docker compose build app
$ docker compose down -v
$ docker compose up -d

compose.yml

• https://hub.docker.com/_/postgres

volumes:
  db-store:

services:
  db:
    image: postgres:15
    ports:
      - target: 5432
        published: ${DB_PORT:-5432}
        protocol: tcp
        mode: host
    volumes:
      - type: volume
        source: db-store
        target: /var/lib/postgresql/data
        volume:
          nocopy: true
    environment:
      - POSTGRES_DB=${DB_NAME:-laravel_local}
      - POSTGRES_USER=${DB_USER:-phper}
      - POSTGRES_PASSWORD=${DB_PASS:-secret}
      - PGPASSWORD=${DB_PASS:-secret}
      - TZ=UTC

Execute migrate

$ docker compose exec app bash
$ php artisan migrate

Show character encode & timezone

$ docker compose exec db bash
$ psql -U $POSTGRES_USER $POSTGRES_DB

=# SELECT character_set_name FROM information_schema.character_sets;
 character_set_name 
--------------------
 UTF8
(1 row)

=# SHOW timezone;
 TimeZone 
----------
 UTC
(1 row)

Show table list

=# \d
               List of relations
 Schema |        Name        |   Type   | Owner 
--------+--------------------+----------+-------
 public | failed_jobs        | table    | phper
 public | failed_jobs_id_seq | sequence | phper
 public | migrations         | table    | phper
 public | migrations_id_seq  | sequence | phper
 public | password_resets    | table    | phper
 public | users              | table    | phper
 public | users_id_seq       | sequence | phper
(7 rows)

Add psql command to Makefile

psql:
	docker compose exec db bash -c 'psql -U $$POSTGRES_USER $$POSTGRES_DB'

$ make psql