Use Postgres DB with Docker.

In this article i will show you how you can create a Postgres container, create tables if not exist and import data to the tables from csv files if the tables are empty.

Create the following file as docker-compose.yml

version: "3.8" 
services: 
  db: 
    container_name: postgres 
    image: postgres:14.1-alpine 
    restart: always 
    ports: 
      - 5432:5432 
    environment: 
      - POSTGRES_USER=postgres 
      - POSTGRES_PASSWORD=postgres 
      - POSTGRES_DB=shops_db 
    volumes: 
      - db:/var/lib/postgresql/data 
volumes: 
  db: 
    driver: local

This docker compose file will create a container that will use the postgres:14.1-alpine image. The database will be named shops_db which the username and password is postgres and will accept connections on port 5432 of the host. The database data will be stored in a Docker volume named db which will be mounted to the /var/lib/postgresql/data directory of the container.

To start the container we enter:

$ docker compose up

If everything goes fine you should see the following message on the console which means that postgres is ready.

database system is ready to accept connections

Creating Schemas when the container is starting

You can create schemas with the usual way: open a database administrator tool and create your schemas, but there is the docker way as well which is automated.

Create the following file as create_tables.sql

create table if not exists "pets" ( 
                   "name" varchar(255)  null default null, 
                   "age"  integer null default null);

Then modify the docker-compose.yml file to match these changes

version: "3.8" 
services: 
  db: 
    container_name: postgres 
    image: postgres:14.1-alpine 
    restart: always 
    ports: 
      - 5432:5432 
    environment: 
      - POSTGRES_USER=postgres 
      - POSTGRES_PASSWORD=postgres 
      - POSTGRES_DB=shops_db 
    volumes: 
      - db:/var/lib/postgresql/data 
      - ./create_tables.sql:/docker-entrypoint-initdb.d/create_tables.sql 
volumes: 
  db: 
    driver: local

On the volumes section of the file we added a map of the sql file we created to the /docker-entrypoint-initdb.d/ directory of the container, any file in this directory will be executed.

Running docker compose up should print in the in console the following message

postgres  | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/create_tables.sql 
postgres  | CREATE TABLE

This indicates that the create table command in the SQL file executed and created the table in our database.

Importing CSV files to the database

Like the previous example there are many ways to import a CSV to a table, but there is the Docker way as well, to do this we need to create a helper container that will be insert the CSV file to the table only when the Database is listening for connections and only if the table has a row count of zero.

Create the following file as pets.csv

name,age 
"spike","2" 
"rose","3"

Now modify docker-compose.yml to match the following

version: "3.8" 
services: 
  db: 
    container_name: postgres 
    image: postgres:14.1-alpine 
    restart: always 
    ports: 
      - 5432:5432 
    environment: 
      - POSTGRES_USER=postgres 
      - POSTGRES_PASSWORD=postgres 
      - POSTGRES_DB=shops_db 
    volumes: 
      - db:/var/lib/postgresql/data 
      - ./create_tables.sql:/docker-entrypoint-initdb.d/create_tables.sql 
  import_csv: 
    container_name: postgres_helper 
    image: postgres:14.1-alpine 
    command: 
    - sh 
    - -c 
    - | 
      while ! pg_isready -d shops_db -U postgres -h db -p 5432 -q -d shops_db -U postgres -h db -p 5432 -q; do echo 'postgres still not up' ; sleep 10; done 
      if [ $(psql postgres://postgres:postgres@db:5432/shops_db -tAc "SELECT COUNT(*) FROM pets") -eq 0 ] 
      then 
        psql postgres://postgres:postgres@db:5432/shops_db -c "\copy pets FROM '/var/lib/postgresql/csv/pets.csv' DELIMITER ',' CSV HEADER;" 
      fi 
    depends_on: 
      - db 
    volumes: 
      - ./pets.csv:/var/lib/postgresql/csv/pets.csv 
volumes: 
  db: 
    driver: local

We added another container named import_csv this container live only to import the csv files and will exit then. The command section of the file are actually shell commands executed on the import_csv container, the line staring with while uses the pg_isready tool and waits until the main container named db accepts for connections, then the if line queries the database if the pets table has zero rows, if yes then imports the csv file with the copy command. Note that we need also to mount the pets.csv file to the container in order to be accessible.

if we run docker compose up again we should see

postgres         | 2023-02-22 19:10:45.660 UTC [1] LOG:  database system is ready to accept connections 
postgres_helper  | COPY 2 
postgres_helper exited with code 0

The COPY 2 means that two lines imported from the csv

if we stop the container and re-run it will not do any imports or re-create the table because the table exist and have data, but if we stop the container with the -v parameter which deletes any used resources it will re-create them from the start.

Conclusion

In this article we saw how to create a Postgres container, create tables if not exist and import data to the tables from csv files if the tables are empty

Join Medium with my referral link - Konstantinos Patronas

As a Medium member, a portion of your membership fee goes to writers you read, and you get full access to every story…