Setting Up A PostgreSQL Docker Sandbox

Motivation

Sandboxes are very handy for isolated testing and demonstration purposes. As it happens, I began to write a post that was going to use a PostgreSQL Docker container as a sandbox and I decided it was better to break it out into its own post that could be referred to in future posts rather than add complexity to a post where it wasn't the main focus.

Install Docker

For my docker host system I am using Debian 9 (Stretch). Installation was done following the official Get Docker CE for Debian instructions.

I won't repeat the commands here as those instructions will be updated appropriately as packages, dependencies and Debian distributions change.

However I will mention I'm not a fan of piping raw URL output directly into sudo commands as some of the official instructions indicate. Anytime the instructions call for it I would suggest instead dumping the output to a file and sanity checking it before feeding it to a command with elevated privileges.

One last installation note is that depending on your environment and requirements you may want to give specific users the ability to control docker. As a result of completing the instructions indicated above a new group 'docker' was added, adding users to this group is as easy as:

usermod -aG docker yourUsername

Afterwhich you'll need to re-login for the group changes to take effect (a simple 'su - yourUsername' on the command line you plan to run docker commands on suffices for the short-term)

Firing Up The Sandbox

Downloading and starting the PostgreSQL sandbox is as easy as:

docker run \
            --name pgsandbox1 \
            -d \
            -e "POSTGRES_PASSWORD=password" \
            -p 5432:5432 \
            postgres:10

This tells docker to:

Run a (ephemeral) container named 'pgsandbox1' based of the postgres:10 image tag
In detached mode (-d)
Setting the password for the PostgreSQL superuser to 'password' via a system environmental variable (-e)
And publishes (-p) port 5432 of the container on the host

Kicking Tires

First, we verify the container is running:

$ docker container list

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
3b445b6a2dc0 postgres:10 "docker-entrypoint.s…" 3 minutes ago Up 3 minutes 5432/tcp pgsandbox1

Next, we lauch the psql interactive shell

Method 1: Exec psql inside the running docker
This method does not require psql to be installed on the host system:

$ docker exec \
          -it \
          pgsandbox1 \
          psql -U postgres

psql (10.3 (Debian 10.3-1.pgdg90+1))
Type "help" for help.

postgres=#

Method 2: Connect via local psql to the published container port

$ psql \
     --host=127.0.0.1 \
     --port=5432 \
     --username=postgres

Password for user postgres: 

psql (10.3 (Debian 10.3-1.pgdg90+1))
Type "help" for help.

postgres=#

And as a final check we create a table, add a row to it and run a query:

postgres=# CREATE TABLE test (id SERIAL, name TEXT);
CREATE TABLE

postgres=# \d
             List of relations
 Schema |    Name     |   Type   |  Owner   
--------+-------------+----------+----------
 public | test        | table    | postgres
 public | test_id_seq | sequence | postgres
(2 rows)


postgres=# \d test
                            Table "public.test"
 Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | nextval('test_id_seq'::regclass)
 name   | text    |           |          | 


postgres=# INSERT INTO test (name) VALUES ('Pat');
INSERT 0 1

postgres=# SELECT * FROM test;
 id | name 
----+------
 1 | Pat
(1 row)

Important reminder: The docker container running above is ephemeral; it does not retain or persist data between container restarts.

Final Notes

In the end of this we have a minimalist PostgreSQL instance ready for simple testing and examples. There is obviously a lot of room for enhancements and additional functionality. For those interested in modifications to the simple use-case above I would recommend reading through the base postgres docker image page which includes guidance on how best to do so.