I have an existing Rails 7 / PostgreSQL app I can run locally and is currently deployed on Heroku. I am migrating it to a VPS running Ubuntu 22 LTS (originally Ubuntu 20 LTS) but when I attempted to run the migrations (directly or via Capistrano) they failed with the error:
PG::UndefinedFunction: ERROR: function gen_random_uuid() does not exist
The first (of ten!) migrations was failing so, rather than rewrite history and insert/modify an early migration, I fixed it on the server by manually creating the pgcrypto extension:
myApp$ sudo su - postgres
postgres$ psql
postgres=# \c myapp_production
myapp_production=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION
myapp_production=# exit
As far as I recall I didn't do anything to enable the extension on my Mac, I simply ran rails new myapp --database=postgresql and used create_table :people, id: :uuid do |t| in my first migration.
Why did I need to create/enable this extension on Ubuntu but not macOS or Heroku?
On my Mac I'm using Postgres 14.5 (psql (PostgreSQL) 14.5, Homebrew). On the Ubuntu 22 LTS server I'm also using Postgres 14.5 (psql (PostgreSQL) 14.5, Ubuntu 14.5-0ubuntu0.22.04.1).
When I first noticed it I was using a different version of PostgreSQL on the server (psql (PostgreSQL) 12.12, Ubuntu 12.12-0ubuntu0.20.04.1).
According to the Heroku Postgres web dashboard I'm using Postgres 13.9 on Heroku.
Even stranger, when I check installed extensions for that database in both development and prod, pgcrypto is not even listed:
# macOS using psql
myapp_development=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
# Heroku using `heroku pg:psql`
myapp::DATABASE=> \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)
How are my :uuid columns working on these two platforms!?
The explanation is simple after all:
gen_random_uuid() was added to the main distribution with Postgres 13. (Check the Postgres version with SELECT version();)
This is unrelated to the OS of the server.
This is also unrelated to the version of the interactive terminal psql (which can differ from the server version).
The release notes of Postgres 13:
Add function
gen_random_uuid()to generate version-4 UUIDs (Peter Eisentraut)Previously UUID generation functions were only available in the external modules uuid-ossp and pgcrypto.