3

I have a PostgREST instance deployed on Heroku using the buildpack.

The Postgres schemas are created by a Node.js program that uses node-pg-migrate.

After the migrations have run, the schema is changed and PostgREST needs to reload the schema to update its schema cache.

To refresh the cache without restarting the PostgREST server there's the option to send the server process a SIGHUP signal: killall -HUP postgrest

Since I have the migrations running from a Node.js program (npm run migrate:up) it seems to make sense to send that signal with a post-migration script. I'm not even sure if I can send such a signal from another server to the PostgREST instance.

Basically, what I'm asking is how to send a SIGHUP signal to PostgREST on Heroku from a Node.js program on another server.

Christiaan Westerbeek
  • 10,619
  • 13
  • 64
  • 89

1 Answers1

2

One way to trigger the schema reload across servers is to have your post-migration script run NOTIFY postgrest_reload; in the database to which PostgREST is attached. Then on the same server as PostgREST, run a tool like pg_listen to catch that event and send the sighup. For instance: pg_listen <db-uri> postgrest_reload "killall -HUP postgrest".

You can even make PostgREST automatically reload its schema cache whenever the schema changes (using a DDL trigger), as explained in https://postgrest.com/en/v4.4/admin.html#schema-reloading

Joe Nelson
  • 549
  • 5
  • 12
  • 1
    However on Heroku it would require modifying the buildpack to include pg_listen. We've been looking into adding LISTEN support right inside the postgrest binary, but it requires a feature in our postgresql access library that is not yet available. – Joe Nelson Mar 12 '18 at 05:56