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 1gyemQ-0004L0-W5 for pgsql-admin@arkaria.postgresql.org; Tue, 26 Feb 2019 15:36:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gyemP-0000ir-K5 for pgsql-admin@arkaria.postgresql.org; Tue, 26 Feb 2019 15:36:25 +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 1gyemO-0000ic-QF for pgsql-admin@lists.postgresql.org; Tue, 26 Feb 2019 15:36:25 +0000 Received: from sonic307-3.consmr.mail.bf2.yahoo.com ([74.6.134.42]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gyemD-0007el-Dj for pgsql-admin@lists.postgresql.org; Tue, 26 Feb 2019 15:36:23 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1551195371; bh=0Lkj0UzGEuGWlHVbu35W7t/1obefSr6rQM7/Cb1Z2+E=; h=Date:From:Reply-To:To:Cc:Subject:References:From:Subject; b=GbSFt8h6nS6l6aH2JQmWq+52O48GQSrYi39hZRvUrcaUf4yOfzLaspvNPkD9OQOHH6f9FVUFS6L2pPH/Wa03+hsoyxJDwGi/AvcOJHBW01URmyCzRmS7BLW2hBokjOHFpa884NjQCwyXyCwurgdKMs/iV/d1zqOEvo9rMy6Xdnrh/E0+a2PHEQM7TOt9PoqJg2LP7c/DlEpbs9Ir6hxJH58VmUuFS2If6erRrg/aFPMk2JCamy2gIEks0xWHMZnsunwysW0Vsd8ApaRibLqPApqMLQksbPVM+8CVSuW7CGlcTYIISqTmwS9yhWQ5uHxcdjBng6dXToxnSGd19x9sKg== X-YMail-OSG: e0KVVtAVM1kBudOQiSUgFQWSfzD9CciwVkv_q9lQUlLPW1xwzLtCOl8yrAfXk6q ua75OLdVJb1MW5nhVwZQKyQ7Zk5.YM6kLIwBbOfth_KBRCzTTVt0Je.PJdAqWSf9eQdNte5Q6GrQ .sdGloUxL8TV3EUDXPlEq2C_IlHAWgjJRMWEffa1X5YHfWlmMhYuKVSRAbUpLGgdj4gkJfwu0gu0 AuV2BZUB4tf_ZLU_CtXbZvofJ7keANV_R4tpkVX_jUJAjg9OrB4VyxH.gGtf9RxWhICXkdYzyZPS X_0wvbriSWDLBB9rk0I7Vmecj6dYgxM_k1DLfc5rAA0jSYUhocakQdpYuwWNycuhdTzSEB5Rw0At HMoaeH6BdzylTozq.IUUwetRFwjckpH_09QcO3CGyCU56vaLuw112itZOiQT1pMrs3ZnnXQyuN05 MaHQBVGvexFYXs8ByMK5RZbx9On0aHhyvGtzuCJ8k8l8NSNvvgvluWo3BsLCxCPfU_Bspy5qzoKJ WMjM_NCU2xlxXuHqrvb6bKdJxRs8_kmnXWGv4.A.dxvpUgJPLePs3r2mSUXl_kzA2Mh7N6wmE7Vu mOJCeYUScBDU_teEfJ0zmYugP08LfzcFhQHVE4ZWzqyztkiwx88UrKYG9VB1pkIyoRU4kYsu6Gyi vV4W.k3kKc2soR3uKnUfwNgPJM2isUzmkDDS1Pm6bmSR3MeegVYETo6X5wDFZ_8RotBYf5Z8TBys 06AAdr1ROp7d98Bob18MGRqkM8.75fpJZtg_jasW0CirydMduUMK5qjvBMLUqiuCNcxexJDw3tUo 13Xa8NDziMb2vahTlQzWrvM5RMUWP3jzAYif7nUURuDkJ_XDCujvxgBznUCKpmga.zKVw_KEnKQr Ta9uIHLusidlJ7GRZw.qkMVITANltpN1RydOK2X0HhruV2U2H3vOqhL_oCKFVzD9uuEdO86gHA4g v_bkTqzGh4IbEttQWe1j.j5SHqqwxQneqJIDCcCei.0UMMACjEKqhnPWuoTy7xBI9K8ULtt9YtrA fnzrrbD3YcYN6Hhk9YWUq36MGhWG0fPYuAK8p8y0uWicHPEMpy.MTyDEdiNra0T0- Received: from sonic.gate.mail.ne1.yahoo.com by sonic307.consmr.mail.bf2.yahoo.com with HTTP; Tue, 26 Feb 2019 15:36:11 +0000 Date: Tue, 26 Feb 2019 15:36:08 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: Shreeyansh Dba Cc: "gilberto.castillo@etecsa.cu" , Pgsql-admin Message-ID: <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_5546565_142733167.1551195368812" References: <1766891885.5546566.1551195368839.ref@mail.yahoo.com> 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: 104991 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_5546565_142733167.1551195368812 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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 ------=_Part_5546565_142733167.1551195368812 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
yes mam'm/sir.   I already posted it<= /span>
=
here it is again.

<= /div>
#vi pg= _hba.conf  (from /usr/local/pgsql/data/pg_hba.conf) 

#TYPE  DATAB= ASE     USER     ADDRESS     M= ETHOD
# "local" is for Unix domain socket connectiosn only

local     all            &= nbsp;    all              &nbs= p;              trust

# IP= v4 local connections:

host   = ;  all                 al= l         127.0.0.1/32 trust

<= /span>
# IPv6 local connections:

host     all&nb= sp;                all   =      ::1/128         trust<= /div>

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


I also found 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 script is not working ev= en 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     USER     ADDRES= S     METHOD
# "local" is for Unix domain socket connectiosn= only
<= br id=3D"yui_3_16_0_ym19_1_1551114507366_257719">
local     all       = ;          all          &= nbsp;                  trust
<= br id=3D"yui_3_16_0_ym19_1_1551114507366_257723">
# IPv4 local connections:

host     all        &nbs= p;        all         127.0.0.= 1/32 trust
host      all&nb= sp;           power_user 0.0.0.0/0    &n= bsp;md5
host      all            othe= r_user 0.0.0.0/0      md5
host      all   =         storageLoader 0.0.0.0/0 md5

# IPv6 local connections:

host     al= l                 all  &n= bsp;      ::1/128         trust
# allow all replica= tion connections from localhost, by a user with the replication privilege
local&nb= sp;   replication      all        &= nbsp;                    = trust
= host    replication      all      &= nbsp;   127.0.0.1/32   rust
host    replication  &nb= sp;   all          ::1/128     = ;    trust


why do I have 3 pg= _hba.conf?  which one should use?

thank you for look= ing into it.

v/r,
 
<= div class=3D"yiv0143508912ms__id13889" id=3D"yiv0143508912yui_3_13_0_rc_1_1= _1381789421038_7868">
Bach-Nga

No one in this world is pure and perfect.  If you avo= id people for their mistakes you will be alone. So judge less, love and for= give more.
To call him a dog hardl= y seems to do him justice though in as much as he had four legs, a tail, an= d 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 gener= ously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


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


<= div id=3D"yiv0143508912">
The error message says the authentication m= ethod used isn't trust. Can you please share your pg_hba.conf file entries.=

T= hanks & Regards,
Shreeyansh DBA Team
www.shr Hi Pepe eeyansh.com<= /font>


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

as root:

# export HOSTNAME=3D"localhost"
# export USERNAME=3D"postgr= es"
# e= xport DATABASE=3D"postgres"
# pg_dump -Fc -h "$HOSTNAME" -U "$USERNAM= E" "$DATABASE" > /tmp/postresp.sql

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

# export HOSTNA= ME=3D"127.0.0.1"
#= export USERNAME=3D"postgres"
# export DATABASE=3D"postgres"
# pg_dump -Fc -h "$HOSTNAME" -U "$USER= NAME" "$DATABASE" > /tmp/postresp.sql

pg_dump:[archiver (db)] con= nection to database "DATABASE" failed: FATAL: Ident authentication failed f= or user "postgres"


as = same as postgres:

#su - postgres

<= font id=3D"yiv0143508912gmail-m_6327888191913896433yui_3_16_0_ym19_1_155111= 4507366_173557" 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 failed 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 "DATABASE" failed: FATAL: Ident authenticatio= n failed for user "postgres"


as the postgres, I= can run pgdump dbname > output.sql
v/r,
 
Bach-Nga

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

**Live simply **Love gener= ously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Shar= ing success


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:
<= /div>
> I am running script in local host (same db serve= r)

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


Please, Check this:<= br clear=3D"none">

root@gilbertoc:/hom= e/gilbertoc# export HOSTNAME=3D"localhost";
USERNAME=3D"postgres"; DATABASE=3D"postgres"
<= /div>

root@gilbertoc:/home/gilbertoc# pg_dump= -Fc -h "$HOSTNAME" -U
"$USERNAME= " "$DATABASE" > /tmp/postresp.sql
Password:
pg_dump: [archiver (d= b)] connection to database "postgres" failed:
FATAL:  password authentication failed for user "postgres= "
FATAL:  password authentica= tion failed for user "postgres"

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_5546565_142733167.1551195368812--