Compose Notes details the small enhancements and changes which make life better for Compose users. In this edition, there's a lot happening in the PostgreSQL department.
Smoother version updating
PostgreSQL 9.4.8 and 9.5.3 are now available on Compose. The updated versions are also the default for new Compose deployments. With these minor version updates, we're also making the ability to explicitly do minor upgrades to the Compose console for PostgreSQL 9.5 users. With 9.4, PostgreSQL deployments stayed with the version they were initially provisioned with and only changed with the intervention of our trusty support crew.
Now, we've moved that control into your hands. Just go to the Settings view in the console, and if a later minor update of your current major version of PostgreSQL, 9.5, is available, then you'll be able to select that and start the update. Please be aware this involves a restart of your database instances so you'll want to ensure none of your applications will be disturbed by a brief restart.
For the major version updates, 9.4 to 9.5 and onwards, you'll still need to do the "restore from backup" process we talk about in this article.
PostgreSQL more directly
In a previous Compose Notes, we mentioned the new capability in our PostgreSQL data browser to run SQL commands directly on the server. Well, since then that feature's had its first enhancement. Now those SQL commands can be saved for later use with a new shared command list:
You'll note the new Save button which will save the query with the name from the field above the query text area. You can also delete a query by name. These queries are shared with all PostgreSQL instances under a Compose account currently, though we want to hear about how you would make this feature work for you.
We've also added a "SET SESSION statement_timeout" command to the SQL template so you can quickly ensure your query will return in a set time; feel free to remove it but it's good insurance against a typo giving you a runaway query.
PostgreSQL FDW FTW
We've just added support for PostgreSQL's postgres_fdw, the foreign data wrapper that lets you make queries on a different PostgreSQL server. We'll be having an in depth look at the postgres_fdw (and dblink) extensions in an imminent article here on Compose Articles.
Update: You can read that article, Cross-Database Querying in Compose PostgreSQL, now.
The Compose Transporter is currently being enhanced in a new experimental branch of the Transporter repository. Among the enhancements that are being worked on is the addition of a PostgreSQL adaptor. This will allow data to flow in and out of PostgreSQL from other supported adaptors that already include MongoDB, Elasticsearch and RethinkDB.
If you want to try out the ongoing work, check out the experimental branch. We'll let you know when there's an easy-to-use downloadable binary release for the experimental branch and when we move this work to the mainstream. As an open source project, we're happy to work with the community to improve the Transporter too; give to code a try, report issues or develop your own enhancements.
That's it for this Compose Notes, covering the added power and control arriving for PostgreSQL users on Compose.