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.