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
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
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>
-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.