La migración de la base de datos se atasca con un valor enorme de un elemento "calendar-details" en la tabla "post_custom_fields"

amotl · 7 Diciembre, 2020 11:54

TLDR;

At post #4 ff., we have been able to confirm that this problem was related to Discourse Calendar (and Event) and the database migration was nudged into success after deactivating this temporarily – thanks @falco.
At post #9 ff., we found that the value of a specific calendar-details item on a single post contained ~80MB worth of data.
After deleting the offending row as outlined in post #13, the database migration has been fully successful even after enabling Discourse Calendar (and Event) again – thanks again, @falco.
The background on this is a database migration being applied since March 2020, see DEV: Migrate from custom fields to tables by nbianca · Pull Request #23 · discourse/discourse-calendar · GitHub. Thanks, @nbianca, @eviltrout and @j.jaffeux.

Dear Discourse developers,

after some time we had not been able to take care about our Discourse instances on https://community.hiveeyes.org/ and https://community.panodata.org/, we went into the process of upgrading them yesterday night.

The upgrade on the Hiveeyes Community Forum went pretty smooth as always. PostgreSQL was upgraded from version 10 to version 12 and it was a pleasure to watch the upgrade process without further ado. Kudos to the whole team for the excellent work you are putting into Discourse!

However, when trying to upgrade the Panodata Community Forum, we are observing some problems now. On the second stage of running ./launcher rebuild community.panodata.org, the system is experiencing extremely high load and currently grinds the whole machine.

I’ve kind of identified where this starts and will share some parts of the logs here. Apparently, it looks like the database migration process is running into a loop of some kind when touching the post_custom_fields table. Let me know if you need more information about that.

Did I manage to get ourselves into serious trouble here? I am very much looking forward on anything which could help us getting out of this situation.

Thank you in advance and with kind regards,
Andreas.

I, [2020-12-07T11:33:28.554810 #1]  INFO -- : > cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate'
2020-12-07 11:33:34.200 UTC [3888] discourse@discourse LOG:  duration: 119.116 ms  parse <unnamed>: SELECT t.oid, t.typname
	FROM pg_type as t
	WHERE t.typname IN ('int2', 'int4', 'int8', 'oid', 'float4', 'float8', 'bool', 'timestamp', 'timestamptz')

2020-12-07 11:33:34.413 UTC [3888] discourse@discourse LOG:  duration: 213.398 ms  bind <unnamed>: SELECT t.oid, t.typname
	FROM pg_type as t
	WHERE t.typname IN ('int2', 'int4', 'int8', 'oid', 'float4', 'float8', 'bool', 'timestamp', 'timestamptz')

2020-12-07 11:33:34.662 UTC [3888] discourse@discourse LOG:  duration: 220.921 ms  parse <unnamed>: SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
	FROM pg_type as t
	LEFT JOIN pg_range as r ON oid = rngtypid
	WHERE
	  t.typname IN ('int2', 'int4', 'int8', 'oid', 'float4', 'float8', 'text', 'varchar', 'char', 'name', 'bpchar', 'bool', 'bit', 'varbit', 'timestamptz', 'date', 'money', 'bytea', 'point', 'hstore', 'json', 'jsonb', 'cidr', 'inet', 'uuid', 'xml', 'tsvector', 'macaddr', 'citext', 'ltree', 'line', 'lseg', 'box', 'path', 'polygon', 'circle', 'interval', 'time', 'timestamp', 'numeric')
	  OR t.typtype IN ('r', 'e', 'd')
	  OR t.typinput = 'array_in(cstring,oid,integer)'::regprocedure
	  OR t.typelem != 0

2020-12-07 11:33:34.820 UTC [3888] discourse@discourse LOG:  duration: 157.985 ms  bind <unnamed>: SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
	FROM pg_type as t
	LEFT JOIN pg_range as r ON oid = rngtypid
	WHERE
	  t.typname IN ('int2', 'int4', 'int8', 'oid', 'float4', 'float8', 'text', 'varchar', 'char', 'name', 'bpchar', 'bool', 'bit', 'varbit', 'timestamptz', 'date', 'money', 'bytea', 'point', 'hstore', 'json', 'jsonb', 'cidr', 'inet', 'uuid', 'xml', 'tsvector', 'macaddr', 'citext', 'ltree', 'line', 'lseg', 'box', 'path', 'polygon', 'circle', 'interval', 'time', 'timestamp', 'numeric')
	  OR t.typtype IN ('r', 'e', 'd')
	  OR t.typinput = 'array_in(cstring,oid,integer)'::regprocedure
	  OR t.typelem != 0

2020-12-07 11:33:35.197 UTC [3888] discourse@discourse LOG:  duration: 376.904 ms  execute <unnamed>: SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
	FROM pg_type as t
	LEFT JOIN pg_range as r ON oid = rngtypid
	WHERE
	  t.typname IN ('int2', 'int4', 'int8', 'oid', 'float4', 'float8', 'text', 'varchar', 'char', 'name', 'bpchar', 'bool', 'bit', 'varbit', 'timestamptz', 'date', 'money', 'bytea', 'point', 'hstore', 'json', 'jsonb', 'cidr', 'inet', 'uuid', 'xml', 'tsvector', 'macaddr', 'citext', 'ltree', 'line', 'lseg', 'box', 'path', 'polygon', 'circle', 'interval', 'time', 'timestamp', 'numeric')
	  OR t.typtype IN ('r', 'e', 'd')
	  OR t.typinput = 'array_in(cstring,oid,integer)'::regprocedure
	  OR t.typelem != 0

2020-12-07 11:33:35.388 UTC [3888] discourse@discourse LOG:  duration: 165.044 ms  statement: SELECT name, data_type, value FROM site_settings
2020-12-07 11:33:37.170 UTC [3888] discourse@discourse LOG:  duration: 166.378 ms  statement: SELECT a.attname, format_type(a.atttypid, a.atttypmod),
	       pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod,
	       c.collname, col_description(a.attrelid, a.attnum) AS comment
	  FROM pg_attribute a
	  LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
	  LEFT JOIN pg_type t ON a.atttypid = t.oid
	  LEFT JOIN pg_collation c ON a.attcollation = c.oid AND a.attcollation <> t.typcollation
	 WHERE a.attrelid = '"users"'::regclass
	   AND a.attnum > 0 AND NOT a.attisdropped
	 ORDER BY a.attnum

2020-12-07 11:33:40.578 UTC [3888] discourse@discourse ERROR:  relation "user_api_key_scopes" does not exist at character 454
2020-12-07 11:33:40.578 UTC [3888] discourse@discourse STATEMENT:  SELECT a.attname, format_type(a.atttypid, a.atttypmod),
	       pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod,
	       c.collname, col_description(a.attrelid, a.attnum) AS comment
	  FROM pg_attribute a
	  LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
	  LEFT JOIN pg_type t ON a.atttypid = t.oid
	  LEFT JOIN pg_collation c ON a.attcollation = c.oid AND a.attcollation <> t.typcollation
	 WHERE a.attrelid = '"user_api_key_scopes"'::regclass
	   AND a.attnum > 0 AND NOT a.attisdropped
	 ORDER BY a.attnum

2020-12-07 11:33:41.954 UTC [3898] discourse@discourse LOG:  duration: 232.874 ms  statement: CREATE TABLE "calendar_events" ("id" bigserial primary key, "topic_id" integer NOT NULL, "post_id" integer, "post_number" integer, "user_id" integer, "username" character varying, "description" character varying, "start_date" timestamp NOT NULL, "end_date" timestamp, "recurrence" character varying, "region" character varying, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL)
2020-12-07 11:33:42.693 UTC [3898] discourse@discourse LOG:  duration: 349.048 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:43.190 UTC [3898] discourse@discourse LOG:  duration: 289.433 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:43.692 UTC [3898] discourse@discourse LOG:  duration: 293.116 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:44.189 UTC [3898] discourse@discourse LOG:  duration: 288.891 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:44.622 UTC [3898] discourse@discourse LOG:  duration: 290.721 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:45.051 UTC [3898] discourse@discourse LOG:  duration: 291.924 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:45.544 UTC [3898] discourse@discourse LOG:  duration: 289.327 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:46.039 UTC [3898] discourse@discourse LOG:  duration: 291.734 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:46.471 UTC [3898] discourse@discourse LOG:  duration: 290.173 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:46.900 UTC [3898] discourse@discourse LOG:  duration: 288.805 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:47.392 UTC [3898] discourse@discourse LOG:  duration: 288.812 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:47.884 UTC [3898] discourse@discourse LOG:  duration: 286.272 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:48.326 UTC [3898] discourse@discourse LOG:  duration: 302.706 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:48.755 UTC [3898] discourse@discourse LOG:  duration: 288.766 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:49.260 UTC [3898] discourse@discourse LOG:  duration: 299.901 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:49.762 UTC [3898] discourse@discourse LOG:  duration: 293.430 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:50.192 UTC [3898] discourse@discourse LOG:  duration: 291.202 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:50.625 UTC [3898] discourse@discourse LOG:  duration: 293.805 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:51.138 UTC [3898] discourse@discourse LOG:  duration: 301.952 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:51.643 UTC [3898] discourse@discourse LOG:  duration: 291.137 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:52.075 UTC [3898] discourse@discourse LOG:  duration: 292.726 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:52.517 UTC [3898] discourse@discourse LOG:  duration: 301.480 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:53.027 UTC [3898] discourse@discourse LOG:  duration: 295.174 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:53.583 UTC [3898] discourse@discourse LOG:  duration: 292.007 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:54.025 UTC [3898] discourse@discourse LOG:  duration: 298.499 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:54.461 UTC [3898] discourse@discourse LOG:  duration: 294.801 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:54.957 UTC [3898] discourse@discourse LOG:  duration: 289.026 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:55.463 UTC [3898] discourse@discourse LOG:  duration: 301.325 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:55.905 UTC [3898] discourse@discourse LOG:  duration: 296.361 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:56.348 UTC [3898] discourse@discourse LOG:  duration: 302.991 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:58.843 UTC [3898] discourse@discourse LOG:  duration: 2289.322 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:33:59.376 UTC [3898] discourse@discourse LOG:  duration: 322.300 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:00.445 UTC [3898] discourse@discourse LOG:  duration: 921.013 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:00.749 UTC [3898] discourse@discourse LOG:  duration: 111.277 ms  bind <unnamed>: SELECT "calendar_events".* FROM "calendar_events" WHERE "calendar_events"."post_id" = 186
2020-12-07 11:34:02.055 UTC [3898] discourse@discourse LOG:  duration: 1302.319 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:02.611 UTC [3898] discourse@discourse LOG:  duration: 299.807 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:03.130 UTC [3898] discourse@discourse LOG:  duration: 310.089 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:03.586 UTC [3898] discourse@discourse LOG:  duration: 310.537 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:04.050 UTC [3898] discourse@discourse LOG:  duration: 316.072 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:04.585 UTC [3898] discourse@discourse LOG:  duration: 315.813 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:05.138 UTC [3898] discourse@discourse LOG:  duration: 332.339 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:05.611 UTC [3898] discourse@discourse LOG:  duration: 320.420 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:06.086 UTC [3898] discourse@discourse LOG:  duration: 313.841 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:06.636 UTC [3898] discourse@discourse LOG:  duration: 330.514 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:07.277 UTC [3898] discourse@discourse LOG:  duration: 423.374 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:08.308 UTC [3898] discourse@discourse LOG:  duration: 881.346 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:09.041 UTC [3898] discourse@discourse LOG:  duration: 581.518 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:09.674 UTC [3898] discourse@discourse LOG:  duration: 413.955 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:10.538 UTC [3898] discourse@discourse LOG:  duration: 642.608 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:11.047 UTC [3898] discourse@discourse LOG:  duration: 358.794 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:17.793 UTC [3898] discourse@discourse LOG:  duration: 6599.545 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:33.146 UTC [3898] discourse@discourse LOG:  duration: 15127.645 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:34:53.075 UTC [3898] discourse@discourse LOG:  duration: 316.940 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:35:04.574 UTC [57] LOG:  using stale statistics instead of current ones because stats collector is not responding
2020-12-07 11:35:19.396 UTC [3898] discourse@discourse LOG:  duration: 26159.832 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:35:19.893 UTC [3898] discourse@discourse LOG:  duration: 315.783 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:35:20.438 UTC [3898] discourse@discourse LOG:  duration: 322.196 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:35:21.081 UTC [3898] discourse@discourse LOG:  duration: 403.873 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:35:34.236 UTC [3898] discourse@discourse LOG:  duration: 12995.895 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:35:55.785 UTC [3898] discourse@discourse LOG:  duration: 20970.124 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
167:M 07 Dec 2020 11:36:31.074 * 10 changes in 300 seconds. Saving...
167:M 07 Dec 2020 11:36:31.267 * Background saving started by pid 3911
2020-12-07 11:36:40.770 UTC [3898] discourse@discourse LOG:  duration: 44203.350 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
3911:C 07 Dec 2020 11:36:52.503 * DB saved on disk
3911:C 07 Dec 2020 11:36:52.518 * RDB: 1 MB of memory used by copy-on-write
167:M 07 Dec 2020 11:36:52.618 * Background saving terminated with success
2020-12-07 11:37:00.570 UTC [3898] discourse@discourse LOG:  duration: 19089.557 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:37:09.917 UTC [3898] discourse@discourse LOG:  duration: 9086.196 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:37:50.176 UTC [3898] discourse@discourse LOG:  duration: 39768.704 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:38:06.180 UTC [3898] discourse@discourse LOG:  duration: 15340.958 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:38:16.549 UTC [3898] discourse@discourse LOG:  duration: 9526.000 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:38:45.231 UTC [3898] discourse@discourse LOG:  duration: 28221.839 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:39:26.818 UTC [3898] discourse@discourse LOG:  duration: 41289.489 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:39:56.099 UTC [3898] discourse@discourse LOG:  duration: 28172.307 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:39:58.117 UTC [3898] discourse@discourse LOG:  duration: 1384.587 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:39:59.305 UTC [3898] discourse@discourse LOG:  duration: 758.125 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:41:07.711 UTC [3898] discourse@discourse LOG:  duration: 67823.399 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:41:24.048 UTC [3898] discourse@discourse LOG:  duration: 15546.933 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:41:35.033 UTC [3898] discourse@discourse LOG:  duration: 10181.365 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:42:04.037 UTC [3898] discourse@discourse LOG:  duration: 28545.266 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:43:41.573 UTC [3898] discourse@discourse LOG:  duration: 92603.676 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:43:50.101 UTC [3898] discourse@discourse LOG:  duration: 7813.096 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:44:03.377 UTC [3898] discourse@discourse LOG:  duration: 12590.301 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:44:37.995 UTC [3898] discourse@discourse LOG:  duration: 34268.080 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:44:54.399 UTC [3898] discourse@discourse LOG:  duration: 15760.378 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:45:56.976 UTC [3898] discourse@discourse LOG:  duration: 58722.471 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:46:46.091 UTC [3898] discourse@discourse LOG:  duration: 48556.685 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:47:09.536 UTC [3898] discourse@discourse LOG:  duration: 23130.829 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:47:33.762 UTC [3898] discourse@discourse LOG:  duration: 23694.256 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:48:09.190 UTC [3898] discourse@discourse LOG:  duration: 34922.442 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:48:09.539 UTC [3898] discourse@discourse LOG:  duration: 127.286 ms  parse <unnamed>: SELECT "calendar_events".* FROM "calendar_events" WHERE "calendar_events"."post_id" = 478
2020-12-07 11:49:05.314 UTC [3898] discourse@discourse LOG:  duration: 55677.361 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:49:07.150 UTC [3898] discourse@discourse LOG:  duration: 1647.675 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:49:19.072 UTC [3898] discourse@discourse LOG:  duration: 11720.259 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:49:31.135 UTC [3898] discourse@discourse LOG:  duration: 11582.021 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:49:44.365 UTC [3898] discourse@discourse LOG:  duration: 12831.278 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:51:41.565 UTC [3898] discourse@discourse LOG:  duration: 116599.870 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:52:22.060 UTC [3898] discourse@discourse LOG:  duration: 39664.789 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:52:56.540 UTC [3898] discourse@discourse LOG:  duration: 33136.236 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:52:57.214 UTC [3898] discourse@discourse LOG:  duration: 139.491 ms  parse <unnamed>: SELECT "calendar_events".* FROM "calendar_events" WHERE "calendar_events"."post_id" = 487
2020-12-07 11:52:57.472 UTC [3898] discourse@discourse LOG:  duration: 258.338 ms  bind <unnamed>: SELECT "calendar_events".* FROM "calendar_events" WHERE "calendar_events"."post_id" = 487
2020-12-07 11:53:08.567 UTC [3898] discourse@discourse LOG:  duration: 10615.113 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:53:13.908 UTC [3898] discourse@discourse LOG:  duration: 1035.837 ms  parse <unnamed>: SELECT "calendar_events".* FROM "calendar_events" WHERE "calendar_events"."post_id" = 488
2020-12-07 11:53:14.443 UTC [3898] discourse@discourse LOG:  duration: 475.898 ms  bind <unnamed>: SELECT "calendar_events".* FROM "calendar_events" WHERE "calendar_events"."post_id" = 488
2020-12-07 11:53:21.659 UTC [3898] discourse@discourse LOG:  could not send data to client: Broken pipe
2020-12-07 11:53:21.659 UTC [3898] discourse@discourse STATEMENT:  SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
2020-12-07 11:53:21.734 UTC [3898] discourse@discourse FATAL:  connection to client lost
2020-12-07 11:53:21.734 UTC [3898] discourse@discourse STATEMENT:  SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc
Killed
I, [2020-12-07T11:53:30.522597 #1]  INFO -- :
I, [2020-12-07T11:53:35.390781 #1]  INFO -- : Terminating async processes
I, [2020-12-07T11:53:35.815966 #1]  INFO -- : Sending INT to HOME=/var/lib/postgresql USER=postgres exec chpst -u postgres:postgres:ssl-cert -U postgres:postgres:ssl-cert /usr/lib/postgresql/12/bin/postmaster -D /etc/postgresql/12/main pid: 50
2020-12-07 11:53:36.108 UTC [50] LOG:  received fast shutdown request
I, [2020-12-07T11:53:36.185340 #1]  INFO -- : Sending TERM to exec chpst -u redis -U redis /usr/bin/redis-server /etc/redis/redis.conf pid: 167
167:signal-handler (1607342016) Received SIGTERM scheduling shutdown...
167:M 07 Dec 2020 11:53:36.211 # User requested shutdown...
167:M 07 Dec 2020 11:53:36.211 * Saving the final RDB snapshot before exiting.
2020-12-07 11:53:36.626 UTC [50] LOG:  aborting any active transactions
2020-12-07 11:53:37.025 UTC [50] LOG:  background worker "logical replication launcher" (PID 59) exited with exit code 1
2020-12-07 11:53:37.616 UTC [54] LOG:  shutting down
167:M 07 Dec 2020 11:53:38.311 * DB saved on disk
167:M 07 Dec 2020 11:53:38.312 # Redis is now ready to exit, bye bye...
2020-12-07 11:53:40.331 UTC [50] LOG:  database system is shut down


FAILED
--------------------
Pups::ExecError: cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate' failed with return #<Process::Status: pid 3870 exit 137>
Location of failure: /pups/lib/pups/exec_command.rb:112:in `spawn'
exec failed with the params {"cd"=>"$home", "hook"=>"db_migrate", "cmd"=>["su discourse -c 'bundle exec rake db:migrate'"]}
a03fe2903df190bacab2f312cb2c8dca7707a9f5d9fcca0821ea5b7ce0e2d742
** FAILED TO BOOTSTRAP ** please scroll up and look for earlier error messages, there may be more than one.
./discourse-doctor may help diagnose the problem.

amotl · 7 Diciembre, 2020 12:26

Siguiendo Upgrade of postgres failed y PostgreSQL 12 update, no hemos podido identificar nada específico que pueda ayudarnos aquí.

Esta instalación no es particularmente grande

root@pulp:/srv/www/organizations/panodata/community.panodata.org# du -sch .
661M	total

y al intentar volver a iniciar la aplicación en orden

fallos, ya que la aplicación de Rails no puede conectarse a PostgreSQL:

==> shared/standalone/log/rails/unicorn.stderr.log <==
I, [2020-12-07T12:20:58.960126 #459]  INFO -- : Refreshing Gem list
/var/www/discourse/vendor/bundle/ruby/2.6.0/gems/pg-1.1.4/lib/pg.rb:56:in `initialize': could not connect to server: No such file or directory (PG::ConnectionBad)
	Is the server running locally and accepting
	connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
	from /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/pg-1.1.4/lib/pg.rb:56:in `new'
	from /var/www/discourse/vendor/bundle/ruby/2.6.0/gems/pg-1.1.4/lib/pg.rb:56:in `connect'

amotl · 7 Diciembre, 2020 12:32

tail -n100 -F shared/standalone/log/rails/production.log

revela:

Creando ámbito :pending. Sobrescribiendo el método existente DiscoursePostEvent::EventDate.pending.
Creando ámbito :open. Sobrescribiendo el método existente Poll.open.
Migrando a AddSmtpAndImapToGroups (20181220115844)
Migrando a AddImapFieldsToIncomingEmails (20190111183409)
Migrando a AddSecureToUploads (20190716173854)
Migrando a RsvpMigration (20191016154136)
Migrando a AddLastSeenToCategoryUsers (20191107025041)
Migrando a AddIndexToLastSeenAtOnCategoryUsers (20191107025140)
Migrando a ChangeNotificationLevel (20191107032231)
Migrando a RemoveSuppressFromLatestFromCategory (20191107190330)
Migrando a AddTargetTagIdToTags (20191113193141)
Migrando a AddGraphToPolls (20191114160613)
Migrando a RenameSiteSettingAssignEmailer (20191119174425)
Migrando a AddTimezoneToUserOptions (20191120015344)
Migrando a AddUniqueIndexToDevelopers (20191128222140)
Migrando a DropUnusedGoogleInstagramAuthTables (20191129144706)
Migrando a AddFeaturedTopicIdToUserProfiles (20191202202212)
Migrando a CreateStandaloneBookmarksTable (20191205100434)
Migrando a AddGroupNameToPolls (20191206123012)
Migrando a EnsuresUniqueAcceptedAnswerPostId (20191209095548)
Migrando a AddIndexToInvites (20191211152404)
Migrando a AddHashedApiKey (20191211170000)
Migrando a PopulateTopicIdOnBookmarks (20191217035630)
Migrando a RemoveKeyFromApiKeys (20191219112000)
Migrando a AddDistinctBadgeCountToUserStats (20191220134101)
Migrando a AddAccessControlColumnsToUpload (20191230055237)
Migrando a AddFeaturedRankToUserBadges (20200107161405)
Migrando a UpdateUserProfilesIndexes (20200109130028)
Migrando a AddGroupToCustomEmojis (20200116092259)
Migrando a RenameReplyIdColumn (20200116140132)
Migrando a UpdatePostReplyIndexes (20200117141138)
Migrando a AddTriggerToSyncPostReplies (20200117172135)
Migrando a MakePostReplyIdColumnReadOnly (20200117174646)
Migrando a DropUnusedColumns (20200120131338)
Migrando a RemoveBounceScoreThresholdDeactivateSiteSetting (20200130115859)
Migrando a MarkBookmarksTopicIdNotNull (20200203061927)
Migrando a CreateCalendarEvents (20200226183018)

y (¡probablemente!) en el segundo intento de ejecutar ./launcher rebuild community.panodata.org, se mostró:

Creando ámbito :pending. Sobrescribiendo el método existente DiscoursePostEvent::EventDate.pending.
Creando ámbito :open. Sobrescribiendo el método existente Poll.open.
Migrando a CreateCalendarEvents (20200226183018)

Falco · 7 Diciembre, 2020 12:34

Sugiero eliminar el complemento de calendario e intentarlo de nuevo.

amotl · 7 Diciembre, 2020 12:35

En ello, ¡gracias! Acabo de desactivar ambos y estoy reconstruyendo de nuevo.

#          - git clone https://github.com/discourse/discourse-calendar.git
#          - git clone https://github.com/angusmcleod/discourse-events.git

amotl · 7 Diciembre, 2020 13:09

Estimado Rafael,

Puedo confirmar que el plugin Discourse Calendar (and Event) ha sido efectivamente el culpable aquí, por lo que ajustaré el título de este post en consecuencia. Por si acaso, también comparto nuestra lista actual de plugins. Con estos plugins instalados (y discourse-calendar desactivado), la tarea de migración de la base de datos se completó sin problemas y el foro vuelve a estar operativo: https://community.panodata.org/.

  after_code:
    - exec:
        cd: $home/plugins
        cmd:
          - git clone https://github.com/discourse/docker_manager.git
          - git clone https://github.com/discourse/discourse-translator.git
          - git clone https://github.com/discourse/discourse-checklist.git
          - git clone https://github.com/discourse/discourse-assign.git
          - git clone https://github.com/discourse/discourse-solved.git
          - git clone https://github.com/discourse/discourse-math.git
          - git clone https://github.com/angusmcleod/discourse-tickets.git
          - git clone https://github.com/angusmcleod/discourse-knowledge-base.git
          - git clone https://github.com/angusmcleod/discourse-locations.git
          - git clone https://github.com/discourse/discourse-footnote.git
          - git clone https://github.com/discourse/discourse-graphviz.git
          - git clone https://github.com/angusmcleod/discourse-custom-wizard.git
          - git clone https://github.com/discourse/discourse-sitemap.git
          - git clone https://github.com/angusmcleod/discourse-events.git

#          - git clone https://github.com/discourse/discourse-calendar.git

Muchas gracias, sigue así y un cordial saludo,
Andreas.

P.D.: ¿Cómo deberíamos proceder para presentar un informe de error contra discourse-calendar, si fuera apropiado?

Falco · 7 Diciembre, 2020 13:22

Ahora que has migrado correctamente a PG12, puedes intentar volver a agregar el plugin para ver si la migración funciona.

amotl · 7 Diciembre, 2020 14:49

Ya lo hice. Sin embargo, el proceso de migración vuelve a caer en la misma situación.

amotl:

2020-12-07 11:33:41.954 UTC [3898] discourse@discourse LOG:  duration: 232.874 ms  statement: CREATE TABLE "calendar_events" ("id" bigserial primary key, "topic_id" integer NOT NULL, "post_id" integer, "post_number" integer, "user_id" integer, "username" character varying, "description" character varying, "start_date" timestamp NOT NULL, "end_date" timestamp, "recurrence" character varying, "region" character varying, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL)
2020-12-07 11:33:42.693 UTC [3898] discourse@discourse LOG:  duration: 349.048 ms  execute <unnamed>: SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc

¿Crees que tenga algo que ver con datos específicos de nuestra instancia dentro de las tablas calendar_events y post_custom_fields?

Aparentemente, el proceso parece quedarse atascado en post_id = 20. Así que procedí a probar esto:

./launcher enter community.panodata.org
rails console
DB.query('SELECT "post_custom_fields"."name", "post_custom_fields"."value" FROM "post_custom_fields" WHERE "post_custom_fields"."post_id" = 20 ORDER BY id asc')

Después de eso, el sistema comienza a volverse completamente no responsivo y la carga del sistema se dispara.

`top`

top - 15:28:06 up 158 days, 14:05,  5 users,  load average: 19.28, 12.96, 5.94
Tasks: 289 total,   1 running, 288 sleeping,   0 stopped,   0 zombie
%Cpu(s):  2.3 us,  6.1 sy,  0.3 ni,  1.2 id, 90.0 wa,  0.0 hi,  0.0 si,  0.0 st
MiB Mem :   7006.9 total,     40.1 free,   6177.4 used,    789.4 buff/cache
MiB Swap:   4096.0 total,   2048.4 free,   2047.6 used.    659.2 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
12470 xxx       25   5 9229456  89424   4604 S 117.4   1.2   0:35.66 ruby
   43 root      20   0       0      0      0 D   1.0   0.0  11:46.02 kswapd0
10035 xxx       20   0 7481716   4.2g   6020 D   1.0  61.3   0:12.60 bundle

`iotop`

Total DISK READ:      1373.05 K/s | Total DISK WRITE:        29.79 K/s
Current DISK READ:    1373.05 K/s | Current DISK WRITE:       3.17 M/s
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
27667 be/4 root        0.00 B/s    0.00 B/s  0.00 % 99.99 % sshd -D
10035 be/4 xxx         0.00 B/s    0.00 B/s  0.00 % 99.99 % pry
 4863 be/4 xxx         0.00 B/s    0.00 B/s  0.00 % 99.99 % bash config/unicorn_launcher -E production -c config/unicorn.conf.rb
11145 be/4 xxx         0.00 B/s    0.00 B/s  0.00 % 99.99 % unicorn worker[0] -E production -c config/unicorn.conf.rb
11363 be/4 xxx         0.00 B/s    0.00 B/s  0.00 % 99.99 % sleep 1
11362 be/4 xxx         0.00 B/s    0.00 B/s  0.00 % 99.99 % sleep 1
   43 be/4 root        0.00 B/s    0.00 B/s  0.00 % 99.99 % [kswapd0]
  344 be/4 root       49.66 K/s    0.00 B/s  0.00 % 61.49 % [xfsaild/xvda4]
13189 be/4 xxx         0.00 B/s    9.93 K/s  0.00 % 56.78 % postgres: 12/main: walwriter
13357 be/5 xxx         0.00 B/s    0.00 B/s  0.00 % 20.45 % sidekiq 6.1.2 discourse [0 of 5 busy] [mini_scheduler*]
 5497 be/5 xxx         4.97 K/s    0.00 B/s  2.82 %  0.00 % sidekiq 6.0.5 discourse [0 of 5 busy] [heartbeat]

Solo después de salir de

[3] pry(main)>

el sistema se recupera lentamente y las cosas comienzan a volver a ser responsivas. Parece que hay un serio desgaste de recursos ocurriendo.

DB.query_single('SELECT count(*) FROM "post_custom_fields"')
=> [2002]

DB.query_single('SELECT count(*) FROM "post_custom_fields" WHERE post_id=20')
=> [2]

DB.query_single('SELECT name FROM "post_custom_fields" WHERE post_id=20')
=> ["calendar-details", "post_detected_lang"]

Sin embargo, en cuanto selecciono la columna value, todo se va al traste.

amotl · 7 Diciembre, 2020 14:49

Ja. ¿Esto es razonable?

DB.query_single('SELECT name, length(value) FROM "post_custom_fields" WHERE post_id=20')
=> ["calendar-details", 83361791, "post_detected_lang", 2]

Así que, la columna value de este elemento específico calendar-details contiene aproximadamente 80 MB de datos. Creo que hemos dado en el clavo.

Al parecer, este es el único elemento que lleva una carga tan grande dentro de su valor calendar-details.

print(DB.query("SELECT post_id, length(value) FROM post_custom_fields WHERE name='calendar-details' ORDER BY post_id").map { |r| r.post_id.to_s + ": " + r.length.to_s }.join("\n"))
20: 83361791
84: 113
133: 113
177: 58
223: 2
263: 113
379: 112
385: 130
439: 112
456: 112
457: 58
495: 117
552: 2

amotl · 7 Diciembre, 2020 15:10

Estoy avanzando en la reducción del problema.

DB.query("SELECT topic_id, cooked FROM posts WHERE id=20")
=> [#<#<Class:0x0000560b91760168>:0x0000560b8b841880
  @cooked=
   "<p>Si estás interesado en las actividades en los repositorios de <a href=\"https://github.com/panodata\" rel=\"nofollow noopener\">Panodata GitHub</a>, por favor sigue este hilo.</p>\n<p>Estamos enviando notificaciones push de GitHub a Discourse utilizando el excelente <a href=\"https://github.com/huw/github-to-discourse\" class=\"inline-onebox\" rel=\"nofollow noopener\">GitHub - huw/github-to-discourse: Reenvía datos de commits desde un webhook de GitHub a un hilo de Discourse</a>.</p>",
  @topic_id=16>]

Parece que se trata del primer mensaje del tema Logbook of code changes - The workbench - Panodata Community. Hasta donde puedo confiar en mi propia cordura, nunca asigné a este mensaje nada relacionado con una entrada de calendario.

En realidad, es un tema donde se publican mensajes automáticos usando GitHub - huw/github-to-discourse: Forwards commit data from a GitHub webhook to a Discourse thread · GitHub. Sin embargo, el primer mensaje (id=20) fue creado manualmente.

Falco · 7 Diciembre, 2020 15:42

Bueno, eso significa que no tiene nada que ver con la actualización de PG12, así que no es mi culpa

Sí, como hemos dicho varias veces, usar la tienda de plugins o post_custom_fields para cosas complejas no es sostenible a largo plazo. Por eso, el plugin Calendar está realizando esta migración para pasar a tablas adecuadas.

Desafortunadamente, parece que no llegó lo suficientemente pronto para ti y un evento quedó en un estado incorrecto. Elimina la fila problemática y todo debería volver a funcionar.

amotl · 7 Diciembre, 2020 15:46

¡Definitivamente no! Ya he corregido el título del tema para reflejar el resultado actual. Muchas gracias por tu ayuda y tus comentarios al respecto.

Entiendo. Gracias nuevamente por tus aportes.

De acuerdo, lo haré. Entonces, creo que no hay motivo para investigar más a fondo la causa raíz, ya que ya la conocías y has mejorado la situación modificando el esquema de la base de datos mediante la migración que has mencionado aquí. ¡Gracias!

amotl · 7 Diciembre, 2020 16:01

Hola de nuevo,

[70] pry(main)> DB.query("DELETE FROM post_custom_fields WHERE post_id=20 AND name='calendar-details'")
=> []

Tras eliminar la fila problemática, volvimos a activar discourse-calendar. Tras realizar otro rebuild, podemos informar de que las migraciones faltantes se han aplicado con éxito:

20200226183018 CreateCalendarEvents: migrated (0.8584s)
20200409102640 CreatePostEventsTable: migrated (0.0381s)
20200409102643 RenameTablesToDiscoursePostEvent: migrated (0.1840s)
20201110225115 CreatePostEventDatesTable: migrated (0.0655s)
20201111005205 MoveDataToEventDates: migrated (0.0039s)

y todo vuelve a estar bien.

Muchas gracias por el excelente trabajo que están haciendo aquí.

Saludos cordiales,
Andreas.

Tema		Respuestas	Vistas
'bundle exec rake db:migrate' taking a long time due to calendar migration Self-hosting	33	881	5 Diciembre 2023
Plugin causing errors during rebuild Self-hosting	21	1873	10 Noviembre 2022
Help! Upgrade led to complete fail Self-hosting	8	1352	9 Marzo 2021
40 Fatals and 7 Errors in recent logs Support	13	1633	8 Junio 2024
Restore fails due to disk space on migration because of 70M calendar events Support calendar-and-event	11	159	14 Febrero 2026

La migración de la base de datos se atasca con un valor enorme de un elemento "calendar-details" en la tabla "post_custom_fields"

TLDR;

top

iotop

Temas relacionados

`top`

`iotop`