Postgresql Replication and Load Balancing with Docker

Tools

  • Postgres 13
  • HAProxy

Design

Alt text

Manual Installation

  1. Run Postgres docker-compose up -d

  2. Create User For Replication

    # 1. Enter the container
    docker exec -it database-master /bin/sh
    
    # 2. Connect to PostgreSQL
    psql -U postgres
    
    # 3. Create user replica
    postgres-# CREATE ROLE repuser1 WITH LOGIN REPLICATION CONNECTION LIMIT 5 PASSWORD '123456';
    Username repuser1; Maximum number of links: 5; Password: 123456
    
    postgres-# CREATE ROLE repuser1 WITH LOGIN REPLICATION CONNECTION LIMIT 5 PASSWORD '123456';
    Username repuser1; Maximum number of links: 5; Password: 123456
    
    # 4. View rules
    postgres-# \du
                                       List of roles
    Role name |                         Attributes                         | Member of 
    -----------+------------------------------------------------------------+-----------
    postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
    repuser1  | Replication                                               +| {}
              | 5 connections                                              | 
    repuser2  | Replication                                               +| {}
              | 5 connections                                              |
  3. Modify the master configuration file

    pg_hba.conf

    host    replication     repuser1        10.0.3.201/24           md5
    host    replication     repuser2        10.0.3.202/24           md5

    postgresql.conf

    archive_mode = on   # Enable Archive Mode
    archive_command = '/bin/date'	# Set archiving behavior
    # The sum of the number of concurrent connections from the slave to the host
    max_wal_senders = 10			
    # Specifies that if the backup server needs to obtain log segment files for stream replication, pg_ The minimum size of past log file segments that can be retained in the wal directory	
    wal_keep_size = 16		
    # Specify a list of backup servers that support synchronous replication
    synchronous_standby_names = '*'
  4. Craete basebackup on slave

    Slave 1

    # 1. Enter the container slave 1
    docker exec -it database-slave-1 /bin/sh
    
    # 2. Back up the host data to the repl folder. Here, enter the password set above: 123456
    pg_basebackup -R -D /var/lib/postgresql/repl -Fp -Xs -v -P -h 10.0.3.200 -p 5432 -U repuser1

    Slave 2

    # 1. Enter the container slave 2
    docker exec -it database-slave-2 /bin/sh
    
    # 2. Back up the host data to the repl folder. Here, enter the password set above: 123456
    pg_basebackup -R -D /var/lib/postgresql/repl -Fp -Xs -v -P -h 10.0.3.200 -p 5432 -U repuser2
  5. Reconfig docker volumes container slave

    Slave 1

    volumes:
      - ./data/slave1:/var/lib/postgresql/data
      - ./data/repl1:/var/lib/postgresql/repl
    

    volumes:
      - ./data/repl1:/var/lib/postgresql/data
    

    Slave 2

    volumes:
      - ./data/slave2:/var/lib/postgresql/data
      - ./data/repl2:/var/lib/postgresql/repl
    

    volumes:
      - ./data/repl2:/var/lib/postgresql/data
    

    After modif run command docker-compose up -d, if not work restart container slave manual

  6. Status database replication

    docker exec -it database-master /bin/sh
    # su postgres
    [email protected]:/$ psql
    psql (13.8 (Debian 13.8-1.pgdg110+1))
    Type "help" for help.
    
    postgres=# SELECT * FROM pg_stat_replication;
    pid | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         | backend_xmin |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time           
    -----+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
    36 |    16385 | repuser  | walreceiver      | 10.0.3.201  |                 |       34324 | 2022-08-17 11:41:19.455511+00 |              | streaming | 0/A0000D8 | 0/A0000D8 | 0/A0000D8 | 0/A0000D8  |           |           |            |             1 | sync       | 2022-08-17 11:42:14.418949+00
    37 |    16392 | repuser2 | walreceiver      | 10.0.3.202  |                 |       60442 | 2022-08-17 11:41:24.210798+00 |              | streaming | 0/A0000D8 | 0/A0000D8 | 0/A0000D8 | 0/A0000D8  |           |           |            |             1 | potential  | 2022-08-17 11:42:14.418667+00
    (2 rows)
    
    postgres=#
  7. Example testing database with Golang

    Setup

    # 1. Install Golang
    https://go.dev/doc/install
    
    # 2. Init module
    go mod tidy

    Try-1 Run apps

    go run example_apps.go
    
    Create Data :
    {2 Test}
    IP DB Master: 10.0.3.200
    
    Read Data :
    {2 Test}
    IP DB Slave : 10.0.3.201

    Try-2 Run apps

    go run example_apps.go
    
    Create Data :
    {3 Test}
    IP DB Master: 10.0.3.200
    
    Read Data :
    {3 Test}
    IP DB Slave : 10.0.3.202

    Slave database load balancing is working.

Contact

https://www.linkedin.com/in/aji-indra-jaya

License

MIT

GitHub

View Github