postgres数据库的运维常用操作

程序员文章站 2022-07-14 11:00:25

...

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

 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)

查看当前登录用户名

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

切换数据库

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

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

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

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

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

查看当前数据库表信息

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 |

查看当前数据库某表结构

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)

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

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

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

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)

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

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

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

手动同步主postgresql文件

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

创建用户、删除用户

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;

转载于:https://www.jianshu.com/p/8df552d59f03

上一篇： Postgres数据库基础操作

下一篇： postgres数据库

postgres数据库的运维常用操作

常用的PHP数据库操作方法（MYSQL版）

常用的Mysql数据库操作语句大全整理

linux运维实用的42个常用命令总结

mysql数据库表的创建以及字段的增删改查操作及一些常用的查询命令介绍

关于php操作mysql执行数据库查询的一些常用操作汇总

php操作数据库的常用操作

Drupal7中常用的数据库操作实例

mysql数据库的常用操作命令

Cassandra数据库的安装及常用操作

MySQL数据库运维之数据恢复的方法