PostgreSQL 9.4 is now available and our Future Plan for database versioning
Since the begining of Scalingo, we’ve developped 5 data store addons “in house” for MySQL, PostgreSQL, MongoDB, Redis and Elasticsearch. Since the beginning our PG addon was based on version 9.3. And since the beginning we have a great plan to handle database versioning correctly. Initially we wanted to have versioning enabled and then released an updated PG addon with a nice way for our users to easily change the version of their database addon (with just a click). However, since this great plan takes a little bit more time to be completed, we took the decision to release an updated PG addon with version 9.4 without waiting for a better database versioning to be released.
What’s new in PG 9.4?
By far, the biggest change in PostgreSQL 9.4 is the ability to use the type JSONB in your tables. This type let you store raw JSON data in your PostgreSQL database. Multiple new support functions enables you to extract and manipulate JSON data.
With the new JSONB data type for PostgreSQL, you no longer have to choose between relational and non-relational data stores: you can have both at the same time.
We won’t go into the debate to know if it’s better to use PG 9.4+JSONB or MongoDB. You can get both on Scalingo :)
You can find a detailed changelog at the following address: https://wiki.postgresql.org/wiki/What’s_new_in_PostgreSQL_9.4
Scalingo PG Addon features
In the previous versions we have not been transparent on the enabled extensions for PostgreSQL. From this release, the following list will be available in all new databases.
It contains different common built-in modules as well as Postgis 2.1 which will let you store and query geographic objects if your application needs to.
| Extension Name | Version | Description |
|---|---|---|
| btree_gist | 1.0 | support for indexing common datatypes in GiST |
| citext | 1.0 | data type for case-insensitive character strings |
| cube | 1.0 | data type for multidimensional cubes |
| dblink | 1.1 | connect to other PostgreSQL databases from within a database |
| dict_int | 1.0 | text search dictionary template for integers |
| earthdistance | 1.0 | calculate great-circle distances on the surface of the Earth |
| fuzzystrmatch | 1.0 | determine similarities and distance between strings |
| hstore | 1.3 | data type for storing sets of (key, value) pairs |
| intarray | 1.0 | functions, operators, and index support for 1-D arrays of integers |
| isn | 1.0 | data types for international product numbering standards |
| ltree | 1.0 | data type for hierarchical tree-like structures |
| pg_stat_statements | 1.2 | track execution statistics of all SQL statements executed |
| pg_trgm | 1.1 | text similarity measurement and index searching based on trigrams |
| pgcrypto | 1.1 | cryptographic functions |
| pgrowlocks | 1.1 | show row-level locking information |
| plpgsql | 1.0 | PL/pgSQL procedural language |
| postgis | 2.1.8 | PostGIS geometry, geography, and raster spatial types and functions |
| postgis_tiger_geocoder | 2.1.8 | PostGIS tiger geocoder and reverse geocoder |
| postgis_topology | 2.1.8 | PostGIS topology spatial types and functions |
| tablefunc | 1.0 | functions that manipulate whole tables, including crosstab |
| unaccent | 1.0 | text search dictionary that removes accents |
| uuid-ossp | 1.0 | generate universally unique identifiers (UUIDs) |
How to upgrade
- Go on the dashbaord of your application
- Click on the Addons tab
- Follow the Link to dashboard anchor located under your addon icon
- Click on the button Upgrade to 9.4.5-1
Wait a few seconds, it’s done your database is now running PG 9.4 and your data have been migrated from the old version to this one.
This is the upgrade dialog you will get:
Of course, if you don’t bother with the data, you can always deprovision your current addon and reprovision a new one.
Just a Docker image
It’s not a secret that we’re using Docker internally. Thus, this release of PG 9.4 is associated with an image, which is open to anyone to use on it’s workstation.
docker run -e DB_USER=myuser \
-e DB_PASSWORD=secretpassword \
-e DB_ADMIN_PASSWORD=secretadminpassword \
-p 7000:5432 \
-v /tmp/dbdata:/var/lib/postgresql \
scalingo/postgresql:9.4.5-1 /postgresql
That’s it your database is up and running on port 7000. You can find the image on the Docker Hub: https://hub.docker.com/r/scalingo/postgresql/
Future Plan for datastore image versioning
Currently, we don’t provide much tools to control precisely your database instances. That’s something we want to improve and you’ll be able to control the versions you want to use and how and when your databases are backed up and updated (minor and major upgrades).
For instance, everyone is not working in the same timezone, so naturally you may want to configure your backup routine when there is not much traffic for your app. That’s something you’ll be able to configure.
As well as the backups, you may want the security updates to be applied at a certain hour to avoid impacting your users, you will be able to setup that.
To get a taste of what this Future Plan will provide to you, we propose today the migration to PostgreSQL 9.4 in one click. For any major upgrade of database, we’ll automatize the process to let you achieve this operation for any kind of database. A list of availables versions will be available, and you’ll be able to choose precisely.
