Re: backup script error with could not connect to database

[mallikarjun t <mallit333@xxxxxxxxx>]

Dear team, 

Do onething ident authentication problem to overcome on create .pgpass file and what username and password, port number and dbname put it on .pgpass file. Now you never ever getting this error.

On Thu, Dec 19, 2019, 12:25 AM Pepe TD Vo <pepevo@xxxxxxxxx> wrote:

hello expert,

I had to do a backup on replication master-slave servers.  I also have asked this question before, but  I don't understand why I followed the information and same scripting to run on the replication servers and didn't work.  I can run pg_dumpall successful but not with the script.  Would you please tell me what's wrong and what need to correct the error?

my replication server (master) pg_hba.conf

# "local" is for Unix domain socket connections only

local   all   all                                                     peer

host   all    all                     192.168.2.0/24        trust

# IPv4 local connections:

host   all   all                       127.0.0.1/32           indent

local  all   postgres                                            trust

host   all   power_user         0.0.0.0/0               md5

host   all   other_user          0.0.0.0/0                md5

host   all   storageLoader    0.0.0.0/0                md5

# IPv6 local connections:

host   all   all                        ::1/128                  indent

#allow replication connections from localhost, by a user with the replication priviledge

local   replication   all                                        trust

host   replication   all         127.0.0.1/32           trust

host   replicaiton   all         ::1/128                    trust

host   replication   replicauser     masterIP/32  trust

host   replicaiton   replicauser     slaveIP/32     trust

from the postgres prompt I can connect to each database

#psql -d template0 -U postgres

template0-#

#psql -d template1 -U postgres

template1-#

#psql -d pscidr -U postgres

pscidr-#

#psql -d postgres -U postgres

postgres-#

when I run the script as postgres os, I don't see the error but the only global.sql backup but nothing in it, 0 byte (blank) sql script.

when I run as root su -c postgres /path/pg_backup.sh > pgbackup.log, I get below:

Performing globals backup:

Globals backup

pg_dumpall: could not connect to database "template1":FATAL: Ident authentication failed for user "postgres" fro globals backup.  

Performing schema-only backup:

psql:FATAL: Ident authentication failed for user "postgres"

the following databases were matched for schema-only backup:

performing full backup:

psql:FATAL: Ident authentication failed for user "postgres"

I can run pg_dumpall > postgresdump.sql as postgres user os fine will all the databases backup

pg_backup.sh and pg_backup.config attachment.

v/r,

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.

To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.

*** Genuinely rich *** Faithful talent *** Sharing success