PostgreSQL运维常用命令梳理分享

PostgreSQL是一款功能非常强大的开源关系型数据库，它支持哈希索引、反向索引、部分索引、Expression 索引、GiST、GIN等多种索引模式。本文主要整理总结了一些实用SQL，方便大家可以高效利用PostgreSQL。

查看版本

show server_version;selectversion();SELECT * FROM pg_catalog.pg_settings WHEREname = 'server_version'; #信息更全

查看数据库的连接数量​​​​​​​

select datid,datname,pid,usename,state,client_addr,query from pg_stat_activity; #显示更详细SELECTCOUNT(*) FROM pg_stat_activity; #汇总出数量show max_connections; #查看最大连接数

查询当前的用户​​​​​​​

select*fromcurrent_user;SELECTSESSION_USER;select*

查看数据库运行的时间​​​​​​​

select pg_postmaster_start_time();SELECT age(now(), pg_postmaster_start_time()) AS uptime;#汇总出时间

查看当前用户有多少个表​​​​​​​

SELECT tablenameFROM pg_catalog.pg_tablesWHERE schemaname !='pg_catalog'AND schemaname !='information_schema';
SELECT tablenameFROM pg_catalog.pg_tablesWHERE schemaname ='public'; #查看特定模式的表

表空间管理​​​​​​​

SELECT pg_tablespace.spcname,       pg_size_pretty(pg_tablespace_size(pg_tablespace.oid)) AS sizeFROM pg_tablespace;select pg_size_pretty(pg_tablespace_size('pg_global'));#查询表空间大小createtablespace test owner test location '/pgdb/data/test'; #创建表空间，/pgdb/data/test路径得提前建好drop tablespace test; #删除表空间

查看所有的schema

select*from information_schema.schemata; #信息更全

查看数据库大小​​​​​​​

select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS total_size from pg_database;SELECT

查看锁信息

SELECT*FROM pg_locks;

查看表结构​​​​​​​

SELECT    column_name,    data_type,    character_maximum_length,    is_nullable,    column_default FROM    information_schema.COLUMNS WHERE    TABLE_NAME ='students';

查看是否开启归档日志​​​​​​​

show archive_mode;show archive_command ;

用户管理​​​​​​​

createuser test1  with password '123456';#创建用户createuser superwith password '123456' superuser;#创建超级管理员create schema test1  authorization  test1;#创建和用户同名的 schemagrantallon schema test1  to test1;#将 schema 的所有权限给用户grant usage on schema test1  to test;#将schema 的所有权限给另外一个用户ALTERTABLE my_table SET SCHEMA test1;修改schemaalteruser superwith password '123123';#\password  super也可以进行修改postgres=# alteruser super  with nologin; #禁止用户登录drop owned bysuper;#删除用户DROPUSER test;#删除用户

角色管理​​​​​​​

create role role2;#创建角色drop role role1; #删除角色

数据库管理​​​​​​​

CREATE DATABASE test; #创建DROP DATABASE test; #删除 \c test #切换数据库 \ds #查看当前数据库序列

表管理​​​​​​​

ANALYZE test; #分析表altertable test add c3 int; #新增字段altertable test drop c3 ;  #删除字段altertable test altercolumn c3 type varchar(10); #更改字段\x #列模式显示每个字段

索引管理​​​​​​​

REINDEX TABLE tablename;#重建索引 \di #列出所有索引SELECT t.relname AS table_name,  i.relname AS index_name,  string_agg(a.attname, ',') AS column_name FROM pg_class t,  pg_class i,  pg_index ix,  pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND a.attnum =ANY(ix.indkey) AND t.relkind ='r'AND t.relname NOTLIKE'pg_%'GROUPBY  t.relname, i.relnameORDERBY  t.relname, i.relname;

检查表的大小

SELECT pg_size_pretty(pg_total_relation_size('tablename'));

数据库启停管理​​​​​​​

[postgres@localhost ~]$ pg_ctl stop #停止[postgres@localhost ~]$ pg_ctl start #启动

数据库登录​​​​​​​

[postgres@localhost ~]$psql #未开启密码登录验证可以用此登录[postgres@localhost ~]$ psql -U postgres -p 5785 -d postgres -h 192.168.59.138

查看所有的 function​​​​​​​

 \dfSELECT proname,   proargtypes,   prosrcFROM pg_proc;

会话管理​​​​​​​

\conninfo #查看当前会话连接信息\x #因为表字段非常多，通过终端查询会重叠在一块，比较难看清，需要开启扩展显示，和 MySQL \G 类似select*from pg_stat_activity where state !='idle';SELECT datname,   usename,   pid,   client_hostname,   backend_start,   query_start,   wait_event_type,   STATE,   backend_type FROM pg_stat_activity; #查看会话的状态select now(),  pid,  client_addr,  now()-query_start exec_time,  query from pg_stat_activity where state ='active'and pid <> pg_backend_pid()and query_start < now()-'60 s'::intervalorderby query_start; #查询超过60S的会话select pg_backend_pid(); #查看当前会话ID

显示 SQL 执行时间

\timing

将查询结果输出到操作系统（Oracle spool 功能）

\o test.txt select*from demo1; \o

执行 SQL 脚本

\i test.sql

查看 blocker​​​​​​​

SELECT  datname,   pid,   usename,   client_hostname,   wait_event,   STATE,   query FROM pg_stat_activity WHERE pid IN (SELECT pid FROM pg_locks WHERE GRANTED<>'f'AND transactionid = (SELECT transactionid FROM pg_locks WHERE GRANTED='f'));

查看膨胀高的 top 10 表​​​​​​​

SELECT relname AS TABLE_NAME,   pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) AS table_size,   n_dead_tup,   n_live_tup,  (n_dead_tup *100/ (n_live_tup + n_dead_tup)) AS dead_tup_ratio FROM pg_stat_user_tables WHERE n_dead_tup<>0 LIMIT 10;

查看长事务​​​​​​​

SELECTextract(epoch FROM (clock_timestamp() - xact_start)) AS longtrans,  extract(epoch FROM (clock_timestamp() - query_start)) AS longquery FROM pg_stat_activity WHERE STATE <>'idle' ;

当前数据库TOP10大小的表​​​​​​​

SELECT relname,   pg_relation_size('public."'|| relname ||'"')/1024/1024AS MB,   relkind FROM pg_class WHERE relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname NOTIN ('pg_toast', 'pg_temp_1', 'pg_toast_temp_1', 'pg_catalog', 'information_schema')) AND relkind ='r'ORDERBY2DESC LIMIT 10 ;

查看当前数据库top10对象​​​​​​​

SELECT relname,  pg_relation_size('public."'|| relname ||'"')/1024/1024AS MB,   relkind FROM pg_class WHERE relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname NOTIN ('pg_toast', 'pg_temp_1', 'pg_toast_temp_1', 'pg_catalog', 'information_schema')) ORDERBY2DESC LIMIT 10;

查看统计信息​​​​​​​

analyze students; #收集统计信息（分析表）vacuum students; # vacuum 表#vacuum; # vacuum 数据库vacuum full; # vacuum 回收空间,会锁表SELECT schemaname,  relname,   n_live_tup,   n_dead_tup,   n_mod_since_analyze,   last_analyze,   last_autoanalyze FROM pg_stat_user_tables WHERE relname=’students’; # 查看统计信息

查看 SQL 执行计划​​​​​​​

explain select*from students;#注意，analyze 会执行 SQL 。explain (analyze,verbose,costs,buffers,timing) select*from students;

查找PG堵塞​​​​​​​

SELECT blocked_locks.pid AS blocked_pid,       blocked_activity.usename AS blocked_user,       blocking_locks.pid AS blocking_pid,                blocking_activity.usename AS blocking_user,                blocked_activity.query AS blocked_statement,                blocking_activity.query AS current_statement_in_blocking_processFROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activityON blocked_activity.pid = blocked_locks.pidJOIN pg_catalog.pg_locks blocking_locks    ON blocking_locks.locktype = blocked_locks.locktype        AND blocking_locks.database ISNOT DISTINCTFROM blocked_locks.database        AND blocking_locks.relation ISNOT DISTINCTFROM blocked_locks.relation        AND blocking_locks.page ISNOT DISTINCTFROM blocked_locks.page        AND blocking_locks.tuple ISNOT DISTINCTFROM blocked_locks.tuple        AND blocking_locks.virtualxid ISNOT DISTINCTFROM blocked_locks.virtualxid        AND blocking_locks.transactionid ISNOT DISTINCTFROM blocked_locks.transactionid        AND blocking_locks.classid ISNOT DISTINCTFROM blocked_locks.classid        AND blocking_locks.objid ISNOT DISTINCTFROM blocked_locks.objid        AND blocking_locks.objsubid ISNOT DISTINCTFROM blocked_locks.objsubid        AND blocking_locks.pid != blocked_locks.pidJOIN pg_catalog.pg_stat_activity blocking_activity    ON blocking_activity.pid = blocking_locks.pidWHERE NOT blocked_locks.granted;

