postgresql基本sql语句用法

1. 查询用户

   SELECT rolname from pg_roles;

1. 创建普通用户

   CREATE USER jira WITH PASSWORD 'password';

2. 给jira用户授jira数据库所有权限

   GRANT ALL PRIVILEGES ON DATABASE jira to jira;

3. 创建以utf-8字符的数据库，并且以template0为模版创建

   CREATE DATABASE jira WITH  OWNER = postgres TEMPLATE = template0 ENCODING = 'UTF8';

4. 在执行登陆操作后提示FATAL： role ‘root’ is not permitted to log in.

   alter user "root" login;

5. 把全库都导出

   $ pg_dumpall > db.out
   
   从该文件中恢复数据库：
   $ psql -f db.out postgres
   执行这个命令的时候连接到哪个数据库无关紧要，因为pg_dumpall 创建的脚本将会包含恰当的创建和连接数据库的命令
   http://www.postgres.cn/docs/9.4/app-pg-dumpall.html

6. 创建一个用户名为，密码为的用户

   CREATE USER <readonlyuser> WITH ENCRYPTED PASSWORD '<your_own_password>';

7. 修改用户只读事务属性

   ALTER USER <readonlyuser> SET default_transaction_read_only=on;

8. 设置USAGE权限给到

   GRANT USAGE ON SCHEMA public to <readonlyuser>;

9. 在对应的数据库中，授予权限，如select。

   GRANT SELECT ON ALL TABLES IN SCHEMA public TO <readonlyuser>;

10. 查询当前连接数:

    select count(1) from pg_stat_activity;

11. 查询最大连接数

    show max_connections;
    
    最大连接数也可以在pg配置文件中配置：
    在postgresql.conf中设置：
    max_connections = 500

12. 统计各数据库占用磁盘大小：

    # 参考链接： https://wiki.postgresql.org/wiki/Disk_Usage
    
     SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
        CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
            THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
            ELSE 'No Access'
        END AS SIZE
    FROM pg_catalog.pg_database d
        ORDER BY
        CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
            THEN pg_catalog.pg_database_size(d.datname)
            ELSE NULL
        END DESC -- nulls first
        LIMIT 20

13. 统计数据库中各表占用磁盘大小：

    # 只显示表名和占用磁盘大小
    SELECT
        table_schema || '.' || table_name AS table_full_name,
        pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
    FROM information_schema.tables
    ORDER BY
        pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
    
    # 详细显示各个参数
    SELECT *, pg_size_pretty(total_bytes) AS total
        , pg_size_pretty(index_bytes) AS INDEX
        , pg_size_pretty(toast_bytes) AS toast
        , pg_size_pretty(table_bytes) AS TABLE
      FROM (
      SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
          SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
                  , c.reltuples AS row_estimate
                  , pg_total_relation_size(c.oid) AS total_bytes
                  , pg_indexes_size(c.oid) AS index_bytes
                  , pg_total_relation_size(reltoastrelid) AS toast_bytes
              FROM pg_class c
              LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
              WHERE relkind = 'r'
      ) a
    ) a ORDER BY total_bytes desc;

14. 导出数据库和导入数据库命令

    # 例子
    pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)   >/data/dum.sql
    gunzip < filename.gz | psql dbname
    
    pg_dump -h ${host} -U card -p 3433 -W -d apollon | gzip > backup_all.sql.gz 
    gunzip < backup_all.sql.gz | psql -h ${host} -U ${user} -p 3433 -W ${database}

15. 创建一个用户名为allreadonly密码为allreadonly的用户

    CREATE USER allreadonly WITH ENCRYPTED PASSWORD 'allreadonly';

16. 用户只读事务

    alter user allreadonly set default_transaction_read_only=on;

1. 把所有库的语言的USAGE权限给到 allreadonly (设置不生效)

   GRANT USAGE ON SCHEMA public to allreadonly;

2. 授予select权限(这句要进入具体数据库操作在哪个db环境执行就授予那个db的权)

   grant select on all tables in schema public to allreadonly;

3. 查看PostgreSQL正在执行的SQL

   SELECT 
       procpid, 
       start, 
       now() - start AS lap, 
       current_query 
   FROM 
       (SELECT 
           backendid, 
           pg_stat_get_backend_pid(S.backendid) AS procpid, 
           pg_stat_get_backend_activity_start(S.backendid) AS start, 
          pg_stat_get_backend_activity(S.backendid) AS current_query 
       FROM 
           (SELECT pg_stat_get_backend_idset() AS backendid) AS S 
       ) AS S 
   WHERE 
      current_query <> '<IDLE>' 
   ORDER BY 
      lap DESC;
   
   procpid：进程id
   start：进程开始时间
   lap：经过时间
   current_query：执行中的sql
   
   通过命令：
   =# select pg_cancel_backend(线程id);
   来kill掉指定的SQL语句。
   （这个函数只能 kill  Select 查询，而updae,delete DML不生效）
   
   使用
   =# select  pg_terminate_backend(pid int)
   可以kill 各种DML(SELECT,UPDATE,DELETE,DROP)操作
   
   虽然可以使用 kill -9 来强制删除用户进程，但是不建议这么去做。
   因为：对于执行update的语句来说，kill掉进程，可能会导致Postgres进入到recovery mode
   而在recovery mode下，会锁表，不允许链接数据库。

4. 备份单表操作

   -U表示用户
   -h表示主机
   -p表示端口号
   -t表示表名
   -f表示备份后的sql文件的名字
   -d表示要恢复数据库名称
   
    pg_dump -U postgres -h localhost -p 5432 -t staff -f staff.sql yjl（表示数据库名称）

5. 恢复数据单表操作

   psql -U postgres -h localhost -p 5432 -d product -f /home/yjl/staff.sql

6. 导出整个数据库

   pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)   >/data/dum.sql

7. 导入整个数据库

   psql -U postgres(用户名)  数据库名(缺省时同用户名) < /data/dum.sql

8. 压缩备份

   # 压缩方法
   
   一般用dump导出数据会比较大，推荐使用xz压缩
   压缩方法  xz dum.sql 会生成 dum.sql.xz 的文件
   
   
   # xz压缩数据倒数数据库方法
   
   xzcat /data/dum.sql.xz | psql -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)

---本文结束感谢您的阅读。微信扫描二维码，关注我的公众号---

本文作者： Peng Yang
本文链接： https://www.yp14.cn/2019/08/29/postgresql基本sql语句用法/
版权声明： 本作品采用 知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议 进行许可。转载请注明出处！

赏

谢谢您的打赏

微信