Docker Container keeps crashing when upgrading to v2.0

[julianengel]

Hi all,

I am running Plausible through CapRover.

I am trying to install the new V2 release. I've managed to upgrade the clickhouse DB without any problem.

The issue arises when trying to update the main image. CapRover uses the docker API, so docker-compose doesn't work.
When the guide instructs us to:

$ cd hosting
$ docker compose stop plausible
$ docker compose rm plausible
$ docker compose up -d

Instead I can do a deploy directly from the image, specifically plausible/analytics:v2.0.0

This works, the image spins up, but then after a few seconds it crashes. This is not enough time for me to docker exec into it, and run the migration script.

The error which kills the process is ERROR 42703 (undefined_column) column g1.site_id does not exist.

Following some other issues and discussions here from Github, I could figure out that it crashes because the Postgres DB doesn't have this table. However, because the container crashes within a few seconds, I can't run the migration script.

Is there any way I could create the minimum required tables directly in the Postgres container so that the. plausible container start-up, without any data, and then exec into it and run the migration?

Any help would be much appreciated!

The full error just in case:

> GenServer Plausible.Site.Cache.Warmer terminating
> 2023-07-28T14:09:09.379756373Z ** (Postgrex.Error) ERROR 42703 (undefined_column) column g1.site_id does not exist
> 2023-07-28T14:09:09.379760066Z 
> 2023-07-28T14:09:09.379762957Z     query: SELECT s0."id", s0."domain", s0."domain_changed_from", s0."ingest_rate_limit_scale_seconds", s0."ingest_rate_limit_threshold", g1."id", g1."event_name", g1."page_path", g1."currency", g1."site_id", g1."inserted_at", g1."updated_at", s0."domain", s0."domain_changed_from" FROM "sites" AS s0 LEFT OUTER JOIN "goals" AS g1 ON (g1."site_id" = s0."id") AND NOT (g1."currency" IS NULL)

[julianengel]

Referencing the following other issues/discussions:
#3047
#3042

[ruslandoga]

👋 @julianengel

I wonder if you can execute a one off command like

$ docker run -ti --rm plausible/analytics:v2.0.0 /entrypoint.sh db migrate

with CapRover?

[julianengel]

Hi @ruslandoga,

Thanks so much for the super fast reply. I've had to modify this a bit into a new dockerfile, which looks like this:

# Use the base image
FROM plausible/analytics:v2.0

# Set the working directory
WORKDIR /plausible

# Sleep for 10 seconds
RUN sleep 10

# Run the commands to create the database and perform migrations
RUN /entrypoint.sh db migrate

# Start the plausible service
CMD ["/entrypoint.sh", "run"]

However this throws the following error:

{"code":1,"message":"The command '/bin/sh -c /entrypoint.sh db migrate' returned a non-zero code: 1"}
The command '/bin/sh -c /entrypoint.sh db migrate' returned a non-zero code: 1
Build has failed!

The database runs as a separate container, and I'm trying to get the new V2 version to work on the main plausible container. Just because it failed a few times, I've reverted back to the previous version (pre-1.5) and everything works the way it should.

Now, I am trying to get this to a stable container in the plausible-v2 container, with no luck, as it keeps on crashing.
plausible-v2 is a copy of the main plausible instance, with the same environment variables and access to all other containers (as the Postgres one logs the error of the missing table)

[ruslandoga]

RUN is executed at image build-time (and you probably don't have all the ENV set up so it fails). You probably want to put it into CMD to make it run in the container:

CMD /entrypoint.sh db migrate & /entrypoint.sh run

[julianengel]

Thank you! You're right, I didn't catch that. Let me give it a try :)
EDIT: This solved it. If you scroll down, all steps for CapRover upgrade are outlined here:

[ruslandoga]

I think the new image is unnecessary as you can provide the same command to the existing one:

$ docker run -ti --rm plausible/analytics:v2.0.0 "/entrypoint.sh db migrate & /entrypoint.sh run"

[julianengel]

Thank you @ruslandoga! This managed to get it to be up and work as expected again. I have one more quick question, I have the running instance of plausible now, but trying to docker exec srv-captain--plausible bin/plausible rpc Plausible.DataMigration.NumericIDs.run throwing OCI runtime exec failed: exec failed: unable to start container process: exec: "bin/plausible": stat bin/plausible: no such file or directory: unknown. Any idea how I can do the data migration without docker compose?

Is the migration happening in Clickhouse or in Postgres? Can I do it manually?

[ruslandoga]

This is probably due to your new image, it doesn't have the same WORKDIR /app set as the original one.

Try passing the absolute path:

$ docker exec -ti srv-captain--plausible /app/bin/plausible rpc Plausible.DataMigration.NumericIDs.run

Note that you'd need to pass -ti flags make it interactive (you need to type stuff like yes/no during the migration script)

[julianengel]

Thank you! Using CMD instead of RUN fixed the issue.

CapRover upgrade of Plausible One-Click-App to V2:

1. Perform a manual update of the image using method 4:

Method 4: Deploy plain Dockerfile.

# Use the base image
FROM plausible/analytics:v2.0

# Set the working directory
WORKDIR /plausible

CMD /entrypoint.sh db migrate & /entrypoint.sh run

2. Run the data migration:
   Once the container is running, it's time to run the migration script with docker exec instead of docker-compose exec.

   1. Run a quick docker ps to get the container ID.
   2. Run docker exec -it CONTAINER_ID_FROM_COMMAND bin/plausible rpc Plausible.DataMigration.NumericIDs.run

3. Access your dashboard as normal. You have just upgraded to V2!

PS: You can continue to drop the v1 tables as per the upgrade guide if needed :) To do that, run docker ps to find the clickhouse container id. Then run the following command: docker exec -it CONTAINER_ID_CLICKHOUSE clickhouse-client and proceed with the command.

Thank you so very much to @ruslandoga, this wouldn't have been possible without him, and he saved me hours of frustration in a matter of minutes.

[ruslandoga]

RUN sleep 10 is probably useless as it sleeps during image build, not CMD. Although I'm not familiar with CapRover so...

[julianengel]

Thank you! Updated :)