查询当前的最老事务距离当前时间、距离当前事务数, 说明膨胀空间大小, 越大可能导致越多膨胀垃圾

SELECT*FROM pg_prepared_xacts;

查看WAL的空间占用大小​​​​​​​

select pg_size_pretty(sum(size)) from pg_ls_waldir();

查看数据库的启动时间

select pg_postmaster_start_time();

查询数据库是否为备库

select pg_is_in_recovery();

修改表字段相关操作​​​​​​​

---修改字段类型，从int4改为int8ALTERTABLE event ALTERCOLUMN event_id TYPE int8 USING event_id::int4;------修改字段长度altertable nb_device altercolumn  cmd_json  type varchar(1024);---- 新增字段altertable event addcolumn house_id varchar(36);---修改字段名称ALTERTABLE event rename event_id to id;
----移除非空限制altertable person_house alterCOLUMN person_house_type dropnotnull;
------- 删除字段ALTERTABLE users DROPCOLUMN id_card CASCADE;

 查看某个表的索引使用情况​​​​​​

select    relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch from    pg_stat_user_indexes where    relname = table_name orderby    idx_scan asc, idx_tup_read asc, idx_tup_fetch asc;

关于锁

PostgreSQL中主要有两种锁，一个表锁一个行锁，PostgreSQL中也提供了页锁，咨询锁，But，这个不需要关注，他是为了锁的完整性表锁显而易见，就是锁住整张表。表锁也分为很多中模式。表锁的模式很多，其中最核心的两个：ACCESS SHARE：共享锁（读锁），读读操作不阻塞，但是不允许出现写操作并行。ACCESS EXCLUSIVE：互斥锁（写锁），无论什么操作进来，都阻塞。表锁使用示例：​​​​​​​

begin;-- 基于默认的ACCESS EXCLUSIVE锁住test表lock test in ACCESS SHARE mode;-- 操作select*from test;-- 提交事务，表锁释放commit;

注意，表锁语法为 lock table [only] [表名] in [xxx]  mode;  [xxx]的可选值为：ROW SHARE ， ROW EXCLUSIVE SHARE ， exclusive ，ACCESS SHARE  ， ACCESS EXCLUSIVE ，SHARE ROW EXCLUSIVE ， SHARE update exclusive。比如: ​​​​​​​

lock tableonly "event" in SHARE update EXCLUSIVE  mode;  ## event是表名

PostgreSQL的行锁和MySQL的基本是一模一样的，基于select for update就可以指定行锁。MySQL中有一个概念，for update时，如果select的查询没有命中索引，可能会锁表。PostgerSQL有个特点，一般情况，在select的查询没有命中索引时，他不一定会锁表，依然会实现行锁。PostgreSQL的行锁，就玩俩，一个for update，一个for share。在开启事务之后，直接执行select * from table where 条件 for update。行锁使用示例：​​​​​​​

-- 先开启事务begin;-- 基于for update 锁住id为3的数据select*from test where id =3forupdate;update test set name ='v1'where id =3;-- 提交事务，锁释放commit;

 这两年，IT行业面临经济周期波动与AI产业结构调整的双重压力，确实有很多运维与网络工程师因企业缩编或技术迭代而暂时失业。

很多人都在提运维网工失业后就只能去跑滴滴送外卖了，但我想分享的是，对于运维人员来说，即便失业以后仍然有很多副业可以尝试。

运维副业方向

运维，千万不要再错过这些副业机会！

第一个是知识付费类副业：输出经验打造个人IP

在线教育平台讲师

操作路径：在慕课网、极客时间等平台开设《CCNA实战》《Linux运维从入门到精通》等课程，或与培训机构合作录制专题课。
收益模式：课程销售分成、企业内训。

技术博客与公众号运营

操作路径：撰写网络协议解析、故障排查案例、设备评测等深度文章，通过公众号广告、付费专栏及企业合作变现。
收益关键：每周更新2-3篇原创，结合SEO优化与社群运营。

第二个是技术类副业：深耕专业领域变现

企业网络设备配置与优化服务

操作路径：为中小型企业提供路由器、交换机、防火墙等设备的配置调试、性能优化及故障排查服务。可通过本地IT服务公司合作或自建线上接单平台获客。
收益模式：按项目收费或签订年度维护合同。

远程IT基础设施代维

操作路径：通过承接服务器监控、日志分析、备份恢复等远程代维任务。适合熟悉Zabbix、ELK等技术栈的工程师。
收益模式：按工时计费或包月服务。

网络安全顾问与渗透测试

