AWS EC2 Amazon Linux AMI 2实例中PostgreSQL 13已安装后的启动与配置方法咨询

Hey there! Let's get your PostgreSQL 13 up and running smoothly on your Amazon Linux 2 EC2 instance, plus cover the key configurations you’ll probably need. Here’s a step-by-step breakdown:

1. 启动PostgreSQL服务 #

First things first, let's fire up the service and confirm it's running:

• Start the service:

  sudo systemctl start postgresql-13
  

• Verify the service status:

  sudo systemctl status postgresql-13
  

  Look for the active (running) status in the output—if you see that, the service is live!

2. 设置开机自启（避免EC2重启后手动启动） #

To make sure PostgreSQL starts automatically whenever your EC2 instance restarts, run:

sudo systemctl enable postgresql-13

3. 初始化数据库（若安装未自动完成） #

Sometimes the install doesn’t auto-initialize the database cluster. If that’s the case for you:

• Switch to the postgres system user (PostgreSQL runs under this account by default):

  sudo su - postgres
  

• Initialize the database cluster:

  initdb -D /var/lib/pgsql/13/data
  

• Exit the postgres user shell:

  exit
  

• Restart the service to apply the initialization:

  sudo systemctl restart postgresql-13
  

4. 为默认postgres管理员用户设置密码 #

By default, the postgres user has no password—let’s fix that for security:

• Switch to the postgres user:

  sudo su - postgres
  

• Launch the PostgreSQL interactive terminal:

  psql
  

• Run the SQL command to set a secure password (replace your_strong_password_here with a real, secure password):

  ALTER USER postgres WITH PASSWORD 'your_strong_password_here';
  

• Exit the psql terminal with \q, then exit the postgres user shell with exit.

5. 配置远程访问（可选，若需从EC2外部连接） #

If you want to connect to PostgreSQL from your local machine or another server, you’ll need to update two config files and adjust your EC2 security group:

修改postgresql.conf监听所有网络接口 #

编辑主配置文件：

sudo vi /var/lib/pgsql/13/data/postgresql.conf

找到listen_addresses行（可能被#注释），取消注释并修改为：

listen_addresses = '*'

这会让PostgreSQL接受来自任何网络接口的连接。

修改pg_hba.conf允许外部连接 #

编辑基于主机的认证文件：

sudo vi /var/lib/pgsql/13/data/pg_hba.conf

在文件末尾添加一行，允许指定IP范围的连接：

• 允许所有IP（仅建议测试环境使用，生产环境不推荐）：

  host    all             all             0.0.0.0/0               scram-sha-256
  

• 仅允许你的本地IP（生产环境更安全）：

  host    all             all             your_local_ip_address/32      scram-sha-256
  

将your_local_ip_address替换为你的实际公网IP。

重启PostgreSQL应用配置变更 #

sudo systemctl restart postgresql-13

别忘了配置EC2安全组！ #

前往AWS EC2控制台，找到实例绑定的安全组，添加一条入站规则，允许5432端口（PostgreSQL默认端口）的流量从你允许的IP范围进入。

6. 测试连接（可选） #

如果本地安装了psql，可以用以下命令测试远程连接：

psql -h your_ec2_public_ip -U postgres -d postgres

输入之前设置的密码，若能进入psql提示符，说明所有配置都正常工作！

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