MacLochlainns Weblog

The ODBC library you use when connecting Python to PostgreSQL is the psycopg2 Python library. This blog post will show use how to use it in Python and install it on your Fedora Linux installation. It leverages a videodb database that I show you how to build in this earlier post on configuring PostgreSQL 14.

You would import psycopg2 as follows in your Python code:

import psycopg2

Unfortunately, that only works on Linux servers when you’ve installed the library. That library isn’t installed with generic Python libraries. You get the following error when the psycopg2 library isn’t installed on your server.

Traceback (most recent call last):
  File "python_new_hire.sql", line 1, in <module>
    import psycopg2
ModuleNotFoundError: No module named 'psycopg2'

You can install it on Fedora Linux with the following command:

yum install python3-psycopg2

It will install:

====================================================================================
 Package                  Architecture   Version               Repository      Size
====================================================================================
Installing:
 python3-psycopg2         x86_64         2.7.7-1.fc30          fedora         160 k
 
Transaction Summary
====================================================================================
Install  1 Package
 
Total download size: 160 k
Installed size: 593 k
Is this ok [y/N]: y
Downloading Packages:
python3-psycopg2-2.7.7-1.fc30.x86_64.rpm            364 kB/s | 160 kB     00:00    
------------------------------------------------------------------------------------
Total                                               167 kB/s | 160 kB     00:00     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                            1/1 
  Installing       : python3-psycopg2-2.7.7-1.fc30.x86_64                       1/1 
  Running scriptlet: python3-psycopg2-2.7.7-1.fc30.x86_64                       1/1 
  Verifying        : python3-psycopg2-2.7.7-1.fc30.x86_64                       1/1 
 
Installed:
  python3-psycopg2-2.7.7-1.fc30.x86_64                                              
 
Complete!

Here’s a quick test case that you can run in PostgreSQL and Python to test all the pieces. The first SQL script creates a new_hire table and inserts two rows, and the Python program queries data from the new_hire table.

The new_hire.sql file creates the new_hire table and inserts two rows:

-- Environment settings for the script.
SET SESSION "videodb.table_name" = 'new_hire';
SET CLIENT_MIN_MESSAGES TO ERROR;
 
--  Verify table name.
SELECT current_setting('videodb.table_name');
 
-- ------------------------------------------------------------------
--  Conditionally drop table.
-- ------------------------------------------------------------------
DROP TABLE IF EXISTS new_hire CASCADE;
 
-- ------------------------------------------------------------------
--  Create table.
-- -------------------------------------------------------------------
CREATE TABLE new_hire
( new_hire_id  SERIAL
, first_name   VARCHAR(20)  NOT NULL
, middle_name  VARCHAR(20)
, last_name    VARCHAR(20)  NOT NULL
, hire_date    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
, PRIMARY KEY (new_hire_id));
 
-- Alter the sequence by restarting it at 1001.
ALTER SEQUENCE new_hire_new_hire_id_seq RESTART WITH 1001;
 
-- Display the table organization.
SELECT   tc.table_catalog || '.' || tc.constraint_name AS constraint_name
,        tc.table_catalog || '.' || tc.table_name AS table_name
,        kcu.column_name
,        ccu.table_catalog || '.' || ccu.table_name AS foreign_table_name
,        ccu.column_name AS foreign_column_name
FROM     information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu
ON       tc.constraint_name = kcu.constraint_name
AND      tc.table_schema = kcu.table_schema JOIN information_schema.constraint_column_usage AS ccu
ON       ccu.constraint_name = tc.constraint_name
AND      ccu.table_schema = tc.table_schema
WHERE    tc.constraint_type = 'FOREIGN KEY'
AND      tc.table_name = current_setting('videodb.table_name')
ORDER BY 1;
 
SELECT c1.table_name
,      c1.ordinal_position
,      c1.column_name
,      CASE
         WHEN c1.is_nullable = 'NO' AND c2.column_name IS NOT NULL THEN 'PRIMARY KEY'
         WHEN c1.is_nullable = 'NO' AND c2.column_name IS NULL THEN 'NOT NULL'
       END AS is_nullable
,      CASE
         WHEN data_type = 'character varying' THEN
           data_type||'('||character_maximum_length||')'
         WHEN data_type = 'numeric' THEN
           CASE
             WHEN numeric_scale != 0 AND numeric_scale IS NOT NULL THEN
               data_type||'('||numeric_precision||','||numeric_scale||')'
             ELSE
               data_type||'('||numeric_precision||')'
             END
         ELSE
           data_type
        END AS data_type
FROM    information_schema.columns c1 LEFT JOIN
          (SELECT trim(regexp_matches(column_default,current_setting('videodb.table_name'))::text,'{}')||'_id' column_name
           FROM   information_schema.columns) c2
ON       c1.column_name = c2.column_name
WHERE    c1.table_name = current_setting('videodb.table_name')
ORDER BY c1.ordinal_position;
 
-- Display primary key and unique constraints.
SELECT constraint_name
,      lower(constraint_type) AS constraint_type
FROM   information_schema.table_constraints
WHERE  table_name = current_setting('videodb.table_name')
AND    constraint_type IN ('PRIMARY KEY','UNIQUE');
 
-- Insert two test records.
INSERT INTO new_hire
( first_name, middle_name, last_name, hire_date )
VALUES
 ('Malcolm','Jacob','Lewis','2018-2-14')
,('Henry',null,'Chabot','1990-07-31');

You can put it into a local directory, connect as the student user to a videodb database, and run the following command (or any database you’ve created).

\i new_hire.sql

The new_hire.py file creates the new_hire table and inserts two rows:

# Import the PostgreSQL connector library.
import psycopg2
 
try:
  # Open a connection to the database.
  connection = psycopg2.connect( user="student"
                               , password="student"
                               , port="5432"
                               , dbname="videodb")
 
  # Open a cursor.
  cursor = connection.cursor()
 
  # Assign a static query.
  query = "SELECT new_hire_id, first_name, last_name " \
          "FROM new_hire"
 
  # Parse and execute the query.
  cursor.execute(query)
 
  # Fetch all rows from a table.
  records = cursor.fetchall()
 
  # Read through and print the rows as tuples.
  for row in range(0, len(records)):
    print(records[row]) 
 
