PostgreSQL 12 with pgadmin4 on Kubuntu 20.04

[GreyGeek]

Note that while PostgreSQL 12 is in the 20.04 repository, pgadmin4 is not This post details my experiment using the howto article linked here which explains how to install pgadmin4.

Before I retired 13 years ago I used to write client software against Oracle servers, using Kate as my editor and PostgreSQL as my oracle substitute. It was running on a SuSE 6.3 server in my office. I used compiler defines to determine the OS the source was being compiled on so as to switch in the proper code for PostgreSQL or Oracle.

The last time I used PostgreSQL was version 7.4 with pgadmin3, in 2008. At the time I retired I was using PostgreSQL 8,3. I ran across the article linked above while browsing the web yesterday and decided to see what PostgreSQL 12 with pgadmin4 looked like. I have one word to describe my impressions -- WOW!



Below is a screen capture of pgadmin4 running against my jlserver containing jlkdb.


The installation of pgadmin4 also requires the installation of apache2, which is done automatically when installing pgadmin4.

I didn't see an option to create a user from pgadmin4. So, to create a user you can do "sudo su - postgres" to gain access to psql in the postgres account, which is the PostgreSQL console admin tool.

From psql, as postgres, you can create the first user:

CREATE ROLE someusername WITH
  LOGIN
  SUPERUSER
  INHERIT
  CREATEDB
  CREATEROLE
  REPLICATION

Prefix "NO" to any of the options listed above if you don't want someusername to have those options. The SUPERUISER option gives them "WITH PRIVILEGES" (i.e., ALL privileges: SELECT INSERT UPDATE DELETE TRUNCATE REFERENCES TRIGGER CREATE CONNECT TEMPORARY EXECUTE USAGE ). Or you can say "WITH <and list privileges you want them to have>"

Then, while in psql, you can create a database owned by someusername.

CREATE DATABASE someusernamedb
    WITH 
    OWNER = someusername
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_CA.UTF-8'
    LC_CTYPE = 'en_CA.UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

GRANT TEMPORARY, CONNECT ON DATABASE someusernamedb TO PUBLIC;

GRANT ALL ON DATABASE someusernamedb TO someusername;

Now, as a superuser, and without depending on postgres, someusername use pgadmin4's web interface to do everything one needs to do with a PostgreSQL server & database, with a lot more capability than pgadmin3 used to have.

I am going to play with PostgreSQL 12 and see how well the pgsql modules in Python3 and Jupyter Notebook work with it.

[GreyGeek]

I tried to install the Python3 module psychopg2 but psychopg2 would not install because of compiling errors. So, following the suggestion in the compile error msg I installed psycopg2-binary instead:

sudo python3 -m pip install psycopg2-binary

That worked beautifully.

Then wrote some code after consulting the psychopg2 documentation:

import psycopg2
from psycopg2 import Error

try:
    connection = psycopg2.connect(user="jerry",
                                  password="***********",
                                  host="127.0.0.1",
                                  port="5432",
                                  database="jlkdb")

     cursor = connection.cursor()
    cursor.execute("SELECT * FROM .... ;")
    record = cursor.fetchone()
    print(record,'\n')
   .......
....

except (Exception, Error) as error:
    print("Error while connecting to PostgreSQL", error)

finally:
    if (connection):
        cursor.close()
        connection.close()
        print("PostgreSQL connection is closed")

and I'm off and running!

[oshunluvr]

Yeah, I've been using PGAdmin III for the better part of a decade. IMO, PGAdmin IV is not better. Maybe I'm just old and don't want to learn it, LOL. Seems way harder to get things done - as someone who uses it to, well, get things done. Server 12 seems more stable than the 9 series I;m used to and brings a LOT of new functionality, I used server 12 and the postgis extensions to build radius based regional tables from more than 230,000 lat/long points, along with some other cool stuff like aircraft callsign conversion to speech language for text-to-speech and automatic transponder code assignment. All aviation related navigation stuff. Still, not in love with the HTML interface of IV.

[oshunluvr]

Also, just FYI, I found it better to build a bare-bones Ubuntu server VM to run the postgres server. Since postgres stores databases as "schemas" rather than in compressed external files, the server VM makes the whole shebang easily transportable. Which is good considering the 50+ locations I use it at, LOL.