Re: backup script error with could not connect to database
Pepe TD Vo <[email protected]> Thu, 19 Dec 2019 16:44:53 +0000 (UTC)
| Newsgroups | gmane.comp.db.postgresql.pgadmin,gmane.comp.db.postgresql.admin |
|---|---|
| Message-ID | <[email protected]> |
------=_Part_892188_561617255.1576773893892
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
I made it work.=C2=A0 I changed all in pg_hba.conf to trust and backup scri=
pts run fine.
thank you so much.
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 Thursday, December 19, 2019, 07:12:54 AM EST, bw <[email protected]>=
wrote: =20
=20
Did you try
root su -c postgres pg_dumpall > postgresdump.sql
On Wed, 18 Dec 2019 at 19:55, Pepe TD Vo <[email protected]> wrote:
>
> hello expert,
>
> I had to do a backup on replication master-slave servers.=C2=A0 I also ha=
ve asked this question before, but=C2=A0 I don't understand why I followed =
the information and same scripting to run on the replication servers and di=
dn't work.=C2=A0 I can run pg_dumpall successful but not with the script.=
=C2=A0 Would you please tell me what's wrong and what need to correct the e=
rror?
>
> my replication server (master) pg_hba.conf
>
> # "local" is for Unix domain socket connections only
> local=C2=A0 all=C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 peer
> host=C2=A0 all=C2=A0 =C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 192.168.2.0/24=C2=A0 =C2=A0 =C2=A0 =C2=A0 trust
> # IPv4 local connections:
> host=C2=A0 all=C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 127.0.0.1/32=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
indent
> local=C2=A0 all=C2=A0 postgres=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 trust
> host=C2=A0 all=C2=A0 power_user=C2=A0 =C2=A0 =C2=A0 =C2=A0 0.0.0.0/0=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 md5
> host=C2=A0 all=C2=A0 other_user=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 0.0.0.0=
/0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 md5
> host=C2=A0 all=C2=A0 storageLoader=C2=A0 =C2=A0 0.0.0.0/0=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 md5
> # IPv6 local connections:
> host=C2=A0 all=C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ::1/128=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 indent
> #allow replication connections from localhost, by a user with the replica=
tion priviledge
> local=C2=A0 replication=C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 trust
> host=C2=A0 replication=C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 127.0.0.1/32=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 trust
> host=C2=A0 replicaiton=C2=A0 all=C2=A0 =C2=A0 =C2=A0 =C2=A0 ::1/128=C2=A0=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 trust
> host=C2=A0 replication=C2=A0 replicauser=C2=A0 =C2=A0 masterIP/32=C2=A0 t=
rust
> host=C2=A0 replicaiton=C2=A0 replicauser=C2=A0 =C2=A0 slaveIP/32=C2=A0 =
=C2=A0 trust
>
> from the postgres prompt I can connect to each database
>
> #psql -d template0 -U postgres
> template0-#
> #psql -d template1 -U postgres
> template1-#
>
> #psql -d pscidr -U postgres
> pscidr-#
> #psql -d postgres -U postgres
> postgres-#
>
>
> when I run the script as postgres os, I don't see the error but the only =
global.sql backup but nothing in it, 0 byte (blank) sql script.
>
> when I run as root su -c postgres /path/pg_backup.sh > pgbackup.log, I ge=
t below:
>
> Performing globals backup:
> Globals backup
> pg_dumpall: could not connect to database "template1":FATAL: Ident authen=
tication failed for user "postgres" fro globals backup.
>
> Performing schema-only backup:
> psql:FATAL: Ident authentication failed for user "postgres"
> the following databases were matched for schema-only backup:
>
> performing full backup:
> psql:FATAL: Ident authentication failed for user "postgres"
>
>
> I can run pg_dumpall > postgresdump.sql as postgres user os fine will all=
the databases backup
>
> pg_backup.sh and pg_backup.config attachment.
>
> v/r,
>
> Bach-Nga
>
> No one in this world is pure and perfect.=C2=A0 If you avoid people for t=
heir 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 appearanc=
es. But to those who knew him well, he was a perfect gentleman (Hermione Gi=
ngold)
>
> **Live simply **Love generously **Care deeply **Speak kindly.
> *** Genuinely rich *** Faithful talent *** Sharing success
=20
------=_Part_892188_561617255.1576773893892
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
<html><head></head><body><div class=3D"ydp69f78c4ayahoo-style-wrap" style=
=3D"font-family:verdana, helvetica, sans-serif;font-size:16px;"><div><div d=
ir=3D"ltr" data-setdir=3D"false"><span><span style=3D"color: rgb(0, 0, 0); =
font-family: verdana, helvetica, sans-serif; font-size: 16px;">I made it wo=
rk. I changed all in pg_hba.conf to trust and backup scripts run fine=
.</span></span><br></div><div dir=3D"ltr" data-setdir=3D"false"><span><span=
style=3D"color: rgb(0, 0, 0); font-family: verdana, helvetica, sans-serif;=
font-size: 16px;"><br></span></span></div><div dir=3D"ltr" data-setdir=3D"=
false"><span><span style=3D"color: rgb(0, 0, 0); font-family: verdana, helv=
etica, sans-serif; font-size: 16px;">thank you so much.</span></span></div>=
<div><br></div><div class=3D"ydp69f78c4asignature"><div id=3D"ydp69f78c4ayu=
i_3_13_0_rc_1_1_1381789421038_7868" class=3D"ydp69f78c4ayiv3811552299ms__id=
13889"><font color=3D"#0000ff"><b><span class=3D"ydp69f78c4ayiv3811552299sg=
"><font color=3D"#8000ff" style=3D"background-color: inherit;"></font></spa=
n></b></font><div id=3D"ydp69f78c4ayui_3_13_0_rc_1_1_1381789421038_7874" cl=
ass=3D"ydp69f78c4ayiv3811552299ms__id13893"><font id=3D"ydp69f78c4ayui_3_13=
_0_rc_1_1_1381789421038_7873" color=3D"#0000ff"><font color=3D"#8000ff"></f=
ont><span id=3D"ydp69f78c4ayui_3_13_0_rc_1_1_1381789421038_7871" class=3D"y=
dp69f78c4ayiv3811552299sg"><font id=3D"ydp69f78c4ayui_3_13_0_rc_1_1_1381789=
421038_7870" color=3D"#8000ff" style=3D"background-color: inherit;"><b id=
=3D"ydp69f78c4ayui_3_13_0_rc_1_16_1381789421038_18">Bach-Nga<br clear=3D"no=
ne"><br clear=3D"none"></b>No one in this world is pure and perfect. =
If you avoid people for their mistakes you will be alone. So judge less, lo=
ve and forgive more.</font></span></font></div><div id=3D"ydp69f78c4ayui_3_=
13_0_rc_1_1_1381789421038_7874" class=3D"ydp69f78c4ayiv3811552299ms__id1389=
3"><font color=3D"#0000ff"><span class=3D"ydp69f78c4ayiv3811552299sg"><font=
color=3D"#8000ff" style=3D"background-color: inherit;">To call him a dog h=
ardly seems to do him justice though in as much as he had four legs, a tail=
, and barked, I admit he was, to all outward appearances. But to those who =
knew him well, he was a perfect gentleman (Hermione Gingold)</font></span><=
/font></div><br clear=3D"none"><span style=3D"color:rgb(128, 0, 255);" id=
=3D"ydp69f78c4ayui_3_13_0_rc_1_1_1381789421038_7867">**Live simply **Love g=
enerously **Care deeply **Speak kindly.</span></div><div id=3D"ydp69f78c4ay=
ui_3_13_0_rc_1_1_1381789421038_7866" class=3D"ydp69f78c4ayiv3811552299ms__i=
d13894"><span id=3D"ydp69f78c4ayui_3_13_0_rc_1_1_1381789421038_7865" class=
=3D"ydp69f78c4ayiv3811552299sg"><font id=3D"ydp69f78c4ayui_3_13_0_rc_1_1_13=
81789421038_7864" color=3D"#8000ff" style=3D"background-color: inherit;">**=
* Genuinely rich *** Faithful talent *** Sharing success</font></span></div=
></div></div>
<div><br></div><div><br></div>
=20
</div><div id=3D"yahoo_quoted_7210724997" class=3D"yahoo_quoted">
<div style=3D"font-family:'Helvetica Neue', Helvetica, Arial, s=
ans-serif;font-size:13px;color:#26282a;">
=20
<div>
On Thursday, December 19, 2019, 07:12:54 AM EST, bw <=
;[email protected]> wrote:
</div>
<div><br></div>
<div><br></div>
<div><div dir=3D"ltr">Did you try<br clear=3D"none"><br cle=
ar=3D"none">root su -c postgres pg_dumpall > postgresdump.sql<br clear=
=3D"none"><div class=3D"yqt5894315542" id=3D"yqtfd56431"><br clear=3D"none"=
>On Wed, 18 Dec 2019 at 19:55, Pepe TD Vo <<a shape=3D"rect" ymailto=3D"=
mailto:[email protected]" href=3D"mailto:[email protected]">[email protected]<=
/a>> wrote:<br clear=3D"none">><br clear=3D"none">> hello expert,<=
br clear=3D"none">><br clear=3D"none">> I had to do a backup on repli=
cation master-slave servers. I also have asked this question before, =
but I don't understand why I followed the information and same script=
ing to run on the replication servers and didn't work. I can run pg_d=
umpall successful but not with the script. Would you please tell me w=
hat's wrong and what need to correct the error?<br clear=3D"none">><br c=
lear=3D"none">> my replication server (master) pg_hba.conf<br clear=3D"n=
one">><br clear=3D"none">> # "local" is for Unix domain socket connec=
tions only<br clear=3D"none">> local all all =
 =
; &nb=
sp; peer<br clear=3D"none">> host all =
all =
192.168.2.0/24 trust<br clear=3D"none">> # IP=
v4 local connections:<br clear=3D"none">> host all all&nbs=
p; 1=
27.0.0.1/32 indent<br clear=3D"none">>=
; local all postgres =
 =
; trust<br clear=3D"none">> host =
; all power_user 0.0.0.0/0 =
md5<br clear=3D"none">> host =
all other_user 0.0.0.0/0 &n=
bsp; md5<br clear=3D"none">> h=
ost all storageLoader 0.0.0.0/0 &nb=
sp; md5<br clear=3D"none">> # IPv6 lo=
cal connections:<br clear=3D"none">> host all all &n=
bsp; =
::1/128 inden=
t<br clear=3D"none">> #allow replication connections from localhost, by =
a user with the replication priviledge<br clear=3D"none">> local =
replication all &nbs=
p; &n=
bsp; trust<br clear=3D"none">> host replication all=
127.0.0.1/32 =
trust<br clear=3D"none">> host replicaiton all &nb=
sp; ::1/128 =
trust<br clear=3D"none">> host replication&n=
bsp; replicauser masterIP/32 trust<br clear=3D"none">&=
gt; host replicaiton replicauser slaveIP/32&nbs=
p; trust<br clear=3D"none">><br clear=3D"none">> from the pos=
tgres prompt I can connect to each database<br clear=3D"none">><br clear=
=3D"none">> #psql -d template0 -U postgres<br clear=3D"none">> templa=
te0-#<br clear=3D"none">> #psql -d template1 -U postgres<br clear=3D"non=
e">> template1-#<br clear=3D"none">><br clear=3D"none">> #psql -d =
pscidr -U postgres<br clear=3D"none">> pscidr-#<br clear=3D"none">> #=
psql -d postgres -U postgres<br clear=3D"none">> postgres-#<br clear=3D"=
none">><br clear=3D"none">><br clear=3D"none">> when I run the scr=
ipt as postgres os, I don't see the error but the only global.sql backup bu=
t nothing in it, 0 byte (blank) sql script.<br clear=3D"none">><br clear=
=3D"none">> when I run as root su -c postgres /path/pg_backup.sh > pg=
backup.log, I get below:<br clear=3D"none">><br clear=3D"none">> Perf=
orming globals backup:<br clear=3D"none">> Globals backup<br clear=3D"no=
ne">> pg_dumpall: could not connect to database "template1":FATAL: Ident=
authentication failed for user "postgres" fro globals backup.<br clear=3D"=
none">><br clear=3D"none">> Performing schema-only backup:<br clear=
=3D"none">> psql:FATAL: Ident authentication failed for user "postgres"<=
br clear=3D"none">> the following databases were matched for schema-only=
backup:<br clear=3D"none">><br clear=3D"none">> performing full back=
up:<br clear=3D"none">> psql:FATAL: Ident authentication failed for user=
"postgres"<br clear=3D"none">><br clear=3D"none">><br clear=3D"none"=
>> I can run pg_dumpall > postgresdump.sql as postgres user os fine w=
ill all the databases backup<br clear=3D"none">><br clear=3D"none">> =
pg_backup.sh and pg_backup.config attachment.<br clear=3D"none">><br cle=
ar=3D"none">> v/r,<br clear=3D"none">><br clear=3D"none">> Bach-Ng=
a<br clear=3D"none">><br clear=3D"none">> No one in this world is pur=
e and perfect. If you avoid people for their mistakes you will be alo=
ne. So judge less, love and forgive more.<br clear=3D"none">> To call hi=
m a dog hardly seems to do him justice though in as much as he had four leg=
s, a tail, and barked, I admit he was, to all outward appearances. But to t=
hose who knew him well, he was a perfect gentleman (Hermione Gingold)<br cl=
ear=3D"none">><br clear=3D"none">> **Live simply **Love generously **=
Care deeply **Speak kindly.<br clear=3D"none">> *** Genuinely rich *** F=
aithful talent *** Sharing success<br clear=3D"none"><br clear=3D"none"></d=
iv></div></div>
</div>
</div></body></html>
------=_Part_892188_561617255.1576773893892--