postgres-setup.sh

#!/bin/bash

# 数据库配置变量
DB_USER="xxx"
DB_PASSWORD="xxx"
DB_NAME="xxx"
DB_HOST="0.0.0.0"  # 监听所有 IP 地址，根据需要可以修改为特定的外部 IP 地址

# 安装 PostgreSQL
echo "Installing PostgreSQL..."
sudo apt update
sudo apt install -y postgresql postgresql-contrib

# 确保 PostgreSQL 服务已启动
sudo systemctl start postgresql
sudo systemctl enable postgresql

# 修改 postgresql.conf
echo "Configuring PostgreSQL to listen on all interfaces..."
sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '$DB_HOST'/g" /etc/postgresql/*/main/postgresql.conf

# 修改 pg_hba.conf，允许外部连接
echo "Configuring pg_hba.conf for remote connections..."
echo "host    $DB_NAME    $DB_USER    0.0.0.0/0    md5" | sudo tee -a /etc/postgresql/*/main/pg_hba.conf

# 重启 PostgreSQL 服务应用配置
sudo systemctl restart postgresql

# 创建数据库和用户
echo "Creating new database and user..."
sudo -u postgres psql -c "CREATE USER $DB_USER WITH PASSWORD '$DB_PASSWORD';"
sudo -u postgres psql -c "CREATE DATABASE $DB_NAME OWNER $DB_USER;"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $DB_USER;"

# 输出连接字符串
echo "Database connection string:"
echo "postgresql://$DB_USER:$DB_PASSWORD@$DB_HOST:5432/$DB_NAME"

# 开放防火墙端口（如果有防火墙）
echo "Configuring firewall to allow PostgreSQL connections..."
sudo ufw allow 5432/tcp

echo "Database and user created successfully with remote access enabled."