pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedA local replication entry
14+ messages / 6 participants
[nested] [flat]
* A local replication entry
@ 2014-10-29 13:18 John Scalia <jayknowsunix@gmail.com>
2014-10-29 13:43 ` Re: A local replication entry Doiron, Daniel <DoironD@advisory.com>
2014-10-29 13:58 ` Re: A local replication entry Shreeyansh dba <shreeyansh2014@gmail.com>
2016-02-08 20:28 ` Re: A local replication entry kwfarrell <kwfarrell@gmail.com>
0 siblings, 3 replies; 14+ messages in thread
From: John Scalia @ 2014-10-29 13:18 UTC (permalink / raw)
To: pgsql-admin
I think maybe it's just too early in the morning for me, but I can't seem to get a local entry for replication working. I'm trying to create a script for hourly backups using
pg_basebackup, and here's the command I'm using:
/usr/pgsql-9.3/bin/pg_basebackup -D - -Ft -z -c fast -l hourly.backup > backup_file.gz
The entry in the pg_hba.conf file is:
local replication postgres trust
And the error is: FATAL: no pg_hba.conf entry for replication connection from host "[local]", user "postgres", SSL off, but I have gotten this to work only if I use a "-h
10.10.1.129" option for the command. This only works since another entry in the pg_hba.conf satisfies the command.
Do I have an error with the local entry? Or are local entries even allowed for replication? As this has to be automatically deployed to various servers, I'd prefer to NOT specify
each host's IP address both in pg_hba.conf file and in the script calling the pg_basebackup command. Oh, and at some point when this starts working, I am going to change the user
from postgres to another without superuser capability.
--
Jay
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: A local replication entry
2014-10-29 13:18 A local replication entry John Scalia <jayknowsunix@gmail.com>
@ 2014-10-29 13:43 ` Doiron, Daniel <DoironD@advisory.com>
2014-10-29 13:50 ` Re: A local replication entry jayknowsunix@gmail.com
2 siblings, 1 reply; 14+ messages in thread
From: Doiron, Daniel @ 2014-10-29 13:43 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; pgsql-admin
Do you get the same error using psql client?
If so, do you see more connection details in the log? Is your /etc/hosts configured to resolve correctly? (I've had rogue sysadmins configure my servers to resolve to strange things like IPv6)
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of John Scalia
Sent: Wednesday, October 29, 2014 9:19 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] A local replication entry
I think maybe it's just too early in the morning for me, but I can't seem to get a local entry for replication working. I'm trying to create a script for hourly backups using pg_basebackup, and here's the command I'm using:
/usr/pgsql-9.3/bin/pg_basebackup -D - -Ft -z -c fast -l hourly.backup > backup_file.gz
The entry in the pg_hba.conf file is:
local replication postgres trust
And the error is: FATAL: no pg_hba.conf entry for replication connection from host "[local]", user "postgres", SSL off, but I have gotten this to work only if I use a "-h 10.10.1.129" option for the command. This only works since another entry in the pg_hba.conf satisfies the command.
Do I have an error with the local entry? Or are local entries even allowed for replication? As this has to be automatically deployed to various servers, I'd prefer to NOT specify each host's IP address both in pg_hba.conf file and in the script calling the pg_basebackup command. Oh, and at some point when this starts working, I am going to change the user from postgres to another without superuser capability.
--
Jay
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: A local replication entry
2014-10-29 13:18 A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 13:43 ` Re: A local replication entry Doiron, Daniel <DoironD@advisory.com>
@ 2014-10-29 13:50 ` jayknowsunix@gmail.com
2014-10-29 14:02 ` Re: A local replication entry Doiron, Daniel <DoironD@advisory.com>
0 siblings, 1 reply; 14+ messages in thread
From: jayknowsunix@gmail.com @ 2014-10-29 13:50 UTC (permalink / raw)
To: Doiron, Daniel <DoironD@advisory.com>; +Cc: pgsql-admin
Daniel,
Thanks for the reply. I don't think psql would diagnose this as it wouldn't use a replication connection, just a standard one. That is unless there is a way to force a replication connection through psql, and I just don't know it. As it is, the postgres user has no issues getting in with psql, as in "psql csg_db" with no other options specified.
I'm almost certain that my /etc/hosts file is up to date and I see all three of my db servers in it. There are no IPv6 entries in it at any event,
Sent from my iPad
> On Oct 29, 2014, at 9:43 AM, "Doiron, Daniel" <DoironD@advisory.com> wrote:
>
> Do you get the same error using psql client?
>
> If so, do you see more connection details in the log? Is your /etc/hosts configured to resolve correctly? (I've had rogue sysadmins configure my servers to resolve to strange things like IPv6)
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of John Scalia
> Sent: Wednesday, October 29, 2014 9:19 AM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] A local replication entry
>
> I think maybe it's just too early in the morning for me, but I can't seem to get a local entry for replication working. I'm trying to create a script for hourly backups using pg_basebackup, and here's the command I'm using:
>
> /usr/pgsql-9.3/bin/pg_basebackup -D - -Ft -z -c fast -l hourly.backup > backup_file.gz
>
> The entry in the pg_hba.conf file is:
>
> local replication postgres trust
>
> And the error is: FATAL: no pg_hba.conf entry for replication connection from host "[local]", user "postgres", SSL off, but I have gotten this to work only if I use a "-h 10.10.1.129" option for the command. This only works since another entry in the pg_hba.conf satisfies the command.
>
> Do I have an error with the local entry? Or are local entries even allowed for replication? As this has to be automatically deployed to various servers, I'd prefer to NOT specify each host's IP address both in pg_hba.conf file and in the script calling the pg_basebackup command. Oh, and at some point when this starts working, I am going to change the user from postgres to another without superuser capability.
> --
> Jay
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: A local replication entry
2014-10-29 13:18 A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 13:43 ` Re: A local replication entry Doiron, Daniel <DoironD@advisory.com>
2014-10-29 13:50 ` Re: A local replication entry jayknowsunix@gmail.com
@ 2014-10-29 14:02 ` Doiron, Daniel <DoironD@advisory.com>
0 siblings, 0 replies; 14+ messages in thread
From: Doiron, Daniel @ 2014-10-29 14:02 UTC (permalink / raw)
To: jayknowsunix@gmail.com <jayknowsunix@gmail.com>; +Cc: pgsql-admin
Hey, it's worth a try! I've never seen a replication access entry with 'local' instead of 'host'. Could you satisfy it with a host replication postgres 127.0.0.1/32 trust?
-----Original Message-----
From: jayknowsunix@gmail.com [mailto:jayknowsunix@gmail.com]
Sent: Wednesday, October 29, 2014 9:51 AM
To: Doiron, Daniel
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] A local replication entry
Daniel,
Thanks for the reply. I don't think psql would diagnose this as it wouldn't use a replication connection, just a standard one. That is unless there is a way to force a replication connection through psql, and I just don't know it. As it is, the postgres user has no issues getting in with psql, as in "psql csg_db" with no other options specified.
I'm almost certain that my /etc/hosts file is up to date and I see all three of my db servers in it. There are no IPv6 entries in it at any event,
Sent from my iPad
> On Oct 29, 2014, at 9:43 AM, "Doiron, Daniel" <DoironD@advisory.com> wrote:
>
> Do you get the same error using psql client?
>
> If so, do you see more connection details in the log? Is your /etc/hosts configured to resolve correctly? (I've had rogue sysadmins configure my servers to resolve to strange things like IPv6)
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of John Scalia
> Sent: Wednesday, October 29, 2014 9:19 AM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] A local replication entry
>
> I think maybe it's just too early in the morning for me, but I can't seem to get a local entry for replication working. I'm trying to create a script for hourly backups using pg_basebackup, and here's the command I'm using:
>
> /usr/pgsql-9.3/bin/pg_basebackup -D - -Ft -z -c fast -l hourly.backup > backup_file.gz
>
> The entry in the pg_hba.conf file is:
>
> local replication postgres trust
>
> And the error is: FATAL: no pg_hba.conf entry for replication connection from host "[local]", user "postgres", SSL off, but I have gotten this to work only if I use a "-h 10.10.1.129" option for the command. This only works since another entry in the pg_hba.conf satisfies the command.
>
> Do I have an error with the local entry? Or are local entries even allowed for replication? As this has to be automatically deployed to various servers, I'd prefer to NOT specify each host's IP address both in pg_hba.conf file and in the script calling the pg_basebackup command. Oh, and at some point when this starts working, I am going to change the user from postgres to another without superuser capability.
> --
> Jay
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: A local replication entry
2014-10-29 13:18 A local replication entry John Scalia <jayknowsunix@gmail.com>
@ 2014-10-29 13:58 ` Shreeyansh dba <shreeyansh2014@gmail.com>
2014-10-29 14:35 ` Re: A local replication entry John Scalia <jayknowsunix@gmail.com>
2 siblings, 1 reply; 14+ messages in thread
From: Shreeyansh dba @ 2014-10-29 13:58 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
I suspect there is a small modification is required in pg_hba.conf file ::
Remove the '#' and modify the line like :: 'host replication postgres
127.0.0.1/32 trust'
I hope this will help you to resolve the issue
Thanks and Regards
Ajinkya Bangale
www.shreeyansh.com
On Wed, Oct 29, 2014 at 6:48 PM, John Scalia <jayknowsunix@gmail.com> wrote:
> I think maybe it's just too early in the morning for me, but I can't seem
> to get a local entry for replication working. I'm trying to create a script
> for hourly backups using pg_basebackup, and here's the command I'm using:
>
> /usr/pgsql-9.3/bin/pg_basebackup -D - -Ft -z -c fast -l hourly.backup >
> backup_file.gz
>
> The entry in the pg_hba.conf file is:
>
> local replication postgres trust
>
> And the error is: FATAL: no pg_hba.conf entry for replication connection
> from host "[local]", user "postgres", SSL off, but I have gotten this to
> work only if I use a "-h 10.10.1.129" option for the command. This only
> works since another entry in the pg_hba.conf satisfies the command.
>
> Do I have an error with the local entry? Or are local entries even allowed
> for replication? As this has to be automatically deployed to various
> servers, I'd prefer to NOT specify each host's IP address both in
> pg_hba.conf file and in the script calling the pg_basebackup command. Oh,
> and at some point when this starts working, I am going to change the user
> from postgres to another without superuser capability.
> --
> Jay
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: A local replication entry
2014-10-29 13:18 A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 13:58 ` Re: A local replication entry Shreeyansh dba <shreeyansh2014@gmail.com>
@ 2014-10-29 14:35 ` John Scalia <jayknowsunix@gmail.com>
2014-10-29 14:54 ` Re: A local replication entry Doiron, Daniel <DoironD@advisory.com>
2014-10-29 15:05 ` Re: A local replication entry Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 14+ messages in thread
From: John Scalia @ 2014-10-29 14:35 UTC (permalink / raw)
To: Shreeyansh dba <shreeyansh2014@gmail.com>; +Cc: pgsql-admin
Ok, the line now reads:
host replication postgres 127.0.0.1/32 trust
And I performed a "service postgresql-9.3 reload", and it still failed with the same error. Then, I tried again using a "-h 127.0.0.1" option for the pg_basebackup. That complained
"pg_basebackup: could not connect to server: connection refused." So, I did a "netstat -an | grep 5432" and I see that postgresql is not listening on that port for 127.0.0.1.
On 10/29/2014 9:58 AM, Shreeyansh dba wrote:
> I suspect there is a small modification is required in pg_hba.conf file ::
>
> Remove the '#' and modify the line like :: 'host replication postgres 127.0.0.1/32 <http://127.0.0.1/32; trust'
>
> I hope this will help you to resolve the issue
>
> Thanks and Regards
>
> Ajinkya Bangale
> www.shreeyansh.com <http://www.shreeyansh.com;
>
> On Wed, Oct 29, 2014 at 6:48 PM, John Scalia <jayknowsunix@gmail.com <mailto:jayknowsunix@gmail.com>> wrote:
>
> I think maybe it's just too early in the morning for me, but I can't seem to get a local entry for replication working. I'm trying to create a script for hourly backups using
> pg_basebackup, and here's the command I'm using:
>
> /usr/pgsql-9.3/bin/pg_basebackup -D - -Ft -z -c fast -l hourly.backup > backup_file.gz
>
> The entry in the pg_hba.conf file is:
>
> local replication postgres trust
>
> And the error is: FATAL: no pg_hba.conf entry for replication connection from host "[local]", user "postgres", SSL off, but I have gotten this to work only if I use a "-h
> 10.10.1.129" option for the command. This only works since another entry in the pg_hba.conf satisfies the command.
>
> Do I have an error with the local entry? Or are local entries even allowed for replication? As this has to be automatically deployed to various servers, I'd prefer to NOT
> specify each host's IP address both in pg_hba.conf file and in the script calling the pg_basebackup command. Oh, and at some point when this starts working, I am going to
> change the user from postgres to another without superuser capability.
> --
> Jay
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org <mailto:pgsql-admin@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: A local replication entry
2014-10-29 13:18 A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 13:58 ` Re: A local replication entry Shreeyansh dba <shreeyansh2014@gmail.com>
2014-10-29 14:35 ` Re: A local replication entry John Scalia <jayknowsunix@gmail.com>
@ 2014-10-29 14:54 ` Doiron, Daniel <DoironD@advisory.com>
1 sibling, 0 replies; 14+ messages in thread
From: Doiron, Daniel @ 2014-10-29 14:54 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; Shreeyansh dba <shreeyansh2014@gmail.com>; +Cc: pgsql-admin
pg_basebackup -h $HOSTNAME -U replicant -D - -Ft -z -c fast -l hourly.backup > backup_file.gz
instead of –h 127.0.0.1?
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of John Scalia
Sent: Wednesday, October 29, 2014 10:36 AM
To: Shreeyansh dba
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] A local replication entry
Ok, the line now reads:
host replication postgres 127.0.0.1/32 trust
And I performed a "service postgresql-9.3 reload", and it still failed with the same error. Then, I tried again using a "-h 127.0.0.1" option for the pg_basebackup. That complained "pg_basebackup: could not connect to server: connection refused." So, I did a "netstat -an | grep 5432" and I see that postgresql is not listening on that port for 127.0.0.1.
On 10/29/2014 9:58 AM, Shreeyansh dba wrote:
I suspect there is a small modification is required in pg_hba.conf file ::
Remove the '#' and modify the line like :: 'host replication postgres 127.0.0.1/32<http://127.0.0.1/32; trust'
I hope this will help you to resolve the issue
Thanks and Regards
Ajinkya Bangale
www.shreeyansh.com<http://www.shreeyansh.com;
On Wed, Oct 29, 2014 at 6:48 PM, John Scalia <jayknowsunix@gmail.com<mailto:jayknowsunix@gmail.com>> wrote:
I think maybe it's just too early in the morning for me, but I can't seem to get a local entry for replication working. I'm trying to create a script for hourly backups using pg_basebackup, and here's the command I'm using:
/usr/pgsql-9.3/bin/pg_basebackup -D - -Ft -z -c fast -l hourly.backup > backup_file.gz
The entry in the pg_hba.conf file is:
local replication postgres trust
And the error is: FATAL: no pg_hba.conf entry for replication connection from host "[local]", user "postgres", SSL off, but I have gotten this to work only if I use a "-h 10.10.1.129" option for the command. This only works since another entry in the pg_hba.conf satisfies the command.
Do I have an error with the local entry? Or are local entries even allowed for replication? As this has to be automatically deployed to various servers, I'd prefer to NOT specify each host's IP address both in pg_hba.conf file and in the script calling the pg_basebackup command. Oh, and at some point when this starts working, I am going to change the user from postgres to another without superuser capability.
--
Jay
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: A local replication entry
2014-10-29 13:18 A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 13:58 ` Re: A local replication entry Shreeyansh dba <shreeyansh2014@gmail.com>
2014-10-29 14:35 ` Re: A local replication entry John Scalia <jayknowsunix@gmail.com>
@ 2014-10-29 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-10-29 15:35 ` Re: A local replication entry John Scalia <jayknowsunix@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Tom Lane @ 2014-10-29 15:05 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: Shreeyansh dba <shreeyansh2014@gmail.com>; pgsql-admin
John Scalia <jayknowsunix@gmail.com> writes:
> And I performed a "service postgresql-9.3 reload", and it still failed with the same error. Then, I tried again using a "-h 127.0.0.1" option for the pg_basebackup. That complained
> "pg_basebackup: could not connect to server: connection refused." So, I did a "netstat -an | grep 5432" and I see that postgresql is not listening on that port for 127.0.0.1.
Hm, that suggests your problem is with the setting of listen_addresses,
not just what's in pg_hba.conf.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: A local replication entry
2014-10-29 13:18 A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 13:58 ` Re: A local replication entry Shreeyansh dba <shreeyansh2014@gmail.com>
2014-10-29 14:35 ` Re: A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 15:05 ` Re: A local replication entry Tom Lane <tgl@sss.pgh.pa.us>
@ 2014-10-29 15:35 ` John Scalia <jayknowsunix@gmail.com>
2014-10-29 16:20 ` Re: A local replication entry Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 14+ messages in thread
From: John Scalia @ 2014-10-29 15:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Shreeyansh dba <shreeyansh2014@gmail.com>; pgsql-admin
I thought you might be correct, Tom, but I double-checked the postgresql.conf file and listen_addresses = "*". I had forgotten to look to see if netstat reported:
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN
But even with that established, the pg_basebackup using:
/usr/pgsql-9.3/bin/pg_basebackup -D - -h 127.0.0.1 -Ft -z -c fast -l hourly.backup > backup_file.gz or
/usr/pgsql-9.3/bin/pg_basebackup -D - -h 127.0.0.1 -U postgres -Ft -z -c fast -l hourly.backup > backup_file.gz
are still failing with:
pg_basebackup: could not connect to server: FATAL: no pg_hba.conf entry for replication connection from host "127.0.0.1", user "postgres", SSL off
The line from the pg_hba.conf file currently reads:
host replication postgres 127.0.0.1/32 trust
I really don't see where the problem is, and I know I've done a reload after every change in the pg-hba.conf.
On 10/29/2014 11:05 AM, Tom Lane wrote:
> John Scalia <jayknowsunix@gmail.com> writes:
>> And I performed a "service postgresql-9.3 reload", and it still failed with the same error. Then, I tried again using a "-h 127.0.0.1" option for the pg_basebackup. That complained
>> "pg_basebackup: could not connect to server: connection refused." So, I did a "netstat -an | grep 5432" and I see that postgresql is not listening on that port for 127.0.0.1.
> Hm, that suggests your problem is with the setting of listen_addresses,
> not just what's in pg_hba.conf.
>
> regards, tom lane
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: A local replication entry
2014-10-29 13:18 A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 13:58 ` Re: A local replication entry Shreeyansh dba <shreeyansh2014@gmail.com>
2014-10-29 14:35 ` Re: A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 15:05 ` Re: A local replication entry Tom Lane <tgl@sss.pgh.pa.us>
2014-10-29 15:35 ` Re: A local replication entry John Scalia <jayknowsunix@gmail.com>
@ 2014-10-29 16:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-10-29 17:42 ` Re: A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 17:53 ` Re: A local replication entry John Scalia <jayknowsunix@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: Tom Lane @ 2014-10-29 16:20 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: Shreeyansh dba <shreeyansh2014@gmail.com>; pgsql-admin
John Scalia <jayknowsunix@gmail.com> writes:
> I thought you might be correct, Tom, but I double-checked the postgresql.conf file and listen_addresses = "*". I had forgotten to look to see if netstat reported:
> tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN
> But even with that established, the pg_basebackup using:
> /usr/pgsql-9.3/bin/pg_basebackup -D - -h 127.0.0.1 -Ft -z -c fast -l hourly.backup > backup_file.gz or
> /usr/pgsql-9.3/bin/pg_basebackup -D - -h 127.0.0.1 -U postgres -Ft -z -c fast -l hourly.backup > backup_file.gz
> are still failing with:
> pg_basebackup: could not connect to server: FATAL: no pg_hba.conf entry for replication connection from host "127.0.0.1", user "postgres", SSL off
> The line from the pg_hba.conf file currently reads:
> host replication postgres 127.0.0.1/32 trust
> I really don't see where the problem is, and I know I've done a reload after every change in the pg-hba.conf.
Everything that you've said looks fine, so the problem is somewhere you're
not looking :-(. At this point, I'd wonder if the pg_hba.conf file you're
changing is the same one the postmaster is reading. You might try
confirming that directly by inserting a syntactically-incorrect entry and
seeing if the postmaster bleats about it to the postmaster log when you
issue a reload.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: A local replication entry
2014-10-29 13:18 A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 13:58 ` Re: A local replication entry Shreeyansh dba <shreeyansh2014@gmail.com>
2014-10-29 14:35 ` Re: A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 15:05 ` Re: A local replication entry Tom Lane <tgl@sss.pgh.pa.us>
2014-10-29 15:35 ` Re: A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 16:20 ` Re: A local replication entry Tom Lane <tgl@sss.pgh.pa.us>
@ 2014-10-29 17:42 ` John Scalia <jayknowsunix@gmail.com>
2014-10-29 18:35 ` Re: A local replication entry Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 14+ messages in thread
From: John Scalia @ 2014-10-29 17:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Shreeyansh dba <shreeyansh2014@gmail.com>; pgsql-admin
Well, it is using the pg_hba.conf I thought. I edited the pg_hba.conf file and changed the line I thought pg_basebackup should be using from "trust" to "fake_it" and issued a
reload. I saw an "invalid authentication method 'fake_it'" reported in the pg_log/postgresql-Wed.log file. To confirm that it really was using the correct pg_hba.conf file and I
stopped the database and tried to start it again. That start did fail with:
<timestamp> % LOG: invalid authentication method "fake_it"
<timestamp> % CONTEXT: line 101 of configuration file "/var/lib/pgsql/9.3/data/pg_hba.conf"
<timestamp> % FATAL: could not load pg_hba.conf
So, it really is using the correct pg_hba.conf.
Allow me to note, in closing, that this particular VM: csgha2 has been a problem child since it was built, and many things have been broken on it. I don't know if this is another
one of the very hard-to-solve problems or not. I may try this on another of my sandbox systems. Still I need to know why this is failing.
On 10/29/2014 12:20 PM, Tom Lane wrote:
> John Scalia <jayknowsunix@gmail.com> writes:
>> I thought you might be correct, Tom, but I double-checked the postgresql.conf file and listen_addresses = "*". I had forgotten to look to see if netstat reported:
>> tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN
>> But even with that established, the pg_basebackup using:
>> /usr/pgsql-9.3/bin/pg_basebackup -D - -h 127.0.0.1 -Ft -z -c fast -l hourly.backup > backup_file.gz or
>> /usr/pgsql-9.3/bin/pg_basebackup -D - -h 127.0.0.1 -U postgres -Ft -z -c fast -l hourly.backup > backup_file.gz
>> are still failing with:
>> pg_basebackup: could not connect to server: FATAL: no pg_hba.conf entry for replication connection from host "127.0.0.1", user "postgres", SSL off
>> The line from the pg_hba.conf file currently reads:
>> host replication postgres 127.0.0.1/32 trust
>> I really don't see where the problem is, and I know I've done a reload after every change in the pg-hba.conf.
> Everything that you've said looks fine, so the problem is somewhere you're
> not looking :-(. At this point, I'd wonder if the pg_hba.conf file you're
> changing is the same one the postmaster is reading. You might try
> confirming that directly by inserting a syntactically-incorrect entry and
> seeing if the postmaster bleats about it to the postmaster log when you
> issue a reload.
>
> regards, tom lane
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: A local replication entry
2014-10-29 13:18 A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 13:58 ` Re: A local replication entry Shreeyansh dba <shreeyansh2014@gmail.com>
2014-10-29 14:35 ` Re: A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 15:05 ` Re: A local replication entry Tom Lane <tgl@sss.pgh.pa.us>
2014-10-29 15:35 ` Re: A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 16:20 ` Re: A local replication entry Tom Lane <tgl@sss.pgh.pa.us>
2014-10-29 17:42 ` Re: A local replication entry John Scalia <jayknowsunix@gmail.com>
@ 2014-10-29 18:35 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 14+ messages in thread
From: Tom Lane @ 2014-10-29 18:35 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: Shreeyansh dba <shreeyansh2014@gmail.com>; pgsql-admin
John Scalia <jayknowsunix@gmail.com> writes:
> ... So, it really is using the correct pg_hba.conf.
Yeah, sure looks that way.
> Allow me to note, in closing, that this particular VM: csgha2 has been a problem child since it was built, and many things have been broken on it. I don't know if this is another
> one of the very hard-to-solve problems or not. I may try this on another of my sandbox systems. Still I need to know why this is failing.
Interesting, though it doesn't offer any very clear theory for what's
going on here.
The only theory I've got at this point is that somehow the address
matching code is failing to match the incoming connection to the spec
"127.0.0.1/32", perhaps because the platform's libc is filling in the
sockaddr struct weirdly. check_ip() and pg_range_sockaddr() would be
the things to look at if you care to try to trace through the code.
You could also experiment, for instance does 0.0.0.0/0 successfully
match?
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: A local replication entry
2014-10-29 13:18 A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 13:58 ` Re: A local replication entry Shreeyansh dba <shreeyansh2014@gmail.com>
2014-10-29 14:35 ` Re: A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 15:05 ` Re: A local replication entry Tom Lane <tgl@sss.pgh.pa.us>
2014-10-29 15:35 ` Re: A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 16:20 ` Re: A local replication entry Tom Lane <tgl@sss.pgh.pa.us>
@ 2014-10-29 17:53 ` John Scalia <jayknowsunix@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: John Scalia @ 2014-10-29 17:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Shreeyansh dba <shreeyansh2014@gmail.com>; pgsql-admin
OK, weirder yet!!!!
After all this screwing around, and I think this was the first time that I did a "stop", removed the log file so I could easily see the result, and then I issued a "start" on the
database, just for grins, I tried running the pg_basebackup command again. I pulled it from the shell's history and invoked it, and. It ran without a single problem. I'm guessing
now that something was incorrect in memory somewhere, and just reloading the pg_hba.conf wasn't fixing it.
I apologize to all for not thinking to issue a restart before this moment, but now I am a little worried that this might bite us in production.
On 10/29/2014 12:20 PM, Tom Lane wrote:
> John Scalia <jayknowsunix@gmail.com> writes:
>> I thought you might be correct, Tom, but I double-checked the postgresql.conf file and listen_addresses = "*". I had forgotten to look to see if netstat reported:
>> tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN
>> But even with that established, the pg_basebackup using:
>> /usr/pgsql-9.3/bin/pg_basebackup -D - -h 127.0.0.1 -Ft -z -c fast -l hourly.backup > backup_file.gz or
>> /usr/pgsql-9.3/bin/pg_basebackup -D - -h 127.0.0.1 -U postgres -Ft -z -c fast -l hourly.backup > backup_file.gz
>> are still failing with:
>> pg_basebackup: could not connect to server: FATAL: no pg_hba.conf entry for replication connection from host "127.0.0.1", user "postgres", SSL off
>> The line from the pg_hba.conf file currently reads:
>> host replication postgres 127.0.0.1/32 trust
>> I really don't see where the problem is, and I know I've done a reload after every change in the pg-hba.conf.
> Everything that you've said looks fine, so the problem is somewhere you're
> not looking :-(. At this point, I'd wonder if the pg_hba.conf file you're
> changing is the same one the postmaster is reading. You might try
> confirming that directly by inserting a syntactically-incorrect entry and
> seeing if the postmaster bleats about it to the postmaster log when you
> issue a reload.
>
> regards, tom lane
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: A local replication entry
2014-10-29 13:18 A local replication entry John Scalia <jayknowsunix@gmail.com>
@ 2016-02-08 20:28 ` kwfarrell <kwfarrell@gmail.com>
2 siblings, 0 replies; 14+ messages in thread
From: kwfarrell @ 2016-02-08 20:28 UTC (permalink / raw)
To: pgsql-admin
was having the same issue. in the pg_log/enterprisedb-2016-02-08_031257.log
i see this
"Client IP address resolved to "<actual_hostname>", forward lookup not
checked."
So, i modified pg_hba.conf file to this:
host replication postgres <actual_hostname> trust
and then ran the backup command like this;
pg_basebackup -D /mnt/gfs/pgdata/base_backup -h <actual_hostname> -Ft -z
-xlog-method=f -l backup4DB-02 -P -v
that worked.
hope this helps the next son of a bitch with the same issue
--
View this message in context: http://postgresql.nabble.com/A-local-replication-entry-tp5824819p5886429.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2016-02-08 20:28 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-10-29 13:18 A local replication entry John Scalia <jayknowsunix@gmail.com>
2014-10-29 13:43 ` Doiron, Daniel <DoironD@advisory.com>
2014-10-29 13:50 ` jayknowsunix@gmail.com
2014-10-29 14:02 ` Doiron, Daniel <DoironD@advisory.com>
2014-10-29 13:58 ` Shreeyansh dba <shreeyansh2014@gmail.com>
2014-10-29 14:35 ` John Scalia <jayknowsunix@gmail.com>
2014-10-29 14:54 ` Doiron, Daniel <DoironD@advisory.com>
2014-10-29 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-10-29 15:35 ` John Scalia <jayknowsunix@gmail.com>
2014-10-29 16:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-10-29 17:42 ` John Scalia <jayknowsunix@gmail.com>
2014-10-29 18:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-10-29 17:53 ` John Scalia <jayknowsunix@gmail.com>
2016-02-08 20:28 ` kwfarrell <kwfarrell@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox