postgres数据库的运维常用操作

• 1.查看指定数据库内用户关联表权限
• 2.查看当前登录用户名
• 3.切换数据库
• 4.查看当前数据库查询schema模式路径
• 5.查看当前数据库所有schema模式
• 6.查看当前数据库表信息
• 7.查看当前数据库某表结构
• 8.查看当前数据库有哪些schema模式
• 9.查看当前数据库指定表权限
• 10.创建指定数据库的只读账号
• 11.手动同步主postgresql文件
• 12.创建用户、删除用户
• 13.创建数据库、删除数据库
• 14.查询正在执行的SQL操作
• 15.杀死SQL进程
• 16.使用表pg_stat_all_tables查看autovacuum执行记录
• 17.查看所有数据库事务ID目前值
• 18.vacuum重置事务ID
• 19.PG进入单用户模式

---

1. 查看指定数据库内用户关联表权限

 sms=> select * from information_schema.table_privileges where grantee = 'seal_rw';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
    ---------+---------+---------------+--------------+------------+----------------+--------------+----------------
    (0 rows)

2. 查看当前登录用户名

sms=> select current_user;
    current_user 
--------------
    sms_rw
(1 row)

3. 切换数据库

sms=> \c db_name db_user
sms=> \c - db_user
sms=> \c db_name -

4. 查看当前数据库查询schema模式路径

sms=> show search_path;
    search_path   
-----------------
    "$user", public
(1 row)

5. 查看当前数据库所有schema模式

sms=> select current_schemas(true);
    current_schemas   
---------------------
    {pg_catalog,public}
(1 row)

6. 查看当前数据库表信息

sms=> \d
                    List of relations
    Schema |         Name          |   Type   |  Owner   
--------+-----------------------+----------+----------
    public | pg_stat_statements    | view     | postgres
    public | sms_black_list        | table    | sms_rw
    public | sms_black_list_id_seq | sequence | sms_rw
    public | sms_send_log          | table    | sms_rw
    public | sms_send_log_id_seq   | sequence | sms_rw
    public | sms_sending           | table    | sms_rw
    public | sms_sending_id_seq    | sequence | sms_rw
    public | sms_template          | table    | sms_rw
    public | sms_template_id_seq   | sequence | sms_rw
(9 rows)
sms=> \d+
                                    List of relations
    Schema |         Name          |   Type   |  Owner   |    Size    |  Description   
--------+-----------------------+----------+----------+------------+----------------
    public | pg_stat_statements    | view     | postgres | 0 bytes    | 
    public | sms_black_list        | table    | sms_rw   | 16 kB      | 黑名单
    public | sms_black_list_id_seq | sequence | sms_rw   | 8192 bytes | 
    public | sms_send_log          | table    | sms_rw   | 3080 kB    | 已短信发送记录
    public | sms_send_log_id_seq   | sequence | sms_rw   | 8192 bytes | 
    public | sms_sending           | table    | sms_rw   | 16 kB      | 待短信发送记录
    public | sms_sending_id_seq    | sequence | sms_rw   | 8192 bytes | 
    public | sms_template          | table    | sms_rw   | 64 kB      | 短信模板
    public | sms_template_id_seq   | sequence | sms_rw   | 8192 bytes | 

7. 查看当前数据库某表结构

sms=> \d+ sms_black_list
                                                Table "public.sms_black_list"
    Column  |  Type  |                          Modifiers                          | Storage  | Stats target | Description 
---------+--------+-------------------------------------------------------------+----------+--------------+-------------
    id      | bigint | not null default nextval('sms_black_list_id_seq'::regclass) | plain    |              | 
    mobiles | jsonb  |                                                             | extended |              | 手机号码
Indexes:
    "sms_black_list_pkey" PRIMARY KEY, btree (id)

8. 查看当前数据库有哪些schema模式

sms=> \dn
    List of schemas
    Name  |  Owner   
--------+----------
    public | postgres
(1 row)

9. 查看当前数据库指定表权限

sms=# \dp sms_black_list
                                    Access privileges
    Schema |      Name      | Type  |   Access privileges   | Column privileges | Policies 
--------+----------------+-------+-----------------------+-------------------+----------
    public | sms_black_list | table | sms_rw=arwdDxt/sms_rw+|                   | 
        |                |       | sms_read=r/sms_rw     |                   | 
(1 row)

10. 创建指定数据库的只读账号

创建指定数据库相对应的只读账号，需要先切换到指定数据库，在执行下面命令

此处以创建p2p_activity_r 只读账号为例
postgres=#  create role p2p_activity_r LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE encrypted password 'xxxx';
postgres=# \c  p2p_activity   p2p_activity_rw;
Password for user p2p_activity_rw: 
You are now connected to database "p2p_activity" as user "p2p_activity_rw".
p2p_activity=> grant connect on database p2p_activity to p2p_activity_r;
GRANT
p2p_activity=> grant usage on schema public to p2p_activity_r;
WARNING:  no privileges were granted for "public" 
GRANT
p2p_activity=> grant select on all tables in schema public to p2p_activity_r;
WARNING:  no privileges were granted for "pg_stat_statements"
GRANT
p2p_activity=> ALTER DEFAULT PRIVILEGES IN SCHEMA public grant select on tables to p2p_activity_r;
ALTER DEFAULT PRIVILEGES
p2p_activity=> \q
----------------------------------------------------------
说明：
查看当前用户下的schema，一般情况都是public
sms=> select current_schema;
    current_schema 
    ----------------
    public
    (1 row)

11. 手动同步主postgresql文件

-bash-4.1$ pg_basebackup -D $PGDATA -Fp -Xs -v -h 192.168.6.54 -p 5432 -U postgres

12. 创建用户、删除用户

postgres=# create user zhangshan with login encrypted password '100abcd';
postgres=# drop user user_name;

13.创建数据库、删除数据库

postgres=# create database db_name owner user_name;
postgres=# drop database db_name;

14.查询正在执行的SQL操作

postgres=# show track_activities ;
    track_activities 
------------------
    off
(1 row)
postgres=# set track_activities=on;
postgres=# select datname,usename,state,query from pg_stat_activity where state='active';

15.杀死SQL进程

kill有两种方式,第一种是：
SELECT pg_cancel_backend(PID);
这种方式只能kill select查询，对update、delete 及DML不生效)

第二种是：
SELECT pg_terminate_backend(PID);
这种可以kill掉各种操作(select、update、delete、drop等)操作

16.使用表pg_stat_all_tables查看autovacuum执行记录

postgres=# select schemaname,relname,last_autovacuum,last_autoanalyze from pg_stat_all_tables;

17.查看所有数据库事务ID目前值

postgres=# SELECT datname,age(datfrozenxid) FROM pg_database ;

18.vacuum重置事务ID

需使用超级管理员登录PG，并使用超级管理员用户切换到重置数据库中，才能有效重置

psql
postgres=# \c channel;
postgres=# vacuum freeze;

19.PG进入单用户模式

PG必须停机才能进入单用户模式，流复制模式下，只需主库进行事务ID重置，从库不需要重置操作。

postgres --single -D $PGDATA loanapply
> vacuum freeze;