Testing replication connections

January 23, 2019
PostgreSQL Administration

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 user 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';

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 your 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 usefull informations in the official documentation.

Where do my Postgres settings come from ?

April 19, 2020
Hacking PostgreSQL Administration

Debian Default Logging Explained

April 4, 2020
Hacking PostgreSQL Debian

Can't connect to Postgres

March 30, 2020
Administration PostgreSQL Troubleshooting