There's more…

Another system view, pg_available_extension_versions, shows all the versions available for each extension. It can be valuable when there are multiple versions of the same extension available at the same time; for example when making preparations for an extension upgrade.

When a more recent version of an already installed extension becomes available to the database server, for instance, because of a distribution upgrade that installs updated package files, the super user can perform an upgrade by issuing the following command:

ALTER EXTENSION myext UPDATE TO '1.1';

This assumes that the author of the extension taught it how to perform the upgrade.

Extensions interact nicely with logical backup and restore nicely, a topic that will be fully discussed in Chapter 11, Backup and Recovery. As an example, if your database contains the cube extension, then you will surely want a single line (CREATE EXTENSION cube;) in the dump file instead of lots of lines recreating each object inpidually, which is inefficient and also dangerous.

The CASCADE option is accepted also by the CREATE EXTENSION syntax, with the meaning of "issue CREATE EXTENSION recursively to cover all dependencies." So, instead of creating an extension cube before creating the extension earthdistance, you could have issued the following command:

postgres=# CREATE EXTENSION earthdistance CASCADE; 
NOTICE:  installing required extension "cube" 
CREATE EXTENSION

Remember that CREATE EXTENSION ... CASCADE will only work if all the extensions it tries to install have already been placed in the appropriate location.