Preloading `timescaledb` extension using `--pg-conf-opt` produces error

[jannismain]

I have also posted my issue on StackOverflow.

I'm trying to use pytest-pgsql with timescaledb, but cannot get it setup correctly. Do you have any advice?

The relevant error message is this one:

$ pytest --pg-conf-opt="shared_preload_libraries='timescaledb'" --pg-extensions="timescaledb" tests/test_database.py
...
> raise RuntimeError("*** failed to launch %s ***\n" % self.name + self.read_bootlog())
E     RuntimeError: *** failed to launch Postgresql ***
E     2020-06-15 07:57:03.052 CEST [91515] FATAL:  could not access file "'timescaledb'": No such file or directory

It looks like pytest-pgsql's Postgres instance cannot find timescaledb. However, I'm using it in a manually managed database on the same system just fine. According to the Troubleshooting site over at Timescale, I have verified that my test is running on the same postgresql version as the manually managed database.

Setup

• macOS
• installed postgresql, timescaledb via brew
• timescaledb is working in manually managed database

Console Output

$ pytest --pg-conf-opt="shared_preload_libraries='timescaledb'" --pg-extensions="timescaledb" tests/test_database.py
================================== test session starts ===================================
platform darwin -- Python 3.8.3, pytest-5.4.3, py-1.8.1, pluggy-0.13.1
rootdir: /Users/mkj/Developer, inifile: setup.cfg
plugins: pgsql-1.1.2
collected 1 item                                                                         

tests/test_database.py E                                                           [100%]

========================================= ERRORS =========================================
___________________________ ERROR at setup of test_add_schema ____________________________

request = <SubRequest 'database_uri' for <Function test_add_schema>>

    @pytest.fixture(scope='session')
    def database_uri(request):
        """A fixture giving the connection URI of the session-wide test database."""
        # Note: due to the nature of the variable configs, the command line options
        # must be tested manually.
    
        work_mem = request.config.getoption('--pg-work-mem')
        if work_mem < 0:    # pragma: no cover
            pytest.exit('ERROR: --pg-work-mem value must be >= 0. Got: %d' % work_mem)
            return
        elif work_mem == 0:  # pragma: no cover
            # Disable memory tweak and use the server default.
            work_mem_setting = ''
        else:
            # User wants to change the working memory setting.
            work_mem_setting = '-c work_mem=%dMB ' % work_mem
    
        # pylint: disable=bad-continuation,deprecated-method
        conf_opts = request.config.getoption('--pg-conf-opt')
        if conf_opts:
            conf_opts_string = ' -c ' + ' -c '.join(conf_opts)
        else:
            conf_opts_string = ''
    
>       with testing.postgresql.Postgresql(
            postgres_args='-c TimeZone=UTC '
                          '-c fsync=off '
                          '-c synchronous_commit=off '
                          '-c full_page_writes=off '
                          + work_mem_setting +
                          '-c checkpoint_timeout=30min '
                          '-c bgwriter_delay=10000ms'
                          + conf_opts_string
                            ) as pgdb:

env/lib/python3.8/site-packages/pytest_pgsql/plugin.py:56: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
env/lib/python3.8/site-packages/testing/common/database.py:98: in __init__
    self.start()
env/lib/python3.8/site-packages/testing/common/database.py:154: in start
    self.wait_booting()
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <testing.postgresql.Postgresql object at 0x1102f5460>

    def wait_booting(self):
        boot_timeout = self.settings.get('boot_timeout', self.DEFAULT_BOOT_TIMEOUT)
        exec_at = datetime.now()
        while True:
            if self.child_process.poll() is not None:
>               raise RuntimeError("*** failed to launch %s ***\n" % self.name +
                                   self.read_bootlog())
E               RuntimeError: *** failed to launch Postgresql ***
E               2020-06-15 07:57:03.052 CEST [91515] FATAL:  could not access file "'timescaledb'": No such file or directory
E               2020-06-15 07:57:03.052 CEST [91515] LOG:  database system is shut down

env/lib/python3.8/site-packages/testing/common/database.py:170: RuntimeError
================================ short test summary info =================================
ERROR tests/test_database.py::test_add_schema - RuntimeError: *** failed to launch Post...
==================================== 1 error in 1.80s ====================================

But when I do not preload timescaledb, Postgresql can be started just fine. However, I get the warning that timescaledb must be preloaded:

$ pytest --pg-extensions="timescaledb" test_database.py
================================== test session starts ===================================
platform darwin -- Python 3.8.3, pytest-5.4.3, py-1.8.1, pluggy-0.13.1
rootdir: /Users/mkj/Developer, inifile: setup.cfg
plugins: pgsql-1.1.2
collected 1 item                                                                         

tests/test_database.py E                                                           [100%]

========================================= ERRORS =========================================
___________________________ ERROR at setup of test_add_schema ____________________________
...
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x102854a00>
cursor = <cursor object at 0x1026407c0; closed: 0>
statement = 'BEGIN TRANSACTION; CREATE EXTENSION IF NOT EXISTS "timescaledb"; COMMIT;'
parameters = {}
context = <sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x1028830a0>

    def do_execute(self, cursor, statement, parameters, context=None):
>       cursor.execute(statement, parameters)
E       psycopg2.errors.InternalError_: extension "timescaledb" must be preloaded
E       HINT:  Please preload the timescaledb library via shared_preload_libraries.
E       
E       This can be done by editing the config file at: /var/folders/1g/hcdk5d553g334m2nd7_thy6m5y1jp8/T/tmpo9ut8s1l/data/postgresql.conf
E       and adding 'timescaledb' to the list in the shared_preload_libraries config.
E               # Modify postgresql.conf:
E               shared_preload_libraries = 'timescaledb'
E       
E       Another way to do this, if not preloading other libraries, is with the command:
E               echo "shared_preload_libraries = 'timescaledb'" >> /var/folders/1g/hcdk5d553g334m2nd7_thy6m5y1jp8/T/tmpo9ut8s1l/data/postgresql.conf 
E       
E       (Will require a database restart.)
E       
E       If you REALLY know what you are doing and would like to load the library without preloading, you can disable this check with: 
E               SET timescaledb.allow_install_without_preload = 'on';
E       server closed the connection unexpectedly
E               This probably means the server terminated abnormally
E               before or while processing the request.

[dargueta]

Just guessing here but there might be a difference between your database's search paths and the default one used by the plugin?

[lafrech]

People searching about this might be interested about the answer I provided on SO.

I managed to use TimescaleDB with pytest-postgresql.

from pytest_postgresql import factories as ppf


postgresql_proc = ppf.postgresql_proc(
    postgres_options="-c shared_preload_libraries='timescaledb'"
)
postgresql = ppf.postgresql('postgresql_proc')


@pytest.fixture
def database(postgresql):

    cur = postgresql.cursor()
    cur.execute("CREATE EXTENSION IF NOT EXISTS timescaledb;")
    postgresql.commit()
    cur.close()

    yield postgresql