PostgreSQL Tutorial with Docker

Setting up a database with Docker is the quickest way to have a programming environment ready to start coding on a home PC, without performing installations that leave file remnants and configurations scattered throughout the hard drive, greatly simplifying development and production environments.

And how is it done? Well, in short, Docker is an open-source platform in the form of a virtual machine that manages and runs containers that package applications. What? Let's see the detail…

What is Docker

Docker is a service for application virtualization within containers. This allows applications to run independently of the host that houses them. When an application is virtualized, its launch is managed by orchestrators that monitor the state of the applications and the load they support. They can easily start or shut down instances in the cloud to handle high workloads.

Docker is available at no cost, for the main desktop platforms, and is one of the basic tools for every modern programmer. If you want to expand your knowledge about its usage, check out our Docker tutorial.

Requirements for Postgres with Docker

If you are a Windows or Mac user, you will need to have Docker Desktop installed on your PC or laptop, and it must have a certain capacity and power. On the other hand, if you are a Windows user, you will need the Professional version to install Docker. If not, visit the ubuntu installation tutorial, the best operating system for programming, totally free and Open Source. If you are a Ubuntu or Debian user, make sure to have Docker Compose in your system, also explained in the Docker installation tutorial.

Install Postgres with Docker

To start a Postgres database with Docker, the process couldn't be simpler. Just take the template of the Docker Compose configuration file that we provide below and invoke the command that will perform the entire process.

version: '2'
services:
    postgres:
        image: 'postgres:latest'
        restart: always
        volumes:
        - './postgres_data:/var/lib/postgresql/data'
        environment:
        - POSTGRES_PASSWORD=secure_pass_here
        ports:
        - '5432:5432'

Save the file locally with the name docker-compose.yml in a directory named <homeusuario>/docker/postgres if you are on Unix, or \docker\postgres if you are on Windows. The configuration **saves the data in the ./postgresdata** permanently because otherwise, each time the container restarts, the hosted databases would be lost. Then, navigate to the same directory as the file using a command terminal and execute:

docker-compose up -d

The system will first download the image from the public Docker repositories when it's not available locally and then proceed to start up and open the port indicated in the configuration file. The -d option instructs Docker to run the service in the background. From that point on, use your preferred graphical interface to connect to the database. If you don't have one, you can get the free and multi-database client DBeaver.

Verify that the PostgreSQL service is active

To verify that the service is active and the container is running normally, try running the following docker command that displays the running containers:

docker ps

At least one entry corresponding to the MySQL container in an active state should appear, as shown below:

CONTAINER ID  IMAGE        COMMAND                  CREATED        STATUS       PORTS                   NAMES
327fd50443a7  postgres:8   "docker-entrypoint.s…"   29 hours ago   Up 6 hours   0.0.0.0:5432->5432/tcp  docker_postgres_1

If so, you can proceed with the rest of the activities, as indicated in the previous text fragment, the container is running (status up) and listening on port 5432.

Connect PostgreSQL with DBeaver

If you have the SQL client DBeaver, once the container is started, you can connect to it with this multi-platform client by entering the following data:

Seleccionar nueva conexión para PostgreSQL
Server host: localhost
Port: 5432
User name: postgres
Password: secure_pass_here

Then, click on "Test Connection", and if everything went well, click on "Finish". From that moment on, the SQL graphical client will be able to establish a connection with the container as long as it is running.

If you don't have much experience in managing databases like PostgreSQL, we recommend you read the SQL tutorial for beginners.

Create PostgreSQL user

Creating a user, other than the system administrator (postgres), is an important step because if we make a mistake executing SQL statements or if the application behaves unexpectedly, it should not affect beyond the scope of this user. Therefore, it is usually recommended to create a user for each database managed by the PostgreSQL database management system. This way, all applications can operate independently without interfering with each other. This approach is highly recommended.

To create a user in PostgreSQL, we need to know the username, password, and the IP from which their access will be allowed (or if it should be open from any address). Once we have these parameters clear, we will open a connection using our preferred method and execute the following statement:

create user pepito with encrypted password 'pepitoPass';

The username and password should be changed to the corresponding ones in each case.

Change password in PostgreSQL

To change the password of a user in PostgreSQL:

ALTER USER pepito WITH PASSWORD 'securePepitoPass';

Delete a user in PostgreSQL

Deleting a user cannot be undone; however, if a mistake is made, the same user can be recreated. By doing so, neither the database nor the permissions it had would be affected.

drop user pepito;

Create PostgreSQL database

Creating a database in PostgreSQL is one of the simplest operations to perform; it only requires the name of the database:

create database pepito_app;

Grant permissions to a user in PostgreSQL

Once the user and the database have been created within the container running the SQL management system, the next step is to grant permissions to the user on that database. There is a wide range of permissions that users can have, allowing for precise control over what each user can and cannot do. This granularity offers a lot of control and security over the stored data. For example, some frequently used permissions are:

• ALL: Grants all permissions on the database
• ALTER: Allows to modify tables, sequences, etc...
• CREATE: Allows to create tables, sequences, etc...
• DROP: Allows to delete tables, sequences, etc...
• DELETE: Allows to delete records in tables
• INSERT: Allows to insert records into tables
• UPDATE: Allows to update records in tables
• SELECT: Allows to query tables, sequences, etc...

In most cases, if we have one user per database, it will be sufficient to grant the ALL permission, or at least the necessary permissions to manage the records of the database. However, to grant permissions to a user on a database, we should write the following SQL statement:

grant all privileges on database pepito_app to pepito;

This statement grants all permissions on all objects of the pepito_app database. From that moment on, the user will be able to create, modify, or delete objects such as tables, sequences, etc. They can also operate with the tables and records they contain, being able to insert, update, query, and delete the rows they contain.

Revoke permissions from a user in PostgreSQL

Removing permissions from a user is done analogously to granting access, but in this case, the revoke keyword is used as follows:

revoke all privileges on database pepito_app from pepito;

Delete database in PostgreSQL

If creation is one of the simplest operations to perform, deletion will be similar:

drop database pepito_app;

All tables, sequences, indexes, and data from the database will be permanently and irrevocably deleted from the management system. Make sure you enter the correct name of the database!

Docker simplifies the creation of development environments. Want to start quickly? Discover how at Arteco Consulting, SL. Visit our website and access our Docker tutorial to master this essential tool. Start your programming journey today!