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 postgresqlWindows系统安装从官网下载安装包运行安装向导记住设置的密码安装完成后可以在开始菜单找到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 restart2.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 products3.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;导出数据到CSVCOPY 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 mydb5.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;5.3 常见问题解决连接数不足修改max_connections参数后需要重启服务。性能下降定期运行VACUUM ANALYZE可以优化数据库性能。忘记密码可以修改pg_hba.conf临时设置为trust认证登录后再修改密码。记得有一次生产环境数据库突然变慢后来发现是autovacuum没有及时运行导致表膨胀手动执行VACUUM后性能立即恢复了。