Using PostgreSQL in CoCalc in 2 Minutes!

You can easily run your own PostgreSQL server inside any CoCalc project to manage data (or just learn about SQL and databases).

Step 1: Setup

(only needed once)

In a terminal (+New --> Terminal), paste this:

cd
pg_ctl initdb -D postgres_data
echo "unix_socket_directories = '$HOME/postgres_data'" >> postgres_data/postgresql.conf
echo "unix_socket_permissions = 0700" >> postgres_data/postgresql.conf

The postgres_data subdirectory is where the data files and the communication socket are stored.

The unix_socket_* settings make sure that local communication with the database runs through the files in your own project.

The *_permissions setting disables reading and writing for anyone else. This isn't necessary because CoCalc uses a Docker container for each project, so there are no other users to worry about.

Step 2: Start

pg_ctl -D postgres_data -l logfile -o "-h ''" start

This command starts the postgresql server listening on a Unix domain socket and not on an IP address and port number. To check the status, either run cat logfile to see the logfile's content or run

pg_ctl -D postgres_data status

Tip: you can add the 'pg_ctl...start' line at the bottom of your .bash_aliases file.

Step 3: Connect to the database

export PGHOST="/home/user/postgres_data"
createdb test
psql test 

Output:

psql (9.6.1)
Type "help" for help.
 
test=#  

and now you're off to the races!

test=# create table people (name varchar, age int);
CREATE TABLE
test=# insert into people values ('joe', 10), ('fred', 20);
INSERT 0 2
test=# select * from people;
 name | age
------+-----
 joe  |  10
 fred |  20
(2 rows)
test=# \q
~$

You can also use PostgreSQL from Python (e.g., a Sage worksheet, Jupyter notebook, script, etc.) like so:

import psycopg2
conn = psycopg2.connect("dbname='test' host=/home/user/postgres_data")
cur = conn.cursor()
cur.execute("SELECT * FROM people")
rows = cur.fetchall()
print("\nShow me the databases:\n")
for row in rows:
    print("   ", row[0])

See https://wiki.postgresql.org/wiki/Psycopg2_Tutorial

NOTE: With this setup, you will not have the usual worries about passwords and users because the database is not accessible from outside your project.

Step 4: shutting down

After you have finished working with PostgreSQL, the following step prevents data loss:

pg_ctl -D postgres_data stop