23

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?

Sachin Verma
  • 789
  • 4
  • 9
  • 15

3 Answers3

32

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

András Váczi
  • 31,278
  • 13
  • 101
  • 147
Sachin Verma
  • 789
  • 4
  • 9
  • 15
  • do we need to create a replication database? or is it in-built?? – gimibarak Oct 13 '15 at 15:04
  • It's not inbuilt and you have to setup replication. Here's the reference: https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps – Sachin Verma Oct 14 '15 at 04:31
  • 1
    Note that this is not the case for logical replication. – mlissner Apr 24 '19 at 07:07
6

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!

mlissner
  • 543
  • 1
  • 6
  • 18
  • 2
    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' – Alf47 Jun 20 '19 at 22:11
  • oh man, i was going crazy on this... thank you for this comment. – gtato Jun 30 '23 at 09:22
4

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

András Váczi
  • 31,278
  • 13
  • 101
  • 147
Rogerlr
  • 441
  • 4
  • 14