When you set your instance to allow replication type connection with a user, it happens that sometimes you’d like to test that connection with your user. In this blog post, we’ll go through user creation and setting authorization before testing a connection.
Creating a user for replication 🔗
I don’t like using Postgres users for something different than connecting manually to perform administration tasks. That’s why I like creating a special user for replication (and backups).
In Postgres, the SQL command create user exists but is an alias to create role ... with login. That’s why Postgres always answers CREATE ROLE when you
perform a create user statement.
postgres=# create user replicator with replication password 'secret';
CREATE ROLE
I granted replication permissions while creating the user but you can always
grant them after creation with a grant command.
Authorizing replication connections 🔗
By default, the pg_hba let any user connecting to replication stream locally,
but you need to add some line·s if you want to connect to perform replication (or
backup with WAL streaming) remotely.
I simply added that line :
host replication replicator <my_IP>/32 md5
And (of course) I reloaded my configuration files :
pg_ctlcluster 11 main reload
Testing the replication connection 🔗
Now, you just want to test your replication connection. If you’re trying to perform physical replication, you should perform that command :
psql "dbname=postgres replication=true" -c "IDENTIFY_SYSTEM;" -U replicator -h <your_primary_IP>
The -c part sends a simple command to the instance and exits psql. Here, we
asked for the server to identify itself.
If you want to test logical replication from db1, that command was made for you:
psql "dbname=postgres replication=db1" -c "IDENTIFY_SYSTEM;" -U replicator -h <your_primary_IP>
You’ll find a lot of useful information in the official documentation.