Blog

Victor is a full stack software engineer who loves travelling and building things. Most recently created Ewolo, a cross-platform workout logger.

    Dockerized posgresql with local data storage on Ubuntu

    Docker has given developers the ability to easily switch between project setups. In this tutorial, we will look at creating a docker postgresql container and use a local folder to store data within it. The tutorial is written for Ubuntu but the commands can just as easily be modified for other operating systems.

    Pre-requisites

    This article assumes that you have the following installed:

    Creating a docker container using a pre-built postgresql image is fairly straightforward. The tricky bit comes into play when we want to use a separate location to store the actual database data. Note that by default, postgresql stores it's data under /var/lib/postgresql/data for an ubuntu installation.

    We thus have 2 options as provided by docker.

    Use a local docker volume

    This is the recommended "docker" way where we first create a named volume via the following:

    docker volume create postgres-data  
    

    We then start our docker container and mount the created volume via the following command:

    docker run --name local-postgres9.6.7 -p 5432:5432 -e POSTGRES_PASSWORD=ppp --mount source=postgres-data,destination=/var/lib/postgresql/data postgres:9.6.7-alpine
    

    Note that in the above case the volume is most likely created under /var/lib/docker/volumes/postgres-data/ and the actual db contents would be found under /var/lib/docker/volumes/postgres-data/_data. This information can be verified by running the following commands and paying close attention to the Mounts section:

    docker volume inspect postgres-data
    docker inspect local-postgres9.6.7
    
    Using a local folder mount

    Another option of using a local folder is simply mounting the local folder into the desired location in the container. This can be achieved very easily via the following command:

    docker run --name local-postgres9.6.7 -p 5432:5432 -e POSTGRES_PASSWORD=usesomethingbetter --mount type=bind,source=/home/victor/npq/docker/postgres-data,target=/var/lib/postgresql/data postgres:9.6.7-alpine
    

    Note that after mounting the local folder, it's ownership gets changed to a different user and you would need to sudo chown -R user:group /home/victor/npq/docker/postgres-data to be able to access it again. The ownership always changes everytime the container is started.

    Connecting to the docker instance

    In the above commands we created a container with the following attributes:

    • name local-postgres9.6.7
    • expose port 5432
    • set environment variable (also the postgres user password) usesomethingbetter
    • mounted a volume/local folder to /var/lib/postgresql/data

    Connecting to the running database is thus as simple as:

    psql -h localhost -U postgres
    
    postgres=# CREATE ROLE dummy WITH CREATEDB LOGIN PASSWORD 'dummy';
    CREATE ROLE
    postgres=# CREATE DATABASE dummy;
    CREATE DATABASE
    postgres=# \du
                                        List of roles
      Role name |                         Attributes                         | Member of 
    -----------+------------------------------------------------------------+-----------
      dummy     | Create DB                                                  | {}
      postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
    
    postgres=# \q
    
    Container management

    Starting and stopping the container is as easy as:

    docker stop local-postgres9.6.7
    docker start local-postgres9.6.7 -a -i
    

    Voilá, we now have a throwaway postgres db which we can spin up depending on the project configuration and have the data also saved across multiple runs and even containers as required.

    Listing and deleting containers and volumes can be done via the following:

    docker container ls --all
    docker container prune
    docker volume ls
    docker volume prune
    
    References and further reading

    In the next article we will look at using docker compose to achieve something similar but with more services and applications all rolled up for a single project setup! As always please, feel free to get in touch for any questions/comments :).

    HackerNews submission / discussion

    Back to the article list.

    SmallData newsletter

    Subscribe to get articles as they are published direct to your inbox!