9. Debugging PostgreSQL Port 5433 and Column Does Not Exist Error

   I am creating a Django application using PostgreSQL (PSQL) for my database and was nearly finished with the API when I discovered some strange behavior. After successfully testing the API in the Django app, I decided to run some basic queries on the database. I received the following error for nearly every field in the app:

       select MaxSceneKey from game_progress_gameplaykeys;
       ERROR:  column "maxscenekey" does not exist
       LINE 1: select MaxSceneKey from game_progress_gameplaykeys;
                      ^
       HINT:  Perhaps you meant to reference the column "game_progress_gameplaykeys.MaxSceneKey".
   

   I was getting the same result for every field in the table that I tried (and when I try to include the table name as the hint suggests), except for ‘user_id’ and ‘objective'.

   I confirmed that the fields existed using \d+ game_progress_gameplaykeys, tried changing some of their field types, and even upgraded from Postgres 9.5 to 10.5 (I was planning to do this anyway).

   After a bunch of searching, I found the issue:

   “All identifiers (including column names) that are not double-quoted are folded to lower case in PostgreSQL.” from https://stackoverflow.com/questions/20878932/are-postgresql-column-names-case-sensitive

   I created camelCase field names in my Django app based on what the field names were previously in my application (written in C#).

   I decided to fix this (for now) by fixing my models to all use snake_case and using https://github.com/vbabiy/djangorestframework-camel-case to switch the keys from camelCase to snake_case when they come into the API. One issue solved!

   While debugging that issue, I decided to update my laptop’s code + postgres version since I hadn’t worked on it in a while and wanted to see if the issue was just on my desktop. When I reinstalled PSQL, I couldn’t seem to log into it using the user I was creating. Using the postgres user was fine, though.

   I finally figured out the issue was that PSQL was running on port 5433, not 5432 (the default). After that, I was puzzling over what could be running on 5432 since ‘netstat’ and ‘lsof’ revealed nothing else running on my WSL Ubuntu VM. As I was searching around, I saw someone mention that really only PSQL should be running on that port, and I realized I had installed PSQL on Windows on that machine before I moved over to WSL. I uninstalled that, switched back to 5432 in Linux, restarted PSQL, and boom, good to go.

   While I was debugging that issue, I learned some good information about PSQL along the way:

   /etc/postgresql/10/main/postgresql.conf allows you to set and check the port that PSQL is running on.

   /etc/postgresql/10/main/pg_hba.conf allows you to set different security protocols for connections to PSQL. Notable for local development: set the local connection lines to ‘trust’ so you don’t have to enter a password when logging in.

   Note: you need to restart the PSQL server for either of these changes to take effect. Note 2: MORE IMPORTANT NOTE: Don’t use trust anywhere other than a local version of PSQL. Ever.

   These are the lines I had to change to get that to work (may be different in versions of PSQL other than 10.5):

   # "local" is for Unix domain socket connections only
   local   all             all                                     trust
   # IPv4 local connections:
   host    all             all             127.0.0.1/32            trust
   # IPv6 local connections:
   host    all             all             ::1/128                 trust
   

10. Python Reference Talks

    While trying to get more familiar with Django, I started watching talks from DjangoCon from the last few years. I can’t seem to find the talk, but one of them had a list of great Python/Django talks, which inspired me to create me own list (with some definite overlap).

    I have found that revisiting talks like these make me reconsider some design problems that I have recently worked through, so I want to keep a list and rewatch these periodically. I will likely add to this list in the future.

    • Greg Ward - How to Write Reusable Code

    • You Don’t Need That! - Christopher Neugebauer (About Design Patterns in Python)

    • Raymond Hettinger - Beyond PEP 8 -- Best practices for beautiful intelligible code

    • Raymond Hettinger - Transforming Code into Beautiful, Idiomatic Python

    • Raymond Hettinger - Python's Class Development Toolkit

    • Loop like a native: while, for, iterators, generators

    These two go together (from DjangoCon2015): - Jane Austen on Pep 8 - Lacey Williams Henschel - The Other Hard Problem: Lessons and Advice on Naming Things by Jacob Burch

11. Setting up Conda, Django, and PostgreSQL in Windows Subsystem for Linux

    Because I feel much more comfortable in a terminal than on the Windows command line (or in Powershell), I’ve really been enjoying Windows Subsystem for Linux (WSL). In fact, I use it almost exclusively for accessing the server I run this blog from. WSL is essentially a Linux VM of only a terminal shell in Windows (with no GUI access to Linux) and no lag (like you get in most VMs).

    When I created my Grocery List Flask App, I began by using WSL. However, I ran into an issue that prevented me from seeing a locally hosted version of the API in Windows, so I switched to the Windows command line for that app.

    Recently, I’ve been developing a Django application (more on that in a future post), and I ran into a similar issue. Between posting the issue with localhost on WSL and starting this new app, there was a response that I had been meaning to check out. I found that for Django and PostgreSQL, making sure everything was running from localhost (or 0.0.0.0) instead of 127.0.0.x, seemed to fix any issues I had. PSQL gave me some issues just running within WSL, but I found that I just need to add '-h localhost' to get it to run.

    Below are the commands I used to get Conda, Django, and PSQL all set up on my PC and then again my laptop. This works for Django 2.0, PSQL 9.5, and Conda 4.5.9.

    Installation Instructions

    Edit: I originally had installation instructions in here for PSQL 9.5. If you want 9.5 in Ubuntu, good news! You already have it. To install the newest version of PSQL, you should uninstall that version first, then install the new version from here

    Install Conda (need to restart after installing for it to recognize ‘conda’ commands)

    #create environment
    conda create --name NameOfEnvironment
    #activate environment
    source/conda activate NameOfEnvironment
    #install Django
    conda install -c anaconda django
    #install psycopg2, to interface with PSQL
    conda install -c anaconda psycopg2
    
    If you get ‘permission denied’, or it hangs, just rerun the install command that failed. Not sure why, but that fixed things for me.
    
    #Remove PSQL from Ubuntu:
    sudo apt-get --purge remove postgresql\*
    #Then run this to make sure you didn’t miss any:
    dpkg -l | grep postgres
    
    #Install PSQL 10 using instructions here: https://www.postgresql.org/download/linux/ubuntu/ I have copied them here for convenience, but please double check that they have not changed
    #Create the file /etc/apt/sources.list.d/pgdg.list and add the following line:
    #deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main
    
    #Then exeucte the following three commands
    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
    sudo apt-get update
    sudo apt-get install postgresql-10
    
    sudo service postgresql start    
    sudo -i -u postgres -h localhost
    createuser --interactive
           psql user: local_user
           y to superuser
    
    #create the database
    createdb local_db
    #log into local_db
    psql -d local_db
    
    #privileges for Django to modify tables.
    GRANT ALL PRIVILEGES ON DATABASE local_db TO local_user;
    
    ALTER USER local_user WITH PASSWORD 'password';
    
    '\q' to quit interactive console.
    'exit' to leave postgres as postgres user.
    
    #one line command to log in as the user to check tables during development.
    psql -h localhost -d local_db -U local_user
    
    python manage.py makemigrations
    python manage.py migrate
    
    Now log back in to PSQL using the line above, then enter '\dt' and you should see tables like django_admin_log, django_content_type, django_migrations, and django_sessions. Your PSQL DB is now connected to your Django app!
    
    #optional for now, but allows you to ensure db connection works by storing credentials for the superuser you create.
    python manage.py createsuperuser
    
    #command to run the server. go to localhost:8000 in your web browser to view!
    python manage.py runserver 0.0.0.0:8000
    

    I used this post for reference

⇇ « Page 2 / 2