不同机器postgresql 全量同步脚本

先设置ssh远程登录
https://blog.csdn.net/qq_41982570/article/details/122009809?spm=1001.2014.3001.5501

设置 pg_dump 和 psql 无密码输入
https://blog.csdn.net/qq_41982570/article/details/122008995?spm=1001.2014.3001.5501

#!/bin/bash

# 获取前一天的日期
before_date=`date --date="1 days ago" +%F`

# 获取前三天的日期
before3_date=`date --date="3 days ago" +%F`

# 重命名sql脚本 
mv /home/bigdata/eimos_business.sql  /home/bigdata/eimos_business_$before_date.sql
mv /home/bigdata/data_integration.sql  /home/bigdata/data_integration_$before_date.sql


# 远程导出 并同步回来
ssh bigdata@172.16.5.162 > /dev/null 2>&1 << sshoff
    rm /home/bigdata/eimos_business.sql
	rm /home/bigdata/data_integration.sql
    rm /home/bigdata/eimos_business.tar.gz
	rm /home/bigdata/data_integration.tar.gz

    pg_dump -h 172.16.5.162 -p 5432 -U bigdata --column-inserts eimos_business > /home/bigdata/eimos_business.sql
    pg_dump -h 172.16.5.162 -p 5432 -U bigdata --column-inserts data_integration > /home/bigdata/data_integration.sql

	tar -zcvf /home/bigdata/eimos_business.tar.gz /home/bigdata/eimos_business.sql
	tar -zcvf /home/bigdata/data_integration.tar.gz /home/bigdata/data_integration.sql

    scp /home/bigdata/eimos_business.tar.gz bigdata@172.16.5.161:/home/bigdata/
    scp /home/bigdata/data_integration.tar.gz bigdata@172.16.5.161:/home/bigdata/
exit
sshoff


# 将原来库中的表做快照  执行python脚本
python3 /home/bigdata/dataintegration.py

#解压
tar -zxvf /home/bigdata/eimos_business.tar.gz /home/bigdata/eimos_business.sql
tar -zxvf /home/bigdata/data_integration.tar.gz /home/bigdata/data_integration.sql

#本地导入
psql -h 172.16.5.161 -p 5432 -U bigdata -f /home/bigdata/eimos_business.sql eimos_business >> /home/bigdata/eimos_business.txt 2>&1
psql -h 172.16.5.161 -p 5432 -U bigdata -f /home/bigdata/data_integration.sql data_integration >> /home/bigdata/data_integration.txt 2>&1

rm  /home/bigdata/eimos_business.sql
rm /home/bigdata/data_integration.sql
# 删除三天以前的
rm  /home/bigdata/eimos_business_$before3_date.tar.gz
rm /home/bigdata/data_integration_$before3_date.tar.gz

echo "数据库同步成功"

import psycopg2
import time
import re

conn = psycopg2.connect(database="xxx",
                        user="xxx",
                        password="xx",
                        host="xxx.xxx.xxx.xxx",
                        port="5432")

cur = conn.cursor()
# 查询所有的表
cur.execute(
    '''select relname as tabname from pg_class c where relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%' order by relname;''')

rows = cur.fetchall()
# 获取当前日期
cur_date = time.strftime('%Y_%m_%d', time.localtime(time.time()))

# 更新表名 只更新不带日期的表名
for row in rows:
    if re.match('.*\d{4}_\d{2}_\d{2}', row[0]) == None:
        str_sql = "alter table " + row[0] + " rename to " + row[0] + "_" + cur_date + ";"
        cur.execute(str_sql)
# 提交事务
conn.commit()

导出单个的表
pg_dump -h 0.0.0.0 -U postgres -p 5432 -d xxx（库名） -t public.xxx（表名）--inserts >/root/xxx/2021-12-24/xxx.sql