search

timescale / timescaledb

An open-source time-series SQL database optimized for fast ingest and complex queries. Packaged as a PostgreSQL extension.
https://www.timescale.com/
Other
17.9k stars 882 forks source link

Install timescaledb on debian 10 #3493

Closed syco closed 3 years ago

syco commented 3 years ago

Relevant system information:

Describe the bug I have a new clean debian, I followed the instructions from https://docs.timescale.com/timescaledb/latest/how-to-guides/install-timescaledb/self-hosted/debian/installation-apt-debian/#installation-apt-debian but I can't get the extension to load, here's the full output:

root@debian:~# echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -c -s)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
deb http://apt.postgresql.org/pub/repos/apt/ buster-pgdg main
root@debian:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
OK
root@debian:~# sudo apt-get update
Hit:1 http://security.debian.org/debian-security buster/updates InRelease
Hit:2 http://deb.debian.org/debian buster InRelease
Get:3 http://apt.postgresql.org/pub/repos/apt buster-pgdg InRelease [110 kB]
Get:4 http://deb.debian.org/debian buster-updates InRelease [51.9 kB]
Get:5 http://apt.postgresql.org/pub/repos/apt buster-pgdg/main amd64 Packages [234 kB]
Fetched 396 kB in 1s (304 kB/s)   
Reading package lists... Done
root@debian:~# sudo sh -c "echo 'deb https://packagecloud.io/timescale/timescaledb/debian/ `lsb_release -c -s` main' > /etc/apt/sources.list.d/timescaledb.list"
root@debian:~# wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo apt-key add -

OK
root@debian:~# sudo apt-get update
Hit:1 http://security.debian.org/debian-security buster/updates InRelease
Hit:2 http://deb.debian.org/debian buster InRelease                                         
Hit:3 http://apt.postgresql.org/pub/repos/apt buster-pgdg InRelease                         
Hit:4 http://deb.debian.org/debian buster-updates InRelease                                 
Get:5 https://packagecloud.io/timescale/timescaledb/debian buster InRelease [23.3 kB]
Get:6 https://packagecloud.io/timescale/timescaledb/debian buster/main amd64 Packages [66.4 kB]
Fetched 89.7 kB in 3s (30.6 kB/s)  
Reading package lists... Done
root@debian:~# 
root@debian:~# # Now install appropriate package for PG version
root@debian:~# sudo apt-get install timescaledb-2-postgresql-13
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following additional packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm7 libpq5 libsensors-config libsensors5 libtypes-serialiser-perl libxslt1.1 pgdg-keyring postgresql-13
  postgresql-client-13 postgresql-client-common postgresql-common ssl-cert sysstat timescaledb-2-loader-postgresql-13 timescaledb-tools
Suggested packages:
  lm-sensors postgresql-doc-13 openssl-blacklist isag libpq-dev
The following NEW packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm7 libpq5 libsensors-config libsensors5 libtypes-serialiser-perl libxslt1.1 pgdg-keyring postgresql-13
  postgresql-client-13 postgresql-client-common postgresql-common ssl-cert sysstat timescaledb-2-loader-postgresql-13 timescaledb-2-postgresql-13 timescaledb-tools
