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
In this tutorial we will be looking at a Multi-Master PostgreSQL Replication solution called 'Bucardo' you can check it out here
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:
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.
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.
Some Address 67120,
Duttlenheim, France.
This cute theme was created to showcase your work in a simple way. Use it wisely.