Postgresql template troubles

01 Aug 2019

I’ve been doing a lot of Drupal work lately. For the last couple of days I’ve been struggling with the deployment of a website I’m working on. For some reason one of the standard Drupal tables in the Postgresql database ended up being owned by another Postgresql role. This resulted in an error in the deployment. I have literally been staring at the error for two days, I could not figure out where it went wrong. After a lot of searching on the internet I finally found out what the problem was!

Postgresql uses a database called template1 when a new database is created as a template. For some reason there was a relation defined in template1 for the table that caused the error in the deployment. The relation defined the table to be owned by another Postgresql role. The problem I had could be solved by removing the template1 database, but this is only possible when you tell Postgresql the template is not a template anymore (because template databases cannot be dropped). So I had to issue the following command:

update pg_database set datistemplate = false where datname = 'template1';

Now you can

drop database template1;

after which you can recreate it using

create database template1 template template0;

Now I was finally able to deploy the website. Well, until another problem raised it’s ugly head. But that is a problem for tomorrow.

Productivity is for machines

11 May 2019