Experimenting with PostgreSQL in a Container

  • Worth mentioning the -v switch for the docker run command to get a persistent volume.

    -v $HOME/docker/volumes/postgres:/var/lib/postgresql/data

    It will be slightly different for Windows, the code switch above is on my works MacBook.

    • $HOME is a linux inbuilt variable that points to my profile directory.  On Windows it will be %HOMEPATH%
    • /docker/volumes/postgres is simply the path in my profile where the Postgres data files are stored.  It is outside the container, hence the persistence.
    • The : is the delimiter that separates out the location of data files on my host from the location of the data files within my docker container.

    If I connected to my container I would find the postgres data files in /var/lib/postresql/data/

    The other thing I have found useful is the --network switch for when I have a Python app in a docker container that needs to talk to PostGres in a docker container.

    docker network create pg_net
    docker run --rm --name pg-docker --network pg_net -d -p 5432:5432 -v $HOME/docker/volumes/postgres:/var/lib/postgresql/data postgres

    • --rm means remove the container when you terminate.  Otherwise the container just sits there in an EXITED state.
    • --name is useful when you might be running multiple containers and/or multiple instances of PostGres.
    • -d means run in detached mode.  without this you have to keep your command window open.

    One thing to watch out for is PostGres versions.  PostGres 13 has just been released.  I'd been working on my local container for a while on Version 12 and, following a reboot, my database became unavailable.  Turns out you PostGres13 cannot read PostGres12 data files without some messing around.  fortunately the DB scripts were all under source control so a rebuild from scratch took seconds.

  • Thanks,

    I decided not to persist the volume here, precisely because everything is in the VCS and I didn't anticpate this living long. The data isn't important here.

    I also did use the networking features because I wanted to run pgadmin in a container as well and need to connect.

  • I appreciate the overview. I hadn't considered pulling in a docker image to play around w/ postgresql, but that makes sense.

    I did want to note that the command to pull is for "postgres", not "postgresql".  Was trying to figure out what I was missing when it wouldn't pull.

    This will be helpful to play around with the engine without having a lot of extra overhead.

  • Thanks, I think I've gotten used to typing postgresql too often.

    container are my go to now, to avoid installing more software that might change behavior. I keep thinking I ought to resubscribe to Turbo and get GUI containers for apps, but that didn't work as well because some of these updates too often and the container is always behind.

     

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply