SQL maintenance tips

Installing psycopg2

This is a Python driver for PostgreSQL. It's not necessary for the dev environment which uses Python's built-in Sqlite3 library, but would be required for the production environment. On Linux use sudo apt install libpq-dev build-essentials to install the development libraries and headers for PostgreSQL and the GCC compliers used to build the binding. See PSQL client & PostgreSQL sections below.

PSQL client

To interact with the database use the psql client. On Linux use sudo apt install postgresql-client and then use psql <service uri> to connect. Get the SERVICE_URI from the Aiven console overview. To quit enter \q, to list databases enter \l, to list tables use \d, to list roles use \du or \dg, and use \? for help. You will need psql to interact with both the Aiven PostgreSQL database and the local travis_ci_test database. See Database setup below.

connect to Django app databaese

To connect to the local database on Ubuntu either use the builtin django commands

python manage.py dbshell --settings=pvfree.settings.travis

or use

sudo -u postgres psql travis_ci_test

You should see travis_ci_test=#. If you leave off travis_ci_test then you will not be able to access the tables. Note: you will not be able to log into psql as the database user travis because in the default PostgreSQL configuration file, only the built-in postgres user can make peer connections. Other database users like travis can only connect using tcp/ip, which is how Django connects. There is no reason to edit the /etc/postgresql/16/main/pg_hba.conf file. Leave it alone! There is also a tool called pg_conftool that will show or allow edits to other PostgreSQL configs. EG: pg_conftool show all displays PostgreSQL cluster configurations. Just leave it alone!

PostgreSQL

To install on Ubuntu use sudo apt install postgresql postgresql-contrib libpq-dev and if not using conda, then also install Python headers python3-dev to build any extensions, eg: psycopg2, the Python binding.

Database setup

Before you can use the database with Django there are a few hoops to jump through.

1. When you install PostgreSQL on Ubuntu it creates a new system user called postgres who is the main db admin. (Before you can use postgres you may need to create a system password for the postgres user using the Linux command sudo passwd postgres which will prompt you for a password. NOTE: this may not be strictly true. EG: I was able to use the postgres user without setting a system password.)

2. Now you can start the database using sudo service postgresql start. You can also check the status with sudo service postgresql status or stop the db with sudo service postgresql stop.

3. These next steps are super important! You need to create a new database for your Django app and a new db user role with access to the new database. First start psql the postgres command line interface with sudo -u postgres psql. You should see the prompt postgres-#. You can use \q to exit. Also \l to list databases and \du to list the database user roles which are different from Ubuntu system users. As mentioned earlier, you won't be able to list the database tables.

4. In psql, create a new database for your Django app with CREATE DATABASE <django-app-dbname>; and don't forget the semicolon or psql will ignore your input and combine it with the next line.

5. Now create a new user role with CREATE ROLE <django-app-dbuser> WITH PASSWORD '<django-app-dbpswd>'; and don't forget the single quotes or the semicolon.

6. You need to add the ability to login and create database tables to the new django user. Enter the following lines:

ALTER ROLE <django-app-dbuser> WITH LOGIN;
ALTER ROLE <django-app-dbuser> WITH SUPERUSER;
ALTER ROLE <django-app-dbuser> WITH CREATEDB;

7. Following the Django PostgreSQL notes alter the role to use UTF8 encoding, set timezone to UTC, and set the default transaction isolation level to "read committed" with these commands:

ALTER ROLE <django-app-dbuser> SET client_encoding TO 'UTF8';
ALTER ROLE <django-app-dbuser> SET default_transaction_isolation TO 'read committed';
ALTER ROLE <django-app-dbuser> SET timezone TO 'UTC';

8. Finally grant the new user access to the new database with GRANT ALL PRIVILEGES ON DATABASE <django-app-dbname> TO <django-app-dbuser>;

9. If you want to edit the database with psql use sudo -u postgres psql <django-app-dbname> and you should see the prompt <django-app-dbname>=#. You can also list the tables with \d.

10. Quit psql with \q and restart the database server.

SQL commands

In psql use \h to get a list of SQL commands

delete all table records and restart index

A quick way to delete all rows and restart the index, without having to vacuum, is to use TRUNCATE:

TRUNCATE <table name> RESTART IDENTITY;

You might want to restart the index so that the first record is #1, versus some random number. Although this might break some folks code if they are querying the db by index instead of parameter "Name". Oh well.

reset a sequence

After failed attempts to write to the database or after deleting records you may wish to reset the index. The command below resets the index for the CEC Modules table in parameters to 21378. This SO answer has many examples.

