# PostgreSQL database full database backup script
#Author: old farmer (Liu Qihua)
#QQ: 46715422
#Email: 46715422@qq.com
#Wechat: 46715422

Test operating system environment: CentOS 6/7, Debian 8/9, FreeBSD 11/12
Test PostgreSQL version: 11 beta3
User name and password: postgres
The database we need to back up this time: test
Script required for backup:. pgpass, back_database
Script storage path / home/postgres
Generate backup storage path / home/postgres

The PostgreSQL server has set the account password verification. At this time, we need to ensure that the password must be entered automatically during the backup. Then, we will create the configuration script file for automatic password entry under the path of the backup script:. pgpass

# file: .pgpass
# hostname:port:database:username:password
localhost:5432:test:postgres:postgres
127.0.0.1:5432:test:postgres:postgres

# . pgpass authorization
chmod 0600 .pgpass

Create a backup script: back u database

#!/bin/sh

export PGHOME=/usr/local/pgsql/
export PATH=$PGHOME/bin:$PATH
export PGDATA=/usr/local/pgsql/data/
export BACKUP_DIR=/home/postgres/
export USER=postgres
export DATABASE=test
export DATE=`date +"%Y-%m-%d"`
export OLDDATE=`date -v -7d +"%Y-%m-%d"`

######################################################
##
##  purpose:backup total database everyday logically
##
##  file: back_database
##  
##  author: EricLiu
##  
##  email: 46715422@qq.com
##   
##  created:2018-08-24
##
##  restore_script:psql -d $DATABASE -U postgres < /home/postgres/file_name
##  
#####################################################

test -f $BACKUP_DIR/$DATABASE.$OLDDATE.dump

if [ $? -eq 0 ] ; then

  rm -f $BACKUP_DIR/$DATABASE.$OLDDATE.dump

  echo " `date +"%Y-%m-%d %H:%M:%S"` the file $BACKUP_DIR/$DATABASE.$OLDDATE.dump has been moved!"

fi

test -f $BACKUP_DIR/$DATABASE.$DATE.dump

if [ $? -eq 0 ] ; then

  echo "Today's(`date +"%Y-%m-%d %H:%M:%S"`) BACKUP job has done!"

else

  pg_dump -h 127.0.0.1 -p 5432 -U $USER -b -Fp $DATABASE -f $BACKUP_DIR/$DATABASE.$DATE.dump


######check the dump file whether produced successfully

  test -f $BACKUP_DIR/$DATABASE.$DATE.dump

  if [ $? -eq 0 ]; then
   
     echo " `date +"%Y-%m-%d %H:%M:%S"` the database $DATABASE has been backuped to $BACKUP_DIR/$DATABASE.$DATE.dump successfully!"

  else
    
     echo " `date +"%Y-%m-%d %H:%M:%S"` the pg_dump failed!"

  fi

fi

# Back database script authorization
chmod 700 back_database

All we have to do is add the back database file to the scheduled task.

The author usually sells special products and second-hand computers in Xinjiang. If you want to communicate, you can scan the QR code below and add the author!