Use bitnami / PostgreSQL repmgr image to quickly set up PostgreSQL ha

What is? PostgreSQL HA？

this PostgreSQL Cluster solutions include PostgreSQL Replication Manager (replication manager), This is a method for managing PostgreSQL Replication on cluster (replication) And failover (failover) Open source tools for .

Get this image

obtain Bitnami PostgreSQL HA Docker The recommended method of mirroring is from Docker Hub Registry Extract the pre built image .

$ docker pull bitnami/postgresql-repmgr:latest

To use a specific version , You can pull the versioning tab . You can go to Docker Hub Registry View in List of available versions .

• https://hub.docker.com/r/bitnami/postgresql-repmgr/tags/

$ docker pull bitnami/postgresql-repmgr:[TAG]

If you like , You can also build your own image .

$ docker build -t bitnami/postgresql-repmgr:latest 'https://github.com/bitnami/bitnami-docker-postgresql-repmgr.git#master:14/debian-10'

Persist your application

If you delete a container , All data will be lost , The next time you run the mirror , The database will be reinitialized . To avoid this data loss , You should mount a volume that will persist even after the container is deleted .

For persistence , You should be in /bitnami/postgresql Mount a directory on the path . If the mounted directory is empty , It will be initialized the first time it runs .

$ docker run \
    -v /path/to/postgresql-repmgr-persistence:/bitnami/postgresql \
    bitnami/postgresql-repmgr:latest

In this repository docker-compose.yml The file has been configured with persistence .

Be careful ： Because this is a non root Containers , Therefore, the mounted files and directories must have UID 1001 Appropriate permissions for .

Connect to other containers

Use Docker Container network , Your application container has easy access to the PostgreSQL The server , vice versa .

• https://docs.docker.com/engine/userguide/networking/

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

Using the command line

In this example , We're going to create one PostgreSQL Client instance , The instance will connect to the same server as the client docker Server instance running on the network .

Step 1: establish network

$ docker network create my-network --driver bridge

Step 2: In your network Start in postgresql-repmgr Containers

Use docker run Ordered --network <NETWORK> Parameter attaches the container to my-network The Internet .

$ docker run --detach --rm --name pg-0 \
  --network my-network \
  --env REPMGR_PARTNER_NODES=pg-0 \
  --env REPMGR_NODE_NAME=pg-0 \
  --env REPMGR_NODE_NETWORK_NAME=pg-0 \
  --env REPMGR_PRIMARY_HOST=pg-0 \
  --env REPMGR_PASSWORD=repmgrpass \
  --env POSTGRESQL_PASSWORD=secretpass \
  bitnami/postgresql-repmgr:latest

Step 3: Run your PostgreSQL client example

Last , Let's create a new container instance to start PostgreSQL client And connect to the server created in the previous step ：

$ docker run -it --rm \
  --network my-network \
  bitnami/postgresql:10 \
  psql -h pg-0 -U postgres

Use Docker Compose

If not specified ,Docker Compose A new network is automatically set up and all deployed services are attached to the network . however , We will clearly define a named my-network The new bridge The Internet . In this example , Let's assume that you want to connect to... From your own custom application image PostgreSQL The server , The image is represented by the service name in the following code snippet myapp identification .

version: '2'

networks:
  my-network:
    driver: bridge

services:
  pg-0:
    image: 'bitnami/postgresql-repmgr:latest'
    networks:
      - my-network
    environment:
      - POSTGRESQL_PASSWORD=custompassword
      - REPMGR_PASSWORD=repmgrpassword
      - REPMGR_PRIMARY_HOST=pg-0
      - REPMGR_NODE_NETWORK_NAME=pg-0
      - REPMGR_NODE_NAME=pg-0
      - REPMGR_PARTNER_NODES=pg-0
  myapp:
    image: 'YOUR_APPLICATION_IMAGE'
    networks:
      - my-network

Important ：

1. Please update the... In the above code snippet with your application image YOUR_APPLICATION_IMAGE Place holder
2. In your application container , Use host name pg-0 Connect to PostgreSQL The server

Start the container with the following command ：

$ docker-compose up -d

To configure

Initializes a new instance

When the container is executed for the first time , It will execute at /docker-entrypoint-initdb.d The extension of is .sh、.sql and .sql.gz The file of .

In order to put your customization file into docker In the mirror , You can mount them as volumes .

