Re: backup script error with could not connect to database
Ekaterina Amez <[email protected]> Fri, 20 Dec 2019 10:59:00 +0100
| Newsgroups | gmane.comp.db.postgresql.admin |
|---|---|
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format. --------------111C8F2F749E5C237E9EB410 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit El 19/12/19 a las 17:44, Pepe TD Vo escribió: > I made it work. I changed all in pg_hba.conf to trust and backup > scripts run fine. When Postgres searches for the matching line in pg_hba.conf, the first line that matches current connection wins. This means that if you have: host all all 127.0.0.1/32 indent host replication all 127.0.0.1/32 trust The first line wins and indent is applied instead of trust. > thank you so much. > > ** > *Bach-Nga > > *No one in this world is pure and perfect. If you avoid people for > their mistakes you will be alone. So judge less, love and forgive more. > To call him a dog hardly seems to do him justice though in as much as > he had four legs, a tail, and barked, I admit he was, to all outward > appearances. But to those who knew him well, he was a perfect > gentleman (Hermione Gingold) > > **Live simply **Love generously **Care deeply **Speak kindly. > *** Genuinely rich *** Faithful talent *** Sharing success > > > On Thursday, December 19, 2019, 07:12:54 AM EST, bw > <[email protected]> wrote: > > > Did you try > > root su -c postgres pg_dumpall > postgresdump.sql > > On Wed, 18 Dec 2019 at 19:55, Pepe TD Vo <[email protected] > <mailto:[email protected]>> wrote: > > > > hello expert, > > > > I had to do a backup on replication master-slave servers. I also > have asked this question before, but I don't understand why I > followed the information and same scripting to run on the replication > servers and didn't work. I can run pg_dumpall successful but not with > the script. Would you please tell me what's wrong and what need to > correct the error? > > > > my replication server (master) pg_hba.conf > > > > # "local" is for Unix domain socket connections only > > local all all peer > > host all all 192.168.2.0/24 trust > > # IPv4 local connections: > > host all all 127.0.0.1/32 indent > > local all postgres trust > > host all power_user 0.0.0.0/0 md5 > > host all other_user 0.0.0.0/0 md5 > > host all storageLoader 0.0.0.0/0 md5 > > # IPv6 local connections: > > host all all ::1/128 indent > > #allow replication connections from localhost, by a user with the > replication priviledge > > local replication all trust > > host replication all 127.0.0.1/32 trust > > host replicaiton all ::1/128 trust > > host replication replicauser masterIP/32 trust > > host replicaiton replicauser slaveIP/32 trust > > > > from the postgres prompt I can connect to each database > > > > #psql -d template0 -U postgres > > template0-# > > #psql -d template1 -U postgres > > template1-# > > > > #psql -d pscidr -U postgres > > pscidr-# > > #psql -d postgres -U postgres > > postgres-# > > > > > > when I run the script as postgres os, I don't see the error but the > only global.sql backup but nothing in it, 0 byte (blank) sql script. > > > > when I run as root su -c postgres /path/pg_backup.sh > pgbackup.log, > I get below: > > > > Performing globals backup: > > Globals backup > > pg_dumpall: could not connect to database "template1":FATAL: Ident > authentication failed for user "postgres" fro globals backup. > > > > Performing schema-only backup: > > psql:FATAL: Ident authentication failed for user "postgres" > > the following databases were matched for schema-only backup: > > > > performing full backup: > > psql:FATAL: Ident authentication failed for user "postgres" > > > > > > I can run pg_dumpall > postgresdump.sql as postgres user os fine > will all the databases backup > > > > pg_backup.sh and pg_backup.config attachment. > > > > v/r, > > > > Bach-Nga > > > > No one in this world is pure and perfect. If you avoid people for > their mistakes you will be alone. So judge less, love and forgive more. > > To call him a dog hardly seems to do him justice though in as much > as he had four legs, a tail, and barked, I admit he was, to all > outward appearances. But to those who knew him well, he was a perfect > gentleman (Hermione Gingold) > > > > **Live simply **Love generously **Care deeply **Speak kindly. > > *** Genuinely rich *** Faithful talent *** Sharing success > --------------111C8F2F749E5C237E9EB410 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> </head> <body> <p><br> </p> <div class="moz-cite-prefix">El 19/12/19 a las 17:44, Pepe TD Vo escribió:<br> </div> <blockquote type="cite" cite="mid:[email protected]"> <meta http-equiv="content-type" content="text/html; charset=UTF-8"> <div class="ydp69f78c4ayahoo-style-wrap" style="font-family:verdana, helvetica, sans-serif;font-size:16px;"> <div> <div dir="ltr" data-setdir="false"><span><span style="color: rgb(0, 0, 0); font-family: verdana, helvetica, sans-serif; font-size: 16px;">I made it work. I changed all in pg_hba.conf to trust and backup scripts run fine.</span></span><br> </div> </div> </div> </blockquote> <p>When Postgres searches for the matching line in pg_hba.conf, the first line that matches current connection wins. This means that if you have:</p> <p> host all all 127.0.0.1/32 indent</p> <p> host replication all 127.0.0.1/32 trust</p> <p>The first line wins and indent is applied instead of trust.<br> </p> <p><br> </p> <blockquote type="cite" cite="mid:[email protected]"> <div class="ydp69f78c4ayahoo-style-wrap" style="font-family:verdana, helvetica, sans-serif;font-size:16px;"> <div> <div dir="ltr" data-setdir="false"><span><span style="color: rgb(0, 0, 0); font-family: verdana, helvetica, sans-serif; font-size: 16px;">thank you so much.</span></span></div> <div><br> </div> <div class="ydp69f78c4asignature"> <div id="ydp69f78c4ayui_3_13_0_rc_1_1_1381789421038_7868" class="ydp69f78c4ayiv3811552299ms__id13889"><font color="#0000ff"><b><span class="ydp69f78c4ayiv3811552299sg"></span></b></font> <div id="ydp69f78c4ayui_3_13_0_rc_1_1_1381789421038_7874" class="ydp69f78c4ayiv3811552299ms__id13893"><font id="ydp69f78c4ayui_3_13_0_rc_1_1_1381789421038_7873" color="#0000ff"><span id="ydp69f78c4ayui_3_13_0_rc_1_1_1381789421038_7871" class="ydp69f78c4ayiv3811552299sg"><font id="ydp69f78c4ayui_3_13_0_rc_1_1_1381789421038_7870" style="background-color: inherit;" color="#8000ff"><b id="ydp69f78c4ayui_3_13_0_rc_1_16_1381789421038_18">Bach-Nga<br clear="none"> <br clear="none"> </b>No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.</font></span></font></div> <div id="ydp69f78c4ayui_3_13_0_rc_1_1_1381789421038_7874" class="ydp69f78c4ayiv3811552299ms__id13893"><font color="#0000ff"><span class="ydp69f78c4ayiv3811552299sg"><font style="background-color: inherit;" color="#8000ff">To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)</font></span></font></div> <br clear="none"> <span style="color:rgb(128, 0, 255);" id="ydp69f78c4ayui_3_13_0_rc_1_1_1381789421038_7867">**Live simply **Love generously **Care deeply **Speak kindly.</span></div> <div id="ydp69f78c4ayui_3_13_0_rc_1_1_1381789421038_7866" class="ydp69f78c4ayiv3811552299ms__id13894"><span id="ydp69f78c4ayui_3_13_0_rc_1_1_1381789421038_7865" class="ydp69f78c4ayiv3811552299sg"><font id="ydp69f78c4ayui_3_13_0_rc_1_1_1381789421038_7864" style="background-color: inherit;" color="#8000ff">*** Genuinely rich *** Faithful talent *** Sharing success</font></span></div> </div> </div> <div><br> </div> <div><br> </div> </div> <div id="yahoo_quoted_7210724997" class="yahoo_quoted"> <div style="font-family:'Helvetica Neue', Helvetica, Arial, sans-serif;font-size:13px;color:#26282a;"> <div> On Thursday, December 19, 2019, 07:12:54 AM EST, bw <a class="moz-txt-link-rfc2396E" href="mailto:[email protected]"><[email protected]></a> wrote: </div> <div><br> </div> <div><br> </div> <div> <div dir="ltr">Did you try<br clear="none"> <br clear="none"> root su -c postgres pg_dumpall > postgresdump.sql<br clear="none"> <div class="yqt5894315542" id="yqtfd56431"><br clear="none"> On Wed, 18 Dec 2019 at 19:55, Pepe TD Vo <<a shape="rect" ymailto="mailto:[email protected]" href="mailto:[email protected]" moz-do-not-send="true">[email protected]</a>> wrote:<br clear="none"> ><br clear="none"> > hello expert,<br clear="none"> ><br clear="none"> > I had to do a backup on replication master-slave servers. I also have asked this question before, but I don't understand why I followed the information and same scripting to run on the replication servers and didn't work. I can run pg_dumpall successful but not with the script. Would you please tell me what's wrong and what need to correct the error?<br clear="none"> ><br clear="none"> > my replication server (master) pg_hba.conf<br clear="none"> ><br clear="none"> > # "local" is for Unix domain socket connections only<br clear="none"> > local all all peer<br clear="none"> > host all all 192.168.2.0/24 trust<br clear="none"> > # IPv4 local connections:<br clear="none"> > host all all 127.0.0.1/32 indent<br clear="none"> > local all postgres trust<br clear="none"> > host all power_user 0.0.0.0/0 md5<br clear="none"> > host all other_user 0.0.0.0/0 md5<br clear="none"> > host all storageLoader 0.0.0.0/0 md5<br clear="none"> > # IPv6 local connections:<br clear="none"> > host all all ::1/128 indent<br clear="none"> > #allow replication connections from localhost, by a user with the replication priviledge<br clear="none"> > local replication all trust<br clear="none"> > host replication all 127.0.0.1/32 trust<br clear="none"> > host replicaiton all ::1/128 trust<br clear="none"> > host replication replicauser masterIP/32 trust<br clear="none"> > host replicaiton replicauser slaveIP/32 trust<br clear="none"> ><br clear="none"> > from the postgres prompt I can connect to each database<br clear="none"> ><br clear="none"> > #psql -d template0 -U postgres<br clear="none"> > template0-#<br clear="none"> > #psql -d template1 -U postgres<br clear="none"> > template1-#<br clear="none"> ><br clear="none"> > #psql -d pscidr -U postgres<br clear="none"> > pscidr-#<br clear="none"> > #psql -d postgres -U postgres<br clear="none"> > postgres-#<br clear="none"> ><br clear="none"> ><br clear="none"> > when I run the script as postgres os, I don't see the error but the only global.sql backup but nothing in it, 0 byte (blank) sql script.<br clear="none"> ><br clear="none"> > when I run as root su -c postgres /path/pg_backup.sh > pgbackup.log, I get below:<br clear="none"> ><br clear="none"> > Performing globals backup:<br clear="none"> > Globals backup<br clear="none"> > pg_dumpall: could not connect to database "template1":FATAL: Ident authentication failed for user "postgres" fro globals backup.<br clear="none"> ><br clear="none"> > Performing schema-only backup:<br clear="none"> > psql:FATAL: Ident authentication failed for user "postgres"<br clear="none"> > the following databases were matched for schema-only backup:<br clear="none"> ><br clear="none"> > performing full backup:<br clear="none"> > psql:FATAL: Ident authentication failed for user "postgres"<br clear="none"> ><br clear="none"> ><br clear="none"> > I can run pg_dumpall > postgresdump.sql as postgres user os fine will all the databases backup<br clear="none"> ><br clear="none"> > pg_backup.sh and pg_backup.config attachment.<br clear="none"> ><br clear="none"> > v/r,<br clear="none"> ><br clear="none"> > Bach-Nga<br clear="none"> ><br clear="none"> > No one in this world is pure and perfect. If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.<br clear="none"> > To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)<br clear="none"> ><br clear="none"> > **Live simply **Love generously **Care deeply **Speak kindly.<br clear="none"> > *** Genuinely rich *** Faithful talent *** Sharing success<br clear="none"> <br clear="none"> </div> </div> </div> </div> </div> </blockquote> </body> </html> --------------111C8F2F749E5C237E9EB410--