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 1gygoc-0002fF-98 for pgsql-admin@arkaria.postgresql.org; Tue, 26 Feb 2019 17:46:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gygoa-0003Qj-Te for pgsql-admin@arkaria.postgresql.org; Tue, 26 Feb 2019 17:46:48 +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 1gygoZ-0003QY-Nt for pgsql-admin@lists.postgresql.org; Tue, 26 Feb 2019 17:46:48 +0000 Received: from sonic314-13.consmr.mail.bf2.yahoo.com ([74.6.132.123]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gygoU-0002Hh-DG for pgsql-admin@lists.postgresql.org; Tue, 26 Feb 2019 17:46:46 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1551203200; bh=4ikM6Uo/xmLN+IdqCm5d3qIkd7N7di1NYvTya5k+4d0=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=h+Obn+CDcqB+Zl/kk3sfwf4KgJTAsLacFJ9wz7u8v5VbGrg9rf+gfvOJBx6cLKzmBr4aeZgcU+u77pMU115CpF2/sGXfn2dRowux9E5cnt+iBr51koLYqfOf9PLcmvCzgQRMUhhi4UxsGvuLywpyM8Qk9E0w45d8cNo8GWoxZF3OTK4Ym+NnoN7aFvyHfCaVWpyHZ/KacIFcE6Z7pln9suwE3VZgBsqzoGGLPNSJl//wLtm8mf4gatw1eSs9turBHZ6oPXRy4LcSecw1RMBUUe5ZX9B2/Z4rx60HH9jcOdZOF3b5fMsAW9g1Vj0odcD9QDlxaMMKWJhKSjvZUnNuBw== X-YMail-OSG: sN95V3EVM1lNY7crnEQZuEdLU7AFdWWrPiaJ8cHlmwtLlU1MH9pKHDo3MkbAKPg 8H5Vn0Nx8JAvI.7fyustOyRAvUlGgFAEP3applc211AMcsqAhHGhiWQ4rqcACD2pZe2ShE3ZvdLm Ljj.snoAtqnD6wKj80l..WG52LZlvJ75FctesPQGN5s9p6.Ci4C9RrDcgAMd.UrPdd1FNGBmWG1S nM8ODxLeFT848tz7vxONcc3FTZGK3T1k8Yt4LhUf2G1ABollqIRICT2eonmmjVSORMeVP7PTYOR0 5mMvV8LHrO6f0iO8XSjdifqt3jvPWsLuaDm.4EwcNlDwNf2UZMk8VbCEArkCyNQvjj_p7wLbFZvx VpIH31T6_ydZNKIAH2CV5YDj6v2n6sNvdsDWY6eT8Zjtem7deMZ4pNJFMa2Qc6E5Hk35rn3I9WUg zRQhyvCtM2geE2318P4myKJFiyklinA02zcRrRdocmNoZSFkttk_MZIDcu3nJmMOtKYvbkC7Me7c 50wDVGLcummHRPX2ZA5lfBrEA5Ncpk_Na2ky6E8hm6GUQy354I7KYESFLf8Ds7JkdYPcrVrM9R7O 1MWguIiQ9gLsC6fBN2OvBg3Q_18P79nip190imn7oriM1f0rI3F5vwcke03twttLhj5OFvuiTkZO 6qVnoZwvWTBqZAWPjjNX1mbu9KHAqMAWooAqFUUc6L7PLJkHNyp9.8.YlAaR0JX47SLL.2Np4BNY 4jvyoZbK2wzELVyzDxDdU_.3_nDzSB4coPR9bvF6Bybc.z131Kpsi8hOfkjGVu7EqCzWRMcb4G.3 ueGEN5m9OC12IVzFrvY4StrlqtD.ZPrL.y83YFYiCS4jMaOyC9TuT1S4FcFlPstgF8C0sBgIMzN0 aM2GGAYq8RHsviV2O9Q.64JJd4kEC9kByYW7IPaECsywVwUY4yU4h3AywFP0sdB8rgcwUaI0vDMS ._RafQQca1bvMrMmlgLFoIR0amhQ6q5aSwszX_HMeNq5t2onVM7VI5aF4zPXmMf15XbH4LOLyAv. qWAEffu2gq2U0GBRP9Wq_iuRthsyyuBgfPImvtWylURLuV6iMm2VL_wwFMA-- Received: from sonic.gate.mail.ne1.yahoo.com by sonic314.consmr.mail.bf2.yahoo.com with HTTP; Tue, 26 Feb 2019 17:46:40 +0000 Date: Tue, 26 Feb 2019 17:46:38 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Shreeyansh Dba Cc: "gilberto.castillo@etecsa.cu" , Pgsql-admin Message-ID: <1161831433.5624889.1551203198375@mail.yahoo.com> In-Reply-To: References: <1766891885.5546566.1551195368839.ref@mail.yahoo.com> <1766891885.5546566.1551195368839@mail.yahoo.com> Subject: Re: [MASSMAIL]Re: pg_dumpall: could not connect to database: FATAL MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5624888_1602436000.1551203198354" X-Mailer: WebService/1.1.13123 YahooMailNeo Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.109 Safari/537.36 Content-Length: 124510 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_5624888_1602436000.1551203198354 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable same error: run as root:# export HOSTNAME=3Dhostname# export USERNAME=3Dpostgres# expor= t DATABASE=3Dpostgres=C2=A0# export PORT 5432# pg_dump -v -p $PORT -U $USER= NAME -d $DATABASE -f /tmp/postgresbakup.sql pg_dump:[archiver (db)]connection to database "DATABASE" failed: could not = connect to server: Connection refusedIs the server running on host "portal-= p" (138.24.1.110) and acceptiong TCP/IP connection on port 5432? run as postgres# export HOSTNAME=3Dhostname# export USERNAME=3Dpostgres# ex= port DATABASE=3Dpostgres=C2=A0# export PORT 5432# pg_dump -v -p $PORT -U $U= SERNAME -d $DATABASE -f /tmp/postgresbakup.sql pg_dump:[archiver (db)]connection to database "DATABASE" failed: FATAL: Ide= nt authentication failed for user "postgres" portal-p already on /etc/hosts. v/r, 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 Tuesday, February 26, 2019 11:01 AM, Shreeyansh Dba wrote: =20 Ohh, It=C2=A0looks your pg_hba entries are correct, reset your database us= er "postgres" password then try=C2=A0again or use -w option, use a single h= ba.conf file. try: pg_dump -v -p $PORT -U $USERNAME -d $DATABASE -f /tmp/postresp.sql Thanks & Regards, Shreeyansh DBA Team www.shreeyansh.com On Tue, Feb 26, 2019 at 9:06 PM Pepe TD Vo wrote: yes mam'm/sir.=C2=A0 =C2=A0I already posted it here it is again. #vi pg_hba.conf=C2=A0 (from /usr/local/pgsql/data/pg_hba.conf)=C2=A0 #TYPE=C2=A0 DATABASE=C2=A0 =C2=A0 =C2=A0USER=C2=A0 =C2=A0 =C2=A0ADDRESS=C2= =A0 =C2=A0 =C2=A0METHOD# "local" is for Unix domain socket connectiosn only local=C2=A0 =C2=A0 =C2=A0all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0all=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=A0trust # IPv4 local connections: host=C2=A0 =C2=A0 =C2=A0all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0127.0.0.1/32 trust # IPv6 local connections: host=C2=A0 =C2=A0 =C2=A0all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0::1/128=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0trust # allow all replication connections from localhost, by a user with the repl= ication privilegelocal=C2=A0 =C2=A0 replication=C2=A0 =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 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 trusthost=C2=A0 =C2=A0 replication=C2=A0 =C2=A0= =C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 127.0.0.1/32=C2=A0 trusthost= =C2=A0 =C2=A0 replication=C2=A0 =C2=A0 =C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 ::1/128=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0trust I also found pg_hba.conf on both folders=C2=A0 /var/lib/pgsql/11/data/pg_hb= a.conf & /var/lib/pgsql/data/pg_hba.conf).=C2=A0 I modified it as above and= the script is not working even run from the command prompt. #vi pg_hba.conf ( (from /var/lib/pgsql/11/data/pg_hba.conf & /var/lib/pgsql= /data/pg_hba.conf)=C2=A0 #TYPE=C2=A0 DATABASE=C2=A0 =C2=A0 =C2=A0USER=C2=A0 =C2=A0 =C2=A0ADDRESS=C2= =A0 =C2=A0 =C2=A0METHOD# "local" is for Unix domain socket connectiosn only local=C2=A0 =C2=A0 =C2=A0all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0all=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=A0trust # IPv4 local connections: host=C2=A0 =C2=A0 =C2=A0all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0127.0.0.1/32 trust host=C2=A0 =C2=A0 =C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 power= _user 0.0.0.0/0=C2=A0 =C2=A0 =C2=A0md5host=C2=A0 =C2=A0 =C2=A0 all=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 other_user 0.0.0.0/0=C2=A0 =C2=A0 =C2=A0= md5host=C2=A0 =C2=A0 =C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 s= torageLoader 0.0.0.0/0 md5 # IPv6 local connections: host=C2=A0 =C2=A0 =C2=A0all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0::1/128=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0trust # allow all replication connections from localhost, by a user with the repl= ication privilegelocal=C2=A0 =C2=A0 replication=C2=A0 =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 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 trusthost=C2=A0 =C2=A0 replication=C2=A0 =C2=A0= =C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 127.0.0.1/32=C2=A0 =C2=A0rust= host=C2=A0 =C2=A0 replication=C2=A0 =C2=A0 =C2=A0 all=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 ::1/128=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0trust why do I have 3 pg_hba.conf?=C2=A0 which one should use? thank you for looking into it. v/r,=C2=A0Bach-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 Tuesday, February 26, 2019 9:52 AM, Shreeyansh Dba wrote: =20 The error message says the authentication method used isn't trust. Can you= please share your pg_hba.conf file entries. Thanks & Regards, Shreeyansh DBA Team www.shr Hi Pepe eeyansh.com On Tue, Feb 26, 2019 at 7:41 PM Pepe TD Vo wrote: No sir.=C2=A0 =C2=A0I have tried what your suggestion and still failed. as root: # export HOSTNAME=3D"localhost"# export USERNAME=3D"postgres"# export DATAB= ASE=3D"postgres"# pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" > /= tmp/postresp.sql pg_dump:[archiver (db)] connection to database "DATABASE" failed: FATAL: Id= ent authentication failed for user "postgres" # export HOSTNAME=3D"127.0.0.1"# export USERNAME=3D"postgres"# export DATAB= ASE=3D"postgres"# pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" > /= tmp/postresp.sql pg_dump:[archiver (db)] connection to database "DATABASE" failed: FATAL: Id= ent authentication failed for user "postgres" as same as postgres: #su - postgres =C2=A0export HOSTNAME=3D"localhost"# export USERNAME=3D"postgres"# export D= ATABASE=3D"postgres"# pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE"= > /tmp/postresp.sql pg_dump:[archiver (db)] connection to database "DATABASE" failed: FATAL: Id= ent authentication failed for user "postgres" # export HOSTNAME=3D"127.0.0.1"# export USERNAME=3D"postgres"# export DATAB= ASE=3D"postgres"# pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" > /= tmp/postresp.sql pg_dump:[archiver (db)] connection to database "DATABASE" failed: FATAL: Id= ent authentication failed for user "postgres" as the postgres, I can run pgdump dbname > output.sql v/r,=C2=A0Bach-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 Monday, February 25, 2019 2:39 PM, "gilberto.castillo@etecsa.cu" wrote: =20 El 2019-02-25 14:16, Pepe TD Vo escribi=C3=B3: > I am running script in local host (same db server) El 2019-02-25 14:16, Pepe TD Vo escribi=C3=B3: > I am running script in local host (same db server) Please, Check this: root@gilbertoc:/home/gilbertoc# export HOSTNAME=3D"localhost";=20 USERNAME=3D"postgres"; DATABASE=3D"postgres" root@gilbertoc:/home/gilbertoc# pg_dump -Fc -h "$HOSTNAME" -U=20 "$USERNAME" "$DATABASE" > /tmp/postresp.sql Password: pg_dump: [archiver (db)] connection to database "postgres" failed:=20 FATAL:=C2=A0 password authentication failed for user "postgres" FATAL:=C2=A0 password authentication failed for user "postgres" Other: root@gilbertoc:/home/gilbertoc# sudo su - postgres postgres@gilbertoc:~$ export HOSTNAME=3D"localhost"; USERNAME=3D"postgres";= =20 DATABASE=3D"postgres" postgres@gilbertoc:~$ pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME"=20 "$DATABASE" > /tmp/postresp.sql =C2=A0 /tmp/postresp.sql: Permiso denegado Fine: postgres@gilbertoc:~$ export HOSTNAME=3D"127.0.0.1"; USERNAME=3D"postgres";= =20 DATABASE=3D"postgres" postgres@gilbertoc:~$ pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME"=20 "$DATABASE" > /tmp/salvapostgres.backup postgres@gilbertoc:~$ ls /tmp/salvapostgres.backup /tmp/salvapostgres.backup >=20 > Bach-Nga >=20 > 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 > 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 >=20 >=C2=A0 On Monday, February 25, 2019 1:58 PM, "gilberto.castillo@etecsa.cu" > wrote: >=20 > El 2019-02-25 13:48, Pepe TD Vo escribi=C3=B3: >=20 >> Thank you for your information. >=20 >> I saw this document this morning and I did check my pg_hba.conf and >=20 >> for local host and tried to connect to the template1/0 and/or other >=20 >> database with postgres fine without prompting me for the password >=20 >>=20 >=20 >> #psql -d template0 -U postgres >=20 >> template0-# >=20 >> #psql -d template1 -U postgres >=20 >> template1-# >=20 >>=20 >=20 >> #psql -d CIDR -U postgres >=20 >> CIDR-# >=20 >> #psql -d postgres -U postgres >=20 >> postgres-# >=20 >>=20 >=20 >> and a pg_hba.conf: >=20 >>=20 >=20 >> " >=20 >>=20 >=20 >> #TYPE=C2=A0 DATABASE=C2=A0 =C2=A0 USER=C2=A0 =C2=A0 ADDRESS=C2=A0 =C2=A0= METHOD >=20 >> local=C2=A0 =C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =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 =C2=A0 =C2=A0 =C2=A0 =C2=A0 trust >=20 >> host=C2=A0 =C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 127.0.0.1/32 trust >=20 >> host=C2=A0 =C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 ::1/128=C2=A0 =C2=A0 =C2=A0 =C2=A0 tr= ust >=20 > Look here the IP only connect to localhost. From where Are your > running >=20 > the script? >=20 >> #allo replication connections from localhost, by a user with the >=20 >> replication privilege >=20 >> local=C2=A0 =C2=A0 replication=C2=A0 =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 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 trust >=20 >> host=C2=A0 =C2=A0 replication=C2=A0 =C2=A0 =C2=A0 all=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 127..0.1/32=C2=A0 trust >=20 >> host=C2=A0 =C2=A0 replication=C2=A0 =C2=A0 =C2=A0 all=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 ::1/128=C2=A0 =C2=A0 =C2=A0 =C2=A0 trust >=20 >>=20 >=20 >> the problem is I'm not sure I need to reconfigure pg_hba.conf with >=20 >> database with user postgres or not? >=20 >>=20 >=20 >> When I'm connecting into the each database and the "show > pg_hba.conf" >=20 >> show nothing. >=20 >>=20 >=20 >> postgres-# show pg_hba.conf >=20 >> postgres-# >=20 >>=20 >=20 >> Bach-Nga >=20 >>=20 >=20 >> No one in this world is pure and perfect.=C2=A0 If you avoid people for >=20 >> their mistakes you will be alone. So judge less, love and forgive >=20 >> more. >=20 >> To call him a dog hardly seems to do him justice though in as much > as >=20 >> he had four legs, a tail, and barked, I admit he was, to all outward >=20 >> appearances. But to those who knew him well, he was a perfect >=20 >> gentleman (Hermione Gingold) >=20 >> **Live simply **Love generously **Care deeply **Speak kindly. >=20 >> *** Genuinely rich *** Faithful talent *** Sharing success >=20 >>=20 >=20 >>=C2=A0 On Monday, February 25, 2019 12:28 PM, Shreeyansh Dba >=20 >> wrote: >=20 >>=20 >=20 >> Hi Pepe, >=20 >>=20 >=20 >> It seems your are using os user for taking the backup. >=20 >>=20 >=20 >> When you run command line commands against the postgres database and >=20 >> they ask for the user and password that is the database user > postgres >=20 >> and its associated password, so when you do something like: psql -d >=20 >> template1 -U postgres the password you will be prompted for is >=20 >> your_db_user password >=20 >>=20 >=20 >> Now the above also depends on what is set in pg_hba.conf for the >=20 >> various combinations of host, database, user and auth_method. >=20 >>=20 >=20 >> For more information see >=20 >>=20 > https://www.postgresql.org/docs/9.5/static/client-authentication.html >=20 >>=20 >=20 >> Thanks & Regards, >=20 >> SHREEYANSH DBA TEAM >=20 >> www.shreeyansh.com [1] >=20 >>=20 >=20 >> On Mon, Feb 25, 2019 at 10:21 PM Pepe TD Vo > wrote: >=20 >>=20 >=20 >>> good morning Expert DBAs, >=20 >>>=20 >=20 >>> I created a script to do backup and I have faced the error of >=20 >>>=20 >=20 >>>> Performing globals backup: >=20 >>>> pg_dumpall: could not connect to database "template1":FATAL: Ident >=20 >>>> authentication failed for user "postgres" fro globals backup.=C2=A0 As >=20 >>>> same as performing schema-only backups and full backup. >=20 >>>>=20 >=20 >>>> Performing schema-only backup: >=20 >>>> psql:FATAL: Ident authentication failed for user "postgres" >=20 >>>> the following databases were matched for schema-only backup: >=20 >>>>=20 >=20 >>>> performing full backup: >=20 >>>> psql:FATAL: Ident authentication failed for user "postgres" >=20 >>>=20 >=20 >>> I can run pg_dumpall > postgresdump.sql as postgres user os but >=20 >>> couldn't run from script even the script declared >=20 >>> BACKUP_USER=3Dpostgres >=20 >>>=20 >=20 >>> If I take the syntax, -H "$HOSTNAME" -U "$USERNAME" out and the >=20 >>> backup script working fine >=20 >>>=20 >=20 >>> # VI PG_BACKUP.CONFIG >=20 >>>=20 >=20 >>> ############################## >=20 >>> ## POSTGRESQL BACKUP CONFIG ## >=20 >>> ############################## >=20 >>>=20 >=20 >>> # Optional system user to run backups as.=C2=A0 If the user the script > is >=20 >>> running as doesn't match this >=20 >>> # the script terminates.=C2=A0 Leave blank to skip check. >=20 >>> BACKUP_USER=3D >=20 >>>=20 >=20 >>> # Optional hostname to adhere to pg_hba policies.=C2=A0 Will default to >=20 >>> "localhost" if none specified. >=20 >>> HOSTNAME=3D"hostname" >=20 >>>=20 >=20 >>> # Optional username to connect to database as.=C2=A0 Will default to >=20 >>> "postgres" if none specified. >=20 >>> USERNAME=3D"postgres" >=20 >>>=20 >=20 >>> # This dir will be created if it doesn't exist.=C2=A0 This must be >=20 >>> writable by the user the script is >=20 >>> # running as. >=20 >>> BACKUP_DIR=3D/home/backups/database/postgresql/ >=20 >>>=20 >=20 >>> # List of strings to match against in database name, separated by >=20 >>> space or comma, for which we only >=20 >>> # wish to keep a backup of the schema, not the data. Any database >=20 >>> names which contain any of these >=20 >>> # values will be considered candidates. (e.g. "system_log" will >=20 >>> match "dev_system_log_2010-01") >=20 >>> SCHEMA_ONLY_LIST=3D"" >=20 >>>=20 >=20 >>> # Will produce a custom-format backup if set to "yes" >=20 >>> ENABLE_CUSTOM_BACKUPS=3Dyes >=20 >>>=20 >=20 >>> # Will produce a gzipped plain-format backup if set to "yes" >=20 >>> ENABLE_PLAIN_BACKUPS=3Dyes >=20 >>>=20 >=20 >>> # Will produce gzipped sql file containing the cluster globals, > like >=20 >>> users and passwords, if set to "yes" >=20 >>> ENABLE_GLOBALS_BACKUPS=3Dyes >=20 >>>=20 >=20 >>> #### SETTINGS FOR ROTATED BACKUPS #### >=20 >>>=20 >=20 >>> # Which day to take the weekly backup from (1-7 =3D Monday-Sunday) >=20 >>> DAY_OF_WEEK_TO_KEEP=3D5 >=20 >>>=20 >=20 >>> # Number of days to keep daily backups >=20 >>> DAYS_TO_KEEP=3D7 >=20 >>>=20 >=20 >>> # How many weeks to keep weekly backups >=20 >>> WEEKS_TO_KEEP=3D5 >=20 >>>=20 >=20 >>> ###################################### >=20 >>>=20 >=20 >>> #VI PG_BACKUP.SH >=20 >>>=20 >=20 >>> #!/bin/bash >=20 >>>=20 >=20 >>> ########################### >=20 >>> ####### LOAD CONFIG ####### >=20 >>> ########################### >=20 >>>=20 >=20 >>> while [ $# -gt 0 ]; do >=20 >>> case $1 in >=20 >>> -c) >=20 >>> if [ -r "$2" ]; then >=20 >>> source "$2" >=20 >>> shift 2 >=20 >>> else >=20 >>> ${ECHO} "Unreadable config file >=20 >>> \"$2\"" 1>&2 >=20 >>> exit 1 >=20 >>> fi >=20 >>> ;; >=20 >>> *) >=20 >>> ${ECHO} "Unknown Option \"$1\"" 1>&2 >=20 >>> exit 2 >=20 >>> ;; >=20 >>> esac >=20 >>> done >=20 >>>=20 >=20 >>> if [ $# =3D 0 ]; then >=20 >>> SCRIPTPATH=3D$(cd ${0%/*} && pwd -P) >=20 >>> source $SCRIPTPATH/pg_backup.config >=20 >>> fi; >=20 >>>=20 >=20 >>> ########################### >=20 >>> #### PRE-BACKUP CHECKS #### >=20 >>> ########################### >=20 >>>=20 >=20 >>> # Make sure we're running as the required backup user >=20 >>> if [ "$BACKUP_USER" !=3D "" -a "$(id -un)" !=3D "$BACKUP_USER" ]; then >=20 >>> echo "This script must be run as $BACKUP_USER. Exiting." 1>&2 >=20 >>> exit 1; >=20 >>> fi; >=20 >>>=20 >=20 >>> ########################### >=20 >>> ### INITIALISE DEFAULTS ### >=20 >>> ########################### >=20 >>>=20 >=20 >>> if [ ! $HOSTNAME ]; then >=20 >>> HOSTNAME=3D"localhost" >=20 >>> fi; >=20 >>>=20 >=20 >>> if [ ! $USERNAME ]; then >=20 >>> USERNAME=3D"postgres" >=20 >>> fi; >=20 >>>=20 >=20 >>> ########################### >=20 >>> #### START THE BACKUPS #### >=20 >>> ########################### >=20 >>>=20 >=20 >>> FINAL_BACKUP_DIR=3D$BACKUP_DIR"`date +\%Y-\%m-\%d`/" >=20 >>>=20 >=20 >>> echo "Making backup directory in $FINAL_BACKUP_DIR" >=20 >>>=20 >=20 >>> if ! mkdir -p $FINAL_BACKUP_DIR; then >=20 >>> echo "Cannot create backup directory in $FINAL_BACKUP_DIR. Go >=20 >>> and fix it!" 1>&2 >=20 >>> exit 1; >=20 >>> fi; >=20 >>>=20 >=20 >>> ####################### >=20 >>> ### GLOBALS BACKUPS ### >=20 >>> ####################### >=20 >>>=20 >=20 >>> echo -e "\n\nPerforming globals backup" >=20 >>> echo -e "--------------------------------------------\n" >=20 >>>=20 >=20 >>> if [ $ENABLE_GLOBALS_BACKUPS =3D "yes" ] >=20 >>> then >=20 >>> echo "Globals backup" >=20 >>>=20 >=20 >>> if ! pg_dumpall -g -h "$HOSTNAME" -U "$USERNAME" | gzip > >=20 >>> $FINAL_BACKUP_DIR"globals".sql.gz.in_progress; then >=20 >>> echo "[!!ERROR!!] Failed to produce globals backup" >=20 >>> 1>&2 >=20 >>> else >=20 >>> mv $FINAL_BACKUP_DIR"globals".sql.gz.in_progress >=20 >>> $FINAL_BACKUP_DIR"globals".sql.gz >=20 >>> fi >=20 >>> else >=20 >>> echo "None" >=20 >>> fi >=20 >>>=20 >=20 >>> ########################### >=20 >>> ### SCHEMA-ONLY BACKUPS ### >=20 >>> ########################### >=20 >>>=20 >=20 >>> for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ } >=20 >>> do >=20 >>> SCHEMA_ONLY_CLAUSE=3D"$SCHEMA_ONLY_CLAUSE or datname ~ >=20 >>> '$SCHEMA_ONLY_DB'" >=20 >>> done >=20 >>>=20 >=20 >>> SCHEMA_ONLY_QUERY=3D"select datname from pg_database where false >=20 >>> $SCHEMA_ONLY_CLAUSE order by datname;" >=20 >>>=20 >=20 >>> echo -e "\n\nPerforming schema-only backups" >=20 >>> echo -e "--------------------------------------------\n" >=20 >>>=20 >=20 >>> SCHEMA_ONLY_DB_LIST=3D`psql -h "$HOSTNAME" -U "$USERNAME" -At -c >=20 >>> "$SCHEMA_ONLY_QUERY" postgres` >=20 >>>=20 >=20 >>> echo -e "The following databases were matched for schema-only >=20 >>> backup:\n${SCHEMA_ONLY_DB_LIST}\n" >=20 >>>=20 >=20 >>> for DATABASE in $SCHEMA_ONLY_DB_LIST >=20 >>> do >=20 >>> echo "Schema-only backup of $DATABASE" >=20 >>>=20 >=20 >>> if ! pg_dump -Fp -s -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | >=20 >>> gzip > $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress; then >=20 >>> echo "[!!ERROR!!] Failed to backup database schema of >=20 >>> $DATABASE" 1>&2 >=20 >>> else >=20 >>> mv $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress >=20 >>> $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz >=20 >>> fi >=20 >>> done >=20 >>>=20 >=20 >>> ########################### >=20 >>> ###### FULL BACKUPS ####### >=20 >>> ########################### >=20 >>>=20 >=20 >>> for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ } >=20 >>> do >=20 >>> EXCLUDE_SCHEMA_ONLY_CLAUSE=3D"$EXCLUDE_SCHEMA_ONLY_CLAUSE and >=20 >>> datname !~ '$SCHEMA_ONLY_DB'" >=20 >>> done >=20 >>>=20 >=20 >>> FULL_BACKUP_QUERY=3D"select datname from pg_database where not >=20 >>> datistemplate and datallowconn $EXCLUDE_SCHEMA_ONLY_CLAUSE order by >=20 >>> datname;" >=20 >>>=20 >=20 >>> echo -e "\n\nPerforming full backups" >=20 >>> echo -e "--------------------------------------------\n" >=20 >>>=20 >=20 >>> for DATABASE in `psql -h "$HOSTNAME" -U "$USERNAME" -At -c >=20 >>> "$FULL_BACKUP_QUERY" postgres` >=20 >>> do >=20 >>> if [ $ENABLE_PLAIN_BACKUPS =3D "yes" ] >=20 >>> then >=20 >>> echo "Plain backup of $DATABASE" >=20 >>>=20 >=20 >>> if ! pg_dump -Fp -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | >=20 >>> gzip > $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress; then >=20 >>> echo "[!!ERROR!!] Failed to produce plain backup >=20 >>> database $DATABASE" 1>&2 >=20 >>> else >=20 >>> mv $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress >=20 >>> $FINAL_BACKUP_DIR"$DATABASE".sql.gz >=20 >>> fi >=20 >>> fi >=20 >>>=20 >=20 >>> if [ $ENABLE_CUSTOM_BACKUPS =3D "yes" ] >=20 >>> then >=20 >>> echo "Custom backup of $DATABASE" >=20 >>>=20 >=20 >>> if ! pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" >=20 >>> -f $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress; then >=20 >>> echo "[!!ERROR!!] Failed to produce custom backup >=20 >>> database $DATABASE" 1>&2 >=20 >>> else >=20 >>> mv $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress >=20 >>> $FINAL_BACKUP_DIR"$DATABASE".custom >=20 >>> fi >=20 >>> fi >=20 >>>=20 >=20 >>> done >=20 >>>=20 >=20 >>> echo -e "\nAll database backups complete!" >=20 >>>=20 >=20 >>> I'm appreciate if anyone know how to correct this? >=20 >>>=20 >=20 >>> thank you. >=20 >>>=20 >=20 >>> v/r, >=20 >>>=20 >=20 >>> Bach-Nga >=20 >>>=20 >=20 >>> No one in this world is pure and perfect.=C2=A0 If you avoid people for >=20 >>> their mistakes you will be alone. So judge less, love and forgive >=20 >>> more. >=20 >>> To call him a dog hardly seems to do him justice though in as much >=20 >>> as he had four legs, a tail, and barked, I admit he was, to all >=20 >>> outward appearances. But to those who knew him well, he was a >=20 >>> perfect gentleman (Hermione Gingold) >=20 >>> **Live simply **Love generously **Care deeply **Speak kindly. >=20 >>> *** Genuinely rich *** Faithful talent *** Sharing success >=20 >>=20 >=20 >>=20 >=20 >>=20 >=20 >> Links: >=20 >> ------ >=20 >> [1] http://www.shreeyansh.com/ =20 =20 =20 ------=_Part_5624888_1602436000.1551203198354 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
same error:

r= un as root:
# export HOSTNAME=3Dhostname
# export USERNAME=3Dpostgres
# export= DATABASE=3Dpostgres 
# export PORT 5432
# pg_dump -v -p $PORT -U $USERNAME -d $DATABASE -f /tmp= /postgresbakup.sql
=
pg= _dump:[archiver (db)]connection to database "DATABASE" failed: could not co= nnect to server: Connection refused
Is the server running on host "portal-p" (138.= 24.1.110) and acceptiong TCP/IP connection on port 5432?

run as postgres
# export HOSTNAME= =3Dhostname
# export USERNAME=3Dpostgres
# export DATABASE=3Dpostgres 
# export PORT 5432
# pg_dump -v -p $PO= RT -U $USERNAME -d $DATABASE -f /tmp/postgresbakup.sql

pg_dump:[archiver (db)]connection to database "DATABASE" failed: FATAL= : Ident authentication failed for user "postgres"


portal-p already on /etc/hosts.=

v/r,

=

Bach-Nga

No one in this world is pure and perfect.  If you avoid peo= ple for their mistakes you will be alone. So judge less, love and forgive m= ore.
To call him a dog hardly seems to do him j= ustice though in as much as he had four legs, a tail, and barked, I admit h= e 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 ki= ndly.
*** Genuinely rich *** Faithful talent *** Sharing suc= cess


<= div class=3D"yahoo_quoted" style=3D"display: block;">
On Tuesday, February 26, 2019 11:01 AM, Shreeyansh Dba <shreeyansh20= 14@gmail.com> wrote:


Ohh, It looks your pg_hba entri= es are correct, reset your database user "postgres" password then try = again or use -w option, use a single hba.conf file.

try: pg_dump -v -p $PORT -U $USERNAME -d $DATABASE -f /tmp/pos= tresp.sql

Thanks & Regards,
Shreeyansh DBA T= eam
www.shreeyansh.com=


On Tue, Feb 26, 2019 at 9:06 PM Pepe TD Vo <pepevo@yahoo.com> wrote= :
yes mam'm/sir.   I already= posted it

here it is again.

= #vi pg_hba.conf  (from /usr/local/pgsql/data/pg_hba.conf) 
<= div id=3D"yiv4439118880gmail-m_-6997542720245005570yiv0143508912yui_3_16_0_= ym19_1_1551114507366_234981">
# "local" is for Unix domain socket connectiosn only

local    &n= bsp;all                 all&nb= sp;                     &= nbsp;      trust

# IPv4 local connections:

host     all&nbs= p;                all    =      127.0.0.1/32 trust

# IPv6 loc= al connections:

= host     all        &n= bsp;        all         ::1/12= 8         trust

# allow all replication connections fr= om localhost, by a user with the replication privilege
local    replication    &nbs= p; all                   =           trust
host    replication      all  &= nbsp;       127.0.0.1/32  trust
host    replication      al= l          ::1/128        &nbs= p;trust

I also foun= d pg_hba.conf on both folders  /var/lib/pgsql/11/data/pg_hba.conf &= ; /var/lib/pgsql/data/pg_hba.conf).  I modified it as above and the sc= ript is not working even run from the command prompt.

