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

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/