1.查看pg进程

$ ps f -u postgres
PID TTY STAT TIME COMMAND
3107 ? Ss 0:00 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
3138 ? Ss 0:00 _ postgres: logger
3140 ? Ss 0:00 _ postgres: checkpointer
3141 ? Ss 0:00 _ postgres: background writer
3142 ? Ss 0:00 _ postgres: walwriter
3143 ? Ss 0:00 _ postgres: autovacuum launcher
3144 ? Ss 0:00 _ postgres: stats collector
3145 ? Ss 0:00 _ postgres: logical replication launcher
特别是当存在多个版本或多个实例时，非常方便。

2.查看函数定义

select pg_get_functiondef(to_regproc(‘function’));

select prosrc from pg_proc where proname=‘function’;

\df+

3.perform

客户端不需要返回数据时可替换select，效率更高
perform posfile_trade_sp_rcvpos_trade();

perform 1 from basic_param ;

if found then

insert into XXX;

end if;

4.update from

语法更简洁
basic_equipment：设备表，有网点的属性

basic_equipment_plan：设备网点变更计划表

作业需求：每天将设备网点变更计划表中到期的设备进行网点属性的变更
update basic_equipment be
set net_no = bep.new_net_no,modify_date = current_timestamp(0)
from basic_equipment_plan bep
where be.equip_id = bep.equip_id
and bep.plan_modify_date = current_date

5.update返回旧值

report=> create table test(id int ,info text);
CREATE TABLE
report=> insert into test values(100,‘aaa’);
INSERT 0 1
report=> update test set info=‘bbb’ where id=100 returning *;
id | info
-----±-----
100 | bbb
report=> UPDATE test x
report-> SET info = ‘ccc’
report-> FROM test y
report-> WHERE x.id = y.id
report-> AND x.id = 100
report-> RETURNING x.id,x.info as new_info,y.info AS old_info;
id | new_info | old_info
-----±---------±---------
100 | ccc | bbb
(1 row)

update test set info=‘ddd’ where id=100 returning(select info from test where id=100);

也可采用with as实现

6.upsert (insert + update)

insert into table ()
values ()
on conflict do update ;-- or do nothing

7.备份恢复

按schema先备份再恢复
pg_dump -h 192.168.20.19 -p 5432 -Fc -v --schema=ebas -f ebas.backup -U ebas student
pg_restore -p 5432 -d student -U ebas --schema ebas -v ebas.backup

备份恢复数据不落地
pg_dump -h 192.168.20.19 -p 5432 -Fc -v --schema=ebas -U ebas student
|
pg_restore -h 192.168.20.138 -p 5432 -d student -U ebas --schema ebas -v

只备份结构
pg_dump -Fp -v --schema=afc --schema-only --no-owner --no-tablespaces --no-privileges -T c_card_trade* -f 19_edb_schema_afc.sql -U afc edb
cat 19_edb_schema_afc.sql | grep -v “^–” > 19_edb_schema_afc.txt

备份全库
pg_dumpall -h 192.168.20.19 -p 5432 -c -f /tcps/pg-19-dumpall.sql -U postgres
psql -p 5432 -U postgres -f /tcps/pg-19-dumpall.sql

输出导出过程的日志到文件

ab.out >>~/ab.out 2>&1

8.COPY与 \COPY

program选项，可调用操作系统的命令
注意：确保数据干净和正确格式化，否则出错数据将占用磁盘空间并不可访问

9.事务锁

begin;
set local lock_timeout = ‘10s’;
– DDL query;
end;

10.idle_in_transaction_session_timeout

关注pg_stat_activity里idle in transaction