The problem I’m having
dbt 1.9.0-b3 does not connect to postgres 17. The error is very long and it is pasted at the end of this post. The final bit of the error is:
File “C:\Users\cestaane\Desktop\Lineas\python\utils\venv\Lib\site-packages\dbt\adapters\postgres_init_.py”, line 4, in
from dbt.adapters.postgres.connections import PostgresConnectionManager, PostgresCredentials
File “C:\Users\cestaane\Desktop\Lineas\python\utils\venv\Lib\site-packages\dbt\adapters\postgres\connections.py”, line 13, in
import psycopg2
File “C:\Users\cestaane\Desktop\Lineas\python\utils\venv\Lib\site-packages\psycopg2_init_.py”, line 51, in
from psycopg2._psycopg import ( # noqa
…<10 lines>…
)
ImportError: DLL load failed while importing _psycopg: The specified module could not be found.
The context of why I’m trying to do this
dbt 1.9.0-b3 which is ahead of the latest 1.8.8 as of 2024-11-02.
Postgres 17.
On windows.
Reproducing the error;
pip install dbt-postgres
dbt debug # you get the error message above.
Do not be fooled to belive it is the DLL. It is the fact that psycopg2 is not good to connect to postgres17. See this post by myself how I fixed Python to connect via psycopg (not psycopg2) to Postgres 17: Reddit - Dive into anything
What I’ve already tried
I tried editing the dbt package file: venv\Lib\site-packages\dbt\adapters\postgres\connections.py
In this file I replaced the import of psycopg2 by: import psycopg as psycopg2 # this is to trick the interpreter to use the latest psycopg3 installed on my machine via pip install psycopg # without the 2 as in pip install psycopg2
So the above solves the problem. I have not tried this below, but it should also work: install somewhere else an old version of postgres. Copy the dlls needed by psycopg into a folder that is in the library path for psycopg2. Use only psycopg2 not psycopg (= psycopg3 today) in both pip install and in import (import psycopg2). THe dll aparently is the: libpq.dll and requires some import like: import os
os.environ[‘PATH’] = r’C:\postgresql\bin;’ + os.environ[‘PATH’]
import psycopg2
Of course we won’t change the os path from within python, but on the shell of the os.
The problem is. If you replace this line above you break for everybody using postgres before 17 I believe. Thus we need some configuration that allows choosing whether we want to use a DDL client for dbt connection based on the old postgres or the new postgres.
Your thoughts? Can you reproduce it?