Setting up Pre-configured PostgreSQL DB w/ pgAdmin4 in Docker

This docker-compose file spins up a PostgreSQL database along with an instance of PgAdmin4 which can be accessed from a browser

HOW TO RUN

Defining custom dump files

The files in the sql directory are called dump files.

• Each file reflects a different SQL database
• The init.sh file goes through each file, creates the corresponding database in PostgreSQL, and runs the contents of that SQL file against the corresponding database

[! NOTE] Initial SQL files in this repo were generated by ChatGPT

Create .env file

Rename the .env-example file to .env and update the username and password credentials

Spinning Up the Docker Compose File + Initializing / Seeding Databases via init.sh

1. cd into directory containing init.sh file
2. Make sure that the init.sh file is executable. If it is not, use chmod +x init.sh
3. Execute the init.sh file

./init.sh

init.sh does the following things:

• Loads .env file to gain access to environmental variables (needed by the init.sh file itself)
• Initializes docker-compose.yaml file via docker compose up -d
• Creates PostgreSQL databases and loads into them the dump files as defined by .sql files in the sql directory
  • Each database name is the same as the sql file name

Spin Down the Docker Compose File

docker compose down -v

NOTE: -v option removes any created volumes (while optional, it is recommended)

Access postgres db from cli

docker exec -it <container name> bash
psql -U <POSTGRES_USER> <POSTGRES_DB>

1st step accesses bash shell within the docker db container 2nd step accesses psql db from within the terminal

NOTE: <POSTGRES_USER> and <POSTGRES_DB> are specified by the .env file NOTE: To exit the bash cli, use CTRL + d

Access pgAdmin4 using web browser

1. Navigate to http://localhost:<PGADMIN_PORT> (from .env file)
2. You will be prompted to login with username and password. Use .env credentials <PGADMIN_DEFAULT_EMAIL> AND <PGADMIN_DEFAULT_PASSWORD>
3. Add a "Name" to your database under the "General" tab
4. Click on "Add New Server" in pgAdmin homepage
5. In the "Connection" tab, enter the following information: a. Host name/address -- db (The name of database service defined in docker-compose) b. Port -- <DB_PORT> (5432 is the default PostgreSQL port number) c. Maintenance database -- <POSTGRES_DB>(The name of the database specified in .env file) d. Username -- <POSTGRES_USER> e. Password -- <POSTGRES_PASSWORD>
6. Click "Save" to save the server configuration
7. Your PostgreSQL server should now appear under the "Servers" section in homepage
8. Click on the server name to expand the database tree and view your databases, tables, etc.

References

• Awesome Docker Compose - PostgreSQL + pgadmin

NOTE: The sql files in this starter template were generated by ChatGPT