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 表名