PostgreSQL逻辑备份脚本

脚本功能

应对多个库多个表需要定期逻辑备份的场景

需要备份的数据库较多时按数据库并行执行备份

脚本使用示例

database.list 为需要备份的数据库信息，文件内容为竖线分隔:ip|port|database|user|passwd|app_name

test.table_list 为需要备份的表名，此示例文件名里的”test“为database.list中的database名，多个database时需要创建多个文件

可以在文件内容中每行开头添加“#”来注释掉不需要备份的数据库或表

[postgres@mydb1a pg_dump_parallel]$ ls -ltr
总用量 8
-rw-------. 1 postgres postgres 3031  9月 18 23:42 pg_dump_parallel.sh
drwx------. 2 postgres postgres 4096  9月 18 23:56 config
[postgres@mydb1a pg_dump_parallel]$ cat config/database.list
192.168.8.11|5432|test|postgres|postgres|test_dump
[postgres@mydb1a pg_dump_parallel]$ cat config/test.table_list
test
test_1
[postgres@mydb1a pg_dump_parallel]$ sh pg_dump_parallel.sh
[postgres@mydb1a pg_dump_parallel]$ cat logs/telepg_dump_test_dump.log.20240918
2024-09-18__23:56:54--fun start...... 192.168.8.11:5432/test
2024-09-18__23:56:54--fun 192.168.8.11:5432/test connect check ok
2024-09-18__23:56:54--fun begin create data dir /home/postgres/pg_dump_parallel/data/test_dump/20240918
2024-09-18__23:56:54--fun begin dump test to /home/postgres/pg_dump_parallel/data/test_dump/20240918/test.20240918
COPY 0
2024-09-18__23:56:54--fun begin dump test_1 to /home/postgres/pg_dump_parallel/data/test_dump/20240918/test_1.20240918
COPY 10
2024-09-18__23:56:54--fun begin clear 20240911 data dir  /home/postgres/pg_dump_parallel/data/test_dump/20240911
rm: 无法删除 '/home/postgres/pg_dump_parallel/data/test_dump/20240911': 没有那个文件或目录
2024-09-18__23:56:54--fun end...... 192.168.8.11:5432/test
[postgres@mydb1a pg_dump_parallel]$ ll data/test_dump/20240918/*
-rw-------. 1 postgres postgres  27  9月 18 23:56 data/test_dump/20240918/test_1.20240918.csv
-rw-------. 1 postgres postgres 953  9月 18 23:56 data/test_dump/20240918/test_1.20240918.sql
-rw-------. 1 postgres postgres   0  9月 18 23:56 data/test_dump/20240918/test.20240918.csv
-rw-------. 1 postgres postgres 750  9月 18 23:56 data/test_dump/20240918/test.20240918.sql
复制

pg_dump_parallel.sh

#!/bin/bash


dir=$(cd "$(dirname "${BASH_SOURCE[0]}")" >/dev/null 2>&1 && pwd)


if [ -f $dir/config/database.list ];then
  dbfile=$dir/config/database.list
else
  echo "database.list file does not exists!"
  echo "eg:  ip|port|database|user|passwd|app_name"
  exit;
fi

today=`date +"%Y%m%d"`
clear_day=`date -d '7 day ago' +"%Y%m%d"`
log_dir=$dir/logs
mkdir -p $log_dir
db_connect_timeout=300

write_log(){
  msg="$1"
  log_file="$2"
  echo "`date +%Y-%m-%d__%H:%M:%S`--$msg" >> $log_file
}

#check db connection
check_connect(){
  url="$1"
  res=`psql "$url" -Atc "select 1" 2>/dev/null`
  if [ -n $res ] && [[ $res -eq 1 ]];then
    return 1;
  fi
}

#dump table def to *.sql,and copy data to *.csv
dump(){
  url="$1"
  table_name="$2"
  file_name="$3"
  log_file="$4"
  pg_dump $url -s -t $table_name -f ${file_name}.sql >>$log_file 2>&1 
  psql $url -Atc "\copy  (select * from $table_name) to ${file_name}.csv " >>$log_file  2>&1
#  echo "pg_dump $url -s -t $table_name -f ${file_name}.sql"
#  echo "psql $url -Atc \"\copy  (select * from $table_name) to ${file_name}.csv \""
}

#loop db list,get table list
fun(){
  for conn_info in `cat $dbfile|grep -v "^#"`
  do
  (  #get db connection info from database.list
    ip=`echo $conn_info|awk -F '|' '{print $1}'`
    port=`echo $conn_info|awk -F '|' '{print $2}'`
    database=`echo $conn_info|awk -F '|' '{print $3}'`
    user=`echo $conn_info|awk -F '|' '{print $4}'`
    passwd=`echo $conn_info|awk -F '|' '{print $5}'`
    app_name=`echo $conn_info|awk -F '|' '{print $6}'`
    log_file=$log_dir/telepg_dump_$database_$app_name.log.$today
    url="postgresql://$user:$passwd@$ip:$port/$database?connect_timeout=$db_connect_timeout"
    write_log "${FUNCNAME} start...... $ip:$port/$database" "$log_file"
    if [ -f $dir/config/${database}.table_list ];then
      table_file=$dir/config/${database}.table_list
      check_connect "$url"
      if [ $? -eq 1 ];then
        write_log "${FUNCNAME} $ip:$port/$database connect check ok"  "$log_file"
        data_dir=$dir/data/$app_name/$today
        write_log "${FUNCNAME} begin create data dir $data_dir"  "$log_file"
        mkdir -p $data_dir >>$log_file 2>&1
        #get table list from db.table_list
        for table_name in `cat $table_file|grep -v "^#"`
        do
          file_name=$data_dir/${table_name}.${today}
          write_log "${FUNCNAME} begin dump $table_name to $file_name" "$log_file"
          dump "$url" "$table_name" "$file_name" "$log_file"
        done
        #clear 7 days ago data
        clear_data_dir=$dir/data/$app_name/$clear_day
        write_log "${FUNCNAME} begin clear $clear_day data dir  $clear_data_dir"  "$log_file"
        rm -r $clear_data_dir >>$log_file 2>&1
      else
        write_log "${FUNCNAME} connect check is not ok,$ip:$port/$database connect fail"  "$log_file"
      fi  
    else
      write_log "${FUNCNAME} ${database}.table_list does not exists!" "$log_file"
    fi
    write_log "${FUNCNAME} end...... $ip:$port/$database"  "$log_file"
  )&  done
}

fun
复制