Postgresql doesn't accept replication connection

Question

Plain old streaming replication. PostgreSQL: 9.2.7 Windows 8.1 64 bit

My primary and secondary clusters are on same windows machine. I have already done pg_start_backup() and everything, so both nodes have exact same data.

Now the problem with replication is "replication connection" from slave server do not connect to primary server but I can connect using same params using psql shell. What I think culprit is the connection string in slave's recovery.conf:

primary_conninfo = 'host = 127.0.0.1 port = 5432 user = postgres password = postgres'

I tried localhost, 0.0.0.0, lan IP everything but pg log says:

 FATAL: could not connect to the primary server: FATAL: no pg_hba.conf entry for replication connection from host "127.0.0.1", user "postgres", SSL off

Now look at my Master's pg_hba.conf:

host all all 0.0.0.0/0 trust
host all postgres 127.0.0.1/0 trust
# IPv6 local connections:
host all all ::1/128 md5
hostnossl all postgres 127.0.0.1/32 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host replication postgres 127.0.0.1/32 md5
#host replication postgres ::1/128 md5

It like I allowed every possible connection yet slave can't connect. Can you do any help?

Answer 1

The database name has to be replication as all does not cover replication connections.

host replication postgres 127.0.0.1/0 trust

The PostgreSQL documentation further says:

The value replication specifies that the record matches if a replication connection is requested (note that replication connections do not specify any particular database). Otherwise, this is the name of a specific PostgreSQL database. Multiple database names can be supplied by separating them with commas. A separate file containing database names can be specified by preceding the file name with @.

• giri: do we need to create a replication database? or is it in-built??
• Sachin Verma: It's not inbuilt and you have to setup replication. Here's the reference: digitalocean.com/community/tutorials/…
• mlissner: Note that this is not the case for logical replication.

Answer 2

One other possible solution here that I ran into. If you're doing logical replication and you have the DATABASE set to replication, it won't work. It needs to just get a regular parameter. The replication parameter is for physical replication, not logical replication.

Man, that one took some work to figure out. I hope this helps!

• Alf47: This needs to be upvoted more. I spent hours troubleshooting the wal2json plugin until I found this and this was the right answer for me. It appears that the wal2json plugin uses logical replication so in order to get their example pg_recvlogical command to work I needed to set the pg_hba.conf to use the 'test' database name instead of the keyword 'replication'
• gtato: oh man, i was going crazy on this... thank you for this comment.

Answer 3

Adding the line below to pg_hba.conf and reloading worked for me. Considering the type is 'local', explicitly specifying an address isn't necessary.

# TYPE DATABASE USER ADDRESS METHOD
local replication postgres peer

And remember to pg_ctl reload