Ubuntu:PostgreSql安装PostGis、TimeScaleDB插件

Ubuntu:PostgreSql安装PostGis、TimeScaleDB插件

https://docs.timescale.com/self-hosted/latest/install/installation-linux/

 

Installing self-hosted TimescaleDB on Debian-based systems

1. At the command prompt, as root, add the PostgreSQL third party repository to get the latest PostgreSQL packages:

    

   apt install gnupg postgresql-common apt-transport-https lsb-release wget

• Copy

• Run the PostgreSQL repository setup script:

   

  /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
• Copy

• Add the TimescaleDB third party repository:

   

  echo "deb https://packagecloud.io/timescale/timescaledb/debian/ $(lsb_release -c -s) main" | sudo tee /etc/apt/sources.list.d/timescaledb.list
• Copy

  Debian

  Ubuntu

• Install TimescaleDB GPG key

   

  wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo apt-key add -
• Copy
  

  note

  For Ubuntu 21.10 and later use this command to install TimescaleDB GPG key wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/timescaledb.gpg

• Update your local repository list:

   

  apt update
• Copy

• Install TimescaleDB:

   

  apt install timescaledb-2-postgresql-14

6. Copy
   

   note

   If you want to install a specific version of TimescaleDB, instead of the most recent, you can specify the version like this: apt-get install timescaledb-2-postgresql-12='2.6.0*' timescaledb-2-loader-postgresql-12='2.6.0*'

   You can see the full list of TimescaleDB releases by visiting the releases page. Note that older versions of TimescaleDB don't always support all the OS versions listed above.

7. Configure your database by running the timescaledb-tune script, which is included with the timescaledb-tools package. Run the timescaledb-tune script using the sudo timescaledb-tune command. For more information, see the configuration section.

Set up the TimescaleDB extension

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

Install psql on Linux

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

Debian

Red Hat

ArchLinux

Installing psql using the apt package manager

1. Make sure your apt repository is up to date:

    

   apt-get update

• 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