SoFunction
Updated on 2025-03-10

Docker deploy postgresql method implementation

The name of the image of the postgresql database in Docker ispostgres, you can pull it from DockerHub. If it doesn't pull it, it is a high probability that it is caused by network problems. At this time, you may need to find some useful mirror sources online to successfully pull it.postgresMirror.

Have itpostgresAfter the image of thepostgresand run the container. We have two ways to create containers:

  • Create directly using the command linepostgresand run the container;
  • usedocker-composeTo createpostgresand run the container;

Here we first introduce the method of using the command line to create itpostgresContainer method:

docker run -d \
--name postgres-exprdb \
-p 5432:5432 \
-e POSTGRES_PASSWORD=123456 \
-e POSTGRES_USER=aderversa \
-e POSTGRES_DB=testdb \
-v ~/postgresql/data:/var/lib/postgresql/data \
postgres
  • -eIt is used to set uppostgresFor the value of the environment variable in the container, some special environment variables will affect the startuppostgresqlSome parameters ofpostgresqlThe password of the database, the user, etc. For specific meanings of some environment variables, you can check the introduction of environment variables in dockerhub. At the end of this article, the meaning of some environment variables will also be introduced.
  • -pExpose the 5432 port of PostgreSQL to the 5432 port of the host, so that we can access the PostgreSQL service in the container externally.
  • -vPut the container in/var/lib/postgresql/dataThe folder is mounted to the host~/postgresql/dataIn the folder, the data in the database will not be lost because we deleted the container. You need to define where this data folder should be mounted to your host. The values ​​I gave here are for reference only.
  • --name postgres-exprdbSpecifies the name of the PostgreSQL container created.
  • -dIndicates that the container is running in the background and prints the ID of the container that was created to the console.
  • postgresIt is the mirror we use.

Run the above command and usedocker psCheck container status:

CONTAINER ID   IMAGE      COMMAND                   CREATED         STATUS         PORTS                    NAMES
b1f5d4521cd0   postgres   "…"   8 seconds ago   Up 7 seconds   0.0.0.0:5432->5432/tcp   postgres-exprdb

As you can see, the container is already running in the background. After that, we can use other tools to access the created onestestdbDatabase, use defined privileged usersaderversa, and specify a good password123456。

If you have known IP, port, username and password, you can connect to this PostgreSQL database in a way you can imagine.

Use docker-compose to deploy

I prefer to write container configuration toIn the file, then one line:

docker compose -f  -p package_name up -d
  • -fSpecifies the file to operate.
  • -pSpecify the name of the project.
  • upis a command to create and start a container.
  • -dIndicates that the container is started in the background.

For example, I wrote onedocument:

version: "3.8"

services:
  database_expr:
    image: postgres:latest
    container_name: postgres-expr
    restart: on-failure:3
    ports:
      - 5432:5432
    volumes:
      - ./db:/var/lib/postgresql/data
    healthcheck:
      test: [ "CMD", "pg_isready" ]
      interval: 10s
      timeout: 5s
      retries: 5
    environment:
      - POSTGRES_PASSWORD=123456
      - POSTGRES_USER=aderversa
      - POSTGRES_DB=testdb

By directly operating it, you can create a container and start it. Users do not need to care about the configuration inside. Even if you think you use itdocker composeThe command line to start is not abstract enough, so you can write it into a shell script, and the user runs the script directly. As long as the user installs the Docker environment, he can install PostgreSQL successfully. He doesn't even need to care about how the Docker command should be used.

Enter the postgres container to execute SQL

Use the command:

docker ps

Find out the ID of the PostgreSQL container you just created:

CONTAINER ID   IMAGE             COMMAND                   CREATED        STATUS                  PORTS                    NAMES
dcf5e3c0ff7f   postgres:latest   "…"   13 hours ago   Up 13 hours (healthy)   0.0.0.0:5432->5432/tcp   postgres-expr

getcontainer_id = dcf5e3c0ff7f, then we use the following command to enter the containerbashIn order to facilitate operation of containers:

docker exec -it dcf5e3c0ff7f /bin/bash

Note that if you execute this command under Windows, useGit BashThe execution may fail due to some problems in the terminal. At this time, you need to change the terminal that executes the command, such as: PowerShell.

