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 | {}