Docker PostgreSQL containerized deployment method

Docker PostgreSQL containerized deployment

PostgreSQL standard software is built based on Bitnami PostgreSQL. The current version is 16.1.0

You can directly install and deploy through the Light Cloud UC deployment tool, or you can manually operate it as follows. The project has been fully open source and can be obtained from the following environment

Configuration file address:/qingplus/qingcloud-platform

Connect to other containers

Using the Docker container network, application containers can easily access PostgreSQL servers running within the container.

Containers connected to the same network can communicate with each other using the container name as the host name.

Use the command line

In this example, we will create a PostgreSQL client instance that connects to a server instance running on the same docker network as the client.

Step 1: Create a network

docker network create app-tier --driver bridge

Step 2: Start the PostgreSQL server instance

Use the command –network app-tier parameter docker run to connect the PostgreSQL container to the network app-tier.

docker run -d --name postgresql-server \
    --network app-tier \
    /qingcloudtech/postgresql:latest

Step 3: Start the PostgreSQL client instance

Finally, we create a new container instance to start the PostgreSQL client and connect to the server created in the previous step:

docker run -it --rm \
    --network app-tier \
    /qingcloudtech/postgresql:latest psql -h postgresql-server -U postgres

Using Docker Compose Files

If not specified, Docker Compose automatically sets up a new network and attaches all deployed services to that network. However, we will explicitly define a new network app-tier with a bridge named .

In this example, we assume that you want to connect to a PostgreSQL server from your own custom application image, which is identified by the service name in the following code snippet.

version: '2'

networks:
  app-tier:
    driver: bridge

services:
  postgresql:
    image: '/qingcloudtech/postgresql:latest'
    networks:
      - app-tier
  myapp:
    image: 'YOUR_APPLICATION_IMAGE'
    networks:
      - app-tier

important:

Replace YOUR_APPLICATION_IMAGE placeholder

In your application container, use the hostname postgresql to connect to the PostgreSQL server

Start the container:

docker-compose up -d

Configuration

When container starts

When the container executes, before initializing or starting postgresql, it executes a file with the extension .sh located in /.

In order to put custom files into the docker image, you can mount them as volumes.

Pass additional command line flags to PostgreSQL

Additional command line flags can be passed to the postgresql service command via the following environment variables:

• POSTGRESQL_EXTRA_FLAGS: Flag to be attached to the postgres startup command. No default value

Initialize a new instance

When the container is executed for the first time, it will execute the extension as.sh, .sqland.The file is located in / directory.

In order to put custom files into the docker image, you can mount them as volumes.

Set root password for the first run

In the above command you may have noticed the use of POSTGRESQL_PASSWORD for environment variables.

Passing the environment variable POSTGRESQL_PASSWORD when the image is first run will set the password of the postgres user to the value of the POSTGRESQL_PASSWORD (or the contents of the file specified in the POSTGRESQL_PASSWORD_FILE variable).

docker run --name postgresql -e POSTGRESQL_PASSWORD=password123 /qingcloudtech/postgresql:latest

Or by modifying the file:

services:
  postgresql:
  ...
    environment:
      - POSTGRESQL_PASSWORD=password123
  ...

Notice! The postgres user is a super user with full administrative access to the PostgreSQL database.

Create a database at first run

POSTGRESQL_DATABASE creates a database by passing environment variables when the image is first run.

This is useful if your application requires that the database already exists, so that you do not have to manually create the database using the PostgreSQL client.

docker run --name postgresql -e POSTGRESQL_DATABASE=my_database /qingcloudtech/postgresql:latest

Or by modifying the file:

services:
  postgresql:
  ...
    environment:
      - POSTGRESQL_DATABASE=my_database
  ...

Create database user at first run

You can also create a restricted database user that has only permissions to the database created with environment variables POSTGRESQL_DATABASE. To do this, provide the POSTGRESQL_USERNAME environment variable.

docker run --name postgresql -e POSTGRESQL_USERNAME=my_user -e POSTGRESQL_PASSWORD=password123 -e POSTGRESQL_DATABASE=my_database /qingcloudtech/postgresql:latest

