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

Hi

You look your system Path but choose pg_hba

Remember:

-----------Begin------------

root(at)gilbertoc:/home/gilbertoc# export HOSTNAME="localhost";
USERNAME="postgres"; DATABASE="postgres"

root(at)gilbertoc:/home/gilbertoc# pg_dump -Fc -h "$HOSTNAME" -U
"$USERNAME" "$DATABASE" > /tmp/postresp.sql

root(at)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(at)gilbertoc:/home/gilbertoc# sudo su - postgres

postgres(at)gilbertoc:~$ export HOSTNAME="localhost"; USERNAME="postgres";
DATABASE="postgres"

postgres(at)gilbertoc:~$ pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME"
"$DATABASE" > /tmp/postresp.sql
-su: /tmp/postresp.sql: Permiso denegado

postgres(at)gilbertoc:~$ export HOSTNAME="127.0.0.1"; USERNAME="postgres";
DATABASE="postgres"

postgres(at)gilbertoc:~$ pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME"
"$DATABASE" > /tmp/salvapostgres.backup

postgres(at)gilbertoc:~$

postgres(at)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(at)gmail(dot)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(at)yahoo(dot)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(dot)castillo(at)etecsa(dot)cu"
>> <gilberto(dot)castillo(at)etecsa(dot)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(at)gilbertoc:/home/gilbertoc# export HOSTNAME="localhost";
>>
>> USERNAME="postgres"; DATABASE="postgres"
>>
>> root(at)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(at)gilbertoc:/home/gilbertoc# sudo su - postgres
>>
>> postgres(at)gilbertoc:~$ export HOSTNAME="localhost";
>> USERNAME="postgres";
>>
>> DATABASE="postgres"
>>
>> postgres(at)gilbertoc:~$ pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME"
>>
>> "$DATABASE" > /tmp/postresp.sql
>>
>> /tmp/postresp.sql: Permiso denegado
>>
>> Fine:
>>
>> postgres(at)gilbertoc:~$ export HOSTNAME="127.0.0.1";
>> USERNAME="postgres";
>>
>> DATABASE="postgres"
>>
>> postgres(at)gilbertoc:~$ pg_dump -Fc -h "$HOSTNAME" -U "$USERNAME"
>>
>> "$DATABASE" > /tmp/salvapostgres.backup
>>
>> postgres(at)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(dot)castillo(at)etecsa(dot)cu"
>>
>>> <gilberto(dot)castillo(at)etecsa(dot)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(at)gmail(dot)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(at)yahoo(dot)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/