Postgres CREATE EXTENSION All Databases

Question

I've got postgres installed in multiple environments. In each of those environments, I have 2+ databases.

If I have superuser database rights, is there a way to install the CITEXT extension for all databases on a given postgres install?

As of right now, once logged into an environment and in a postgres console, I have to run CREATE EXTENSION IF NOT EXISTS citext; for each database.

Answer 1

The CREATE command does need to be run individually on each database, but you can easily automate this with a shell script, e.g.:

for DB in $(psql -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1')"); do
  psql -d $DB -c "CREATE EXTENSION IF NOT EXISTS citext"
done

If you want citext to be included by default whenever you create a new database, you can also install the extension in template1.