Postgres connection error

matlag commented 2 years ago

Describe the bug

Service starts, front-end works, but no backend: front-end shows same timeline since problem started (displays last toot from cache from several days ago). Logs show PostgreSQL connection fails systematically at start.

Context

Hardware: Kimsufi KS7
YunoHost version: 4.2.8.3
I have access to my server: Through SSH
Are you in a special context or did you perform some particular tweaking on your YunoHost instance?: no
Using, or trying to install package version/branch:Pleroma 2.4.1~ynh1

Steps to reproduce

-Stop service -Start service

Expected behavior

Normal start of service. Front-end continuously updates timeline

Logs

In logs, domain name replaced with domain.ext journalctl -f -u pleroma.service

Sep 23 17:09:01 domain.ext systemd[1]: Started pleroma social network.
Sep 23 17:09:21 domain.ext  pleroma[4206]: 17:09:21.453 [info] Running Pleroma.Web.Endpoint with cowboy 2.9.0 at 127.0.0.1:8096 (http)
Sep 23 17:09:21 domain.ext  pleroma[4206]: 17:09:21.453 [info] Access Pleroma.Web.Endpoint at https://pleroma.domain.ext 
Sep 23 17:09:21 domain.ext t pleroma[4206]: 17:09:21.455 [info] Gopher server disabled
Sep 23 17:09:21 domain.ext t pleroma[4206]: 17:09:21.461 [info] Postgrex.Protocol (#PID<0.6732.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.6843.0> exited
Sep 23 17:09:21 domain.ext  pleroma[4206]: 17:09:21.469 [info] Application pleroma exited: :stopped
Sep 23 17:09:21 domain.ext  pleroma[4206]: 17:09:21.863 [info] Running Pleroma.Web.Endpoint with cowboy 2.9.0 at 127.0.0.1:8096 (http)
Sep 23 17:09:21 domain.ext  pleroma[4206]: 17:09:21.864 [info] Access Pleroma.Web.Endpoint at https://pleroma.domain.ext 
Sep 23 17:09:21 domain.ext  pleroma[4206]: 17:09:21.865 [info] Gopher server disabled
Sep 23 17:09:22 domain.ext  pleroma[4206]: 17:09:22.626 [info] GET /notice/ABOwPq6yAxqBmUByz2
Sep 23 17:09:22 domain.ext  pleroma[4206]: 17:09:22.663 request_id=FqePrOHGkVKwnc0AAAHh [info] Sent 200 in 37ms
Sep 23 17:09:26 domain.ext  pleroma[4206]: 17:09:26.143 [info] GET /api/v1/timelines/home

And more logs after, but no more reference to PGSQL.

tail -f /var/log/postsgresql/postgresql-11-main.log

2021-09-23 17:09:21.486 EDT [4361] pleroma@pleroma ERROR:  canceling statement due to user request
2021-09-23 17:09:21.486 EDT [4361] pleroma@pleroma STATEMENT:  SELECT distinct split_part(u0."nickname", '@', 2) FROM "users" AS u0 WHERE (u0."local" != $1)
2021-09-23 17:09:21.486 EDT [4361] pleroma@pleroma LOG:  could not send data to client: Broken pipe
2021-09-23 17:09:21.487 EDT [4361] pleroma@pleroma FATAL:  connection to client lost
2021-09-23 17:10:03.967 EDT [4382] pleroma@pleroma ERROR:  canceling statement due to user request
2021-09-23 17:10:03.967 EDT [4382] pleroma@pleroma STATEMENT:  SELECT a0."id", a0."data", a0."local", a0."actor", a0."recipients", a0."inserted_at", a0."updated_at", o1."id", o1."data", o1."inserted_at", o1."updated_at", NOT (t2."id" IS NULL) FROM "activities" AS a0 INNER JOIN "objects" AS o1 ON (o1."data"->>'id') = COALESCE(a0."data"->'object'->>'id', a0."data"->>'object') LEFT OUTER JOIN "thread_mutes" AS t2 ON (t2."user_id" = $1) AND (t2."context" = a0."data"->>'context') WHERE ($2 && a0."recipients") AND (a0."local" = TRUE) AND (a0."data"->>'type' = ANY($3)) AND (not (a0."actor" = ANY($4))) AND (((not (a0."recipients" && $5)) or a0."actor" = $6)) AND (recipients_contain_blocked_domains(a0."recipients", $7) = false) AND (not (a0."data"->>'type' = 'Announce' and a0."data"->'to' ?| $8)) AND ((not (split_part(a0."actor", '/', 3) = ANY($9))) or a0."actor" = ANY($10)) AND ((not (split_part(o1."data"->>'actor', '/', 3) = ANY($11))) or (o1."data"->>'actor') = ANY($12)) AND (not (a0."actor" = ANY($13))) AND (not (a0."data"->'to' ?| $14) or a0."actor" = $15) AND (t2."user_id" IS NULL) AND (not ( a0."data"->>'type' = 'Announce' and a0."actor" = ANY($16))) AND (NOT (a0."actor" = ANY($17))) AND (not(o1."data"->>'type' = 'Answer')) AND (not(o1."data"->>'type' = 'ChatMessage')) AND (NOT (a0."actor" = ANY($18))) AND (not (coalesce(a0."data"->'cc', '{}'::jsonb) ?| $19)) ORDER BY a0."id" desc nulls last LIMIT $20
2021-09-23 17:10:03.967 EDT [4382] pleroma@pleroma LOG:  could not send data to client: Broken pipe

I found some possible tweaks on the config file, so I tried these in config.exs (DB password replaced with ********** , but I can manually connect to the DB from a psql terminal copy-pasting the password from the config file, so that's not the DB credentials.

config :pleroma, Pleroma.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: "pleroma",
  password: "********************",
  database: "pleroma",
  hostname: "localhost",
  pool_size: 30,
  timeout: 60_000

config :pleroma, :dangerzone, override_repo_pool_size: true

But I got the same result, so I reverted the change.

I have no idea where to go from here.

yalh76 commented 2 years ago

Maybe you should upgrade to 2.4.2~ynh1, to see if you still have the issue

matlag commented 2 years ago

Sorry, forgot to come back on this: no improvement with the latest version.

matlag commented 2 years ago

I ended up running a full vacuum jpb on the database as documented here:

https://docs-develop.pleroma.social/backend/administration/CLI_tasks/database/

And it worked! I've now run for a few days without issue. So apparently it was more a maintenance issue on my end than the package itself.

Closing!

YunoHost-Apps / pleroma_ynh