如何在PostgreSQL中一次性向多个数据库执行SQL语句

下面是几种可行的方法，帮你一次性完成所有或指定数据库的INSERT操作：

方法1：使用dblink扩展跨库执行 #

PostgreSQL的dblink扩展允许在一个数据库会话中连接并操作其他数据库。你可以创建函数遍历目标数据库列表，批量执行插入语句：

步骤： #

1. 在当前数据库中启用dblink扩展：

CREATE EXTENSION IF NOT EXISTS dblink;

2. 创建批量插入函数（假设表名为menu_items，插入数据为('新菜单项', '/new-path')）：

CREATE OR REPLACE FUNCTION batch_insert_menu()
RETURNS void AS $$
DECLARE
    db_name text;
    -- 替换为你的目标数据库列表，也可通过pg_database动态筛选
    target_dbs text[] := ARRAY['db1', 'db2', 'db3', ..., 'db30'];
BEGIN
    FOREACH db_name IN ARRAY target_dbs LOOP
        PERFORM dblink_connect('dbname=' || db_name);
        PERFORM dblink_exec(
            'INSERT INTO menu_items (name, path) VALUES (''新菜单项'', ''/new-path'');'
        );
        PERFORM dblink_disconnect();
    END LOOP;
END;
$$ LANGUAGE plpgsql;

3. 执行函数完成批量插入：

SELECT batch_insert_menu();

注意：执行用户需拥有所有目标数据库的INSERT权限；若目标库未启用dblink，可在函数内先执行CREATE EXTENSION IF NOT EXISTS dblink;。

方法2：用Shell脚本批量执行 #

如果不想依赖数据库扩展，可编写Shell脚本通过psql遍历数据库执行插入：

#!/bin/bash

# 目标数据库列表
DBS=("db1" "db2" "db3" ... "db30")
# 插入语句
INSERT_SQL="INSERT INTO menu_items (name, path) VALUES ('新菜单项', '/new-path');"

for DB in "${DBS[@]}"
do
    echo "正在插入数据库: $DB"
    psql -U your_username -d "$DB" -c "$INSERT_SQL"
done

添加执行权限后运行：

chmod +x batch_insert.sh
./batch_insert.sh

这种方式无需依赖数据库扩展，可灵活筛选要操作的数据库，适合简单批量场景。

方法3：重构架构（长期最优解） #

既然所有数据库的菜单表结构、数据完全一致，建议重构架构避免重复维护：

• 创建公共数据库（如shared_core），将menu_items表放在该库中。
• 在其他30个业务库中，创建**外部表（Foreign Table）**指向公共库的menu_items表：

  -- 在每个业务库中执行
  CREATE EXTENSION IF NOT EXISTS postgres_fdw;
  CREATE SERVER shared_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'shared_core');
  CREATE USER MAPPING FOR your_username SERVER shared_server OPTIONS (user 'your_username', password 'your_password');
  CREATE FOREIGN TABLE menu_items (
      id serial,
      name varchar(100),
      path varchar(200)
  ) SERVER shared_server OPTIONS (schema_name 'public', table_name 'menu_items');
  

此后只需向公共库的menu_items表插入一次，所有业务库即可通过外部表访问最新数据，彻底解决重复插入问题。

内容的提问来源于stack exchange，提问作者Arif Hosain