操作路径：利用OWASP Top 10漏洞分析、Nmap/BurpSuite等工具，为企业提供漏洞扫描、渗透测试及安全加固方案。需考取CISP等认证提升资质。
收益模式：单次渗透测试报告收费；长期安全顾问年费。

比如不久前跟我一起聊天的一个粉丝，他自己之前是大四实习的时候做的运维，发现运维7*24小时待命受不了，就准备转网安，学了差不多2个月，然后开始挖漏洞，光是补天的漏洞奖励也有个四五千，他说自己每个月的房租和饭钱就够了。

为什么我会推荐你网安是运维人员的绝佳副业&转型方向?

1.你的经验是巨大优势: 你比任何人都懂系统、网络和架构。漏洞挖掘、内网渗透、应急响应，这些核心安全能力本质上是“攻击视角下的运维”。你的运维背景不是从零开始，而是降维打击。

2.越老越吃香，规避年龄危机: 安全行业极度依赖经验。你的排查思路、风险意识和对复杂系统的理解能力，会随着项目积累而愈发珍贵，真正做到“姜还是老的辣”。

3.职业选择极其灵活: 你可以加入企业成为安全专家，可以兼职“挖洞“获取丰厚奖金，甚至可以成为自由顾问。这种多样性为你提供了前所未有的抗风险能力。

4.市场需求爆发，前景广阔: 在国家级政策的推动下，从一线城市到二三线地区，安全人才缺口正在急剧扩大。现在布局，正是抢占未来先机的黄金时刻。

运维转行学习路线

（一）第一阶段：网络安全筑基

1. 阶段目标

你已经有运维经验了，所以操作系统、网络协议这些你不是零基础。但要学安全，得重新过一遍——只不过这次我们是带着“安全视角”去学。

2. 学习内容

**操作系统强化：**你需要重点学习 Windows、Linux 操作系统安全配置，对比运维工作中常规配置与安全配置的差异，深化系统安全认知（比如说日志审计配置，为应急响应日志分析打基础）。

**网络协议深化：**结合过往网络协议应用经验，聚焦 TCP/IP 协议簇中的安全漏洞及防护机制，如 ARP 欺骗、TCP 三次握手漏洞等（为 SRC 漏扫中协议层漏洞识别铺垫）。

**Web 与数据库基础：**补充 Web 架构、HTTP 协议及 MySQL、SQL Server 等数据库安全相关知识，了解 Web 应用与数据库在网安中的作用。

**编程语言入门：**学习 Python 基础语法，掌握简单脚本编写，为后续 SRC 漏扫自动化脚本开发及应急响应工具使用打基础。

**工具实战：**集中训练抓包工具（Wireshark）、渗透测试工具（Nmap）、漏洞扫描工具（Nessus 基础版）的使用，结合模拟场景练习工具应用（掌握基础扫描逻辑，为 SRC 漏扫工具进阶做准备）。

（二）第二阶段：漏洞挖掘与 SRC 漏扫实战

1. 阶段目标

这阶段是真正开始“动手”了。信息收集、漏洞分析、工具联动，一样不能少。

熟练运用漏洞挖掘及 SRC 漏扫工具，具备独立挖掘常见漏洞及 SRC 平台漏扫实战能力，尝试通过 SRC 挖洞搞钱，不管是低危漏洞还是高危漏洞，先挖到一个。

2. 学习内容

信息收集实战：结合运维中对网络拓扑、设备信息的了解，强化基本信息收集、网络空间搜索引擎（Shodan、ZoomEye）、域名及端口信息收集技巧，针对企业级网络场景开展信息收集练习（为 SRC 漏扫目标筛选提供支撑）。

漏洞原理与分析：深入学习 SQL 注入、CSRF、文件上传等常见漏洞的原理、危害及利用方法，结合运维工作中遇到的类似问题进行关联分析（明确 SRC 漏扫重点漏洞类型）。

工具进阶与 SRC 漏扫应用：

• 系统学习 SQLMap、BurpSuite、AWVS 等工具的高级功能，开展工具联用实战训练；

• 专项学习 SRC 漏扫流程：包括 SRC 平台规则解读（如漏洞提交规范、奖励机制）、漏扫目标范围界定、漏扫策略制定（全量扫描 vs 定向扫描）、漏扫结果验证与复现；

• 实战训练：使用 AWVS+BurpSuite 组合开展 SRC 平台目标漏扫，练习 “扫描 - 验证 - 漏洞报告撰写 - 平台提交” 全流程。
  SRC 实战演练：选择合适的 SRC 平台（如补天、CNVD）进行漏洞挖掘与漏扫实战，积累实战经验，尝试获取挖洞收益。

