Ubuntu 中安装 PostgreSQL 及常规操作指南
目录
一、安装 PostgreSQL
最新版本安装（推荐）
安装特定版本（如 14）
二、基本服务管理
三、连接数据库
四、常规数据库操作
1. 用户与权限管理
2. 数据库管理
3. 表操作
4. 数据操作
五、常用 psql 元命令
六、备份与恢复
1. 使用 pg_dump
2. 恢复数据库
七、配置远程访问
八、性能监控
九、扩展管理
十、日志管理
十一、版本升级
常用管理工具推荐
连接示例
一、安装 PostgreSQL
最新版本安装（推荐）
# 添加官方源并安装
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
sudo apt install postgresql postgresql-contrib
安装特定版本（如 14）
sudo apt install postgresql-14 postgresql-client-14
二、基本服务管理
| 命令 | 说明 |
|---|---|
sudo service postgresql start | 启动服务 |
sudo service postgresql stop | 停止服务 |
sudo service postgresql restart | 重启服务 |
sudo service postgresql status | 查看状态 |
sudo systemctl enable postgresql | 设置开机自启 |
三、连接数据库
# 切换到 postgres 系统用户
sudo -i -u postgres# 连接默认数据库
psql # 或指定数据库： psql mydb# 直接连接（无需切换用户）
sudo -u postgres psql
四、常规数据库操作
1. 用户与权限管理
-- 创建用户
CREATE USER devuser WITH PASSWORD 'securepass';-- 修改密码
ALTER USER devuser PASSWORD 'newpass';-- 授予数据库权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO devuser;-- 删除用户
DROP USER IF EXISTS olduser;
2. 数据库管理
-- 创建数据库
CREATE DATABASE mydb OWNER devuser ENCODING 'UTF8';-- 列出所有数据库
\l-- 切换数据库
\c mydb-- 删除数据库
DROP DATABASE olddb;
3. 表操作
-- 创建表
CREATE TABLE users (id SERIAL PRIMARY KEY,name VARCHAR(50) NOT NULL,email VARCHAR(100) UNIQUE,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);-- 查看所有表
\dt-- 查看表结构
\d users-- 删除表
DROP TABLE users;
4. 数据操作
-- 插入数据
INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');-- 查询数据
SELECT * FROM users WHERE name LIKE 'A%';-- 更新数据
UPDATE users SET email = 'new@example.com' WHERE id = 1;-- 删除数据
DELETE FROM users WHERE id = 1;
五、常用 psql 元命令
| 命令 | 说明 |
|---|---|
\? | 查看帮助 |
\l | 列出数据库 |
\c dbname | 切换数据库 |
\dt | 列出表 |
\d table | 查看表结构 |
\du | 列出用户 |
\x | 切换扩展显示模式 |
\timing | 开启/关闭执行时间 |
\q | 退出 psql |
\i filename.sql | 执行 SQL 文件 |
\e | 在编辑器中编辑命令 |
\conninfo | 显示连接信息 |
六、备份与恢复
1. 使用 pg_dump
# 备份单个数据库
sudo -u postgres pg_dump mydb > mydb_backup.sql# 备份所有数据库
sudo -u postgres pg_dumpall > all_backup.sql# 压缩备份
sudo -u postgres pg_dump mydb | gzip > mydb_backup.gz
2. 恢复数据库
# 恢复 SQL 备份
sudo -u postgres psql -d mydb -f mydb_backup.sql# 恢复压缩备份
gunzip -c mydb_backup.gz | sudo -u postgres psql mydb
七、配置远程访问
-
修改配置文件：
sudo nano /etc/postgresql/14/main/postgresql.conf取消注释并修改：
listen_addresses = '*' -
编辑访问规则：
sudo nano /etc/postgresql/14/main/pg_hba.conf添加行：
host all all 0.0.0.0/0 md5 -
重启服务：
sudo service postgresql restart
八、性能监控
-- 查看活动连接
SELECT * FROM pg_stat_activity;-- 查看锁信息
SELECT * FROM pg_locks;-- 查看表大小
SELECT pg_size_pretty(pg_total_relation_size('users'));-- 查看索引使用情况
SELECT * FROM pg_stat_user_indexes;
九、扩展管理
-- 安装扩展（如 PostGIS）
CREATE EXTENSION postgis;-- 查看已安装扩展
\dx-- 卸载扩展
DROP EXTENSION postgis;
十、日志管理
# 查看错误日志位置
sudo -u postgres psql -c "SHOW log_directory;"# 常用日志配置 (/etc/postgresql/14/main/postgresql.conf)
log_destination = 'stderr'
logging_collector = on
log_line_prefix = '%m [%p] %q%u@%d '
log_statement = 'all' # 记录所有SQL
十一、版本升级
# 安装新版本（如 15）
sudo apt install postgresql-15# 停止服务
sudo service postgresql stop# 升级数据
sudo pg_upgradecluster 14 main 15# 删除旧版本（确认新版本正常后）
sudo pg_dropcluster 14 main
常用管理工具推荐
- pgAdmin (GUI):
sudo apt install pgadmin4 - 命令行工具:
sudo apt install postgresql-client-14
连接示例
# 本地连接
psql -U devuser -d mydb# 远程连接
psql -h 192.168.1.10 -p 5432 -U devuser -d mydb
提示：生产环境建议：
- 定期备份 (
cron + pg_dump)- 启用 WAL 归档
- 配置定期 vacuum
- 使用连接池 (pgBouncer)