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

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(at)gmail(dot)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(at)yahoo(dot)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(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/
> [3] http://0.0.0.0/0