PostgreSQL创建事务、开启归档及用户赋权

基础操作之___创建事务、开启归档、赋权

1、发起事务

（方式一）：

htdb=# start transaction;
START TRANSACTION
1
2

插入数据

htdb=# insert into httab values(10001,'xiaohong',10001);
INSERT 0 1
1
2

查询验证

htdb=# select * from httab limit 2 offset 10000;
  id   |   name   |  age  
-------+----------+-------
 10001 | xiaohong | 10001
1
2
3
4

关闭窗口

htdb=# \q
$ 
1
2

登录验证

$ psql -Uhtuser htdb
psql (12.3)
Type "help" for help.

htdb=# select * from httab limit 2 offset 10000;
 id | name | age 
----+------+-----
(0 rows)
1
2
3
4
5
6
7
8

测试结果发现一个没有提交的事务在窗口被关闭以后事务会被回滚

不开启事务默认提交

（方式二）：

htdb=# begin;
BEGIN
1
2

插入数据

insert into httab values(10002,'xiaoming',10002);
1

查询验证

select * from httab limit 2 offset 10000;
  id   |   name   |  age  
-------+----------+-------
 10002 | xiaoming | 10002
(1 row)
1
2
3
4
5

手动提交

htdb=# commit;
COMMIT
1
2

退出窗口

htdb=# \q
$
1
2

查询验证

htdb=# select * from httab limit 2 offset 10000;
  id   |   name   |  age  
-------+----------+-------
 10002 | xiaoming | 10002
(1 row)
1
2
3
4
5

已提交的事务会持久化保存到数据库中

2、配置归档模式

开启归档

查询当前归档状态

htdb=# show archive_mode;
 archive_mode 
--------------
 off
(1 row)
1
2
3
4
5

启用数据库归档

$ grep  archive_mode postgresql.conf
archive_mode = on		# enables archiving; off, on, or always
1
2

重启数据库使参数生效

$ pg_ctl restart -D /pgdata12/
1

再次查询当前归档状态

htdb=# show archive_mode;
 archive_mode 
--------------
 on
1
2
3
4

archive_mode 为ON表示开启归档模式，修改需要重启PG生效

archive_mode = on
1

archive_command 参数值是一个脚本，或一个命令，PG通过执行它来完成归档动作

archive_command = '*scripts or command*';
1

切换归档日志

$ psql htdb -c 'select pg_switch_wal()';
 pg_switch_wal 
---------------
 0/1980DC0
(1 row)

$ psql htdb -c 'select pg_switch_wal()';
 pg_switch_wal 
---------------
 0/2000078
(1 row)

#也可以在数据库中执行

htdb=# select pg_switch_wal();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

查看归档日志文件

$ pwd
/pgdata12/pg_wal
$ ll
total 49152
-rw-------. 1 postgres postgres 16777216 Aug 21 02:48 000000010000000000000001
-rw-------. 1 postgres postgres 16777216 Aug 21 02:48 000000010000000000000002
-rw-------. 1 postgres postgres 16777216 Aug 21 02:50 000000010000000000000003
drwx------. 2 postgres postgres       82 Aug 21 02:48 archive_status
1
2
3
4
5
6
7
8

归档命令设置

创建归档存放新路径/pgarch

mkdir /pgarch
chown postgres:postgres /pgarch
1
2

在归档后执行命令，拷贝归档到/pgarch目录

htdb=# alter system set archive_command = 'cp %p /pgarch/%f';
ALTER SYSTEM
1
2

重新加载参数文件

$ pg_ctl reload -D /pgdata12
或：
htdb=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)
1
2
3
4
5
6
7

查看归档命令参数

htdb=# show archive_command;
 archive_command  
------------------
 cp %p /pgarch/%f
(1 row)
1
2
3
4
5

切换归档日志

$ psql htdb -c 'select pg_switch_wal()';
 pg_switch_wal 
---------------
 0/180000F0
(1 row)

$ psql htdb -c 'select pg_switch_wal()';
 pg_switch_wal 
---------------
 0/19000078
(1 row)
1
2
3
4
5
6
7
8
9
10
11

查看/pgarch

$ ll /pgarch
total 49152
-rw-------. 1 postgres postgres 16777216 Aug 21 12:50 000000010000000000000017
-rw-------. 1 postgres postgres 16777216 Aug 21 12:52 000000010000000000000018
1
2
3
4

归档失败测试

修改拷贝目的端目录属主

# chown root:root /pgarch/
1

切换归档日志

htdb=# select pg_switch_wal();
-[ RECORD 1 ]-+-----------
pg_switch_wal | 0/1C000078

htdb=# select pg_switch_wal();
-[ RECORD 1 ]-+-----------
pg_switch_wal | 0/1D000000
1
2
3
4
5
6
7

htdb=# \x
Expanded display is on.
htdb=# select * from pg_stat_archiver;
-[ RECORD 1 ]------+------------------------------
archived_count     | 14
last_archived_wal  | 00000001000000000000001B
last_archived_time | 2022-08-22 00:03:42.450784+08
failed_count       | 2
last_failed_wal    | 00000001000000000000001C
last_failed_time   | 2022-08-22 00:04:36.906391+08
stats_reset        | 2022-08-21 23:49:04.060582+08
1
2
3
4
5
6
7
8
9
10
11

恢复拷贝目的端目录属主

chown postgres:postgres /pgarch/
1

再次手动切换归档日志

htdb=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/1E000000
(1 row)

htdb=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/1E000000
(1 row)
1
2
3
4
5
6
7
8
9
10
11

归档日志切换正常

htdb=# \x
Expanded display is on.
htdb=# select * from pg_stat_archiver;
-[ RECORD 1 ]------+------------------------------
archived_count     | 16
last_archived_wal  | 00000001000000000000001D
last_archived_time | 2022-08-22 00:06:28.880997+08
failed_count       | 2
last_failed_wal    | 00000001000000000000001C
last_failed_time   | 2022-08-22 00:05:38.935018+08
stats_reset        | 2022-08-21 23:49:04.060582+08
1
2
3
4
5
6
7
8
9
10
11

3、创建用户并赋权

新建用户

htdb=# create schema ws_user;
CREATE SCHEMA
1
2

创建测试表

htdb=# create table ws_user.ws_tab(id int,name varchar(50));
CREATE TABLE
1
2

插入数据

htdb=# insert into  ws_user.ws_tab values(1,'xiaohong');
INSERT 0 1
1
2

查询数据测试

htdb=# \c htdb htuser
You are now connected to database "htdb" as user "htuser".
htdb=> select * from ws_user.ws_tab;
ERROR:  permission denied for schema ws_user
LINE 1: select * from ws_user.ws_tab;
1
2
3
4
5

授予htuser对ws_user的usage和select权限

htdb=# grant usage on schema ws_user to htuser;
GRANT
htdb=# grant select on ws_user.ws_tab to htuser;
GRANT
1
2
3
4

查询验证

htdb=# select * from ws_user.ws_tab;
 id |   name   
----+----------
  1 | xiaohong
(1 row)
1
2
3
4
5

查看用户对表的权限

htdb=# \dp ws_user.ws_tab
                                Access privileges
 Schema  |  Name  | Type  |   Access privileges   | Column privileges | Policies 
---------+--------+-------+-----------------------+-------------------+----------
 ws_user | ws_tab | table | htuser=arwdDxt/htuser |                   | 
(1 row)
1
2
3
4
5
6

输出可理解为由以下3部分组成

grantee = 权限列表 / grantor

a：插入（append）

r：查询

w：更新（write）

d：删除（delete）

D：truncate

x：REFERENCES

t：trigger

完整权限详见官方文档

https://www.postgresql.org/docs/12/ddl-priv.html

（初次学习、诸多不足、请多指教）