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 1gzMeH-0006zY-60 for pgsql-admin@arkaria.postgresql.org; Thu, 28 Feb 2019 14:26:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gzMeF-0004Xo-BZ for pgsql-admin@arkaria.postgresql.org; Thu, 28 Feb 2019 14:26:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gzMeE-0004XZ-Um for pgsql-admin@lists.postgresql.org; Thu, 28 Feb 2019 14:26:55 +0000 Received: from sonic316-13.consmr.mail.bf2.yahoo.com ([74.6.130.123]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gzMdy-0002mk-E1 for pgsql-admin@lists.postgresql.org; Thu, 28 Feb 2019 14:26:54 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1551363995; bh=QhPaKrQo81P2ub75Vrc49RNa3BSmAyG/QExxccZTkzQ=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=KMkuTb0JQ6Cq0NyTHeTiJ5o7gPpVCcGkmTKPC7W0Canum9Ijkpc1raeOO6qzbnRmXaadHtAJ8qZ1DZEVTbJ8hWcgSCNFQhHeFThCjUr9gcWK0PSE4e6pHYfZuvfLGSaqc/cZr5YXwFpIK2IrjnZTbkG0dxDbJaLspbfXOiXqvBNyCA4LBz3QVR7QT0N6t2kKARH4mkpOdUJx6yZNpUk4l9mLDtLU55eU46tfvk0N4umtTX8V7MjMAADgkuU2bhUCqNZCGf0Rs+eTP+28k1tmUh7TAxIyE9GgSRtzphPaskY/8OyxWy8AwkqUQPcHWrSXigxIACqDAAAKyTO/RD2+FQ== X-YMail-OSG: SQlz278VM1lEecuAl1wZNvEiXPx3W9vwfWV3BhyceSygSCGO.t2VTsCKFVlcdXI E7sVwASSu1HhW4KO_9yvosIlxEZCKrGarIN1zPJ8rBEjS.qPNxzDGr7yH30o57qS4MTXwSNEK8t. RQuT0W.5KecKObAKECGC21n6ht0UNL37FxSnagZXOqUzAm5ZED3EaN7I9brz0q0saTbFh_BdtZZf vYo0FXLDmGNyyB3RldG0ng6DUjnryE_owqfYPxScXuOnd0YK5m2e51cafC1hLg5K6OrtZSLnzvow Nl2uqTIDIC.bsoSUDp8yzr29l69EW3WxJ90_YFj9_ywFfq_E267HXtE2zrN_Tf.rnuF_8qJw8pvj 0DioOZeNEC5FHZQCdHbep7Aywr8mh3aFkghHg1CuQ_AtzVBmNpS3Ci2Hlcxpm._aqHEX.1MhOMpk nBA_CBr9juReR9_vC4kfG8zV9qT7eGtKu.Y.iNFhPMffXlHadPV2CuXN9qfmPTgIs9wh.sX.fQ8o qLeiK37.bQQAzK99qUPrrAceyqmyQ6SYTpTgg2H.1K8Wk8bXgWzXSHoqAp8wyOh6Cox_n3nMVtny BGtaEvNTXSthIPl5tnEc416PJH.HBBPhQomX2WdTPEuLik6nZjmkE93o3RDdJOiCpQAuKQG78Sx1 8wP_wWj7CaXeFZEGI2FOp1xuwS4b1SKDxpKCdeRvkslmjZZ.o1Eb9TQjLmK2P44hWhiJXufSwm6s wB11QLl8CwyujsGKE8bX9iF4JxFCYyWk5OpH3Wd4pWk6Z.XtQRq8yky643iBwtZhmGmJvIiPGDKg DDUX15RQp.72bLoeZ29edA0bXbeN.5908dYhaIHTfFiQaaVZtAaZBN9GXQ6.G188pQa2aZmVDUVA wiNHWKbDLaZqxY8tq6K2u6KLLrk9cF0zKnbhPUvNpmmEP2D9cIPLtEVzbw4oMObj.jaRgfUz6vLY dGRQw0elr67IiFpOIO9wkQ7Qd.u8qmJttQ_gE6M0WWMFcXeRp2KQjoTIgtErRbsZl5.tNhwnqc5N aM9.AxcYN2PbaAfm6KHusRwW2m7h76PJgyPFWi6oNoBn13RCb1lrf_2bcM2jNzaDIyQEPGYlS3iC yr.hKV8ZNPWY- Received: from sonic.gate.mail.ne1.yahoo.com by sonic316.consmr.mail.bf2.yahoo.com with HTTP; Thu, 28 Feb 2019 14:26:35 +0000 Date: Thu, 28 Feb 2019 14:26:33 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Pepe TD Vo , Shreeyansh Dba Cc: "gilberto.castillo@etecsa.cu" , Pgsql-admin Message-ID: <1159025674.4991119.1551363993191@mail.yahoo.com> In-Reply-To: <6A6E9DC8-D679-4790-A36A-C4302388837C@yahoo.com> References: <1766891885.5546566.1551195368839.ref@mail.yahoo.com> <1766891885.5546566.1551195368839@mail.yahoo.com> <1161831433.5624889.1551203198375@mail.yahoo.com> <6A6E9DC8-D679-4790-A36A-C4302388837C@yahoo.com> Subject: Re: [MASSMAIL]Re: pg_dumpall: could not connect to database: FATAL MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4991118_361825777.1551363993161" 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.119 Safari/537.36 Content-Length: 144471 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_4991118_361825777.1551363993161 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable thank you for you all. I hard code everything in 1 script and it works fine= now. I am appreciated for all the inputs and advises. best regards,=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 Wednesday, February 27, 2019 6:38 AM, Pepe TD Vo = wrote: =20 I am back up from local machine not remote. =C2=A0Even the port and listen= _address =3D '*' already set. I can run without environment settings but not with the environment setting= s. V/r, Bach Nga On Feb 27, 2019, at 1:14 AM, Shreeyansh Dba wrot= e: Hmmn, An error says you don't have mentioned an entry of server from where = you are performing the backup.Are you performing a backup from the local ma= chine or remote server?Check the below parameter settings and make sure you= have an entry of your remote server in pg_hba.conf file if you are taking = backup remotely.=C2=A0listen_addresses =3D '*'=20 port =3D your_db_port=20 Thanks & Regards, Shreeyansh DBA Team www.shreeyansh.com On Tue, Feb 26, 2019 at 11:16 PM Pepe TD Vo wrote: 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 =20 ------=_Part_4991118_361825777.1551363993161 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
thank you for you all. I hard code everything in 1 scr= ipt and it works fine now.

I am appreciated for all the inputs and advises.

best regards,
 
Bach-Nga

No one in this world is pure and perfec= t.  If you avoid people for their 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)<= /font>

