From: Pepe TD Vo <pepevo@yahoo.com>
To: gilberto.castillo@etecsa.cu <gilberto.castillo@etecsa.cu>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: [MASSMAIL]Re: pg_dumpall: could not connect to database: FATAL
Date: Tue, 26 Feb 2019 14:11:32 +0000 (UTC)
Message-ID: <22207276.5485668.1551190292642@mail.yahoo.com> (raw)
In-Reply-To: <0490872142b8c63b8fb4fae5e3d84de7@etecsa.cu>
References: <1323549085.4899214.1551113430643.ref@mail.yahoo.com>
<1323549085.4899214.1551113430643@mail.yahoo.com>
<CAGDYbUOX42FLtxr2qNKfdgqSLu-xcJmyMhBQ=nBBw_VSmo4Y0w@mail.gmail.com>
<1114844707.4989150.1551120503721@mail.yahoo.com>
<eac048d777c1d0b387376fbeb02c417d@etecsa.cu>
<318302488.5021763.1551122196711@mail.yahoo.com>
<0490872142b8c63b8fb4fae5e3d84de7@etecsa.cu>
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.castillo@etecsa.cu" <gilberto.castillo@etecsa.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@gilbertoc:/home/gilbertoc# export HOSTNAME="localhost";
USERNAME="postgres"; DATABASE="postgres"
root@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@gilbertoc:/home/gilbertoc# sudo su - postgres
postgres@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 denegado
Fine:
postgres@gilbertoc:~$ export HOSTNAME="127.0.0.1"; USERNAME="postgres";
DATABASE="postgres"
postgres@gilbertoc:~$ pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME"
"$DATABASE" > /tmp/salvapostgres.backup
postgres@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@etecsa.cu"
> <gilberto.castillo@etecsa.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@gmail.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@yahoo.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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: pepevo@yahoo.com, gilberto.castillo@etecsa.cu, pgsql-admin@lists.postgresql.org
Subject: Re: [MASSMAIL]Re: pg_dumpall: could not connect to database: FATAL
In-Reply-To: <22207276.5485668.1551190292642@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox