迁移postgresql 14的时候报错有点多，回退了
,
PostgreSQL Database directory appears to contain a database; Skipping initialization,
,
2021-11-08 03:10:09.353 UTC [1] LOG: starting PostgreSQL 14.0 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20210424) 10.3.1 20210424, 64-bit,
2021-11-08 03:10:09.353 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432,
2021-11-08 03:10:09.353 UTC [1] LOG: listening on IPv6 address "::", port 5432,
2021-11-08 03:10:09.359 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432",
2021-11-08 03:10:09.369 UTC [21] LOG: database system was shut down at 2021-11-08 03:09:59 UTC,
2021-11-08 03:10:09.377 UTC [1] LOG: database system is ready to accept connections,
2021-11-08 03:10:57.632 UTC [43] ERROR: role "postgres" already exists,
2021-11-08 03:10:57.632 UTC [43] STATEMENT: CREATE ROLE postgres;,
2021-11-08 03:11:52.879 UTC [22] LOG: checkpoints are occurring too frequently (24 seconds apart),
2021-11-08 03:11:52.879 UTC [22] HINT: Consider increasing the configuration parameter "max_wal_size".,
2021-11-08 03:12:04.023 UTC [45] ERROR: invalid input syntax for type boolean: "",
2021-11-08 03:12:04.023 UTC [45] CONTEXT: COPY mentions, line 880367, column silent: "",
2021-11-08 03:12:04.023 UTC [45] STATEMENT: COPY public.mentions (id, status_id, created_at, updated_at, account_id, silent) FROM stdin;,
2021-11-08 03:12:12.970 UTC [45] ERROR: invalid input syntax for type bigint: "c565353",
2021-11-08 03:12:12.970 UTC [45] CONTEXT: COPY preview_cards, line 534192, column id: "c565353",
2021-11-08 03:12:12.970 UTC [45] STATEMENT: COPY public.preview_cards (id, url, title, description, image_file_name, image_content_type, image_file_size, image_updated_at, type, html, author_name, author_url, provider_name, provider_url, width, height, created_at, updated_at, embed_url, image_storage_schema_version, blurhash) FROM stdin;,
2021-11-08 03:12:17.027 UTC [22] LOG: checkpoints are occurring too frequently (25 seconds apart),
2021-11-08 03:12:17.027 UTC [22] HINT: Consider increasing the configuration parameter "max_wal_size".,
2021-11-08 03:12:19.727 UTC [45] ERROR: invalid input syntax for type timestamp: "",
2021-11-08 03:12:19.727 UTC [45] CONTEXT: COPY status_stats, line 974194, column updated_at: "",
2021-11-08 03:12:19.727 UTC [45] STATEMENT: COPY public.status_stats (id, status_id, replies_count, reblogs_count, favourites_count, created_at, updated_at) FROM stdin;,
2021-11-08 03:12:33.652 UTC [22] LOG: checkpoints are occurring too frequently (16 seconds apart),
2021-11-08 03:12:33.652 UTC [22] HINT: Consider increasing the configuration parameter "max_wal_size".,
2021-11-08 03:12:51.660 UTC [22] LOG: checkpoints are occurring too frequently (18 seconds apart),
2021-11-08 03:12:51.660 UTC [22] HINT: Consider increasing the configuration parameter "max_wal_size".,
2021-11-08 03:13:11.506 UTC [22] LOG: checkpoints are occurring too frequently (20 seconds apart),
2021-11-08 03:13:11.506 UTC [22] HINT: Consider increasing the configuration parameter "max_wal_size".,
2021-11-08 03:13:27.982 UTC [22] LOG: checkpoints are occurring too frequently (16 seconds apart),
2021-11-08 03:13:27.982 UTC [22] HINT: Consider increasing the configuration parameter "max_wal_size".,
2021-11-08 03:13:49.128 UTC [74] ERROR: canceling autovacuum task,
2021-11-08 03:13:49.128 UTC [74] CONTEXT: while scanning block 62212 of relation "public.media_attachments",
automatic vacuum of table "postgres.public.media_attachments",
2021-11-08 03:13:53.847 UTC [74] ERROR: canceling autovacuum task,
2021-11-08 03:13:53.847 UTC [74] CONTEXT: while scanning block 7912 of relation "public.mentions",
automatic vacuum of table "postgres.public.mentions",
2021-11-08 03:13:55.768 UTC [74] ERROR: canceling autovacuum task,
2021-11-08 03:13:55.768 UTC [74] CONTEXT: while scanning block 2208 of relation "public.pghero_space_stats",
automatic vacuum of table "postgres.public.pghero_space_stats",
2021-11-08 03:13:57.035 UTC [74] ERROR: canceling autovacuum task,
2021-11-08 03:13:57.035 UTC [74] CONTEXT: while scanning block 2117 of relation "public.preview_cards",
automatic vacuum of table "postgres.public.preview_cards",
2021-11-08 03:14:00.123 UTC [94] ERROR: canceling autovacuum task,
2021-11-08 03:14:00.123 UTC [94] CONTEXT: while scanning block 39950 of relation "public.statuses",
automatic vacuum of table "postgres.public.statuses",
2021-11-08 03:15:33.147 UTC [22] LOG: checkpoints are occurring too frequently (24 seconds apart),
2021-11-08 03:15:33.147 UTC [22] HINT: Consider increasing the configuration parameter "max_wal_size".,
2021-11-08 03:15:37.676 UTC [125] ERROR: canceling autovacuum task,
2021-11-08 03:15:37.676 UTC [125] CONTEXT: while scanning block 213998 of relation "public.statuses",
automatic vacuum of table "postgres.public.statuses"
迁移postgresql 14的时候报错有点多，回退了
@holgerhuo 对的，这些错就是导入的时候报的
迁移postgresql 14的时候报错有点多，回退了
@holgerhuo 以前从9.6升级到12过一次，那次是没有任何错误的
迁移postgresql 14的时候报错有点多，回退了
@mashiro
唔 好的
这里还有一个pg_upgrade的命令（虽然是最新版的文档 不过看上去还是在10x版本时修订的orz
https://www.postgresql.org/docs/current/pgupgrade.html
报错主要是那个checkpoint的嘛 那个好像是pg里的一个设定卡的太小了？
https://stackoverflow.com/questions/27972393/postgres-checkpoints-are-occurring-too-frequently
剩下的copy啥的报错 仿佛确实很奇怪 那个BIGINT数据仿佛不应该是那个样子 我去查了文档应该都是十进制的🤔
（当然我没有学过PostgreSQL所以也不一定对orz）
https://www.geeksforgeeks.org/postgresql-bigint-integer-data-type/
@holgerhuo 主要是数据太多了，我想看看出错那几行的内容都不容易
@mashiro
哎 是的
我一直还计划着把实例从（还不太算）上古的CentOS7迁移到Ubuntu或者rocky 然后想想这个流程就很头秃 于是就搁置了lol
迁移postgresql 14的时候报错有点多，回退了
@mashiro 好像说这种迁移是需要 先用pg_dump导出成sql的格式 然后再导入？（不过我没有实践过orz 上次这么干是因为有一次手贱用rpm包升级 没想到数据分区被抹掉了（更神奇的是还只是小版本升级） 所以不得不用之前的备份文件导入恢复）