postgre数据导入导出

背景

• 在软件交付过程中，出厂需要带上较大数据量的数据
• 数据量会比较大，需要一个初始导入的方案
• 需要在初次交付部署时进行导入
• 在后续维护过程中需要持续不断的导入新数据
• 对于数据来说，只需要增加，不需要修改，删除

全量导入方案

1、PG的备份恢复

• 计划使用pg备份恢复功能，对初次交付的sca进行数据导入
• 使用 pg_dump pg_dumpall psql等工具进行数据的备份和恢复
• 需要验证导入时间，相对来说应该是比较快的方式

备份

pg_dump -h postgresql-postgresql-ha-pgpool -p 5432 -U postgres -F t -f backup-prod.tar postgres

恢复

pg_restore -h postgresql-postgresql-ha-pgpool -p 5432 -U postgres -d postgres backup-prod.tar

2、挂载 PG 的持久化目录

• 将内部部署的知识库的pg数据库的持久文件，直接打入部署包
• 在创建数据库容器实例的时候，创建表结构
• 或者在容器创建时，直接挂载该目录

3、创建数据库实例时执行sql

• 在创建数据库实例的时候，挂在数据库sql文件到docker-entrypoint-initdb.d
• 启动数据库可能需要较长时间
• 需要执行数据导入sql，对于io要求较高
• 数据使用sql文本保存，体积较大

4、init容器导入数据

• 容器启动前添加一个新的 init 容器来导入数据

增量导入

1、API导入流程

• 通过api将数据进行上传，可以使用csv或者直接上传sql
• 发起导入，在后台进行sql执行
• 需要严格校验sql的安全性

2、数据文件要求

• 统一采用csv格式，使用zip压缩
• csv文件名与数据库表名称一致
• csv表格字段与数据库表字段名称一致，类型尽量一致

相关脚本

1、删除表

#!/bin/bash

# 数据库连接参数
DB_HOST="192.168.1.1"
DB_PORT="5432"
DB_NAME="postgres"
DB_USER="postgres"
DB_PASSWORD="postgres"

# 删除指定表数据的函数
delete_table_data() {
    table_name=$1
    echo "psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c DELETE FROM $table_name;"
    psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "DELETE FROM $table_name;"
}

# 删除所有表数据的函数
delete_all_tables_data() {
    delete_table_data "t_xxx1"
    delete_table_data "t_xxx2"
    delete_table_data "t_xxx3"
    delete_table_data "t_xxx4"
    delete_table_data "t_xxx5"
}

# 主函数
main() {
	echo "选择要删除数据的表："
  echo "  1. t_xxx1"
  echo "  2. t_xxx2"
  echo "  3. t_xxx3"
  echo "  4. t_xxx4"
  echo "  5. t_xxx5"
  echo "  6. 删除以上表数据"
  echo "  0. 退出"

  printf "\e[1;31m警告：数据删除无法恢复，请谨慎操作 \e[0m\n"
  read -p "请输入选项数字: " choice

  case $choice in
      1) delete_table_data "t_xxx1";;
      2) delete_table_data "t_xxx2";;
      3) delete_table_data "t_xxx3";;
      4) delete_table_data "t_xxx4";;
      5) delete_table_data "t_xxx5";;
      6) delete_all_tables_data;;
      0) exit;;
      *) echo "无效选项";;
  esac
}

main

2、综合执行

#!/bin/bash


function dump() {
    echo "执行 ping 192.168.1.1："
    ping 192.168.1.1
}

function restore() {
    echo "执行 ls 命令："
    ls
}


# 显示选项菜单
printf "\e[1;31m警告：请谨慎操作数据库 \e[0m\n"
echo "请选择要执行的操作："
echo "1. 执行备份"
echo "2. 执行恢复"
echo "0. 退出"

# 读取用户输入的选项
read -p "请输入选项编号： " choice

# 根据用户选择执行相应的函数
case $choice in
    1)
        dump  # 调用执行 ping 命令的函数
        ;;
    2)
        restore    # 调用执行 ls 命令的函数
        ;;
    0)
        exit  # 退出
        ;;
    *)
        echo "错误：无效的选项。"
        ;;
esac

3、备份

#!/usr/bin/expect -f

set timeout 30
set host "192.168.5.81"
set port "31730"
set db "postgres"
set username "postgres"
set password "UV3eYMvL03"

set tables "-t t_comp_version -t t_comp_version_dep -t t_software_license -t t_vul_component -t t_vul_metadata"
set file "backup-dev-test-0509.tar"

spawn pg_dump -U $username -h $host -p $port -F t $tables -f $file -a $db

expect "password"
send "$password\r"

expect eof

4、恢复

#!/usr/bin/expect -f

set timeout 30
set host "192.168.1.1"
set port "5432"
set db "postgres"
set username "postgres"
set password "postgres"

set file "backup.tar"

spawn pg_restore -h $host -p $port -U $username -d $db $file

expect "password"
send "$password\r"

expect eof