Обсуждение: Trouble setting up replication
I have been trying to set up "hot standby" replication, but have run into problems, and hope someone here can help. I have configured an ssh tunnel from a computer that is not on a public IP, and was unable to figure out how to run pg_basebackup from that server to backup the data from the main server, so I decided to run pb_basebackup on the main server and copy it later. So I did this: pg_basebackup -D /var/lib/pgsql/9.3/backups/rep/init -P -v -X s (after a number of other attempts), and got errors. First I got a permissions error, and found that the directory was owned by root instead of postgres. I changed that, but eventually I got this error: pg_basebackup: directory "/var/lib/pgsql/9.3/backups/rep" exists but is not empty So my question is whether I can safely delete the directory and run pb_basebackup again? I appreciate any help you can give. Let me know what additional information is needed to answer the question. Chuck
On 09/04/2015 04:07 PM, clmartin@theombudsman.com wrote: > I have been trying to set up "hot standby" replication, but have run > into problems, and hope someone here can help. > > I have configured an ssh tunnel from a computer that is not on a public > IP, and was unable to figure out how to run pg_basebackup from that > server to backup the data from the main server, so I decided to run > pb_basebackup on the main server and copy it later. So I did this: > > pg_basebackup -D /var/lib/pgsql/9.3/backups/rep/init -P -v -X s > > (after a number of other attempts), and got errors. First I got a > permissions error, and found that the directory was owned by root > instead of postgres. I changed that, but eventually I got this error: > > pg_basebackup: directory "/var/lib/pgsql/9.3/backups/rep" exists but is > not empty So what is in it? > > So my question is whether I can safely delete the directory and run > pb_basebackup again? Was that directory present before you ran pg_basebackup or did pg_basebackup create it? > > I appreciate any help you can give. Let me know what additional > information is needed to answer the question. > > Chuck > > -- Adrian Klaver adrian.klaver@aklaver.com
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. Please tellme 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 even if the list is running slow. While we are list etiquette, the custom on the list is to not top post. I am CCing the list. > > I apologize for leaving out important information, but plead ignorance. <g> I’m not a complete newbie with PG generally,but this is my first attempt at setting up replication. > > I am pretty sure that the directory was created by pg_basebackup when I tried to create the backup but the attempt faileddue to an error. I did this quite a few times, getting many different errors. > > I have continued to study and experiment, and think that I caused most of my problems by not paying attention to the userI was logged in as when doing things. I found that even if I specify the user postgres when executing pg_basebackup,if I am logged in as root, the backup directory it creates, and its contents, are owned by root. Yes the user specified by -U is the database user, not the system user. The system user being whoever you are logged in as. Other then init scripts you should not be doing anything with Postgres as root. > > So, I deleted the whole directory, changed to the postgres user, and then did: > > pg_basebackup -D /var/lib/pgsql/9.3/backups/rep -P -v -X s > > This produced errors but seemed to work despite them. This is what pg_basebackup reported: > > could not change directory to "/root": Permission denied That was from being logged in as root. > 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_basebackup ignored it, but it would worth checking out what else is in pg_tblspc in the database cluster you are backing up from. > transaction log start point: 103/2E000028 on timeline 1 > pg_basebackup: starting background WAL receiver > 260715040/260715040 kB (100%), 1/1 tablespace > transaction log end point: 103/2E0009B0 > pg_basebackup: waiting for background process to finish streaming ... > pg_basebackup: base backup completed > > I think the first line is because I logged in as root, changed to the postgres user, but didn’t change my working directoryto the postgres directory. This doesn’t seem like anything to worry about. > The second one didn’t stop it from working, but I like to fix things that are wrongly configured. > > Am I right in thinking I can rely on this backup despite the warning? And any thoughts on how to fix the warning? See comments above. > > 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 -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. > > as I have an ssh tunnel set up between the servers using certificates to authenticate. I also tried with md5 instead ofcert, but both produced the error quoted above. > > Any suggestions? > > I very much appreciate you looking at this. > > Chuck > > >> On Sep 5, 2015, at 12:38 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> >> On 09/04/2015 04:07 PM, clmartin@theombudsman.com wrote: >>> I have been trying to set up "hot standby" replication, but have run >>> into problems, and hope someone here can help. >>> >>> I have configured an ssh tunnel from a computer that is not on a public >>> IP, and was unable to figure out how to run pg_basebackup from that >>> server to backup the data from the main server, so I decided to run >>> pb_basebackup on the main server and copy it later. So I did this: >>> >>> pg_basebackup -D /var/lib/pgsql/9.3/backups/rep/init -P -v -X s >>> >>> (after a number of other attempts), and got errors. First I got a >>> permissions error, and found that the directory was owned by root >>> instead of postgres. I changed that, but eventually I got this error: >>> >>> pg_basebackup: directory "/var/lib/pgsql/9.3/backups/rep" exists but is >>> not empty >> >> So what is in it? >> >>> >>> So my question is whether I can safely delete the directory and run >>> pb_basebackup again? >> >> Was that directory present before you ran pg_basebackup or did pg_basebackup create it? >> >>> >>> I appreciate any help you can give. Let me know what additional >>> information is needed to answer the question. >>> >>> Chuck >>> >>> >> >> >> -- >> Adrian Klaver >> adrian.klaver@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 > -- Adrian Klaver adrian.klaver@aklaver.com
> 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
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 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 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 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. 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 > > -- Adrian Klaver adrian.klaver@aklaver.com
> 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 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 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. Butwhen 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/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. Any ideas? Or should I just change gears and execute pg_basebackup on the main/master instead? Chuck Martin Avondale Software 123 N. McDonough St. Decatur, GA 30030 404-373-3116, fax 404-373-4110 clmartin@theombudsman.com
On 10/10/2015 12:02 PM, Chuck Martin wrote: > 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. try, /usr/pgsql-9.3/bin/pg_basebackup -V if you don't have that path, you'll need to install postgresql-9.3 on it, using the PGDG yum repository at http://yum.postgresql.org... # yum install http://yum.postgresql.org/9.3/redhat/rhel-7-x86_64/pgdg-centos93-9.3-1.noarch.rpm .... # yum install postgresql93 ... (note, the last command just installs the runtime libraries and utilities, it does NOT install the postgresql 9.3 database server, that is package postgresql93-server) -- john r pierce, recycling bits in santa cruz
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
> On Oct 10, 2015, at 3:44 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > 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 outof the 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 yourpg_hba.conf did 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 Postgres9.3 is running on the standby? > John’s reply solved my issue. It showed me that PG 9.3 as also installed, so I modified my command to say: /usr/pgsql-9.3/bin/pg_basebackup --pgdata=/media/RAID2015/data/ --checkpoint=fast --verbose --progress —host=[url to my server] Thanks John and Adrian! Chuck