Set... On first run root and repmgr password

In the above order , You may have noticed POSTGRESQL_PASSWORD and REPMGR_PASSWORD Use of environment variables . The first time you run the image, pass POSTGRESQL_PASSWORD The environment variable will postgres The user's password is set to POSTGRESQL_PASSWORD Value （ or POSTGRESQL_PASSWORD_FILE The content of the file specified in ）. Again , Pass on REPMGR_PASSWORD Environment variables will repmgr The user's password is set to REPMGR_PASSWORD Value （ or REPMGR_PASSWORD_FILE The content of the file specified in ）.

$ docker run --name pg-0 --env REPMGR_PASSWORD=repmgrpass --env POSTGRESQL_PASSWORD=secretpass bitnami/postgresql-repmgr:latest

Or by modifying the existing in this repository docker-compose.yml file ：

...
services:
  pg-0:
  ...
    environment:
-      - POSTGRESQL_PASSWORD=adminpassword
+      - POSTGRESQL_PASSWORD=password123
-      - REPMGR_PASSWORD=repmgrpassword
+      - REPMGR_PASSWORD=password123
  ...
  pg-1:
  ...
  environment:
-      - POSTGRESQL_PASSWORD=adminpassword
+      - POSTGRESQL_PASSWORD=password123
-      - REPMGR_PASSWORD=repmgrpassword
+      - REPMGR_PASSWORD=password123
...

Note！postgres and repmgr Users are super users , And right PostgreSQL The database has full administrative access .

If you want to postgres Users set non privileged users and passwords , See creating database users on first run .

Create database on first run

By passing POSTGRESQL_DATABASE environment variable , A database will be created . If your application requires that the database already exists , This will be very useful , You do not have to use PostgreSQL The client creates the database manually .

$ docker run --name pg-0 --env POSTGRESQL_DATABASE=my_database bitnami/postgresql-repmgr:latest

Create database users at first run

You can also create a restricted database user , This user only uses POSTGRESQL_DATABASE The database created by the environment variable has permissions . So , Please provide POSTGRESQL_USERNAME environment variable .

$ docker run --name pg-0 --env POSTGRESQL_USERNAME=my_user --env POSTGRESQL_PASSWORD=password123 --env POSTGRESQL_DATABASE=my_database bitnami/postgresql-repmgr:latest

In this repository docker-compose.yml This setting is already configured in the file .

Note！ Appoint POSTGRESQL_USERNAME when , Not for postgres User assigned password , Therefore, you cannot postgres User identity remote login PostgreSQL The server . If you still want to use the user postgres visit , Please set up POSTGRESQL_POSTGRES_PASSWORD environment variable （ or POSTGRESQL_POSTGRES_PASSWORD_FILE The content of the file specified in ）.

Use stream replication and repmgr Set up HA PostgreSQL colony

Use the following environment variables , have access to Bitnami PostgreSQL HA Docker Mirroring is easy to set up with [ Stream replication ](Streaming replication) and repmgr Of HA PostgreSQL colony ：

• POSTGRESQL_PASSWORD：postgres User's password . No default .
• POSTGRESQL_PASSWORD_FILE： contain postgres The path to the user password file . This will cover POSTGRESQL_PASSWORD The value specified in . No default .
• REPMGR_USERNAME：repmgr User name . The default is repmgr.
• REPMGR_PASSWORD_FILE： contain repmgr The path to the user password file . This will cover REPMGR_PASSWORD The value specified in . No default .
• REPMGR_PASSWORD：repmgr User's password . No default .
• REPMGR_USE_PASSFILE： To configure repmgr To use... In its configuration passfile and PGPASSFILE Instead of plain text passwords .
• REPMGR_PASSFILE_PATH： The location of the password file , If it doesn't exist , It will use REPMGR Create credentials .
• REPMGR_PRIMARY_HOST： The hostname of the initial master node . No default .
• REPMGR_PARTNER_NODES： Comma separated list of partner nodes in the cluster . No default .
• REPMGR_NODE_NAME： The name of the node . No default .
• REPMGR_NODE_NETWORK_NAME： Node hostname . No default .
• REPMGR_PGHBA_TRUST_ALL： This will be in the generated pg_hba.conf Set in auth-method. Only if you use with LDAP Authenticated pgpool Set it to yes. The default is no.

stay HA PostgreSQL In the cluster , You can have one primary node and zero or more standby nodes . The master node is in read-write mode , The standby node is in read-only mode . For best performance , It is recommended to limit reads to standby nodes .

Be careful ： about 9.6 Before version Postgresql,REPMGR_USE_PASSFILE and REPMGR_PASSFILE_PATH Will be ignored .

Use REPMGR_PASSFILE_PATH When mounting the external password file , You also need to configure... Accordingly REPMGR_PASSWORD and REPMGR_USERNAME.

Step 1: establish network

$ docker network create my-network --driver bridge

Step 2: Create the initial master node

The first step is to start the initial master node ：

$ docker run --detach --name pg-0 \
  --network my-network \
  --env REPMGR_PARTNER_NODES=pg-0,pg-1 \
  --env REPMGR_NODE_NAME=pg-0 \
  --env REPMGR_NODE_NETWORK_NAME=pg-0 \
  --env REPMGR_PRIMARY_HOST=pg-0 \
  --env REPMGR_PASSWORD=repmgrpass \
  --env POSTGRESQL_PASSWORD=secretpass \
  bitnami/postgresql-repmgr:latest

Step 3: Create an alternate node

Next, let's start a standby node ：

$ docker run --detach --name pg-1 \
  --network my-network \
  --env REPMGR_PARTNER_NODES=pg-0,pg-1 \
  --env REPMGR_NODE_NAME=pg-1 \
  --env REPMGR_NODE_NETWORK_NAME=pg-1 \
  --env REPMGR_PRIMARY_HOST=pg-0 \
  --env REPMGR_PASSWORD=repmgrpass \
  --env POSTGRESQL_PASSWORD=secretpass \
  bitnami/postgresql-repmgr:latest

Use these three commands , You can now start and run a two node PostgreSQL Primary and standby stream replication clusters . You can do this by adding / Delete the standby node to expand the cluster , Without causing any downtime .

Be careful ： The cluster will completely replicate the master node , This includes all users and databases .

If the primary node goes down ,repmgr It will ensure that any spare node acts as the primary node , To ensure high availability .

Be careful ： The configuration of other nodes in the cluster needs to be updated , So they know they . This will require restarting the old node , Adapt to REPMGR_PARTNER_NODES environment variable .

Use Docker Compose, You can use... In this repository docker-compose.yml File settings HA PostgreSQL colony ：

$ curl -sSL https://raw.githubusercontent.com/bitnami/bitnami-docker-postgresql-repmgr/master/docker-compose.yml > docker-compose.yml
$ docker-compose up -d

Protect PostgreSQL Traffic

PostgreSQL Support use SSL/TLS The protocol encrypts the connection . If you wish to enable this optional feature , You can use the following environment variables to configure your application ：

• POSTGRESQL_ENABLE_TLS： Whether to enable... For traffic TLS. The default is no.
• POSTGRESQL_TLS_CERT_FILE： contain TLS Flow certificate file . No default .
• POSTGRESQL_TLS_KEY_FILE： File containing certificate key . No default .
• POSTGRESQL_TLS_CA_FILE： Include certificate CA The file of . Provided ,PostgreSQL Will pass to TLS/SSL The client requests a certificate to authenticate it （ see also ref）. No default .
• POSTGRESQL_TLS_CRL_FILE： The file containing the certificate revocation list . No default .
• POSTGRESQL_TLS_PREFER_SERVER_CIPHERS： Whether to use the of the server TLS Password preferences rather than client's . The default is yes.

Enable TLS when ,PostgreSQL Standard traffic and encrypted traffic are supported by default , But I prefer the latter . Here are some tips on how to quickly set up TLS Examples of traffic ：

1. Use docker run

$ docker run \
    -v /path/to/certs:/opt/bitnami/postgresql/certs \
    -e POSTGRESQL_ENABLE_TLS=yes \
    -e POSTGRESQL_TLS_CERT_FILE=/opt/bitnami/postgresql/certs/postgres.crt \
    -e POSTGRESQL_TLS_KEY_FILE=/opt/bitnami/postgresql/certs/postgres.key \
    bitnami/postgresql-repmgr:latest

2. Modify the existing in this repository docker-compose.yml file ：