0 upgraded, 19 newly installed, 0 to remove and 0 not upgraded.
Need to get 34.1 MB of archives.
After this operation, 138 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 http://deb.debian.org/debian buster/main amd64 libcommon-sense-perl amd64 3.74-2+b7 [24.0 kB]
Get:2 http://apt.postgresql.org/pub/repos/apt buster-pgdg/main amd64 libpq5 amd64 13.4-1.pgdg100+1 [179 kB]        
Get:3 http://deb.debian.org/debian buster/main amd64 libjson-perl all 4.02000-1 [88.8 kB]          
Get:4 http://deb.debian.org/debian buster/main amd64 libtypes-serialiser-perl all 1.0-1 [12.7 kB]                
Get:5 http://deb.debian.org/debian buster/main amd64 libjson-xs-perl amd64 3.040-1+b1 [91.0 kB]                                
Get:6 http://deb.debian.org/debian buster/main amd64 libllvm7 amd64 1:7.0.1-8+deb10u2 [13.1 MB]                    
Get:7 http://apt.postgresql.org/pub/repos/apt buster-pgdg/main amd64 pgdg-keyring all 2018.2 [10.7 kB]        
Get:8 http://apt.postgresql.org/pub/repos/apt buster-pgdg/main amd64 postgresql-client-common all 226.pgdg100+1 [90.6 kB]
Get:9 http://apt.postgresql.org/pub/repos/apt buster-pgdg/main amd64 postgresql-client-13 amd64 13.4-1.pgdg100+1 [1,507 kB]  
Get:10 https://packagecloud.io/timescale/timescaledb/debian buster/main amd64 timescaledb-2-loader-postgresql-13 amd64 2.4.0~debian10 [26.1 kB]
Get:11 https://packagecloud.io/timescale/timescaledb/debian buster/main amd64 timescaledb-2-postgresql-13 amd64 2.4.0~debian10 [1,020 kB]                                            
Get:12 http://deb.debian.org/debian buster/main amd64 libsensors-config all 1:3.5.0-3 [31.6 kB]                                                                                   
Get:13 http://deb.debian.org/debian buster/main amd64 libsensors5 amd64 1:3.5.0-3 [52.6 kB]                                                                                          
Get:14 http://deb.debian.org/debian buster/main amd64 libxslt1.1 amd64 1.1.32-2.2~deb10u1 [237 kB]                                                                                   
Get:15 http://apt.postgresql.org/pub/repos/apt buster-pgdg/main amd64 postgresql-common all 226.pgdg100+1 [246 kB]                                                                  
Get:16 http://deb.debian.org/debian buster/main amd64 ssl-cert all 1.0.39 [20.8 kB]                                                                                           
Get:17 http://deb.debian.org/debian buster/main amd64 sysstat amd64 12.0.3-2 [562 kB]                                                                                                
Get:18 http://apt.postgresql.org/pub/repos/apt buster-pgdg/main amd64 postgresql-13 amd64 13.4-1.pgdg100+1 [15.0 MB]     
Get:19 https://packagecloud.io/timescale/timescaledb/debian buster/main amd64 timescaledb-tools amd64 0.11.0~debian10 [1,873 kB]
Fetched 34.1 MB in 7s (4,712 kB/s)                                                                                                                                                   
Preconfiguring packages ...
Selecting previously unselected package libcommon-sense-perl.
(Reading database ... 36238 files and directories currently installed.)
Preparing to unpack .../00-libcommon-sense-perl_3.74-2+b7_amd64.deb ...
Unpacking libcommon-sense-perl (3.74-2+b7) ...
Selecting previously unselected package libjson-perl.
Preparing to unpack .../01-libjson-perl_4.02000-1_all.deb ...
Unpacking libjson-perl (4.02000-1) ...
Selecting previously unselected package libtypes-serialiser-perl.
Preparing to unpack .../02-libtypes-serialiser-perl_1.0-1_all.deb ...
Unpacking libtypes-serialiser-perl (1.0-1) ...
Selecting previously unselected package libjson-xs-perl.
Preparing to unpack .../03-libjson-xs-perl_3.040-1+b1_amd64.deb ...
Unpacking libjson-xs-perl (3.040-1+b1) ...
Selecting previously unselected package libllvm7:amd64.
Preparing to unpack .../04-libllvm7_1%3a7.0.1-8+deb10u2_amd64.deb ...
Unpacking libllvm7:amd64 (1:7.0.1-8+deb10u2) ...
Selecting previously unselected package libpq5:amd64.
Preparing to unpack .../05-libpq5_13.4-1.pgdg100+1_amd64.deb ...
Unpacking libpq5:amd64 (13.4-1.pgdg100+1) ...
Selecting previously unselected package libsensors-config.
Preparing to unpack .../06-libsensors-config_1%3a3.5.0-3_all.deb ...
Unpacking libsensors-config (1:3.5.0-3) ...
Selecting previously unselected package libsensors5:amd64.
Preparing to unpack .../07-libsensors5_1%3a3.5.0-3_amd64.deb ...
Unpacking libsensors5:amd64 (1:3.5.0-3) ...
Selecting previously unselected package libxslt1.1:amd64.
Preparing to unpack .../08-libxslt1.1_1.1.32-2.2~deb10u1_amd64.deb ...
Unpacking libxslt1.1:amd64 (1.1.32-2.2~deb10u1) ...
Selecting previously unselected package pgdg-keyring.
Preparing to unpack .../09-pgdg-keyring_2018.2_all.deb ...
Unpacking pgdg-keyring (2018.2) ...
Selecting previously unselected package postgresql-client-common.
Preparing to unpack .../10-postgresql-client-common_226.pgdg100+1_all.deb ...
Unpacking postgresql-client-common (226.pgdg100+1) ...
Selecting previously unselected package postgresql-client-13.
Preparing to unpack .../11-postgresql-client-13_13.4-1.pgdg100+1_amd64.deb ...
Unpacking postgresql-client-13 (13.4-1.pgdg100+1) ...
Selecting previously unselected package ssl-cert.
Preparing to unpack .../12-ssl-cert_1.0.39_all.deb ...
Unpacking ssl-cert (1.0.39) ...
Selecting previously unselected package postgresql-common.
Preparing to unpack .../13-postgresql-common_226.pgdg100+1_all.deb ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Unpacking postgresql-common (226.pgdg100+1) ...
Selecting previously unselected package postgresql-13.
Preparing to unpack .../14-postgresql-13_13.4-1.pgdg100+1_amd64.deb ...
Unpacking postgresql-13 (13.4-1.pgdg100+1) ...
Selecting previously unselected package sysstat.
Preparing to unpack .../15-sysstat_12.0.3-2_amd64.deb ...
Unpacking sysstat (12.0.3-2) ...
Selecting previously unselected package timescaledb-2-loader-postgresql-13.
Preparing to unpack .../16-timescaledb-2-loader-postgresql-13_2.4.0~debian10_amd64.deb ...
Unpacking timescaledb-2-loader-postgresql-13 (2.4.0~debian10) ...
Selecting previously unselected package timescaledb-2-postgresql-13.
Preparing to unpack .../17-timescaledb-2-postgresql-13_2.4.0~debian10_amd64.deb ...
Unpacking timescaledb-2-postgresql-13 (2.4.0~debian10) ...
Selecting previously unselected package timescaledb-tools.
Preparing to unpack .../18-timescaledb-tools_0.11.0~debian10_amd64.deb ...
Unpacking timescaledb-tools (0.11.0~debian10) ...
Setting up pgdg-keyring (2018.2) ...
Removing apt.postgresql.org key from trusted.gpg: OK
Setting up timescaledb-tools (0.11.0~debian10) ...
Setting up libsensors-config (1:3.5.0-3) ...
Setting up libpq5:amd64 (13.4-1.pgdg100+1) ...
Setting up libcommon-sense-perl (3.74-2+b7) ...
Setting up ssl-cert (1.0.39) ...
Setting up libsensors5:amd64 (1:3.5.0-3) ...
Setting up libtypes-serialiser-perl (1.0-1) ...
Setting up libjson-perl (4.02000-1) ...
Setting up libxslt1.1:amd64 (1.1.32-2.2~deb10u1) ...
Setting up libllvm7:amd64 (1:7.0.1-8+deb10u2) ...
Setting up sysstat (12.0.3-2) ...

Creating config file /etc/default/sysstat with new version
update-alternatives: using /usr/bin/sar.sysstat to provide /usr/bin/sar (sar) in auto mode
Created symlink /etc/systemd/system/multi-user.target.wants/sysstat.service → /lib/systemd/system/sysstat.service.
Setting up postgresql-client-common (226.pgdg100+1) ...
Setting up libjson-xs-perl (3.040-1+b1) ...
Setting up postgresql-client-13 (13.4-1.pgdg100+1) ...
update-alternatives: using /usr/share/postgresql/13/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up postgresql-common (226.pgdg100+1) ...
Adding user postgres to group ssl-cert

Creating config file /etc/postgresql-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service.
Setting up postgresql-13 (13.4-1.pgdg100+1) ...
Creating new PostgreSQL cluster 13/main ...
/usr/lib/postgresql/13/bin/initdb -D /var/lib/postgresql/13/main --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_GB.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/13/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Europe/London
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctlcluster 13 main start

Ver Cluster Port Status Owner    Data directory              Log file
13  main    5432 down   postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
update-alternatives: using /usr/share/postgresql/13/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Setting up timescaledb-2-loader-postgresql-13 (2.4.0~debian10) ...
Setting up timescaledb-2-postgresql-13 (2.4.0~debian10) ...
RECOMMENDED: Run 'timescaledb-tune' (installed as part of the 
timescaledb-tools package, a recommended dependency) to update your 
config settings for TimescaleDB: 

  timescaledb-tune --quiet --yes

