Re: streaming replication not working

On 9/23/2013 9:30 PM, Ray Stell wrote:
>
> On Sep 23, 2013, at 4:47 PM, John DeSoi wrote:
>>
>> You mean on the primary, right?
>
> right
>
>
>> Yes, I have one there. But even if I did not, I would expect to see a
>> connection error in the log on the standby. No error or any
>> indication the streaming replication process is running on the standby.
>
> you're right, I was firing from the hip. sure enough, it does toss an
> error:
> 2013-09-23 18:00:01 EDT,0,authentication FATAL: 28000: no pg_hba.conf
> entry for replication connection from host "xxx", user "repuser", SSL off
>
> I'd guess a firewall issue? What happens with "telnet primary_host
> port" maybe use tcpdump to see what's happening with the traffic?

No, there is a missing line in pg_hba.conf that should look something
like this:

host replication repuser xxx trust

(where "xxx" is the hostname)

See the pg_hba.conf file for more examples. Note that "replication" is
a special database tag and a replicating connection must have one of
these defined as "all" does not match it.

You can use "host", "hostssl" or "hostnossl"; "trust" means that no
password is demanded and for obvious reasons should NOT be used for
other than a local connection that can be trusted implicitly. I prefer
not to use that method for other than local socket connections and then
only on a machine where nobody signs in that is untrusted (e.g. only
admins are permitted general access.) If you are connecting over an
insecure channel or untrusted users are on the machine then consider SSL
to encrypt the traffic and either use md5 for the password or use a
certificate.

You can reload the file without restarting postgres with "pg_ctl -D
data-directory reload"

(where "data-directory" is wherever the data directory that has the
pg_hba.conf file -- and the rest of the base of the data store -- is)

--
Karl Denninger
karl(at)denninger(dot)net
/Cuda Systems LLC/