This article walks us through the process of installing and configuring two Postgres servers for stream replication. “Stream” replication allows us to replicate all databases from the master to the/a slave. There’s another kind of replication called “Logical” replication, that will replicate “individual” databases on the master to the slave.
The result of replication is that a new database created on the master, or changes to existing databases on the master, will be instantly sent over to the slave (replica).
We’re using Ubuntu 22.04 LTS. The postgres version is 14. Both servers are on the same network. We’re doing this in a lab with no need for backups as there is no data on these servers. But if you have data, take appropriate actions to ensure the safety of your data.
Our database servers have the following names. This gives us context throughout the walk-through:
- master.example.local
- slave.example.local
Run these commands on both servers (master and slave).
Install the Postgres server and client software:
apt update apt install postgresql postgresql-client ufw
Edit the following file:
/etc/postgresql/14/main/postgresql.conf
Uncomment the “listen_addresses ” option and make sure it looks like this:
# Listen on all interfaces listen_addresses = '*'
Enable and restart Postgres:
systemctl enable postgresql systemctl restart postgresql
Open the firewall ports on both servers. Take these steps with care. Be mindful of your situation with the firewall. Ie, don’t lock yourself out:
ufw status ufw allow ssh ufw allow postgres ufw enable ufw status
Run these commands on the master.
Create the replication user. This is the user (or role) that will be used by the client to login. Obviously change the password (and possibly the username) to something better. Here’s how it looks:
# su - postgres $ psql postgres=# CREATE ROLE repuser WITH REPLICATION PASSWORD 'mypass' LOGIN; \q $ exit
The above means: a) change from the root user to the postgres user. b) run the psql command. c) create the new user (role) with the specified password. d) Quit the DB session. e) return to the root user. I’ve kept the “$” and “#” symbols for context.
Edit the following file to allow access to the master from the slave:
/etc/postgresql/14/main/pg_hba.conf
Add the following line to the bottom of this configuration file:
# Allow replication host replication repuser slave.example.local/32 md5
The above re-uses the same username as created earlier. Keep them the same.
Restart the Postgres master server:
systemctl restart postgresql
Run these commands on the slave.
Remove everything inside the postgres database directory. First stop Postgres and then remove the content, and then restart postgres. Note that this is damaging and anything that exists in that location will be removed. Take backups if you need.
systemctl stop postgresql rm -rf /var/lib/postgresql/14/main/* systemctl start postgresql
Use the “pg_basebackup” tool to initiate the replication process. Again we’re using the username “repuser”:
sudo -u postgres pg_basebackup -h master.example.local -U repuser -D /var/lib/postgresql/14/main/ -Fp -Xs -R -C
Now you can create a database on the master and instantly see it on the slave.
Promote the slave to master.
This is a demonstration only. Use this in a lab. Take care in production to ensure you have backups, etc.
In the event of a disaster or there’s a need to decommission the master, the slave can be promoted to the master role.
Turn off the master:
systemctl stop postgresql
Promote the slave:
su - postgres /usr/lib/postgresql/14/bin/pg_ctl promote -D 14/main
Verify this by trying to create a new database on the newly promoted mater (was the slave).
Building a new slave to replace the old slave that was promoted to master.
Ok, so you’ve removed your old master, promoted your old slave to be your new master, and now you need a new slave to keep the replication happening. We’ve already covered every step in the above content, so we can simply re-apply those steps but on new/different servers.
The only issue I had was that I needed to reset my “repuser” password on the new master. That was as easy as:
psotgres=# ALTER ROLE repuser PASSWORD 'mypass';
References:
- https://www.digitalocean.com/community/tutorials/how-to-set-up-physical-streaming-replication-with-postgresql-12-on-ubuntu-20-04
- https://ubuntu.com/server/docs/databases-postgresql
- https://www.postgresql.org/message-id/CABUevExRKrT%2B7bLMPC7uyCXsZ%2B8Q89-yBKnn3vWiHxPHVNt0hg%40mail.gmail.com
