extension "timescaledb" must be preloaded
Closed this issue · 19 comments
how to do it?
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';
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.
Did following those instructions (adding it to shared_preload_libraries in your conf file) work for you @wangchuanxiang ?
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.
@kevin-ht-ho there are 2 things here:
- pre-loading the extension by the PostgreSQL server instance if it's mentioned in the
shared_preload_librariesstring array - 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.
Thanks @AndyMender . I missed that step in the guide.
@kevin-ht-ho Is there anything outstanding, or can we close this issue?
(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 onI 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
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.
@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.
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
Just an update – I finally got it to work but had to use
pg_ctlmanually to start and stop, instead of thebrewcommand. All good now!WELCOME TO _____ _ _ ____________ |_ _(_) | | | _ \ ___ \ | | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ / | | | | _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \ | | | | | | | | | __/\__ \ (_| (_| | | __/ |/ /| |_/ / |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/ Running version 1.2.1
Exactly what solved it for me
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
I had the same issues, I have restarted the server and got fixed.
I have the same issues but I'm using the timescale/timescaledb:latest-pg13 docker image, which should have all this preconfigured? Does anyone have an idea or should I create a new issue?
I just ran into the same issue when following the doc to install on Ubuntu. I ended up manually adding that line to the config and then restarting pg service. I think since so many people above had this issue, the doc definitely can use some improvement.
In case anyone else reaches this page: I eventually figured out that I had a postgresql.auto.conf that contained a shared_preload_libraries that overrode the one I put in postgresql.conf. To update the auto file, I issued this command inside psql, making sure to keep the extension that was already present in my auto file:
alter system set shared_preload_libraries = pg_stat_statements,timescaledb;
Then I restarted PostgreSQL, and everything was fine.
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 -sI 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.appinstallation before -- it does not play nicely with the homebrew installation.Can you check the output of
show config_file;in apsqlshell and see if that file matches the one you have theshared_preload_librariessetting in? Mine points to/usr/local/var/postgres/postgresql.conf.
Worked perfectly