Postgres Debian/Ubuntu Packaging

Part 1

What follows is a simple explanation of how the Postgres Debian/Ubuntu packaging
system works in order to answer recurring questions that are posted on various
forums. This post will focus on the install and initial setup. The next post
will deal with the commands that are added by the the packaging.

The Debian/Ubuntu packaging is designed to allow for multiple concurrent
instances of Postgres to be run on a single machine. Exactly what you can run is
going to depend on the repositories from which you fetch the packages. The
Debian and Ubuntu native repos will be ‘pinned’ to one version of Postgres. So
for the Ubuntu distribution being used here, 22.04, the version is Postgres 14.
For the previous LTS release, 20.04, it would be version 12. In the Debian case
stable(bullseye) is at version 13 and unstable(sid) is at 15. In either of
these cases the packaging will allow the running multiple cluster/instances of
the distribution version. NOTE: Cluster or instance will be used interchangeably
to refer to the same concept, namely the data directory produced by initdb. For
this article a different repository will be used, the PGDG(PostgreSQL Global Development Group) version. Also referred to as the Postgres community repos. The reason is that these repos can install multiple versions of Postgres on a given distribution. The versions available will be those that currently have community support. Since there is a release a year and approximately 5 year support for each release this means there will be generally 5 versions to choose from. At the time of writing (06/2023) that is 11, 12, 13, 14 and 15. For each major version the repo will have the latest minor version. For releases other then the latest supported production releases visit the links under Archive Repo and Development/Beta Repos below. In order to run multiple instances the packaging system utilizes wrapper code that runs the Postgres utilities on a cluster specific basis. How that is done is explained in the following sections. There are snippets of information available on the Web, but nothing I could find that ties it all together. Hence, this attempt.

Archive repo

Announcing archive repo.
Archive repo

This repo will have older not supported releases. This includes releases no longer
supported by community as well a prior point releases of currently supported
releases.

Development/Beta Repos

Beta version

Development snapshots

Install

In order to explore the packaging it needs to be installed. To install from the PGDG
repos use the following commands per instructions from here Install instructions:

1) Get sources list and repo key.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Above deprecated in Ubuntu 22.04, use below instead.
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/pg_repo.asc

2) Install Postgresql 15(latest production version at time of writing).

sudo apt-get update

sudo apt-get -y install postgresql-15


Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5 
libtypes-serialiser-perl postgresql-15 postgresql-client-15 
postgresql-client-common postgresql-common ssl-cert
Suggested packages:
postgresql-doc postgresql-doc-15
The following NEW packages will be installed:
libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5 
libtypes-serialiser-perl postgresql postgresql-15 postgresql-client-15 
postgresql-client-common postgresql-common ssl-cert
0 upgraded, 12 newly installed, 0 to remove and 0 not upgraded.
Need to get 43.4 MB of archives.

...

3) The above will automatically create an active Postgres cluster that can seen with:

pg_lsclusters 
Ver Cluster Port Status Owner    Data directory              Log file
14  main    5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
15  main    5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log

Note the port number 5433 assigned to the new cluster as a pre-existing cluster already has the default port of 5432. Cluster designation follows pattern PostgreSQL version number and cluster name. The default name is main. More on this in commands section.

4) File locations:

a)  Binaries

    ls -al /usr/lib/postgresql/15/bin/
    total 12920
    drwxr-xr-x 2 root root    4096 Jun 27 09:13 .
    drwxr-xr-x 4 root root    4096 Jun 27 09:13 ..
    -rwxr-xr-x 1 root root   56048 May  9 10:05 clusterdb
    -rwxr-xr-x 1 root root   60376 May  9 10:05 createdb
    -rwxr-xr-x 1 root root   56400 May  9 10:05 createuser
    -rwxr-xr-x 1 root root   51888 May  9 10:05 dropdb
    -rwxr-xr-x 1 root root   51824 May  9 10:05 dropuser
    ...


b)  Data

    sudo ls -al /var/lib/postgresql/15/main/

    total 92
    drwx------ 19 postgres postgres 4096 Jun 27 09:13 .
    drwxr-xr-x  3 postgres postgres 4096 Jun 27 09:13 ..
    drwx------  5 postgres postgres 4096 Jun 27 09:13 base
    drwx------  2 postgres postgres 4096 Jun 27 09:14 global
    drwx------  2 postgres postgres 4096 Jun 27 09:13 pg_commit_ts
    drwx------  2 postgres postgres 4096 Jun 27 09:13 pg_dynshmem
    drwx------  4 postgres postgres 4096 Jun 27 09:13 pg_logical

    ...

