congcong126/patroni-postgres repository overview

⁠What is patroni-postgres?

On the basis of retaining the official image usage characteristics, the patroni feature is added It retains the usage characteristics of the official image. If you only use it on a single machine or for testing, you can completely refer to the usage of the official image: https://hub.docker.com/_/postgres⁠

⁠How to use this image

⁠start a single postgres instance

docker run --name ddd -e POSTGRES_PASSWORD=123456 -d congcong126/patroni-postgres:16.1

⁠start a patroni instance

docker run -d --name ddd -e PG_MODE=patroni -e ETCD_MODE=int congcong126/patroni-postgres:16.1

⁠How to extend this image

According to the preset variables, more methods can be used to start the image to achieve the purpose of use.

⁠PGDATA

⁠If using single mode

Important Note: when mounting a volume to /var/lib/postgresql, the /var/lib/postgresql/data path is a local volume from the container runtime, thus data is not persisted on the mounted volume.

This optional variable can be used to define another location - like a subdirectory - for the database files. The default is /var/lib/postgresql/data. If the data volume you're using is a filesystem mountpoint (like with GCE persistent disks), or remote folder that cannot be chowned to the postgres user (like some NFS mounts), or contains folders/files (e.g. lost+found), Postgres initdb requires a subdirectory to be created within the mountpoint to contain the data.

For example:

docker run --name ddd -e POSTGRES_PASSWORD=123456 -d  -v /custom/mount:/var/lib/postgresql/data congcong126/patroni-postgres:16.1

⁠If using patroni mode

Important Note: when mounting a volume to /data/pg, /data/pg is the default pgdata directory in patroni mode For example:

docker run -d --name ddd -e PG_MODE=patroni -e ETCD_MODE=int -v /custom/mount:/data/pg congcong126/patroni-postgres:16.1

⁠If using ETCD_MODE=int

Important Note: when mounting a volume to /data/etcd, /data/etcd is the default pgdata directory in patroni mode For example:

docker run -d --name ddd -e PG_MODE=patroni -e ETCD_MODE=int -v /custom/mount:/data/etcd congcong126/patroni-postgres:16.1

⁠environment variables

⁠Used to set vip

⁠PATRONI_VIP

The VIP address that needs to be started. The default is empty. When this value is set, a VIP address will be added to the master node when starting the service.

⁠--cap-add=NET_ADMIN

Allows the container to set up the network. If vip is enabled, this parameter must be enabled.

⁠Used to set etcd

⁠ETCD_MODE

Whether to enable the etcd service inside the container, the default is ext Using external etcd, if you use the internal etcd service, you need to set ETCD_MODE=int

⁠ETCD_COSTOMCONF

Configuration injection of etcd service, custom configuration startup or cluster creation requires setting this parameter, for example: "ETCD_COSTOMCONF=--name good --listen-peer-urls http://192.168.2.67:2380⁠ --listen-client-urls http://127.0.0.1:2379,http://192.168.2.67:2379⁠ --initial-advertise-peer-urls http://192.168.2.67:2380⁠ --advertise-client-urls http://192.168⁠. 2.67:2379 --listen-metrics-urls http://192.168.2.67:2381⁠"

⁠Used to set patroni

⁠PG_MODE

Set the startup mode of the service. The default is single. If you want to use patroni mode, you need to set PG_MODE=patroni.

⁠PATRONI_NETDEV

Set the name of the network adapter，for example, name the network adapter ens18，you need to set PATRONI_NETDEV=ens18. The default setting is eth0.

⁠PATRONI_SCOPE

Set the cluster scope. The default is pgsql. If you need to run multiple clusters in the same network, you need to set different scopes.

⁠HOSTNAME

Set the cluster host name, the default is container $HOSTNAME

⁠PATRONI_ETCDNUM

Set the version of etcd to connect to, the default is etcd3

⁠PATRONI_ETCDHOSTS

Set the etcd connection pool, the default is 127.0.0.1

⁠PATRONI_PROTOCOL

Set the request method of etcd, the default is http

⁠PATRONI_PGPORT

Set the port of pgsql in patroni startup mode, the default is 5432

⁠PATRONI_HOST

Set the patroni connection address, which is $HOSTNAME by default.

⁠PATRONI_SUPASS

Set the pgsql super management password in patroni mode, the default is OG9Iu54cvuWi

⁠PATRONI_REPPASS

Set the pgsql rep password in patroni mode, the default is OG9Iu54cvuWi

⁠PATRONI_NOFAILOVER

Do not participate in the election, default is false

⁠PATRONI_NOLOADBALANCE

