PostgreSQL 入门实战（一）-- 从零搭建到数据操作-育师

1. PostgreSQL简介与安装准备

PostgreSQL是一款功能强大的开源关系型数据库，被广泛认为是"开源界的Oracle"。它支持SQL标准并提供丰富的企业级特性，包括复杂查询、事务完整性、多版本并发控制等。我第一次接触PostgreSQL是在2013年为一个电商项目选型数据库时，当时就被它的稳定性和扩展性所吸引。

1.1 为什么选择PostgreSQL

相比其他数据库，PostgreSQL有几个显著优势：

完全开源：采用自由的BSD许可证，可以自由使用和修改
功能丰富：支持JSON、GIS地理信息、全文搜索等高级特性
高度可扩展：可以自定义数据类型、函数、操作符等
稳定性强：适合处理高并发和大量数据的场景

我曾在生产环境用PostgreSQL处理过日增百万级数据的场景，即使在数据量暴涨时性能依然稳定。

1.2 安装前准备

根据你的操作系统选择安装方式：

Linux系统安装

对于Ubuntu/Debian系统：

sudo apt update sudo apt install postgresql postgresql-contrib

对于CentOS/RHEL系统：

sudo yum install postgresql-server postgresql-contrib sudo postgresql-setup initdb sudo systemctl start postgresql

Windows系统安装

从官网下载安装包
运行安装向导，记住设置的密码
安装完成后可以在开始菜单找到pgAdmin图形工具

macOS系统安装

使用Homebrew安装最为简单：

brew install postgresql brew services start postgresql

安装完成后，默认会创建一个名为postgres的系统用户和数据库超级用户。我第一次安装时就犯了个错误，直接用root操作导致权限问题，后来发现应该切换到postgres用户操作。

2. 初始配置与数据库创建

2.1 配置访问权限

安装后首先需要配置允许远程或本地连接。编辑配置文件：

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

找到listen_addresses行，修改为：

listen_addresses = '*' # 允许所有IP连接

然后修改客户端认证文件：

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

在文件末尾添加：

# 允许所有本地连接使用密码认证 local all all md5 # 允许所有远程连接使用密码认证 host all all 0.0.0.0/0 md5

重启服务使配置生效：

sudo service postgresql restart

2.2 创建第一个数据库

切换到postgres用户：

sudo -i -u postgres

创建新用户和数据库：

CREATE USER myuser WITH PASSWORD 'mypassword'; CREATE DATABASE mydb OWNER myuser; GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

测试连接：

psql -U myuser -d mydb -h 127.0.0.1

如果连接成功，你会看到提示符变成mydb=>。我在第一次创建用户时忘记赋权，导致无法操作数据库，后来通过GRANT命令解决了这个问题。

3. 基础表操作与数据管理

3.1 创建和管理表

连接数据库后，我们来创建第一个表：

CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, price DECIMAL(10,2) CHECK (price > 0), stock INT DEFAULT 0, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP );

这里有几个实用技巧：

SERIAL是自增整数，适合做主键
CHECK约束确保价格为正数
DEFAULT设置默认值
时间戳带时区是个好习惯

查看表结构：

\d products

3.2 数据增删改查

插入数据：

INSERT INTO products (name, price, stock) VALUES ('笔记本电脑', 5999.99, 10), ('智能手机', 2999.50, 20);

查询数据：

-- 基本查询 SELECT * FROM products; -- 条件查询 SELECT name, price FROM products WHERE price > 3000; -- 排序和限制 SELECT * FROM products ORDER BY price DESC LIMIT 1;

更新数据：

UPDATE products SET stock = stock - 1 WHERE id = 1;

删除数据：

DELETE FROM products WHERE stock = 0;

我曾经在一个促销活动中因为没有加WHERE条件，误删了整个产品表，幸好有备份。所以提醒大家：执行DELETE前一定要再三确认WHERE条件！

4. 高级数据操作技巧

4.1 事务处理

PostgreSQL支持完整的事务特性：

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; UPDATE accounts SET balance = balance + 100 WHERE user_id = 2; COMMIT;

如果中间出错可以回滚：

BEGIN; -- 一些操作 SAVEPOINT my_savepoint; -- 更多操作 ROLLBACK TO my_savepoint; -- 继续操作 COMMIT;

4.2 批量导入导出数据

从CSV导入数据：

COPY products(name, price, stock) FROM '/path/to/products.csv' DELIMITER ',' CSV HEADER;

导出数据到CSV：

COPY products TO '/path/to/export.csv' WITH CSV HEADER;

我曾经用COPY命令在10秒内导入了50万条数据，比用INSERT快太多了。

4.3 使用索引提高查询速度

为常用查询字段创建索引：

CREATE INDEX idx_products_price ON products(price);

查看查询计划分析性能：

EXPLAIN ANALYZE SELECT * FROM products WHERE price > 1000;

注意：索引不是越多越好，每个索引都会增加写入时的开销。我曾经在一个频繁更新的表上建了太多索引，导致写入性能下降了70%。

5. 实用维护与管理

5.1 备份与恢复

使用pg_dump进行逻辑备份：

pg_dump -U myuser -d mydb -f backup.sql

恢复备份：

psql -U myuser -d mydb -f backup.sql

对于大型数据库，可以使用并行备份：

pg_dump -j 4 -F d -f /backup/dir mydb

5.2 性能监控

查看当前活动连接：

SELECT * FROM pg_stat_activity;

查看表空间使用情况：

SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename))) FROM pg_tables ORDER BY pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)) DESC;