c)  Log 

    ls -al /var/log/postgresql/postgresql-15-main.log 
    -rw-r----- 1 postgres adm 660 Jun 27 09:13 /var/log/postgresql/postgresql-15-main.log


d)  Conf

    ls -al /etc/postgresql/15/main/
    total 68
    drwxr-xr-x 3 postgres postgres  4096 Jun 27 09:13 .
    drwxr-xr-x 3 postgres postgres  4096 Jun 27 09:13 ..
    drwxr-xr-x 2 postgres postgres  4096 Jun 27 09:13 conf.d
    -rw-r--r-- 1 postgres postgres   315 Jun 27 09:13 environment
    -rw-r--r-- 1 postgres postgres   143 Jun 27 09:13 pg_ctl.conf
    -rw-r----- 1 postgres postgres  5002 Jun 27 09:13 pg_hba.conf
    -rw-r----- 1 postgres postgres  1636 Jun 27 09:13 pg_ident.conf
    -rw-r--r-- 1 postgres postgres 29735 Jun 27 09:13 postgresql.conf
    -rw-r--r-- 1 postgres postgres   317 Jun 27 09:13 start.conf

Connecting

Initial connection

At this point the only user that is present in the database cluster is postgres, as setup by the package install. To connect as that user there needs to be a side trip to the pg_hba.conf file where hba stands for Host Based Authentication. It is the configuration file that controls who can access a database from what host with what authentication method. More information can be found at pg_hba.conf.

The pg_hba.conf file is set up by the package install in the location below:

sudo vi /etc/postgresql/15/main/pg_hba.conf
…

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

…

Note the lines:

Database administrative login by Unix domain socket

local all postgres peer

This sets up the postgres user to log in via the local socket using peer authentication.
Where peer authentication uses the logged in OS user name as the database user
name to login in as. The package install creates the OS user postgres as part of the process. This OS user does not have a home directory nor can you directly log in as that user. To operate as that OS user you indirectly log in using sudo. This means the initial log in can be done as:

aklaver@arkansas:~$ sudo -i -u postgres psql -p 5433
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
Type "help" for help.

NOTE: I already have a Postgres instance running on port 5432 so the new 15 instance
was assigned port 5433 on install.

The above uses the OS postgres user to login to the cluster using the psql
client. psql without any further arguments defaults to using the OS user name as
the database name to connect to and the database user name to connect as. Also
since no host was specified the connection will be made to the local socket.
Therefore the pg_hba.conf line shown above will be used as it is the first one
that matches the connection parameters.

The command below will show you the current databases in the cluster.

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype |
-----------+----------+----------+-------------+-------------+ ...
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8  ...
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8  ...
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8  ...
(3 rows)

These where created by the initdb done by the install. More information on the template databases can be found here Template database. For now it is sufficient to know template0 is read only and template1 is the default template used to create new databases. The postgres database is there to operate as a default connection database to do further operations on the cluster. These databases should not be used as end user databases. For that new application specific databases should be created.

It is a good idea to set up the postgres user to work with the lines below from pg_hba.conf.

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

To that end run the command below with the strong password of your choice:

postgres=# alter role postgres with password 'pg_test_SimplePWD';
ALTER ROLE

This then allows for logging from a OS user account without going through sudo.

psql -d postgres -h localhost -U postgres -p 5433
Password for user postgres:
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=#

In the above the -d postgres specifies connecting to the postgres database, -h
localhost makes the connection be a host one and -U postgres means connect as
the database user postgres.

It is also a good idea to create a non superuser database user you can also log in as. Example shown below.

postgres=# create role aklaver with login createdb createrole password 'TEST_pwd_Cat';
CREATE ROLE
psql -d postgres -h localhost -U aklaver -p 5433
Password for user aklaver:
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=>

It would also not hurt to go over the server configuration file:

sudo vi /etc/postgresql/15/main/postgresql.conf

The above holds the configuration settings for the operation of the cluster. It is out of the scope of this post to go through what they all mean. That information can be found here:

Server configuration

There is another file set up by the Postgres package install and deals with how the
server is started. It can be viewed here:

sudo vi /etc/postgresql/15/main/start.conf

