Restarting Postgres to install TimescaleDB

I’m trying to install TimescaleDB on a Fly postgres cluster, following:

As the last step of the process, I need to restart the Postgres cluster. When using service postgresql restart, I receive the error No PostgreSQL clusters exist; see "man pg_createcluster". Restarting the Fly app doesn’t work either, as it seems to delete the TimescaleDB installation completely.

Any ideas on how to resolve this? Or any alternative ways to install TimescaleDB?

Thanks!

Thanks! This is actually pretty much the approach I have taken (create a normal PG HA app and then ssh in to install Timescale afterwards).

I might be missing something, but the linked answer seems to use the same steps (just targeting CentOS instead of Debian) to install and restart Timescale as the documentation I linked. I still can’t get it to work, I’m afraid.

Thanks! This is actually pretty much the approach I have taken (create a normal PG HA app and then ssh in to install Timescale afterwards).

You will need to modify the Dockerfile to include your required dependencies. If the required dependencies are not part of the underlying image, they will be blown away when the VM is restarted.

That makes sense, thanks! Sorry if this is a stupid question, but what would be the best way to go about doing this? Can it be achieved through the fly pg interface or would I need to create a standalone app from the Dockerfile and attach a volume manually (and would this still enable features such as scaling and snapshot creation/restoration)?

I’d suggest forking the repository we use, so it’ll still have all the internal Fly management commands. You’ll also want to keep it up to date as we add more commands.

So you can start a Fly PG instance, fork the repo, add Timescale to the Dockerfile, deploy the new repo to the Postgres application. At that point Fly can still manage it for you, and your additions will still be in place.

I tried:

  • Start a Fly PG instance,
  • Fork the Fly HA PG repo, add Timescale
  • Change the app name in the fly.toml to match the deployed PG instance
  • Run fly deploy

But get an error I can’t quite figure out:

My updated Dockerfile: Dockerfile · GitHub

This indicates that one of the health checks is failing — fly checks list should tell you which one.

This seems to be the failing health check:

Tried deleting and recreating the PG app before deploying the updated repo, but no luck.

Try commenting out the health checks in fly.toml and see what happens: postgres-ha/fly.toml at main · fly-apps/postgres-ha · GitHub

That error means the built in haproxy can’t connect to postgres for some reason. It doesn’t log much, sadly, but if you can get it running without health check you can do this:

  1. Run fly proxy 8404:8404 (port 8404 is the haproxy diagnostics page)
  2. Visit http://localhost:8404/stats

You should see a table like this:

If you’re feeling ambitious, you can connect to your organization through Wireguard and run health checks directly, too. Or use that same fly proxy command:

fly proxy 5500:5500

curl http://localhost:5500/flycheck/pg

I managed to get it running without the healthchecks:

I’m a complete beginner when it comes to haproxy, but to me this looks like it can’t connect to any of the pg instances.

(Really appreciate the help from all of you btw, super useful)

That seems like it’s not discovering any hosts. It looks for $PRIMARY_REGION.$FLY_APP_NAME.internal.

Did you happen to set the primary region env var? It is set to ord in the fly.toml, you may need to change that to ams (assuming you’re running in Amsterdam).

That’s it, got the app running now - thanks!

The last step needed to have Timescale installed is being able to customize the postgresql.conf to include shared_preload_libraries = 'timescaledb' before the postgres service is started (alternatively restarting the service).

I tried altering the config in flypg/config.go (no effect) and updating postgresql.conf from the Dockerfile (doesn’t appear to have access to the volume where the file is located during build).

I might be missing something obvious, but is there a good way to customize the postgresql.conf file?