A local PostgreSQL development environment based on Docker

Use the following steps to create the environment. For more information, please the article below the steps. :). (By the way, I use Ubuntu 20.04)

1. Create a directory data in any directory you want.
2. Create a Docker network using the following command.

docker network create learning-postgreSQL-network 

3. Create a container running a PostgreSQL server. The container will populate the directory data with necessary data.

docker run --rm --name postgres-server -v $(pwd)/data:/var/lib/postgresql/data --network learning-postgreSQL-network -e POSTGRES_PASSWORD=your-password postgres

4. You can connect to the above PostgreSQL server by the following command.

docker run --rm -it --network learning-postgreSQL-network postgres psql -U postgres -h postgres-server -W

5. Remove all containers when jobs are done.

6. Next time when you want to run a PostgreSQL server, you can skip the password.

docker run --rm --name postgres-server -v $(pwd)/data:/var/lib/postgresql/data --network learning-postgreSQL-network postgres

More than a decade ago, I took a database course. The textbook was Fundamentals of Database System (4th edition). (This book is still in my hand.) I then did not use database any more, because a part of my previous job was to write numerical modeling program in C++. Now I run my own company that has nothing to do with programming. Almost everything about database is left behind. However, to store sales record and to keep track of the item stock, I decide to familiarize myself with database and write my own specialized program.

From somewhere I read PostgreSQL is a powerful, open source database system. I therefore select PostgreSQL as the database system to play with. By combining the book, Fundamentals of Database System, with the online resource of PostgreSQL, I think it is a good start of the journey to learn the database system.

First of the first is to set up a local environment for running PostgreSQL. I try to keep my computer as slim as possible. Therefore, I use Docker to implement the local developing environment.

Before actually doing anything, it is a good habit to write down the requirement. Here is the requirement.

1. A container running a PostgreSQL server.
2. A client to connect to the PostgreSQL server. The client I want to use is psql. psql is also run in a container.
3. Data has to be stored locally of course. Any change must be kept in local file system.

First I need a container running PostgreSQL server. It can be easily done by following command:

docker run --rm --name postgres-server -e POSTGRES_PASSWORD=your-password postgres

Notice the -e option. This sets the password for the PostgreSQL server. You have to remember the password to connect to the server.

Now I have a running PostgreSQL server. The problem then is to find a way to connect to the server. psql is already installed in the official PostgreSQL image. Several steps must be done to connect to another container from within a PostgreSQL container by psql. Before proceed, remove the container just created.

Create a network in Docker where containers can be attached to. Containers in the network can communicate with each other.

Create this network by the following command:

docker network create learning-postgreSQL-network

This network will exist until being explicitly removed. To attach a container which runs a PostgreSQL server to the network, use the following command:

docker run --rm --name postgres-server --network learning-postgreSQL-network -e POSTGRES_PASSWORD=your-password postgres

Notice that in the above command a name postgre-server is given to the container

Good. Now you have a running PostgreSQL server that can be connected by other containers in the same network.

Time to come up with a way to connect to the running PostgreSQL server by psql. It is necessary to read the document of psql. The simplest way to connect to the running PostgreSQL by a psql is to use -U, -W and -h options.

• -U designates the user name
• -h specifies the hostname
• -W force psql to prompt for a password before connecting to a database

To run psql within a container, use the following command:

docker run --rm -it --network learning-postgreSQL-network postgres psql -U postgres -h postgres-server -W

Now you can manipulate the database as you want by using psql.

One last thing

Everything I do to the database must be kept. However until now everything written to the database will vanish into the air, if the container is stopped and then automatically removed.

To store changes written to a container is to use the volume. The document of PostgreSQL image indicate the mount point is /var/lib/postgresql/data. To keep a local copy of the database, just mount a local directory to /var/lib/postgresql/data of the container.

To accomplish this is easy. Create a local directory, say data, and then use the following command to run a postgreSQL server. (The previous PostgreSQL server container should be deleted first as well.)

docker run --rm --name postgres-server -v $(pwd)/data:/var/lib/postgresql/data --network learning-postgreSQL-network -e POSTGRES_PASSWORD=your-password postgres

Another little thing

After the above command is run, the directory data is populated with necessary data including the password. Next time the password is no longer needed to run a PostgreSQL container. The environment variable POSTGRES_PASSWORD can be ignored. Here is the command after the directory data is populated:

docker run --rm --name postgres-server -v $(pwd)/data:/var/lib/postgresql/data --network learning-postgreSQL-network postgres

Summary

Here I explain the way to run PostgreSQL like a standalone program. The way to run psql as a standalone program is also provided. Every change made to the database cab be locally stored as well.

OK, that is it. Happy coding.