except (Exception, psycopg2.Error) as error :
  print("Error while fetching data from PostgreSQL", error)
 
finally:
  # Close the database connection.
  if (connection):
    cursor.close()
    connection.close()

You run it from the command line, like:

python3 ./new_hire.py

It should print:

(1001, 'Malcolm', 'Lewis')
(1002, 'Henry', 'Chabot')

As always, I hope this helps those trying to sort out how to connect Python to PostgreSQL.

Problems get reported to me all the time on installations for my students, this one was interesting. They got an error complaining about a missing libpq.so.5 library.

psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql)
psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql)
could not change directory to "/root": Permission denied
psql (11.7, server 11.8)
Type "help" for help.
 
postgres=#

It appeared as a mismatch of libraries but it’s not that. For reference, this was a Fedora instance. I ran the rpm utility:

rpm -qa | grep postgres

It returned:

postgresql11-libs-11.8-1PGDG.f30.x86_64
postgresql-11.7-2.fc30.x86_64
postgresql-server-11.7-2.fc30.x86_64

Then, I had them run the rpm utility again looking for the Python driver for PostgreSQL:

rpm -qa | grep psycopg2

It returned:

python3-psycopg2-2.7.7-1.fc30.x86_64

Then, it was easy to explain. The Python psycopg2 library uses both PostgreSQL 11.7 and 11.8 dependent libraries and the libpq.so.5 library is missing version information. You must ignore the error, which is really only a warning message, when you want to work on Fedora, PostgreSQL 11, and Python 3.

The most straightforward way to view the description of a PostgreSQL table is the \d command. For example, this lets you display an account_list table:

\d account_list

Unfortunately, this shows you the table, indexes, and foreign key constraints. Often, you only want to see the list of columns in positional order. So, I wrote a little function to let me display only the table and columns.

There are a few techniques in the script that might seem new to some developers. For example, the data types of the return parameter values of a function that returns values from the data dictionary are specific to types used by the data dictionary. These specialized types are required because the SQL cursor gathers the information from the data dictionary in the information_schema, and most of these types can’t be cast as variable length strings.

A simple assumption that the data dictionary strings would implicitly cast to variable length strings is incorrect. That’s because while you can query them like VARCHAR variables they don’t cast to variable length string. If you wrote a wrapper function that returned VARCHAR variables, you would probably get a result like this when you call your function:

ERROR:  structure of query does not match function result type
DETAIL:  Returned type information_schema.sql_identifier does not match expected type character varying in column 1.

The “character varying” is another name for a VARCHAR data type. Some notes will advise you to fix this type of error by using the column name and a %TYPE. The %TYPE anchors the data type in the function’s parameter list to the actual data type of the data dictionary’s table. You would implement that suggestion with code like:

RETURNS TABLE ( table_schema      information_schema.columns.table_schema%TYPE
              , table_name        information_schema.columns.table_name%TYPE
              , ordinal_position  information_schema.columns.ordinal_position%TYPE
              , column_name       information_schema.columns.column_name%TYPE
              , data_type         information_schema.columns.data_type%TYPE
              , is_nullable       information_schema.columns.is_nullable%TYPE ) AS

Unfortunately, your function would raise a NOTICE for every dynamically anchored column at runtime. The NOTICE messages would appear as follows for the describe_table function with anchored parameter values:

psql:describe_table.sql:34: NOTICE:  type reference information_schema.columns.table_schema%TYPE converted to information_schema.sql_identifier
psql:describe_table.sql:35: NOTICE:  type reference information_schema.columns.table_name%TYPE converted to information_schema.sql_identifier
psql:describe_table.sql:36: NOTICE:  type reference information_schema.columns.ordinal_position%TYPE converted to information_schema.cardinal_number
psql:describe_table.sql:37: NOTICE:  type reference information_schema.columns.column_name%TYPE converted to information_schema.sql_identifier
psql:describe_table.sql:38: NOTICE:  type reference information_schema.columns.data_type%TYPE converted to information_schema.character_data
psql:describe_table.sql:39: NOTICE:  type reference information_schema.columns.is_nullable%TYPE converted to information_schema.yes_or_no

As a rule, there’s a better solution when you know how to discover the underlying data types. You can discover the required data types with the following query of the pg_attribute table in the information_schema:

SELECT attname
,      atttypid::regtype
FROM   pg_attribute
WHERE  attrelid = 'information_schema.columns'::regclass
AND    attname IN ('table_schema','table_name','ordinal_position','column_name','data_type','is_nullable')
ORDER  BY attnum;

It returns:

     attname      |              atttypid
------------------+------------------------------------
 table_schema     | information_schema.sql_identifier
 table_name       | information_schema.sql_identifier
 ordinal_position | information_schema.cardinal_number
 column_name      | information_schema.sql_identifier
 is_nullable      | information_schema.yes_or_no
 data_type        | information_schema.character_data
(6 rows)

Only the character_data type can be replaced with a VARCHAR data type, the others should be typed as shown above. Here’s the modified describe_table function.

CREATE OR REPLACE
  FUNCTION describe_table (table_name_in  VARCHAR)
  RETURNS TABLE ( table_schema      information_schema.sql_identifier
                , table_name        information_schema.sql_identifier
                , ordinal_position  information_schema.cardinal_number
                , column_name       information_schema.sql_identifier
                , data_type         VARCHAR
                , is_nullable       information_schema.yes_or_no ) AS
$$
BEGIN
  RETURN QUERY
  SELECT   c.table_schema
  ,        c.table_name
  ,        c.ordinal_position
  ,        c.column_name
  ,        CASE
             WHEN c.character_maximum_length IS NOT NULL
             THEN CONCAT(c.data_type, '(', c.character_maximum_length, ')')
             ELSE
               CASE
                 WHEN c.data_type NOT IN ('date','timestamp','timestamp with time zone')
                 THEN CONCAT(c.data_type, '(', numeric_precision::text, ')')
                 ELSE c.data_type
               END
           END AS modified_type
  ,        c.is_nullable
  FROM     information_schema.columns c
  WHERE    c.table_schema NOT IN ('information_schema', 'pg_catalog')
  AND      c.table_name = table_name_in
  ORDER BY c.table_schema
  ,        c.table_name
  ,        c.ordinal_position;
END;
$$ LANGUAGE plpgsql;