services:
  pg-0:
  ...
    environment:
      ...
      - POSTGRESQL_ENABLE_TLS=yes
      - POSTGRESQL_TLS_CERT_FILE=/opt/bitnami/postgresql/certs/postgres.crt
      - POSTGRESQL_TLS_KEY_FILE=/opt/bitnami/postgresql/certs/postgres.key
    ...
    volumes:
      ...
      - /path/to/certs:/opt/bitnami/postgresql/certs
  ...

perhaps , You can also provide this configuration in a custom configuration file .

The configuration file

The image is in /opt/bitnami/repmgr/conf/ and /opt/bitnami/postgresql/conf/ Search for repmgr.conf、postgresql.conf and pg_hba.conf file . You can go to /bitnami/repmgr/conf/ Mount a volume , And copy / edit /path/to/custom-conf/ Configuration file in . If /bitnami/repmgr/conf/ It's empty , The default configuration will be populated to conf/ Catalog .

/path/to/custom-conf/
└── postgresql.conf

With Replication manager mirrored PostgreSQL Right and wrong root user , Therefore, you need to set the appropriate permissions for the mount directory in the host ：

$ sudo chgrp -R root /path/to/custom-conf/
$ sudo chmod -R g+rwX /path/to/custom-conf/

Step 1: function PostgreSQL Mirror image

function PostgreSQL Mirror image , Mount a directory from your host .

$ docker run --name pg-0 \
    -v /path/to/custom-conf/:/bitnami/repmgr/conf/ \
    bitnami/postgresql-repmgr:latest

Or use Docker Compose：

version: '2'

services:
  pg-0:
    image: bitnami/postgresql-repmgr:latest
    ports:
      - '5432:5432'
    volumes:
      - /path/to/custom-conf/:/bitnami/repmgr/conf/
  pg-1:
    image: bitnami/postgresql-repmgr:latest
    ports:
      - '5432:5432'
    volumes:
      - /path/to/custom-conf/:/bitnami/repmgr/conf/

Step 2: Edit the configuration

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

vi /path/to/custom-conf/postgresql.conf

Step 3: restart PostgreSQL

After changing the configuration , Restart PostgreSQL Container for changes to take effect .

$ docker restart pg-0

Or use Docker Compose：

$ docker-compose restart pg-0
$ docker-compose restart pg-1

Complete list of configuration options , see also Server configuration manual .

• http://www.postgresql.org/docs/10/static/runtime-config.html

Allow from default postgresql.conf File loading settings other than .

In addition to using custom repmgr.conf、postgresql.conf or pg_hba.conf, You can also /bitnami/postgresql/conf/ Your volume contains conf.d In the table of contents .conf Final document . So , default postgresql.conf It includes the following parts ：

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

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

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

If you use custom postgresql.conf, You should create... In the configuration file （ Or uncomment ） Above , under these circumstances , The structure should be similar to

/path/to/custom-conf/
└── postgresql.conf
/path/to/extra-custom-conf/
└── extended.conf

Remember to set the appropriate permissions for the mount directory in the host ：

$ sudo chgrp -R root /path/to/extra-custom-conf/
$ sudo chmod -R g+rwX /path/to/extra-custom-conf/

Step 1: function PostgreSQL Mirror image

function PostgreSQL Mirror image , Mount a directory from your host .

$ docker run --name pg-0 \
    -v /path/to/extra-custom-conf/:/bitnami/postgresql/conf/conf.d/ \
    -v /path/to/custom-conf/:/bitnami/repmgr/conf/ \
    bitnami/postgresql-repmgr:latest

Or use Docker Compose：

version: '2'

services:
  pg-0:
    image: bitnami/postgresql-repmgr:latest
    ports:
      - '5432:5432'
    volumes:
      - /path/to/extra-custom-conf/:/bitnami/postgresql/conf/conf.d/
      - /path/to/custom-conf/:/bitnami/repmgr/conf/
  pg-1:
    image: bitnami/postgresql-repmgr:latest
    ports:
      - '5432:5432'
    volumes:
      - /path/to/extra-custom-conf/:/bitnami/postgresql/conf/conf.d/
      - /path/to/custom-conf/:/bitnami/repmgr/conf/

Step 2: Edit the configuration

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

vi /path/to/extra-custom-conf/extended.conf

Step 3: restart PostgreSQL

After changing the configuration , Restart PostgreSQL Container for changes to take effect .

$ docker restart pg-0

Or use Docker Compose：

$ docker-compose restart pg-0
$ docker-compose restart pg-1

environment variable

