search

plausible / community-edition

Example Docker Compose setup for hosting Plausible Community Edition
1.45k stars 304 forks source link

Restoring Postgres from a backup #171

Closed Aboitier closed 1 month ago

Aboitier commented 1 month ago

Hello,

I want to restore the pg plausible_db database from a backup I made. I tried with both pg_restore and pg_dumpall and the exact same error shows up right after restoring in the plausible container logs : 

Loading plausible..
Starting dependencies..
Starting repos..
Plausible.Repo database already exists
Plausible.IngestRepo database already exists
Creation of Db successful!
Loading plausible..
Starting dependencies..
Starting repos..
** (Postgrex.Error) ERROR 42701 (duplicate_column) column "native_stats_start_at" of relation "sites" already exists
    (ecto_sql 3.12.0) lib/ecto/adapters/sql.ex:1078: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.17.1) lib/enum.ex:1703: Enum."-map/2-lists^map/1-1-"/2
    (ecto_sql 3.12.0) lib/ecto/adapters/sql.ex:1185: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.12.0) lib/ecto/migration/runner.ex:348: Ecto.Migration.Runner.log_and_execute_ddl/3
    (elixir 1.17.1) lib/enum.ex:1703: Enum."-map/2-lists^map/1-1-"/2
    (ecto_sql 3.12.0) lib/ecto/migration/runner.ex:311: Ecto.Migration.Runner.perform_operation/3
    (stdlib 6.0) timer.erl:590: :timer.tc/2

The backup is made from a postgres v14. I tried to restore it to a v14, a v15 and a v16 and I always get the same error. I wiped clean the pg plausible_db several times. I am using Plausible 2.1.4.

Do you have any hint on how to approach this ?

ruslandoga commented 1 month ago

👋 @Aboitier

Please share the commands you used and whether any of the Docker Compose containers were running at the time. Note that in the default configuration plausible attempts to run migrations (i.e. create the missing tables) on startup: https://github.com/plausible/community-edition/blob/938f5510ed021537b9a5b204d1e8468b7ba5744e/compose.yml#L34

ruslandoga commented 1 month ago

And please check out https://github.com/plausible/community-edition/wiki/Upgrade-PostgreSQL

It contains an easy guide on a backing up and restoring plausible_db

Aboitier commented 1 month ago

Thanks for the quick answer @ruslandoga :)

I am indeed running the default configuration with this command : command: sh -c "sleep 10 && /entrypoint.sh db createdb && /entrypoint.sh db migrate && /entrypoint.sh run"

I removed the /entrypoint.sh db createdb && /entrypoint.sh db migrate from the above command and followed the steps described in the link you shared. Postgres upgraded and restored successfully 🎉

On to Clickhouse now !

ruslandoga commented 1 month ago

Glad you got it worked out!

ruslandoga commented 1 month ago

On to Clickhouse now !

These might be helpful: