PostgreSQL 多个库批量执行脚本
有这样的一个业务场景。我的一个数据库实例上面有差不多 80 多个数据库，现在有一个需求，有一个脚本文件需要在这 80 多个数据库上都执行一遍，那么有没有什么简单的方法可以实现。
[root@localhost ~]#
[root@localhost ~]# su - postgres
[postgres@localhost ~]$ vi batch_execute_script.sh
#!/bin/bash
HOST="192.168.30.140"
PORT="5432"
USER="postgres"
PASSWORD="Centos@2024"
export PGPASSWORD=${PASSWORD}
/usr/local/pgsql-12.8/bin/psql -h $HOST -p $PORT -U $USER -d postgres -c "SELECT datname FROM pg_database WHERE datname like 'cloud%';" | tail -n +3 | head -n -2 > datname.txt
for i in `cat datname.txt`
do
/usr/local/pgsql-12.8/bin/psql -h $HOST -p $PORT -U $USER -d $i -f /usr/local/src/test.sql -o i.log
done
#cat datname.txt | while while read line;
#do
# echo $line
# /usr/local/pgsql-12.8/bin/psql -h $HOST -p $PORT -U $USER -d $line -f /usr/local/src/test.sql -o out.log
#done
[root@localhost ~]# touch /usr/local/src/test.sql
[root@localhost ~]# chmod 755 /usr/local/src/test.sql
[root@localhost ~]# vi /usr/local/src/test.sql
CREATE TABLE "public"."mb_user" (
"user_id" int8 NOT NULL,
"user_name" varchar(30),
"user_type" varchar(32),
"telephone" varchar(30),
"gender" varchar(5),
"logic_state" varchar(16) DEFAULT 0,
"create_time" timestamp(6),
"password" varchar(255),
"hospital_id" int8,
"client_id" varchar(64),
"birth" timestamp(6),
"head_img" varchar(2000),
"user_code" varchar(50),
PRIMARY KEY ("user_id")
);
ALTER TABLE "public"."mb_user" OWNER TO "postgres";
COMMENT ON COLUMN "public"."mb_user"."user_name" IS '用户姓名';
COMMENT ON COLUMN "public"."mb_user"."logic_state" IS '逻辑状态';
COMMENT ON COLUMN "public"."mb_user"."create_time" IS '创建时间';
COMMENT ON COLUMN "public"."mb_user"."password" IS '用户密码';
COMMENT ON COLUMN "public"."mb_user"."hospital_id" IS '默认操作医院';
COMMENT ON COLUMN "public"."mb_user"."client_id" IS '个推CID';
COMMENT ON COLUMN "public"."mb_user"."birth" IS '出生年月';
COMMENT ON COLUMN "public"."mb_user"."head_img" IS '头像';
[postgres@localhost ~]$ chmod +x batch_execute_script.sh
[postgres@localhost ~]$ dos2unix batch_execute_script.sh
[postgres@localhost ~]$ sh batch_execute_script.sh
在数据库实例对应的数据库下去查看脚本是否已执行
[postgres@localhost ~]$ /usr/local/pgsql-12.8/bin/psql -h localhost
postgres=# \c cloud_health
cloud_health=# \d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | mb_user | table | postgres
(1 row)
cloud_health=#
cloud_health=# \c cloud_test
cloud_test=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------------+-------------------+----------
public | cloud_backup_test | table | postgres
public | cloud_backup_test_id_seq | sequence | postgres
public | deps_saved_ddl | table | postgres
public | deps_saved_ddl_deps_id_seq | sequence | postgres
public | mb_user | table | postgres
public | partition_table | partitioned table | postgres
public | partition_table_202201 | table | postgres
public | partition_table_202202 | table | postgres
public | partition_table_id_seq | sequence | postgres
public | patient_registration | table | postgres
public | patient_registration_20231219 | table | postgres
public | patient_registration_20231220 | table | postgres
public | patient_registration_id_seq | sequence | postgres
public | user_history | table | postgres
public | user_history_id_seq | sequence | postgres
public | view_test | table | postgres
public | vw_view_testa | view | postgres
public | vw_view_testb | view | postgres
(18 rows)
cloud_test=#
[postgres@localhost ~]$ cat datname.txt
[postgres@localhost ~]$ cat i.log
说明：在当前目录下会有文件 datname.txt 显示了执行了哪些数据库