ALTER SEQUENCE parameters_cec_module_id_seq RESTART WITH 21378;

FYI: According to the PostgreSQL docs ALTER SEQUENCE <sequence> RESTART WITH <nextval>; is similar to setval(); except that it blocks concurrent transactions, while setval(); allows them. See Sequence Manipulation Functions for more on setval();.

update the index record values

To update the id of records after a deletion or a failed POST leaves an empty index, use UPDATE:

defaultdb=> UPDATE parameters_cec_module SET id = 87405 WHERE id = 87406;
# UPDATE 1
defaultdb=> UPDATE parameters_cec_module SET id = 87406 WHERE id = 87407;
UPDATE 1
defaultdb=> UPDATE parameters_cec_module SET id = 87407 WHERE id = 87408;
UPDATE 1
defaultdb=> SELECT ("id", "Name", "Date", "Version") FROM parameters_cec_module WHERE id > 87400;
                               row
-----------------------------------------------------------------
 (87401,"Andalay Solar ST165-1",2019-12-19,9)
 (87402,"APOS Energy AP 200M",2019-12-19,9)
 (87403,"Jiangsu Shunda PV-Tech SDM-170/(185)-72M",2019-12-19,9)
 (87404,"Jiangsu Wanfeng PV WF260P-02A",2019-12-19,9)
 (87405,"Jinko Solar Co._ Ltd JKM295M-60HB-V",2019-12-19,9)
 (87406,"Topsun TS-S390SA1",2019-12-19,9)
 (87407,"Topsun TS-S399VA1",2019-12-19,9)
(7 rows)

defaultdb=> ALTER SEQUENCE parameters_cec_module_id_seq RESTART WITH 87408;
ALTER SEQUENCE

Don't forget to reset the index to restart at the new next value!

FYI: Use \d <table> get the schema of a table.

defaultdb=> \d parameters_cec_module
                                        Table "public.parameters_cec_module"
     Column     |          Type          | Collation | Nullable |                      Default
----------------+------------------------+-----------+----------+---------------------------------------------------
 id             | integer                |           | not null | nextval('parameters_cec_module_id_seq'::regclass)
 Name           | character varying(100) |           | not null |
 BIPV           | boolean                |           | not null |
 Date           | date                   |           | not null |
 T_NOCT         | double precision       |           | not null |
 A_c            | double precision       |           | not null |
 N_s            | integer                |           | not null |
 I_sc_ref       | double precision       |           | not null |
 V_oc_ref       | double precision       |           | not null |
 I_mp_ref       | double precision       |           | not null |
 V_mp_ref       | double precision       |           | not null |
 alpha_sc       | double precision       |           | not null |
 beta_oc        | double precision       |           | not null |
 a_ref          | double precision       |           | not null |
 I_L_ref        | double precision       |           | not null |
 I_o_ref        | double precision       |           | not null |
 R_s            | double precision       |           | not null |
 R_sh_ref       | double precision       |           | not null |
 Adjust         | double precision       |           | not null |
 gamma_r        | double precision       |           | not null |
 Version        | integer                |           | not null |
 PTC            | double precision       |           | not null |
 Technology     | integer                |           | not null |
 Bifacial       | boolean                |           | not null |
 Length         | double precision       |           |          |
 STC            | double precision       |           | not null |
 Width          | double precision       |           |          |
 created_by_id  | integer                |           | not null |
 created_on     | date                   |           | not null |
 modified_by_id | integer                |           | not null |
 modified_on    | date                   |           | not null |
 Manufacturer   | character varying(100) |           | not null |
Indexes:
    "parameters_cec_module_pkey" PRIMARY KEY, btree (id)
    "parameters_cec_module_Name_Date_Version_30e8b612_uniq" UNIQUE CONSTRAINT, btree ("Name", "Date", "Version")
    "parameters_cec_module_created_by_id_85cfa2c2" btree (created_by_id)
    "parameters_cec_module_modified_by_id_211ad02a" btree (modified_by_id)
Foreign-key constraints:
    "parameters_cec_module_created_by_id_85cfa2c2_fk_auth_user_id" FOREIGN KEY (created_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "parameters_cec_module_modified_by_id_211ad02a_fk_auth_user_id" FOREIGN KEY (modified_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED

Aiven console

PV Free uses Aiven for PostgreSQL. You must log in monthly to keep your account active or the database will be turned off, and you will need to manually restart it by visiting the console. Aiven will send an email before it deactivates the database.