Installing self-hosted TimescaleDB on Debian-based systems

When you have PostgreSQL and TimescaleDB installed, you can connect to it from your local system using the psql command-line utility.

You can use the apt on Debian-based systems, yum on Red Hat-based systems, and pacman package manager to install the psql tool.

• Copy

• Install the postgresql-client package:

   

  apt-get install postgresql-client

   

  Debian

  Red Hat

  ArchLinux

  Setting up the TimescaleDB extension on Debian-based systems

  Restart PostgreSQL and create the TimescaleDB extension:

  1. Restart the service after enabling TimescaleDB with timescaledb-tune:

      

     systemctl restart postgresql

  • Copy

  • On your local system, at the command prompt, open the psql command-line utility as the postgres superuser:

     

    sudo -u postgres psql

  Copy

  If your connection is successful, you'll see a message like this, followed by the psql prompt:

   

  psql (15.0 (Ubuntu 15.0-1.pgdg20.04+1), server 14.5 (Ubuntu 14.5-2.pgdg20.04+2))

   

  SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)

   

  Type "help" for help.

  • Copy

  • Set the password for the postgres user:

     

    \password postgres
  • Copy

  • Exit from PostgreSQL:

     

    \q

  Use psql client to connect to PostgreSQL:

   

  psql -U postgres -h localhost

  • Copy

  • At the psql prompt, create an empty database. Our database is called tsdb:

     

    CREATE database tsdb;
  • Copy

  • Connect to the database you created:

     

    \c tsdb
  • Copy

  • Add the TimescaleDB extension:

     

    CREATE EXTENSION IF NOT EXISTS timescaledb;
  • Copy

  • Check that the TimescaleDB extension is installed by using the \dx command at the psql prompt. Output is similar to:

   

  tsdb-# \dx

   

  List of installed extensions

   

  Name | Version | Schema | Description

   

  -------------+---------+------------+-------------------------------------------------------------------

   

  plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

   

  timescaledb | 2.7.0 | public | Enables scalable inserts and complex queries for time-series data

   

  (2 rows)

  9. Copy

  After you have created the extension and the database, you can connect to your database directly using this command:

   

  psql -U postgres -h localhost -d tsdb

   

  apt install postgresql-14

  1

  启动pg

  service postgresql start

  apt install postgresql-14-postgis-3

  apt install timescaledb-2-2.5.1-postgresql-14

  create databases test;

  \c test

  create extension postgis;

  create extension timescaledb;

  创建timescaledb扩展时会报一下错误

  FATAL: extension "timescaledb" must be preloaded
  HINT: Please preload the timescaledb library via shared_preload_libraries.

  This can be done by editing the config file at: /etc/postgresql/14/main/postgresql.conf
  and adding 'timescaledb' to the list in the shared_preload_libraries config.
  # Modify postgresql.conf:
  shared_preload_libraries = 'timescaledb'

  Another way to do this, if not preloading other libraries, is with the command:
  echo "shared_preload_libraries = 'timescaledb'" >> /etc/postgresql/14/main/postgresql.conf

  (Will require a database restart.)

  If you REALLY know what you are doing and would like to load the library without preloading, you can disable this check with:
  SET timescaledb.allow_install_without_preload = 'on';
  server closed the connection unexpectedly
  This probably means the server terminated abnormally
  before or while processing the request.
  The connection to the server was lost. Attempting reset: Succeeded.

   

  根据提示修改配置文件

  echo "shared_preload_libraries = 'timescaledb'" >> /etc/postgresql/14/main/postgresql.conf

   

  重启pg

  service postgresql restart

   

  再次建立扩展

  create extension timescaledb;

   

  查看已安装好的扩展

  \dx