Upgrade postgres in Docker

Published by powerfulyang on Dec 19, 2022

如何升级 docker 中的 postgres 版本?

Backup

Use beblow command, replace yourService with yourself container_name.

bash
1docker-compose exec yourService pg_dumpall -U postgres > 14.5.backup

Modify your postgres tag by using "15" instead of "14", then down your container.

bash
1docker-compose rm -s -v yourService

Boot the new DB - PostgreSQL 15.1 and Restore from backup

bash
1docker-compose up -d yourService
2cat 14.5.backup | docker-compose exec -T db psql -U postgres

关于 postgres 的一些介绍

JSON 和 JSONB 的区别

postgresql支持两种json数据类型：json和jsonb，而两者唯一的区别在于效率,json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json类型存储快，使用慢，jsonb类型存储稍慢，使用较快。

注意：键值对的键必须使用双引号

从PostgreSQL 9.3开始，json就成了postgres里的一种数据类型，也就是和varchar、int一样，我们表里的一个字段的类型可以为json了。

与此同时，postgres还提供了jsonb格式，jsonb格式是json的二进制形式，二者的区别在于json写入快，读取慢，jsonb写入慢，读取快，但在操作上，二者是没有区别的。

查询数据

Postgres里的查询需要用到查询符。比如说，我们要查询id为1的数据，语句如下：

sql
1select info from name_age where info @> '{"id":1}'::jsonb

用到了 @> 这个查询符，表明info当前这条记录里的顶层json中有没有id为1的key-value对；有的话则满足条件。

再来一个复杂一点的查询的，查询 age>16 的记录，并且只显示 name ，语句如下：

sql
1select info->'name' from name_age where (info->>'age')::int4 > 16

关于详细运算符使用，请参考官方文档： 9.15. JSON Functions and Operators

修改数据

下面，将 age 从 18 改为 22 ，SQL语句：

sql
1SELECT info ||'{"age":22}'::jsonb from name_age where (info->>'id')::int4 = 1

上述用法仅适用于9.5以上，9.5以下需要整个记录更新，不可以单独修改某个值。

除了操作符以外，还可以使用函数操作： jsonb_set() ，函数签名如下：

sql
1jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

详细使用可参考 9.15. JSON Functions and Operators

删除数据

删除age这个key，SQL如下：

sql
1SELECT info-'age' from name_age where (info->>'id')::int4 = 1

直接用操作符 - 即可。

总结

PostgreSQL 9.5以上的版本中有了很多方便的操作符，使得操作json变得非常方便了。

json和jsonb的操作符

操作符右操作数类型描述示例结果
->int获取JSON数组元素（索引从0开始）select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2;{"c":"baz"}
->text通过键获取值select '{"a": {"b":"foo"}}'::json->'a';{"b":"foo"}
->>int获取JSON数组元素为 textselect '[1,2,3]'::json->>2;3
->>text通过键获取值为textselect '{"a":1,"b":2}'::json->>'b';2
#>text[]在指定的路径获取JSON对象select '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}';{"c": "foo"}
#>>text[]在指定的路径获取JSON对象为 textselect '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}';3

jsonb额外操作符

操作符右操作数类型描述示例结果
@>jsonb左侧json最上层的值是否包含右边json对象select '{"a":{"b":2}}'::jsonb @> '{"b":2}'::jsonb; select '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb;f;t
<@jsonb左侧json对象是否包含于右侧json最上层的值内select '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb;t
?texttext是否作为左侧Json对象最上层的键select '{"a":1, "b":2}'::jsonb ? 'b';t
?text[]text[]中的任一元素是否作为左侧Json对象最上层的键select '{"a":1, "b":2, "c":3}'::jsonb ?
?&text[]text[]中的所有元素是否作为左侧Json对象最上层的键select '["a", "b"]'::jsonb ?& array['a', 'b'];t
jsonb连接两个json对象，组成一个新的json对象
-text删除左侧json对象中键为text的键值对select '{"a": "b"}'::jsonb - 'a';{}
-integer删除数组指定索引处的元素，如果索引值为负数，则从右边计算索引值。如果最上层容器内不是数组，则抛出错误。select '["a", "b"]'::jsonb - 1;["a"]
#-text[]删除指定路径下的域或元素（如果是json数组，且整数值是负的，则索引值从右边算起）select '["a", {"b":1}]'::jsonb #- '{1,b}';["a", {}]

不要信任 PostgreSQL 的默认排序

MYSQL,PG 是没有默认排序的, 不指定排序返回顺序不可靠

PG 官方文档: The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen.

在 PostgreSQL 中创建一个只读用户

1. 创建新用户

创建一个新用户（例如，readonly_user）并为其设置密码：

sql
1CREATE USER readonly_user WITH ENCRYPTED PASSWORD 'your_password';

2. 赋予只读权限

对于您希望只读用户访问的每个表，执行以下命令以赋予只读权限：

sql
1GRANT USAGE ON SCHEMA public TO readonly_user;
2GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

如果您以后在 public 模式下添加了更多表，您还需要为这些新表赋予只读权限。您可以通过以下命令自动完成这一操作：

sql
1ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

请注意，此命令仅适用于在执行此命令之后创建的表。对于已存在的表，您仍然需要使用 GRANT SELECT 命令。