Instructions for getting started with postgresql on linux

postgresql_instructions.md

1. Install postgresql using your system's package manager.

2. Switch to user postgres and create a new user that matches your regular username

   sudo su postgres
   createuser -d (YOUR USERNAME HERE)
   

3. Launch a postgresql shell, still as user postgres

   psql
   

4. In the postgresql shell, run the following command

   show hba_file;
   

5. Exit the shell and edit the file that it printed out. For example, on Ubuntu 16.04:

   vim /etc/postgresql/9.5/main/pg_hba.conf
   

   Find these lines:

    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
   

   On Fedora, in the last column, md5 is replaced with ident

   Replace with:

   # IPv4 local connections:
   host    all             all             127.0.0.1/32            trust
   # IPv6 local connections:
   host    all             all             ::1/128                 trust
   

   I think this is safe, since you have to be connecting from localhost in order for the trusted rule to kick in. Another option is to set a password or install the oidentd package and use ident, but I didn't experiment with that. Save and exit.

6. On Ubuntu, also edit /etc/postgresql/9.5/main/postgresql.conf. Find the line containing the string unix_socket_directories and change it to say:

   unix_socket_directories = '/var/run/postgresql,  /tmp'
   

   We need to do this because psycopg2 expects the socket to be in the /tmp directory. Alternatively, we could provide a host='localhost' keyword arg when calling psycopg2.connect, but if I'm not mistaken, this means we are using TCP/IP instead of unix sockets, which might be slightly slower.

7. If the postgresql server is running, restart it:

   sudo systemctl restart postgresql
   

8. Lastly, create a database named like your username. You can do this as your regular user.

   createdb (YOUR USERNAME HERE)
   

   This is to work around some issue with SQLAlchemy. Postgres by default assumes that you want to connect to a database with the same name as your user, and it seems like SQLAlchemy tries to connect to such a database even if you specify another database to connect to. So for instance, in the notebook when you try to run database_exists(engine.url), you get an error unless you have created a database named like your username. It's strange because psycopg2.connect is perfectly even if you haven't created the default database.

There are probably some sub-optimal steps in here, but this is enough to get things running quickly.

Great instructions, thank you! I also had to grant permissions to the new user, e.g., as in here: https://stackoverflow.com/questions/13497352/error-permission-denied-for-relation-tablename-on-postgres-while-trying-a-selec