Bucardo – a table-based postgresql replication system

Bucardo is a powerful replication system for PostgreSQL, enabling asynchronous multi-master replication and other advanced replication configurations. Installing Bucardo within Docker allows you to easily deploy, manage, and scale replication setups in isolated containers, which can streamline database management in production environments. This guide will walk you through the process of setting up Bucardo inside Docker as a table-based postgresql replication system.

Preparations

We need to setup the schema from source to target database.

# dump database dari source
pg_dump -U postgres -h 192.168.12.16 -p 5432 -d my-db -s > schema.sql

# restore ke database target
psql -U postgres -h 192.168.12.18 -p 5432 my-db < schema.sql


# by table
pg_dump -U postgres -h 192.168.12.16 -p 5432 -d my-db -s -t tabel1 -t tabel2 > schema.sql
pg_dump -U postgres -h 10.41.1.45 -p 5432 -d skm -s -t tabel1 -t tabel2 > skm.sql

Folder Structure

Create Dockerfile

# Gunakan Ubuntu 22.04 LTS sebagai basis
FROM ubuntu:22.04

ARG DEBIAN_FRONTEND=noninteractive

# Update dan instal PostgreSQL, Bucardo, dan dependensi dasar
RUN apt-get update \
    && apt-get install -y \
        wget \
        iputils-ping \
        postgresql \
        postgresql-contrib \
        bucardo \
        jq \
    && apt-get clean \
    && rm -rf /var/lib/apt/lists/*

# Salin konfigurasi
COPY etc/pg_hba.conf /etc/postgresql/14/main/
COPY etc/bucardorc /etc/bucardorc
COPY etc/.pgpass /root/.pgpass

# Set permissions dan setup Bucardo
RUN chmod 600 /root/.pgpass
RUN chown postgres /etc/postgresql/14/main/pg_hba.conf \
    && chown postgres /etc/bucardorc \
    && chown postgres /var/log/bucardo \
    && mkdir -p /var/run/bucardo && chown postgres /var/run/bucardo \
    && usermod -aG bucardo postgres \
    && echo "listen_addresses='*'" >> /etc/postgresql/14/main/postgresql.conf

# Salin skrip entrypoint
COPY lib/entrypoint.sh /entrypoint.sh
RUN chmod +x /entrypoint.sh

# Volume untuk Bucardo
VOLUME "/media/bucardo"

EXPOSE 5432

# Command untuk menjalankan PostgreSQL, konfigurasi dan Bucardo

CMD ["/bin/bash", "-c", "\
    service postgresql start && \
    sleep 5 && \
    su - postgres -c \"psql -tc \\\"SELECT 1 FROM pg_roles WHERE rolname='bucardo'\\\" | grep -q 1 || psql -c \\\"CREATE USER bucardo WITH SUPERUSER PASSWORD 'password';\\\"\" && \
    su - postgres -c \"psql -tc \\\"SELECT 1 FROM pg_database WHERE datname='bucardo'\\\" | grep -q 1 || createdb -O postgres bucardo\" && \
    su - postgres -c \"bucardo install --batch\" && \
    /entrypoint.sh"]

Create docker-compose.yaml

services:
  bucardo:
    build:
      context: .
      dockerfile: Dockerfile
    container_name: bucardo_container
    networks:
      - bucardo_network
    volumes:
      - ./bucardo-config/bucardo.json:/media/bucardo/bucardo.json
    ports:
      - "6432:5432"

networks:
  bucardo_network:
    driver: bridge

Create new folder named it bucardo-config

create bucardo.json inside that folder

{
    "databases": [
        {
            "id": 0,
            "dbname": "my-db",
            "host": "192.168.12.16",
            "port": "5432",
            "user": "postgres",
            "pass": "mypassW0Rd"
        },
        {
            "id": 1,
            "dbname": "my-db",
            "host": "192.168.12.18",
            "port": "5432",
            "user": "postgres",
            "pass": "myOtherDBmypassW0Rd"
        }
    ],
    "syncs": [
        {
            "sources": [
                0
            ],
            "targets": [
                1
            ],
            "tables": "all",
            "onetimecopy": 1
        },
        {
            "sources": [
                1
            ],
            "targets": [
                2
            ],
            "tables": "all",
            "onetimecopy": 1
        }
    ]
}

create new folder named it etc

create 3 files:

.pgpass
bucardorc
pg_hba.conf

.pgpass

192.168.12.16:5432:my-db:bucardo:passw789WOrdxnaord123
192.168.12.18:5432:my-db:bucardo:passw789WOrdxnaord123

bucardorc

dbport = 5432
dbuser = bucardo
dbname = bucardo

pg_hba.conf

local   all             postgres                                trust
local   bucardo         bucardo                                 trust
local   all             all                                     md5
host    all             all             0.0.0.0/0               md5

Create folder lib

create entrypoint.sh inside that folder

#/bin/bash

# Exits with an error when there are any invalid chars
check_invalid_chars() {
  local attr_name=$1
  local invalid_chars=$2
  if [[ "$invalid_chars" != "" ]]; then
    echo "[ERROR] Invalid value \"$string_attr\" for attribute $attr_name!" 1>&2
    exit 1
  fi
}

# An Integer is only allowed to be an Integer
validate_integer_attr() {
  local attr_name=$1
  local string_attr=$2
  local invalid_chars=$(echo $string_attr | sed -e "s/[[:digit:]]//g")
  check_invalid_chars $attr_name $invalid_chars
}

# A String is allowed to have alphanumeric chars,
# dots, dashes, and underscores.
validate_string_attr() {
  local attr_name=$1
  local string_attr=$2
  local invalid_chars=$(echo $string_attr | sed -e "s/[[:alnum:]]//g" \
                                                -e "s/\(.\|-\|_\)//g")
  check_invalid_chars $attr_name $invalid_chars
}

# A List is allowed to have have alphanumeric chars,
# dots, dashes, underscores, and commas.
validate_list_attr() {
  local attr_name=$1
  local string_attr=$2
  local invalid_chars=$(echo $string_attr | sed -e "s/\,//g")
  validate_string_attr $attr_name $invalid_chars
}

run_bucardo_command() {
  local comm=$1
  su - postgres -c "bucardo $comm"
}

start_postgres() {
  service postgresql start
  local status=false
  while [[ $status == false ]]; do
    [[ $(run_bucardo_command "status") ]] && status=true
    sleep 5
  done
}

db_attr() {
  local database=$1
  local attr=$2
  local attr_type=$3
  local value="$(jq ".databases[$database].$attr" /media/bucardo/bucardo.json)"
  case "$attr_type" in
    "string") validate_string_attr "$attr" "$value" ;;
    "list") validate_list_attr "$attr" "$value" ;;
    "integer") validate_integer_attr "$attr" "$value" ;;
    *) echo "Invalid type for $attr." 1>&2; exit 2;;
  esac
  echo $value
}

sync_attr() {
  local sync=$1
  local attr=$2
  local attr_type=$3
  local value="$(jq ".syncs[$sync].$attr" /media/bucardo/bucardo.json)"
  case "$attr_type" in
    "string") validate_string_attr "$attr" "$value" ;;
    "list") validate_list_attr "$attr" "$value" ;;
    "integer") validate_integer_attr "$attr" "$value" ;;
    *) echo "Invalid type for $attr." 1>&2; exit 2;;
  esac
  echo $value
}

#First validate onetimecopy is an integer and then validate if it's 0,1 or 2
one_time_copy_attr() {
  local sync_index=$1
  local value=$(sync_attr $sync_index onetimecopy integer)
  local invalid_chars=$(echo $string_attr | sed -e "s/[0,1,2]//")
  check_invalid_chars $value $invalid_chars
  echo $value
}

load_db_pass() {
  local database=$1
  local pass=$(db_attr $database pass string)
  local id=$(db_attr $database id integer)
  if [[ "$pass" == "\"env\"" ]]; then
    echo "$(env | grep "BUCARDO_DB$id" | cut -d'=' -f2)"
  else
    echo "$pass"
  fi
}

add_databases_to_bucardo() {
  echo "[CONTAINER] Registrasi databases ke Bucardo Bosque..."
  local db_id db_pass
  local db_index=0
  NUM_DBS=$(jq '.databases' /media/bucardo/bucardo.json | grep dbname | wc -l)
  while [[ $db_index -lt $NUM_DBS ]]; do
    echo "[CONTAINER] Menambahkan db$db_index nama database: $(db_attr $db_index dbname string)"
    db_id=$(db_attr $db_index id integer)
    db_pass=$(load_db_pass $db_index)
    run_bucardo_command "del db db$db_id --force"
    run_bucardo_command "add db db$db_id dbname=\"$(db_attr $db_index dbname string)\" \
                                user=\"$(db_attr $db_index user string)\" \
                                pass=\"$db_pass\" \
                                host=\"$(db_attr $db_index host string)\" \
                                port=\"$(db_attr $db_index port string)\""
    db_index=$(expr $db_index + 1)
  done
}

db_sync_entities() {
  local sync_index=$1
  local entity=$2
  local db_index=0
  local sync_entity

  sync_entity=$(sync_attr $sync_index $entity"s[$db_index]" string)
  while [[ "$sync_entity" != null ]]; do
    [[ "$DB_STRING" != "" ]] && DB_STRING="$DB_STRING,"
    DB_STRING=$DB_STRING"db"$sync_entity":$entity"
    db_index=$(expr $db_index + 1)
    sync_entity=$(sync_attr $sync_index $entity"s[$db_index]" string)
  done
}

db_sync_string() {
  local sync_index=$1
  DB_STRING=""
  db_sync_entities $sync_index "source"
  db_sync_entities $sync_index "target"
}

add_syncs_to_bucardo() {
  local sync_index=0
  local num_syncs=$(jq '.syncs' /media/bucardo/bucardo.json | grep tables | wc -l)
  while [[ $sync_index -lt $num_syncs ]]; do
    echo "[CONTAINER] Menambahkan sync$sync_index ke Bucardo..."
    db_sync_string $sync_index
    local one_time_copy="$(one_time_copy_attr $sync_index)"

    # Periksa apakah "tables" bernilai "all"
    local tables_attr=$(sync_attr $sync_index tables string)
    if [[ "$tables_attr" == "\"all\"" ]]; then
      tables_attr="all"
    fi

    run_bucardo_command "del sync sync$sync_index"
    run_bucardo_command "add sync sync$sync_index \
                         dbs=$DB_STRING \
                         tables=$tables_attr \
                         onetimecopy=$one_time_copy"
    sync_index=$(expr $sync_index + 1)
  done
}


start_bucardo() {
  echo "[CONTAINER] Starting Bucardo..."
  run_bucardo_command "start"
}

bucardo_status() {
  echo "[CONTAINER] Update status replica terkini..."
  local run=true
  while [[ $run ]]; do
    run_bucardo_command "status"
    sleep 10
  done
}

main() {
  start_postgres 2> /dev/null
  add_databases_to_bucardo
  add_syncs_to_bucardo
  start_bucardo
  bucardo_status
}

main

Run Bucardo using docker

docker-compose up -d --build