If you’re new to PL/pgSQL table functions, you can check my basic tutorial on table functions. You call the describe_table table function with the following syntax:

SELECT * FROM describe_table('account_list');

It returns:

 table_schema |  table_name  | ordinal_position |   column_name    |        data_type         | is_nullable
--------------+--------------+------------------+------------------+--------------------------+-------------
 public       | account_list |                1 | account_list_id  | integer(32)              | NO
 public       | account_list |                2 | account_number   | character varying(10)    | NO
 public       | account_list |                3 | consumed_date    | date                     | YES
 public       | account_list |                4 | consumed_by      | integer(32)              | YES
 public       | account_list |                5 | created_by       | integer(32)              | NO
 public       | account_list |                6 | creation_date    | timestamp with time zone | NO
 public       | account_list |                7 | last_updated_by  | integer(32)              | NO
 public       | account_list |                8 | last_update_date | timestamp with time zone | NO
(8 rows)

As always, I hope this helps those looking for a solution to functions that wrap the data dictionary and display table data from the PostgreSQL data dictionary.

How to write an overloaded set of hello_world functions in PostgreSQL PL/pgSQL. The following code lets you write and test overloaded functions and the concepts of null, zero-length string, and string values.

-- Drop the overloaded functions.
DROP FUNCTION IF EXISTS hello_world(), hello_world(whom VARCHAR);
 
-- Create the function.
CREATE FUNCTION hello_world()
RETURNS text AS
$$
DECLARE
  output  VARCHAR(20);
BEGIN
  /* Query the string into a local variable. */
  SELECT 'Hello World!' INTO output;
 
  /* Return the output text variable. */
  RETURN output;
END
$$ LANGUAGE plpgsql;
 
-- Create the function.
CREATE FUNCTION hello_world(whom VARCHAR)
RETURNS text AS
$$
DECLARE
  output  VARCHAR(20);
BEGIN
  /* Query the string into a local variable. */
  IF whom IS NULL OR LENGTH(whom) = 0 THEN
    SELECT 'Hello World!' INTO output;
  ELSE
    SELECT CONCAT('Hello ', whom, '!') INTO output;
  END IF;
 
 
  /* Return the output text variable. */
  RETURN output;
END
$$ LANGUAGE plpgsql;
 
-- Call the function.
SELECT hello_world();
SELECT hello_world(Null) AS output;
SELECT hello_world('') AS output;
SELECT hello_world('Harry') AS output;

It should print:

    output
--------------
 Hello World!
(1 row)
 
    output
--------------
 Hello World!
(1 row)
 
    output
--------------
 Hello World!
(1 row)
 
    output
--------------
 Hello Harry!
(1 row)

As always, I hope this helps those looking for the basics and how to solve problems.

This post is a step-by-step install guide to PostgreSQL 14 on Windows 10. It sometimes makes me curious that folks want a summary of screen shots from a Microsoftw Software Installer (MSI) because they always appear to me as straightforward.

This walks you through installing PostgreSQL 14, EDS’s version of Apache, supplemental connection libraries, and pgAdmin4. You can find the post-installation steps in my earlier Configure PostgreSQL 14 post.

PostgreSQL Database 14 Installation Steps

1. The first thing you need to do is download the PostgreSQL MSI file, which should be in your C:\Users\username\Downloads directory. You can double-click on the MSI file.

2. After double-clicking on the MSI file, you are prompted by User Account Control to allow the PostgreSQL MSI to make changes to your device. Clicking the Yes button is the only way forward.

3. The Setup – PostgreSQL dialog requires you click the Next button to proceed.

4. The Installation Directory dialog prompts you for an installation directory. The default directory is C:\Program Files\PostgreSQL\14 and you should use it. Click the Next button to continue.

5. The Select Components dialog prompts you to choose the products to install. You should choose all four – PostgreSQL Server, pgAdmin 4, Stack Builder, and Command Line Tools. Click the Next button to continue.

6. The Password dialog prompts you for the PostgreSQL superuser password. In a development system for your local computer, you may want to use something straightforward like cangetin. Click the Next button to continue.

7. The Setup dialog lets you select the port number for the PostgreSQL listener. Port 5432 is the standard port for a PostgreSQL database, and ports 5433 and 5434 are used sometimes. Click the Next button to continue.

8. The Advanced Options dialog lets you select the Locale for the database. As a rule for a development instance you should chose the Default locale. Click the Next button to continue.

9. The Pre Installation Summary dialog tells you what you’ve chosen to install. It gives you a chance to verify what you are installing. Click the Next button to continue.

10. The Ready to Install dialog lets you pause before you install the software. Click the Next button to continue.

11. The Installing dialog is a progress bar that will take several minutes to complete. When the progress bar completes, click the Next button to continue.

12. The Completing the PostgreSQL Setup Wizard dialog tells you that the installation is complete. Click the Finish button to complete the PostgreSQL installation.

13. The Welcome to Stack Builder! dialog lets you choose an installation from those on your computer to build a software stack. Click the drop down box to chose an installation.

14. The second copy of the Welcome to Stack Builder! dialog shows the choice of the PostgreSQL installation you just completed. Click on the Next button to continue.

15. The Stack Builder dialog prompts you to choose the products to install. You should choose all four database drivers – Npgsql, pgJDBC, psqlODBC, psqlODBC; and the PostGIS 3.1 and PostGIS 3.2 Bundles for PostgreSQL. Then, click the Next button to continue.

16. The Stack Builder dialog shows you the products you will install. You should choose all four database drivers – Npgsql, pgJDBC, psqlODBC, psqlODBC; and the PostGIS 3.1 and PostGIS 3.2 Bundles for PostgreSQL. Click the Next button to continue.

17. The Stack Builder dialog shows a download progress bar subdialog, which may take some time to complete. The Stack Builder dialog’s Progress Bar automatically advances to the next dialog box.

18. The Stack Builder dialog tells you the products you downloaded. Click the Next button to continue the developer stack.

19. The Setup dialog advises that you are installing the PEM-HTTPD Setup Wizard. Click the Next button to continue.

20. The Installation Directory dialog prompts you for an installation directory. The default directory is C:\Program Files (x86)\edb\pem\httpd and you should use it because that’s where Windows 10 puts 64-bit libraries. Click the Next button to continue.