Do not participate in load balancing, default is false

⁠PATRONI_CLONEFROM

Specify the clone address, the default is false

⁠PATRONI_NOSYNC

Not used as a synchronization library, the default is false

⁠PATRONI_RETRYTMOUT

The timeout period for connecting to etcd, the default is 10,000 days

⁠PATRONI_SYNCHRONOUS

Synchronous replication mode, default is true

⁠Used to set patroni pgsql optimization，Reference address: https://pgtune.leopard.in.ua/

⁠PATRONI_MAXCONN

The maximum number of pgsql connections in patroni mode, default 2000

⁠PATRONI_SHARD_BUFFERS

In patroni mode, pgsql sets the available memory at the database instance level, default: 512MB

⁠PATRONI_EFFECTIVE_CACHE

In patroni mode, pgsql is used to tell the database optimizer (the component responsible for query plan generation) how much system memory can be considered an available cache. Default: 1536MB

⁠PATRONI_MAINTENANCE

The maximum memory allocation used by pgsql for maintenance operations, data sorting and recovery tasks in patroni mode, default: 128MB

⁠PATRONI_CHECKPOINT

In patroni mode, pgsql guides how to smoothly perform checkpoint I/O. Default: 0.9

⁠PATRONI_WAL_BUFFERS

In patroni mode, pgsql is used to determine the size of the buffer used by the write-ahead log (WAL), default: 16MB

⁠PATRONI_STATISTICS_TARGET

In patroni mode, pgsql determines the granularity of the histogram of the column generated when performing the ANALYZE operation, which can also be understood as the sampling granularity. Default: 100

⁠PATRONI_RANDOM_PAGE

In patroni mode, pgsql sets the query optimizer's estimated cost for non-sequential acquisition of disk pages. Default: 1.1

⁠PATRONI_RANDOM_PAGE

In patroni mode, pgsql is used to optimize the management of the system when performing parallel disk I/O (input/output) operations. The setting of this parameter affects how Postgres manages parallel access to the hard disk, especially for table spaces located on different storage devices. Default: 200

⁠PATRONI_WORK_MEM

In patroni mode pgsql is used to specify the maximum amount of memory that a SQL operation (for example, sorting or hash table operation) can use before requiring the use of disk temporary files. In other words, this value sets how much data can be kept in memory before Postgres starts using disk for storage. Default: 131kB

⁠PATRONI_HUGE_PAGES

In patroni mode, pgsql controls whether PostgreSQL attempts to use the "huge pages" feature. "Huge Pages" is a way to manage memory in modern operating systems to improve the efficiency of memory management. Default: off

⁠PATRONI_MIN_WAL

In patroni mode, pgsql is used to set the minimum WAL (Write Ahead Log) data volume kept on disk, default: 1GB

⁠PATRONI_MAX_WAL

In patroni mode, pgsql determines the maximum rated capacity of WAL data on disk before PostgreSQL starts deleting or reusing old WAL (Write Ahead Log) files. Default: 4GB

⁠PATRONI_MAX_WORKER

In patroni mode, pgsql sets the maximum number of background processes allowed by the database system. Default: -1

⁠PATRONI_MAX_PARALLEL_PER

In patroni mode, pgsql is used to control the maximum number of parallel worker processes allowed by the system when executing a single parallel operation (such as a sequential scan or aggregation operation). Default: -1

⁠PATRONI_MAX_PARALLEL_WORKERS

In patroni mode, pgsql is used to set the maximum number of parallel processes allowed by the database. Default: -1

⁠PATRONI_MAX_MAIN

In patroni mode, pgsql is used to set the maximum number of parallel processes allowed for maintenance commands (such as CREATE INDEX). Default: -1

⁠How to create a highly available cluster

⁠Create a cluster based on external etcd

#The host address is: 192.168.2.65-67
#The etcd connection address is: 192.168.2.65-67

