Selfhost Plausible with a preexisting PostgreSQL Database

Plausible is a great analytics platform to get basic information about page visits. The great thing about it is that it is privacy friendly and GDPR compliant. It does not use any cookies and does not track users.

Plausible has a self-hosted and cloud option. The cloud option does cost money while the self-hosted one is completely free of charge.

They have documentation on how to set up the self-hosted option with docker-compose. Unfortunately, it expects you to set up a new Postgres DB. There is no documentation on how to use an existing one.

With the help of this GitHub issue, I managed to do it. These are the necessary steps:

First you need to change the entry point in the docker-compose file. In the example the the statement is as follows: command: sh -c "sleep 10 && /entrypoint.sh db createdb && /entrypoint.sh db migrate && /entrypoint.sh db init-admin && /entrypoint.sh run". We need to remove the entrypoint.sh db createdb && part.

The entry now should look like this.

1plausible:
2  image: plausible/analytics:v1.3
3  container_name: plausible
4  command: sh -c "sleep 10 && /entrypoint.sh db migrate && /entrypoint.sh dbinit-admin && /entrypoint.sh run"
5  depends_on:
6  ...
7

Also don’t forget to remove the postgres entry from the example compose files.

Then create a new database in your existing Postgres instance. You need to add the DB connection details to the Plausible env file. Unfortunately, I could not get it to work with a separate DB User for that new Database and got an error when Plausible tried to install a Postgres extension. So I used the default Postgres user which is quite suboptimal because it has admin rights. If you know of any solution to this please let me know!

Update the example env file and set the connection details to your new database:

1...
2DATABASE_URL=postgresql://postgres:{password}@{host}:{port}/{new_db_name}
3...
4

Now you can start the plausible containers with docker-compose up -d

Finally, we need to ssh into the Clickhouse docker container. In there we need to manually create a new database because we removed the part that would have done this from the entry point. Just execute the following command:

1docker exec -it clickhouse-plausible /bin/bash
2clickhouse-client --query "CREATE DATABASE IF NOT EXISTS plausible_events_db"
3

Plausible should now start correctly and you can log in with the email and password from the env file.

If you don’t want to use an email server with Plausible like me, there is another problem. Plausible wants the admin account activated by email. We can surpass this by manually updating a column in the Postgres database.

1docker exec -it postgres /bin/bash
2psql -U postgres
3\c {new_db_name}
4update users set email_verified = true where 1 = 1;
5