Re: [MASSMAIL]Re: pg_dumpall: could not connect to database: FATAL

same error:
run as root:# export HOSTNAME=hostname# export USERNAME=postgres# export DATABASE=postgres # export PORT 5432# pg_dump -v -p $PORT -U $USERNAME -d $DATABASE -f /tmp/postgresbakup.sql
pg_dump:[archiver (db)]connection to database "DATABASE" failed: could not 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=hostname# export USERNAME=postgres# export DATABASE=postgres # export PORT 5432# pg_dump -v -p $PORT -U $USERNAME -d $DATABASE -f /tmp/postgresbakup.sql
pg_dump:[archiver (db)]connection to database "DATABASE" failed: FATAL: Ident authentication failed for user "postgres"

portal-p already on /etc/hosts.
v/r,

Bach-Nga

No one in this world is pure and perfect.  If you avoid 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

On Tuesday, February 26, 2019 11:01 AM, Shreeyansh Dba <shreeyansh2014(at)gmail(dot)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.

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 <pepevo(at)yahoo(dot)com> wrote:

yes mam'm/sir.   I already posted it
here it is again.
#vi pg_hba.conf  (from /usr/local/pgsql/data/pg_hba.conf) 
#TYPE  DATABASE     USER     ADDRESS     METHOD# "local" is for Unix domain socket connectiosn only
local     all                 all                             trust
# IPv4 local connections:
host     all                 all         127.0.0.1/32 trust

# IPv6 local connections:
host     all                 all         ::1/128         trust
# allow all replication connections from localhost, by a user with the replication privilegelocal    replication      all                              trusthost    replication      all          127.0.0.1/32  trusthost    replication      all          ::1/128         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 even run from the command prompt.
#vi pg_hba.conf ( (from /var/lib/pgsql/11/data/pg_hba.conf & /var/lib/pgsql/data/pg_hba.conf) 
#TYPE  DATABASE     USER     ADDRESS     METHOD# "local" is for Unix domain socket connectiosn only
local     all                 all                             trust
# IPv4 local connections:
host     all                 all         127.0.0.1/32 trust
host      all            power_user 0.0.0.0/0     md5host      all            other_user 0.0.0.0/0      md5host      all            storageLoader 0.0.0.0/0 md5

# IPv6 local connections:
host     all                 all         ::1/128         trust
# allow all replication connections from localhost, by a user with the replication privilegelocal    replication      all                              trusthost    replication      all          127.0.0.1/32   rusthost    replication      all          ::1/128         trust

why do I have 3 pg_hba.conf?  which one should use?
thank you for looking into it.
v/r, Bach-Nga

No one in this world is pure and perfect.  If you avoid people for 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

On Tuesday, February 26, 2019 9:52 AM, Shreeyansh Dba <shreeyansh2014(at)gmail(dot)com> wrote:

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

Thanks & Regards,
Shreeyansh DBA Team
www.shr Hi Pepe eeyansh.com

On Tue, Feb 26, 2019 at 7:41 PM Pepe TD Vo <pepevo(at)yahoo(dot)com> wrote:

No sir.   I have tried what your suggestion and still failed.
as root:
# export HOSTNAME="localhost"# export USERNAME="postgres"# export DATABASE="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="127.0.0.1"# export USERNAME="postgres"# export DATABASE="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"

as same as postgres:
#su - postgres
 export HOSTNAME="localhost"# export USERNAME="postgres"# export DATABASE="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="127.0.0.1"# export USERNAME="postgres"# export DATABASE="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"

as the postgres, I can run pgdump dbname > output.sql
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

On Monday, February 25, 2019 2:39 PM, "gilberto(dot)castillo(at)etecsa(dot)cu" <gilberto(dot)castillo(at)etecsa(dot)cu> wrote:

El 2019-02-25 14:16, Pepe TD Vo escribió:
> I am running script in local host (same db server)

El 2019-02-25 14:16, Pepe TD Vo escribió:
> I am running script in local host (same db server)

Please, Check this:

root(at)gilbertoc:/home/gilbertoc# export HOSTNAME="localhost";
USERNAME="postgres"; DATABASE="postgres"

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

Other:

root(at)gilbertoc:/home/gilbertoc# sudo su - postgres

postgres(at)gilbertoc:~$ export HOSTNAME="localhost"; USERNAME="postgres";
DATABASE="postgres"

postgres(at)gilbertoc:~$ pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME"
"$DATABASE" > /tmp/postresp.sql
  /tmp/postresp.sql: Permiso denegado

Fine:

postgres(at)gilbertoc:~$ export HOSTNAME="127.0.0.1"; USERNAME="postgres";
DATABASE="postgres"

postgres(at)gilbertoc:~$ pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME"
"$DATABASE" > /tmp/salvapostgres.backup
postgres(at)gilbertoc:~$ ls /tmp/salvapostgres.backup
/tmp/salvapostgres.backup

