TimescaleDB Tutorial for an ObjectRocket Instance
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.
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.
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 execcommand, 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:
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:
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:
psql -h ingress.uri.for.objectrocket.cloud \
-p 1234 \
-U example_user \
Here is a breakdown of the he
-poption designates the port.
-Uoption is for the user that was assigned for the instance.
-dflag is for the default
PostgreSQLdatabase that was automatically generated after creating the instance.
Following is a screenshot of connecting to the database:
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:
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:
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:
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
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:
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 \
--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
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:
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:
_____ _ _ ____________
|_ _(_) | | | _ \ ___ \
| | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ /
| | | | _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
| | | | | | | | | __/\__ \ (_| (_| | | __/ |/ /| |_/ /
|_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
Running version 1.7.0
NOTE: To run the container as a background daemon, use the
--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:
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:
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.
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