Linux 下 PostgreSQL 安装与常用操作指南

前言

1. 确认系统环境

2. 安装 PostgreSQL

2.1 CentOS / RHEL / RockyLinux

2.2 Ubuntu / Debian

3. 初次使用 PostgreSQL

常用内置命令（\\ 开头）

4. 数据库与用户管理

数据库操作

用户与权限操作

5. 表操作与数据管理

6. 数据导入与导出

7. 高级查询与管理

8. 远程访问配置

总结

附录：PostgreSQL 常用命令速查表

连接数据库

内置快捷命令（\\ 开头）

数据库操作

用户/角色操作

表操作

数据导入导出

高级查询/管理

PostgreSQL 常用数据类型表

数值类型

字符串类型

日期和时间类型

布尔类型

枚举与 JSON 类型

二进制类型

PostgreSQL 用户权限表（角色属性与权限）

对象级权限

视图和函数权限

PostgreSQL 核心文件路径

1. 安装目录（二进制文件与核心程序）

2. 数据目录（数据库文件、日志、配置）

3. 配置文件路径

4. 日志文件路径

5. 服务管理脚本/单元文件

如何查看实际路径？

总结

前言

PostgreSQL 是一款功能强大、开源免费的关系型数据库管理系统，常用于网站、企业应用以及数据分析场景。本文将从 Linux 系统出发，介绍 PostgreSQL 的安装、基本使用以及常用操作，帮助你快速上手。

1. 确认系统环境

在安装 PostgreSQL 前，先确认你的 Linux 发行版：

cat /etc/*release

常见的 Linux 发行版主要分为两类：

• RedHat 系列（CentOS、RHEL、RockyLinux）

• Debian 系列（Ubuntu、Debian）

本文会分别介绍两类系统的安装方法。

2. 安装 PostgreSQL

2.1 CentOS / RHEL / RockyLinux

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm

#如报错没有dnf命令可无视,继续向下执行
sudo dnf -qy module disable postgresql

sudo yum install -y postgresql15 postgresql15-server

sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15

2.2 Ubuntu / Debian

sudo apt update
sudo apt install -y wget ca-certificates
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 -y postgresql-15

sudo systemctl enable postgresql
sudo systemctl start postgresql
systemctl status postgresql

3. 初次使用 PostgreSQL

安装完成后，系统会自动创建一个 postgres 用户和数据库。可以使用以下命令登录：

sudo -i -u postgres
psql

也可以指定用户名和数据库直接登录：

psql -U postgres -d postgres

常用内置命令（\\ 开头）

4. 数据库与用户管理

数据库操作

CREATE DATABASE testdb;   — 创建数据库
DROP DATABASE testdb;     — 删除数据库
SELECT current_database(); — 查看当前数据库

用户与权限操作

CREATE USER testuser WITH PASSWORD '123456';         — 创建用户
CREATE USER adminuser WITH SUPERUSER PASSWORD 'pass'; — 创建超级用户
ALTER USER testuser WITH PASSWORD 'newpassword';     — 修改密码
DROP USER testuser;                                 — 删除用户
GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser; — 授权
REVOKE ALL PRIVILEGES ON DATABASE testdb FROM testuser; — 撤销权限

5. 表操作与数据管理

— 创建表
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
​
— 插入数据
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
​
— 查询数据
SELECT * FROM users;
SELECT name, email FROM users WHERE id = 1;
​
— 更新数据
UPDATE users SET email='alice123@example.com' WHERE id=1;
​
— 删除数据
DELETE FROM users WHERE id=1;
​
— 删除表
DROP TABLE users;
​
— 清空表
TRUNCATE TABLE users;

6. 数据导入与导出

# 导出数据库
pg_dump -U postgres -d testdb -f testdb.sql
​
# 导入数据库
psql -U postgres -d testdb -f testdb.sql
​
# 导出并压缩
pg_dump testdb | gzip > testdb.sql.gz
​
# 导入压缩文件
gunzip -c testdb.sql.gz | psql -U postgres -d testdb

7. 高级查询与管理

— 查看表大小
SELECT pg_size_pretty(pg_total_relation_size('users'));
​
— 查看数据库大小
SELECT pg_size_pretty(pg_database_size('testdb'));
​
— 查看当前连接
SELECT * FROM pg_stat_activity;
​
— 查看表及索引信息
\\d+ users

8. 远程访问配置

如果需要远程连接 PostgreSQL，需要修改配置文件：

编辑 postgresql.conf：

#60行,取消注释
listen_addresses = '*'

编辑 pg_hba.conf，添加远程访问权限：

#87行
host   all     all     0.0.0.0/0   md5

重启 PostgreSQL 服务：

sudo systemctl restart postgresql-15   # CentOS/RHEL
sudo systemctl restart postgresql     # Ubuntu/Debian

创建一个测试用户

总结

本文介绍了 PostgreSQL 在 Linux 系统下的安装方法以及最常用的操作，包括：

• 数据库和用户管理

• 表和数据操作

• 数据导入导出

• 远程访问配置

掌握这些基础操作后，你就可以在 Linux 下快速部署和管理 PostgreSQL 数据库了。

附录：PostgreSQL 常用命令速查表

连接数据库

内置快捷命令（\\ 开头）

数据库操作

用户/角色操作

表操作

数据导入导出

高级查询/管理

PostgreSQL 常用数据类型表

数值类型

字符串类型

日期和时间类型

布尔类型

枚举与 JSON 类型

二进制类型

PostgreSQL 用户权限表（角色属性与权限）

对象级权限

视图和函数权限

PostgreSQL 核心文件路径

1. 安装目录（二进制文件与核心程序）

包含 bin、lib、share 等子目录，如 psql、pg_dump、postgres 等可执行文件位于 bin/ 下。

2. 数据目录（数据库文件、日志、配置）

包含：

• postgresql.conf：主配置文件

• pg_hba.conf：客户端认证配置文件

• pg_wal/：WAL 日志目录

• base/：实际数据库文件存储位置

3. 配置文件路径

4. 日志文件路径

日志文件通常命名为 postgresql-15-main.log 或类似。

5. 服务管理脚本/单元文件

如何查看实际路径？

你可以在 PostgreSQL 中使用以下 SQL 查询关键路径：

sql

— 查看数据目录
SELECT setting FROM pg_settings WHERE name = 'data_directory';
​
— 查看配置文件路径
SELECT setting FROM pg_settings WHERE name = 'config_file';
​
— 查看 HBA 文件路径
SELECT setting FROM pg_settings WHERE name = 'hba_file';

总结

这些路径是 PostgreSQL 安装后的典型路径，具体版本号（如 15）可根据实际安装版本调整。