Connect to Postresql database from Google Colab

问题:

I am trying to connect my Google Colab to my Postgres DB. When I try to connect to from Jupyter Notebook it's working, so I'm guessing that my credentials are fine. This is the error that I got:

could not connect to server: No such file or directory

Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

How can I solve it? My Postgres running on my machine.

Thanks!

回答1:

You can also install PostgreSQL in Colab.

# install
!apt install postgresql postgresql-contrib &>log
!service postgresql start
!sudo -u postgres psql -c "CREATE USER root WITH SUPERUSER"
# set connection
%load_ext sql
%config SqlMagic.feedback=False 
%config SqlMagic.autopandas=True
%sql postgresql+psycopg2://@/postgres

Then you can query using %sql or %%sql magic

df = %sql SELECT * FROM pg_catalog.pg_tables
df

回答2:

Your Jupyter is working locally, that's why it sees Postgres. Google Colab is somewhere in the clouds, so it doesn't :)

You need to connect Colab to your local runtime:

Connect to the local runtime: In Colaboratory, click the "Connect" button and select "Connect to local runtime...". Enter the URL from the previous step in the dialog that appears and click the "Connect" button. After this, you should now be connected to your local runtime.

(for details like URL from previous step, see source: https://research.google.com/colaboratory/local-runtimes.html)

Then you can try something like:

%sql postgresql://username:@localhost:5432/username

but I'm not sure if it works.