PostgreSQL

PostgreSQL (opens new window) is a relational database management system that can elevate your data handling.

• Set password
• Connecting from Workbench
• Connecting through pgweb in Workbench
• Installing psql client in Workbench
• Installing Python connectors in Workbench

Requirement

This guide is for the managed PostgreSQL pilot service on home machines.

You can use this guide to connect to your installation if you have requested the setup and received a confirmation that the setup is completed.

Set password

First you need to login over ssh and set your password using this command:

psql -c "\password"

Connecting from Workbench

Once you have configured your password you can also login from Workbench Terminal:

psql --host=/var/run/postgresql

Connecting through pgweb in Workbench

• Host: /var/run/postgresql
• Port: 5432
• Username: use your username
• Password: use password configured in the beggining of the guide
• Database: same as username

Easiest way is to connect with Scheme URL:

postgresql:///?host=/var/run/postgresql

Installing psql client in Workbench

You can install psql using conda into default environment:

mamba install -c conda-forge -n default -y postgresql

Then connect:

psql --host=/var/run/postgresql

Installing Python connectors in Workbench

You can install psycopg2 using conda into py310 environment:

conda install -c conda-forge -n py310 psycopg2

Example of connecting to database named with username:

import os
import psycopg2
print(f"Connecting to dbname={os.getenv('USER')} as user={os.getenv('USER')}")
conn = psycopg2.connect(f"host=/var/run/postgresql dbname={os.getenv('USER')} user={os.getenv('USER')}")

Test connection by listing pg_tables records fron pg_catalog:

with conn:
    with conn.cursor() as curs:
        curs.execute('SELECT * FROM pg_catalog.pg_tables;')
        for table in curs.fetchall():
            print(table)