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)