TimescaleDB Tutorial for an ObjectRocket Instance

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

TimescaleDB is a time-series database system, often referred to by it TSDB acronym. TimescaleDB is celebrated for being able to efficiently managing large data and executing CRUD functions faster than traditional relational databases. TimescaleDB tutorial for an ObjectRocket instance will explain how to create a TimescaleDB ObjectRocket instance for easier data management and remotely use a PostgreSQL server running on the local machine.

TimescaleDB Performance

Time-series databases, such as TimescaleDB, use dynamically assessed and indexed values that are based on timestamps, resulting in vastly improved performance. TimescaleDB uses compression algorithms to aid in decreasing the footprint of stored data. This reduces storage costs while increasing query speed of records on large databases.

TimescaleDB vs Postgres

While both Timescale and Postgres are written in C, TimescaleDB is proven to add and delete records much more quickly than PostgreSQL, Both Timescale and Postgres are accessible using the PSQL client interface. TimescaleDB is based on Postgres, but Postgres remains the more popular program. As such, Postgres will therefore continue to offer more support and better documentation than Timescale.

Prerequisites

  • Postgres and its PSQL client interface must be installed and working properly on the local system in order to access the ObjectRocket instance for TimescaleDB remotely.

  • The Postgres version installed on local device should match the TimescaleDB instance version. However, Postgres and the TimescaleDB service can be ran as a Docker container in lieu of installing Postgres on the local environment, Full instructions will be provided for connecting to the ObjectRocket instance with Docker, using the docker exec command, later in this tutorial.

Create a TimescaleDB Instance

Navigate to the Mission Control panel for the ObjectRocket instances and create a new instance for TimescaleDB. The panel will look like the following image:

PostgreSQL create user example in the TimescaleDB tutorial for an ObjectRocket instance

Connect to TimescaleDB With PSQL

Once the instance is running, and a user for Postgres has been created, connect remotely using the PSQL command-line interface on the local machine. Open a new shell terminal or command-prompt window on the machine to get started.

Use PSQL to Connect to a Database

Confirm the PSQL service is running by executing the psql -V command in a terminal window. Next, execute the pg_ctl utility pg_ctl command to start the PostgreSQL cluster. Use the following command if Postgres is installed on a Mac:

1
pg_ctl -D /usr/local/var/PostgreSQL/ -l logfile start

Take the ingress URI for the instance, the username and the port that the TimescaleDB instance is running on and pass them all to the psql command as shown in here:

1
2
3
4
psql -h ingress.uri.for.objectrocket.cloud \
-p 1234 \
-U example_user \
-d PostgreSQL

Here is a breakdown of the he psql command:

  • The -p option designates the port.

  • The -U option is for the user that was assigned for the instance.

  • The -d flag is for the default PostgreSQL database that was automatically generated after creating the instance.

Following is a screenshot of connecting to the database:

TimescaleDB tutorial for an ObjectRocket instance getting the URI and port connection values

Use Psql to Create a Database for TimescaleDB

When using Postgres to create a database for TimescaleDB, be certain to correctly input the password for the ObjectRocket instance and then press the -kbd-Return-/kbd- key. The system should now grant access to the database stored on the instance using the PSQL interface.

Now execute the following CREATE DATABASE SQL statement to create a new database for the instance:

1
CREATE DATABASE sample_db;

Once the database is created, use the \c sample_db command to connect to the new database. Type \q to exit the Postgres CLI.

Following is an image of creating the database for a TimescaleDB ObjectRocket instance:

Using psql to connect to a database for a TimescaleDB ObjectRocket instance

TimescaleDB Docker Container

The PostGIS Docker image for TimescaleDB can also be used to run a Postgres server that can access the ObjectRocket instance. Make sure there is a supported version of the Docker Engine installed on the local device.

TimescaleDB PostGIS Image

Executing the following docker pull shell command will download the PostGIS image tag for a version of TimescaleDB that is compatible with Postgres 12:

1
docker pull timescale/timescaledb-postgis:1.7.0-pg12

NOTE: Make certain the version of Postgres specified in the Docker tag matches the version of the ObjectRocket instance. Check out the complete list of the TimescaleDB at PostGIS tags for further details.

Docker Run Command for TimescaleDB

The timescale/timescaledb-postgis image can now be used to run a custom tailored TimescaleDB container for the ObjectRocket instance using the docker run command.

The following example shows how to run a new container instance of the PostGIS image that can be used to connect to the TimescaleDB instance:

1
2
3
4
5
6
7
8
docker run -d --name postgis-example \
    -p 5432:1234 \
    -e PGHOST="ingress.uri.for.objectrocket.cloud" \   
    -e POSTGRES_PASSWORD="sTrOnGpAsSwOrD" \
    -e POSTGRES_USER="example_user" \
    -e PGDATABASE="PostgreSQL" \
    -e PGPORT=1234 \
    timescale/timescaledb-postgis:1.7.0-pg12

Use the -p or --port flag to specify the ObjectRocket instance’s port; the default port for Postgres is 5432. Make absolutely certain the second port, the one on the right-hand side of the colon, matches the instance’s port.

Postgres Environment Variables for Docker

The above docker run command uses the -e flag to pass the Postgres environmental variables for the ObjectRocket instance to the Docker container. Make certain to pass the instance’s ingress URI to the PGHOST environmental variable.

See the complete list of PostgreSQL environmental variables for more details.

Following is a screenshot of the Postgres environment variables for the Docker example:

Postgres environment variables for Docker example in a TimescaleDB tutorial for ObjectRocket

When the image for the timescale/timescaledb-postgis has completed pulling, it should run the TimescaleDB server as a container in the terminal as a foreground service.

Provided the container in the terminal window is running as a foreground process, the following welcome message should be visible once the container has started:

1
2
3
4
5
6
7
8
WELCOME TO
 _____ _                               _     ____________  
|_   _(_)                             | |    |  _  \ ___ \
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ /
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
               Running version 1.7.0

NOTE: To run the container as a background daemon, use the -d (or --detach) option when executing the docker run command.

Docker Exec Psql Example

In order to access the Postgres interface for the TimeScaleDB instance, open a new shell tab or window and execute the following docker exec command to enter the container:

1
docker exec -it postgis-example psql -U example_user -d PostgreSQL

Make sure to use the container name that was specified when executing the docker run command earlier and pass the instance’s username to the -U flag. The results should resemble this:

Docker exec psql example using a TimescaleDB PostGIS image to run TimescaleDB in Docker

After the database has been created, execute the \q command to quit the container’s Postgres interface and then input exit to leave the container.

Conclusion

This TimescaleDB tutorial for an ObjectRocket instance explained how to get Postgres running locally in the Postgres command-line interface. The tutorial covered the major difference between TimescaleDB and Postgres and then explained how to create a TimescaleDB instance, connect to TimescaleDB with Postgres and how to use PSQL to create a database for TimescaleDB. The article then explained the Docker run command for TimescaleDB, the Postgres environment variables for Docker and provided an example for accessing the Postgres interface for the TimeScaleDB instance. While there are several methods that will work, this TimescaleDB tutorial for an ObjectRocket instance has provided enough information to be able to successfully establish a connection to the TimescaleDB instance remotely. Remember to be sure there is a supported version of the Docker engine installed on the local device.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.