postgresql数据库非实时同步实现方案

前言

公司有个数据中台项目，数据要求可以非实时同步，既可以实现第二天把第一天的生产数据同步到一个中间库，数据中台项目再从这个中间库抽取数据。考虑到同步整个库量大，且存在可能出现的性能问题，决定采用postgresql逻辑备份的方式，在一个备份服务器上定时备份中台需要的表数据，然后通过rsync的方式同步到中间库并还原恢复。

注：该方案也可以用于其他类型的数据库(oracle,mysql,sqlserver)等，如果目标数据库要求可以非实时同步的话。

数据流如下：
生成库 – 备份服务器 – 中间库 – 中台

---

相关备份脚本：
备份服务器：
1.定时备份脚本，备份目录 aa
2.转移最新的备份文件到一个指定目录 newdata，确保该目录只有一份最新的备份文件
3.配置好rsync，目录指定到newdata

中间库：
1.数据拉取脚本
2.数据还原脚本

具体脚本如下：

备份服务器：

1.备份脚本export_ccmdb.bat

@echo off

REM database infomation
set db=ccm_db
set host=10.128.xxx.xxx
set port=5436
set user=postgres
set output_base_dir="D:\DMP\CCMDB"
set pg_dump_dir="D:\Program Files\PostgreSQL\12\bin\"

REM set zip tool path
set ziptool="C:\Program Files\7-Zip\7z.exe"

REM KEEP DAYS
set keep_days=2

REM set date time
set datevar=%date:~0,4%%date:~5,2%%date:~8,2%
set timevar=%time:~0,2%
set timevar=%timevar%%time:~3,2%%time:~6,2%

REM set backup file path
set backup_file_dir=%output_base_dir%\%db%_%datevar%
if not exist "%backup_file_dir%" (
    mkdir "%backup_file_dir%"
    echo  %backup_file_dir% Create Successfully!
) else (
    echo  %backup_file_dir% is already exist!
)

REM set backup logfile path
set logfile_path="%output_base_dir%\logs"
set logfile="%output_base_dir%\logs\backuplog_%datevar%%timevar%.log"
if not exist "%logfile_path%" (
    mkdir "%logfile_path%"
    echo  %logfile_path% Create Successfully!
) else (
    echo  %logfile_path% is already exist!
)
call :LOG > %logfile%
exit /B

:LOG
echo ------------------------------------------------------------------------------------------------
echo ---------------- Record the start time ------------------------------------------------------
echo ------------------------------------------------------------------------------------------------
set /a startS=%time:~6,2%
set /a startM=%time:~3,2%
echo Start_Time %time%
echo -------------------------------------------------------------------------------------------------
echo ---------------- begin backup table ---------------------------------------------------------
echo -------------------------------------------------------------------------------------------------
echo.
echo.
REM backup all tables in table_list
set tabname=''
setlocal enabledelayedexpansion
d:
cd %output_base_dir%
echo Current floder %cd%
for /f %%a in (.\tablelist.txt) do (
	echo ---------------------------------------------------------------
    set tabname=%%a
	echo Start export !tabname!
	%pg_dump_dir%\pg_dump -d %db% -h %host% -p %port% -U %user% -w -f %backup_file_dir%\!tabname!.dmp -Fc -t !tabname!
	echo ---------------------------------------------------------------
	echo.
)

echo.
echo -------------------------------------------------------------------------------------------------
echo ---------------- end backup table ---------------------------------------------------------
echo -------------------------------------------------------------------------------------------------
echo.
echo.
echo ------------------------------------------------------------------------------------------------
echo ---------------- Start compressing the backup folder ---------------------------------------
echo ------------------------------------------------------------------------------------------------
d:
cd %output_base_dir%
echo Current floder %cd%
%ziptool% a -tzip %backup_file_dir%.zip %backup_file_dir%
echo ---------------- delele backup folder -----------------------------------------------------------
echo.
echo.
echo %backup_file_dir%
rd /s /Q %backup_file_dir%
echo ------------------------------------------------------------------------------------------------
echo ---------------- End compressing the backup folder ------------------------------------------
echo ------------------------------------------------------------------------------------------------
echo.
echo.
echo -------------------------------------------------------------------------------------------------
echo ---------------- delete before 7 days backupfile  ---------------------------------------
echo -------------------------------------------------------------------------------------------------
d:
cd %output_base_dir%
echo Current floder %cd%
forfiles /p %output_base_dir% /s /m *.zip /d -%keep_days% /c "cmd /c del @path"
echo.
echo.
echo ------------------------------------------------------------------------------------------------
echo ---------------- Record backup time stake -------------------------------------------------
echo ------------------------------------------------------------------------------------------------
echo.
set /a endS=%time:~6,2%
set /a endM=%time:~3,2%
echo End_Time %time%
set /a diffS_=%endS%-%startS%
set /a diffM_=%endM%-%startM%
echo.
echo.
echo Cost_Time %diffM_% Min %diffS_% Second
echo.
echo.
echo -------------------END----------------------

