postgresql-pgadmin-docker-compose

🚀 This example provides a docker compose for using PostgreSQL and pgAdmin. 🚀

https://github.com/coding-to-music/postgresql-pgadmin-docker-compose

From / By https://github.com/ajeetraina/awesome-compose/tree/master/postgresql-pgadmin

Environment variables:

Before deploying this setup, you need to configure the following values in the [.env](.env) file.
- POSTGRES_USER
- POSTGRES_PW
- POSTGRES_DB (can be default value)
- PGADMIN_MAIL
- PGADMIN_PW

GitHub

git init
git add .
git remote remove origin
git commit -m "first commit"
git branch -M main
git remote add origin git@github.com:coding-to-music/postgresql-pgadmin-docker-compose.git
git push -u origin main

PostgreSQL and pgAdmin

This example provides a base setup for using PostgreSQL and pgAdmin. More details on how to customize the installation and the compose file can be found here (PostgreSQL) and here (pgAdmin).

Project structure:

.
├── .env
├── compose.yaml
└── README.md

compose.yaml

services:
  postgres:
    image: postgres:latest
    ...
  pgadmin:
    image: dpage/pgadmin4:latest

Configuration

.env

Before deploying this setup, you need to configure the following values in the .env file.

• POSTGRES_USER
• POSTGRES_PW
• POSTGRES_DB (can be default value)
• PGADMIN_MAIL
• PGADMIN_PW

Deploy with docker compose

When deploying this setup, the pgAdmin web interface will be available at port 5050 (e.g. http://localhost:5050).

$ docker compose up
Starting postgres ... done
Starting pgadmin ... done

Add postgres database to pgAdmin

After logging in with your credentials of the .env file, you can add your database to pgAdmin.

1. Right-click "Servers" in the top-left corner and select "Create" -> "Server..."
2. Name your connection
3. Change to the "Connection" tab and add the connection details:

• Hostname: "postgres" (this would normally be your IP address of the postgres database - however, docker can resolve this container ip by its name)
• Port: "5432"
• Maintenance Database: $POSTGRES_DB (see .env)
• Username: $POSTGRES_USER (see .env)
• Password: $POSTGRES_PW (see .env)

Expected result

Check containers are running:

$ docker ps
CONTAINER ID   IMAGE                           COMMAND                  CREATED             STATUS                 PORTS                                                                                  NAMES
849c5f48f784   postgres:latest                 "docker-entrypoint.s…"   9 minutes ago       Up 9 minutes           0.0.0.0:5432->5432/tcp, :::5432->5432/tcp                                              postgres
d3cde3b455ee   dpage/pgadmin4:latest           "/entrypoint.sh"         9 minutes ago       Up 9 minutes           443/tcp, 0.0.0.0:5050->80/tcp, :::5050->80/tcp                                         pgadmin

Stop the containers with

$ docker compose down
# To delete all data run:
$ docker compose down -v

To view a table in pgAdmin using the graphical interface, you can follow these steps:

Open pgAdmin and connect to the server that contains the database where the table is located.

• In the object browser on the left-hand side, navigate to the database that contains the table.
• Expand the database and then expand the "Schemas" folder to find the schema that contains the table.
• Expand the schema and click on the "Tables" folder to view a list of tables in that schema.
• Locate the table you want to view and right-click on it.
• Select "View/Edit Data" from the context menu.
• Choose "View Data" to view the data in the table.
• You can also directly execute a SQL query to view the table's data using the "Query Tool" in pgAdmin. To do this, simply right-click on the table and select "Query Tool" from the context menu. This will open a new tab where you can write and execute SQL queries against the table.

verify can connect from the command line

sudo apt install postgresql-client

connect via postgresql-client

psql -h localhost -p 5432 -U postgres

Try a command, always end with a semicolin;

CREATE TABLE IF NOT EXISTS mytable (
  id SERIAL PRIMARY KEY,
  datetime TIMESTAMP NOT NULL
);

verify

SELECT COUNT(*) FROM mytable;

Full example of connecting and executing commands

psql -h localhost -p 5432 -U postgres
Password for user postgres: 

Output

psql (12.14 (Ubuntu 12.14-0ubuntu0.20.04.1), server 15.2 (Debian 15.2-1.pgdg110+1))
WARNING: psql major version 12, server major version 15.
         Some psql features might not work.
Type "help" for help.

postgres=# CREATE TABLE IF NOT EXISTS mytable (
postgres(#   id SERIAL PRIMARY KEY,
postgres(#   datetime TIMESTAMP NOT NULL
postgres(# );
CREATE TABLE
postgres=# SELECT COUNT(*) FROM mytable;
 count 
-------
     0
(1 row)

Try the simulation

npm run simulation

Output

> postgresql-pgadmin-docker-compose@1.0.0 simulation
> node ./simulation.js

Number of rows before: 2
{
  id: '1679990154903',
  mystring: 'mykey_1679990154903',
  datetime: 2023-03-28T12:55:54.903Z
}
Number of rows after: 3
[
  { id: '1679990031918', datetime: 2023-03-28T12:53:51.918Z },
  { id: '1679990039882', datetime: 2023-03-28T12:53:59.882Z },
  { id: '1679990154903', datetime: 2023-03-28T12:55:54.903Z