Ubuntu中安装PostgreSQL步骤及常规操作指南

本文主要讲解关于Ubuntu中安装PostgreSQL步骤及常规操作指南相关内容，由优网导航（www.uonce.com）提供，欢迎关注收藏本站！

一、安装 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

七、配置远程访问

1. 修改配置文件：

   sudo nano /etc/postgresql/14/main/postgresql.conf
   

   取消注释并修改：

   listen_addresses = '*'
   

2. 编辑访问规则：

   sudo nano /etc/postgresql/14/main/pg_hba.conf
   

   添加行：

   host    all             all             0.0.0.0/0               md5
   

3. 重启服务：

   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

常用管理工具推荐

1. pgAdmin (GUI):

   sudo apt install pgadmin4
   

2. 命令行工具:

   sudo apt install postgresql-client-14
   

连接示例

# 本地连接
psql -U devuser -d mydb

# 远程连接
psql -h 192.168.1.10 -p 5432 -U devuser -d mydb

提示：生产环境建议：

1. 定期备份 (cron + pg_dump)
2. 启用 WAL 归档
3. 配置定期 vacuum
4. 使用连接池 (pgBouncer)

总结 

到此这篇关于Ubuntu中安装PostgreSQL步骤及常规操作指南的文章就介绍到这了,更多相关Ubuntu安装PostgreSQL内容请搜索优网导航以前的文章或继续浏览下面的相关文章希望大家以后多多支持优网导航！

您可能感兴趣的文章:

• 在Ubuntu中安装Postgresql数据库的步骤详解
• Ubuntu PostgreSQL安装和配置的介绍
• 详解如何在Ubuntu 18.04上安装和使用PostgreSQL
• Ubuntu 22.04 服务器安装部署(nginx+postgresql)