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)


1. 查看当前登录用户名

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


1. 切换数据库

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


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

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


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

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


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

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 |


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

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)


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

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


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

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)


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

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

此处以创建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)


1. 手动同步主postgresql文件

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


1. 创建用户、删除用户

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;