#vi pg_hba.conf ( (from /var= /lib/pgsql/11/data/pg_hba.conf & /var/lib/pgsql/data/pg_hba.conf) =

#TYPE  DATABASE  &nbs= p;  USER     ADDRESS     METHOD
# "local" is for Unix domain socket connec= tiosn only

local    &n= bsp;all                 all&nb= sp;                     &= nbsp;      trust
# IPv4 local connections:
<= br clear=3D"none" id=3D"yiv4439118880gmail-m_-6997542720245005570yui_3_16_0= _ym19_1_1551114507366_257727">
host =    all                &n= bsp;all         127.0.0.1/32 trust
0= .0.0.0/0     md5
host&= nbsp;     all            other_user= 0.0.0.0/= 0      md5
host  =     all            storageLoader 0.0.0.0/0 md5

# IPv6 local connections:

host     all   = ;              all      &= nbsp;  ::1/128         trust

# allow all replication connections from localhost, by a us= er with the replication privilege
local&n= bsp;   replication      all        =                      = ; trust
host    replication&nbs= p;     all          127.0.0.1/32  &nb= sp;rust
host    replication&nbs= p;     all          ::1/128   =      trust

why do I ha= ve 3 pg_hba.conf?  which one should use?

thank you for looking into it.

v/r,
 
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for th= eir mistakes you will be alone. So judge less, love and forgive more.
To call him a do= g hardly seems to do him justice though in as much as he had four legs, a t= ail, and barked, I admit he was, to all outward appearances. But to those w= ho knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak k= indly.
*** Genuinely rich *** Faithful ta= lent *** Sharing success


=
=
On Tuesday, February 26, 2019 9:52 AM, Shreeyansh Dba &= lt;shreeyans= h2014@gmail.com> wrote:


The error message says the authentication method used isn't trust. Can = you please share your pg_hba.conf file entries.

Thanks & Regards,
Shreeyansh DBA Team<= /b>
www.shr Hi Pepe eeyansh.com
=


On Tue, Feb 26, 2019 at 7:41 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
No sir. = ;  I have tried what your suggestion and still failed.

as root:

# export HOSTNAME=3D"localhost"
# export USER= NAME=3D"postgres"
# export DATABASE=3D"postgres"
# p= g_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" > /tmp/postresp.sql=

pg_dump:[archiver (db)] connect= ion to database "DATABASE" failed: FATAL: Ident authentication failed for u= ser "postgres"

= # export HOSTNAME=3D"127.0.0.1"
# export USERNAME=3D= "postgres"
# export DATABASE=3D"postgres"
# pg_dump = -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" > /tmp/postresp.sql=
=
pg_dump:[archiver (db)] connection to database "DATABASE" fai= led: FATAL: Ident authentication failed for user "postgres"


as same as post= gres:

#su - postgres

 export HOSTNAME=3D"localhost"=
= # expo= rt USERNAME=3D"postgres"
# export DATABASE=3D"postgres"
# pg_dump -Fc -h "$= HOSTNAME" -U "$USERNAME" "$DATABASE" > /tmp/postresp.sql