>
> 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
>
>  On Monday, February 25, 2019 1:58 PM, "gilberto(dot)castillo(at)etecsa(dot)cu"
> <gilberto(dot)castillo(at)etecsa(dot)cu> wrote:
>
> El 2019-02-25 13:48, Pepe TD Vo escribió:
>
>> 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 the password
>
>>
>
>> #psql -d template0 -U postgres
>
>> template0-#
>
>> #psql -d template1 -U postgres
>
>> template1-#
>
>>
>
>> #psql -d CIDR -U postgres
>
>> CIDR-#
>
>> #psql -d postgres -U postgres
>
>> postgres-#
>
>>
>
>> and a pg_hba.conf:
>
>>
>
>> "
>
>>
>
>> #TYPE  DATABASE    USER    ADDRESS    METHOD
>
>> local    all                all                            trust
>
>> host    all                all        127.0.0.1/32 trust
>
>> host    all                all        ::1/128        trust
>
> Look here the IP only connect to localhost. From where Are your
> running
>
> the script?
>
>> #allo replication connections from localhost, by a user with the
>
>> replication privilege
>
>> local    replication      all                              trust
>
>> host    replication      all          127..0.1/32  trust
>
>> host    replication      all          ::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 each database and the "show
> pg_hba.conf"
>
>> show nothing.
>
>>
>
>> postgres-# show pg_hba.conf
>
>> postgres-#
>
>>
>
>> 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
>
>>
>
>>  On Monday, February 25, 2019 12:28 PM, Shreeyansh Dba
>
>> <shreeyansh2014(at)gmail(dot)com> wrote:
>
>>
>
>> Hi Pepe,
>
>>
>
>> It seems your are using os user for taking the backup.
>
>>
>
>> When you run command line commands against the postgres database and
>
>> they ask for the user and password that is the database user
> postgres
>
>> and its associated password, so when you do something like: psql -d
>
>> template1 -U postgres the password you will 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 host, database, user and auth_method.
>
>>
>
>> For more information see
>
>>
> https://www.postgresql.org/docs/9.5/static/client-authentication.html
>
>>
>
>> Thanks & Regards,
>
>> SHREEYANSH DBA TEAM
>
>> www.shreeyansh.com [1]
>
>>
>
>> On Mon, Feb 25, 2019 at 10:21 PM Pepe TD Vo <pepevo(at)yahoo(dot)com>
> wrote:
>
>>
>
>>> good morning Expert DBAs,
>
>>>
>
>>> I created a script to do backup and I have faced the error of
>
>>>
>
>>>> Performing globals backup:
>
>>>> pg_dumpall: could not connect to database "template1":FATAL: Ident
>
>>>> authentication failed for user "postgres" fro globals backup.  As
>
>>>> same as performing schema-only backups and full 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 but
>
>>> couldn't run from script even the script declared
>
>>> BACKUP_USER=postgres
>
>>>
>
>>> If I take the syntax, -H "$HOSTNAME" -U "$USERNAME" out 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
>
>>> running as doesn't match this
>
>>> # the script terminates.  Leave blank to skip check.
>
>>> BACKUP_USER=
>
>>>
>
>>> # Optional hostname to adhere to pg_hba policies.  Will default to
>
>>> "localhost" if none specified.
>
>>> HOSTNAME="hostname"
>
>>>
>
>>> # Optional username to connect to database as.  Will default to
>
>>> "postgres" if none specified.
>
>>> USERNAME="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=/home/backups/database/postgresql/
>
>>>
>
>>> # List of strings to match against in database 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=""
>
>>>
>
>>> # Will produce a custom-format backup if set to "yes"
>
>>> ENABLE_CUSTOM_BACKUPS=yes
>
>>>
>
>>> # Will produce a gzipped plain-format backup if set to "yes"
>
>>> ENABLE_PLAIN_BACKUPS=yes
>
>>>
>
>>> # Will produce gzipped sql file containing the cluster globals,
> like
>
>>> users and passwords, if set to "yes"
>
>>> ENABLE_GLOBALS_BACKUPS=yes
>
>>>
>
>>> #### SETTINGS FOR ROTATED BACKUPS ####
>
>>>
>
>>> # Which day to take the weekly backup from (1-7 = Monday-Sunday)
>
>>> DAY_OF_WEEK_TO_KEEP=5
>
>>>
>
>>> # Number of days to keep daily backups
>
>>> DAYS_TO_KEEP=7
>
>>>
>
>>> # How many weeks to keep weekly backups
>
>>> WEEKS_TO_KEEP=5
>
>>>
>
>>> ######################################
>
>>>
>
>>> #VI PG_BACKUP.SH
>
>>>
>
>>> #!/bin/bash
>
>>>
>
>>> ###########################
>
>>> ####### LOAD CONFIG #######
>
>>> ###########################
>
>>>
>
>>> while [ $# -gt 0 ]; do
>
>>> case $1 in
>
>>> -c)
>
>>> if [ -r "$2" ]; then
>
>>> source "$2"
>
>>> shift 2
>
>>> else
>
>>> ${ECHO} "Unreadable config file
>
>>> \"$2\"" 1>&2
>
>>> exit 1
>
>>> fi
>
>>> ;;
>
>>> *)
>
>>> ${ECHO} "Unknown Option \"$1\"" 1>&2
>
>>> exit 2
>
>>> ;;
>
>>> esac
>
>>> done
>
>>>
>
>>> if [ $# = 0 ]; then
>
>>> SCRIPTPATH=$(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" != "" -a "$(id -un)" != "$BACKUP_USER" ]; then
>
>>> echo "This script must be run as $BACKUP_USER. Exiting." 1>&2
>
>>> exit 1;
>
>>> fi;
>
>>>
>
>>> ###########################
>
>>> ### INITIALISE DEFAULTS ###
>
>>> ###########################
>
>>>
>
>>> if [ ! $HOSTNAME ]; then
>
>>> HOSTNAME="localhost"
>
>>> fi;
>
>>>
>
>>> if [ ! $USERNAME ]; then
>
>>> USERNAME="postgres"
>
>>> fi;
>
>>>
>
>>> ###########################
>
>>> #### START THE BACKUPS ####
>
>>> ###########################
>
>>>
>
>>> FINAL_BACKUP_DIR=$BACKUP_DIR"`date +\%Y-\%m-\%d`/"
>
>>>
>
>>> echo "Making backup directory in $FINAL_BACKUP_DIR"
>
>>>
>
>>> if ! mkdir -p $FINAL_BACKUP_DIR; then
>
>>> echo "Cannot create backup directory in $FINAL_BACKUP_DIR. Go
>
>>> and fix it!" 1>&2
>
>>> exit 1;
>
>>> fi;
>
>>>
>
>>> #######################
>
>>> ### GLOBALS BACKUPS ###
>
>>> #######################
>
>>>
>
>>> echo -e "\n\nPerforming globals backup"
>
>>> echo -e "--------------------------------------------\n"
>
>>>
>
>>> if [ $ENABLE_GLOBALS_BACKUPS = "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
>
>>> fi
>
>>> else
>
>>> echo "None"
>
>>> fi
>
>>>
>
>>> ###########################
>
>>> ### SCHEMA-ONLY BACKUPS ###
>
>>> ###########################
>
>>>
>
>>> for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
>
>>> do
>
>>> SCHEMA_ONLY_CLAUSE="$SCHEMA_ONLY_CLAUSE or datname ~
>
>>> '$SCHEMA_ONLY_DB'"
>
>>> done
>
>>>
>
>>> SCHEMA_ONLY_QUERY="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=`psql -h "$HOSTNAME" -U "$USERNAME" -At -c
>
>>> "$SCHEMA_ONLY_QUERY" postgres`
>
>>>
>
>>> echo -e "The following databases were matched for schema-only
>
>>> backup:\n${SCHEMA_ONLY_DB_LIST}\n"
>
>>>
>
>>> for DATABASE in $SCHEMA_ONLY_DB_LIST
>
>>> do
>
>>> echo "Schema-only backup of $DATABASE"
>
>>>
>
>>> if ! pg_dump -Fp -s -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" |
>
>>> gzip > $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress; then
>
>>> echo "[!!ERROR!!] Failed to backup database schema of
>
>>> $DATABASE" 1>&2
>
>>> else
>
>>> mv $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress
>
>>> $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz
>
>>> fi
>
>>> done
>
>>>
>
>>> ###########################
>
>>> ###### FULL BACKUPS #######
>
>>> ###########################
>
>>>
>
>>> for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
>
>>> do
>
>>> EXCLUDE_SCHEMA_ONLY_CLAUSE="$EXCLUDE_SCHEMA_ONLY_CLAUSE and
>
>>> datname !~ '$SCHEMA_ONLY_DB'"
>
>>> done
>
>>>
>
>>> FULL_BACKUP_QUERY="select datname from pg_database where not
>
>>> datistemplate and datallowconn $EXCLUDE_SCHEMA_ONLY_CLAUSE order by
>
>>> datname;"
>
>>>
>
>>> 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 = "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 produce plain backup
>
>>> database $DATABASE" 1>&2
>
>>> else
>
>>> mv $FINAL_BACKUP_DIR"$DATABASE".sql.gz.in_progress
>
>>> $FINAL_BACKUP_DIR"$DATABASE".sql.gz
>
>>> fi
>
>>> fi
>
>>>
>
>>> if [ $ENABLE_CUSTOM_BACKUPS = "yes" ]
>
>>> then
>
>>> echo "Custom backup of $DATABASE"
>
>>>
>
>>> if ! pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE"
>
>>> -f $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress; then
>
>>> echo "[!!ERROR!!] Failed to produce custom backup
>
>>> database $DATABASE" 1>&2
>
>>> else
>
>>> mv $FINAL_BACKUP_DIR"$DATABASE".custom.in_progress
>
>>> $FINAL_BACKUP_DIR"$DATABASE".custom
>
>>> fi
>
>>> fi
>
>>>
>
>>> done
>
>>>
>
>>> echo -e "\nAll database backups complete!"
>
>>>
>
>>> 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
>
>>> 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
>
>>
>
>>
>
>>
>
>> Links:
>
>> ------
>
>> [1] http://www.shreeyansh.com/