The PostgreSQL extensions ecosystem is extremely robust and provides a wide array of additional capabilities to PostgreSQL. As we mentioned in our launch blog, we want to provide you with all of the tools you need to operate production databases on the ObjectRocket platform, so we’ve recently added the ability to use extensions. The extensions act as plug-ins, and provide extra functionality and features.
Viewing the Available Extensions
We include a number of extensions for our Postgres instances and we’ve tried to cover the most common and requested plug-ins available. To see the list of our supported plug-ins, you can connect to a running instance on our service and use the following queries:
`SELECT * FROM pg_available_extensions;` will show a super list of all of the extensions in the postgres extensions directory.
`SHOW extwlist.extensions;` will show the allowed extensions on our service.
The two lists will differ slightly, because there are some extensions included with the Postgres packages by default that we don’t provide user access. If there is an extension you need that’s not in the whitelist or not available at all, you can always work with our support team to get it added to our platform.
To save you some time, here is the current list of both available and allowed extensions on the ObjectRocket service:
I started with the query approach, because we’ll regularly be adding extensions, as needed, so running the query will always provide the most up-to-date list.
Loading extensions on the service works no differently than it usually does in Postgres. You first issue the CREATE EXTENSION command in the database that you want to add the functionality to.
Let’s show a very simple example. The “uuid-ossp” extension provides utilities to manage UUIDs. Let’s try to use the “uuid_generate_v1()” function.
As you can see, the function fails initially, because we have not yet loaded the extension. However, after loading the “uuid-ossp” extension, we try to use the same function and we see that it now returns a UUID for us.
Once again, this is a very simple example and extensions can do much much more, but you can see the basic mechanics.
Be aware that extensions are only active/usable in the databases you load them in. In other words, every time you create a new database, you’ll need to run CREATE EXTENSION in that database. If you’d like an extension to be available in new databases automatically, you’ll need to either create a new database template and use that when creating new databases, or update the template1 database to include that extension (since by default all new databases are a copy of template1).
Enjoy the new extensions, and let us know if you have any requests for adding specific functionality.