pg_dump:[archiver (db)] connection to database "DATABASE" failed: FA= TAL: Ident authentication failed for user "postgres"

<= /font>
= # expo= rt HOSTNAME=3D"127.0.0.1"
# export USERNAME=3D"postgres"
# export DATABASE= =3D"postgres"
# pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" > = /tmp/postresp.sql

pg_dump:[archiver (db)] connection to= database "DATABASE" failed: FATAL: Ident authentication failed for user "p= ostgres"


as = the postgres, I can run pgdump dbname > output.sql

=
v/r,
&n= bsp;
Bach-Nga

No one in this world is pure and perfect.  If you av= oid people for their mistakes you will be alone. So judge less, love and fo= rgive more.
= To call him a dog hardly seems to do him justice though in as much as he h= ad four legs, a tail, and barked, I admit he was, to all outward appearance= s. But to those who knew him well, he was a perfect gentleman (Hermione Gin= gold)

**Live simp= ly **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing s= uccess


On Monday, February 25, 2019 2:39 PM, "gilberto.castillo@etecsa.cu" <gilberto= .castillo@etecsa.cu> wrote:


El 2019-02-25 14:16, Pepe TD Vo escribi=C3=B3:
> I am running script in local host (same db ser= ver)

El 2019-02-25 14:16, Pepe TD Vo escribi=C3=B3:
> I am running script in local host (same db s= erver)


Please, Check this= :

root@gilbertoc:/ho= me/gilbertoc# export HOSTNAME=3D"localhost";
USERNAME=3D"postgres"; DATABASE=3D"postgres"
=

root@gilbertoc:/home/gilbertoc# pg_dum= p -Fc -h "$HOSTNAME" -U
"$USERNAM= E" "$DATABASE" > /tmp/postresp.sql
Password:
pg_dump: [archiver (= db)] connection to database "postgres" failed:
FATAL:  password authentication failed for user "postgre= s"
FATAL:  password authentic= ation failed for user "postgres"
<= br clear=3D"none">

Other:

root@gil= bertoc:/home/gilbertoc# sudo su - postgres

postgres@gilbertoc:~$ export HOSTNAME=3D= "localhost"; USERNAME=3D"postgres";
DATABASE=3D"postgres"

postgres@gilbertoc:~$ pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME= "
"$DATABASE" > /tmp/postresp.= sql
  /tmp/postresp.sql: Perm= iso denegado


Fine:

postgres@gilbertoc= :~$ export HOSTNAME=3D"127.0.0.1"; USERNAME=3D"postgres";
DATABASE=3D"postgres"

postgres@gilbertoc:~$ pg_dump -Fc -h "$H= OSTNAME" -U "$USERNAME"
"$DATABAS= E" > /tmp/salvapostgres.backup
= postgres@gilbertoc:~= $ ls /tmp/salvapostgres.backup
/tm= p/salvapostgres.backup


= >
> Bach-Nga
>
&= gt; No one in this world is pure and perfect.  If you avoid people for=
> their mistakes you will be a= lone. So judge less, love and forgive
> more.
> To call him a = dog hardly seems to do him justice though in as much as
<= /div>
> 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 Gingold)
> **Live simply **Love generously **Care deeply **Sp= eak kindly.
> *** Genuinely ric= h *** Faithful talent *** Sharing success
>
>  On Monda= y, February 25, 2019 1:58 PM, "gilberto.castillo@etecsa.cu"
>
> El 2019-02-25 13:48, Pepe TD Vo escribi=C3=B3:
>
= >> Thank you for your information.
>
>> I saw this= document this morning and I did check my pg_hba.conf and
>
>= > for local host and tried to connect to the template1/0 and/or other
>
>> database with postgres fine without prompting me for th= e password
>
>>
= >
>> #psql -d template0 = -U postgres
>
>> template0-#
>
>> #psql -= d template1 -U postgres
>
>> template1-#
>
>= >
>
>> #psql -d CIDR -U postgres
>
>>= CIDR-#
>
>> #psql -d postgres -U postgres
>
&g= t;> postgres-#
>
>>
>
>> and a pg_h= ba.conf:
>
<= /div>
>>
&g= t;
>> "
<= /div>
>
>&g= t;
>
=
>> #TYPE  DATABASE    USER  &nbs= p; ADDRESS    METHOD
>= ;
>> local    all=                 all    &n= bsp;                     =   trust
>
>> host    all     = ;           all        127.0.0.1/32= trust
>
>> host    all      =           all        ::1/128&n= bsp;       trust
&g= t;
> Look here the IP only con= nect to localhost. From where Are your
> running
>
> the script?
<= div dir=3D"ltr">>
>> #al= lo replication connections from localhost, by a user with the
>
= >> replication privilege
>= ;
>> local    rep= lication      all            &= nbsp;                 trust
>
>> host    replication      all  =         127..0.1/32  trust
>
>> h= ost    replication      all      &n= bsp;   ::1/128        trust
>
>> =
>
>> the problem is I'm not sure I need to reconfigure pg= _hba.conf with
>
>> database with user postgres or not?
>
>>
>
>> When I'm connecting into the eac= h database and the "show
> pg_h= ba.conf"
>
<= /div>
>> show nothing.
>
>>
>
>> postgres-# show pg_hba.conf
>
>> postgres-#=
>
>>
>
>> Bach-Nga
>
>>= ;
>
<= div dir=3D"ltr">>> No one in this world is pure and perfect.  If= you avoid people for
>
>> their mistakes you will be alon= e. So judge less, love and forgive
>
>> more.
>
>> To call him a dog hardly seems to do him justice though in as muc= h
> 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.
>
&= gt;> *** Genuinely rich *** Faithful talent *** Sharing success
>
>>
>
>>  On Monday, February 25, 2019 12:= 28 PM, Shreeyansh Dba
>
>
<= div dir=3D"ltr">>>
>
>> Hi Pepe,
=
>
>&= gt;
>
>> It seems your are using os user for taking the b= ackup.
>
>>
>=
>> When you run command li= ne commands against the postgres database and
>
>> they as= k for the user and password that is the database user
> postgres
= >
>> and its associated = password, so when you do something like: psql -d
>
>> temp= late1 -U postgres the password you will be prompted for is
>
>= ;> your_db_user password
> <= br clear=3D"none">
>>
=
>
>> No= w the above also depends on what is set in pg_hba.conf for the
>
>> various combinations of host, database, user and auth_method.
>
>>
>
>> For more information see
>
>>
>
>>
>
>> Thanks & Regards,
>
>> SHREEYANSH DBA TE= AM
>
<= div dir=3D"ltr">>> www.shreeyansh.com [1]
>
>>
>
>> On Mon, Feb 25, 2019 at 10:21 PM Pepe = TD Vo <pepevo@yahoo.com>
>
>>> good morning Expert DBAs,
>
&g= t;>>
>
>>> I created a script to do backup and I= have faced the error of
>
>>>
>
>>&g= t;> Performing globals backup:
= >
>>>> pg_dumpall:= could not connect to database "template1":FATAL: Ident
<= /div>
>
>&g= t;>> authentication failed for user "postgres" fro globals backup.&nb= sp; As
>
>>>> same as performing schema-only backups= and full backup.
>
>>>>
=
>
>>>= ;> Performing schema-only backup:
>
>>>> psql:FAT= AL: Ident authentication failed for user "postgres"
>
>>&g= t;> the following databases were matched for schema-only backup:
>
>>>>
>
>>>> performing full backu= p:
>
<= div dir=3D"ltr">>>>> psql:FATAL: Ident authentication failed fo= r user "postgres"
>
>>>
>
>>> I = can run pg_dumpall > postgresdump.sql as postgres user os but
>
>>> couldn't run from script even the script declared
>
>>> BACKUP_USER=3Dpostgres
>
>>>
>
>>> If I take the syntax, -H "$HOSTNAME" -U "$USERNAME" o= ut and the
>
>>> backup script working fine
>
>>>
>
>>> # VI PG_BACKUP.CONFIG
>
>>>
>
>>> ##############################
>
>>> ## POSTGRESQL BACKUP CONFIG ##
>
>>= ;> ##############################
>
>>>
>
>>> # Optional system user to run backups as.  If the user = the script
> is
>
&g= t;>> running as doesn't match this
>
>>> # the = script terminates.  Leave blank to skip check.
>
>>&g= t; BACKUP_USER=3D
>
>>>
>
>>> # = Optional hostname to adhere to pg_hba policies.  Will default to
>
>>> "localhost" if none specified.
>
>>&= gt; HOSTNAME=3D"hostname"
>
>>>
>
>>&= gt; # Optional username to connect to database as.  Will default to
>
>>> "postgres" if none specified.
>
>>= > USERNAME=3D"postgres"
>
>>>
>
>>= > # This dir will be created if it doesn't exist.  This must be
>
>>> writable by the user the script is
>
>= >> # running as.
>
>>> BACKUP_DIR=3D/home/backups= /database/postgresql/
>
>>>
>
>>> = # List of strings to match against in database name, separated by
>
>>> space or comma, for which we only
>
>>&g= t; # wish to keep a backup of the schema, not the data. Any database
>
>>> names which contain any of these
>
>>= ;> # values will be considered candidates. (e.g. "system_log" will
>
>>> match "dev_system_log_2010-01")
>
>>= > SCHEMA_ONLY_LIST=3D""
>
>>>
>
>>= > # Will produce a custom-format backup if set to "yes"
>
>= ;>> ENABLE_CUSTOM_BACKUPS=3Dyes
>
>>>
>
>>> # Will produce a gzipped plain-format backup if set to "y= es"
>
=
>>> ENABLE_PLAIN_BACKUPS=3Dyes
=
>
>&= gt;>
>
<= /div>
>>> # Will produce gzipped sql file containi= ng the cluster globals,
> like<= br clear=3D"none">
>
>>> users and passwords, if set to "yes"
>
>>> ENABLE_GLOBALS_BACKUPS=3Dyes
>
>>>
>
>>> #### SETTINGS FOR ROTATED BACKUPS ####
>
&= gt;>>
>
>>> # Which day to take the weekly backu= p from (1-7 =3D Monday-Sunday)
>= ;
>>> DAY_OF_WEEK_TO_KEE= P=3D5
>
>>>
&= gt;
>>> # Number of days= to keep daily backups
>
>>> DAYS_TO_KEEP=3D7
>
>>>
>
>>> # How many weeks to keep week= ly backups
>
>>> WEEKS_TO_KEEP=3D5
>
>>= ;>
>
>>> ######################################
>
>>>
>
>>> #VI PG_BACKUP.SH
>
>>>
>
>>> #!/bin/bash
>
>= >>
>
=
>>> ###########################
>
>>> ####### LOAD CONFIG #######
>
>>> ######= #####################
>
>>>
>
>>> = while [ $# -gt 0 ]; do
>
>>> case $1 in
>
&g= t;>> -c)
>
>>> if [ -r "$2" ]; then
>
= >>> source "$2"
>
>>> shift 2
>
>= ;>> else
>
>>> ${ECHO} "Unreadable config file
>
>>> \"$2\"" 1>&2
>
>>> exit = 1
>
>>> fi
>= ;
>>> ;;
>
&g= t;>> *)
>
>>> ${ECHO} "Unknown Option \"$1\"" 1&g= t;&2
>
<= /div>
>>> exit 2
>
>>> ;;
>
>>> esac
> <= br clear=3D"none">
>>> done
>
>= ;>>
>
>>> if [ $# =3D 0 ]; then
>
>= ;>> SCRIPTPATH=3D$(cd ${0%/*} && pwd -P)
>
>>= ;> source $SCRIPTPATH/pg_backup.config
>
>>> fi;
>
>>>
>
>>> ########################= ###
>
=
>>> #### PRE-BACKUP CHECKS ####
>
>= >> ###########################
>
>>>
>
>>> # Make sure we're running as the required backup user
>
>>> if [ "$BACKUP_USER" !=3D "" -a "$(id -un)" !=3D "$BAC= KUP_USER" ]; then
>
>>> echo "This script must be run= as $BACKUP_USER. Exiting." 1>&2
>
>>> exit 1;
>
>>> fi;
> =
>>>
<= /div>
>
>&g= t;> ###########################
>
>>> ### INITIALISE= DEFAULTS ###
>
>>> ###########################
>
>>>
>
>>> if [ ! $HOSTNAME ]; then
>
>>> HOSTNAME=3D"localhost"
>
>>> fi;<= br clear=3D"none">
>
>>>
>
>>> if [ ! $USERNAME ]; th= en
>
<= div dir=3D"ltr">>>> USERNAME=3D"postgres"
=
>
>>>= ; fi;
>
>>>
&= gt;
>>> ################= ###########
>
>>> #### START THE BACKUPS ####
>
>>> ###########################
>
>>>
>
>>> FINAL_BACKUP_DIR=3D$BACKUP_DIR"`date +\%Y-\%m-\%d`/= "
>
>>>
> =
>>> echo "Making backup = directory in $FINAL_BACKUP_DIR"
&g= t;
>>>
>
>= ;>> if ! mkdir -p $FINAL_BACKUP_DIR; then
>
>>> e= cho "Cannot create backup directory in $FINAL_BACKUP_DIR. Go
>
&= gt;>> and fix it!" 1>&2
>
>>> exit 1;
>
>>> fi;
>
>>>
>
>>&= gt; #######################
> <= br clear=3D"none">
>>> ### GLOBALS BACKUPS #= ##
>
<= div dir=3D"ltr">>>> #######################
>
>>&= gt;
>
>>> echo -e "\n\nPerforming globals backup"
>
>>> echo -e "--------------------------------------------= \n"
>
=
>>>
>= ;
>>> if [ $ENABLE_GLOBA= LS_BACKUPS =3D "yes" ]
>
>>> then
>
>>= > echo "Globals backup"
>
>>>
>
>>= > if ! pg_dumpall -g -h "$HOSTNAME" -U "$USERNAME" | gzip >
>
>>> $FINAL_BACKUP_DIR"globals".sql.gz.in_progress; then
>
>>> echo "[!!ERROR!!] Failed to produce globals backup"
>
>>> 1>&2
>
>>> else
>
>>> mv $FINAL_BACKUP_DIR"globals".sql.gz.in_progress
>
>>> $FINAL_BACKUP_DIR"globals".sql.gz
>
>>&= gt; fi
>
>>> else
>
>>> echo "None"=
>
>>> fi
>=
>>>
=
>
>&= gt;> ###########################
>
>>> ### SCHEMA-ON= LY BACKUPS ###
>
>>> ###########################
>
>>>
>
>>> for SCHEMA_ONLY_DB in ${SC= HEMA_ONLY_LIST//,/ }
>
>>> do
<= div dir=3D"ltr">>
>>>= SCHEMA_ONLY_CLAUSE=3D"$SCHEMA_ONLY_CLAUSE or datname ~
<= /div>
>
>&g= t;> '$SCHEMA_ONLY_DB'"
>
>>> done
=
>
>&= gt;>
>
<= /div>
>>> SCHEMA_ONLY_QUERY=3D"select datname from= pg_database where false
>
>>> $SCHEMA_ONLY_CLAUSE orde= r by datname;"
>
>>>
>
>>> echo -= e "\n\nPerforming schema-only backups"
>
>>> echo -e "-= -------------------------------------------\n"
>
>>>
>
>>> SCHEMA_ONLY_DB_LIST=3D`psql -h "$HOSTNAME" -U "$US= ERNAME" -At -c
>
>>> "$SCHEMA_ONLY_QUERY" postgres`
>
>>>
>
>>> echo -e "The following da= tabases were matched for schema-only
>
>>> backup:\n${S= CHEMA_ONLY_DB_LIST}\n"
>
>>>
<= div dir=3D"ltr">>
>>>= for DATABASE in $SCHEMA_ONLY_DB_LIST
>
>>> do
>
>>> echo "Schema-only backup of $DATABASE"
=
>
>&= gt;>
>
<= /div>
>>> if ! pg_dump -Fp -s -h "$HOSTNAME" -U "$= USERNAME" "$DATABASE" |
>
>>> gzip > $FINAL_BACKUP_D= IR"$DATABASE"_SCHEMA.sql.gz.in_progress; then
>
>>> ech= o "[!!ERROR!!] Failed to backup database schema of
=
>
>>>= ; $DATABASE" 1>&2
>
>>> else
<= /div>
>
>&g= t;> mv $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress
>
>>> $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz
>
&= gt;>> fi
>
>>> done
>
>>>
>
>>> ###########################
>
>>&g= t; ###### FULL BACKUPS #######
>= ;
>>> ##################= #########
>
=
>>>
>
>>> for SCHEMA_O= NLY_DB in ${SCHEMA_ONLY_LIST//,/ }
>
>>> do
>
>>> EXCLUDE_SCHEMA_ONLY_CLAUSE=3D"$EXCLUDE_SCHEMA_ONLY_CLAUSE an= d
>
>>> datname !~ '$SCHEMA_ONLY_DB'"
>
>= >> done
>
>>>
>
>>> FULL_B= ACKUP_QUERY=3D"select datname from pg_database where not
=
>
>&= gt;> datistemplate and datallowconn $EXCLUDE_SCHEMA_ONLY_CLAUSE order by=
>
>>> datname;"
>
>>>
>
>>> echo -e "\n\nPerforming full backups"
>
>>&= gt; echo -e "--------------------------------------------\n"
>
&= gt;>>
>
>>> for DATABASE in `psql -h "$HOSTNAME"= -U "$USERNAME" -At -c
>
>>> "$FULL_BACKUP_QUERY" postg= res`
>
>>> do
= >
>>> if [ $ENABLE_PL= AIN_BACKUPS =3D "yes" ]
>
>>> then
>
>>= ;> echo "Plain backup of $DATABASE"
>
>>>
>
>>> if ! pg_dump -Fp -h "$HOSTNAME" -U "$USERNAME" "$DATABASE= " |
>
=
>>> gzip > $FINAL_BACKUP_DIR"$DATABASE".sql.gz= .in_progress; then
>
>>> echo "[!!ERROR!!] Failed to p= roduce plain backup
>
>>> database $DATABASE" 1>&= ;2
>
<= div dir=3D"ltr">>>> else
= >
>>> mv $FINAL_BACKU= P_DIR"$DATABASE".sql.gz.in_progress
>
>>> $FINAL_BACKUP= _DIR"$DATABASE".sql.gz
>
>>> fi
>
>>&g= t; fi
>
>>>
&= gt;
>>> if [ $ENABLE_CUS= TOM_BACKUPS =3D "yes" ]
>
>>> then
>
>>= ;> echo "Custom backup of $DATABASE"
>
>>>
>
>>> if ! pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE= "
>
>>> -f $FINAL_BACKUP_DIR"$DATABASE".custom.in_progr= ess; then
>
=
>>> echo "[!!ERROR!!] Failed to produce cus= tom backup
>
>>> database $DATABASE" 1>&2
>
>>> else
>
>>> mv $FINAL_BACKUP_DIR"$= DATABASE".custom.in_progress
> =
>>> $FINAL_BACKUP_DIR"$D= ATABASE".custom
>
>>> fi
>
>>> fi
>
>>>
>
>>> done
=
>
>&= gt;>
>
<= /div>
>>> echo -e "\nAll database backups complete= !"
>
<= div dir=3D"ltr">>>>
>=
>>> I'm appreciate if a= nyone know how to correct this?
&g= t;
>>>
>
>= ;>> thank you.
>
>>>
>
>>> 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
>
&g= t;>> more.
>
>>> To call him a dog hardly seems t= o do him justice though in as much
>
>>> as he had four= legs, a tail, and barked, I admit he was, to all
<= div dir=3D"ltr">>
>>>= 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
>
>>
>
>>
>
>>
>
>> Links:
>
>> ------
=
>


=


=


------=_Part_5624888_1602436000.1551203198354--