postgresql日常管理

 

1.创建用户并将某个数据库的属主修改为该用户

create user uhxl;
alter user uhxl with password 'uhxl';
alter user uhxl with CONNECTION LIMIT 20;

alter database hxl owner to uhxl; ##修改数据库属主
GRANT ALL PRIVILEGES ON DATABASE hxl TO uhxl; ##或是授权

 

2.进入数据库查看数据库下的表
psql -h localhost -U uhxl -d hxl
select * from pg_tables where schemaname = 'public';


3.修改表的owner
person目前属主是postgres,现在修改为uhxl
psql -h localhost -U postgres -d hxl
alter table person owner to uhxl;

 

4.查看表的字段
hxl=#\d 表名称

 

5.查看表的ddl
只能使用pg_dump,使用pg_dump我们可以把表还有索引的语句都dump出来,这里使用-s选项(schema only)和-t选项(tables)。
pg_dump -s -t person -d hxl|egrep -v "^--|^$"

 

6.清除数据并清除自增ID
--清除所有的记录（有外键关联的情况下）
truncate table tb_test cascade;
--清除所有的记录，并且索引号从0开始
truncate table tb_test restart identity cascade;

 

7.查看权限

登陆具体的数据库(特别说明需要登录特定的数据库，否则查询结果不一致):
psql -h localhost -U uhxl -d hxl
select * from information_schema.table_privileges where grantee='uhxl';
select * from information_schema.usage_privileges where grantee='uhxl';
select * from information_schema.routine_privileges where grantee='uhxl';

 

8.登陆数据库授权
psql -h localhost -U hxl01 -d hxl
grant SELECT on table public.tb_hxl to uhxl;

9.查询某个表在那个库下面:
SELECT * FROM information_schema.tables WHERE table_name='tb_hxl';

10.查看用户拥有的角色
postgres=# \du uhxl
             List of roles
 Role name |   Attributes   | Member of
-----------+----------------+-----------
 uhxl      | 20 connections | {}

 

postgres=# \du postgres
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

 

11.创建数据库指定字符集

create database db_zifuji encoding = 'utf8';

 

12.查看所有的库

postgres=# \l
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

 

 

13.查看用户

postgres=# \du
 goldengate | Superuser                                                  | {}
 postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

 

14.归档日志清理(wal)

[postgres@host134 pg_wal]$ pg_archivecleanup -d /opt/pg14/archivelog 000000010000000000000008  ##删除000000010000000000000008之前的归档

 

15.查看对象大小
##查看表占用空间大小

db_test=# SELECT pg_size_pretty(pg_database_size('db_test'));
pg_size_pretty
----------------
886 MB
(1 row)

 

db_test=# select relpages*8/1024||'M' as 占用空间,reltuples 记录数 from pg_class where relname='tb_test';
占用空间 | 记录数
----------+-------------
737M | 5.80968e+06
(1 row)

##查看数据库占用空间大小
SELECT pg_size_pretty(pg_database_size('db_test'));

 

16.查看表大小

SELECT relname, pg_size_pretty(pg_relation_size(relid)) AS size
FROM pg_stat_user_tables;

 

 

查看表结构
\d 表名

 

17.查看会话

/opt/pg16/bin/psql -h localhost -U postgres -p5432

开启竖模式

postgres=# \x
Expanded display is on.

 

select state,count(1) from pg_stat_activity group by state;

select pid,usename,client_addr,client_port,backend_start,query_start,state_change,state,query from pg_stat_activity;

select pid,usename,client_addr,client_port,backend_start,query_start,state_change,state,query from pg_stat_activity where state='active';

select pid,usename,query from pg_stat_activity where state='active'; 

 

#杀会话
select pg_terminate_backend('pid号');

 

查看当前自身登录的信息
select version(), txid_current(), pg_backend_pid(), current_user, current_schema, current_timestamp;

 

本身自己的会话
select pg_backend_pid();

 

18.锁相关

##granted=true(t)是阻塞别人的,false是被阻塞的
SELECT database, locktype, relation, relation::regclass, mode, pid,granted FROM pg_locks where granted=true and pid<>pg_backend_pid();

select pid,usename,wait_event,state,query from pg_stat_activity where pid=6943;

 

 19.不堵塞dml创建索引

-- 在大型用户表上在线创建索引
CREATE INDEX CONCURRENTLY idx_created_at ON events(created_at);

 

删除无效索引

drop index concurrently if exists idx_created_at;

 

查看索引创建进度

 

-- 详细监控查询
SELECT 
p.pid, p.datname, p.usename, p.client_addr,
p.query_start, now() - p.query_start AS duration,i.phase, i.blocks_total, i.blocks_done,
round(100 * i.blocks_done / NULLIF(i.blocks_total, 0), 2) AS progress_pct
FROM pg_stat_progress_create_index i JOIN pg_stat_activity p ON i.pid = p.pid;

查看百分比
SELECT blocks_done,blocks_total,round(100 * i.blocks_done / NULLIF(i.blocks_total, 0), 2) AS progress_pct FROM pg_stat_progress_create_index i;

 

 

 

20.查看有那些存储过程
postgres=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------------------------+------------------+---------------------+------
public | batch_delete_data | void | | func
public | sp_batch_delete_data | | | proc
public | sp_batch_delete_data_bak | | | proc
(3 rows)

 

 

获取存储过程代码
SELECT pg_get_functiondef(oid)
FROM pg_proc
WHERE proname = 'sp_batch_delete_data';