title: How to test timescaledb database using Python
body: <p>I want to test a Timescale database using Python and pytest.</p>
  
  <p>I have discovered, that <code>pytest-pqsql</code> provides a database fixture to be used in testing. However, I am struggling to enable timescaledb support for it.</p>
  
  <p>I have gone through the following steps to get it working:</p>
  
  <ul>
  <li>enable extension using <code>--pg-extension="timescaledb"</code> -> <code>Error: extension "timescaledb" must be preloaded</code></li>
  <li>preload extension using <code>--pg-conf-opt="shared_preload_libraries='timescaledb'"</code> -> <code>Error: could not access file "'timescaledb'": No such file or directory</code>
  
  <ul>
  <li><a href="https://docs.timescale.com/latest/using-timescaledb/troubleshooting#alter-issue" rel="nofollow noreferrer">Timescale Docs</a> suggest to update extension, which doesn't apply here, as it hasn't been updated previously.</li>
  </ul></li>
  </ul>
  
  <p>Here is my example test file and the (truncated) output:</p>
  
  <pre class="lang-py prettyprint-override"><code># test_database.py
  import pytest
  
  @pytest.fixture
  def DB(transacted_postgresql_db):
      assert transacted_postgresql_db.is_extension_available('timescaledb')
      yield transacted_postgresql_db
  
  
  def test_add_schema(DB):
      assert not DB.has_schema('foo')
      DB.session.execute("CREATE TABLE foo (bar VARCHAR(40));")
      assert DB.has_schema('foo')
  </code></pre>
  
  <pre class="lang-sh prettyprint-override"><code>$ 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 = &lt;sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x102854a00&gt;
  cursor = &lt;cursor object at 0x1026407c0; closed: 0&gt;
  statement = 'BEGIN TRANSACTION; CREATE EXTENSION IF NOT EXISTS "timescaledb"; COMMIT;'
  parameters = {}
  context = &lt;sqlalchemy.dialects.postgresql.psycopg2.PGExecutionContext_psycopg2 object at 0x1028830a0&gt;
  
      def do_execute(self, cursor, statement, parameters, context=None):
  &gt;       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'" &gt;&gt; /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.
  </code></pre>
  
  <p>preloading the extension yields another error:</p>
  
  <pre class="lang-sh prettyprint-override"><code>$ 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 ____________________________
  
  ...
  _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
  
  self = &lt;testing.postgresql.Postgresql object at 0x1102f5460&gt;
  
      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:
  &gt;               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 ====================================
  </code></pre>