飞天使-pgsql数据库迁移以及备份

安装pgsql客户端

pgsql14安装
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql14-server
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14

涉及一个pgsql 有多个库时，则先运行这条命令，将库名记录下来
/usr/pgsql-14/bin/pg_dumpall -h 127.0.0.1 -p 5432 -U postgres -c  -f db_szb11ak.sql

导出数据

#!/bin/bash

cur_time=$(date '+%Y-%m-%d')
 
sevendays_time=$(date -d -7days '+%Y-%m-%d')

databases=(
xxx
xxx
xxx
)
 
export PGPASSWORD=fefeefefefsfe
 
for database in "${databases[@]}";
  do
        pg_dump -Ft -U postgres -p 5432 -h 35.0.0.0 -d ${database} > "pgsql-backup-${database}.$cur_time.tar"
  done
 
echo "Remove temp file ..."
 
# rm -rf pgsql-backup.*.tar
echo "Finish Backup ..."

创建新数据库

docker run --name pre-host -e POSTGRES_PASSWORD=4efdfda -p 10022:5432 -v /data/pre/xx/:/var/lib/postgresql/data -d  postgres:14.0

创建多个数据库

#!/bin/bash

cur_time=$(date '+%Y-%m-%d')
 
sevendays_time=$(date -d -7days '+%Y-%m-%d')

databases=(
xxx
xxxx
xxxx
)
 
export PGPASSWORD=xxxxxxx
 
for database in "${databases[@]}";
  do
      psql -h 34.0.0.0 -p 10022 -U postgres -c "CREATE DATABASE ${database}"
  done
 
echo "create finsh"

导入数据

#!/bin/bash

cur_time=$(date '+%Y-%m-%d')
 
sevendays_time=$(date -d -7days '+%Y-%m-%d')

databases=(
xxx
xxx
)
 
export PGPASSWORD=fefsfesfes
for database in "${databases[@]}";
  do
       pg_restore -U postgres -p 10022 -h 34.0.0.0 -d ${database}  pgsql-backup-${database}.$cur_time.tar
  done
 
echo "restore..."
 
 
echo "Finish restore ..."

参考链接：

Postgresql备份与还原命令pg_dump_pg_dump: invalid option -- 'd_Morpheus丶的博客-CSDN博客

PostgreSql自动备份密码设置问题+官方说明_export pgpassword_柴神的博客-CSDN博客

postgresql备份与恢复数据库_ITPUB博客