Re: Trouble setting up replication
От | Chuck Martin |
---|---|
Тема | Re: Trouble setting up replication |
Дата | |
Msg-id | 2CA8200C-5C18-40FE-8E07-9093B83E1FA2@theombudsman.com обсуждение исходный текст |
Ответ на | Re: Trouble setting up replication (Adrian Klaver <adrian.klaver@aklaver.com>) |
Ответы |
Re: Trouble setting up replication
|
Список | pgsql-general |
> 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: >> Thanks for responding Adrian. >> >> After writing this, I noticed that the list is configure to reply to sender. I hope this is not inappropriate. Pleasetell me if I should re-send changing the address to the list. > > The best way to do it is to use Reply All. This sends to both the list and the user. Ensures the user gets the email evenif the list is running slow. While we are list etiquette, the custom on the list is to not top post. I’ll follow these customs in the future. >> [snip] Other then init scripts you should not be doing anything with Postgres as root. That is the cause of many of the problems I’ve experienced, and I’ll avoid that now. > [snip] >> WARNING: could not read symbolic link "pg_tblspc/pg_hba.conf": Invalid argument > > pg_tblspc contain symlinks to tablespaces. pg_hba.conf is a configuration file, it should not be there. Looks like pg_basebackupignored it, but it would worth checking out what else is in pg_tblspc in the database cluster you are backingup from. Thanks. I’ll research this. >> [snip] >> Could I have run pg_basebackup on the replicant/slave over ssh? I thought I could, and when I tried, the results suggestit is possible, but my configuration of one or the other server was incorrect. Can you see what I had wrong from this: >> >> pg_basebackup -h [main_server_url] -D /var/lib/pgsql/9.3/backups/rep -P -v -X s >> pg_basebackup: could not connect to server: FATAL: no pg_hba.conf entry for replication connection from host “[my replicantserver’s outside IP]", user "root", SSL on >> FATAL: no pg_hba.conf entry for replication connection from host "[my replicant server’s outside IP]", user "root", SSLoff >> >> 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 -Uin 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 thehabit 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 not understandthat 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. Thanks for your help, Adrian. I think I’ll get this working now. Chuck
В списке pgsql-general по дате отправления: