3 min read postgresql

Репликация postgresql

Небольшая заметка на тему настройки репликации pgsql.

Установка PostgreSQL

Как и все остальное ставить будем в докер.

services:
  postgres_master:
    container_name: postgres_master
    image: postgres:14
    restart: always
    environment:
      POSTGRES_DB: "pg_db"
      POSTGRES_USER: "root"
      POSTGRES_PASSWORD: "password"
    volumes:
      - "/mnt/HD/HD_a2/Public/Docker/pg_master/:/var/lib/postgresql/data"
    ports:
      - "1001:5432"

  postgres_slave:
    container_name: postgres_slave
    image: postgres:14
    restart: always
    environment:
      POSTGRES_DB: "pg_db"
      POSTGRES_USER: "root"
      POSTGRES_PASSWORD: "password"
    volumes:
      - "/mnt/HD/HD_a2/Public/Docker/pg_slave/data:/var/lib/postgresql/data"
      - "/mnt/HD/HD_a2/Public/Docker/pg_slave/main:/var/lib/postgresql/main"

docker-compose.yml

На мастере разрешаем доступ для наших ip: 172.18.0.0/24 для слейва и 172.19.0.102 для нашего пк.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
                                                                     
local   all             all                                     trust 

host    all             all             127.0.0.1/32            trust
host    all             all             172.18.0.0/24           trust 
host    all             all             172.19.0.102/32         trust
host    all             all             ::1/128                 trust

host    replication     all             127.0.0.1/32            trust 
host    replication     all             ::1/128                 trust
host    replication     root            172.18.0.0/24           trust

pg_hba.conf

Проверим что база отвечает:

#! /bin/python3

import psycopg2

# пытаемся подключиться к базе данных
conn = psycopg2.connect(dbname='pg_db', user='root', password='password', host='pgsql.homelab-notes.duckdns.org', port='1001')
# получение объекта курсора
cursor = conn.cursor()
cursor.execute('create table test (id bigserial primary key, data text NOT NULL);')
cursor.execute('insert into test(data) values(\'test\')')
cursor.execute('commit')
# Получаем список всех пользователей
cursor.close() # закрываем курсор
conn.close() # закрываем соединение

подключаемся, создаем новую талицу и пробуем туда что-нибудь записать

Добавим туда генерацию данных и увеличим кол-во записей(будем передавать их аргументом) и можно будет заливать в базу много данных, для теста реплики должно подойти

#! /bin/python3

import random
import argparse
import psycopg2

parser = argparse.ArgumentParser()
parser.add_argument('--num', type=int)
arg = parser.parse_args()

conn = psycopg2.connect(dbname='pg_db', user='root', password='password', host='pgsql.homelab-notes.duckdns.org', port='1001')
cursor = conn.cursor()

i=0
while i <= arg.num:
  data = ''
  for d in range (random.randint(1000,10000)):
    data = data + random.choice(list('123456789qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM'))
  i= i + 1
  cursor.execute('insert into test(data) values (\'{0}\')'.format(data))
  cursor.execute('COMMIT')

cursor.close()
conn.close()

будем забивать таблицу случайными данными

Настройка репликации

На мастере добавим следующие строки в postgres.conf

wal_level = replica
max_wal_senders = 2
hot_standby = on
archive_mode = on
archive_command = 'tar -cf /var/lib/postgresql/data/backup/%f %p'

На реплике заходим в контейнер и делаем basebackup в /var/lib/postgresql/main

pg_basebackup -R -h postgres_master -U root -D /var/lib/postgresql/main/ -P ; chown -R postgres:1000  /var/lib/postgresql/main/

заодно имеет смысл поправить владельца каталога

Тушим слейв и в каталоге в postgres меняем каталог data на main

rm -r /mnt/HD/HD_a2/Public/Docker/pg_slave/data
mv /mnt/HD/HD_a2/Public/Docker/pg_slave/main /mnt/HD/HD_a2/Public/Docker/pg_slave/data

Запускаем контейнер, в случае если репликация началась то в логах будут соответствующие записи:

2023-12-21 02:34:03.125 MSK [36] LOG:  started streaming WAL from primary at 0/19000000 on timeline 1

реплика заработала

# мастер
psql -d postgres -c "select pid, state, application_name, sent_lsn from pg_stat_replication;"

# слейв
psql -d postgres -c "select pid, status, sender_host, written_lsn from pg_stat_wal_receiver;

еще статус можно посмотреть в базе

Как проверить актуальность реплики? Для этого нам будут полезны следующие фунции postgres:

  • pg_current_wal_lsn() (старый аналог pg_current_xlog_location()) — функция которая позволяет посмотреть текущую позицию в журнале транзакций.
  • pg_last_wal_receive_lsn(), pg_last_xlog_receive_location() — это аналогичная функция вышеупомянутой, только для реплик.

т.е. нам надо взять последнюю точку на местере и сравнить с последней точкой на слейве:

#! /bin/python3

import psycopg2

def master_wal():
  conn = psycopg2.connect(dbname='postgres', user='root', password='password', host='postgres_master')
  cursor = conn.cursor()
  cursor.execute('select pg_current_wal_lsn();')
# забираем результат и убираем лишнее до слеша
  wal = cursor.fetchone()[0].split("/")[1]
# переводим в int 16
  wal = int(wal, 16)
  return (wal)
  cursor.close()
  conn.close()


def slave_wal():
  conn = psycopg2.connect(dbname='postgres', user='root', password='password', host='localhost')
  cursor = conn.cursor()
  cursor.execute('select pg_last_wal_receive_lsn();')
  wal = cursor.fetchone()[0].split("/")[1]
  wal = int(wal, 16)
  return (wal)
  cursor.close()
  conn.close()

total = slave_wal() - master_wal()
print (total)

Допустим что у нас что то пошло не так и реплика не способна догнать мастер. В таком случае тушим postgres на слейве и делаем заново basebackup.

Так же очень много полезного есть на habr.