Talk:Automated Backup on Linux

From PostgreSQL wiki

Jump to: navigation, search

Hi Guys,

This works well but i have to manually type in the password to access database.

If i need to automate this , i need to have the password read automatically.

Please assist on how to do this.

Maloke

With the default installation postgres user is allowed to access but via socket no via localhost. A quick fix is to remove

-h "$HOSTNAME"

from the psql commands.

--adrianlzt


In the pg_backup.sh script it didn't check if the config file exists:

if [ $# = 0 ]; then
       SCRIPTPATH=$(cd ${0%/*} && pwd -P)
       source $SCRIPTPATH/pg_backup.config
fi;

Without the config file it will not make any backup because this will be empty or false:

SCHEMA_ONLY_DB_LIST=`psql -h "$HOSTNAME" -U "$USERNAME" -At -c "$SCHEMA_ONLY_QUERY" postgres`
if [ $ENABLE_PLAIN_BACKUPS = "yes" ]
if [ $ENABLE_CUSTOM_BACKUPS = "yes" ]


I think that piece of code should be changed to be like the pg_backup_rotated.sh script:

if [ -z $CONFIG_FILE_PATH ] ; then
       SCRIPTPATH=$(cd ${0%/*} && pwd -P)
       CONFIG_FILE_PATH="${SCRIPTPATH}/pg_backup.config"
fi

if [ ! -r ${CONFIG_FILE_PATH} ] ; then
       echo "Could not load config file from ${CONFIG_FILE_PATH}" 1>&2
       exit 1
fi

source "${CONFIG_FILE_PATH}"

--adrianlzt



With Bash 4.1.2: /usr/local/sbin/pg_backup_rotated.sh: line 37: [: too many arguments

Line 37 should be changed to:

if  $BACKUP_USER != "" && "$(id -un)" != "$BACKUP_USER"  ; then

Adding port option

I needed to be able to configure the port.

In pg_backup.config

# Optional port.  Will default to "5432" if none specified.
PORT=

In pg_backup.sh, modify the "DEFAULTS" section to add the last 3 lines (default port number 5432) as below:

###########################
### INITIALISE DEFAULTS ###
###########################
 
if [ ! $HOSTNAME ]; then
	HOSTNAME="localhost"
fi;

if [ ! $USERNAME ]; then
	USERNAME="postgres"
fi;

if [ ! $PORT ]; then
	PORT="5432"
fi;

In pg_backup.sh, after each instance of

-h "$HOSTNAME" 

add:

-p "$PORT"

For example, line 90:

SCHEMA_ONLY_DB_LIST=`psql -h "$HOSTNAME" -p "$PORT" -U "$USERNAME" -At -c "$SCHEMA_ONLY_QUERY" postgres`

becomes:

SCHEMA_ONLY_DB_LIST=`psql -h "$HOSTNAME" -U "$USERNAME" -At -c "$SCHEMA_ONLY_QUERY" postgres`



--Mayeulk (talk) 15:46, 6 October 2014 (UTC) (Natural Resource Governance Institute)

Personal tools