21. The Setup dialog lets you select the port number for the HTTP listener. Port 8080 is the standard port for an HTTP listener, and ports 8081, 8082, and so forth are used when you have more than one HTTP listener on a single server or workstation. Click the Next button to continue.

22. The Ready to Install dialog lets you pause before you install the software. Click the Next button to continue.

23. The Installing dialog is a progress bar that will take several minutes to complete. When the progress bar completes, click the Next button when it becomes available to continue.

24. The Windows Security Alert dialog asks you to allow the Apache HTTP Server to use port 8080. Click the Private networks, such as my home or work network checkbox and then the Allow access button to continue.

25. The Setup dialog advises that you have completed the installation of the PEM-HTTPD Setup Wizard. Click the Finish button to continue.

26. The Stack Builder dialog advises you that all four database drivers – Npgsql, pgJDBC, psqlODBC, psqlODBC are downloaded and ready to install. Click the Next button to continue.

27. The Installation Directory dialog prompts you for an installation directory. The default directory is C:\Program Files (x86)\PostgreSQL\Npgsql and you should use it because that’s where Windows 10 puts 64-bit libraries and the subdirectory meets the standard installation convention for Microsoft .Net libraries. Click the Next button to continue.

28. The Ready to Install dialog lets you pause before you install the Npgsql software for Microsoft .Net. Click the Next button to continue.

29. The Setup dialog advises that you have completed the installation of npgsql driver for Microsoft .Net. Click the Finish button to continue.

30. The Setup dialog advises that you are installing the pgJDBC diver Setup Wizard. Click the Next button to continue.

31. The Installation Directory dialog prompts you for an installation directory. The default directory is C:\Program Files (x86)\PostgreSQL\pgJDBC and you should use it because that’s where Windows 10 puts 64-bit libraries and the subdirectory meets the standard installation convention for libraries. Click the Next button to continue.

32. The Ready to Install dialog lets you pause before you install the pgJDBC software. Click the Next button to continue.

33. The Setup dialog advises that you have completed the installation of pgJDBC driver. Click the Finish button to continue.

34. The Advisory Message pgAdmin is Starting dialog is really telling you to be patient. It can take a couple minutes to launch pgAdmin.

35. The Password dialog prompts you for the pgAdmin superuser password. In a development system for your local computer, you may want to use something straightforward like cangetin. Click the Next button to continue.

36. Enter your password from the earlier step and click the OK button.

37. This is the pgAdmin console. You should see one database and tweleve login/group roles.

The foregoing walked you through the installation of PostgreSQL, the connector libraries and pgAdmin utility. I hope it helps those who would like to see the installation steps.

After you install PostgreSQL 14 on Windows, there are a few steps to create a sandbox database. This post shows you those steps, including a couple Windows OS tasks you’ll need to complete. You should note that these instructions are for the PostgreSQL psql Command Line Interface (CLI).

Open a Command Prompt with Administrator privileges. It should give you a command prompt like the following:

Microsoft Windows [Version 10.0.19042.1466]
(c) Microsoft Corporation. All rights reserved.
 
C:\Users\username>

Type psql to launch the PostgreSQL CLI and then the return or enter key:

C:\Users\username>psql

Most likely, you’ll get the following error message. It means that your System Path environment variable doesn’t include the directory where the psql executable is found, and that’s why the command is not recognized.

'psql' is not recognized as an internal or external command, operable program or batch file.

You can set it temporarily in your current Windows CLI with the following command:

set PATH=%PATH%;C:\Program Files\PostgreSQL\14\bin;

For those familiar with Windows CLI navigation in prior releases, the convention is to append a semicolon at the end of the item added to the %PATH% environment variable. If you were to put the semicolon between the %PATH% and new directory path there would be two semicolons together. While it won’t do any harm, it’s best to follow the new convention or style.

CRITICAL NOTE: The rest of the post assumes you have set the correct %PATH% environment variable or added it to your System’s Path environment variable and restarted the Windows CLI after adding it through the GUI tool. The reason you need to restart the Windows CLI is that the %PATH% environment variable is inherited at startup and doesn’t change in an open Windows CLI shell.

Another common mistake some users make, at least those who have used an older version of the psql utility on a Linux distribution (or “distro”), is to type psql without any arguments to become the superuser. This error doesn’t occur in Linux because you most likely connected as the postgres user before trying to connect to the PostgreSQL database. A quick demonstration should illustrate the error and support explaining why it occurs on the Windows OS.

Attempting to connect to the PostgreSQL database as a non-postgres user:

C:\Users\username>psql

You should get the following error:

psql: error: connection to server at "localhost" (::1), port 5432 failed: fe_sendauth: no password supplied

This error occurs because you’re not the postgres user, and all other users must designate that they’re connecting to the superuser account. The correct syntax is:

C:\Users\username>psql -U postgres

Then, you’ll be prompted for the password that you set when you installed PostreSQL database. Enter that password from the installation at the prompt.

Password for user postgres:
psql (14.1)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.
 
postgres=#

The warning message is telling you that the character set collation differs between your Windows OS and the PostgreSQL database. We’ll discuss this more later but for the sake of almost all your work, it won’t matter. If the warning message bothers you, you can run the chcp command before launching PostgreSQL when you open your Windows CLI:

chcp 1252

The chcp command changes your terminal character set to align with the Latin 1 character set, which enables you to use things like non-English accent characters (the umlaut over an o, or ö). After running the You will see this when you connect after running that command:

psql (14.2)
Type "help" for help.
 
postgres#

Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Command Processor

Now that you’re connected as the superuser, let’s examine the steps to configure your playground or videodb database. There are five steps to configure your database and one step to connect and use the videodb database as the student user

1. Create Physical Directory

postgres=# show data_directory;

           data_directory
-------------------------------------
 C:/Program Files/PostgreSQL/14/data
(1 row)

md video_db

2. Create Tablespace

CREATE TABLESPACE video_db
  OWNER postgres
  LOCATION 'C:\Users\username\video_db';

CREATE TABLESPACE

SELECT * FROM pg_tablespace;

  oid  |  spcname   | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        |
  1664 | pg_global  |       10 |        |
 16395 | video_db   |       10 |        |
(3 rows)

3. Create a Database

postgres=# SELECT datname, datcollate FROM pg_database WHERE datname = 'postgres';

  datname  |         datcollate