# Automatic startup configuration
#   auto: automatically start the cluster
#   manual: manual startup with pg_ctlcluster/postgresql@.service only
#   disabled: refuse to start cluster
# See pg_createcluster(1) for details. When running from systemd,
# invoke 'systemctl daemon-reload' after editing this file.

auto

The commented(#) section explains what settings are available and what they do.
By default the server startup will be set to auto which means it will start the
server on boot/reboot.

The below deals with some basic commands to manage the server via the command
line. There will be more on this in the next post.

Managing server state.

Using pg-common(installed by package) commands

aklaver@arkansas:~$ sudo pg_ctlcluster stop 15 main

aklaver@arkansas:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
15 main 5433 down postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log

aklaver@arkansas:~$ sudo pg_ctlcluster 15 main start

aklaver@arkansas:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
15 main 5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log

NOTE: The action(stop/start) can be before or after the cluster designation.
Other actions are restart, reload, status and promote. More on this in commands
section.

Using systemd.

aklaver@arkansas:~$ sudo systemctl stop postgresql@15-main.service

aklaver@arkansas:~$ sudo systemctl status postgresql@15-main.service
postgresql@15-main.service - PostgreSQL Cluster 15-main
Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime; vendor preset: enabled)
Active: inactive (dead) since Tue 2023-06-27 09:36:51 PDT; 1min 10s ago
Process: 3669737 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 15-main start (code=exited, status=0/SUCCESS)
Process: 3669769 ExecStop=/usr/bin/pg_ctlcluster --skip-systemctl-redirect -m fast 15-main stop (code=exited, status=0/SUCCESS)
Main PID: 3669742 (code=exited, status=0/SUCCESS)
CPU: 215ms

Jun 27 09:35:56 arkansas systemd[1]: Starting PostgreSQL Cluster 15-main…
Jun 27 09:35:58 arkansas systemd[1]: Started PostgreSQL Cluster 15-main.
Jun 27 09:36:51 arkansas systemd[1]: Stopping PostgreSQL Cluster 15-main…
Jun 27 09:36:51 arkansas systemd[1]: postgresql@15-main.service: Deactivated successfully.
Jun 27 09:36:51 arkansas systemd[1]: Stopped PostgreSQL Cluster 15-main.

aklaver@arkansas:~$ sudo systemctl start postgresql@15-main.service

