Talk:Automated Backup on Linux

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)

Push backup directly to S3 via s3cmd and stdin

Hi guys, I am working on a script that makes use of the new stdin upload feature of s3cmd and tries to keep the rotation logic.

It's on GitHub, feel free to contribute.

Cheers!

adding Globals_only and Roles to your backup

       #########################################
       ###### GLOBALS and ROLES  BACKUPS #######
       #########################################

       echo -e "\n\nPerforming globals_only and role_only backup"
       echo -e "--------------------------------------------\n"

              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

              if ! pg_dumpall -r -h "$HOSTNAME" -U "$USERNAME" | gzip > "$FINAL_BACKUP_DIR"roles.sql.gz.in_progress; then
                       echo "[!!ERROR!!] Failed to produce roles backup" 1>&2
              else
                       mv "$FINAL_BACKUP_DIR"roles.sql.gz.in_progress "$FINAL_BACKUP_DIR"roles.sql.gz
              fi

        echo -e "\n\nAll global and role backups complete"