**Live simply **Love = generously **Care deeply **Speak kindly.
*** Genuinely rich = *** Faithful talent *** Sharing success


On Wednesday, February 27, 2019 6:38= AM, Pepe TD Vo <pepevo@yahoo.com> wrote:


=
I am bac= k up from local machine not remote.  Even the port and listen_address = =3D '*' already set.

I can run w= ithout environment settings but not with the environment settings.

V/r,

Bach Nga


On Feb 27, 2019, at 1:14 AM, Shreeyansh Dba <shreeyansh2014@gmail.com>= wrote:

Hmmn, A= n error says you don't have mentioned an entry of server from where you are= performing the backup.
Are = you performing a backup from the local machine or remote server?
Check the below parameter settings and= make sure you have an entry of your remote server in pg_hba.conf file if y= ou are taking backup remotely.
 listen_addresses =3D '*'
port =3D your_db_port

Thanks & Regards,
Shreeyansh DBA Team
w= ww.shreeyansh.com

<= /div>

On Tue, Feb 26, 2019 at= 11:16 PM Pepe TD Vo <pep= evo@yahoo.com> wrote:
same error:=

run as root:
# export HOSTNAME=3Dhostname
# export USERNAME=3Dpostgres
#= export DATABASE=3Dpostgres 
# export PORT 5432<= /div>
# pg_dump -v -p $PORT -U $USERNAME -d $DA= TABASE -f /tmp/postgresbakup.sql

<= /div>
pg_dump:[archiver (db)]connection to data= base "DATABASE" failed: could not connect to server: Connection refused
Is the server running on host "portal-p" (13= 8.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 $= PORT -U $USERNAME -d $DATABASE -f /tmp/postgresbakup.sql

pg_du= mp:[archiver (db)]connection to database "DATABASE" failed: FATAL: Ident au= thentication failed for user "postgres"
<= br clear=3D"none">

portal-p already on /etc/hosts.



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.
T= o 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 Gingo= ld)

**Live simply **Love generously **Care deeply **Speak= kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Tuesday, February 26, 2019 11:01 AM, Shreeyansh Dba <= ;shreeyansh2= 014@gmail.com> wrote:


Ohh, It = looks your pg_hba entries are correct, reset your database user "postgres" = password then try again or use -w option, use a single hba.conf file.<= br clear=3D"none">
try: pg_dump -v -p $PORT -U $USERNAME -= d $DATABASE -f /tmp/postresp.sql

<= div>
=
Thanks & Regards,
Shreeyansh DBA Team
www.shreeyansh.com
<= /div>


On Tue, Feb 26, 2019 at 9:06 PM Pepe TD Vo &l= t;pepevo@yahoo.com> w= rote:
yes mam'm/si= r.   I already posted it

<= /div>
here it is again.

#vi pg_hba.conf  (from /usr/local/pgsql/data/pg_hba.conf) 
<= br clear=3D"none" id=3D"yiv3585448637gmail-m_-2299962063794332172yiv4439118= 880gmail-m_-6997542720245005570yiv0143508912yui_3_16_0_ym19_1_1551114507366= _234982">
#TYPE  DATABASE     USER  &nbs= p;  ADDRESS     METHOD
# "local" i= s for Unix domain socket connectiosn only

<= /div>
local     all          &n= bsp;      all             = ;                trust
=

# IPv4 local connections:

host    &n= bsp;all                 all&nb= sp;        127.0.0.1/32 trust

# IPv6 local connections:

host     = all                 all  =        ::1/128         trust

# allow all replication connecti= ons from localhost, by a user with the replication privilege
= local    replication      all      =                      = ;   trust
host    replication    &nbs= p; all          127.0.0.1/32  trust
=

I also found pg_hb= a.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 script is= not working even run from the command prompt.

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

#TYPE  DATABASE     USER  =    ADDRESS     METHOD
# "local" is for Unix= domain socket connectiosn only

local     all         = ;        all            &= nbsp;                trust
<= br clear=3D"none" id=3D"yiv3585448637gmail-m_-2299962063794332172yiv4439118= 880gmail-m_-6997542720245005570yui_3_16_0_ym19_1_1551114507366_257723">
# IPv4 local connec= tions:

host     all          &= nbsp;      all         127.0.0.1/32 t= rust
hos= t      all            power_us= er 0.0.0.= 0/0     md5
host      all    &n= bsp;       other_user 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         trust

# allow all= replication connections from localhost, by a user with the replication pri= vilege
local    replication      all    =                      = ;     trust
host    replication      all=           127.0.0.1/32   rust
host  &= nbsp; replication      all         = ::1/128         trust


why do I have 3 p= g_hba.conf?  which one should use?

thank you for looking into it.

<= /div>
v/r,
 
Bach-Nga

No one in this world is pure and perfect.  If you avoi= d people for their mistakes you will be alone. So judge less, love and forg= ive more.
<= span class=3D"yiv3585448637gmail-m_-2299962063794332172yiv4439118880gmail-m= _-6997542720245005570yiv0143508912sg" id=3D"yiv3585448637gmail-m_-229996206= 3794332172yiv4439118880gmail-m_-6997542720245005570yui_3_16_0_ym19_1_155111= 4507366_257638">To call him a dog hardly seems to do him justice though i= n as much as he had four legs, a tail, and barked, I admit he was, to all o= utward appearances. But to those who knew him well, he was a perfect gentle= man (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak k= indly.
*** Genuinely rich *** Faithful talent *** Sharing suc= cess


On Tuesday, February 26, 2019 9:52 AM, Shreeyansh Dba <= shreeyansh20= 14@gmail.com> wrote:


The error message says the a= uthentication method used isn't trust. Can you please share your pg_hba.con= f file entries.

=
Thanks & Regards,
Shreeyans= h DBA Team
www.shr H= i Pepe eeyansh.com


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

as root:
<= font size=3D"3">
# export HOSTNAME=3D"localhost"
# 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 faile= d for user "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 "DAT= ABASE" failed: FATAL: Ident authentication failed for user "postgres"

=
as same as postgres:

#su - postgres

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

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

# export HOSTNAM= E=3D"127.0.0.1"
# export USERNAME=3D"postgres"
# export DATA= BASE=3D"postgres"
# pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DA= TABASE" > /tmp/postresp.sql

pg_dump:[a= rchiver (db)] connection to database "DATABASE" failed: FATAL: Ident authen= tication failed for user "postgres"

<= br clear=3D"none">
as = the postgres, I can run pgdump dbname > output.sql

v/r,
 
= Bach-Nga

No one in this world is pure and perfect.&= nbsp; If you avoid people for their mistakes you will be alone. So judge le= ss, love and forgive more.
To call him a dog hardly seems to do h= im justice though in as much as he had four legs, a tail, and barked, I adm= it he was, to all outward appearances. But to those who knew him well, he w= as a perfect gentleman (Hermione Gingold)

**Live si= mply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success=


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


El 2019-02-25 14:16, Pepe TD Vo es= cribi=C3=B3:
> I am running scr= ipt in local host (same db server)

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


Please, Check this:
root@gilbertoc:/home/gilbertoc# export HOSTNAME=3D"localhost"; =
USERNAME=3D"postgres"; DATABASE= =3D"postgres"

<= /div>
root@gilbe= rtoc:/home/gilbertoc# pg_dump -Fc -h "$HOSTNAME" -U
=
"$USERNAME" "$DATABASE" > /tmp/postresp.sql
Password:
pg_dump: [archiver (db)] connection to database "postgres" fail= ed:
FATAL:  password authent= ication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

=
Other:

root@gilbertoc:/home/gilbertoc# sudo su - po= stgres

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: Permiso denegado


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

<= /div>
po= stgres@gilbertoc:~$ pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME"
"$DATABASE" > /tmp/salvapostgres.backup=
postgres@gilbertoc:~$ ls /tmp/salvapostgres.backup
/tmp/salvapostgres.backup

=
>
> Bach-Nga
> <= br clear=3D"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 for= give
> more.
=
> To call him a dog hardly seems to do him justic= e 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 we= ll, he was a perfect
> gentlema= n (Hermione Gingold)
> **Live s= imply **Love generously **Care deeply **Speak kindly.
> *** Genuinely rich *** Faithful talent *** Sharing= success
>
<= /div>
>  On Monday, February 25, 2019 1:58 PM, "gilberto.= castillo@etecsa.cu"
> <<= a rel=3D"nofollow" shape=3D"rect" ymailto=3D"mailto:gilberto.castillo@etecs= a.cu" target=3D"_blank" href=3D"mailto:gilberto.castillo@etecsa.cu">gilbert= o.castillo@etecsa.cu> wrote:
>
> El 2019-02-25 13:48, = Pepe TD Vo escribi=C3=B3:
>
>> Thank you for your informat= ion.
>
>> I saw this document this morning and I did check= my pg_hba.conf and
>
>> for local host and tried to conne= ct to the template1/0 and/or other
>
>> database with post= gres fine without prompting me for the password
>
>>
>
>> #psql -d template0 -U postgres
>
>> temp= late0-#
>
>> #psql -d template1 -U postgres
>
&= gt;> template1-#
>
>>
>
>> #psql -d C= IDR -U postgres
>
>> CIDR-#
>
>> #psql -d= postgres -U postgres
>
>> postgres-#
>
>>= ;
>
<= div dir=3D"ltr">>> and a pg_hba.conf:
>
>>
>
>> "
>
>>
>
>> #TYPE  DA= TABASE    USER    ADDRESS    METHOD
>
>> local    all          &nbs= p;     all               =             trust
>
>> host=     all                al= l        127.0.0.1/32 trust
>
>> host&= nbsp;   all                all=         ::1/128        trust
>
> Look here the IP only connect to localhost. From where Are yo= ur
> running
=
>
> = the script?
>
>> #allo replication connections from localh= ost, by a user with the
>
>> replication privilege
>
>> local    replication      all =                     &nbs= p;       trust
>=
>> host    repli= cation      all          127..0.1/3= 2  trust
>
>> host    replication  &nb= sp;   all          ::1/128     = ;   trust
>
>>
>
>> the problem is = I'm not sure I need to reconfigure pg_hba.conf with
>
>> d= atabase with user postgres or not?
>
>>
>
>= > When I'm connecting into the each database and the "show
> pg_hba.conf"
>
>> show no= thing.
>
>>
>=
>> postgres-# show pg_hba.= conf
>
>> postgres-#
>
>>
>
&= gt;> Bach-Nga
>
>>
>
>> No one in thi= s 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 see= ms 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 thos= e who knew him well, he was a perfect
>
>> gentleman (Herm= ione Gingold)
>
>> **Live simply **Love generously **Care = deeply **Speak kindly.
>
>> *** Genuinely rich *** Faithfu= l talent *** Sharing success
> =
>>
>
>>&n= bsp; On Monday, February 25, 2019 12:28 PM, Shreeyansh Dba
>
>
>>
>
&= gt;> Hi Pepe,
>
>>
>
>> It seems your= are using os user for taking the backup.
>
>>
>
>> When you run command line commands against the postgres datab= ase and
>
>> they ask for the user and password that is th= e database user
> postgres
>
>> and its associated password, so when you do something lik= e: psql -d
>
>> template1 -U postgres the password you wil= l be prompted for is
>
>> your_db_user password
>
>>
>
>> Now the above also depends on what is = set in pg_hba.conf for the
>
>> various combinations of ho= st, database, user and auth_method.
>
>>
>
>= ;> For more information see
>= ;
>>
>
>>
> =
>> Thanks & Regards,
>
>> SHREEYANSH DBA TEAM
>
> <= br clear=3D"none">
>>
=
>
>> On= Mon, Feb 25, 2019 at 10:21 PM Pepe TD Vo <pepevo@yahoo.com>
> wrote:
>
>>
>
>>> good = morning Expert DBAs,
>
>>>
>
>>> I= created a script to do backup and I have faced the error of
>
&= gt;>>
>
>>>> Performing globals backup:
>
>>>> pg_dumpall: could not connect to database "templa= te1":FATAL: Ident
>
>>>> authentication failed for= user "postgres" fro globals backup.  As
>
>>>>= same as performing schema-only backups and full backup.
=
>
>&= gt;>>
>
>>>> Performing schema-only backup:
>
>>>> psql:FATAL: Ident authentication failed for us= er "postgres"
>
>>>> the following databases were ma= tched for schema-only backup:
>=
>>>>
>
&= gt;>>> performing full backup:
>
>>>> psql:= FATAL: Ident authentication failed for 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<= br clear=3D"none">
>
>>>
>
>>> If I take the syntax, = -H "$HOSTNAME" -U "$USERNAME" out and the
>
>>> backup= script working fine
>
>>>
>
>>> #= VI PG_BACKUP.CONFIG
>
>>>
>
>>> #= #############################
>=
>>> ## POSTGRESQL BACKU= P CONFIG ##
>
>>> ##############################
>
>>>
>
>>> # Optional system user to = run backups as.  If the user the script
> is
>
>>> running as doesn't match this=
>
>>> # the script terminates.  Leave blank to sk= ip check.
>
=
>>> BACKUP_USER=3D
=
>
>>>= ;
>
<= div dir=3D"ltr">>>> # Optional hostname to adhere to pg_hba polici= es.  Will default to
>
>>> "localhost" if none spe= cified.
>
>>> HOSTNAME=3D"hostname"
<= /div>
>
>&g= t;>
>
>>> # Optional username to connect to databas= e as.  Will default to
> <= br clear=3D"none">
>>> "postgres" if none sp= ecified.
>
<= /div>
>>> USERNAME=3D"postgres"
=
>
>&= gt;>
>
<= /div>
>>> # This dir will be created if it doesn't= exist.  This must be
>
>>> writable by the user t= he script is
>
>>> # running as.
>
>>&= gt; BACKUP_DIR=3D/home/backups/database/postgresql/
>
>>&g= t;
>
=
>>> # List of strings to match against in databas= e name, separated by
>
>>> space or comma, for which we= only
>
>>> # 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""
>
>>&= gt;
>
>>> # Will produce a custom-format backup if set= to "yes"
>
=
>>> ENABLE_CUSTOM_BACKUPS=3Dyes
>
>>>
>
>>> # Will produce a gzipped plain-fo= rmat backup if set to "yes"
> <= br clear=3D"none">
>>> ENABLE_PLAIN_BACKUPS= =3Dyes
>
>>>
= >
>>> # Will produce = gzipped sql file containing the cluster globals,
> like
>
>>> users and passwords, if = set to "yes"
>
>>> ENABLE_GLOBALS_BACKUPS=3Dyes
>
>>>
>
>>> #### SETTINGS FOR ROTATED BAC= KUPS ####
>
=
>>>
>
>>> # Which day = to take the weekly backup from (1-7 =3D Monday-Sunday)
>
>>= ;> DAY_OF_WEEK_TO_KEEP=3D5
>=
>>>
=
>
>&= gt;> # Number of days to keep daily backups
>
>>> DA= YS_TO_KEEP=3D7
>
>>>
>
>>> # How = many weeks to keep weekly backups
= >
>>> WEEKS_TO_KEEP= =3D5
>
>>>
&g= t;
>>> #################= #####################
>
>>>
>
>>> = #VI PG_BACKUP.SH
>
>>>
>
>>> #!/bi= n/bash
>
>>>
= >
>>> ###############= ############
>
>>> ####### LOAD CONFIG #######
>
>>> ###########################
>
>>>
>
>>> while [ $# -gt 0 ]; do
>
>>> ca= se $1 in
>
<= /div>
>>> -c)
>
>>> if [ -r "$= 2" ]; then
>
>>> source "$2"
>
>>> = shift 2
>
>>> else
>
>>> ${ECHO} "U= nreadable config file
>
>>> \"$2\"" 1>&2
>
>>> exit 1
>
>>> fi
>
>>= ;> ;;
>
<= /div>
>>> *)
>
>>> ${ECHO} "Un= known Option \"$1\"" 1>&2
&= gt;
>>> exit 2
>
>>> ;;
>
>>> esac
>
>>&g= t; done
>
>>>
>
>>> if [ $# =3D 0 = ]; then
>
>>> SCRIPTPATH=3D$(cd ${0%/*} && pwd = -P)
>
=
>>> source $SCRIPTPATH/pg_backup.config
>
>>> fi;
>
>>>
>
>>> = ###########################
> <= br clear=3D"none">
>>> #### PRE-BACKUP CHECK= S ####
>
>>> ###########################
>
&= gt;>>
>
>>> # Make sure we're running as the req= uired backup user
>
>>> if [ "$BACKUP_USER" !=3D "" -= a "$(id -un)" !=3D "$BACKUP_USER" ]; then
>
>>> echo "= This script must be run as $BACKUP_USER. Exiting." 1>&2
>
>>> exit 1;
>
>>> fi;
>
>>&g= t;
>
=
>>> ###########################
>
>= >> ### INITIALISE DEFAULTS ###
>
>>> ############= ###############
>
>>>
>
>>> if [ != $HOSTNAME ]; then
>
>>> HOSTNAME=3D"localhost"
>
>>> fi;
>
>>>
>
>>&= gt; if [ ! $USERNAME ]; then
> =
>>> USERNAME=3D"postgres= "
>
>>> fi;
&g= t;
>>>
>
>= ;>> ###########################
>
>>> #### START = THE BACKUPS ####
>
>>> ###########################
>
>>>
>
>>> FINAL_BACKUP_DIR=3D$BACKUP= _DIR"`date +\%Y-\%m-\%d`/"
>
>>>
>
>>= > echo "Making backup directory in $FINAL_BACKUP_DIR"
=
>
>&= gt;>
>
<= /div>
>>> if ! mkdir -p $FINAL_BACKUP_DIR; then
>
>>> echo "Cannot create backup directory in $FINAL_BACK= UP_DIR. Go
>
>>> and fix it!" 1>&2
>
= >>> exit 1;
>
>>> fi;
=
>
>>>= ;
>
<= div dir=3D"ltr">>>> #######################
>
>>&= gt; ### GLOBALS BACKUPS ###
> <= br clear=3D"none">
>>> #####################= ##
>
<= div dir=3D"ltr">>>>
>=
>>> echo -e "\n\nPerfor= ming globals backup"
>
>>> echo -e "-------------------= -------------------------\n"
> =
>>>
<= /div>
>
>&g= t;> if [ $ENABLE_GLOBALS_BACKUPS =3D "yes" ]
>
>>> t= hen
>
=
>>> echo "Globals backup"
>
>>&g= t;
>
=
>>> if ! pg_dumpall -g -h "$HOSTNAME" -U "$USERNA= ME" | gzip >
>
>>> $FINAL_BACKUP_DIR"globals".sql.gz= .in_progress; then
>
>>> echo "[!!ERROR!!] Failed to p= roduce globals backup"
>
>>> 1>&2
>
&= gt;>> else
>
>>> mv $FINAL_BACKUP_DIR"globals".sq= l.gz.in_progress
>
>>> $FINAL_BACKUP_DIR"globals".sql.g= z
>
>>> fi
>= ;
>>> else
>
= >>> echo "None"
>
>>> fi
>
>>= >
>
>>> ###########################
>
&g= t;>> ### SCHEMA-ONLY BACKUPS ###
>
>>> ##########= #################
>
>>>
>
>>> fo= r SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
>
>>> do
>
>>> SCHEMA_ONLY_CLAUSE=3D"$SCHEMA_ONLY_CLAUSE or datna= me ~
>
>>> '$SCHEMA_ONLY_DB'"
<= div dir=3D"ltr">>
>>>= done
>
>>>
&= gt;
>>> SCHEMA_ONLY_QUER= Y=3D"select datname from pg_database where false
>
>>> = $SCHEMA_ONLY_CLAUSE order by datname;"
>
>>>
>
>>> echo -e "\n\nPerforming schema-only backups"
>
>>> echo -e "--------------------------------------------\n"
>
>>>
>
>>> SCHEMA_ONLY_DB_LIST=3D`psq= l -h "$HOSTNAME" -U "$USERNAME" -At -c
>
>>> "$SCHEMA_O= NLY_QUERY" postgres`
>
>>>
>
>>> e= cho -e "The following databases were matched for schema-only
>
&= gt;>> backup:\n${SCHEMA_ONLY_DB_LIST}\n"
>
>>>
>
>>> for DATABASE in $SCHEMA_ONLY_DB_LIST
>
= >>> do
>
>>> echo "Schema-only backup of $DATA= BASE"
>
>>>
&= gt;
>>> if ! pg_dump -Fp= -s -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" |
>
>>> g= zip > $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress; then
>
>>> echo "[!!ERROR!!] Failed to backup database schema of=
>
>>> $DATABASE" 1>&2
<= div dir=3D"ltr">>
>>>= else
>
>>> mv $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.= gz.in_progress
>
>>> $FINAL_BACKUP_DIR"$DATABASE"_SCHEM= A.sql.gz
>
<= /div>
>>> fi
>
>>> done
>
>>>
>
>>> ##########################= #
>
>>> ###### FULL BACKUPS #######
<= /div>
>
>&g= t;> ###########################
>
>>>
>
= >>> for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
>
&g= t;>> do
>
>>> EXCLUDE_SCHEMA_ONLY_CLAUSE=3D"$EXCL= UDE_SCHEMA_ONLY_CLAUSE and
>
>>> datname !~ '$SCHEMA_ON= LY_DB'"
>
>>> done
>
>>>
>
>>> FULL_BACKUP_QUERY=3D"select datname from pg_database wher= e not
>
>>> datistemplate and datallowconn $EXCLUDE_SCH= EMA_ONLY_CLAUSE order by
>
>>> datname;"
>
&= gt;>>
>
>>> echo -e "\n\nPerforming full backups= "
>
>>> echo -e "--------------------------------------= ------\n"
>
=
>>>
>
>>> for DATABASE= in `psql -h "$HOSTNAME" -U "$USERNAME" -At -c
>
>>> "$= FULL_BACKUP_QUERY" postgres`
> =
>>> do
>
>= >> if [ $ENABLE_PLAIN_BACKUPS =3D "yes" ]
>
>>> t= hen
>
=
>>> echo "Plain backup of $DATABASE"
>
>>>
>
>>> if ! pg_dump -Fp -h "$HOSTNAME" -U= "$USERNAME" "$DATABASE" |
>
>>> gzip > $FINAL_BACKU= P_DIR"$DATABASE".sql.gz.in_progress; then
>
>>> echo "= [!!ERROR!!] Failed to produce plain backup
>
>>> databa= se $DATABASE" 1>&2
>
>>> else
=
>
>&= gt;> mv $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress
>
>= ;>> $FINAL_BACKUP_DIR"$DATABASE".sql.gz
>
>>> fi<= br clear=3D"none">
>
>>> fi
> =
>>>
<= /div>
>
>&g= t;> if [ $ENABLE_CUSTOM_BACKUPS =3D "yes" ]
>
>>> th= en
>
<= div dir=3D"ltr">>>> echo "Custom backup of $DATABASE"
>
>>>
>
>>> if ! pg_dump -Fc -h "$HOSTNAME" -U= "$USERNAME" "$DATABASE"
>
>>> -f $FINAL_BACKUP_DIR"$DA= TABASE".custom.in_progress; then
&= gt;
>>> echo "[!!ERROR!!= ] Failed to produce custom backup
= >
>>> database $DATAB= ASE" 1>&2
>
>>> else
>
>>> m= v $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress
<= div dir=3D"ltr">>
>>>= $FINAL_BACKUP_DIR"$DATABASE".custom
>
>>> fi
>
>>> fi
>
>>>
>
>>> d= one
>
=
>>>
>= ;
>>> echo -e "\nAll dat= abase backups complete!"
>
>>>
>
>>&g= t; I'm appreciate if anyone know how to correct this?
>
>>= >
>
>>> 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
>
<= /div>
>>> more.
>
>>> To cal= l him a dog hardly seems to do him justice though in as much
>
&= gt;>> as he had four legs, a tail, and barked, I admit he was, to all=
>
>>> outward appearances. But to those who knew him w= ell, he was a
>
>>> perfect gentleman (Hermione Gingold= )
>
>>> **Live simply **Love generously **Care deeply *= *Speak kindly.
>
>>> *** Genuinely rich *** Faithful ta= lent *** Sharing success
>
>>
>
>>
>
>>
>
>> Links:
>
>> ----= --
>
<= div dir=3D"ltr">>> [1] http://www.shreeyansh.com/


=






<= /html> ------=_Part_4991118_361825777.1551363993161--