#192.168.2.65
docker run -d --network host --cap-add=NET_ADMIN  --add-host=master65:192.168.2.65 --add-host=master66:192.168.2.66 --add-host=master67:192.168.2.67 --name ddd -v /etc/localtime:/etc/localtime -e PG_MODE=patroni -e "PATRONI_VIP=192.168.2.35" -e "PATRONI_ETCDHOSTS=192.168.2.67:2379,192.168.2.66:2379,192.168.2.65:2379" -e "PATRONI_SCOPE=aabbce" congcong126/patroni-postgres:16.1
#192.168.2.66
docker run -d --network host --cap-add=NET_ADMIN  --add-host=master65:192.168.2.65 --add-host=master66:192.168.2.66 --add-host=master67:192.168.2.67 --name ddd -v /etc/localtime:/etc/localtime -e PG_MODE=patroni -e "PATRONI_VIP=192.168.2.35" -e "PATRONI_ETCDHOSTS=192.168.2.67:2379,192.168.2.66:2379,192.168.2.65:2379" -e "PATRONI_SCOPE=aabbce" congcong126/patroni-postgres:16.1
#192.168.2.67
docker run -d --network host --cap-add=NET_ADMIN  --add-host=master65:192.168.2.65 --add-host=master66:192.168.2.66 --add-host=master67:192.168.2.67 --name ddd -v /etc/localtime:/etc/localtime -e PG_MODE=patroni -e "PATRONI_VIP=192.168.2.35" -e "PATRONI_ETCDHOSTS=192.168.2.67:2379,192.168.2.66:2379,192.168.2.65:2379" -e "PATRONI_SCOPE=aabbce" congcong126/patroni-postgres:16.1

⁠Create a cluster based on internal etcd

#The host address is: 192.168.2.65-67
#192.168.2.65
docker run -d --network host --cap-add=NET_ADMIN  --add-host=master65:192.168.2.65 --add-host=master66:192.168.2.66 --add-host=master67:192.168.2.67 --name ddd -v /etc/localtime:/etc/localtime -e PG_MODE=patroni -e ETCD_MODE=int -e "ETCD_COSTOMCONF=--name master65 --listen-peer-urls http://192.168.2.65:2380 --listen-client-urls http://127.0.0.1:2379,http://192.168.2.65:2379 --initial-advertise-peer-urls http://192.168.2.65:2380 --advertise-client-urls http://192.168.2.65:2379 --listen-metrics-urls http://192.168.2.65:2381 --initial-cluster master65=http://192.168.2.65:2380,master66=http://192.168.2.66:2380,master67=http://192.168.2.67:2380 --initial-cluster-token etcd-cluster-1 --initial-cluster-state new" -e "PATRONI_VIP=192.168.2.35" -e "PATRONI_ETCDHOSTS=192.168.2.67:2379,192.168.2.66:2379,192.168.2.65:2379" -e "PATRONI_SCOPE=aabbce" congcong126/patroni-postgres:16.1
#192.168.2.66
docker run -d --network host --cap-add=NET_ADMIN  --add-host=master65:192.168.2.65 --add-host=master66:192.168.2.66 --add-host=master67:192.168.2.67 --name ddd -v /etc/localtime:/etc/localtime -e PG_MODE=patroni -e ETCD_MODE=int -e "ETCD_COSTOMCONF=--name master66 --listen-peer-urls http://192.168.2.66:2380 --listen-client-urls http://127.0.0.1:2379,http://192.168.2.66:2379 --initial-advertise-peer-urls http://192.168.2.66:2380 --advertise-client-urls http://192.168.2.66:2379 --listen-metrics-urls http://192.168.2.66:2381 --initial-cluster master65=http://192.168.2.65:2380,master66=http://192.168.2.66:2380,master67=http://192.168.2.67:2380 --initial-cluster-token etcd-cluster-1 --initial-cluster-state new" -e "PATRONI_VIP=192.168.2.35" -e "PATRONI_ETCDHOSTS=192.168.2.67:2379,192.168.2.66:2379,192.168.2.65:2379" -e "PATRONI_SCOPE=aabbce" congcong126/patroni-postgres:16.1
#192.168.2.67
docker run -d --network host --cap-add=NET_ADMIN  --add-host=master65:192.168.2.65 --add-host=master66:192.168.2.66 --add-host=master67:192.168.2.67 --name ddd -v /etc/localtime:/etc/localtime -e PG_MODE=patroni -e ETCD_MODE=int -e "ETCD_COSTOMCONF=--name master67 --listen-peer-urls http://192.168.2.67:2380 --listen-client-urls http://127.0.0.1:2379,http://192.168.2.67:2379 --initial-advertise-peer-urls http://192.168.2.67:2380 --advertise-client-urls http://192.168.2.67:2379 --listen-metrics-urls http://192.168.2.67:2381 --initial-cluster master65=http://192.168.2.65:2380,master66=http://192.168.2.66:2380,master67=http://192.168.2.67:2380 --initial-cluster-token etcd-cluster-1 --initial-cluster-state new" -e "PATRONI_VIP=192.168.2.35" -e "PATRONI_ETCDHOSTS=192.168.2.67:2379,192.168.2.66:2379,192.168.2.65:2379" -e "PATRONI_SCOPE=aabbce" congcong126/patroni-postgres:16.1