The error message says the authentication method used isn't trust. Can you please share your pg_hba.conf file entries.
On Tue, Feb 26, 2019 at 7:41 PM Pepe TD Vo <pepevo@xxxxxxxxx> 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.sqlpg_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.sqlpg_dump:[archiver (db)] connection to database "DATABASE" failed: FATAL: Ident authentication failed for user "postgres"as same as postgres:#su - postgresexport HOSTNAME="localhost"# export USERNAME="postgres"# export DATABASE="postgres"# pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" > /tmp/postresp.sqlpg_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.sqlpg_dump:[archiver (db)] connection to database "DATABASE" failed: FATAL: Ident authentication failed for user "postgres"as the postgres, I can run pgdump dbname > output.sqlv/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 successOn Monday, February 25, 2019 2:39 PM, "gilberto.castillo@xxxxxxxxx" <gilberto.castillo@xxxxxxxxx> 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@gilbertoc:/home/gilbertoc# export HOSTNAME="localhost";USERNAME="postgres"; DATABASE="postgres"root@gilbertoc:/home/gilbertoc# pg_dump -Fc -h "$HOSTNAME" -U"$USERNAME" "$DATABASE" > /tmp/postresp.sqlPassword: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@gilbertoc:/home/gilbertoc# sudo su - postgrespostgres@gilbertoc:~$ export HOSTNAME="localhost"; USERNAME="postgres";DATABASE="postgres"postgres@gilbertoc:~$ pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME""$DATABASE" > /tmp/postresp.sql/tmp/postresp.sql: Permiso denegadoFine:postgres@gilbertoc:~$ export HOSTNAME="127.0.0.1"; USERNAME="postgres";DATABASE="postgres"postgres@gilbertoc:~$ pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME""$DATABASE" > /tmp/salvapostgres.backuppostgres@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.castillo@xxxxxxxxx"> <gilberto.castillo@xxxxxxxxx> 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@xxxxxxxxx> 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>>>>>>>>> Thanks & Regards,>>> SHREEYANSH DBA TEAM>>> www.shreeyansh.com [1]>>>>>> On Mon, Feb 25, 2019 at 10:21 PM Pepe TD Vo <pepevo@xxxxxxxxx>> 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/