Please check in the table below Bitnami PostgreSQL HA List of variables available in the environment ：

Environment Variable	Default value
REPMGR_NODE_ID	nil
REPMGR_NODE_ID_START_SEED	1000
REPMGR_NODE_NAME	nil
REPMGR_NODE_NETWORK_NAME	nil
REPMGR_NODE_PRIORITY	100
REPMGR_PARTNER_NODES	nil
REPMGR_PRIMARY_HOST	nil
REPMGR_NODE_LOCATION	default
REPMGR_PRIMARY_PORT	5432
REPMGR_PORT_NUMBER	5432
REPMGR_LOG_LEVEL	NOTICE
REPMGR_START_OPTIONS	nil
REPMGR_CONNECT_TIMEOUT	5
REPMGR_RECONNECT_ATTEMPTS	3
REPMGR_RECONNECT_INTERVAL	5
REPMGR_USE_REPLICATION_SLOTS	1
REPMGR_MASTER_RESPONSE_TIMEOUT	20
REPMGR_DEGRADED_MONITORING_TIMEOUT	5
REPMGR_USERNAME	repmgr
REPMGR_DATABASE	repmgr
REPMGR_PASSWORD	nil
REPMGR_PASSWORD_FILE	nil
REPMGR_FENCE_OLD_PRIMARY	no
REPMGR_CHILD_NODES_CHECK_INTERVAL	5
REPMGR_CHILD_NODES_CONNECTED_MIN_COUNT	1
REPMGR_CHILD_NODES_DISCONNECT_TIMEOUT	30
REPMGR_USE_PASSFILE	nil
POSTGRESQL_USERNAME	postgres
POSTGRESQL_DATABASE	nil
POSTGRESQL_PASSWORD	nil
POSTGRESQL_PASSWORD_FILE	nil
POSTGRESQL_POSTGRES_PASSWORD	nil
POSTGRESQL_POSTGRES_PASSWORD_FILE	nil
POSTGRESQL_PORT_NUMBER	5432
POSTGRESQL_INITDB_ARGS	nil
POSTGRESQL_PGCTLTIMEOUT	60
POSTGRESQL_SHUTDOWN_MODE	fast
POSTGRESQL_ENABLE_TLS	no
POSTGRESQL_TLS_CERT_FILE	nil
POSTGRESQL_TLS_KEY_FILE	nil
POSTGRESQL_TLS_CA_FILE	nil
POSTGRESQL_TLS_CRL_FILE	nil
POSTGRESQL_TLS_PREFER_SERVER_CIPHERS	yes

journal

Bitnami PostgreSQL HA Docker The mirror sends the container log to stdout. Check the log ：

$ docker logs pg-0

If you want to use container logs differently , You can use --log-driver Option configuration container logging driver. In the default configuration ,docker Use json-file driver.

• https://docs.docker.com/engine/admin/logging/overview/

maintain

Upgrade this image

Bitnami Provides PostgreSQL HA Latest version , Including security patches , These patches will be released soon after they are released upstream . We recommend that you follow these steps to upgrade the container .

Step 1: Get the updated image

$ docker pull bitnami/postgresql-repmgr:latest

perhaps , If you are using Docker Compose, Please put image The value of the property is updated to bitnami/postgresql-repmgr:latest.

Step 2: Stop a running container

Use the command to stop the currently running container

$ docker stop pg-0

Or use Docker Compose：

$ docker-compose stop pg-0
$ docker-compose stop pg-1

Next , Use the following command to the persistent volume /path/to/postgresql-persistence Take a snapshot ：

$ rsync -a /path/to/postgresql-persistence /path/to/postgresql-persistence.bkp.$(date +%Y%m%d-%H.%M.%S)

Step 3: Remove the currently running container

$ docker rm -v pg-0

Or use Docker Compose：

$ docker-compose rm -v pg-0
$ docker-compose rm -v pg-1

Step 4: Run the new image

Recreate the container from the new image .

$ docker run --name pg-0 bitnami/postgresql-repmgr:latest

Or use Docker Compose：

$ docker-compose up pg-0
$ docker-compose up pg-1

more

• Pgpool-II 4.3 Chinese Manual - Preface
• pgpool-II 4.3 Chinese Manual - Introductory tutorial
• 「 stay Kubernetes Up operation Pgpool-Il」 Realization PostgreSQL Inquire about ( read ) Load balancing and connection pooling