搞数据库这些年最常被问到的不是那些花里胡哨的高可用方案反而是“PG到底怎么用才顺手”。PostgreSQLPG这个数据库上手容易但想用得舒服、少踩坑确实有不少门道。我平时工作里几乎天天跟PG打交道从部署到日常查询从备份恢复到性能排查积累了不少可以直接照搬的经验。这篇文章我就把PG日常应用中最核心、最常用的那套东西梳理一遍全是实操向的内容适合刚把PG装起来准备干活的人也适合用了一阵子但总觉得差点意思的开发或运维朋友。看完不敢说让你变成专家但日常开发、维护、面试聊起PG心里绝对更有底。1. 环境搭建与日常连接先把手上的工具磨利索1.1 安装部署的几个关键选择很多朋友第一次接触PG第一反应是去官网下载安装包其实大可不必。在咱们日常使用的Linux服务器上直接用发行版自带的软件源安装是最省事的。以CentOS和Ubuntu为例# Ubuntu / Debian系 sudo apt install postgresql postgresql-contrib # CentOS / RHEL系 sudo yum install postgresql-server postgresql-contribRedHat系装完以后多一步初始化数据库的命令postgresql-setup --initdb。这一步很容易被忽略初始化没做后面启动服务肯定会报错找不到数据目录。Ubuntu系倒是省心装完自动帮你初始化好了。这里我要多说一句选版本的看法。PG的各个大版本新版本功能确实多比如更牛的并行查询、更好的分区表支持。但我个人建议日常应用求稳的话别追最新的大版本选已经发布超过半年的版本比较妥当。我见过不少同事一升级就遇到扩展插件不支持的情况尤其是一些第三方插件跟新版PG的适配经常有滞后。1.2 客户端连接与常用管理命令数据库装好了怎么连上去干活是第一步。PG默认情况下的认证配置比较保守本地用psql连接时它默认用peer认证就是要求当前操作系统用户和数据库用户名一致。所以你会看到很多教程让你先su - postgres再用psql进去。这个设计从安全角度看没毛病但开发环境这么搞太憋屈了咱还是配成密码登录方便。需要改两个文件都在数据目录里。一个是pg_hba.conf控制谁能连、怎么认证另一个是postgresql.conf控制监听地址和端口。把listen_addresses改成*在pg_hba.conf里加一行host all all 0.0.0.0/0 scram-sha-256改完记得重启服务。我刚开始用PG的时候不知道要重启改完配置怎么连都报错还以为是防火墙的问题排查了半天最后才发现是配置没生效这个教训记忆深刻。连接命令方面日常最常用的其实就是psql参数也就那么几个psql -h 192.168.1.10 -p 5432 -U myuser -d mydb-h指定主机-p指定端口-U指定用户-d指定数据库。还有个小技巧连接以后想看表结构用\d 表名想看所有数据库列表用\l想切换数据库用\c 数据库名。这些反斜杠命令是psql的精髓用熟了效率能提升不少。2. 日常增删改查的精进之路从能用到好用2.1 建表与数据类型选择的智慧PG的数据类型丰富到让人眼花缭乱但日常应用其实用不到那么多。整数用integer或bigint带小数用numeric字符串用varchar或text时间用timestamp这几个是最常用的。我有一个原则分享给大家就是能用text就别用varchar。PG的text和varchar在性能上其实没差别但text不用纠结长度限制后期需求变了也不用改表结构。这个观点在PG圈子里有争议但我个人实践下来text真的省心不少。建表的时候主键怎么设计是个大问题。我见过不少人习惯用自增整数做主键PG里用serial类型或者identity语法。但我要提示一下如果以后考虑做分布式扩展或者数据合并用UUID或者雪花ID做主键更合适。别觉得这是过度设计我身边真有同事因为用了自增ID后面做数据迁移的时候ID冲突搞得焦头烂额。当然如果确定就是单库单表自增ID也没问题。2.2 数据写入与更新的高级特性日常写入数据大家都会用INSERT但PG有几个特性是真正用了就回不去的。首先就是ON CONFLICT语法也就是常说的“冲突则更新”。比如我们要往用户表里插入数据但用户ID已经存在我们希望更新某些字段而不是报错INSERT INTO users (id, name, email) VALUES (1, 张三, zhangsanexample.com) ON CONFLICT (id) DO UPDATE SET name EXCLUDED.name, email EXCLUDED.email;这个语法最妙的地方在于EXCLUDED关键字它代表本次想插入但发生了冲突的那行数据。这在做数据同步、批量导入的时候特别好用可以说是日常应用中使用频率最高的一条命令。再说批量插入很多人图省事一条一条执行INSERT数据量小倒是无所谓但上千条的时候就明显感觉慢。正确姿势是用INSERT INTO ... VALUES (...), (...), (...)一次拼多条或者用COPY命令从文件导入。COPY是PG导入大批量数据的最快方式我测试过百万级别的数据用COPY几分钟就能导完用普通INSERT可能要几十分钟甚至更久。2.3 查询优化EXPLAIN与索引的正确玩法查询是数据库日常应用的重头戏但很多人对索引的使用停留在“给查询字段加索引”这个朴素认知上。实际上索引加不对地方反而拖累写入性能还占磁盘空间。日常开发中最典型的场景就是模糊查询。很多人在大字段上做LIKE %关键词%这种查询用普通B-tree索引是帮不上忙的照样全表扫描。如果你们项目确实需要频繁做这种模糊搜索可以考虑PG的pg_trgm扩展它提供GIN索引来加速模糊查询CREATE EXTENSION pg_trgm; CREATE INDEX idx_users_name_trgm ON users USING gin (name gin_trgm_ops);这个扩展在搜索名字、描述类字段时效果拔群我专门做过对比测试加了GIN索引以后模糊查询速度提升了不止一个数量级。还有联合索引的问题。日常查询经常用多个条件比如WHERE status active AND created_at 2024-01-01。这时候建索引要考虑字段顺序原则是等值查询的字段放前面范围查询的字段放后面。PG的优化器虽然厉害但索引用不好性能差距依然是天壤之别。遇到查询慢千万别瞎猜一定要看EXPLAIN ANALYZE的输出。这个命令会真实执行查询告诉你每一步花了多少时间、扫描了多少行。我排查慢查询的标准流程是先开EXPLAIN ANALYZE看执行计划确认是全表扫描还是索引扫描再看有没有排序、有没有嵌套循环没走哈希连接。80%的慢查询问题看执行计划一眼就能定位到原因。3. 备份恢复与数据迁移保命的日常操作3.1 逻辑备份恢复pg_dump与pg_restore日常工作中最常用的备份工具就是pg_dump。它是逻辑备份导出的是SQL语句或者自定义格式的数据文件。基本用法都不难# 备份单个数据库 pg_dump -h localhost -U myuser -d mydb -F c -f mydb.backup # 恢复 pg_restore -h localhost -U myuser -d mydb -c -j 4 mydb.backup-F c是自定义格式比纯SQL格式好在恢复的时候可以指定-j并行恢复多个表同时恢复速度能快很多。-c参数表示在导入前先删除已存在的数据库对象这个要谨慎使用注意会覆盖掉目标库里的同对象。我吃过一次亏当时要给测试库恢复数据忘了加-c结果恢复的时候报错说表已存在。后来学乖了每次恢复前都会检查一下目标库里有没有同名对象要么先清空要么用--clean参数。还有一种情况大家容易忽视就是数据库版本升级的时候大版本升级建议用pg_dumpall导出全部数据再到新版本里导入。不过这个操作在跨大版本升级时要注意某些老的数据类型在新版本里可能不兼容了需要提前检查。3.2 日常备份策略的制定说实话真正让我对备份这件事重视起来的是亲眼见过同行因为没做备份数据库磁盘坏了整个业务数据全丢的场面。那次以后我给所有项目定的规矩就是至少每天做一次逻辑备份保留最近7天的备份文件同时每周做一次全量物理备份。物理备份直接拷贝数据目录比逻辑备份可靠得多恢复起来也快但缺点是无法做到按表恢复。两套方案配合使用日常应用基本能覆盖绝大多数故障场景。备份文件要注意存储位置别放在数据库服务器本地。万一服务器硬盘坏掉数据目录和备份文件一起没了那就哭都来不及了。我现在习惯性的操作是备份完以后自动用rsync同步到另一台机器或者对象存储上多一个副本多一分安心。3.3 数据迁移的常见坑数据迁移是日常应用里最考验细心程度的活。不同数据库之间的迁移别指望一条命令搞定我总结下来PG迁移到PG或者从MySQL迁到PG最稳的路子是先用工具把结构转过来再处理数据最后校验数据一致性。从MySQL迁到PG的话很多类型需要手动调整比如MySQL的TINYINT(1)对应PG的BOOLEANMySQL的DATETIME对应PG的TIMESTAMP这些映射关系不处理好数据导过去看着别扭用起来也容易出问题。另外MySQL的AUTO_INCREMENT要对应改成PG的SERIAL或者IDENTITY。说实话这块没有一键搞定的工具用pgloader能省不少事但它比较挑场景复杂的库结构还是得自己写脚本处理。我自己常用的一套迁移流程是在源库导出数据为CSV格式再在目标PG库里用COPY命令导入。这个流程看着原始但对大批量数据特别有效而且中途出问题容易定位是哪些数据有问题。比起依赖图形化工具一把梭这种方式反而更可控。4. 日常维护与故障排查稳住数据库的底线4.1 连接数与性能参数的合理调配PG日常维护中最常遇到的性能问题不是CPU不够也不是磁盘太慢而是连接数打满。PG默认最大连接数是100这在开发环境够用但生产环境稍微有点并发就顶不住了。连接数往上调很简单在postgresql.conf里改max_connections重启就行。但这里有个坑你有没有想过连接数从100调到500PG背后的进程模型决定了每个连接都要消耗一定内存连接数太高会吃光内存反而导致性能下降。调连接数没有标准答案我一般按照“连接数乘以每个连接的内存开销”来估算。每个PG连接大约消耗几MB到十几MB的内存你可以按10MB来粗估。假设机器内存32GB留一半给共享缓冲区和系统那么实际可用的连接内存大概是16GB也就是说连接数设置400到500是最多的上限了。超过这个数还嫌连接不够用就该考虑上连接池工具像PgBouncer或者Pgbouncer变体把前端的几千个连接复用成一两百个后端连接这才是正解。4.2 死锁与锁等待的排查方法数据库锁和死锁这是日常应用里绕不开的话题。PG的锁机制不像MySQL那么复杂但排查起来依然需要技巧。死锁的场景通常是两个事务互相等对方持有的锁PG检测到死锁以后会自动回滚其中一个事务所以你会在应用日志里看到类似deadlock detected的错误。遇到死锁别慌这是数据库的自我保护机制在起作用关键是要找出死锁发生的原因从根本上避免。我排查死锁的经验是先开数据库日志的锁监控在postgresql.conf里设置log_lock_waits on deadlock_timeout 1s这样当会话等待锁超过1秒就会记录到日志死锁发生时会打印出双方持有的锁和等待的锁根据日志里的SQL语句和表名就能反推出是哪两个业务流程在互相竞争。日常开发中最常见的死锁原因就是业务代码里多个事务加锁的顺序不一致。比如一个场景是先更新订单表再更新用户表另一个场景是先更新用户表再更新订单表两个事务并发执行就很容易撞上死锁。解决思路就是统一加锁顺序或者把涉及多个表的操作放到一个事务里减少锁竞争时间。4.3 VACUUM与表膨胀的日常管理PG的MVCC机制带来了强大的并发控制能力但也带来了一个让新手头疼的问题——表膨胀。删除的数据不立即释放磁盘空间更新操作也会产生旧版本数据这些都需要依靠VACUUM来清理。PG有个自动autovacuum进程默认开启但有时候它跑得不够快特别是大批量删除或者更新以后表膨胀严重查询性能直线下降。日常维护中我是这样管理表膨胀的首先监控表的实际大小和统计信息里的大小差距差距过大说明膨胀严重了。然后手动执行VACUUM (VERBOSE, ANALYZE) 表名;VERBOSE会打印详细的清理日志ANALYZE会更新统计信息这两个参数组合是日常维护中最常用的。如果膨胀特别严重普通的VACUUM也没法回收磁盘空间需要使用VACUUM FULL但这会持有表级的排他锁业务高峰期千万别跑否则直接阻塞所有对该表的读写造成线上事故。4.4 常见问题速查连接失败、端口被占、认证错误日常应用里PG最让人抓狂的报错就是连接问题我整理了一个速查表遇到麻烦可以直接对照。报错信息可能原因常见解决方式could not connect to server: Connection refused服务没启动或端口不对检查pg_isready和服务器端口监听状态FATAL: password authentication failed密码错误或认证方式不对确认密码检查pg_hba.conf的认证方法FATAL: database xxx does not exist库名打错了或没创建库用\l查看已有库FATAL: sorry, too many clients already连接数达到上限调大max_connections或使用连接池could not translate host nameDNS解析失败检查pg_hba.conf里是否有DNS反向解析的配置还有一个容易被忽略的问题就是pg_hba.conf的配置顺序。这个文件是自上而下匹配的第一条匹配到的规则生效。如果你在前面写了一条拒绝所有来源的规则后面的放行规则就永远不生效。我遇到过好几次这种问题配置看着没问题但就是连不上最后才发现是规则顺序搞反了。5. 日常性能调优的经验心得慢查询分析与配置优化5.1 慢查询日志的开启与分析很多朋友问我开发环境跑得好好的怎么一到生产环境就慢得像蜗牛这个问题很大程度是因为开发环境数据量小SQL写得好不好根本看不出来。到了生产环境数据一多问题全冒出来了。所以开启慢查询日志是日常维护的第一步。在postgresql.conf里设置log_min_duration_statement 1000超过1000毫秒的SQL会被记录到日志里。然后定期分析日志里哪些SQL频繁慢这些就是需要优化的重点。我再推荐一个思路就是把日志收集到一张表里定期用SQL统计SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;pg_stat_statements是PG自带的一个扩展安装开启以后会自动统计SQL的执行次数、总耗时、平均耗时等指标是日常定位慢查询的一把利器。我每次接手一个新的PG项目第一件事就是把pg_stat_statements打开看看哪些SQL是真正的性能杀手。5.2 内存与存储参数的合理调整PG的配置项多如牛毛但日常应用真正需要调的也就那么几个。最重要的就是shared_buffersPG的共享缓冲区一般建议设置为物理内存的15%到25%。比如服务器有32GB内存设置到8GB就比较合理。另一个是effective_cache_size这个参数是告诉查询优化器操作系统和数据库总共可以提供多少缓存设置得太小优化器会偏向选择索引扫描而放弃顺序扫描设置得太大优化器又会过于激进。一般建议设置为物理内存的50%到75%。这两个参数调整完以后我记得有一回帮客户优化一个报表查询原来一条统计SQL要跑2分钟调整完shared_buffers和effective_cache_size同样的SQL直接降到30秒以内。当然这不是说所有慢查询都靠调参就能解决但配置合理是基础把地基打牢了SQL本身的问题才能暴露出来才能进一步去优化。5.3 WAL日志与归档的日常关注点WALWrite-Ahead Logging是PG保证数据安全的核心机制所有数据修改先写WAL再写数据文件。日常维护中最常遇到的问题是WAL日志占用磁盘空间过大。很多人奇怪明明没多少数据为什么WAL目录能吃掉几十GB磁盘这通常是因为wal_keep_size设置过大或者归档失败导致日志堆积。我建议是归档配置要开启但归档命令的容错性一定要做好。archive_command里如果返回非零退出码会导致归档失败WAL日志就全堆积在pg_wal目录下。我见过一个生产事故归档目标是NASNAS磁盘写满导致归档一直失败WAL目录直接爆掉数据库停止服务。排查思路很简单就是看WAL目录大小和归档日志有没有持续报错。日常巡检我习惯性地检查几个关键指标WAL目录大小、连接数使用率、慢查询数量、磁盘空间剩余。这些指标可以通过简单的脚本监控一旦超过阈值就告警。这套体系建立好了数据库稳定运行就有了最基本的保障。6. 面试与技能进阶PG知识体系的日常积累6.1 日常应用中的高频考点PG用久了跟同行交流、面试新人都会发现有些知识点是绕不开的高频考点。比如MVCC的实现原理、VACUUM的作用机制、事务隔离级别的区别与联系、EXPLAIN怎么读、索引类型怎么选。我发现一个有意思的现象很多人工作里天天用PG但这些基础知识反而比较薄弱一被追问原理就露馅。所以我建议日常使用PG的时候别只停留在“能跑就行”的层面遇到一个报错、一个参数多问一句“为什么”。比如为什么PG的默认隔离级别是Read Committed而MySQL默认是Repeatable Read这两个隔离级别在并发下行为有何不同把这些为什么搞清楚了用起PG来会更得心应手面试时聊起来也有底气得多。6.2 从日常应用到核心原理的进阶路径说实话我刚用PG的前两年也只停留在写SQL、调备份的水平。真正让我对PG的理解上一个台阶的是开始阅读官方文档和源码分析相关的文章。PG的官方文档写得非常详尽而且有中文翻译这是很多人没有充分利用起来的宝藏。我的建议是日常应用PG的同时给自己定一个小目标每两周搞懂一个底层原理。比如这周研究PG的进程结构下周研究MVCC的可见性判断规则再下周研究查询优化器的代价模型。慢慢积累下来一年不到你就能从“会用PG”变成“懂PG”。到那时候不管是日常排查问题还是别人问你PG相关的面试题你都会有一种游刃有余的感觉。这个路径我也还在走但回头来看确实是提升PG水平性价比最高的一条路。