Setting up a TimescaleDB on Google Compute Engine


This article is part of a series of blog posts exploring time series databases and TimescaleDB in particular.  Today we will see how to get a TimescaleDB instance up and running on Google Cloud.

A time series database is one that is optimized to handle a large volume of chronological data.  IoT events  or application log data are some such examples of timed datasets.   Applications built around these datasets rely on  fast inserts and efficient query mechanisms to analyze patterns across various time ranges.  

TimescaleDB is built on top of PostgreSQL.  This allows you to use a very familiar SQL syntax. It also supports the usual RDBMS semantics including joins, complex predicates and secondary indexes.  Be sure to checkout the Getting Started documentation on Timescale site.

Installing TimescaleDB hosted on Google cloud running Ubuntu

We are going to assume here a basic familiarity with Google cloud infrastructure. Article requires that you have a cloud account setup and that you have the GCloud SDK installed your machine.

Startup script for a semi-automated install

gcloud is a command line tool for creating and managing various components on Google Cloud Platform.  We will use this to create a virtual machine by passing it a startup shell script to install and configure TimescaleDB.  Lets dig into the script.

The startup script is run every single time the VM instance is restarted. To prevent it overwriting our install, we will create a file under /var and use its presence to skip installation on subsequent runs (shout out to Kevin Sookocheff who first used this approach for his Kafka scripts).

Next, install TimescaleDB  via  apt-get

The script then edits the postgresql.conf file and enables preloading of TimescaleDB libraries and enables PostgreSQL to listen on all IP addresses.

We finish off by restarting the postgresql service and creating our startup mark file.

Note: On Ubuntu, installing PostgreSQL via apt-get auto creates postgres db account.  You will notice we skip over this step in the script below.

Here is
the complete script (also on GitHub)

Running the script

Save the script as to a local folder and run gcloud

This will create a VM instance called timescaledb01  based on ubuntu-1604-lts image. We also enable serial port so we can monitor the logs to see how the script is doing. You may turn it off on the VM Instances page later.

If the script completes successfully  it will create a startup.script.1 file under /var. It may take a few minutes for the startup script to finish.

Finishing it up

Setting a password for the database user

ssh into your newly created VM instance and verify that the script has finished successfully.  

In order to connect to the server externally we must set the password for our database user. Locally, on the server however, you may login without a password (using peer authentication) and launch psql.

In the ssh terminal type:

sudo -u postgres psql postgres
Next at the psql prompt set the password using the command:

Setting up the firewall to allow external connections

First we must configure PostgreSQL for remote access. To do this edit the pg_hba.conf file (under /etc/postgresql/9.6/main in this example). Scroll all the way to the bottom and add the following line:

Replace [YOUR_IPV4_ADDRESS] with your local IP address and ensure that the CIDR suffix /32 is retained.

Restart the PostgreSQL service  so that these changes are picked up.

Next, we create the firewall rule on google cloud portal to allow connections to the server from our local machine.

  • Set the Targets to “All instances in the network”.
  • Set the source IP range to your local IPv4 address with /32 CIDR suffix. This must match the IP specified within pg_hba.conf file above
  • Set the specified protocols and ports to  tcp:5432

Save your changes.  You should now be able to connect to your server from your local postgres client – e.g. pgAdmin.

Related Posts

Comments (1)

Please try to be more descriptive. What is /var?

Leave a comment

Privacy Preferences
When you visit our website, it may store information through your browser from specific services, usually in form of cookies. Here you can change your privacy preferences. Please note that blocking some types of cookies may impact your experience on our website and the services we offer.