Experimenting with PostgreSQL in a Container

Question

Experimenting with PostgreSQL in a Container

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
October 6, 2020 at 12:00 am

#3791742

Comments posted to this topic are about the item Experimenting with PostgreSQL in a Container

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

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

David.Poole SSC Guru Points: 76000 More actions · Answer 1

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.

LinkedIn Profile

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

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.

Peter Schott SSCrazy Eights Points: 9675 More actions · Answer 3

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.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

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.