Postgresql doesn’t accept replication connection

postgresqlreplication

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?

Best Answer

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

Related Question