aklaver@arkansas:~$ sudo systemctl status postgresql@15-main.service
postgresql@15-main.service - PostgreSQL Cluster 15-main
Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime; vendor preset: enabled)
Active: active (running) since Tue 2023-06-27 09:38:42 PDT; 5s ago
Process: 3669790 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 15-main start (code=exited, status=0/SUCCESS)
Main PID: 3669795 (postgres)
Tasks: 6 (limit: 4676)
Memory: 18.6M
CPU: 139ms
CGroup: /system.slice/system-postgresql.slice/postgresql@15-main.service
├─3669795 /usr/lib/postgresql/15/bin/postgres -D /var/lib/postgresql/15/main -c config_file=/etc/postgresql/15/main/postgresql.conf
├─3669796 "postgres: 15/main: checkpointer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" """"" "" "" "" "" ">
├─3669797 "postgres: 15/main: background writer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "">
├─3669799 "postgres: 15/main: walwriter " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ">
├─3669800 "postgres: 15/main: autovacuum launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" >
└─3669801 "postgres: 15/main: logical replication launcher " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" >

Jun 27 09:38:39 arkansas systemd[1]: Starting PostgreSQL Cluster 15-main…
Jun 27 09:38:42 arkansas systemd[1]: Started PostgreSQL Cluster 15-main.

In either case pg_lsclusters will also show you the correct state.

aklaver@arkansas:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log
15 main 5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log

Using iCalendar RRULE in Postgres

RRULE is an iCalendar specification for computing recurring calendar events. Something like, on first Tuesday of each month pay this bill. This is done as a rule that generates the occurrences of the event as needed. This solves the issue of a continually recurring event having to be physically stored as set of occurrences. The link above has some examples and there is this site RRULE generator where you can explore the options. This post will be a light introduction on how to store to, retrieve from a Postgres database the rules using Python and Javascript. Then use that information to populate a Javascript calendar in a Flask application. For Python the rrule module of the dateutil program will be used. In Javascript the rrule.js program which is a port of dateutil.rrule.

Setting up Python dateutil:

from dateutil.parser import parse
from dateutil.rrule import *

all for rrule is

["rrule", "rruleset", "rrulestr",

"YEARLY", "MONTHLY", "WEEKLY", "DAILY",

"HOURLY", "MINUTELY", "SECONDLY",

"MO", "TU", "WE", "TH", "FR", "SA", "SU"]

Examples.

Note the use of count. This is good habit to get into until you are
sure of what the rule is going to produce. Unless you want to produce an
infinite list of occurrences and bring your computer to its knees:). Don’t ask
me how I know.

Start at dstart and reoccur every month on same day of month for five occurences.

list(rrule(freq=MONTHLY, count=5, dtstart=parse("06/22/23")))

[datetime.datetime(2023, 6, 22, 0, 0),
 datetime.datetime(2023, 7, 22, 0, 0),
 datetime.datetime(2023, 8, 22, 0, 0),
 datetime.datetime(2023, 9, 22, 0, 0),
 datetime.datetime(2023, 10, 22, 0, 0)]

Same as above but specify occurrences to be on 31st of month. This skips month with < 31 days as the RRULE specification requires incorrect dates and/or times to be skipped not ’rounded’ down.

list(rrule(freq=MONTHLY, bymonthday=31, count=5, dtstart=parse("06/22/23")))

[datetime.datetime(2023, 7, 31, 0, 0),
 datetime.datetime(2023, 8, 31, 0, 0),
 datetime.datetime(2023, 10, 31, 0, 0),
 datetime.datetime(2023, 12, 31, 0, 0),
 datetime.datetime(2024, 1, 31, 0, 0)]

bymonthday supports negative indexing, so to get last day of month regardless of its day number use -1.

list(rrule(freq=MONTHLY, bymonthday=-1, count=5, dtstart=parse("06/22/23")))

[datetime.datetime(2023, 6, 30, 0, 0),
 datetime.datetime(2023, 7, 31, 0, 0),
 datetime.datetime(2023, 8, 31, 0, 0),
 datetime.datetime(2023, 9, 30, 0, 0),
 datetime.datetime(2023, 10, 31, 0, 0)]

To get a better idea of what is possible I recommend looking at the examples
here rrule examples

Incorporating RRULE into Postgres.

Create database table to hold rules and associated information.

CREATE TABLE public.rrule_example(
    task_id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    task_title varchar NOT NULL,
    task_desc varchar NOT NULL,
    task_rrule varchar NOT NULL,
    start_date date NOT NULL,
    until_date date
);

Underlying RRULE is a string format that is fully explained in the RFC. The quick and dirty way to derive that in dateutil.rrule is to use the str() method on a rrule.

r = rrule(freq=WEEKLY, interval=2,   dtstart=parse("06/22/2023"))

r.__str__()
'DTSTART:20230622T000000\nRRULE:FREQ=WEEKLY;INTERVAL=2'


Insert string form of rrule into database.

INSERT INTO public.rrule_example OVERRIDING SYSTEM VALUE VALUES (1, 'Every two weeks', 'Task occurrs every two weeks on Thursday', E'DTSTART:20230622T000000\nRRULE:FREQ=WEEKLY;INTERVAL=2', '2023-06-22', NULL);

select * from rrule_example;
-[ RECORD 1 ]----------------------------------------
task_id    | 1
task_title | Every two weeks
task_desc  | Task occurrs every two weeks on Thursday
task_rrule | DTSTART:20230622T000000                 +
           | RRULE:FREQ=WEEKLY;INTERVAL=2
start_date | 06/22/2023
until_date | NULL

Create function to find next rule occurrence using plpython3u procedural language.

CREATE OR REPLACE FUNCTION public.rrule_next_occurrence(t_rrule character
varying, start_dt timestamp with time zone)
RETURNS timestamp with time zone
LANGUAGE plpython3u
SECURITY DEFINER
AS $function$
from datetime import datetime
from dateutil.parser import parse
from dateutil.rrule import rrulestr

rule = rrulestr(t_rrule, ignoretz=True)
next_occ = rule.after(parse(start_dt, ignoretz=True), inc=True)

return next_occ

$function$
;

The function uses dateutil.rrulestr to parse the string version of the rrule. Then the after() method to find first occurrence of rule after specified date.

select rrule_next_occurrence(task_rrule, '2023-06-21') from rrule_example where task_id =1;

rrule_next_occurrence  
-------------------------
 06/22/2023 00:00:00 PDT

Create function to find previous rule occurrence.

CREATE OR REPLACE FUNCTION public.rrule_prior_occurrence(t_rrule character
varying, start_dt timestamp with time zone)
RETURNS timestamp with time zone
LANGUAGE plpython3u
SECURITY DEFINER
AS $function$
from datetime import datetime
from dateutil.parser import parse
from dateutil.rrule import rrulestr

rule = rrulestr(t_rrule, ignoretz=True)
prior_occ = rule.before(parse(start_dt, ignoretz=True), inc=True)

return prior_occ

$function$
;

Use rrulestr to parse string rrule. Then before() to find last occurrence of
rule before specified date.

select rrule_prior_occurrence(task_rrule, '2023-06-23') from rrule_example where task_id =1;

 rrule_prior_occurrence  
-------------------------
 06/22/2023 00:00:00 PDT

Using this information in a Web page.

Using Flask set up FullCalendar(https://fullcalendar.io/) calendar to display recurring
events using rrule.js(https://github.com/jakubroztocil/rrule).

Need to include rrule-tz.js first then the FullCalendar rrule plugin.

<!--rrule.js with timezone support-->
<script type=""text/javascript" src="{{ url_for('static', 
filename='js/external/rrule/rrule-tz.js') }}"></script>
<script type=""text/javascript" src="{{ url_for('static', 
filename='js/external/full_calendar/main.js') }}"></script>
<!--FullCalendar rrule plugin-->
<script type=""text/javascript" src="{{ url_for('static', 
filename='js/external/rrule/main.global.js') }}"></script>

In calendar constructor eventSources is where the calendar gets the information
to fill in the calendar.

<script>

      document.addEventListener('DOMContentLoaded', function() {
        var calendarEl = document.getElementById('calendar');
        var calendar = new FullCalendar.Calendar(calendarEl, {
            timeZone: "US/Pacific",
            slotMinTime: "07:00",
            slotMaxTime: "19:00",
            slotDuration: "00:15:00",
            forceEventDuration: true,
            defaultTimedEventDuration: "00:15",
            initialView: "dayGridMonth",
            headerToolbar: {
                left: "prev,next today, prevYear,nextYear",
                center: "title",
                right: "dayGridMonth,timeGridWeek,timeGridDay"
                },
            stickyHeaderDates: true,
            eventSources: [
                {
                   url: "/task_calendar_data",
                },
                {events: 
                    [{
                        title: 'Weekly Mon/Fri',
                        rrule: {
                            freq: 'weekly',
                            interval: 1,
                            byweekday: [ 'mo', 'fr' ],
                            dtstart: '2023-06-01T10:30:00', 
                            until: '2023-10-31'
                        }
                    }],
                    id: "fixed_event"

                
                }
            ]
        });
        calendar.render();
      });

    </script>

In this case there are two sources url which fetches from a view in Flask and
events which is a fixed event that uses the rrule.js syntax to build an event.

The view is:

@calendar_bp.route("/task_calendar_data")
def taskCalendarData():
    today_dt = date.today()
    start_dt = request.args.get("start", today_dt.strftime("%m/%d/%Y"))
    end_dt = request.args.get("end",
                              (today_dt
                               + timedelta(days=1)).strftime("%m/%d/%Y"))
    # The connection(con) returned from get_db() uses cursor_factory=RealDictCursor
    # so results are returned as dictionaries.
    con = db.get_db()
    cur = con.cursor()
    cur.execute("select * from rrule_example")
    rs = cur.fetchall()
    tasks = []
    if rs:
        for task in rs:
            tasks.append({"id": task["task_id"], "title": task["task_title"], 
                          "rrule": task["task_rrule"], "allDay": True})
    response = current_app.response_class(
                response=json.dumps(tasks),
                mimetype='application/json'
            )
    return response

allDay is set True to pin the task to 00:00.

Insert a rrule that shows an occurrence on last day of month.

INSERT INTO
    public.rrule_example OVERRIDING SYSTEM VALUE
VALUES 
(2, 'Last day of month', 'Task occurrs last day of each month',
E'DTSTART:20230622T000000\nRRULE:FREQ=MONTHLY;BYMONTHDAY=-1', 
'2023-06-22', NULL);

The calendar display for the rrules inserted into the database and from the eventSources in the calendar constructor. The current month and October 2023 when the rrule in the calendar constructor ends.