postgres操作指南

安装使用

mac可以通过命令行用brew来安装或者直接下载安装包来安装，网上资料很多，这里就不介绍了。

如果通过brew来安装的，可以通过brew services start/stop postgres来开启或停止。

还有一种方法是通过docker来跑，这里以mac桌面版为例，创建启动脚本pg-docker-compose.yml:

# Use postgres/example user/password credentials
version: '3.1'

services:
  pgsql:
    image: postgres:12
    restart: always
    privileged: true
    ports:
      - '5432:5432'
    environment:
      POSTGRES_USER: 'postgres'
      POSTGRES_DB: 'postgres'
      POSTGRES_PASSWORD: 'postgres'
      PGDATA: /var/lib/postgresql/data/pgdata
    volumes:
      - /Users/xxx/docker/postgres/data:/var/lib/postgresql/data/pgdata

通过 docker-compose -f pg-docker-compose.yml up -d启动docker。打开就能看到容器已经启动了，如下所示：

接下来可以通过pgAdmin来进行数据库访问和操作。具体安装过程这里不介绍。

安装完成之后，创建数据库连接，以上面的docker启动的数据库为例，连接参数如下：

postgres与mysql有点不一样，mysql的table是database的下级元素，而postgres的database下面还有schemas，其中public是默认的schema，schema下面才是table。

在需要操作的表格上右键，选择不同的script，可以进行数据操作。其中，PSQL Tool是客户端命令行(如果安装了postgres服务端而没有安装pgAdmin，可以直接通过psql来开启命令行)。

上面的SQL栏可以查看table的建表语句。

postgres常用命令：

角色权限

点开Login/FGroup ROles可以查看role和其属性，包括每个role的权限，如下所示：

可以看出，postgres是一个super用户，拥有所有权限。

创建新用户

先通过\c查看当前用户，然后通过当前用户创建新用户。当然，当前用户必须拥有Create Role的权限才行。

新创建的用户test只有login权限。

注意关键字role和user的区别，如create user会自动赋予用户登录权限，而create role不会。
在早期版本中，用户和角色是分开的，但从PostgreSQL 10开始，用户和角色被统一，用户现在只是一个拥有默认登录权限的角色。用户可以拥有登录权限，而角色不能。

切换用户

注意必须与登录用户的密码相同才能切换，否则要修改db连接密码重新登录。切换到新用户后，由于新用户只有默认的登录权限，因此是不能再创建用户的。

修改用户权限

重新切换回postgres用户，修改test用户的权限：

然后切换到test用户就可以创建新用户了：

此时test用户虽然拥有了创建新用户的权限，但其实没有表的增删改查的权限，需要单独授权。再次切换到postgres用户对test用户进行授权：

grant usage on schema public to test;
grant all privileges on all tables in schema public to test;

此时test用户就拥有增删改查权限了。

但后续通过postgres用户创建的新表，test用户还是没有权限。如果想让test用户对新表也有权限，授权语句需要修改一下：

grant usage on schema public to test;
grant all privileges on all tables in schema public to test;
alter default privileges in schema public grant all privileges on tables to test;

注意，如果整个schema都被删除了再重建，需要重新授权。

删除用户

切换到新用户test1，尝试删除本身：

因为test1只有login权限，所以不能删除role。可以切换到具有createrole权限的test用户来删除：

如果当前用户拥有删除role权限但报错：

cannot be dropped because some objects depend on it

说明这个不能删除的role拥有其他对象。但是drop role并没有cascade删除指令，只能先删除其拥有的其他对象，再删除role：

注意：执行此操作需保证要删除的role所拥有的其他objects确实是可以删除的。

drop owned by test cascade;
drop user test

如果不能直接粗暴删除或者还是没有权限，那么可以先回收权限

# 回收所有表权限
revoke all privileges on all tables in schema public from test;

# 回收单个表权限
revoke all privileges on table xxx from test;

# 回收schema权限
revoke all privileges on schema public from test;

