Postgres Docker Image Setup

Intro

• This repository is intended to help users learn postgresql
  • It also has the added benefit of learning some docker!
• It outlines how to setup a dockerized container with postgres 16
• By default it will create a database called "dvdrental"
  • This is a sample database from Neon
    • https://neon.com/postgresql/postgresql-getting-started/postgresql-sample-database
  • It will run a restore script on initialization of the image to create it
• Additionally there will be a database called "test_db"
  • The purpose of this database will be to show some examples of creating a new database from scratch
    • Will show creating a new "user" to access the DB
    • Creating a schema
    • Creating a table
    • Inserting some test data
  • This will all be done via a set of setup scripts
• Other topics to discuss
  • Explanation of the Dockerfile and docker compose file
  • Highlighting what happens when you build the image
  • Persisting data in the container vs not persisting
  • Adding new scripts on initialization
    • How to clear / bring down the image so it is rebuilt properly
  • How to use the psql utility
    • Would be used to just query (if you didn't want to use a GUI)
    • Will be used to run sql files that you can place into the container
      • This would cover wanting to run additional scripts without having to rebuild your image
• Roles, Users, and Permissions
  • This local image will not be an accurate representation of how to do permissions
    • In this instance we will have a "generic_user" who has access to entire dbs (rather than using a superuser i.e. using the "postgres" user)
    • You will need to figure out how you want to do permissions for each application / db that you intend to setup, if you need to move it to production

---

Colima and Docker Install Steps

• To Be Completed

---

Important Notes / Paths (Inside Docker Container)

• /usr/lib/postgresql/16/bin
  • Contains path to binaries for things like pg_restore, pg_dump, initdb, psql
• /var/lib/postgresql
  • Contains the data directory (where all the physical storage of databases are)
• Getting into a shell inside your container
  • docker exec -it container_id /bin/bash
    • Then you can then the following to get into psql tool into a specific database
      • psql -U user_name -d database_name

---

Dockerfile, Docker Compose File, and Setup Shell Script

• Dockerfile
  • This file is needed to determine our base image we will copy from, setup directories / transfer data, and copy over any setup scripts to be run when the image is built

      # This sets the base image we will be using, which is provided by postgres for version 16
      FROM postgres:16.0
  
      # Create a directory that will be a bind mount
      # This directory will be used to hold "shared" files between your local machine and container
      # You will be able to edit / drop files on your local machine that you can then later execute
      # inside of your container
      RUN mkdir -p /home/shared_dir
  
      # Add permissions on directory
      # This ensures that you will be able to execute / read / write the items in our bind mount
      # inside of the container
      RUN chmod -R ugo+rwx /home/shared_dir
  
      # Create a directory to house the dvdrental db data
      # This directory will be used to help restore the sample database from neon
      RUN mkdir -p /home/dvdrental
  
      # Copy the sample data over
      COPY postgres_sample_db/dvdrental /home/dvdrental
  
      # Add permissions on directory
      RUN chmod -R ugo+rwx /home/dvdrental
  
      # Copy setup scripts into the entry point db (will be a shell script)
      # Any scripts inside of our "setup_scripts" folder will be executed when your docker image is built
      COPY setup_scripts /docker-entrypoint-initdb.d/
  

• docker-compose.yaml
  • This file is needed as we will be using the "docker-compose" command to build our image and run our container
  • It will determine the port we run this machine on, setup a named volume, setup a bind mount, and setup needed environment variables

       services:
       example_db:
           build: ./ # This tells us to build based off the image in this directory (our DockerFile)
           restart: always
           ports:
           - "5432:5432"
           environment:
           # These are needed environment variables for the docker image to initialize our db
           # These will all come from a .env file that you must have in the same directory as this
           # compose file, typically all 3 env variables are set to "postgres"
           # This will be the super user name, super user password, and super user maintenance db
           # I would not recommend changing these to anything other than "postgres" unless it is the password
           - POSTGRES_USER=${POSTGRES_USER}
           - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
           - POSTGRES_DB=${POSTGRES_DB}
    
           volumes:
           # This first line sets up a named volume, which allows you to persist data inside your databases on this cluster
           # If you want all of the data to be deleted when you bring down a container then comment out this line
           # As well the two lines at the bottom of the yaml noted #COMMENT_HERE
           - example_db_vlm:/var/lib/postgresql/data
           # The line directly below this is to setup a bind mount. What this is, is a shared directory between your container and 
           # your local machine. Anything put into here will be shared across both. This means that while your container is running
           # You can add items into this directory on your local (which you can then later execute - which I will highlight later)
           - ./shared_dir:/home/shared_dir
    
       #COMMENT_HERE (read above for context)
       volumes:
         example_db_vlm:

• The .env file
  • This must be placed inside the same directory as your "Dockerfile" and "docker-compose.yaml" file
  • It should look like the following:

      POSTGRES_USER=user_name # typically is postgres
      POSTGRES_DB=db_name # typically id postgres
      POSTGRES_PASSWORD=my_password # typically is postgres
      GENERIC_USER_PD=some_password # i use hello_world

• The setup_scripts directory
  • Typically how I do this is to place one shell script inside of here (00001.sh)

      #!/bin/bash
      set -e 
    
      ## Create a user to own both the dvdrental and test_db databases
      psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
          CREATE USER generic_user WITH PASSWORD 'hello_world';
      EOSQL
    
      ######### Setup of postgres official sample database and our "test_db"
      psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
          CREATE DATABASE dvdrental OWNER generic_user;
          CREATE DATABASE test_db OWNER generic_user;
      EOSQL
    
      # Run the restore script this will create all the objects in the dvdrental database
      psql -U "$POSTGRES_USER" -d dvdrental --echo-all --file=/home/dvdrental/restore.sql
    
      # Now we are going to run the script to create the objects in our database "test_db"
      # This script contains creating a schema and a table, and then inserting records to the table
      psql -U generic_user -d test_db --echo-all --file=/home/shared_dir/test_db_setup.sql

  • If you need to add a new db / more sql files to execute you would edit this bash script
    • For any new database, add another CREATE DATABASE command (can be done next to the existing ones)
    • For any new objects / scripts to run
      • Make sure that you put the scripts inside the "shared_dir" folder (which contains the bind mount)
      • Then add another line at the bottom with the following format
        • psql -U user_name -d database_name --echo-all --file=/home/shared_dir/file_path_to_sql_file.sql

---

How To Build The Image & Tear It Down

• Traverse to the directory that contains the "docker-compose.yaml" file
  • Proceed to run docker-compose up
  • After the scripts run and the image is built it will build a container
    • From here you can now connect in dbeaver / pgadmin / favorite tool
      • The following arguments will stay constant across this:
        • Host = localhost
        • Port = 5432
      • With regards to a "USER" to login with, you have two options
        • postgres
          • This is the super user and can access everything in your cluster (wouldn't recommend using if you can avoid)
          • Use the password from your .env file
        • generic_user
          • Use the password from your .env file / use "hello_world"
• Bringing down the container
  • If you simply want to bring down the container and stop it from running
    • Run docker-compose down
    • You can then later bring the container back up with docker-compose up
• If you want to rebuild the image
  • A potential reason to rebuild the image would be that you have new setup scripts that you want to test running from the getgo
    • Or perhaps you want to just wipe all the data inside the database
  • To do this run docker-compose down
    • Then proceed to run the following if you enabled the volume example_db_vlm
      • docker volume ls
        • You will then see a volume that is named something like this "setup_db_demo_exaple_db_vlm"
        • Pick that name and then run docker volume rm that_volume_name
      • Then run docker image ls
        • You should see an image named "setup_db_demo-example_db"
        • Proceed to run the following docker volume rm image_name
    • Lastly to rebuild you will want to run docker-compose up again

---

Accesing The psql Utility Inside Of The Container

• There are multiple reasons you may need to access a shell and the psql utility inside of your container
  • You may just want to query items adhoc without going into an editor
  • You may want to run sql files to add new objects into the db without having to bring down the image
  • Etc. Etc. Etc. Other Reasons
• The first thing you are going to want to do, is make sure you have a container running (i.e. docker-compose up from before)
  • Next you will want to run the command docker container ls
    • This will show your active running containers (ps. doing docker container ls -a will show all containers even those that are stopped)
    • You will see the container ID for your particular image, copy that container id
• Now you will want to run the following to get into a shell inside your container
  • docker exec -it container_id /bin/bash
    • Then you can then the following to get into psql tool into a specific database
      • psql -U user_name -d database_name
  • You can also run SQL files with the following syntax
    • psql -U user_name -d database_name -f /filepath/

---

Creating A Database Backup & Restoring

• Where and when this might be useful
  • If you want to export the current snapshot of your database for someone else to load in their dockerized image or even export to a real postgres server
  • If you want to export from a real postgres server what your database looks like and pull it into your dockerized image
• Creating your backup
  • Preferably you should be in the shell connected as the postgres user (as in the OS user postgres)
  • Run the following pg_dump -U postgres -d database_name -F tar -f file_backup_name.tar
• To load a backup
  • First go and create the database in your cluster
    • In psql tool would be CREATE DATABASE database_name OWNER owner_name;
  • Then run the following: pg_restore -U postgres -d database_name file_backup_name.tar
    • Proceed to connect to that database, and check that your tables / schemas / objects exist

---