PostgreSQL ​

PostgreSQL is a relational database management system that can elevate your data handling.

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:

bash
psql -c "\password"

Connecting from Workbench ​

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

bash
psql --host=/var/run/postgresql

Connecting through pgAdmin in Workbench ​

Session settings:

  • Host: /var/run/postgresql
  • Port: 5432
  • Username: use your username

pgadmin-add-database-settings

Connect to database:

  • When asked for password use same password as configured in the beggining of the guide

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:

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

Installing psql client in Workbench ​

You can install psql using conda into default environment:

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

Then connect:

bash
psql --host=/var/run/postgresql

Installing Python connectors in Workbench ​

You can install psycopg2 using conda into py310 environment:

bash
conda install -c conda-forge -n py310 psycopg2

Example of connecting to database named with username:

python
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:

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