# 回收默认权限
alter default privileges in schema public revoke all privileges on tables from test;

再次尝试删除即可。

用户组

test用户虽然具有创建role和db的权限，但是不能创建public模式。将postgres的权限赋予test，然后查看用户权限信息：

此时可以看到：test是postgres的一个成员，因此具有创建public模式的权限：

如果执行某项操作报错：

ERROR: must be member of role "xx"

则同样地，将xx权限赋予给当前用户：

grant xx to current_user

这样当前用户就成为xx的成员，可以将当前用户所拥有的一些table或者schema转让给xx。

数据迁移和校验

查询所有表的记录数

select schemaname, relname, n_live_tup from pg_stat_user_tables order by n_live_tup desc;

可以使用以下命令进行数据dump：

pg_dump -Fc -v -b -h localhost -p 5432 -U postgres -E UTF8 -d postgres > test.dump

生成原始数据的校验文件：

psql -hlocalhost -Upostgres -p5432 -d postgres -f test.sql > validation_test.txt

校验脚本如下：

create function validation() returns text
    language plpgsql as $$
DECLARE
    rec   record;
    countTable bigint;
    sumRowHashTable bigint;
    sch   record;
    seqName record;
    seq record;
   
    results text;
BEGIN
    results = '';
      
    -- table hash
    FOR sch IN
        SELECT *
        FROM information_schema.schemata
        WHERE schema_name <> 'pg_catalog' and schema_name <> 'information_schema'
        ORDER BY schema_name
    LOOP
        FOR rec IN
            SELECT *
            FROM pg_tables
            WHERE schemaname = sch.schema_name
            ORDER BY tablename
        LOOP
            EXECUTE 'SELECT count(*) FROM '
                        || quote_ident(rec.schemaname) || '.'
                || quote_ident(rec.tablename)
                INTO countTable;
              
            -- adjust for precision of datatype for diffrent postgres version, this is for example
            IF rec.tablename = 'order' THEN
                EXECUTE 'SELECT sum(CAST(CAST((''x'' || md5(CAST((f.id, round(f.amount::numeric, 6), f.createTime) AS text))) AS bit(32)) AS int)) FROM '
                            || quote_ident(rec.schemaname) || '.'
                    || quote_ident(rec.tablename) || ' f'
                    INTO sumRowHashTable;
            ELSE
                EXECUTE 'SELECT sum(CAST(CAST((''x'' || md5(CAST((f.*)AS text))) AS bit(32)) AS int)) FROM '
                            || quote_ident(rec.schemaname) || '.'
                    || quote_ident(rec.tablename) || ' f'
                    INTO sumRowHashTable;
            END IF;
              
   
            IF results = '' THEN
                results = rec.schemaname || '.' || rec.tablename || ',' || countTable || ',' || sumRowHashTable;
            ELSE
                results = concat(results, CHR(10), rec.schemaname || '.' || rec.tablename || ',' || countTable || ',' || sumRowHashTable);
            END IF;
        END LOOP;
    END LOOP ;
      
    -- sequence
    results = concat(results, CHR(10), CHR(10), CHR(10), CHR(10), CHR(10), '-------------------------------------');
    FOR seqName IN
        SELECT c.relname
        FROM pg_class c
        WHERE c.relkind = 'S'
        ORDER BY c.relname
    LOOP
        -- change 'public' to your own database schema
        EXECUTE  'SELECT * FROM ' || 'public' || '.' || quote_ident(seqName.relname)
        INTO seq;
        results = concat(results, CHR(10), seqName.relname || ',' || seq.last_value || ',' || seq.log_cnt || ',' || seq.is_called);
    END LOOP ;
      
    RETURN results;
END;
$$;
   
select validation();
   
DROP FUNCTION validation();

接下来通过以下命令进行数据恢复：

pg_restore -Fc -v -h localhost -p 5432 -U postgres -d postgres test.dump 

恢复之后，同样用相同的脚本生成校验文件，比较校验文件是否相同。