PostgreSQL Master-Slave Database Replication
In this tutorial we will setup a Master-Slave database replication between 2 debian hosts running PostgreSQL:
In this tutorial we will setup a Master-Slave database replication between 2 debian hosts running PostgreSQL:
First let's install postgresql on our master node:
root@debian-nginx1:~# apt search postgresql-contrib
Sorting... Done
Full Text Search... Done
postgresql-contrib/stable 11+200+deb10u4 all
additional facilities for PostgreSQL (supported version)
root@debian-nginx1:~# apt install postgresql-contrib -y
root@debian-nginx1:~# systemctl status postgresql
* postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Sat 2021-03-20 12:49:47 UTC; 18s ago
Main PID: 13490 (code=exited, status=0/SUCCESS)
Tasks: 0 (limit: 7372)
Memory: 0B
CGroup: /system.slice/postgresql.service
Mar 20 12:49:47 debian-nginx1 systemd[1]: Starting PostgreSQL RDBMS...
Mar 20 12:49:47 debian-nginx1 systemd[1]: Started PostgreSQL RDBMS.
root@debian-nginx1:~# id postgres
uid=108(postgres) gid=116(postgres) groups=116(postgres),102(ssl-cert)
Then we do the same on our slave node:
root@debian-nginx2:~# apt install postgresql-contrib -y
root@debian-nginx2:~# systemctl status postgresql
* postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Sat 2021-03-20 12:52:09 UTC; 16s ago
Main PID: 13366 (code=exited, status=0/SUCCESS)
Tasks: 0 (limit: 7372)
Memory: 0B
CGroup: /system.slice/postgresql.service
Mar 20 12:52:09 debian-nginx2 systemd[1]: Starting PostgreSQL RDBMS...
Mar 20 12:52:09 debian-nginx2 systemd[1]: Started PostgreSQL RDBMS.
root@debian-nginx2:~# id postgres
uid=108(postgres) gid=116(postgres) groups=116(postgres),102(ssl-cert)
Now let's initialize the databases correctly:
root@debian-nginx1:~# mkdir /opt/pgdata
root@debian-nginx1:~# chown postgres. /opt/pgdata/
root@debian-nginx1:~# su - postgres
postgres@debian-nginx1:~$ initdb -D /opt/pgdata/
if the initdb command isn't in your PATH then locate it like so:
root@debian-nginx1:~# apt install mlocate -y ; updatedb ; locate initdb
Reading package lists... Done
Building dependency tree
Reading state information... Done
Suggested packages:
nocache
The following NEW packages will be installed:
mlocate
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 97.7 kB of archives.
After this operation, 504 kB of additional disk space will be used.
Get:1 http://ftp.debian.org/debian buster/main amd64 mlocate amd64 0.26-3 [97.7 kB]
Fetched 97.7 kB in 0s (641 kB/s)
Selecting previously unselected package mlocate.
(Reading database ... 25750 files and directories currently installed.)
Preparing to unpack .../mlocate_0.26-3_amd64.deb ...
Unpacking mlocate (0.26-3) ...
Setting up mlocate (0.26-3) ...
update-alternatives: using /usr/bin/mlocate to provide /usr/bin/locate (locate) in auto mode
Adding group `mlocate' (GID 117) ...
Done.
Processing triggers for man-db (2.8.5-2) ...
/etc/alternatives/initdb.1.gz
/usr/lib/postgresql/11/bin/initdb
/usr/share/locale/cs/LC_MESSAGES/initdb-11.mo
/usr/share/locale/de/LC_MESSAGES/initdb-11.mo
/usr/share/locale/es/LC_MESSAGES/initdb-11.mo
/usr/share/locale/fr/LC_MESSAGES/initdb-11.mo
/usr/share/locale/he/LC_MESSAGES/initdb-11.mo
/usr/share/locale/it/LC_MESSAGES/initdb-11.mo
/usr/share/locale/ja/LC_MESSAGES/initdb-11.mo
/usr/share/locale/ko/LC_MESSAGES/initdb-11.mo
/usr/share/locale/pl/LC_MESSAGES/initdb-11.mo
/usr/share/locale/pt_BR/LC_MESSAGES/initdb-11.mo
/usr/share/locale/ru/LC_MESSAGES/initdb-11.mo
/usr/share/locale/sv/LC_MESSAGES/initdb-11.mo
/usr/share/locale/tr/LC_MESSAGES/initdb-11.mo
/usr/share/locale/vi/LC_MESSAGES/initdb-11.mo
/usr/share/locale/zh_CN/LC_MESSAGES/initdb-11.mo
/usr/share/man/man1/initdb.1.gz
/usr/share/postgresql/11/man/man1/initdb.1.gz
root@debian-nginx1:~#
Here we see that the initdb binary is in /usr/lib/postgresql/11/bin/initdb so we can create a symlink to it:
root@debian-nginx1:~# su - postgres
postgres@debian-nginx1:~$ echo $PATH
/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games
postgres@debian-nginx1:~$ exit
logout
root@debian-nginx1:~# ln -s /usr/lib/postgresql/11/bin/initdb /usr/bin/initdb
root@debian-nginx1:~# su - postgres
postgres@debian-nginx1:~$ initdb -D /opt/pgdata/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /opt/pgdata ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Etc/UTC
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /opt/pgdata/ -l logfile start
postgres@debian-nginx1:~$ exit
logout
root@debian-nginx1:~#
Then restart the database and we see that the /opt/pgdata/ directory contains our database:
root@debian-nginx1:~# systemctl restart postgresql
root@debian-nginx1:~# systemctl status postgresql
* postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Sat 2021-03-20 13:05:33 UTC; 3s ago
Process: 15162 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 15162 (code=exited, status=0/SUCCESS)
Mar 20 13:05:33 debian-nginx1 systemd[1]: Starting PostgreSQL RDBMS...
Mar 20 13:05:33 debian-nginx1 systemd[1]: Started PostgreSQL RDBMS.
root@debian-nginx1:~# ls -lash /opt/pgdata/
total 120K
4.0K drwx------ 19 postgres postgres 4.0K Mar 20 13:03 .
4.0K drwxr-xr-x 3 root root 4.0K Mar 20 12:58 ..
4.0K -rw------- 1 postgres postgres 3 Mar 20 13:03 PG_VERSION
4.0K drwx------ 5 postgres postgres 4.0K Mar 20 13:03 base
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:03 global
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:03 pg_commit_ts
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:03 pg_dynshmem
8.0K -rw------- 1 postgres postgres 4.5K Mar 20 13:03 pg_hba.conf
4.0K -rw------- 1 postgres postgres 1.6K Mar 20 13:03 pg_ident.conf
4.0K drwx------ 4 postgres postgres 4.0K Mar 20 13:03 pg_logical
4.0K drwx------ 4 postgres postgres 4.0K Mar 20 13:03 pg_multixact
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:03 pg_notify
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:03 pg_replslot
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:03 pg_serial
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:03 pg_snapshots
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:03 pg_stat
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:03 pg_stat_tmp
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:03 pg_subtrans
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:03 pg_tblspc
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:03 pg_twophase
4.0K drwx------ 3 postgres postgres 4.0K Mar 20 13:03 pg_wal
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:03 pg_xact
4.0K -rw------- 1 postgres postgres 88 Mar 20 13:03 postgresql.auto.conf
24K -rw------- 1 postgres postgres 24K Mar 20 13:03 postgresql.conf
Now that's done for our Master node, we do the same for our slave node:
root@debian-nginx2:~# mkdir /opt/pgdata
root@debian-nginx2:~# chown postgres. /opt/pgdata/
root@debian-nginx2:~# ln -s /usr/lib/postgresql/11/bin/initdb /usr/bin/initdb
root@debian-nginx2:~#
root@debian-nginx2:~# su - postgres
postgres@debian-nginx2:~$ initdb -D /opt/pgdata/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /opt/pgdata ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Etc/UTC
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /opt/pgdata/ -l logfile start
postgres@debian-nginx2:~$ ls -lash /opt/pgdata/
total 120K
4.0K drwx------ 19 postgres postgres 4.0K Mar 20 13:08 .
4.0K drwxr-xr-x 3 root root 4.0K Mar 20 13:07 ..
4.0K -rw------- 1 postgres postgres 3 Mar 20 13:08 PG_VERSION
4.0K drwx------ 5 postgres postgres 4.0K Mar 20 13:08 base
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:08 global
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:08 pg_commit_ts
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:08 pg_dynshmem
8.0K -rw------- 1 postgres postgres 4.5K Mar 20 13:08 pg_hba.conf
4.0K -rw------- 1 postgres postgres 1.6K Mar 20 13:08 pg_ident.conf
4.0K drwx------ 4 postgres postgres 4.0K Mar 20 13:08 pg_logical
4.0K drwx------ 4 postgres postgres 4.0K Mar 20 13:08 pg_multixact
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:08 pg_notify
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:08 pg_replslot
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:08 pg_serial
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:08 pg_snapshots
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:08 pg_stat
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:08 pg_stat_tmp
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:08 pg_subtrans
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:08 pg_tblspc
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:08 pg_twophase
4.0K drwx------ 3 postgres postgres 4.0K Mar 20 13:08 pg_wal
4.0K drwx------ 2 postgres postgres 4.0K Mar 20 13:08 pg_xact
4.0K -rw------- 1 postgres postgres 88 Mar 20 13:08 postgresql.auto.conf
24K -rw------- 1 postgres postgres 24K Mar 20 13:08 postgresql.conf
postgres@debian-nginx2:~$ exit
logout
root@debian-nginx2:~# 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 Sat 2021-03-20 13:09:47 UTC; 17ms ago
Process: 14923 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 14923 (code=exited, status=0/SUCCESS)
Mar 20 13:09:47 debian-nginx2 systemd[1]: Starting PostgreSQL RDBMS...
Mar 20 13:09:47 debian-nginx2 systemd[1]: Started PostgreSQL RDBMS.
Now that we have both of our debian hosts running postgresql, we can start by configuring the Master node, in this case here it is debian1 (192.168.0.150):
root@debian-nginx1:~# ip a | grep inet
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
inet 192.168.0.150/24 brd 192.168.0.255 scope global eth0
inet6 fe80::1cb1:50ff:fe1d:27dc/64 scope link
root@debian-nginx1:~# su - postgres
postgres@debian-nginx1:~$ psql
psql (11.10 (Debian 11.10-0+deb10u1))
Type "help" for help.
postgres=# create user repl replication login encrypted password 'slavepassword';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
repl | Replication | {}
postgres=# exit
postgres@debian-nginx1:~$ exit
logout
root@debian-nginx1:~#
Now we need to edit the postgresql.conf file as follows:
root@debian-nginx1:~# vim /opt/pgdata/postgresql.conf
[...]
listen_addresses = '*' # what IP address(es) to listen on;
[...]
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'cp -i %p /opt/pgdata/archive/%f' # command to use to archive a logfile segment
[...]
max_wal_senders = 3 # max number of walsender processes
# (change requires restart)
wal_keep_segments = 8 # in logfile segments; 0 disables
[...]
hot_standby = on
[...]
:wq to save, then create the archive directory and then edit the pg_hba.conf file:
root@debian-nginx1:~# mkdir /opt/pgdata/archive
root@debian-nginx1:~# chown postgres. /opt/pgdata/ -R
root@debian-nginx1:~# vim /opt/pgdata/pg_hba.conf
[...]
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 192.168.0.0/24 md5
# Replication
local replication postgres trust
host replication repl 192.168.0.151/32 md5
[...]
:wq to save and quit, then before we restart the postgresql service, we need to make sure that our config files postgresql.conf and pg_hba.conf are used by the real config file /etc/postgresql/11/main/postgresql.conf, however you CANNOT do this with the root user, otherwise you will fuck up the permissions of your postgresql install (to which i don't know how to fix) So make sure you are the postgres user to do the following:
root@debian-nginx1:~# su - postgres
postgres@debian-nginx1:~$ vim /etc/postgresql/11/main/postgresql.conf
[...]
#data_directory = '/var/lib/postgresql/11/main' # use data in another directory
data_directory = '/opt/pgdata'
# (change requires restart)
#hba_file = '/etc/postgresql/11/main/pg_hba.conf' # host-based authentication file
hba_file = '/opt/pgdata/pg_hba.conf'
include '/opt/pgdata/postgresql.conf'
[...]
:wq to save and then return to the root user, to restart postgresql:
postgres@debian-nginx1:~$ vim /etc/postgresql/11/main/postgresql.conf
postgres@debian-nginx1:~$ exit
logout
root@debian-nginx1:~# systemctl restart postgresql
root@debian-nginx1:~# lsof -i :5432
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
postgres 23180 postgres 3u IPv4 329249966 0t0 TCP *:postgresql (LISTEN)
postgres 23180 postgres 5u IPv6 329249967 0t0 TCP *:postgresql (LISTEN)
And here we have it! We have been able to make postgresql listen to *. Now that our master node is configured the way we want, let's configure our slave node (debian 2 at 192.168.0.151):
root@debian-nginx2:~# systemctl stop postgresql
root@debian-nginx2:~# mv /opt/pgdata/ /opt/pgdata_original
root@debian-nginx2:~# mkdir /opt/pgdata
root@debian-nginx2:~# chown postgres. /opt/pgdata -R
root@debian-nginx2:~# chmod 0700 /opt/pgdata -R
root@debian-nginx2:~# su - postgres
postgres@debian-nginx2:~$
Now that we wiped our slave node's /opt/pgdata/ clean we want to make a backup from our master database, to our slave database using the 'repl' user we created earlier, with its password 'slavepassword':
postgres@debian-nginx2:~$ pg_basebackup -h 192.168.0.150 -D /opt/pgdata -U repl -v -P
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/3000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_23244"
56501/56501 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/30000F8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
postgres@debian-nginx2:~$ ls /opt/pgdata
PG_VERSION base pg_dynshmem pg_logical pg_replslot pg_stat pg_tblspc pg_xact
archive global pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_twophase postgresql.auto.conf
backup_label pg_commit_ts pg_ident.conf pg_notify pg_snapshots pg_subtrans pg_wal postgresql.conf
Now that's done, we have basically cloned our master node's configuration files. We have to first edit postgresql.conf:
root@debian-nginx2:~# vim /opt/pgdata/postgresql.conf
[...]
listen_addresses = '*'
[...]
#archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
#archive_command = 'cp -i %p /opt/pgdata/archive/%f' # command to use to archive a logfile segment
[...]
Now that's done, we configure the recovery.conf file:
root@debian-nginx2:~# vim /opt/pgdata/recovery.conf
standby_mode='on'
primary_conninfo='host=192.168.0.150 port=5432 user=repl password=slavepassword'
restore_command='cp //opt/pgdata/archive/%f %p'
trigger_file='/tmp/postgresql.trigger.5432'
Next we fix recovery.conf's permissions and then edit the real postgresql.conf file
root@debian-nginx2:~# chown -R postgres. /opt/pgdata -R
root@debian-nginx2:~# su - postgresql
su: user postgresql does not exist
root@debian-nginx2:~# su - postgres
postgres@debian-nginx2:~$ vim /etc/postgresql/11/main/postgresql.conf
make sure you only do the following changes:
data_directory = '/opt/pgdata' # use data in another directory
include '/opt/pgdata/postgresql.conf'
# (change requires restart)
hba_file = '/etc/postgresql/11/main/pg_hba.conf' # host-based authentication file
# (change requires restart)
ident_file = '/etc/postgresql/11/main/pg_ident.conf' # ident configuration file
# (change requires restart)
Then :wq to save and exit the config file.
Now that everything is configured correctly, restart the postgresql service:
postgres@debian-nginx2:~$ vim /etc/postgresql/11/main/postgresql.conf
postgres@debian-nginx2:~$ exit
logout
root@debian-nginx2:~# systemctl restart postgresql
root@debian-nginx2:~# lsof -i :5432
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
postgres 15768 postgres 3u IPv4 329548233 0t0 TCP *:postgresql (LISTEN)
postgres 15768 postgres 5u IPv6 329548234 0t0 TCP *:postgresql (LISTEN)
postgres 15777 postgres 3u IPv4 329548262 0t0 TCP debian-nginx2.void.yt:45254->192.168.0.150:postgresql (ESTABLISHED)
And there you have it! We have established our postgresql connection. Let's see if it works as intended, lets return to our master node (192.168.0.150):
root@debian-nginx1:~# su - postgres
postgres@debian-nginx1:~$ psql
psql (11.10 (Debian 11.10-0+deb10u1))
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
-------+----------+---------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------
23330 | 16384 | repl | walreceiver | 192.168.0.151 | | 45254 | 2021-03-20 17:03:19.884639+00 | | streaming | 0/4000140 | 0/4000140 | 0/4000140 | 0/4000140 | | | | 0 | async
(1 row)
postgres=# create database "test";
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+-----------+---------+-------+-----------------------
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
test | postgres | SQL_ASCII | C | C |
(4 rows)
So here we created a test database, lets see if we have the same changes on our slave node:
root@debian-nginx2:~# su - postgres
postgres@debian-nginx2:~$ psql
psql (11.10 (Debian 11.10-0+deb10u1))
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+-----------+---------+-------+-----------------------
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
test | postgres | SQL_ASCII | C | C |
(4 rows)
And there we have it! We have been able to create a Master-Slave PostgreSQL Database Replication.
Some Address 67120,
Duttlenheim, France.
This cute theme was created to showcase your work in a simple way. Use it wisely.