需要备份的表写在一个txt文件 tablelist.txt

app_signature_info
app_task_arrange
app_task_dynamic
app_task_flt
app_task_info
app_task_node
app_task_type
app_sys_dic
app_sys_org

2.转移最新的备份

@echo off
setlocal enabledelayedexpansion
echo ----------------------------------------------------------------------
echo --------------- 功能：获取最新的DMP文件转移到数据中台目录 ------------
echo ----------------------------------------------------------------------


:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
echo --------------------------获取最新的DMP文件转移到数据中台目录------------
echo --------------------------设置DMP文件所在的目录--------------------------
rem 准备执行
rem set userdomain
rem 设置文件所在目录
set src_ccmdb_dir=D:\DMP\CCMDB
set des_ccmdb_dir=D:\SJZT\CCMDB

rem filename用于存放目标文件名
set filename=""
echo --------------------------扫描文件获取最新的文件---------------------
cd /d %src_ccmdb_dir%
for /f %%a in ('dir /o-d /tc /b ') do (
    echo 文件完整信息: %%a
    set filename=%%~na%%~xa
    echo 文件名: !filename!, 最新创建时间： %%~ta
    if not !filename! == ""  (
        goto iconv
    )
 
)
 
echo --------------------------转移最新的文件-----------------------------
:iconv
echo %filename%
echo f|xcopy %filename% %des_ccmdb_dir%\ /y
 
rem 执行完毕
rem pause

3.配置好rsync

uid=0
gid=0
use chroot = false
strict modes = false
hosts allow = *
log file = rsyncd.log

[sjztccmdb]
path = /cygdrive/d/SJZT/CCMDB
read only = false
transfer logging = yes

中间库

数据拉取脚本和数据还原脚本结合在一个脚本里面

$ cat imp_ccm_db.sh 
#!/bin/bash

###########################################################
##### 1，请修改系统名称,大写字母                ###########
##### 2，请修改DMP名称,小写字母                ###########
###########################################################
## 系统名称
export systemname=ccmdb
## rsync的名称
export rycname=sjztccmdb

export dbname=ccmdb
export dbuser=postgres
export dbport=5436
export pg_dump=/OSdata/pg12/bin/pg_dump
export pg_restore=/OSdata/pg12/bin/pg_restore
export pg_psql=/OSdata/pg12/bin/psql

## dmp文件路径 
export dmp=/OSdata/PGDMP/${systemname}
## 日志文件路劲
export LOG=/home/postgres/scripts/logs
# 记录开始时间
export dtime=`date +%Y%m%d%H%M%S`

###开始
echo ----------------------- START -------------------------------------------------
startTime=`date +%s`

## 从备份服务器拉取dmp文件
echo ----------------------- 从备份服务器拉取dmp文件 ----------------------------
##--remove-source-files
rsync --port=873 -avzrtopg --progress --remove-source-files --delete 10.128.xxx.xxx::${rycname} $dmp

## 解压dmp文件
echo ----------------------- 格式化dmp文件 -----------------------------------------
cd $dmp
unzip ccm_db*.zip
rm -rf ccm_db*.zip 
cd ccm_db_*
mv *.* /OSdata/PGDMP/ccmdb
cd .. && rm -rf ccm_db_*     ##删除空目录


###开始导入数据
echo ----------------------- 导入数据-----------------------------------------------
echo ----------------------- 1.delete old data -------------------------------------
IFS=$'\n\n'  
cd /home/postgres/scripts  
for i in `cat tablelist_ccmdb`;  
do  
    echo "------------------ begin_drop_table --------------------------------------"  
    echo $i  
        $pg_psql -h 127.0.0.1 -p 5436 -d ccm_db -U postgres --command "drop table $i"
    echo "------------------ end_drop_table ----------------------------------------"  
done  

echo ----------------------- 2.import new data ------------------------------------

cd $dmp
IFS=$'\n\n'  

files=$(ls $dmp)
for file in $files
do
  echo $file
  echo "-------------------- begin_import_data ------------------------------------"
  $pg_restore -d ccm_db -h 10.128.xxx.xxx -p 5436 -U postgres $dmp/$file
  echo "-------------------- end_import_data --------------------------------------"
done

## 导入成功之后删除DMP文件
echo -----------------------开始删除DMP文件 ----------------------------------------
rm -rf /OSdata/PGDMP/ccmdb/*

###结束
echo ---------END-------------------

echo -----------------------完成：总耗时--------------------------------------------
endTime=`date +%s`
sumTime=$(( $endTime - $startTime ))
echo "Total run $sumTime seconds"
echo ----------------------- END ---------------------------------------------------