Or by modifying the file:

services:
  postgresql:
  ...
    environment:
      - POSTGRESQL_USERNAME=my_user
      - POSTGRESQL_PASSWORD=password123
      - POSTGRESQL_DATABASE=my_database
  ...

Notice! After POSTGRESQL_USERNAME is specified, the postgres user does not assign a password, so you cannot remotely log in to the PostgreSQL server as that postgres user.

If you still want to access using user postgres, set the POSTGRESQL_POSTGRES_PASSWORD environment variable (or the POSTGRESQL_POSTGRES_PASSWORD_FILE specified content in the file).

audit

PostgreSQL image enables the pgAudit module by default. Therefore, you can enable audit information in the container using the following environment variables:

• POSTGRESQL_PGAUDIT_LOG: Comma-separated list with different actions to be reviewed. There is no default value.
• POSTGRESQL_PGAUDIT_LOG_CATALOG: Enable session logging when all relationships in the statement are in pg_catalog. There is no default value.
• POSTGRESQL_LOG_CONNECTIONS: Add login log entry. There is no default value.
• POSTGRESQL_LOG_DISCONNECTIONS: Add logout log entry. There is no default value.
• POSTGRESQL_LOG_HOSTNAME: Record the client host name. There is no default value.
• POSTGRESQL_LOG_LINE_PREFIX: Define the format of the log entry line. Find string parameters in the official PostgreSQL documentation. There is no default value.
• POSTGRESQL_LOG_TIMEZONE: Set the time zone for the log entry timestamp. There is no default value.

Session Settings

The PostgreSQL image allows multiple connection and session management parameters to be configured:

• POSTGRESQL_USERNAME_CONNECTION_LIMIT: If you create a different user postgres, set connection restrictions. There is no default value.
• POSTGRESQL_POSTGRES_CONNECTION_LIMIT: Set user connection restrictions postgres. There is no default value.
• POSTGRESQL_STATEMENT_TIMEOUT: Set the statement timeout time. There is no default value.
• POSTGRESQL_TCP_KEEPALIVES_INTERVAL: TCP keep-alive interval. There is no default value.
• POSTGRESQL_TCP_KEEPALIVES_IDLE: TCP keepalive free time. There is no default value.
• POSTGRESQL_TCP_KEEPALIVES_COUNT: TCP keep-alive count. There is no default value.

Configure time zone

The PostgreSQL image allows the time zone of PostgreSQL to be configured using the following environment variables:

• POSTGRESQL_TIMEZONE: Set the time zone for displaying and interpreting timestamps.
• POSTGRESQL_LOG_TIMEZONE: Set the time zone to use for the timestamp written to the server log.

Modify pg_hba.conf

By default, PostgreSQL image will generate local, md5 in pg_hba.conf files.

To adapt to any other requirements or standards, the pg_hba.conf file can be changed by:

• Mount your own pg_hba.conf to the file /bitnami/postgresql/conf
• POSTGRESQL_PGHBA_REMOVE_FILTERS will be used with a comma-separated pattern list. All rows that match any pattern will be deleted.
• For example, if we want to remove all local and md5 verifications (e.g., only hostssl connections are supported), set POSTGRESQL_PGHBA_REMOVE_FILTERS=local, md5.

Preload the shared library

The list of libraries preloaded by PostgreSQL at startup can be modified by setting the .postgresql file POSTGRESQL_SHARED_PRELOAD_LIBRARIES.

The default value is POSTGRESQL_SHARED_PRELOAD_LIBRARIES=pgaudit.

For example, if you want to add the pg_stat_statements library to preload, set POSTGRESQL_SHARED_PRELOAD_LIBRARIES=pgaudit, pg_stat_statements.

Set up streaming copy

You can set up a stream replication cluster using the following environment variables:

• POSTGRESQL_REPLICATION_MODE: Copy mode. Possible values ​​master/slave. There is no default value.
• POSTGRESQL_REPLICATION_USER: The replica user created on the primary server when run for the first time. There is no default value.
• POSTGRESQL_REPLICATION_PASSWORD: Copy the user password. There is no default value.
• POSTGRESQL_REPLICATION_PASSWORD_FILE: The path to the file that contains the copy of the user's password. This overrides the value specified in POSTGRESQL_REPLICATION_PASSWORD. There is no default value.
• POSTGRESQL_MASTER_HOST: Copy the host name/IP (slave parameter). There is no default value.
• POSTGRESQL_MASTER_PORT_NUMBER: Copy the server port (slave parameters) of the host. The default is 5432. In a replication cluster, you can have one master and zero or more slaves. When replication is enabled, the master node is in read-write mode and the slave node is in read-only mode. For optimal performance, it is recommended to limit reads to slave devices.

Step 1: Create a replication master server

The first step is to start the master.

docker run --name postgresql-master \
  -e POSTGRESQL_REPLICATION_MODE=master \
  -e POSTGRESQL_USERNAME=my_user \
  -e POSTGRESQL_PASSWORD=password123 \
  -e POSTGRESQL_DATABASE=my_database \
  -e POSTGRESQL_REPLICATION_USER=my_repl_user \
  -e POSTGRESQL_REPLICATION_PASSWORD=my_repl_password \
  /qingcloudtech/postgresql:latest

In this command, we use the parameters to configure the container as the main container POSTGRESQL_REPLICATION_MODE=master.

POSTGRESQL_REPLICATION_USER uses and parameters to specify the replication user POSTGRESQL_REPLICATION_PASSWORD.

Step 2: Create a copy slave

Next we start a copy slave container.

docker run --name postgresql-slave \
  --link postgresql-master:master \
  -e POSTGRESQL_REPLICATION_MODE=slave \
  -e POSTGRESQL_MASTER_HOST=master \
  -e POSTGRESQL_MASTER_PORT_NUMBER=5432 \
  -e POSTGRESQL_REPLICATION_USER=my_repl_user \
  -e POSTGRESQL_REPLICATION_PASSWORD=my_repl_password \
  /qingcloudtech/postgresql:latest

In the above command, the container is configured to slave using the POSTGRESQL_REPLICATION_MODE parameter.

Before the replication slave starts, the slave container connects to the master server and replicates the initial database from the master server using the POSTGRESQL_MASTER_HOST and POSTGRESQL_MASTER_PORT_NUMBER parameters.

POSTGRESQL_REPLICATION_PASSWORD and POSTGRESQL_REPLICATION_USER are used to authenticate to the master server. In order to change the default settings of pg_hba.conf, the slave needs to know whether POSTGRESQL_PASSWORD is set.

With these two commands, you have now up and run a two-node PostgreSQL master-slave stream replication cluster. You can scale the cluster by adding/removing slaves without causing any downtime.

Note: The cluster replicates the master node in full, including all users and databases.

If the master fails, you can reconfigure the slave to act as the master and start accepting writes to /tmp/.5432 by creating the trigger file.

For example, the following command reconfigures postgresql-slave to act as the primary server:

docker exec postgresql-slave touch /tmp/.5432

Note: The configuration of other slaves in the cluster needs to be updated so that they know the new master. –link postgresql-slave:master This will require you to restart other slaves as per our example.

With Docker Compose, you can set master-slave replication using the following command:

version: '2'

services:
  postgresql-master:
    image: '/qingcloudtech/postgresql:latest'
    ports:
      - '5432'
    volumes:
      - 'postgresql_master_data:/bitnami/postgresql'
    environment:
      - POSTGRESQL_REPLICATION_MODE=master
      - POSTGRESQL_REPLICATION_USER=repl_user
      - POSTGRESQL_REPLICATION_PASSWORD=repl_password
      - POSTGRESQL_USERNAME=my_user
      - POSTGRESQL_PASSWORD=my_password
      - POSTGRESQL_DATABASE=my_database
  postgresql-slave:
    image: '/qingcloudtech/postgresql:latest'
    ports:
      - '5432'
    depends_on:
      - postgresql-master
    environment:
      - POSTGRESQL_REPLICATION_MODE=slave
      - POSTGRESQL_REPLICATION_USER=repl_user
      - POSTGRESQL_REPLICATION_PASSWORD=repl_password
      - POSTGRESQL_MASTER_HOST=postgresql-master
      - POSTGRESQL_PASSWORD=my_password
      - POSTGRESQL_MASTER_PORT_NUMBER=5432

