Re: Trouble setting up replication - Mailing list pgsql-general

On 10/10/2015 12:02 PM, Chuck Martin wrote:
>
>> On Sep 5, 2015, at 5:37 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>> On 09/05/2015 02:27 PM, Chuck Martin wrote:
>>>> On Sep 5, 2015, at 4:29 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>>>
>>>> On 09/05/2015 11:00 AM, Chuck Martin wrote:
>>
>>>>>
>>>>> I had added to my pg_hba.conf
>>>>>
>>>>> host    replication     rep     64.207.10.121/32   cert
>>>>
>>>>  From the above the only user that can use replication connecting from 64.207.10.121/32 is rep. You did not
specifya -U in your connection above and ran the command as root so pg_basebackup used that as the user, which is the
defaultbehavior: 
>>>>
>>>> http://www.postgresql.org/docs/9.4/interactive/libpq-connect.html#LIBPQ-PARAMKEYWORDS
>>>> "
>>>> user
>>>>
>>>>     PostgreSQL user name to connect as. Defaults to be the same as the operating system name of the user running
theapplication. 
>>>> "
>>>>
>>>> There is no pg_hba entry for database replication and user root so the connection was rejected. To repeat, get out
ofthe habit of running Postgres commands as root, it is not necessary. What matters is the Postgres user you are
connectingas. When using replication, which is what pg_basebackup is doing, you need to connect as a user with
sufficientprivileges: 
>>>>
>>>> http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html
>>>>
>>>> ".. The connection must be made with a superuser or a user having REPLICATION permissions (see Section 20.2), and
pg_hba.confmust explicitly permit the replication connection. .." 
>>>>
>>>> Whatever user you choose to do this with then needs to authorized in pg_hba.conf.
>>>
>>> This is very helpful. I understood that pb_basebackup was for creating a backup for replication purposes, but did
notunderstand that PG needs the same permissions for it as for the replication itself. I ran it as user “postgres” and
notas “rep”. I think I understand my error, but will study the links you included to make sure. 
>>
>> Well the postgres user is a superuser so it will have the permissions to run pg_basebackup. The problem is that your
pg_hba.confdid not authorize the postgres user to connect to the replication 'database' only the rep user. That
mismatchis what needs to corrected, assuming the rep user has replication permissions. 
>>
>>>
>>> Thanks for your help, Adrian. I think I’ll get this working now.
>>>
>>> Chuck
>
> I continue to struggle with this, but have solved many of the problems I caused. I now have the permissions
corrected.But when I run pg_basebackup from the replicant/slave server, it returns an error that the server version
9.3.1is unsupported. 
>
> # pg_basebackup -V
> pg_basebackup (PostgreSQL) 9.2.13
>
> The replicant is on CentOS 7, and the main/master is on CentOS 6. I have installed PostgreSQL 9.3 on both. On the
main/masterserver, I get: 
>
> # pg_basebackup -V
> pg_basebackup (PostgreSQL) 9.3.1
>
> I only find one version of pg_basebackup on the replicant server, but CentOS 7 comes with PG 9.2, so I suspect that
pg_basebackupis left over from that installation. But I’m not sure how to update that. yum update pg_basebackup did not
work.

See John's reply.
>
> Any ideas? Or should I just change gears and execute pg_basebackup on the main/master instead?

Yes, you just need to point it at the -D for the standby assuming that
directory is empty. Also are you sure that Postgres 9.3 is running on
the standby?

>
> Chuck Martin
> Avondale Software
> 123 N. McDonough St.
> Decatur, GA 30030
> 404-373-3116, fax 404-373-4110
> clmartin@theombudsman.com
>
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com