IF NOT, you need to update your postgresql.conf file to load TimescaleDB
by adding 'timescaledb' to your shared_preload_libraries.
Find the line below and change the value as shown (uncomment if needed):

shared_preload_libraries = 'timescaledb'
Processing triggers for systemd (241-7~deb10u8) ...
Processing triggers for man-db (2.8.5-2) ...
Processing triggers for libc-bin (2.28-10) ...
root@debian:~# timescaledb-tune --quiet --yes
Using postgresql.conf at this path:
/etc/postgresql/13/main/postgresql.conf

Writing backup to:
/tmp/timescaledb_tune.backup202108171610

Recommendations based on 1.95 GB of available memory and 3 CPUs for PostgreSQL 13
shared_preload_libraries = 'timescaledb'    # (change requires restart)
shared_buffers = 510722kB
effective_cache_size = 1496MB
maintenance_work_mem = 255361kB
work_mem = 6384kB
timescaledb.max_background_workers = 8
max_worker_processes = 14
max_parallel_workers_per_gather = 2
max_parallel_workers = 3
wal_buffers = 15321kB
min_wal_size = 512MB
default_statistics_target = 500
random_page_cost = 1.1
checkpoint_completion_target = 0.9
max_connections = 20
max_locks_per_transaction = 64
autovacuum_max_workers = 10
autovacuum_naptime = 10
effective_io_concurrency = 200
timescaledb.last_tuned = '2021-08-17T16:10:05+01:00'
timescaledb.last_tuned_version = '0.11.0'
Saving changes to: /etc/postgresql/13/main/postgresql.conf
root@debian:~# pg_ctlcluster 13 main restart
root@debian:~# reboot
Connection to 172.20.12.120 closed by remote host.

syco@torann:~$ ssh root@172.20.12.120
Linux debian 4.19.0-17-amd64 #1 SMP Debian 4.19.194-3 (2021-07-18) x86_64

The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Tue Aug 17 16:07:28 2021 from 172.20.12.117
root@debian:~# sudo -u postgres psql -c 'select * from pg_extension;'
could not change directory to "/root": Permission denied
  oid  | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+---------+----------+--------------+----------------+------------+-----------+--------------
 13381 | plpgsql |       10 |           11 | f              | 1.0        |           | 
(1 row)

root@debian:~# dpkg -l | grep timescale
ii  timescaledb-2-loader-postgresql-13 2.4.0~debian10                      amd64        The loader for TimescaleDB to load individual versions.
ii  timescaledb-2-postgresql-13        2.4.0~debian10                      amd64        An open-source time-series database based on PostgreSQL, as an extension.
ii  timescaledb-tools                  0.11.0~debian10                     amd64        A suite of tools that can be used with TimescaleDB.

root@debian:~# cat /etc/postgresql/13/main/postgresql.conf | grep shared_preload_libraries
shared_preload_libraries = 'timescaledb'    # (change requires restart)

what can I do? Thanks.

svenklemm commented 3 years ago

You need to run CREATE EXTENSION timescaledb; in every database you want to use timescaledb.

syco commented 3 years ago

Thanks, that was it. I feel like this is basic postgresql knowledge, but after a over a decade on mysql and mariadb, it's not as easy as it seems... Maybe a note at the end of the install doc would point others in the right direction. However, this one can be closed. Thanks again.