On 10/10/2015 12:02 PM, Chuck Martin wrote:
On Sep 5, 2015, at 5:37 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
On 09/05/2015 02:27 PM, Chuck Martin wrote:
On Sep 5, 2015, at 4:29 PM, Adrian Klaver <adrian.kla...@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 specify a -U in your connection above and
ran the command as root so pg_basebackup used that as the user, which is the
default behavior:
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 the application.
"
There is no pg_hba entry for database replication and user root so the
connection was rejected. To repeat, get out of the habit of running Postgres
commands as root, it is not necessary. What matters is the Postgres user you
are connecting as. When using replication, which is what pg_basebackup is
doing, you need to connect as a user with sufficient privileges:
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.conf must 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 not understand that PG needs the same
permissions for it as for the replication itself. I ran it as user “postgres”
and not as “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.conf did not authorize the
postgres user to connect to the replication 'database' only the rep user. That
mismatch is 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.1
is 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/master server, 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_basebackup is 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
clmar...@theombudsman.com
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