恭喜你，如果学到这里，你基本可以下班搞搞副业创收了，并且具备渗透测试工程师必备的「渗透技巧」、「溯源能力」，让你在黑客盛行的年代别背锅，工作实现升职加薪的同时也能开创副业创收！

如果你想要入坑黑客&网络安全，笔者给大家准备了一份：全网最全的网络安全资料包需要保存下方图片，微信扫码即可前往获取!

因篇幅有限，仅展示部分资料，需要点击下方链接即可前往获取

（三）第三阶段：渗透测试技能学习

1. 阶段目标

全面掌握渗透测试理论与实战技能，能够独立完成渗透测试项目，编写规范的渗透测试报告，具备渗透测试工程师岗位能力，为护网红蓝对抗及应急响应提供技术支撑。

2. 学习内容

渗透测试核心理论：系统学习渗透测试流程、方法论及法律法规知识，明确渗透测试边界与规范（与红蓝对抗攻击边界要求一致）。

实战技能训练：开展漏洞扫描、漏洞利用、电商系统渗透测试、内网渗透、权限提升（Windows、Linux）、代码审计等实战训练，结合运维中熟悉的系统环境设计测试场景（强化红蓝对抗攻击端技术能力）。

工具开发实践：基于 Python 编程基础，学习渗透测试工具开发技巧，开发简单的自动化测试脚本（可拓展用于 SRC 漏扫自动化及应急响应辅助工具）。

报告编写指导：学习渗透测试报告的结构与编写规范，完成多个不同场景的渗透测试报告撰写练习（与 SRC 漏洞报告、应急响应报告撰写逻辑互通）。

（四）第四阶段：企业级安全攻防（含红蓝对抗）、应急响应

1. 阶段目标

掌握企业级安全攻防、护网红蓝对抗及应急响应核心技能，考取网安行业相关证书。

2. 学习内容

护网红蓝对抗专项：

• 红蓝对抗基础：学习护网行动背景、红蓝对抗规则（攻击范围、禁止行为）、红蓝双方角色职责（红队：模拟攻击；蓝队：防御检测与应急处置）；

• 红队实战技能：强化内网渗透、横向移动、权限维持、免杀攻击等高级技巧，模拟护网中常见攻击场景；

• 蓝队实战技能：学习安全设备（防火墙、IDS/IPS、WAF）联动防御配置、安全监控平台（SOC）使用、攻击行为研判与溯源方法；

• 模拟护网演练：参与团队式红蓝对抗演练，完整体验 “攻击 - 检测 - 防御 - 处置” 全流程。
  应急响应专项：

• 应急响应流程：学习应急响应 6 步流程（准备 - 检测 - 遏制 - 根除 - 恢复 - 总结），掌握各环节核心任务；

• 实战技能：开展操作系统入侵响应（如病毒木马清除、异常进程终止）、数据泄露应急处置、漏洞应急修补等实战训练；

• 工具应用：学习应急响应工具（如 Autoruns、Process Monitor、病毒分析工具）的使用，提升处置效率；

• 案例复盘：分析真实网络安全事件应急响应案例（如勒索病毒事件），总结处置经验。
  其他企业级攻防技能：学习社工与钓鱼、CTF 夺旗赛解析等内容，结合运维中企业安全防护需求深化理解。

证书备考：针对网安行业相关证书考试内容（含红蓝对抗、应急响应考点）进行专项复习，参加模拟考试，查漏补缺。

运维转行网络攻防知识库分享

网络安全这行，不是会几个工具就能搞定的。你得有体系，懂原理，能实战。尤其是从运维转过来的，别浪费你原来的经验——你比纯新人强多了。

但也要沉得住气，别学了两天Web安全就觉得自己是黑客了。内网、域渗透、代码审计、应急响应，要学的还多着呢。

如果你真的想转，按这个路子一步步走，没问题。如果你只是好奇，我劝你再想想——这行要持续学习，挺累的，但也是真有意思。

关于如何学习网络安全，笔者也给大家整理好了全套网络安全知识库，需要的可以扫码获取！

因篇幅有限，仅展示部分资料，需要点击下方链接即可前往获取

1、网络安全意识

2、Linux操作系统

3、WEB架构基础与HTTP协议

4、Web渗透测试

5、渗透测试案例分享

6、渗透测试实战技巧

7、攻防对战实战

8、CTF之MISC实战讲解

关于如何学习网络安全，笔者也给大家整理好了全套网络安全知识库，需要的可以扫码获取！

因篇幅有限，仅展示部分资料，需要点击下方链接即可前往获取