Upgrade postgres in Docker

Published by powerfulyang on Dec 19, 2022

Upgrade postgres from v14.5 to v15.1

如何升级 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 命令。

PostgreSQL 如何 order by 多个字段

在 PostgreSQL 中，您可以通过在 ORDER BY 子句中指定多个字段来实现按照多个字段排序。您只需在字段名称之间用逗号隔开即可。以下是一个示例：

sql
1SELECT *
2FROM your_table
3ORDER BY column1, column2;

在此查询中，数据首先按 column1 排序，然后在 column1 相同的行中按 column2 排序。默认情况下，排序顺序为升序（ASC），如果你想改为降序（DESC），可以这样指定：

sql
1SELECT *
2FROM your_table
3ORDER BY column1 DESC, column2 DESC;

你也可以针对不同字段使用不同的排序顺序，如下所示：

sql
1SELECT *
2FROM your_table
3ORDER BY column1 ASC, column2 DESC;

在这个例子中，数据首先按 column1 升序排序，然后在 column1 相同的行中按 column2 降序排序。

在 TypeORM 中的写法

在 TypeORM 中，你可以在 orderBy 选项中指定多个字段进行排序。例如：

typescript
1const results = await repository.find({
2    order: {
3        column1: 'ASC',
4        column2: 'DESC',
5    },
6});

在这个例子中，column1 以升序排序，column2 以降序排序。

这里的 repository 是你要查询的实体的仓库，你可以通过 getRepository 函数或者直接注入（如果你在 Nest.js 或者其他支持依赖注入的框架中使用）来获取。

请注意你的实体需要有对应的 column1 和 column2 字段，否则 TypeORM 将无法执行此查询。

涉及大小写问题，需要使用引号包起来

typescript
1const results = await repository.find({
2    order: {
3        '"publishYear"': 'ASC',
4    },
5});