CODEWITHSUNDEEP
databasepostgresql
Chad M
Chad M

Reputation: 1003

Postgres CREATE EXTENSION All Databases

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.

Upvotes: 17

Views: 22593

Answers (2)

Debojit Debnath
Debojit Debnath

Reputation: 1

#!/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
--

Upvotes: 0

Nick Barnes
Nick Barnes

Reputation: 21385

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.

Upvotes: 35

Related Questions