Re: [MASSMAIL]Re: pg_dumpall: could not connect to database: FATAL
От | gilberto.castillo@etecsa.cu |
---|---|
Тема | Re: [MASSMAIL]Re: pg_dumpall: could not connect to database: FATAL |
Дата | |
Msg-id | eac048d777c1d0b387376fbeb02c417d@etecsa.cu обсуждение исходный текст |
Ответ на | Re: pg_dumpall: could not connect to database: FATAL (Pepe TD Vo <pepevo@yahoo.com>) |
Список | pgsql-admin |
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/
В списке pgsql-admin по дате отправления: