postgres – docker

Last Update: 2/9/2025

heavy sigh… yes, it saddens me that I can go back half a year later and look at some of drivel I have posted and wonder. Sure in the moment I (hopefully) knew what I was posting and why. With the distance of time it is murky.

This post was ( I am sure ) going to go back and review creating a quick and easy docker container specifically to host postgress.

BEGIN the BEIGN.

Let’s review the creation of our docker-compose.yaml file.

I can tell this is relatively recent because we don’t see the older style “version” tag. Which was deprecated. For some reason I want to go on a rant right about here. Has anyone else noticed that a lot of docker material gets deprecated on a regular basis. VERSION tag deprecated. Docker build – Deprecated. Docker Compose – Deprecated. For reasons that are fairly flimsy. But I digress.

services:
  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: p@$$w0rd
    volumes:
      - pgdata:/var/lib/postgresql/data
    ports:
      - 5432:5432

volumes:
  pgdata:

Let’s review shall we? with this we create one service named “db”

The “image” comes from the image named “postgres”. At the time of of this postings (update) the source OS appears to b Debian “Bookworm”. Knowing what the source images is important because it tells a bit about the default dependencies and what you count on being present.

We sneak in a way to create the default username and password. (so yes please remember to change the most popular password on the planet (arguably).

There are two relatively competing images that can aid in the administration of postgres.

• admin
• pgadmin

  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

To administrate using a CLI utility you will need to get your first “handhold” into the system. Please remember that if you simply use “psql” this will be no bueno.

root@87bb27c51f2f:/# psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "root" does not exist

Instead – remember that gave yourself a way in you have only to use it.

psql -U admin

if you have to force set the postgres username/password

postgres=# ALTER USER postgres PASSWORD 'p@$$w0rd';

Random notes for creating a small table

If you have launched this you might benefit from some quick notes to create a small table.

CREATE TABLE assets (
    AssetID int,
    hostname varchar(255),
    osfamily varchar(255),
    ipaddress varchar(255),
    tag varchar(255)
);

inserting some basic info

INSERT INTO assets (AssetID, hostname, osfamily, ipaddress, tag)
VALUES(1,'NY-0001', 'NT_WORKSTATION','172.16.15.2','');

Using python to access postgres.

import pg8000.native
con = pg8000.native.Connection("postgres", password="p@$$w0rd")

for row in con.run("SELECT * FROM assets;"):
   print(row)

quick side note for when installing postgres client.

apt install postgresql-client-common
apt install postgresql-client

Quick Notes:

Just a reminder for when accessing the docker image

docker exec -it 28a /bin/bash

(in this instances we would have found 28a by doing a “docker ps”)