Timescaledb: extension "timescaledb" must be preloaded

[wangchuanxiang]

how to do it?

[wangchuanxiang]

stupid，this warning alert a tip：“ CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;”

postgres=# CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
致命错误: extension "timescaledb" must be preloaded
提示: Please preload the timescaledb library via shared_preload_libraries.

This can be done by editing the config file at: D:/PostgreSQL/data/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'" >> D:/PostgreSQL/data/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';

[AndyMender]

Yes, any extra extension which is not loaded by default, must be added to the shared_preload_libraries string array in your postgresql.conf file.

[RobAtticus]

Did following those instructions (adding it to shared_preload_libraries in your conf file) work for you @wangchuanxiang ?

[kev-ho]

I'm in the same place as the original poster.
I've modified the postgresql.conf file with the following

And I restarted the instance.

But when I try to create the hypertable it says it the function doesn't exist.

[AndyMender]

@kevin-ht-ho there are 2 things here:
1) pre-loading the extension by the PostgreSQL server instance if it's mentioned in the shared_preload_libraries string array
2) applying the extension to your database like so: CREATE EXTENSION IF NOT EXISTS "timescaledb" CASCADE;
Only then you can create hypertables.

Unfortunately, none of those things can be done automatically by a post-install script, because 1) requires modifying the postgresql.conf config and 2) requires figuring out which databases will actually make use of the extension and which won't.

[kev-ho]

Thanks @AndyMender . I missed that step in the guide.

[mfreed]

@kevin-ht-ho Is there anything outstanding, or can we close this issue?

[kev-ho]

@mfreed This can be closed. Thankyou

[sighrobot]

_(Platform: MacOS 10.14.3)_

I'm pretty sure I followed the docs to the letter, and the same thing just happened to me.

@AndyMender as I understand it, timescaledb-tune takes care of adding 'timescaledb' to shared_preload_libraries (I checked that it is in fact set there).

Then, once you create a database, you are supposed to CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;. But this results in the same error message the OP encountered:

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

I also ran brew services restart postgresql (recommended in your docs) before and after the failed extension creation, with no effect. Any chance someone can take a look? Thanks!

cc @LeeHampton

[LeeHampton]

Hi @sighrobot , fancy meeting you here!

I tried to reproduce your problem with a fresh local install install of timescaledb on my OSX setup (10.13.2), but did _not_ run into the same issue with the following steps:

brew tap timescale/tap
brew install timescaledb
timescaledb_move.sh
timescaledb-tune --quiet --yes
brew services restart postgresql
createuser postgres -s

I have however run into issues with multiple homebrew installs of postgres in the past. This is especially common if you've ever installed Postgres using the Postgres.app installation before -- it does not play nicely with the homebrew installation.

Can you check the output of show config_file; in a psql shell and see if that file matches the one you have the shared_preload_libraries setting in? Mine points to /usr/local/var/postgres/postgresql.conf.

[sighrobot]

@LeeHampton the location of my config is reported as /usr/local/var/postgres/postgresql.conf, and that file does have the changes from running tune. However I forgot I do indeed have desktop Postgres installed so that could be it.

I will try to resolve this from the dual Postgres perspective before requesting further help.

[sighrobot]

Just an update – I finally got it to work but had to use pg_ctl manually to start and stop, instead of the brew command. All good now!

WELCOME TO
     _____ _                               _     ____________
    |_   _(_)                             | |    |  _  \ ___ \
      | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ /
      | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
      | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
      |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
                   Running version 1.2.1

[moodgorning]

Just an update – I finally got it to work but had to use pg_ctl manually to start and stop, instead of the brew command. All good now!

WELCOME TO
   _____ _                               _     ____________
  |_   _(_)                             | |    |  _  \ ___ \
    | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ /
    | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
    | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
    |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
                 Running version 1.2.1

Exactly what solved it for me

[kelvinu]

Yes brew restart doesn't restart with the right config file.

If you installed PostgreSQL via Homebrew:

To start manually:
pg_ctl -D /usr/local/var/postgres start
To stop manually:
pg_ctl -D /usr/local/var/postgres stop
To start PostgreSQL server now and relaunch at login:
brew services start postgresql
And stop PostgreSQL:
brew services stop postgresql

[kumar-it]

I had the same issues, I have restarted the server and got fixed.