Entering the container looks like this:

> docker exec -it dcf5e3c0ff7f /bin/bash
root@dcf5e3c0ff7f:/#

The first line is executed in PowerShell, while the second line is the bash process running in the PostgreSQL container.

Then we can use:

psql [OPTION]... [DBNAME [USERNAME]]

Here we introduce the OPTION option you need to use to connect to the database. You can use the specific onespsql --helpCome check:

  • -hSpecifies the IP address of the PostgreSQL database.
  • -pSpecifies the port number of the PostgreSQL database.
  • -USpecify the user name to log in to the database, if you do not specify the default, it isroot。
  • -wNever prompt for a password, literally, you don’t need to enter a password to access the database, and by default, you don’t need to enter a password.
  • -WForce password input.

According to this usage, we can connect to the database in the following two ways:

# Here, since the privileged user we specified when creating the container was aderversa, root was squeezed out# Username must be set for me.psql -p 127.0.0.1 -p 5432 -U aderversa testdb

Or save trouble and use it directly:

psql testdb aderversa

After successful execution, we will be able to enterpsqlCommand line:

psql (17.2 (Debian 17.2-1.pgdg120+1))
Type "help" for help.

testdb=#

Use of psql

We don't know how to use this command line, it prompts us to use ithelpTo get help, then execute a wave of help and get the following information:

You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

Here it tells us:

  • \copyright, some terms of PostgreSQL DDMS, which roughly says: No matter what your purpose, the software and its documents are allowed to be used, copied, modified and published, and there is no need for any fee and no need for modification. It seems to be a very relaxed statement, after all, PostgreSQL follows the BSD protocol.

  • \hShow SQL commands, such asCREATE TABLE ALTER TABLE…。

  • \?There are still many commands to display psql, most of which are used to view the status of the database, such as tables, databases, views, etc. Here are some commonly used and simple commands:

    • \l, list all databases;
    • \c[onnect], connect to a database. If you have connected to a database before, then this operation is to replace the connected database;
    • \dt, see which tables exist in the database you are using (note that the tables created by default do not appear to be listed);

  • \gExecute the previous command of psql.

  • \qExit psql.

The above is the basic usage of psql. You can execute SQL statements on it according to your own, such as:

create table user ( id int primary key );

Note the SQL command to;The ending is OK.

After creation is completed, you can use:

\dt

Let's see if you created it successfully.

Next is the time to exert your creativity, try using psql yourself.

Supplement the environment variables of the postgres container

  • POSTGRES_PASSWORD，NecessaryEnvironment variables. This environment variable will set the password of the privileged user in the PostgreSQL container. If you want to use PostgreSQL mirror to make it run as a container, then this environment variable must be set, and it must not be empty or undefined. The default privileged user will bePOSTGRES_USERTo define.
  • POSTGRES_USER, optional environment variable. Setting up the privileged username in the PostgreSQL container, you need toPOSTGRES_PASSWORDUse it together to set the name and password of the privileged user. If the environment variable is not specified, the default privileged username ispostgres。
  • POSTGRES_DB, optional environment variable. Defines the name of the default database created by the container's first run. If the environment variable is not used, the value of the environment variable is equal toPOSTGRES_USERvalue.
  • POSTGRES_INITDB_ARGS, optional environment variable. Probably ultimately it was exploited in this way:postgres initdb ${POSTGRES_INITDB_ARGS}。
  • POSTGRES_INITDB_WALDIR, optional environment variable. Defines the location of the PostgreSQL transaction log. The default transaction log location is the PostgreSQL master data folder (PGDATAa subfolder under Specified).
  • POSTGRES_HOST_AUTH_METHOD, optional environment variable. (Personal understanding, it seems to be something related to password summary, which generally does not require our attention. If you are interested, you can goPostgreSQL: Documentation: 14: 21.5. Password AuthenticationLearn about it).
  • PGDATA, optional environment variable. Define the location of the PostgreSQL main data folder, the default is/var/lib/postgresql/data, If there is a need to adjust the position, the value of the variable can be set as required.

This is the end of this article about the implementation of Docker's postgresql method. For more related content on Docker's postgresql, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!