Ubuntu下使用Shell脚本自动化创建PostgreSQL数据库失败求助

问题描述 #

在自动化应用部署流程中，需要创建PostgreSQL数据库并完成初始配置。手动终端操作正常，但编写的.sh脚本运行时，启动交互式psql会话后便停滞，无法执行后续命令。

原脚本代码 #

#!/bin/sh
sudo -u postgres psql
read -p "Enter a unique Postgresql db name: " DBN
CREATE DATABASE $DBN;
read -p "Enter a name for Database $DBN: " USN
read -p "Enter a password for user $USN: " USPSD
CREATE USER USN WITH PASSWORD 'USPSD';
ALTER ROLE USN SET client_encoding TO 'utf8';
ALTER ROLE USN SET default_transaction_isolation TO 'read committed';
ALTER ROLE USN SET timezone TO 'UTC';
GRANT ALL PRIVILEGES ON DATABASE DBN TO USN;
\q

运行输出 #

could not change directory to "/home/ubuntu": Permission denied
psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1))
Type "help" for help.
postgres=#

错误原因 #

1. 交互式会话阻塞：sudo -u postgres psql会启动交互式psql会话，脚本会暂停等待用户手动输入命令，后续的CREATE/ALTER等语句根本不会被执行。
2. 命令环境错误：CREATE/ALTER是PostgreSQL的SQL命令，直接在shell中执行会报错，shell无法识别SQL语法。
3. 变量引用错误：脚本中USN、USPSD、DBN作为变量使用时未加$，会被当作字面量处理，无法正确传递用户输入的值。

解决方案 #

方案1：使用psql -c执行单条SQL命令 #

通过-c参数直接指定要执行的SQL命令，无需进入交互式会话，脚本可持续运行：

#!/bin/sh
read -p "Enter a unique Postgresql db name: " DBN
read -p "Enter a username for Database $DBN: " USN
read -s -p "Enter a password for user $USN: " USPSD
echo

# 创建数据库
sudo -u postgres psql -c "CREATE DATABASE $DBN;"

# 创建用户并设置密码
sudo -u postgres psql -c "CREATE USER $USN WITH PASSWORD '$USPSD';"

# 配置用户角色参数
sudo -u postgres psql -c "ALTER ROLE $USN SET client_encoding TO 'utf8';"
sudo -u postgres psql -c "ALTER ROLE $USN SET default_transaction_isolation TO 'read committed';"
sudo -u postgres psql -c "ALTER ROLE $USN SET timezone TO 'UTC';"

# 授予数据库权限
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE $DBN TO $USN;"

• 用read -s隐藏密码输入，避免明文泄露
• 每个SQL命令独立通过psql -c执行，逻辑清晰

方案2：使用Here Document批量执行SQL #

将所有SQL命令通过Here Doc传递给psql，仅需调用一次psql，效率更高：

#!/bin/sh
read -p "Enter a unique Postgresql db name: " DBN
read -p "Enter a username for Database $DBN: " USN
read -s -p "Enter a password for user $USN: " USPSD
echo

# 转义密码中的单引号（避免SQL语法错误）
USPSD=$(echo "$USPSD" | sed "s/'/''/g")

sudo -u postgres psql << EOF
CREATE DATABASE $DBN;
CREATE USER $USN WITH PASSWORD '$USPSD';
ALTER ROLE $USN SET client_encoding TO 'utf8';
ALTER ROLE $USN SET default_transaction_isolation TO 'read committed';
ALTER ROLE $USN SET timezone TO 'UTC';
GRANT ALL PRIVILEGES ON DATABASE $DBN TO $USN;
EOF

• << EOF与EOF之间的内容会作为输入传递给psql，模拟交互式会话输入
• 添加密码转义逻辑，避免密码含特殊字符时出现SQL语法错误

额外提示 #

• 若要消除could not change directory警告，可在调用psql时指定工作目录：sudo -u postgres psql -d postgres
• 可添加错误检查，通过$?判断每个psql命令是否执行成功，比如：

  sudo -u postgres psql -c "CREATE DATABASE $DBN;"
  if [ $? -ne 0 ]; then
      echo "数据库创建失败"
      exit 1
  fi
  

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