Re: [MASSMAIL]Re: pg_dumpall: could not connect to database: FATAL
От | gilberto.castillo@etecsa.cu |
---|---|
Тема | Re: [MASSMAIL]Re: pg_dumpall: could not connect to database: FATAL |
Дата | |
Msg-id | 55ad4da270bebb46fd05c6d13b659663@etecsa.cu обсуждение исходный текст |
Ответ на | Re: [MASSMAIL]Re: pg_dumpall: could not connect to database: FATAL (Pepe TD Vo <pepevo@yahoo.com>) |
Список | pgsql-admin |
El 2019-02-26 12:46, Pepe TD Vo escribió: > same error: > > run as root: Pepe look my example, You don't run the script like root. > > # export HOSTNAME=hostname > # export USERNAME=postgres > # export DATABASE=postgres > # export PORT 5432 > # pg_dump -v -p $PORT -U $USERNAME -d $DATABASE -f > /tmp/postgresbakup.sql > > pg_dump:[archiver (db)]connection to database "DATABASE" failed: could > not connect to server: Connection refused > Is the server running on host "portal-p" (138.24.1.110) and acceptiong > TCP/IP connection on port 5432? > > run as postgres > # export HOSTNAME=hostname > # export USERNAME=postgres > # export DATABASE=postgres > # export PORT 5432 > # pg_dump -v -p $PORT -U $USERNAME -d $DATABASE -f > /tmp/postgresbakup.sql > > pg_dump:[archiver (db)]connection to database "DATABASE" failed: > FATAL: Ident authentication failed for user "postgres" > > portal-p already on /etc/hosts. > > 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 Tuesday, February 26, 2019 11:01 AM, Shreeyansh Dba > <shreeyansh2014@gmail.com> wrote: > > Ohh, It looks your pg_hba entries are correct, reset your database > user "postgres" password then try again or use -w option, use a single > hba.conf file. > > try: pg_dump -v -p $PORT -U $USERNAME -d $DATABASE -f > /tmp/postresp.sql > > Thanks & Regards, > SHREEYANSH DBA TEAM > www.shreeyansh.com [2] > > On Tue, Feb 26, 2019 at 9:06 PM Pepe TD Vo <pepevo@yahoo.com> wrote: > >> yes mam'm/sir. I already posted it >> >> here it is again. >> >> #vi pg_hba.conf (from /usr/local/pgsql/data/pg_hba.conf) >> >> #TYPE DATABASE USER ADDRESS METHOD >> # "local" is for Unix domain socket connectiosn only >> >> local all all trust >> >> # IPv4 local connections: >> >> host all all 127.0.0.1/32 [1] trust >> >> # IPv6 local connections: >> >> host all all ::1/128 trust >> >> # allow all replication connections from localhost, by a user with >> the replication privilege >> local replication all trust >> host replication all 127.0.0.1/32 [1] trust >> host replication all ::1/128 trust >> >> I also found pg_hba.conf on both folders >> /var/lib/pgsql/11/data/pg_hba.conf & >> /var/lib/pgsql/data/pg_hba.conf). I modified it as above and the >> script is not working even run from the command prompt. >> >> #vi pg_hba.conf ( (from /var/lib/pgsql/11/data/pg_hba.conf & >> /var/lib/pgsql/data/pg_hba.conf) >> >> #TYPE DATABASE USER ADDRESS METHOD >> # "local" is for Unix domain socket connectiosn only >> >> local all all trust >> >> # IPv4 local connections: >> >> host all all 127.0.0.1/32 [1] trust >> >> host all power_user 0.0.0.0/0 [3] md5 >> host all other_user 0.0.0.0/0 [3] md5 >> host all storageLoader 0.0.0.0/0 [3] md5 >> >> # IPv6 local connections: >> >> host all all ::1/128 trust >> >> # allow all replication connections from localhost, by a user with >> the replication privilege >> local replication all trust >> host replication all 127.0.0.1/32 [1] rust >> host replication all ::1/128 trust >> >> why do I have 3 pg_hba.conf? which one should use? >> >> thank you for looking into it. >> >> 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 Tuesday, February 26, 2019 9:52 AM, Shreeyansh Dba >> <shreeyansh2014@gmail.com> wrote: >> >> 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/ > [3] http://0.0.0.0/0
В списке pgsql-admin по дате отправления:
Предыдущее
От: Pepe TD VoДата:
Сообщение: Re: [MASSMAIL]Re: pg_dumpall: could not connect to database: FATAL
Следующее
От: Pepe TD VoДата:
Сообщение: Re: [MASSMAIL]Re: pg_dumpall: could not connect to database: FATAL