volumes:
  postgresql_master_data:

Use the following methods to expand the number of slaves:

docker-compose up --detach --scale postgresql-master=1 --scale postgresql-slave=3

The above command increases the number of Slaves to 3. You can reduce the size in the same way.

Note: You should not increase/decrease the number of master nodes. There is always only one master node running.

Synchronous submission

By default, slave instances are configured for asynchronous replication. To ensure more data stability (at the expense of some performance), you can set up a synchronous commit (i.e., transaction commits will not return success to the client until they are written to a set of replicas) variables.

• POSTGRESQL_SYNCHRONOUS_COMMIT_MODE: Create a type of synchronous commit. Available options are:on, remote_apply, remote_write, local and off. The default value is on.
• POSTGRESQL_NUM_SYNCHRONOUS_REPLICAS: Create the number of replicas that will enable synchronous replication. This number must not exceed the number of slaves you configure in the cluster.

With Docker Compose, you can set up master-slave replication with synchronous commits as follows:

version: '2'

services:
  postgresql-master:
    image: '/qingcloudtech/postgresql:latest'
    ports:
      - '5432'
    volumes:
      - 'postgresql_master_data:/bitnami/postgresql'
    environment:
      - POSTGRESQL_REPLICATION_MODE=master
      - POSTGRESQL_REPLICATION_USER=repl_user
      - POSTGRESQL_REPLICATION_PASSWORD=repl_password
      - POSTGRESQL_USERNAME=my_user
      - POSTGRESQL_PASSWORD=my_password
      - POSTGRESQL_DATABASE=my_database
      - POSTGRESQL_SYNCHRONOUS_COMMIT_MODE=on
      - POSTGRESQL_NUM_SYNCHRONOUS_REPLICAS=1
    volumes:
      - '/path/to/postgresql-persistence:/bitnami/postgresql'
  postgresql-slave:
    image: '/qingcloudtech/postgresql:latest'
    ports:
      - '5432'
    depends_on:
      - postgresql-master
    environment:
      - POSTGRESQL_REPLICATION_MODE=slave
      - POSTGRESQL_REPLICATION_USER=repl_user
      - POSTGRESQL_REPLICATION_PASSWORD=repl_password
      - POSTGRESQL_MASTER_HOST=postgresql-master
      - POSTGRESQL_MASTER_PORT_NUMBER=5432
  postgresql-slave2:
    image: '/qingcloudtech/postgresql:latest'
    ports:
      - '5432'
    depends_on:
      - postgresql-master
    environment:
      - POSTGRESQL_REPLICATION_MODE=slave
      - POSTGRESQL_REPLICATION_USER=repl_user
      - POSTGRESQL_REPLICATION_PASSWORD=repl_password
      - POSTGRESQL_MASTER_HOST=postgresql-master
      - POSTGRESQL_MASTER_PORT_NUMBER=5432

In the above example, the commit needs to be written to one of the master and slave servers to be accepted. Another slave will continue to use asynchronous replication.

Check it with the following SQL query:

postgres=# select application_name as server, state,
postgres-#       sync_priority as priority, sync_state
postgres-#       from pg_stat_replication;
| server      | state     | priority | sync_state |
|-------------|-----------|----------|------------|
| walreceiver | streaming | 0        | sync       |
| walreceiver | streaming | 0        | async      |

Note: For more advanced settings, you can use the application_name to define different replication groups using parameters by setting the POSTGRESQL_CLUSTER_APP_NAME environment variable.

LDAP certification

In order to use LDAP authentication, you need to enable it by setting the environment variable POSTGRESQL_ENABLE_LDAP to yes.

There are two ways to set up LDAP configuration:

• By configuring POSTGRESQL_LDAP_URL, you can configure all associated parameters in the URL.
• Set the parameter POSTGRESQL_LDAP_xxxx independently.

LDAP related parameters are:

• POSTGRESQL_LDAP_SERVER: The IP address or name of the LDAP server to be connected to. Separated by spaces.
• POSTGRESQL_LDAP_PORT: The port number to connect to on the LDAP server
• POSTGRESQL_LDAP_SCHEME: Set to ldaps to use LDAPS. Default is None.
• POSTGRESQL_LDAP_TLS: Set to 1 to use TLS encryption. Default is None.
• POSTGRESQL_LDAP_PREFIX: The string to be added to the username when forming the DN to be bound. Default is None.
• POSTGRESQL_LDAP_SUFFIX: A string attached to the username when forming the DN to be bound. Default is None.
• POSTGRESQL_LDAP_BASE_DN: Start searching for the user's root DN. Default is None.
• POSTGRESQL_LDAP_BIND_DN: The DN of the user to be bound to LDAP. Default is None.
• POSTGRESQL_LDAP_BIND_PASSWORD: The user password that binds to LDAP. Default is None.
• POSTGRESQL_LDAP_SEARCH_ATTR: Attributes that match the username in the search. Default is None.
• POSTGRESQL_LDAP_SEARCH_FILTER: Search filters used when performing search + binding authentication. Default is None.
• POSTGRESQL_LDAP_URL: The URL to be connected, in the format: ldap[s]😕/host[:port]/basedn[?[attribute][?[scope][?[filter]]]].

Protect PostgreSQL traffic

PostgreSQL supports encryption of connections using the SSL/TLS protocol. If you want to enable this optional feature, you can configure the application using the following environment variables:

• POSTGRESQL_ENABLE_TLS: Whether to enable TLS for traffic. Default is no.
• POSTGRESQL_TLS_CERT_FILE: File containing the TLS traffic certificate file. There is no default value.
• POSTGRESQL_TLS_KEY_FILE: File containing the certificate key. There is no default value.
• POSTGRESQL_TLS_CA_FILE: File containing the certificate CA. If provided, PostgreSQL validates the TLS/SSL client by requesting a certificate (see ref). There is no default value.
• POSTGRESQL_TLS_CRL_FILE: File containing the certificate revocation list. There is no default value.
• POSTGRESQL_TLS_PREFER_SERVER_CIPHERS: Whether to use the server's TLS password preferences instead of the client's. Default is yes.

When TLS is enabled, PostgreSQL will support both standard and encrypted traffic by default, but prefer the latter. Here are some examples of how to quickly set up TLS traffic:

Using docker run

```console
$ docker run \
    -v /path/to/certs:/opt/bitnami/postgresql/certs \
    -e ALLOW_EMPTY_PASSWORD=yes \
    -e POSTGRESQL_ENABLE_TLS=yes \
    -e POSTGRESQL_TLS_CERT_FILE=/opt/bitnami/postgresql/certs/ \
    -e POSTGRESQL_TLS_KEY_FILE=/opt/bitnami/postgresql/certs/ \
    /qingcloudtech/postgresql:latest
```

Modify the file:

```yaml
services:
  postgresql:
  ...
    environment:
      ...
      - POSTGRESQL_ENABLE_TLS=yes
      - POSTGRESQL_TLS_CERT_FILE=/opt/bitnami/postgresql/certs/
      - POSTGRESQL_TLS_KEY_FILE=/opt/bitnami/postgresql/certs/
    ...
    volumes:
      ...
      - /path/to/certs:/opt/bitnami/postgresql/certs
  ...
```

Alternatively, you can provide this configuration in a custom configuration file.

Configuration File

The image looks for the file /opt/bitnami/postgresql/conf/ in .

You can mount the volume /bitnami/postgresql/conf/ and copy/edit. If the directory is empty, the default configuration will be populated into the directory.

/path/to/postgresql-persistence/conf/conf/

/path/to/postgresql-persistence/conf/
└── 

0 directories, 1 file

Since the PostgreSQL image is non-root, you need to set appropriate permissions for the mount directory in the host:

sudo chown 1001:1001 /path/to/postgresql-persistence/conf/

Step 1: Run the PostgreSQL image

Run the PostgreSQL image to install the directory from the host.

docker run --name postgresql \
    -v /path/to/postgresql-persistence/conf/:/bitnami/postgresql/conf/ \
    /qingcloudtech/postgresql:latest

Or use Docker Compose:

version: '2'

services:
  postgresql:
    image: '/qingcloudtech/postgresql:latest'
    ports:
      - '5432:5432'
    volumes:
      - /path/to/postgresql-persistence/conf/:/bitnami/postgresql/conf/

Step 2: Edit the configuration

Use your favorite editor to edit the configuration on the host.

vi /path/to/postgresql-persistence/conf/

Step 3: Restart PostgreSQL

After changing the configuration, restart the PostgreSQL container for the changes to take effect.

docker restart postgresql

Or use Docker Compose:

docker-compose restart postgresql

For a complete list of configuration options, see the server configuration manual.

Allow loading settings from files other than default files

In addition to using customization, you can also include .conf files ending in a directory /bitnami/postgresql/conf/. To do this, the following parts are included by default:

##------------------------------------------------------------------------------
## CONFIG FILE INCLUDES
##------------------------------------------------------------------------------

## These options allow settings to be loaded from files other than the
## default .

include_dir = ''  # Include files ending in '.conf' from directory ''

In your host, you should create an extension configuration file in the directory:

mkdir -p /path/to/postgresql-persistence/conf//
vi /path/to/postgresql-persistence/conf//

If you use a custom one, you should create (or uncomment) the above section in the configuration file, in which case the /path/to/postgresql-persistence/conf/ structure should be similar to

/path/to/postgresql-persistence/conf/
├── 
│   └── 
└── 

1 directory, 2 files

Specify the initdb parameter

Use the following environment variables to easily specify additional initdb parameters:

• POSTGRESQL_INITDB_ARGS: Specify additional parameters for the initdb command. There is no default value.
• POSTGRESQL_INITDB_WAL_DIR: Define the custom location of the transaction log. There is no default value.

docker run --name postgresql \
  -e POSTGRESQL_INITDB_ARGS="--data-checksums" \
  -e POSTGRESQL_INITDB_WAL_DIR="/bitnami/waldir" \
  /qingcloudtech/postgresql:latest

Or by modifying the file:

services:
  postgresql:
  ...
    environment:
      - POSTGRESQL_INITDB_ARGS=--data-checksums
      - POSTGRESQL_INITDB_WAL_DIR=/bitnami/waldir
  ...

Stop setting

You can control the parameters used to stop postgresql during the initialization process using the following command:

• POSTGRESQL_PGCTLTIMEOUTThis will set the timeout of the command pg_ctl.
• POSTGRESQL_SHUTDOWN_MODEThis will indicate the off mode used.

Install additional locale

Dockerfile provides two parameters to configure additional locale settings at build time:

• WITH_ALL_LOCALES: Enable all supported locales. Default value: No
• EXTRA_LOCALES: Comma-separated list of additional locales to enable. No default value

For example, to build an image es_ES.UTF-8 that supports locale settings, you can add the following parameters to the build command:

docker build --build-arg EXTRA_LOCALES="es_ES.UTF-8 UTF-8" ...

Environment variable alias

The container allows two different sets of environment variables. See the list of environment variable alias in the table below:

Important information:

• Changing POSTGRES_USER does not change the owner of the database, and the owner of the database will continue to postgres as user.
• To change the database owner, use the postgres user ($psql -U postgres…) to access and execute the following command:

alter database POSTGRES_DATABASE owner to POSTGRES_USER;

You can change the user PostgreSQL used to execute init scripts.

To do this, use the following environment variables:

Default toast compression

The default toast compression is pglz, but you can modify it by setting the environment variable POSTGRES_DEFAULT_COMPRESSION to the desired value.

For example: POSTGRES_DEFAULT_COMPRESSION='lz4'.

log

docker logs postgresql

Or use Docker Compose:

docker-compose logs postgresql

You can directly install and deploy through the Light Cloud UC deployment tool, or you can manually operate it as follows. The project has been fully open source and can be obtained from the following environment

Open source address:/qingplus/qingcloud-platform

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.