-----------+----------------------------
 postgres  | English_United States.1252
(1 row)

CREATE DATABASE videodb
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = video_db
       LC_COLLATE = 'English_United States.1252'
       LC_CTYPE = 'English_United States.1252'
       CONNECTION LIMIT = -1;

postgres# \l

                                                 List of databases
   Name    |  Owner   | Encoding |          Collate           |           Ctype            |   Access privileges
-----------+----------+----------+----------------------------+----------------------------+-----------------------
 postgres  | postgres | UTF8     | English_United States.1252 | English_United States.1252 |
 template0 | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =c/postgres          +
           |          |          |                            |                            | postgres=CTc/postgres
 template1 | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =c/postgres          +
           |          |          |                            |                            | postgres=CTc/postgres
 videodb   | postgres | UTF8     | English_United States.1252 | English_United States.1252 |
(4 rows)

COMMENT ON DATABASE videodb IS 'Video Store Database';

4. Create a Role, Grant, and User

5. Create a Schema

psql -U postgres -W

GRANT CREATE ON DATABASE videodb TO student;

QUIT;

psql -U postgres -W

CREATE SCHEMA app;

SELECT   * 
FROM     pg_catalog.pg_namespace
ORDER BY nspname;

  oid  |      nspname       | nspowner |               nspacl
-------+--------------------+----------+-------------------------------------
 16399 | app                |    16398 |
 13388 | information_schema |       10 | {postgres=UC/postgres,=U/postgres}
    11 | pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
    99 | pg_toast           |       10 |
  2200 | public             |       10 | {postgres=UC/postgres,=UC/postgres}
(5 rows)

-- Set client messages to warning or error, which means any
-- notice associated with the if exists clause is suppressed.
SET client_min_messages TO warning;
 
-- Conditionally drop an existing revision_history table in
-- the app schema.
DROP TABLE IF EXISTS revision_history;
 
-- Create a revision_history table.
CREATE TABLE app.revision_history
( revision_history_id serial
, session_id          VARCHAR
, table_name          VARCHAR
, revision_id         INTEGER );

\I revision_history.sql

\d revision_history

Did not find any relation named "revision_history".

show search_path;

   search_path   
-----------------
 "$user", public
(1 row)

SET search_path TO app, "$user", public;

\d revision_history

                                                  Table "app.revision_history"
       Column        |       Type        | Collation | Nullable |                            Default                            
---------------------+-------------------+-----------+----------+---------------------------------------------------------------
 revision_history_id | integer           |           | not null | nextval('revision_history_revision_history_id_seq'::regclass)
 session_id          | character varying |           |          | 
 table_name          | character varying |           |          | 
 revision_id         | integer           |           |          |

6. Connect as student to videodb:

psql -d videodb -U student -W

Password:
psql (14.1)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.
 
videodb=>

SELECT current_database();

 current_database 
------------------
 videodb
(1 row)

This has shown you how to create a videodb tablespace, a videodb database, a dba role, a student user, an app schema, and connect to your new videodb database as the student user. As always, I hope it lets you get a lot down with little effort and avoiding pages and pages of documentation.

A friend asked me a question about using the OUT mode parameter versus INOUT mode parameters in functions. He formed an opinion that they didn’t work in PostgreSQL PL/pgSQL.

Unfortunately, there’s not a lot of material written about how to use the OUT mode parameter in functions. I thought an article showing the standard example with a call to the function might help. The standard example function from the PostgreSQL documentation is:

1
2
3
4
5
6
7
8
9

CREATE OR REPLACE 
  FUNCTION sales_tax( IN  amount  real
                    , OUT tax     real )
  AS $$
BEGIN
  /* Calculate the tax at 6%. */
  tax := amount * 0.06;
END;
$$ LANGUAGE plpgsql;

The RETURNS clause is optional but here’s how you can include it. The following example works exactly like the former.

1
2
3
4
5
6
7
8
9
10

CREATE OR REPLACE 
  FUNCTION sales_tax( IN  amount  real
                    , OUT tax     real )
  RETURNS real AS
$$
BEGIN
  /* Calculate the tax at 6%. */
  tax := amount * 0.06;
END;
$$ LANGUAGE plpgsql;

You call the PostgreSQL sales_tax() function like this:

SELECT 'Sales Tax ['|| sales_tax(200) ||']' AS "Return Value";

It should return the following:

  Return Value  
----------------
 Sales Tax [12]
(1 row)

You can also call it in an inline code block (e.g., what Oracle documentation calls an anonymous block), like:

1
2
3
4
5
6
7
8
9
10
11
12
13

DO
$$
DECLARE
  /* Declare a local variable. */
  tax_paid  real := 0;
BEGIN
  /* Query the results into a local variable. */
  SELECT sales_tax(100) INTO tax_paid;
 
  /* Print the taxes paid. */
  RAISE NOTICE 'Tax Paid [%]',tax_paid;
END;
$$;

You can replace the sales_tax function with its OUT mode tax parameter with the following classic sales_tax function, which adds a tax_rate parameter.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE OR REPLACE
  FUNCTION sales_tax( IN  amount   real
                    , IN  tax_rate real )
  RETURNS real AS $$
DECLARE
  /* Declare a local variable. */
  tax real;
BEGIN
  /* Calculate the tax at 6%. */
  tax := amount * (tax_rate / 100);
 
  /* Return the tax. */
  RETURN tax;
END;
$$ LANGUAGE plpgsql;

Let’s return the original approach with the OUT parameter. Then, let’s expand the list of parameters to include an INOUT mode state variable, like:

1
2
3
4
5
6
7
8
9
10
11
12

CREATE OR REPLACE
  FUNCTION sales_tax( IN     amount   real
                    , IN     tax_rate real
                    , INOUT  state    VARCHAR(14)
                    , OUT    tax      real )
  RETURNS RECORD AS
$$
BEGIN
  /* Calculate the tax at 6%. */
  tax := amount * (tax_rate / 100);
END;
$$ LANGUAGE plpgsql;

You don’t need to include the RETURNS RECORD phrase because PL/pgSQL implements a very mature adapter pattern and it adjusts the return type automatically to the parameter list. On the other hand, many beginning programmers and support staff won’t know that. That’s why I recommend you include it for clarity.

You can call this in a query with a column alias, like:

SELECT 'Sales Tax ['|| sales_tax(100,8.25,'California') ||']' AS "Return Value";

It will return a tuple:

         Return Value          
-------------------------------
 Sales Tax [(California,8.25)]
(1 row)

You can implement it inside an inline block by adding a local variable of the RECORD data type, like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

DO
$$
DECLARE
  /* Declare a local variable. */
  state     varchar(14) := 'California';
  tax_paid  real        := 0;
  result    RECORD;
BEGIN
  /* Query the results into a local variable. */
  SELECT sales_tax(100,8.25,state) INTO result;
 
  /* Print the taxes paid. */
  RAISE NOTICE 'Tax Paid [%]', result;
END;
$$;

It prints the following:

NOTICE:  Tax Paid [("(California,8.25)")]

You can actually return the individual members of the tuple by putting the function call inside the FROM clause, like:

SELECT * FROM sales_tax(100,8.25,'California');

It now returns the members of the tuple in separate columns:

   state    | tax  
------------+------
 California | 8.25
(1 row)

Alternatively, you can call it from inside an inline block, like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

DO
$$
DECLARE
  /* Declare a local variable. */
  state     varchar(14) := 'California';
  tax_paid  real        := 0;
BEGIN
  /* Query the results into a local variable. */
  SELECT * INTO state, tax_paid FROM sales_tax(100,8.25,state);
 
  /* Print the taxes paid. */
  RAISE NOTICE 'Tax Paid [%] [%]', state, tax_paid;
END;
$$;

It prints:

NOTICE:  Tax Paid [California] [8.25]

Now, let’s rewrite the function into a traditional function with all IN mode variables that returns a RECORD structure with additional values. Just one quick caveat (the big but), you can only assign values to dynamically constructed RECORD structures by using the SELECT-INTO or FOR statements. Below is the refactored sales_tax() function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

CREATE OR REPLACE
  FUNCTION sales_tax( IN  subtotal REAL
                    , IN  tax_rate REAL
                    , IN  state    VARCHAR(14))
  RETURNS RECORD AS
$$
DECLARE
  /* Declare a local variable. */
  tax        REAL;
  tax_record RECORD;
BEGIN
  /* Calculate the tax at 6%. */
  tax := subtotal * (tax_rate / 100);
 
  /* Assign state to record. */
  SELECT state, tax INTO tax_record;
 
  /* Return the tax. */
  RETURN tax_record;
END;
$$ LANGUAGE plpgsql;

It returns the same set of values as the early version with the four parameter example above but you only need three IN-only mode variables to get the result. Other than the parameter lists, the biggest change appears to be the assignment line, which is required in the explicit and traditional function that has only IN mode parameters:

16

  SELECT state, tax INTO tax_record;

Given you can return any RECORD structure you want, why use INOUT and OUT mode parameters? Don’t you loose clarity about what your stored function does? Or, at least, don’t you make understanding the program logic more difficult when you use INOUT and OUT mode variables? The only benefit appears to be when you shift your input variables from the SELECT clause to the INTO clause.

Hopefully, this shows folks how to use the OUT mode parameter; and how closely related it is to a classic function.

While PL/Python is an untrusted language inside PostgreSQL, I was installing it to test some of its features. First, we check to see if PL/Python is installed by attempting to create a PL/Python function:

CREATE FUNCTION pima(a integer, b integer)
RETURNS integer AS
$$
if a > b:
  return a
return b
$$ LANGUAGE plpython3u;

It likely should raise an error like this because PL/Python is an untrusted language. It’s untrusted because it runs with root privileges rather than a restricted user’s privilege. More or less, with PL/Python you can access the entire database. This makes PL/Python more of a threat than tool beyond experimentation in a test database.

ERROR:  language "plpython3u" does not exist
HINT:  Use CREATE EXTENSION to load the language into the database.

A quick query as the postgres user tells you whether or not PL/Python is installed:

SELECT * FROM pg_language;

It returns the following:

 lanname  | lanowner | lanispl | lanpltrusted | lanplcallfoid | laninline | lanvalidator | lanacl 
----------+----------+---------+--------------+---------------+-----------+--------------+--------
 internal |       10 | f       | f            |             0 |         0 |         2246 | 
 c        |       10 | f       | f            |             0 |         0 |         2247 | 
 sql      |       10 | f       | t            |             0 |         0 |         2248 | 
 plpgsql  |       10 | t       | t            |         14088 |     14089 |        14090 | 
(4 rows)

I attempted to add PL/Python with the following command:

CREATE EXTENSION plpython3u;

It raised the following error:

ERROR:  could not open extension control file "/usr/pgsql-11/share/extension/plpython3u.control": No such file or directory

The error basically appeared to occur because there’s a missing Python 3 package from what I could find on the web. I installed the missing postgresql-plpython3 package as the root superuser, which you also could install as a member of the sudoer list. The following shows how to install it as a sudoer member:

sudo yum install -y postgresql-plpython3

You should see something close to the following console output:

Last metadata expiration check: 0:43:53 ago on Fri 06 Nov 2020 10:42:28 AM MST.
Dependencies resolved.
============================================================================================================
 Package                           Architecture        Version                   Repository            Size
============================================================================================================
Installing:
 postgresql-plpython3              x86_64              11.7-2.fc30               updates               86 k
Installing dependencies:
 postgresql-server                 x86_64              11.7-2.fc30               updates              5.3 M
 
Transaction Summary
============================================================================================================
Install  2 Packages
 
Total download size: 5.3 M
Installed size: 23 M
Downloading Packages:
(1/2): postgresql-plpython3-11.7-2.fc30.x86_64.rpm                          218 kB/s |  86 kB     00:00    
(2/2): postgresql-server-11.7-2.fc30.x86_64.rpm                             3.0 MB/s | 5.3 MB     00:01    
------------------------------------------------------------------------------------------------------------
Total                                                                       2.1 MB/s | 5.3 MB     00:02     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                    1/1 
  Running scriptlet: postgresql-server-11.7-2.fc30.x86_64                                               1/2 
  Installing       : postgresql-server-11.7-2.fc30.x86_64                                               1/2 
warning: /var/lib/pgsql/.bash_profile created as /var/lib/pgsql/.bash_profile.rpmnew
 
  Running scriptlet: postgresql-server-11.7-2.fc30.x86_64                                               1/2 
  Installing       : postgresql-plpython3-11.7-2.fc30.x86_64                                            2/2 
  Running scriptlet: postgresql-plpython3-11.7-2.fc30.x86_64                                            2/2 
  Verifying        : postgresql-plpython3-11.7-2.fc30.x86_64                                            1/2 
  Verifying        : postgresql-server-11.7-2.fc30.x86_64                                               2/2 
 
Installed:
  postgresql-plpython3-11.7-2.fc30.x86_64                postgresql-server-11.7-2.fc30.x86_64               
 
Complete!

While I thought this might fix the problem, it didn’t and raised the following error:

ERROR:  could not open extension control file "/usr/pgsql-11/share/extension/plpython3u.control": No such file or directory

I found the plpython3u.control file on GitHub and put the following plpython3u.control file, after comparing it against the plpgsql.control file, into the /usr/pgsql-11/share/extension directory. At this point, I began wondering why it’s looking in the /usr/pgsql-11/share/extension directory instead of a /usr/plpython3u/share/extension directory (does not exist).

# plpython3u extension
comment = 'PL/Python3U untrusted procedural language'
default_version = '1.0'
module_pathname = '$libdir/plpython3'
relocatable = false
schema = pg_catalog
superuser = true

I retried creating the plpython3u extension:

CREATE EXTENSION plpython3u;

It raised the following error:

ERROR:  extension "plpython3u" has no installation script nor update path for version "1.0"

At this point, I could find no further help on the Internet. I did notice that there were these two *.sql files in the /usr/pgsql-11/share/extension directory:

• plpgsql–1.0.sql
• plpgsql–unpackaged–1.0.sql

I found this plpython3u--1.0.sql file on GitHub:

/* src/pl/plpython/plpython3u--1.0.sql */
 
CREATE FUNCTION plpython3_call_handler() RETURNS language_handler
  LANGUAGE c AS 'MODULE_PATHNAME';
 
CREATE FUNCTION plpython3_inline_handler(internal) RETURNS void
  STRICT LANGUAGE c AS 'MODULE_PATHNAME';
 
CREATE FUNCTION plpython3_validator(oid) RETURNS void
  STRICT LANGUAGE c AS 'MODULE_PATHNAME';
 
CREATE LANGUAGE plpython3u
  HANDLER plpython3_call_handler
  INLINE plpython3_inline_handler
  VALIDATOR plpython3_validator;
 
COMMENT ON LANGUAGE plpython3u IS 'PL/Python3U untrusted procedural language';

I retried creating the plpython3u extension, as a member of the sudoer list and got a new error:

ERROR:  permission denied to create extension "plpython3u"
HINT:  Must be superuser to create this extension.

I retried creating the plpython3u extension as the postgres user, who is the owning user:

CREATE EXTENSION plpython3u;

It raised the following error:

ERROR:  could not access file "$libdir/plpython3": No such file or directory

While I intend to finish this, that’s as far as I got. It appears from some of the things I’ve read I need to recompile or configure items that would destabilize what I have working at the moment. Finishing this will need to wait for me to build another test environment from scratch. If you catch this post and know the remaining steps, I invite you to add them in the comments.

It’s always interesting when I answer questions. This question was how to you perform a correlated UPDATE statement. My answer was assuming you’re updating the rating_id foreign key column in the rating table with the value from an item_rating column in the item table where on or another column value in the rating table match the item_rating column value in the item table match, you would write a correlated UPDATE statement like:

UPDATE item i
SET    i.rating_id = r.rating_id
WHERE  EXISTS
        (SELECT NULL
         FROM   rental r
         WHERE  r.rating = i.item_rating
         OR     r.description = i.item_rating);

This works in Oracle, MySQL, MariaDB, and MS SQL Server. I thought my work was done but I was wrong. The individual was trying to write a correlated UPDATE statement for PostgreSQL. The statement returned the following error:

ERROR:  syntax error at or near "WHERE"
LINE 3: WHERE  EXISTS
       ^

I did didn’t find an article to point the individual to after quick Google and DuckDuckGo searches. So, I thought I’d provide how you do it in PostgreSQL:

UPDATE item i
SET    rating_id = r.rating_id
FROM   rating r
WHERE  r.rating = i.item_rating
OR     r.description = i.item_rating;

In short, PostgreSQL doesn’t do what most expect because the UPDATE statement supports a FROM clause. Let’s give them the prize for different dialect. While I hope that I’m not a syntax bigot because I use MySQL more, I think the default syntax should always be supported in SQL dialects. After all, MySQL has a far superior named-notation INSERT statement alternative to the standard with the assignment method but MySQL also supports the standard syntax.

While I’ve shown you how to do it in PostgreSQL, what do you think? Should PostgreSQL be as responsible as MySQL is in maintaining standard SQL approaches?

Last September, the pgAdmin4 installation stymied me. I wasn’t sure what was I had done wrong in the installation but I was on a deadline to release my Fedora 30 Linux virtualization. That meant I had to move on and leave it for later. Today, I’m building the new image and returned to the task.

I installed pgadmin4 with the following command:

dnf -y install pgadmin4

The pgadmin4 configuration instructions can be found for several Linux versions at Josphat Mutai’s Computing for Geeks web page. On Fedora 30, you need to do the following:

• Install, start, and enable Apache as the httpd service unless you already have done that.
• Copy the /etc/httpd/conf.d/pgadmin4.conf.sample file to /etc/httpd/conf.d/pgadmin4.conf, which is a new file.
• Restart the httpd service to incorporate the pgadmin4 configuration file.

After that, my instructions vary from the original web page because they didn’t work. You actually need to create four directories as the sudo or root user:

• /var/lib/pgadmin4
• /var/lib/pgadmin4/sessions
• /var/lib/pgadmin4/storage
• /var/log/pgadmin4

You can make both directories with a single mkdir command, like:

mkdir -p /var/lib/pgadmin4 /var/lib/pgadmin4/sessions /var/lib/pgadmin4/storage /var/log/pgadmin4

As the root or sudo user, change the ownership of these two directories to the apache user with the following syntax:

chown -R apache:apache /var/lib/pgadmin4 /var/lib/pgadmin4/sessions /var/lib/pgadmin4/storage /var/log/pgadmin4

