Re: [MASSMAIL]Re: pg_dumpall: could not connect to database: FATAL - Mailing list pgsql-admin
| From | gilberto.castillo@etecsa.cu |
|---|---|
| Subject | Re: [MASSMAIL]Re: pg_dumpall: could not connect to database: FATAL |
| Date | |
| Msg-id | 6aab1ed6f24b47617beb99e2e559c973@etecsa.cu Whole thread Raw |
| In response to | Re: [MASSMAIL]Re: pg_dumpall: could not connect to database: FATAL (Pepe TD Vo <pepevo@yahoo.com>) |
| Responses |
Re: [MASSMAIL]Re: pg_dumpall: could not connect to database: FATAL
|
| List | pgsql-admin |
Hi You look your system Path but choose pg_hba Remember: -----------Begin------------ 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 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" 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 -su: /tmp/postresp.sql: Permiso denegado 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:~$ postgres@gilbertoc:~$ ls /tmp/salvapostgres.backup /tmp/salvapostgres.backup -----End------------------------- El 2019-02-26 10:36, Pepe TD Vo escribió: > 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 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 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 trust > > host all power_user 0.0.0.0/0 md5 > host all other_user 0.0.0.0/0 md5 > host all storageLoader 0.0.0.0/0 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 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/
pgsql-admin by date: