PostGreSQL常用命令

发表于 更新于 分类于 ， 阅读次数：
PostGreSQL常用命令记录

PostGreSQL常用命令

1. 基础使用

  • 官方文档
    pgpool-II-3.5.4
    postgresql-11

  • shell命令

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    #默认的用户和数据库是postgres
    psql -U user -d dbname -h localhost -p port -d databaseName
    	# -U指定用户 -d要连接的数据库 -h要连接的主机 -W提示输入密码 -p指定端口 -d 指定连接数据库
    #导入sql
     psql -h localhost -p port -d postgres -f *.sql
    #pgpool启停
    sudo pgpool -C -D -n > /data/pgpool/logfile/pgpool.log 2>&1 &
    pgpool -m fast stop
    	#-C --clear-oidmaps Clear query cache oidmaps when memqcache_method is memcached
    	#-D --discard-status Discard pgpool_status file and do not restore previous status
    	#-C，在memqcache_method=memcached时清除查询缓存oidmaps（如果是shmem，则在pgpool启动时丢弃。）
    	#-D，--放弃状态放弃pgpool_status文件，不还原以前的状态

    1.1增

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    #创建用户
    CREATE USER userName WITH PASSWORD 'passwd' ;
    #创建角色
    CREATE ROLE rolename;
    #创建数据库  
    create database [数据库名]; 
    #创建模式
    create schema [schema_name];
    #创建表  
    create  TABLE ([字段名1] [类型1] <references 关联表名(关联的字段名)>;,[字段名2] [类型2],......<,primary key (字段名m,字段名n,...)>;); \copyright

    1.2 删

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    #删除数据库  
    DROP database [数据库名];  
    	#删除数据库是提示 有进程在连接该数据库则执行如下sql
    	#pg_terminate_backend 用来终止与数据库的连接的进程id的函数
    	#pg_stat_activity 是一个系统表，用于存储服务进程的属性和状态
    	#pg_backend_pid() 是一个系统函数，获取附加到当前会话的服务器进程的ID
    SELECT pg_terminate_backend(pg_stat_activity.pid)
    FROM pg_stat_activity
    WHERE datname='smart_horn' AND pid<>pg_backend_pid();
    #删除一个表
    DROP  TABLE [表名]; 
    DELETE FROM [表名];
    #清空表
    TRUNCATE table_name;
    TRUNCATE TABLE table_name RESTART IDENTITY;#postgresql 的自增字段 是通过序列sequence来实现的，在清空表的时候，还需要还原序列
    #删除表中的字段  
    ALTER  TABLE [表名] DROP column [字段名]; 
    #删除表中某行数据  
    DELETE FROM [表名] WHERE [该行特征]; 
    #删除库表保留表结构 执行如下sql生成删除sql脚本语句
    select CONCAT('TRUNCATE  TABLE ',table_name,' CASCADE',';') FROM information_schema.tables WHERE TABLE_SCHEMA = 'public' and table_type = 'BASE  TABLE';
    TRUNCATE  TABLE tablename CASCADE;#会删除引用此表的关联表数据、此命令为清空表命令
    ##删除表
    #若该表有字段被其他表引用成外键，要用级联方式删所有关联的数据 CASCADE
    DROP TABLE tablename CASCADE;##会删除引用此表的引用表外键
    #删除用户和组
    DROP ROLE IF EXISTS role_name;
    #回收用户的所有权限再删除
    revoke all on database databasename from username;

    1.3 改

  • 修改用户属性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#修改用户权限
ALTER ROLE username WITH attribute_options;
	#禁止用户登录 配置无权限
ALTER ROLE username WITH NOLOGIN;
#修改用户密码
ALTER USER userName WITH PASSWORD 'passwd';
#设置访问权限
GRANT permission_type ON table_name TO role_name;
	#permission_type
	ALL:所有权限
	#role_name = PUBLIC 表示所有用户
#撤销用户访问权限
REVOKE permission_type ON table_name FROM user_name;
#INHERIT权限 该属性使组成员拥有组的所有权限
ALTER ROLE test_user INHERIT;

#切换到role_name用户
SET ROLE role_name;
#切换回最初的role
RESET ROLE;
  • 修改表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#重命名一个表  
ALTER  TABLE [表名A] rename to [表名B]; 
#设置表主键
ALTER  TABLE [表名]  add constraint PK_SysUser primary key("UserId"); 
#重命名一个字段   
ALTER  TABLE [表名] rename column [字段名A] to [字段名B]; 
#在已有的表里添加字段  
ALTER  TABLE [表名] add column [字段名] [类型]; 
#给一个字段设置缺省值   
ALTER  TABLE [表名] ALTER column [字段名] SET default [新的默认值];
#去除缺省值   
ALTER  TABLE [表名] ALTER column [字段名] DROP default; 
#在表中插入数据  
insert INTO 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......); 
#修改表中的某行某列的数据  
update [表名] SET [目标字段名]=[目标值] WHERE [该行特征];

1.4 查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#SQL 命令语法上的说明，用 * 显示全部命令
\h [名称]      
#查看用户
\du
#切换数据库,相当于mysql的USE dbname
\c dbname
#列举数据库，相当于mysql的SHOW databases
\l
select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database;#例举database和数据库大小
#列举表，相当于mysql的SHOW tables
\dt
#查看表结构，相当于DESC tablename,SHOW columns FROM tbname
\d tablename
#查看索引 
\di 
#显示 PostgreSQL 的使用和发行条款
 #显示或设定用户端字元编码
\encoding [字元编码名称]
 #提示用户设定内部变数
\prompt [文本] 名称
 #securely change the password for a user
\password [USERNAME]
#查看拓展函数
select * from pg_extension;

2. 常见问题

1. pgpool集群互斥问题

2. pgsql主从流复制问题

1
2
3
4
5
6
7
#查看进程 ps -aux
主库有sender进程
从库有receiver进程
#查看复制状态
select * from pg_stat_replication;
#查看当前是否正在进程流复制
select pg_is_in_recovery();

3. pgsql归档备份问题

  1. 如何使用归档备份
    pg_waldump
    https://www.escapelife.site/posts/b47f1fcb.html
    https://www.infoq.cn/article/Yh39wx9xlrjJMC90TFPN

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    #在数据库查看具体的日志编号LSN
    select proname from pg_proc where proname like 'pg_%_location';
    #查询到当前的日志编号情况
    select pg_current_xlog_location();
    #获得当前预写式日志写入位置
    select pg_current_wal_lsn();
    #强制切换到一个新的预写式日志文件（默认只限于超级用户，但是可以授予其他用户 EXECUTE 特权来执行该函数）
    pg_switch_wal()

    pg_resetwal -f /data/pgsql11/data/

    问题：归档后主从不同步

  2. 优雅clean wal目录

4. pgsql 配置文件软载入

pg_ctl -D /data/pgsql11/data -l /data/pgsql11/log/pgsql.log reload

5. pgsql主从切换问题

  1. 主库挂了后，pgpool会进入只读，需重启（主库和pgpool主节点为统一节点）
    当pgpool状态改变后，需要重启pgpool集群才能改变？
  2. https://www.cnblogs.com/miclis/p/10480979.html

6. pgsql 监控

并发\执行时间\集群状态
数据库监控指标

PostgreSQL 锁等待监控 珍藏级SQL - 谁堵塞了谁
PostgreSQL 用系统表来分析postgresql的问题

6. pgpool

  • 问题原因

    pgpool作为postgresql的中间件，当集群内存在至少两个节点时，就会进行选举，如果此时第三个节点还没起来，当选举完成后，pgpool不会将没有参加选举的节点自动加入集群，需要手工attach进集群，或者同时重启pgpool进行重启选举，即pgpool本身不具有重启后能自动加入集群并恢复的机制。

  • 解决方案

    • 方案1：手动attach
      1
      2
      3
      4
      5
      #将掉线节点手动重新加入数据库集群中，例如掉线节点为192.168.1.1并且node id为0，执行下面的attach命令：
      #pcp port :9898
      pcp_attach_node -n 0 -p 9898 -h 192.168.1.1 -U app

      pcp_attach_node -n 2
    • 方案2：重启pgpool，触发重新选举
      Pgpool每次选举都会读取pgpool状态文件，为了避免影响下次选举，所以需要删除该状态文件

Postgresql & Pgpool 场景问题和解决方案