Обсуждение: pg_dumpall: could not connect to database: FATAL

Поиск

Список

Период

Сортировка

pg_dumpall: could not connect to database: FATAL

От

Pepe TD Vo

Дата:

25 февраля 2019 г., 19:50:30

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" ]; thenecho "This script must be run as $BACKUP_USER. Exiting." 1>&2exit 1;
fi;
 
 
###########################
### INITIALISE DEFAULTS ###
###########################
 
if [ ! $HOSTNAME ]; thenHOSTNAME="localhost"
fi;
 
if [ ! $USERNAME ]; thenUSERNAME="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; thenecho "Cannot create backup directory in $FINAL_BACKUP_DIR. Go and fix it!" 1>&2exit 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
elseecho "None"
fi
 
 
###########################
### SCHEMA-ONLY BACKUPS ###
###########################
 
for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
doSCHEMA_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
doecho "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>&2else	mv $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gzfi
done
 
 
###########################
###### FULL BACKUPS #######
###########################
 
for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
doEXCLUDE_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`
doif [ $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	fifi
 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	fifi
 
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

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

От

gilberto.castillo@etecsa.cu

Дата:

25 февраля 2019 г., 19:54:39

Hi Pepe,

Is there a user postgres in your database?

Do you changer backup user?


El 2019-02-25 11:50, Pepe TD Vo escribió:
> 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

Re: pg_dumpall: could not connect to database: FATAL

От

Shreeyansh Dba

Дата:

25 февраля 2019 г., 20:28:23

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

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

#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" ]; thenecho "This script must be run as $BACKUP_USER. Exiting." 1>&2exit 1;
fi;
 
 
###########################
### INITIALISE DEFAULTS ###
###########################
 
if [ ! $HOSTNAME ]; thenHOSTNAME="localhost"
fi;
 
if [ ! $USERNAME ]; thenUSERNAME="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; thenecho "Cannot create backup directory in $FINAL_BACKUP_DIR. Go and fix it!" 1>&2exit 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
elseecho "None"
fi
 
 
###########################
### SCHEMA-ONLY BACKUPS ###
###########################
 
for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
doSCHEMA_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
doecho "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>&2else	mv $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gzfi
done
 
 
###########################
###### FULL BACKUPS #######
###########################
 
for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
doEXCLUDE_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`
doif [ $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	fifi
 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	fifi
 
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.

**Live simply **Love generously **Care deeply **Speak kindly.

*** Genuinely rich *** Faithful talent *** Sharing success

Re: pg_dumpall: could not connect to database: FATAL

От

Pepe TD Vo

Дата:

25 февраля 2019 г., 21:48:23

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

#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.

**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.

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

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

#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" ]; thenecho "This script must be run as $BACKUP_USER. Exiting." 1>&2exit 1;
fi;
 
 
###########################
### INITIALISE DEFAULTS ###
###########################
 
if [ ! $HOSTNAME ]; thenHOSTNAME="localhost"
fi;
 
if [ ! $USERNAME ]; thenUSERNAME="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; thenecho "Cannot create backup directory in $FINAL_BACKUP_DIR. Go and fix it!" 1>&2exit 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
elseecho "None"
fi
 
 
###########################
### SCHEMA-ONLY BACKUPS ###
###########################
 
for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
doSCHEMA_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
doecho "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>&2else	mv $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gz.in_progress $FINAL_BACKUP_DIR"$DATABASE"_SCHEMA.sql.gzfi
done
 
 
###########################
###### FULL BACKUPS #######
###########################
 
for SCHEMA_ONLY_DB in ${SCHEMA_ONLY_LIST//,/ }
doEXCLUDE_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`
doif [ $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	fifi
 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	fifi
 
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.

**Live simply **Love generously **Care deeply **Speak kindly.

*** Genuinely rich *** Faithful talent *** Sharing success

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

От

gilberto.castillo@etecsa.cu

Дата:

25 февраля 2019 г., 21:58:06

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/

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

От

Pepe TD Vo

Дата:

26 февраля 2019 г., 17:11:32

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.

**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"

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

>>>

>>> 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

>>>

>>> 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/

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

От

Shreeyansh Dba

Дата:

26 февраля 2019 г., 17:52:06

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@yahoo.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.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/

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

От

gilberto.castillo@etecsa.cu

Дата:

26 февраля 2019 г., 18:19:53

Hello Pepe,

Please, Show we, Your pg_hba.


El 2019-02-26 09:52, Shreeyansh Dba escribió:
> 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 [2]
> 
> On Tue, Feb 26, 2019 at 7:41 PM Pepe TD Vo <pepevo@yahoo.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.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 [1] 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 [2] [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/
> 
> 
> Links:
> ------
> [1] http://127.0.0.1/32
> [2] http://www.shreeyansh.com

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: pg_dumpall: could not connect to database: FATAL