Previous Page

nihilist - 00 / 00 / 00

PostgreSQL Multi-Master Replication

In this tutorial we will be looking at a Multi-Master PostgreSQL Replication solution called 'Bucardo' you can check it out here

Initial Setup

First let's create our 2 LXC containers from within proxmox:

Create the first one, then clone it and edit the second:

Then start both of them and setup SSH to work on both:


Debian GNU/Linux 10 debian-psql-1 tty1

debian-psql-1 login: root
Password: 
Linux debian-psql-1 5.4.106-1-pve #1 SMP PVE 5.4.106-1 (Fri, 19 Mar 2021 11:08:47 +0100) x86_64

The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
root@debian-psql-1:~# apt update -y ; apt upgrade -y ; apt install vim -y

root@debian-psql-1:~# vim /etc/ssh/sshd_config 

[...]

PermitRootLogin yes

[...]

:wq


root@debian-psql-1:~# systemctl restart sshd ; systemctl status sshd
* ssh.service - OpenBSD Secure Shell server
   Loaded: loaded (/lib/systemd/system/ssh.service; enabled; vendor preset: enabled)
   Active: active (running) since Sun 2021-04-04 08:26:41 UTC; 28ms ago
     Docs: man:sshd(8)
           man:sshd_config(5)
  Process: 8819 ExecStartPre=/usr/sbin/sshd -t (code=exited, status=0/SUCCESS)
 Main PID: 8820 (sshd)
    Tasks: 1 (limit: 7372)
   Memory: 1.3M
   CGroup: /system.slice/ssh.service
           `-8820 /usr/sbin/sshd -D

Apr 04 08:26:41 debian-psql-1 systemd[1]: Starting OpenBSD Secure Shell server...
Apr 04 08:26:41 debian-psql-1 sshd[8820]: Server listening on 0.0.0.0 port 22.
Apr 04 08:26:41 debian-psql-1 sshd[8820]: Server listening on :: port 22.
Apr 04 08:26:41 debian-psql-1 systemd[1]: Started OpenBSD Secure Shell server.

Do the same for the second debian host and you get the following:

Now from here we can setup our postgresql Databases however we need to make sure that we have the correct dependencies installed:


root@debian-psql-1:~#  apt-get install libdbix-safe-perl libdbd-pg-perl libboolean-perl build-essential postgresql-plperl postgresql -y

root@debian-psql-2:~#  apt-get install libdbix-safe-perl libdbd-pg-perl libboolean-perl build-essential postgresql-plperl postgresql -y
	

And then check to see that the postgresql service successfully started:

Now on our first debian host we're going to install bucardo:


root@debian-psql-1:~# apt install git -y; git clone https://github.com/bucardo/bucardo /opt/bucardo

root@debian-psql-1:~# ls -lash /opt
total 12K
4.0K drwxr-xr-x  3 root root 4.0K Apr  4 09:29 .
4.0K drwxr-xr-x 22 root root 4.0K Apr  4 08:23 ..
4.0K drwxr-xr-x  8 root root 4.0K Apr  4 09:29 bucardo

root@debian-psql-1:~# ls -lash /opt/bucardo/
total 1.1M
4.0K drwxr-xr-x 8 root root 4.0K Apr  4 09:29 .
4.0K drwxr-xr-x 3 root root 4.0K Apr  4 09:29 ..
4.0K drwxr-xr-x 8 root root 4.0K Apr  4 09:29 .git
4.0K -rw-r--r-- 1 root root  510 Apr  4 09:29 .gitignore
4.0K -rw-r--r-- 1 root root 3.7K Apr  4 09:29 .perlcriticrc
4.0K -rwxr-xr-x 1 root root 1.5K Apr  4 09:29 .travis.sh
4.0K -rw-r--r-- 1 root root 1021 Apr  4 09:29 .travis.yml
392K -rw-r--r-- 1 root root 391K Apr  4 09:29 Bucardo.pm
4.0K -rw-r--r-- 1 root root 2.5K Apr  4 09:29 Bucardo.pm.html
 40K -rw-r--r-- 1 root root  38K Apr  4 09:29 Changes
4.0K -rw-r--r-- 1 root root 1.7K Apr  4 09:29 INSTALL
4.0K -rw-r--r-- 1 root root 1.3K Apr  4 09:29 LICENSE
4.0K -rw-r--r-- 1 root root  885 Apr  4 09:29 MANIFEST
4.0K -rw-r--r-- 1 root root  369 Apr  4 09:29 MANIFEST.SKIP
4.0K -rw-r--r-- 1 root root 1.4K Apr  4 09:29 META.yml
4.0K -rw-r--r-- 1 root root 3.9K Apr  4 09:29 Makefile.PL
4.0K -rw-r--r-- 1 root root 1.5K Apr  4 09:29 README
 12K -rw-r--r-- 1 root root  12K Apr  4 09:29 README.dev
8.0K -rw-r--r-- 1 root root 6.2K Apr  4 09:29 SIGNATURE
8.0K -rw-r--r-- 1 root root 5.1K Apr  4 09:29 TODO
4.0K -rw-r--r-- 1 root root 2.2K Apr  4 09:29 UPGRADE
356K -rwxr-xr-x 1 root root 356K Apr  4 09:29 bucardo
 80K -rw-r--r-- 1 root root  77K Apr  4 09:29 bucardo.html
100K -rw-r--r-- 1 root root  99K Apr  4 09:29 bucardo.schema
4.0K drwxr-xr-x 2 root root 4.0K Apr  4 09:29 dev
4.0K drwxr-xr-x 2 root root 4.0K Apr  4 09:29 dist
4.0K drwxr-xr-x 2 root root 4.0K Apr  4 09:29 patches
4.0K drwxr-xr-x 2 root root 4.0K Apr  4 09:29 scripts
4.0K drwxr-xr-x 2 root root 4.0K Apr  4 09:29 t
	

Now compile it like so:


root@debian-psql-1:~# cd /opt/bucardo/

root@debian-psql-1:/opt/bucardo# ls
Bucardo.pm       INSTALL   MANIFEST.SKIP  README      TODO     bucardo.html    dist     t
Bucardo.pm.html  LICENSE   META.yml       README.dev  UPGRADE  bucardo.schema  patches
Changes          MANIFEST  Makefile.PL    SIGNATURE   bucardo  dev             scripts

root@debian-psql-1:/opt/bucardo# perl Makefile.PL
Checking if your kit is complete...
Looks good
Warning: prerequisite CGI 0 not found.
Warning: prerequisite Encode::Locale 0 not found.
Generating a Unix-style Makefile
Writing Makefile for Bucardo
Writing MYMETA.yml and MYMETA.json

root@debian-psql-1:/opt/bucardo# make
cp bucardo.schema blib/share/bucardo.schema
cp Bucardo.pm blib/lib/Bucardo.pm
cp bucardo blib/script/bucardo
"/usr/bin/perl" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/bucardo
Manifying 1 pod document
Manifying 1 pod document

root@debian-psql-1:/opt/bucardo# make install
Manifying 1 pod document
Manifying 1 pod document
Installing /usr/local/share/perl/5.28.1/Bucardo.pm
Installing /usr/local/man/man1/bucardo.1pm
Installing /usr/local/man/man3/Bucardo.3pm
Installing /usr/local/bin/bucardo
Installing /usr/local/share/bucardo/bucardo.schema
Appending installation info to /usr/local/lib/x86_64-linux-gnu/perl/5.28.1/perllocal.pod

root@debian-psql-1:/opt/bucardo# mkdir /var/run/bucardo

root@debian-psql-1:/opt/bucardo# mkdir /var/log/bucardo

root@debian-psql-1:/opt/bucardo# which bucardo
/usr/local/bin/bucardo

And here we have our bucardo binary installed in our PATH. We can get more info about it as follows:


root@debian-psql-1:/opt/bucardo# bucardo -version
bucardo version 5.6.0

root@debian-psql-1:/opt/bucardo# bucardo -help
Usage:
      bucardo [<options>] <command> [<action>] [<command-options>] [<command-params>]

Commands:
    Run "bucardo help <command>" for additional details

    "install"
        Installs the Bucardo configuration database.

    "upgrade"
        Upgrades the Bucardo configuration database to the latest schema.

    "start [<start options>] [<reason>]"
        Starts Bucardo.

    "stop [<reason>]"
        Stops Bucardo.

    "restart [<start options>] [<reason>]"
        Stops and starts Bucardo.

[...]
	

Now once we installled bucardo on our second debian host in the same way, we can move on to setup the PostgreSQL Databases:

Setting up the Databases



Now let's enter a postgresql shell to make sure bucardo can access our databases:


root@debian-psql-1:/opt/bucardo# cd ~
root@debian-psql-1:~# su - postgres
postgres@debian-psql-1:~$ psql
psql (11.11 (Debian 11.11-0+deb10u1))
Type "help" for help.

postgres=# CREATE USER bucardo SUPERUSER PASSWORD 'P@SSW0RD';
CREATE ROLE
postgres=# CREATE DATABASE bucardo_db;
CREATE DATABASE
postgres=# GRANT ALL ON DATABASE bucardo_db TO bucardo;
GRANT
postgres=# ALTER USER postgres PASSWORD 'P@SSW0RD';
ALTER ROLE
postgres-# \q
postgres@debian-psql-1:~$

Do the same on the second debian host:


root@debian-psql-2:/opt/bucardo# cd ~
root@debian-psql-2:~# su - postgres
postgres@debian-psql-2:~$ psql
psql (11.11 (Debian 11.11-0+deb10u1))
Type "help" for help.

postgres=# CREATE USER bucardo SUPERUSER PASSWORD 'P@SSW0RD';
CREATE ROLE
postgres=# CREATE DATABASE bucardo_db;
CREATE DATABASE
postgres=# GRANT ALL ON DATABASE bucardo_db TO bucardo;
GRANT
postgres=# ALTER USER postgres PASSWORD 'P@SSW0RD';
ALTER ROLE
postgres=# \q
postgres@debian-psql-2:~$


	

Now from here our 2 debian hosts are set for replication. if you want to add a third one, it will require all the steps we did above. From here we will focus on our first debian host (at the 10.0.0.161 IP address). We need to edit the ~/.pgpass file as follows:


postgres@debian-psql-1:~$ vim ~/.pgpass
	

10.0.0.161:5432:*:postgres:P@SSW0RD
10.0.0.161:5432:*:bucardo:P@SSW0RD

10.0.0.162:5432:*:postgres:P@SSW0RD
10.0.0.162:5432:*:bucardo:P@SSW0RD

:wq to save and quit out of vim and keep the 0600 permissions on the file we just edited:


postgres@debian-psql-1:~$ vim ~/.pgpass
postgres@debian-psql-1:~$ chmod 0600 ~/.pgpass
postgres@debian-psql-1:~$ ls -lash ~/.pgpass
4.0K -rw------- 1 postgres postgres 63 Apr  4 10:15 /var/lib/postgresql/.pgpass
	

Now obviously, if you had a third debian host to replicate, you would add it in ~/.pgpass accordingly aswell. Next we will run the bucardo install command:


postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 install
This will install the bucardo database into an existing Postgres cluster.
Postgres must have been compiled with Perl support,
and you must connect as a superuser

Current connection settings:
1. Host:           10.0.0.161
2. Port:           5432
3. User:           bucardo
4. Database:       bucardo
5. PID directory:  /var/run/bucardo
Enter a number to change it, P to proceed, or Q to quit: p

-->Sorry, unable to connect to the database

If this error is there, it probably means that postgresql is only listening on localhost (127.0.0.1/8) to fix this we need to edit /etc/postgresql/11/main/postgresql.conf:


psql: could not connect to server: Connection refused
        Is the server running on host "10.0.0.161" and accepting
        TCP/IP connections on port 5432?
Current connection settings:
1. Host:           10.0.0.161
2. Port:           5432
3. User:           bucardo
4. Database:       bucardo
5. PID directory:  /var/run/bucardo
Enter a number to change it, P to proceed, or Q to quit: q

Goodbye!
	
postgres@debian-psql-1:~$ vim /etc/postgresql/11/main/postgresql.conf

Be careful you need to edit this file using the postgres account, if you edit it using the root you may potentially fuck up postgresql's config files permissions, and thus potentially require you to revert the original file pernissions, so edit postgresql's config files with the postgres user and proceed:


#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '10.0.0.161'         # what IP address(es) to listen on (NOT LOCALHOST !!!);
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
                                        # (change requires restart)

:wq to save and quit out of vim, and then restart the postgres service after our config changes:


postgres@debian-psql-1:~$ vim /etc/postgresql/11/main/postgresql.conf
postgres@debian-psql-1:~$ exit
logout
root@debian-psql-1:~# systemctl restart postgresql
root@debian-psql-1:~# systemctl status postgresql
* postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Sun 2021-04-04 10:33:20 UTC; 4s ago
  Process: 18112 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 18112 (code=exited, status=0/SUCCESS)

Apr 04 10:33:20 debian-psql-1 systemd[1]: Starting PostgreSQL RDBMS...
Apr 04 10:33:20 debian-psql-1 systemd[1]: Started PostgreSQL RDBMS.
	

Now from here, postgresql is correctly listening to connections to the 10.0.0.161 ip address, or so we thought:


root@debian-psql-1:~# su - postgres
postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 install
This will install the bucardo database into an existing Postgres cluster.
Postgres must have been compiled with Perl support,
and you must connect as a superuser

Current connection settings:
1. Host:           10.0.0.161
2. Port:           5432
3. User:           bucardo
4. Database:       bucardo
5. PID directory:  /var/run/bucardo
Enter a number to change it, P to proceed, or Q to quit: p

-->Sorry, unable to connect to the database

psql: FATAL:  no pg_hba.conf entry for host "10.0.0.161", user "bucardo", database "bucardo", SSL on
FATAL:  no pg_hba.conf entry for host "10.0.0.161", user "bucardo", database "bucardo", SSL off
	

That means we forgot to edit the pg_hba.conf file! Let's edit it right away, STILL as the postgres user:


Enter a number to change it, P to proceed, or Q to quit: q

Goodbye!
postgres@debian-psql-1:~$ vim /etc/postgresql/11/main/pg_hba.conf
	

In our usecase, we want to allow postgresql connections from our LAN subnet which is 10.0.0.0/16:


# IPv4 local connections:
#host    all             all             127.0.0.1/32            md5
host    all             bucardo         127.0.0.1/32            trust
host    all             bucardo         10.0.0.0/16             trust
host    all             all             10.0.0.0/16             md5

	

:wq to save and quit, then reload postgresql once again:


postgres@debian-psql-1:~$ vim /etc/postgresql/11/main/pg_hba.conf
postgres@debian-psql-1:~$ exit
logout
root@debian-psql-1:~# systemctl restart postgresql ;systemctl status postgresql
* postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Sun 2021-04-04 10:37:47 UTC; 18ms ago
  Process: 18177 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 18177 (code=exited, status=0/SUCCESS)

Apr 04 10:37:47 debian-psql-1 systemd[1]: Starting PostgreSQL RDBMS...
Apr 04 10:37:47 debian-psql-1 systemd[1]: Started PostgreSQL RDBMS.
	

Then try bucardo's install once again:


postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 install
This will install the bucardo database into an existing Postgres cluster.
Postgres must have been compiled with Perl support,
and you must connect as a superuser

Current connection settings:
1. Host:           10.0.0.161
2. Port:           5432
3. User:           bucardo
4. Database:       bucardo
5. PID directory:  /var/run/bucardo
Enter a number to change it, P to proceed, or Q to quit: p

Failed to connect to database 'bucardo', will try 'postgres'
Current connection settings:
1. Host:           10.0.0.161
2. Port:           5432
3. User:           bucardo
4. Database:       postgres
5. PID directory:  /var/run/bucardo
Enter a number to change it, P to proceed, or Q to quit: p

Attempting to create and populate the bucardo database and schema
Database creation is complete

Updated configuration setting "piddir"
Installation is now complete.
If you see errors or need help, please email bucardo-general@bucardo.org

You may want to check over the configuration variables next, by running:
bucardo show all
Change any setting by using: bucardo set foo=bar

postgres@debian-psql-1:~$
	

Here we see that bucardo managed to populate the bucardo database and schema via the bucardo user and postgres database. Now let's make sure that our second host's postgresql database is reachable from 10.0.0.0/16 just like we did for our first host:


root@debian-psql-2:~# su - postgres
postgres@debian-psql-2:~$ vim /etc/postgresql/11/main/postgresql.conf
	

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '10.0.0.162'         # what IP address(es) to listen on (NOT LOCALHOST!);
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories

:wq

postgres@debian-psql-2:~$ vim /etc/postgresql/11/main/pg_hba.conf


# IPv4 local connections:
#host    all             all             127.0.0.1/32            md5
host    all             bucardo         127.0.0.1/32            trust
host    all             bucardo         10.0.0.0/16             trust
host    all             all             10.0.0.0/16             md5


:wq

postgres@debian-psql-2:~$ exit
logout
root@debian-psql-2:~# systemctl restart postgresql ; systemctl status postgresql
* postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Sun 2021-04-04 10:44:49 UTC; 13ms ago
  Process: 18044 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 18044 (code=exited, status=0/SUCCESS)

Apr 04 10:44:49 debian-psql-2 systemd[1]: Starting PostgreSQL RDBMS...
Apr 04 10:44:49 debian-psql-2 systemd[1]: Started PostgreSQL RDBMS.
root@debian-psql-2:~#

Once that's done we can test the remote postgresql database connection like so:


postgres@debian-psql-1:~$ psql -h 10.0.0.162 -U bucardo -d postgres
psql (11.11 (Debian 11.11-0+deb10u1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#
	

We see that it is indeed working! so let's continue by copying the schema from our first host (10.0.0.161) to our second host (10.0.0.162):


postgres=# exit

postgres@debian-psql-1:~$ psql -h 10.0.0.161 -U bucardo template1 -c "drop database if exists btest;"
DROP DATABASE
postgres@debian-psql-1:~$ psql -h 10.0.0.161 -U bucardo template1 -c "create database btest owner bucardo;"
CREATE DATABASE

postgres@debian-psql-1:~$ psql -h 10.0.0.162 -U bucardo template1 -c "drop database if exists btest;"
DROP DATABASE
postgres@debian-psql-1:~$ psql -h 10.0.0.162 -U bucardo template1 -c "create database btest owner bucardo;"
CREATE DATABASE

postgres@debian-psql-1:~$ pg_dump -U bucardo --schema-only -h 10.0.0.161 btest | psql -U bucardo -h 10.0.0.162 btest
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET

Now here we're supposed to have the same btest database on both postgresql hosts accessible by the bucardo user. The next part is the most important one. We need to make sure that our bucardo configuration contains both databases:


postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 -U bucardo add db main1 db=btest user=bucardo pass=P@SSW0RD host=10.0.0.161
Added database "main1"
postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 -U bucardo add db main2 db=btest user=bucardo pass=P@SSW0RD host=10.0.0.162
Added database "main2"

postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 -U bucardo list dbs
Database: main1  Status: active  Conn: psql -U bucardo -d btest -h 10.0.0.161
Database: main2  Status: active  Conn: psql -U bucardo -d btest -h 10.0.0.162

Now that's done, we sync all tables if there are any:


postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 -U bucardo add all tables db=main1 relgroup=btest_relgroup
Sorry, no tables were found
Creating relgroup: btest_relgroup
New tables added: 0

Here is the core of our tutorial, this is where we can setup a Master-Slave replication:


bucardo -h 10.0.0.161 -U bucardo add dbgroup btest_dbgroup main1:source main2:target
	

Or as we initially wanted, a Master-Master replication:


postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 -U bucardo add dbgroup btest_dbgroup main1:source main2:source
Created dbgroup "btest_dbgroup"
Added database "main1" to dbgroup "btest_dbgroup" as source
Added database "main2" to dbgroup "btest_dbgroup" as source

Then we create the synchronization:


postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 -U bucardo add sync btest_sync dbgroup=btest_dbgroup relgroup=btest_relgroup conflict_strategy=bucardo_source onetimecopy=2 autokick=0
WARNING:  Relgroup has no members: btest_relgroup
Added sync "btest_sync"
	

And then finally we start the bucardo service:



postgres@debian-psql-1:~$ exit
logout
root@debian-psql-1:~# apt install sudo -y ; usermod -aG sudo postgres ; passwd postgres
Reading package lists... Done
Building dependency tree
Reading state information... Done
sudo is already the newest version (1.8.27-1+deb10u3).
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.
New password:
Retype new password:
passwd: password updated successfully
root@debian-psql-1:~# su - postgres
postgres@debian-psql-1:~$ sudo bucardo -h 10.0.0.161 -U bucardo -P random_password start

We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:

    #1) Respect the privacy of others.
    #2) Think before you type.
    #3) With great power comes great responsibility.

[sudo] password for postgres:
Checking for existing processes
Starting Bucardo
postgres@debian-psql-1:~$

And that's it! Burcado has been configured and started.

Testing the Multi-Master Replication



Now to test the replication let's use pgbench to do a bunch of changes to our btest database on our first debian host:


postgres@debian-psql-1:~$ pgbench -i btest
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data...
100000 of 100000 tuples (100%) done (elapsed 0.37 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.

postgres@debian-psql-1:~$ pgbench -t 10000 btest
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 10000/10000
latency average = 1.287 ms
tps = 776.789331 (including connections establishing)
tps = 777.510605 (excluding connections establishing)

Now that's done let's check out the bucardo status


postgres@debian-psql-1:~$ bucardo status -h 10.0.0.161
PID of Bucardo MCP: 18974
 Name         State              Last good    Time    Last I/D    Last bad    Time
============+==================+============+=======+===========+===========+=======
 btest_sync | No records found |            |       |           |           |

postgres@debian-psql-1:~$ psql
psql (11.11 (Debian 11.11-0+deb10u1))
Type "help" for help.

postgres=# \c btest
You are now connected to database "btest" as user "postgres".
btest=# select * from btest;
ERROR:  relation "btest" does not exist
LINE 1: select * from btest;
                      ^
btest=# \l
                              List of databases
    Name    |  Owner   | Encoding  | Collate | Ctype |   Access privileges
------------+----------+-----------+---------+-------+-----------------------
 btest      | bucardo  | SQL_ASCII | C       | C     |
 bucardo    | bucardo  | SQL_ASCII | C       | C     |
 bucardo_db | postgres | SQL_ASCII | C       | C     | =Tc/postgres         +
            |          |           |         |       | postgres=CTc/postgres+
            |          |           |         |       | bucardo=CTc/postgres
 postgres   | postgres | SQL_ASCII | C       | C     |
 template0  | postgres | SQL_ASCII | C       | C     | =c/postgres          +
            |          |           |         |       | postgres=CTc/postgres
 template1  | postgres | SQL_ASCII | C       | C     | =c/postgres          +
            |          |           |         |       | postgres=CTc/postgres
(6 rows)

btest=# \dt
              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_history  | table | postgres
 public | pgbench_tellers  | table | postgres
(4 rows)

btest=# select * from pgbench_accounts;

So with this we know that our pgbench command worked. But as we saw earlier we configured the multi master bucardo synchronization without any tables, so now we're going to add the tables we just created thanks to pgbench:


postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 -U bucardo add all tables db=main1 relgroup=btest_relgroup
Added table public.pgbench_accounts to relgroup btest_relgroup
Added table public.pgbench_branches to relgroup btest_relgroup
Added table public.pgbench_tellers to relgroup btest_relgroup
Added table public.pgbench_history to relgroup btest_relgroup
New tables added: 4
postgres@debian-psql-1:~$
	

Now with this we need to update the synchronization itself:


postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 -U bucardo delete sync btest_sync
Removed sync "btest_sync"
Note: table triggers (if any) are not automatically removed!

postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 -U bucardo add all tables db=main1 relgroup=btest_relgroup
New tables added: 0
Already added: 4

postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 -U bucardo add sync btest_sync dbgroup=btest_dbgroup relgroup=btest_relgroup conflict_strategy=bucardo_source onetimecopy=2 autokick=0
Failed to add sync: DBD::Pg::st execute failed: ERROR:  Table "public.pgbench_history" must specify a primary key! at line 119. at line 30.
CONTEXT:  PL/Perl function "validate_sync" at /usr/local/bin/bucardo line 4671.
	

If you get this annoying pgbench history primary key error do the following:


postgres@debian-psql-1:~$ psql
psql (11.11 (Debian 11.11-0+deb10u1))
Type "help" for help.

postgres=# \c btest
You are now connected to database "btest" as user "postgres".
btest=# drop table public.pgbench_history;
DROP TABLE
btest=# exit

postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 -U bucardo delete all tables
Are you sure you want to remove all tables? y
Removed the following tables:
  public.pgbench_accounts
  public.pgbench_branches
  public.pgbench_history
  public.pgbench_tellers

postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 -U bucardo add all tables db=main1 relgroup=btest_relgroup
Added table public.pgbench_accounts to relgroup btest_relgroup
Added table public.pgbench_branches to relgroup btest_relgroup
Added table public.pgbench_tellers to relgroup btest_relgroup
New tables added: 3

Then make sure that both our database schemas are essentially the sqme like we previously did:



postgres@debian-psql-1:~$ pg_dump -U bucardo --schema-only -h 10.0.0.161 btest | psql -U bucardo -h 10.0.0.162 btest
	

and then validate the synchronization:


postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 -U bucardo add sync btest_sync dbgroup=btest_dbgroup relgroup=btest_relgroup conflict_strategy=bucardo_source onetimecopy=2 autokick=0
Added sync "btest_sync"
	

postgres@debian-psql-1:~$ sudo bucardo -h 10.0.0.161 -U bucardo -P random_password stop
[sudo] password for postgres:
Creating /var/run/bucardo/fullstopbucardo ... Done

postgres@debian-psql-1:~$ sudo bucardo -h 10.0.0.161 -U bucardo -P P@SSW0RD start
Checking for existing processes
Removing file "/var/run/bucardo/fullstopbucardo"
Starting Bucardo

Now what's left for us to do is to check if the replication is actually working. to do so we're going to add some values to one of the replicated tables :


postgres@debian-psql-1:~$ bucardo list dbs -h 10.0.0.161
Database: main1  Status: active  Conn: psql -U bucardo -d btest -h 10.0.0.161
Database: main2  Status: active  Conn: psql -U bucardo -d btest -h 10.0.0.162

postgres@debian-psql-1:~$ bucardo list tables -h 10.0.0.161
5. Table: public.pgbench_accounts  DB: main1  PK: aid (integer)
6. Table: public.pgbench_branches  DB: main1  PK: bid (integer)
7. Table: public.pgbench_tellers   DB: main1  PK: tid (integer)

postgres@debian-psql-1:~$ bucardo status -h 10.0.0.161
PID of Bucardo MCP: 19267
 Name         State              Last good    Time    Last I/D    Last bad    Time
============+==================+============+=======+===========+===========+=======
 btest_sync | No records found |            |       |           |           |
postgres@debian-psql-1:~$ psql
psql (11.11 (Debian 11.11-0+deb10u1))
Type "help" for help.

postgres=# \c btest;
You are now connected to database "btest" as user "postgres".

btest=# select * from public.pgbench_accounts where aid = '1' or aid = '101';
 aid | bid | abalance |                                        filler
-----+-----+----------+--------------------------------------------------------------------------------------
   1 |   1 |        0 |
 101 |   1 |        0 |
(2 rows)
	

Before we continue let's check if we have the same rows on the second debian host:


root@debian-psql-2:~# su - postgres
postgres@debian-psql-2:~$ psql
psql (11.11 (Debian 11.11-0+deb10u1))
Type "help" for help.

postgres=# \c btest;
You are now connected to database "btest" as user "postgres".
btest=# select * from public.pgbench_accounts where aid = '1' or aid = '101';
 aid | bid | abalance | filler
-----+-----+----------+--------
(0 rows)


btest=# \dt
              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_tellers  | table | postgres
(3 rows)
	

And we don't ! We have the same tables, now let's update the sync to have autockick on any changes as well as having the latest tables conflict_strategy:


postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 -U bucardo update sync btest_sync autokick=1

postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 -U bucardo update sync btest_sync conflict_strategy=bucardo_latest_all_tables
Set conflict strategy to 'bucardo_latest_all_tables'

postgres@debian-psql-1:~$ bucardo -h 10.0.0.161 -U bucardo reload config
Forcing Bucardo to reload the bucardo_config table
DONE!

postgres@debian-psql-1:~$ bucardo status -h 10.0.0.161
PID of Bucardo MCP: 19267
 Name         State    Last good    Time      Last I/D    Last bad    Time
============+========+============+=========+===========+===========+=======
 btest_sync | Good   | 12:23:49   | 58m 13s | 0/0       | none      |


Now from here let's see if adding a row from the second database (10.0.0.162:5432) also appears on the first database (10.0.0.161:5432)



postgres@debian-psql-2:~$ psql
psql (11.11 (Debian 11.11-0+deb10u1))
Type "help" for help.

postgres=# \c btest;
You are now connected to database "btest" as user "postgres".
btest=# \dt
              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_tellers  | table | postgres
(3 rows)

btest=# select * from pgbench_accounts;
 aid | bid | abalance | filler
-----+-----+----------+--------
(0 rows)

btest=# insert into pgbench_accounts(aid,bid,abalance) values (99999,9,99);
INSERT 0 1

btest=# select * from pgbench_accounts;
  aid  | bid | abalance | filler
-------+-----+----------+--------
 99999 |   9 |       99 |
(1 row)

btest=#

let's see if it appears on the first database:


postgres@debian-psql-1:~$ psql
psql (11.11 (Debian 11.11-0+deb10u1))
Type "help" for help.

postgres=# \c btest;
You are now connected to database "btest" as user "postgres".
btest=# select * from pgbench_accounts where aid = 99999;
  aid  | bid | abalance | filler
-------+-----+----------+--------
 99999 |   9 |       99 |
(1 row)
	

And it appeared ! So now we verified the Master2 -> Master1 replication, let's now verify the Master1 -> Master2 replication by adding a row with the same method:


btest=# insert into pgbench_accounts(aid,bid,abalance) values (888888,8,88);
INSERT 0 1
	

Check if it appears on the second database (10.0.0.162:5432):



btest=# select * from pgbench_accounts;
  aid  | bid | abalance | filler
-------+-----+----------+--------
 99999 |   9 |       99 |
(1 row)

btest=# select * from pgbench_accounts;
  aid   | bid | abalance | filler
--------+-----+----------+--------
  99999 |   9 |       99 |
 888888 |   8 |       88 |
(2 rows)
	

And that's it! We have been able to make a Master-Master replication connection between 2 PostgreSQL hosts.

Nihilism

Until there is Nothing left.



Creative Commons Zero: No Rights Reserved

About nihilist

Donate XMR: 8AUYjhQeG3D5aodJDtqG499N5jXXM71gYKD8LgSsFB9BUV1o7muLv3DXHoydRTK4SZaaUBq4EAUqpZHLrX2VZLH71Jrd9k8


Contact: nihilist@contact.nowhere.moe (PGP)