我正试图用bash脚本删除AWS RDS服务上的postgres数据库。根据这里的一些答案,我想出了这个:
#!/bin/bash -ex
DATABASE="confluence"
USER="postgres"
export PGPASSWORD="XXX"
PORT="5432"
HOST="postgres.XXX.eu-central-1.rds.amazonaws.com"
echo "drop db"
DROP_DB_COMMAND=$(cat <<EOF
REVOKE CONNECT ON DATABASE ${DATABASE} FROM public;
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = '${DATABASE}';
DROP DATABASE ${DATABASE}
EOF
)
psql
--command "${DROP_DB_COMMAND}"
--host=${HOST}
--port=${PORT}
--user=${USER}
但是我得到一个错误DROP DATABASE cannot run inside a transaction block
。
我知道我必须终止所有现有的连接,并且不能连接到我想要删除的数据库。要实现这一点,合适的psql命令是什么?
由于这是AWS RDS,超级用户rdsadmin
归AWS所有,所以我不能使用任何需要超级用户权限的解决方案。
事实证明,与MySQL相比,您不能在一个事务中完成它。这项工作:
#!/bin/bash -ex
DATABASE="confluence"
USER="postgres"
export PGPASSWORD="XXX"
PORT="5432"
HOST="postgres.XXX.eu-central-1.rds.amazonaws.com"
echo "terminate connections to db"
TERMINATE_COMMAND=$(cat <<EOF
REVOKE CONNECT ON DATABASE ${DATABASE} FROM public;
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = '${DATABASE}';
EOF
)
psql
--command "${TERMINATE_COMMAND}"
--host=${HOST}
--port=${PORT}
--user=${USER}
echo "drop db"
DROP_DB_COMMAND=$(cat <<EOF
DROP DATABASE ${DATABASE};
EOF
)
psql
--command "${DROP_DB_COMMAND}"
--host=${HOST}
--port=${PORT}
--user=${USER}