PostgreSQL extensions provide a way to extend the functionality of a database by bundling SQL objects into a package and using them as a unit.
YugabyteDB supports a variety of PostgreSQL extensions.
These extensions are created and owned by a database. That means that for every new database, you have to create the extension again in those databases.
That is, unless you create the extension in the default template database named template1. Then when you create a new database, it will inherit the settings of the template database, including installed extensions!
Example:
When we list all of the current databases, we see that template1 is one of them:
yugabyte=# \list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------+----------+----------+---------+-------------+-----------------------
postgres | postgres | UTF8 | C | en_US.UTF-8 |
system_platform | postgres | UTF8 | C | en_US.UTF-8 |
template0 | postgres | UTF8 | C | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
yugabyte | postgres | UTF8 | C | en_US.UTF-8 |
(5 rows)
Let’s see which extensions are installed by default in the template1 database.
yugabyte=# \c template1
You are now connected to database "template1" as user "yugabyte".
template1=# SELECT extname FROM pg_extension;
extname
--------------------
plpgsql
pg_stat_statements
(2 rows)
If I create a new database now, it will include the plpgsql and pg_stat_statements extensions.
template1=# CREATE DATABASE my_new_database;
CREATE DATABASE
template1=# \c my_new_database;
You are now connected to database "my_new_database" as user "yugabyte".
my_new_database=# SELECT extname FROM pg_extension;
extname
--------------------
plpgsql
pg_stat_statements
(2 rows)
I would like to have the pgcrypto extension created in all new datbaases. To do that I simply have to create it in the template1 database.
my_new_database=# \c template1;
You are now connected to database "template1" as user "yugabyte".
template1=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION
template1=# SELECT extname FROM pg_extension;
extname
--------------------
plpgsql
pg_stat_statements
pgcrypto
(3 rows)
Now when I create a new database, it will automatically contain the pgcrypto extension.
template1=# DROP DATABASE my_new_database;
DROP DATABASE
template1=# CREATE DATABASE my_new_database;
CREATE DATABASE
template1=# \c my_new_database;
You are now connected to database "my_new_database" as user "yugabyte".
my_new_database=# SELECT extname FROM pg_extension;
extname
--------------------
plpgsql
pg_stat_statements
pgcrypto
(3 rows)
Have Fun!
