PG常用语句

1. 查询进程、KIll进程
   进程删除：

SELECT * from pg_stat_activity where query like ‘%t_table_kk_vehpass%’

select pg_terminate_backend(7379)

锁表进程删除： select oid from pg_class where relname =‘t_table_kk_vehpass_part_34’ select pid from pg_locks where relation =‘500177’

select pg_cancel_backend(37925)

select * from sd

2. 正则匹配
   进程删除：

SELECT * from pg_stat_activity where query like ‘%t_table_kk_vehpass%’

select pg_terminate_backend(7379)

锁表进程删除： select oid from pg_class where relname =‘t_table_kk_vehpass_part_34’ select pid from pg_locks where relation =‘500177’

select pg_cancel_backend(37925)

select * from sd

3. PG备份
   pg导出数据库命令

Su - postgres

pg_dump -h 86.81.2.2 -p 5432 -U postgres -F c -b -v -f “/home/postgres/backups_210430/bita_account.backup” bita_account

pg还原数据库命令

cd pgsql/bin/

pg_restore -h 86.81.0.79 -p 5432 -U postgres -d bita_resource -v “/home/postgres/backups_210430/bita_resource.backup”

[root@localhost ~]# su - postgres
[postgres@localhost ~]$ psql

postgres=# \l --查看所有数据库信息

postgres=# \c dbname --切换到指定数据库

postgres=# \d --查看当前数据库下所有的表