PSQL将表复制到CSV-生产数据库的数据一致性

Question

我正在使用下面的shell脚本进行数据库进行分离CSV文件的转储：

PGENGINE=$PGHOME/bin
PGPASSWORD=$1 $PGENGINE/psql -p $2 -h $3 -U $4 -Atc "select tablename from pg_tables where schemaname='public'" $5 |
while read TBL; do
    echo "Exporting table "$TBL
    PGPASSWORD=$1 $PGENGINE/psql -p $2 -h $3 -U $4 -c "COPY public.$TBL TO STDOUT WITH CSV HEADER DELIMITER '"$SEPARATEUR_CSV"'" $5 > /$ROOT_PATH/$6/$TBL.csv
    echo -e $TBL ": Export donen"
done

这在我的测试数据库上正常工作，但是我担心在生产数据库上运行它会发生什么。

我看到了许多主题，说PG_DUMP收到了数据锁定，但我不知道PSQL副本，包括我在所有桌子上进行循环。我需要确保，如果用户更新了我的一个表，则复制命令仍将获取正确的数据和正确的fks。

我的问题：

1. 您认为这是一种正确的方法吗？存储过程是否更安全？

2. 实现这一目标的最有效方法是什么？（由于此生产数据库很大 - 有些桌子超过3000万行）。

Answer 1

通过在可重复的读取隔离模式下启动交易并在所有读取时结束交易，可以实现实时数据库中的表格一致的读取。您的脚本必须进行转换，以便只有一个psql调用，看起来像这样：

psql [connection arguments] << EOF
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
copy table1 TO file1.csv
copy table2 TO file2.csv
copy table3 TO file3.csv
COMMIT;
EOF

注意copy而不是COPY，这是因为将所有人分组为相同的PSQL调用。psql本身将每个copy的数据路由到每个客户端输出文件。

这也是一个两步的工作流程：首先生成上述脚本（例如，在psql -c 'select tablename....'的结果或任何其他方法的结果中循环循环），然后执行脚本。

为什么不能将其简化为一步？

循环不能在PSQL脚本中实现，因为PSQL没有循环，除了某种程度上使用gexec，但在这里不适用，因为copy是一个元命令，而gexec仅处理SQL命令。

除非更改问题的上下文，否则该循环也不能在PLPGSQL中实现，因为COPY TO STDOUT的每个输出都不会路由到相应的topable client端端文件。随着所有内容串联到单个流中，它将返回客户。如果使用SQL命令COPY TO file，它将有效，但您需要成为超级用户，并且文件最终在服务器上而不是在客户端上。

Answer 2

我最终得到了此解决方案：

PGENGINE=$PGHOME/bin
    CHEMIN_SCRIPT_TRANSACTION=/$ROOT_PATH/plc/proc/tmp/dump_transaction.sql
    DOSSIER_DUMP_FICHIERS=/$ROOT_PATH/dump/dump_$6/dump_fichiers
    echo "BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;" > $CHEMIN_SCRIPT_TRANSACTION
    PGPASSWORD=$1 $PGENGINE/psql -p $2 -h $3 -U $4 -Atc "select tablename from pg_tables where schemaname='public'" $5 |
    while read TBL; do
        echo "copy $TBL TO $DOSSIER_DUMP_FICHIERS/$TBL.csv WITH CSV HEADER DELIMITER ';';" >> $CHEMIN_SCRIPT_TRANSACTION
        echo "echo " >> $CHEMIN_SCRIPT_TRANSACTION
    done
    echo "COMMIT;" >> $CHEMIN_SCRIPT_TRANSACTION
    PGPASSWORD=$1 $PGENGINE/psql -p $2 -h $3 -U $4 -d $5 -f $CHEMIN_SCRIPT_TRANSACTION

我正在在另一个文件中创建一个脚本，然后我使用psql -f播放此脚本。