Обсуждение: 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'mtrying 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,
butI 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
automaticallydeployed 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
pointwhen this starts working, I am going to change the user  
from postgres to another without superuser capability.
--
Jay

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
roguesysadmins 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'mtrying 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,
butI have gotten this to work only if I use a "-h 10.10.1.129" option for the command. This only works since another
entryin 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
automaticallydeployed to various servers, I'd prefer to NOT specify each host's IP address both in pg_hba.conf file and
inthe script calling the pg_basebackup command. Oh, and at some point when this starts working, I am going to change
theuser 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

Daniel,

Thanks for the reply. I don't think psql would diagnose this as it wouldn't use a replication connection, just a
standardone. That is unless there is a way to force a replication connection through psql, and I just don't know it. As
itis, 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
entriesin 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
roguesysadmins 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
anotherentry 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
automaticallydeployed to various servers, I'd prefer to NOT specify each host's IP address both in pg_hba.conf file and
inthe script calling the pg_basebackup command. Oh, and at some point when this starts working, I am going to change
theuser 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

Hey, it's worth a try! I've never seen a replication access entry with 'local' instead of 'host'. Could you satisfy it
witha 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
standardone. That is unless there is a way to force a replication connection through psql, and I just don't know it. As
itis, 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
entriesin 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
roguesysadmins 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
anotherentry 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
automaticallydeployed to various servers, I'd prefer to NOT specify each host's IP address both in pg_hba.conf file and
inthe script calling the pg_basebackup command. Oh, and at some point when this starts working, I am going to change
theuser 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

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.

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

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
thatpostgresql 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

I thought you might be correct, Tom, but I double-checked the postgresql.conf file and listen_addresses = "*". I had
forgottento 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
usinga "-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
thatpostgresql 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
>

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
forgottento 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

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
shouldbe 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
thatit 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
thingshave 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
whythis 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
forgottento 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
>

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
couldeasily 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
invokedit, 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
mightbite 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
forgottento 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
>

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
thingshave 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
whythis 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

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.