How do I run a PostgreSQL container using docker-compose with startup files that depends on .env values, then running node-pg-migrate?

Question

I want the following flow.

• PostgreSQL runs startup script with the values from .env file
• PostgreSQL runs successfully
• App runs successfully
• Run migration commands. I have already created the scripts for migration commands. This is done using node-pg-migrate module, and I simply need to run npm run migrate up after the app runs successfully.

I have the following startup scripts for my PostgreSQL.

First file (./initdb.d/001_create_database.sql)

CREATE DATABASE ${PGDATABASE};

Second file (./initdb.d/002_create_user.sql)

CREATE USER ${PGUSER} WITH ENCRYPTED PASSWORD '${PGPASSWORD}';
GRANT ALL PRIVILEGES ON DATABASE ${PGDATABASE} TO ${PGUSER};
GRANT USAGE, CREATE ON SCHEMA public to ${PGUSER};

I have the following .env file.

#db config
PGUSER={something}
PGHOST={something}
PGPASSWORD={something}
PGDATABASE={something}
PGPORT={something}

I have the following Dockerfile for my Node.js app.

FROM node:14
RUN apt-get update && apt-get install -y gettext-base
RUN mkdir -p /usr/src/app
WORKDIR /usr/src/app
COPY package*.json ./
RUN npm install
COPY . .
RUN git clone https://github.com/vishnubob/wait-for-it.git
EXPOSE 5000
CMD [ "npm", "run", "start-dev" ]

Then, I have the following docker-compose.yml file.

version: "3.7"
services:
  app:
    build: .
    ports:
      - "${PORT}:${PORT}"
    env_file:
      - .env
    depends_on:
      - postgres
      - redis
      - rabbitmq
    restart: on-failure
  postgres:
    image: postgres:13
    env_file:
      - .env
    ports:
      - "${PGPORT}:${PGPORT}"
    volumes:
      - "./initdb.d:/docker-entrypoint-initdb.d"
      - musicapidata:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: root
      POSTGRES_DB: root
      POSTGRES_USER: root
  redis:
    ...
  rabbitmq:
    ...
  migration:
    build:
      context: .
    command:
      [
        "./wait-for-it/wait-for-it.sh",
        "postgres:5432",
        "--",
        "npm",
        "run",
        "migrate",
        "up"
      ]
    links:
      - postgres
    depends_on:
      - postgres
    env_file:
      - .env
volumes:
  musicapidata:

I have tried using envsubst in my Dockerfile, but it doesn't work as the PostgreSQL service will copy the SQL script files with the placeholder values. Could someone guide me on how to do this?

EDIT

I have tried the following as well. I think I'm close.

docker-compose.yml

  postgres:
    build:
      context: .
      args:
        - pgversion=13
      dockerfile: Dockerfile-postgres
    env_file:
      - .env
    ports:
      - "${PGPORT}:${PGPORT}"
    volumes:
      - "./initdb.d:/docker-entrypoint-initdb.d"
      - musicapidata:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: root
      POSTGRES_DB: root
      POSTGRES_USER: root

Dockerfile-postgres

ARG pgversion
FROM postgres:$pgversion

RUN apt-get update && apt-get install -y gettext-base
COPY initdb.d /docker-entrypoint-initdb.d
RUN chmod +x /docker-entrypoint-initdb.d/*
WORKDIR /docker-entrypoint-initdb.d
COPY .env ./

RUN envsubst < ./001_create_database.sql
RUN envsubst < ./002_create_user.sql

Unfortunately, it is copying the .env just right, but it si not running the envsubst yet.