Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iiI6T-0007PZ-U0 for pgsql-admin@arkaria.postgresql.org; Fri, 20 Dec 2019 13:14:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iiI6R-0008Ea-Ch for pgsql-admin@arkaria.postgresql.org; Fri, 20 Dec 2019 13:13:59 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iiI6Q-0008ET-Ko for pgsql-admin@lists.postgresql.org; Fri, 20 Dec 2019 13:13:59 +0000 Received: from sonic311-13.consmr.mail.bf2.yahoo.com ([74.6.131.123]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1iiI6L-0005tI-9z for pgsql-admin@postgresql.org; Fri, 20 Dec 2019 13:13:57 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1576847631; bh=RqH7XO1WMDOH5PZsnS8GR+5T/gZv7nrEJmt/UPCm9go=; h=Date:From:Reply-To:To:In-Reply-To:References:Subject:From:Subject; b=cceRK5uBj1os+gXeQ7kLi/U+4yOF6iu/g3drzwonzkdL4D0CRNmoF4HZYn34OtOFnQnV+GdCHkCrePhmieOsG4k/SwA820wjoHz5tnqGpafSJQfT2PRVvtYEPl2WWyTAA/jRqvfyeZKHIO2m4P8TbY3BfNKMw1TK64ANXaB2IkE4f35nkAXNxGlulj2pItP62Q3TaDnrHEzilzZeUMHflDlNMQBzzkZyJQlHVOCyOlXNe8/C6IWfGj2hAtcKKC6/+gq0P7HIQfiQ0zsmzNeIxd7cM4r8Be4Ly3uUDv8tDrPdwptfLkxvVbAM0H3Dycwmw0kTlW8F3UyJuWNln9b2KA== X-YMail-OSG: SdLBrX4VM1lh8k4mZ8KfGYaPsGTOvu3K1QYrUXWdtWnpEMeKGgCaFIsPF50HcL1 h9KqHBVSz95nvRWZrSrkdYiL.wYC1rVoo_VqARW_rw2fVXUdIpXbWSDe5_WZgEQARSDgC_CdFxJ2 Z5qqo5KQyxaKsB.Vr1dXANOn5QuckW1c04sNt0PZPTG0jApHMwVBbJFQfHESGN3ERj5yG884PeG_ JUcfNAlAB4QAwZ1ayD5GvP9kzJQDMnpxHZAXCG.nEBTpBMtaAtoBHQbdBUdj_.C7.lHrANvSGMUq 5uAyXSuWjiLD4tAfE31SXb.U3DDD7YpznN2yiodoWygyiTREn7vhh0lwJlP41ZdBpylmoWEVxH0R 8AHV6HCxcqmE4p1sKdOmQzdnm568k86P3OSV4Xn2d4TV1QQ4krX1MczSfhblOwrFGwNAw.2m1zuW OUNmcOQ3AO1h3qgjEh1LcfAGb4x_UUDy.5eU2evrAldN.tJ5aPAaDoarBmE447Vggw_fN9JutwdI Th3Ry5__x1u11ZyD.H_buGfq0jXZjHT0O1hzO_baWR0hX.GQFZZcaCmogk964_0ra_tTtfiO56ZH LWqyDwPYYrE_Nv5az2jW7s1RanOvcqtXSXUTEMcxQOjXC3az.t2Q7Ae5GucWMtlGF5w_wlDQqvib aqQX3OE2L0LYE2O75F5jCkv1MseL3xg8413RwZiJdDFoALX0B3Jdv.7AW0fNuOaYwmnMteSZU8.M HYHEWa7PcFpMSBAvaltKZTNcpYC8yoU5RCfDeyHuFzCxFMJrsKoVtQuJ0lne0wEp9FQRQ5.Mi0a5 saKrWsKgPBMZ5NtnhHn70Wyzm2ys5jHCJoYqqsEmirb2LSAfCB35Dx_ofJsupugttHwWiM7VLg5M giFGcAmIVokkMeKjJ7WKfZ4eLpnC8HvA9y5gfg7bPcUqhyVlfadTbfSpu9rCKvSI9xwD9KR_UjkO eAIK06DrUIVCmDfJNdGY42nKQM20M_aCNWyzclVtVxaFkUdYFM.uQA8z6yyH_Rw5YljWArypoxQq l1yinb11qgiHwXbxyE9qUYduJuZunqwrj18XV4IifiqRyLFN76F6wGxdkV9HlljWNru3zIc0UE5U VvUi03GcMBG2YpC_y55uVgvyVRkoOqi_Xyz4tvYQ4ydXPy8275gtxUxv6xES_UQJKo5mteFpl3Kz P_pkd35ea2l9gGEp8bOpNwK4zImk4ujjxMwg4LlE5fMJExo6.YRsmO6bbAuosx_TyMA_YHzpxZ4e SgpktdPL2kCyFaNagJxtsaPysIZK93SKg3j_mZUrkx.0vCv0XsaXgJ8Wwm2JRO3Lw2r5hFKv_rh. LZ_04nDANbQVhUg8M7cqCQ8yAEwoGAt_unxDwTfQK1tE- Received: from sonic.gate.mail.ne1.yahoo.com by sonic311.consmr.mail.bf2.yahoo.com with HTTP; Fri, 20 Dec 2019 13:13:51 +0000 Date: Fri, 20 Dec 2019 13:13:49 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Ekaterina Amez , Pgsql-admin Message-ID: <177879592.1149046.1576847629635@mail.yahoo.com> In-Reply-To: <2c818ef8-e555-6dea-a5e8-cd059a3c9ebd@zunibal.com> References: <280013024.631417.1576695326572.ref@mail.yahoo.com> <280013024.631417.1576695326572@mail.yahoo.com> <700937906.892189.1576773893896@mail.yahoo.com> <2c818ef8-e555-6dea-a5e8-cd059a3c9ebd@zunibal.com> Subject: Re: backup script error with could not connect to database MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1149045_657114227.1576847629632" X-Mailer: WebService/1.1.14873 YMailNorrin Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.79 Safari/537.36 Content-Length: 24144 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_1149045_657114227.1576847629632 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable the script worked for my single node postgres database server with the firs= t line in pg_hba.conf=C2=A0host=C2=A0 all=C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 127.0.0.1/32=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 indent not work at all on the replication servers.=C2=A0 I tried to resolve it for= two days and finally, I put "trust" on the 1st line, the script works. Bach-Nga No one in this world is pure and perfect.=C2=A0 If you avoid people for the= ir 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 t= o 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=20 On Friday, December 20, 2019, 04:59:27 AM EST, Ekaterina Amez wrote: =20 =20 =20 =20 El 19/12/19 a las 17:44, Pepe TD Vo escribi=C3=B3: =20 =20 I made it work.=C2=A0 I changed all in pg_hba.conf to trust and backup sc= ripts run fine. =20 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: =20 =C2=A0=C2=A0=C2=A0 host=C2=A0 all=C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 127.0.0.1/32=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 indent =20 =C2=A0=C2=A0=C2=A0 host=C2=A0 replication=C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2= =A0 127.0.0.1/32=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 trust =20 The first line wins and indent is applied instead of trust. =20 =20 =20 thank you so much.=20 Bach-Nga =20 No one in this world is pure and perfect.=C2=A0 If you avoid people for th= eir mistakes you will be alone. So judge less, love and forgive more. To ca= ll him a dog hardly seems to do him justice though in as much as he had fou= r 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)= =20 **Live simply **Love generously **Care deeply **Speak kindly. *** Genuinel= y rich *** Faithful talent *** Sharing success =20 =20 On Thursday, December 19, 2019, 07:12:54 AM EST, bw wrote: =20 =20 Did you try =20 root su -c postgres pg_dumpall > postgresdump.sql =20 On Wed, 18 Dec 2019 at 19:55, Pepe TD Vo wrote: > > hello expert, > > I had to do a backup on replication master-slave servers.=C2=A0 I also h= ave asked this question before, but=C2=A0 I don't understand why I followed= the information and same scripting to run on the replication servers and d= idn't work.=C2=A0 I can run pg_dumpall successful but not with the script.= =C2=A0 Would you please tell me what's wrong and what need to correct the e= rror? > > my replication server (master) pg_hba.conf > > # "local" is for Unix domain socket connections only > local=C2=A0 all=C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 peer > host=C2=A0 all=C2=A0 =C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 192.168.2.0/24=C2=A0 =C2=A0 =C2=A0 =C2=A0 trus= t > # IPv4 local connections: > host=C2=A0 all=C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 127.0.0.1/32=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= indent > local=C2=A0 all=C2=A0 postgres=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 trust > host=C2=A0 all=C2=A0 power_user=C2=A0 =C2=A0 =C2=A0 =C2=A0 0.0.0.0/0=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 md5 > host=C2=A0 all=C2=A0 other_user=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 0.0.0.= 0/0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 md5 > host=C2=A0 all=C2=A0 storageLoader=C2=A0 =C2=A0 0.0.0.0/0=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 md5 > # IPv6 local connections: > host=C2=A0 all=C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ::1/128=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 indent > #allow replication connections from localhost, by a user with the replic= ation priviledge > local=C2=A0 replication=C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 trust > host=C2=A0 replication=C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 127.0.0.1/32= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 trust > host=C2=A0 replicaiton=C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 ::1/128=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 trust > host=C2=A0 replication=C2=A0 replicauser=C2=A0 =C2=A0 masterIP/32=C2=A0 = trust > host=C2=A0 replicaiton=C2=A0 replicauser=C2=A0 =C2=A0 slaveIP/32=C2=A0 = =C2=A0 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 g= et below: > > Performing globals backup: > Globals backup > pg_dumpall: could not connect to database "template1":FATAL: Ident authe= ntication 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 al= l the databases backup > > pg_backup.sh and pg_backup.config attachment. > > v/r, > > Bach-Nga > > No one in this world is pure and perfect.=C2=A0 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 appearan= ces. But to those who knew him well, he was a perfect gentleman (Hermione G= ingold) > > **Live simply **Love generously **Care deeply **Speak kindly. > *** Genuinely rich *** Faithful talent *** Sharing success =20 =20 =20 ------=_Part_1149045_657114227.1576847629632 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
the script worked for my single node postg= res database server with the first line in pg_hba.conf 
host  all  all    =                   127.0.0.1/32=           indent

<= /div>
not work at all on the replicat= ion servers.  I tried to resolve it for two days and finally, I put "t= rust" on the 1st line, the script works.


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 hi= m justice though in as much as he had four legs, a tail, and barked, I admi= t he was, to all outward appearances. But to those who knew him well, he wa= s a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deepl= y **Speak kindly.
*** Genuinely rich *** Fa= ithful talent *** Sharing success


=20
=20
On Friday, December 20, 2019, 04:59:27 AM EST, Ekaterin= a Amez <ekaterina.amez@zunibal.com> wrote:



El 19/12/19 a las 17:44, Pe= pe TD Vo escribi=C3=B3:
I made it work.  I= changed all in pg_hba.conf to trust and backup scripts run fine.
=20

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      &nbs= p;               127.0.0.1/32  &nbs= p;       indent

    host  replication  all    &nb= sp;   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.  I= f 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 <bwakkie@gmail.com> wrote:


Did you try

root su -c postgres pg_dumpall > postgresdump.sql

On Wed, 18 Dec 2019 at 19:55, Pepe TD Vo <pepevo@yahoo.com> wrote:
>
> hello expert,
>
> I had to do a backup on replication master-slave servers.  I also have asked this question before, but&= nbsp; 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 th= e script.  Would you please tell me what's wrong and wha= t need to correct the error?
>
> my replication server (master) pg_hba.conf
>
> # "local" is for Unix domain socket connections only
> local  all  all        &= nbsp;                    =                       pee= r
> host  all    all      &n= bsp;             192.168.2.0/24        trust
> # IPv4 local connections:
> host  all  all        &n= bsp;             127.0.0.1/32          indent
> local  all  postgres      &nb= sp;                     &= nbsp;               trust
> host  all  power_user      &n= bsp; 0.0.0.0/0              md5
> host  all  other_user      &n= bsp;   0.0.0.0/0                md5
> host  all  storageLoader    0.0.0.= 0/0                md5
> # IPv6 local connections:
> host  all  all        &n= bsp;               ::1/128    =               indent
> #allow replication connections from localhost, by a user with the replication priviledge
> local  replication  all      =                      = ;             trust
> host  replication  all      &= nbsp; 127.0.0.1/32          trust
> host  replicaiton  all      &= nbsp; ::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

------=_Part_1149045_657114227.1576847629632--