You add the following four statements to the config_distro.py file in the /usr/lib/python3.7/site-packages/pgadmin4-web directory as the root or sudo user:

LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'
SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
STORAGE_DIR = '/var/lib/pgadmin4/storage'

You need to setup the pgadmin user with the following python3 command:

python3 /usr/lib/python3.7/site-packages/pgadmin4-web/setup.py

Enter the following values, a real email address and a password twice:

NOTE: Configuring authentication for SERVER mode.
 
Enter the email address and password to use for the initial pgAdmin user account:
 
Email address: admin@example.com   
Password: your_password
Retype password: your_password
pgAdmin 4 - Application Initialisation
======================================

Before you move on, you should check ownership of the pgadmin4 directories in the /var/lib and /var/log directories and their files by long listing them as follows:

• Check the /var/lib directory:

  ll /var/lib/pgadmin4

  ll /var/lib/pgadmin4

  It should display:

  total 148 -rw-r--r--. 1 root root 1296 Apr 11 12:12 my-httpd.pp -rw-r--r--. 1 root root 332 Apr 11 12:12 my-httpd.te -rw-------. 1 apache apache 131072 Apr 11 12:16 pgadmin4.db drwx------. 2 apache apache 4096 Apr 11 12:15 sessions drwxr-xr-x. 2 apache apache 4096 Apr 10 17:33 storage

  total 148 -rw-r--r--. 1 root root 1296 Apr 11 12:12 my-httpd.pp -rw-r--r--. 1 root root 332 Apr 11 12:12 my-httpd.te -rw-------. 1 apache apache 131072 Apr 11 12:16 pgadmin4.db drwx------. 2 apache apache 4096 Apr 11 12:15 sessions drwxr-xr-x. 2 apache apache 4096 Apr 10 17:33 storage

• Check the /var/log directory:

  ll /var/log/pgadmin4

  ll /var/log/pgadmin4

  It should display:

  total 4 -rw-r--r--. 1 apache apache 1174 Apr 11 12:15 pgadmin4.log

  total 4 -rw-r--r--. 1 apache apache 1174 Apr 11 12:15 pgadmin4.log

Assuming you have an enabled firewall, you need to issue the following two commands as the root or sudo user:

firewall-cmd --permanent --add-service=http
firewall-cmd --reload

You invoke pgAdmin4 from within a browser window with the following URL for a stand alone workstation (for a workstation on a DNS network you would enter pgadmin.domain.domain_type in lieu of localhost):

pgadmin/localhost/pgadmin4

You most likely will encounter an Internal Server Error, the recommended fix is reputed to be:

ausearch -c 'httpd' --raw | audit2allow -M my-httpd
semodule -i my-httpd.pp

After completing the installation, you should be able to run pgadmin4, by typing in the following URL into a web browser:

http://localhost/pgadmin4

You should see the pgAmin4 web page if everything works. If it fails to launch, you should check the Apache error log. The error_log file is found in the /var/log/httpd directory. This is a type of error you may get if the ownership privileges aren’t assigned to the apache user and apache group.

[Sat Apr 11 12:06:25.433570 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136] mod_wsgi (pid=16086): Failed to exec Python script file '/usr/lib/python3.7/site-packages/pgadmin4-web/pgAdmin4.wsgi'.
[Sat Apr 11 12:06:25.433611 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136] mod_wsgi (pid=16086): Exception occurred processing WSGI script '/usr/lib/python3.7/site-packages/pgadmin4-web/pgAdmin4.wsgi'.
[Sat Apr 11 12:06:25.433720 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136] Traceback (most recent call last):
[Sat Apr 11 12:06:25.433741 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136]   File "/usr/lib/python3.7/site-packages/pgadmin4-web/pgAdmin4.wsgi", line 36, in <module>
[Sat Apr 11 12:06:25.433745 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136]     from pgAdmin4 import app as application
[Sat Apr 11 12:06:25.433749 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136]   File "/usr/lib/python3.7/site-packages/pgadmin4-web/pgAdmin4.py", line 109, in <module>
[Sat Apr 11 12:06:25.433752 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136]     app = create_app()
[Sat Apr 11 12:06:25.433756 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136]   File "/usr/lib/python3.7/site-packages/pgadmin4-web/pgadmin/__init__.py", line 345, in create_app
[Sat Apr 11 12:06:25.433759 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136]     os.chmod(config.SQLITE_PATH, 0o600)
[Sat Apr 11 12:06:25.433768 2020] [wsgi:error] [pid 16086:tid 139914869880576] [remote ::1:39136] PermissionError: [Errno 13] Permission denied: '/var/lib/pgadmin4/pgadmin4.db'
[Sat Apr 11 12:06:28.234643 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138] mod_wsgi (pid=16086): Failed to exec Python script file '/usr/lib/python3.7/site-packages/pgadmin4-web/pgAdmin4.wsgi'.
[Sat Apr 11 12:06:28.234694 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138] mod_wsgi (pid=16086): Exception occurred processing WSGI script '/usr/lib/python3.7/site-packages/pgadmin4-web/pgAdmin4.wsgi'.
[Sat Apr 11 12:06:28.234781 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138] Traceback (most recent call last):
[Sat Apr 11 12:06:28.234816 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138]   File "/usr/lib/python3.7/site-packages/pgadmin4-web/pgAdmin4.wsgi", line 36, in <module>
[Sat Apr 11 12:06:28.234820 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138]     from pgAdmin4 import app as application
[Sat Apr 11 12:06:28.234824 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138]   File "/usr/lib/python3.7/site-packages/pgadmin4-web/pgAdmin4.py", line 109, in <module>
[Sat Apr 11 12:06:28.234826 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138]     app = create_app()
[Sat Apr 11 12:06:28.234830 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138]   File "/usr/lib/python3.7/site-packages/pgadmin4-web/pgadmin/__init__.py", line 345, in create_app
[Sat Apr 11 12:06:28.234832 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138]     os.chmod(config.SQLITE_PATH, 0o600)
[Sat Apr 11 12:06:28.234841 2020] [wsgi:error] [pid 16086:tid 139914945414912] [remote ::1:39138] PermissionError: [Errno 13] Permission denied: '/var/lib/pgadmin4/pgadmin4.db'

As always, I hope my notes are helpful to those who want to work with pgadmin4 and the PostgreSQL database.