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.

you have to run it agaist each db separately. what you can do is writing a bash loop to run it against all dbs — Vao Tsun
– Vao Tsun, Commented Feb 9, 2017 at 14:35

Nick Barnes · Accepted Answer · 2017-02-09 15:04:19Z

36

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.

answered Feb 9, 2017 at 15:04

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Basil Bourque Over a year ago

Good of you to include real example script example.

Matteo Over a year ago

how to do the same thing without using psql?

Nick Barnes Over a year ago

@Madeo: It would be the same with any other client, i.e. connect to the "postgres" database, run the SELECT datname query to get a list of all other databases, then connect to each database in the list to run your CREATE EXTENSION.

Matteo Over a year ago

@NickBarnes I am using the initdb script in Docker and I can only run .sql files.

Nick Barnes Over a year ago

@Madeo: If you can only run plain SQL scripts, then you can only create extensions in the database you're connected to. But the docs contain an example of an initdb .sh script which calls psql, so it looks like this should work.

Suraj Rao · Accepted Answer · 2022-04-08 04:04:30Z

#!/bin/bash export PGPASSWORD='Xyz123'; for DB in $(psql -h localhost -U postgres -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1')"); do psql -U postgres -d $DB -c "CREATE EXTENSION IF NOT EXISTS pg_buffercache" done The script is only working for last database and for remaining dbs i am getting this error **" **does not existAL: database "debdbi123****. Please help on fixing the issue ++ psql -h localhost -U postgres -t -c 'SELECT datname FROM pg_database WHERE datname NOT IN ('\''postgres'\'', '\''template0'\'', '\''template1'\'')' + for DB in $(psql -h localhost -U postgres -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1')") + psql -U postgres -d $'debdbi123\r' -c 'CREATE EXTENSION IF NOT EXISTS pg_buffercache' **" **does not existAL: database "debdbi123**** + for DB in $(psql -h localhost -U postgres -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1')") + psql -U postgres -d 1 -c 'CREATE EXTENSION IF NOT EXISTS pg_buffercache' NOTICE: extension "pg_buffercache" already exists, skipping --

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Postgres CREATE EXTENSION All Databases

2 Answers 2

5 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Related