1. Ubuntu系统准备与PostgreSQL安装在开始PostgreSQL的安装之前强烈建议先确保你的Ubuntu系统处于最新状态。我遇到过不少因为系统包未更新导致的依赖冲突问题特别是当系统版本较老时。打开终端依次执行以下命令sudo apt update sudo apt upgrade -y这个步骤可能需要5-15分钟不等取决于你的网络速度和系统更新量。完成后建议重启系统确保所有更新生效。PostgreSQL官方提供了专为Ubuntu优化的软件源比系统自带的版本更新更及时。添加源时要注意匹配你的Ubuntu版本代号如20.04是focal22.04是jammy。我推荐使用以下自动化方式添加sudo sh -c echo deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main /etc/apt/sources.list.d/pgdg.list wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo apt update密钥添加成功后安装最新稳定版的PostgreSQL当前是15版本sudo apt install -y postgresql postgresql-contrib这里的postgresql-contrib包包含了很多实用扩展如pg_trgm、hstore等建议一并安装。整个安装过程大约需要200MB磁盘空间安装完成后会自动创建postgres系统用户并启动服务。验证安装是否成功有两个简单方法# 方法一检查服务状态 sudo systemctl status postgresql # 方法二查询版本号 sudo -u postgres psql -c SELECT version();如果看到类似PostgreSQL 15.3 on x86_64-pc-linux-gnu的输出说明安装成功。我在AWS的t2.micro实例上测试从开始安装到验证成功总共不到5分钟。2. 数据库用户与权限管理安装完成后系统会自动创建名为postgres的数据库超级用户。但直接使用这个账号存在安全隐患我的建议是创建专属管理员账号。先切换到postgres用户sudo -u postgres psql在psql交互界面中执行以下SQL创建新管理员以adminuser为例CREATE ROLE adminuser WITH LOGIN PASSWORD StrongPassword123# SUPERUSER CREATEDB CREATEROLE;这里有几个关键点需要注意WITH LOGIN表示允许该角色登录SUPERUSER赋予所有权限生产环境慎用CREATEDB允许创建数据库CREATEROLE允许创建其他角色实际项目中我通常会根据最小权限原则分配权限。比如只给开发人员特定数据库的权限CREATE USER devuser WITH PASSWORD DevPass123; GRANT CONNECT ON DATABASE projectdb TO devuser; GRANT USAGE ON SCHEMA public TO devuser; GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO devuser;权限管理容易出错的地方是级联权限。比如要撤销权限时需要加上CASCADEREVOKE ALL ON DATABASE projectdb FROM devuser CASCADE;一个实用的技巧是使用\du命令查看用户权限用\dp查看表级权限。最近遇到一个案例某用户无法查询表最后发现是缺少schema的USAGE权限GRANT USAGE ON SCHEMA sales TO reports_user;3. 数据库与Schema操作创建数据库时我建议指定编码和排序规则特别是需要多语言支持时CREATE DATABASE webapp WITH OWNER adminuser ENCODING UTF8 LC_COLLATE en_US.UTF-8 LC_CTYPE en_US.UTF-8 TEMPLATE template0;这里的TEMPLATE template0可以避免继承默认数据库的配置问题。删除数据库时要特别注意PostgreSQL要求没有活跃连接时才能删除SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname old_db; DROP DATABASE old_db;Schema是PostgreSQL的重要特性我习惯为每个功能模块创建独立schemaCREATE SCHEMA payment AUTHORIZATION finance_user; CREATE SCHEMA reporting;迁移数据时经常需要修改schema所有者ALTER SCHEMA public OWNER TO adminuser;查看数据库对象的小技巧-- 列出所有数据库 \l -- 查看当前数据库的所有schema \dn -- 查看某schema下的表 \dt payment.*4. 远程访问安全配置PostgreSQL默认只允许本地连接要启用远程访问需要修改两个关键配置文件。首先找到你的配置文件位置不同版本路径略有差异sudo find / -name postgresql.conf 2/dev/null通常路径类似于/etc/postgresql/15/main/postgresql.conf。用sudo权限编辑该文件sudo nano /etc/postgresql/15/main/postgresql.conf找到并修改以下参数listen_addresses * port 5432 password_encryption scram-sha-256这里的listen_addresses *表示监听所有网络接口如果只需要特定IP访问可以设为192.168.1.100等具体地址。接下来配置客户端认证编辑pg_hba.conf文件sudo nano /etc/postgresql/15/main/pg_hba.conf在文件末尾添加类似这样的规则根据你的安全需求调整# TYPE DATABASE USER ADDRESS METHOD host all adminuser 192.168.1.0/24 scram-sha-256 host webapp devuser 0.0.0.0/0 scram-sha-256这里解释下各字段含义host表示TCP/IP连接webapp限制只能访问特定数据库devuser指定适用用户192.168.1.0/24限制IP范围scram-sha-256是最安全的密码验证方式配置完成后需要重启服务sudo systemctl restart postgresql测试远程连接时我常用psql命令psql -h 服务器IP -U 用户名 -d 数据库名如果连接失败检查防火墙设置sudo ufw allow 5432/tcp安全提示长期暴露数据库端口存在风险建议配合SSH隧道或VPN使用。我曾遇到过因为弱密码导致数据库被入侵的案例因此强烈建议使用复杂密码12位以上含大小写、数字、符号定期轮换密码限制可连接IP范围启用日志审计5. 日常维护与故障排查PostgreSQL的日志文件是排查问题的第一手资料默认配置下日志位于/var/lib/postgresql/15/main/log/查看实时日志sudo tail -f /var/lib/postgresql/15/main/log/postgresql-15-main.log常见问题1连接数不足-- 查看当前连接数 SELECT count(*) FROM pg_stat_activity; -- 修改最大连接数(需要重启) ALTER SYSTEM SET max_connections 200;常见问题2性能瓶颈-- 查看长查询 SELECT pid, now()-query_start as duration, query FROM pg_stat_activity WHERE state active ORDER BY duration DESC; -- 终止问题查询 SELECT pg_cancel_backend(pid);备份策略建议# 基本备份 pg_dump -U username -d dbname -f backup.sql # 压缩备份 pg_dump -U username -d dbname | gzip backup.gz # 定时备份(crontab示例) 0 2 * * * pg_dump -U adminuser -d webapp | gzip /backups/webapp_$(date \%Y\%m\%d).gz恢复数据库时要注意# 先创建空数据库 createdb -U postgres restoredb # 再导入数据 psql -U postgres -d restoredb -f backup.sql定期维护操作-- 更新统计信息 ANALYZE; -- 清理膨胀空间 VACUUM FULL; -- 重建索引 REINDEX DATABASE dbname;6. 可视化工具连接实践虽然命令行足够强大但图形化工具能提升效率。以DBeaver为例连接配置要点主机填写服务器IP端口保持5432数据库填写要连接的具体库名用户名/密码用之前创建的驱动选择PostgreSQLSSL选项根据服务器配置选择连接测试失败的常见原因防火墙未放行端口pg_hba.conf配置错误密码错误服务器监听地址限制一个实用的调试技巧是在服务器本地先用psql测试psql -h 127.0.0.1 -U testuser -d testdb如果本地能连但远程不行基本可以确定是网络或防火墙问题。我在阿里云服务器上配置时还需要在安全组规则中添加5432端口放行。对于生产环境建议配置连接池如PgBouncer能有效管理连接资源。开发环境可以使用adminer.php这种轻量级Web界面。
Ubuntu系统下PostgreSQL的安装与远程访问配置全攻略
发布时间：2026/7/9 6:48:27
1. Ubuntu系统准备与PostgreSQL安装在开始PostgreSQL的安装之前强烈建议先确保你的Ubuntu系统处于最新状态。我遇到过不少因为系统包未更新导致的依赖冲突问题特别是当系统版本较老时。打开终端依次执行以下命令sudo apt update sudo apt upgrade -y这个步骤可能需要5-15分钟不等取决于你的网络速度和系统更新量。完成后建议重启系统确保所有更新生效。PostgreSQL官方提供了专为Ubuntu优化的软件源比系统自带的版本更新更及时。添加源时要注意匹配你的Ubuntu版本代号如20.04是focal22.04是jammy。我推荐使用以下自动化方式添加sudo sh -c echo deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main /etc/apt/sources.list.d/pgdg.list wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo apt update密钥添加成功后安装最新稳定版的PostgreSQL当前是15版本sudo apt install -y postgresql postgresql-contrib这里的postgresql-contrib包包含了很多实用扩展如pg_trgm、hstore等建议一并安装。整个安装过程大约需要200MB磁盘空间安装完成后会自动创建postgres系统用户并启动服务。验证安装是否成功有两个简单方法# 方法一检查服务状态 sudo systemctl status postgresql # 方法二查询版本号 sudo -u postgres psql -c SELECT version();如果看到类似PostgreSQL 15.3 on x86_64-pc-linux-gnu的输出说明安装成功。我在AWS的t2.micro实例上测试从开始安装到验证成功总共不到5分钟。2. 数据库用户与权限管理安装完成后系统会自动创建名为postgres的数据库超级用户。但直接使用这个账号存在安全隐患我的建议是创建专属管理员账号。先切换到postgres用户sudo -u postgres psql在psql交互界面中执行以下SQL创建新管理员以adminuser为例CREATE ROLE adminuser WITH LOGIN PASSWORD StrongPassword123# SUPERUSER CREATEDB CREATEROLE;这里有几个关键点需要注意WITH LOGIN表示允许该角色登录SUPERUSER赋予所有权限生产环境慎用CREATEDB允许创建数据库CREATEROLE允许创建其他角色实际项目中我通常会根据最小权限原则分配权限。比如只给开发人员特定数据库的权限CREATE USER devuser WITH PASSWORD DevPass123; GRANT CONNECT ON DATABASE projectdb TO devuser; GRANT USAGE ON SCHEMA public TO devuser; GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO devuser;权限管理容易出错的地方是级联权限。比如要撤销权限时需要加上CASCADEREVOKE ALL ON DATABASE projectdb FROM devuser CASCADE;一个实用的技巧是使用\du命令查看用户权限用\dp查看表级权限。最近遇到一个案例某用户无法查询表最后发现是缺少schema的USAGE权限GRANT USAGE ON SCHEMA sales TO reports_user;3. 数据库与Schema操作创建数据库时我建议指定编码和排序规则特别是需要多语言支持时CREATE DATABASE webapp WITH OWNER adminuser ENCODING UTF8 LC_COLLATE en_US.UTF-8 LC_CTYPE en_US.UTF-8 TEMPLATE template0;这里的TEMPLATE template0可以避免继承默认数据库的配置问题。删除数据库时要特别注意PostgreSQL要求没有活跃连接时才能删除SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname old_db; DROP DATABASE old_db;Schema是PostgreSQL的重要特性我习惯为每个功能模块创建独立schemaCREATE SCHEMA payment AUTHORIZATION finance_user; CREATE SCHEMA reporting;迁移数据时经常需要修改schema所有者ALTER SCHEMA public OWNER TO adminuser;查看数据库对象的小技巧-- 列出所有数据库 \l -- 查看当前数据库的所有schema \dn -- 查看某schema下的表 \dt payment.*4. 远程访问安全配置PostgreSQL默认只允许本地连接要启用远程访问需要修改两个关键配置文件。首先找到你的配置文件位置不同版本路径略有差异sudo find / -name postgresql.conf 2/dev/null通常路径类似于/etc/postgresql/15/main/postgresql.conf。用sudo权限编辑该文件sudo nano /etc/postgresql/15/main/postgresql.conf找到并修改以下参数listen_addresses * port 5432 password_encryption scram-sha-256这里的listen_addresses *表示监听所有网络接口如果只需要特定IP访问可以设为192.168.1.100等具体地址。接下来配置客户端认证编辑pg_hba.conf文件sudo nano /etc/postgresql/15/main/pg_hba.conf在文件末尾添加类似这样的规则根据你的安全需求调整# TYPE DATABASE USER ADDRESS METHOD host all adminuser 192.168.1.0/24 scram-sha-256 host webapp devuser 0.0.0.0/0 scram-sha-256这里解释下各字段含义host表示TCP/IP连接webapp限制只能访问特定数据库devuser指定适用用户192.168.1.0/24限制IP范围scram-sha-256是最安全的密码验证方式配置完成后需要重启服务sudo systemctl restart postgresql测试远程连接时我常用psql命令psql -h 服务器IP -U 用户名 -d 数据库名如果连接失败检查防火墙设置sudo ufw allow 5432/tcp安全提示长期暴露数据库端口存在风险建议配合SSH隧道或VPN使用。我曾遇到过因为弱密码导致数据库被入侵的案例因此强烈建议使用复杂密码12位以上含大小写、数字、符号定期轮换密码限制可连接IP范围启用日志审计5. 日常维护与故障排查PostgreSQL的日志文件是排查问题的第一手资料默认配置下日志位于/var/lib/postgresql/15/main/log/查看实时日志sudo tail -f /var/lib/postgresql/15/main/log/postgresql-15-main.log常见问题1连接数不足-- 查看当前连接数 SELECT count(*) FROM pg_stat_activity; -- 修改最大连接数(需要重启) ALTER SYSTEM SET max_connections 200;常见问题2性能瓶颈-- 查看长查询 SELECT pid, now()-query_start as duration, query FROM pg_stat_activity WHERE state active ORDER BY duration DESC; -- 终止问题查询 SELECT pg_cancel_backend(pid);备份策略建议# 基本备份 pg_dump -U username -d dbname -f backup.sql # 压缩备份 pg_dump -U username -d dbname | gzip backup.gz # 定时备份(crontab示例) 0 2 * * * pg_dump -U adminuser -d webapp | gzip /backups/webapp_$(date \%Y\%m\%d).gz恢复数据库时要注意# 先创建空数据库 createdb -U postgres restoredb # 再导入数据 psql -U postgres -d restoredb -f backup.sql定期维护操作-- 更新统计信息 ANALYZE; -- 清理膨胀空间 VACUUM FULL; -- 重建索引 REINDEX DATABASE dbname;6. 可视化工具连接实践虽然命令行足够强大但图形化工具能提升效率。以DBeaver为例连接配置要点主机填写服务器IP端口保持5432数据库填写要连接的具体库名用户名/密码用之前创建的驱动选择PostgreSQLSSL选项根据服务器配置选择连接测试失败的常见原因防火墙未放行端口pg_hba.conf配置错误密码错误服务器监听地址限制一个实用的调试技巧是在服务器本地先用psql测试psql -h 127.0.0.1 -U testuser -d testdb如果本地能连但远程不行基本可以确定是网络或防火墙问题。我在阿里云服务器上配置时还需要在安全组规则中添加5432端口放行。对于生产环境建议配置连接池如PgBouncer能有效管理连接资源。开发环境可以使用adminer.php这种轻量级Web界面。
全流程解析)
