# Upgrading PostgreSQL and Setting up [pg_cron extension](https://github.com/citusdata/pg_cron/tree/main)

## PostgreSQL Upgrade Instructions

### Step 1: Prepare packages for 16

#### Create the file repository configuration:

sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt jammy-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

#### Import the repository signing key:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/postgresql.asc

#### Update the package lists:

sudo apt-get update

#### Install PostgreSQL 16:

sudo apt-get -y install postgresql-16

### Step 2: Checking Your Database Clusters

sudo pg_lsclusters

### Step 3: Preparing for the Upgrade

sudo pg_dropcluster 16 main --stop

### Step 4: Initiating the Upgrade Process

sudo pg_upgradecluster 14 main

sudo pg_dropcluster 14 main

### Step 5: Final Touch — Removing the Old Package

sudo apt purge postgresql-14 postgresql-client-14

### Step 6: Verification — Ensuring a Successful Upgrade

sudo pg_lsclusters

---

## pg_cron Extension Setup

Make sure the PostgreSQL version is 16 if not [follow these steps](https://hackmd.io/mPs0a-X-QLWzFhdUIqGofA?both#PostgreSQL-Upgrade-Instructions)

After verifying PostgreSQL version 16, let's start with the installation of `postgresql-16-cron`.

### Installing pg_cron

Install on Debian, and Ubuntu with PostgreSQL 16 using [apt.postgresql.org](https://wiki.postgresql.org/wiki/Apt):

sudo apt-get -y install postgresql-16-cron

### Setting up pg_cron

To start the pg_cron background worker when PostgreSQL starts, you need to add pg_cron to `shared_preload_libraries` in `postgresql.conf`.

#### Locate your `postgresql.conf`:
Log into PostgreSQL server:

sudo -u postgres psql

Run this command:

show config_file;

In my case, it was in `/etc/postgresql/16/main/postgresql.conf`.

#### Open it in Vim:

sudo vim /etc/postgresql/16/main/postgresql.conf

#### Search for `shared_preload_libraries` and add:

shared_preload_libraries = 'pg_cron'

#### Add cron.database_name to `postgresql.conf`:

cron.database_name = '{Database-Name}'

- Note: We can add multiple databases

Example: In my local PostgreSQL Server, I wanted to add the pg_cron extension to the `bookstore` and `postgres` databases.


After editing, restart PostgreSQL. Create the pg_cron functions and metadata tables using:

CREATE EXTENSION pg_cron;

### Start pg_cron Jobs

#### View Active jobs:

SELECT * FROM cron.job;

#### Create a Cron Job:
Example: [With 1-hour Cron Job](https://git.apps.hexmos.com/hexmos/backend/karma_models/-/blob/main/ic/pg_cron/Cron_jobs.sql)

SELECT cron.schedule(
  'ranked_view_one_hour_refresh',
  '0 * * * *',
  $CRON$ REFRESH MATERIALIZED VIEW ranked_view; $CRON$
);
--SELECT cron.schedule(
--  '{Cron_Job_Name}',
--  '0 * * * *',
--  $CRON$ {SQL_Cammand} $CRON$
-- );

#### Ensure the nodename is empty:

UPDATE cron.job SET nodename = '';

#### View Cron Logs:

--For 10 
SELECT * FROM cron.job_run_details ORDER BY start_time DESC LIMIT 10;
-- for 100
--SELECT * FROM cron.job_run_details ORDER BY start_time DESC LIMIT 100;

#### Unschedule or Remove a Cron Job:

SELECT cron.unschedule(2);
-- SELECT cron.unschedule(jobid);

---

## [Materialized Views](https://git.apps.hexmos.com/hexmos/backend/karma_models/-/blob/main/ic/meterialise%20view/ranked_view.sql)

### Creation:

--CREATE MATERIALIZED VIEW {MATERIALIZED_Table_Name}AS {Table_Creaion_Cammand}
CREATE MATERIALIZED VIEW ranked_view
AS
SELECT
    ici.reviewed_user_id,
    SUM(ici.points) AS hkpoints,
    ROW_NUMBER() OVER(PARTITION BY u.organization ORDER BY SUM(ici.points) DESC) AS org_rank,
    COUNT(*) OVER(PARTITION BY u.organization) AS total_org_member,
    ROW_NUMBER() OVER(PARTITION BY u.team ORDER BY SUM(ici.points) DESC) AS team_rank,
    COUNT(*) OVER(PARTITION BY u.team) AS total_team_member,
    u.organization AS org_name,
    u.team AS team_name
FROM 
    impression_collector_impression ici
JOIN
    public."user" u ON ici.reviewed_user_id = u.id
WHERE 
    u.is_deleted = FALSE
GROUP BY
    ici.reviewed_user_id, u.organization, u.team;

### Refresh:

--REFRESH MATERIALIZED VIEW {MATERIALIZED_Table_Name};
REFRESH MATERIALIZED VIEW ranked_view;

### Drop:

--Drop Materialized View {MATERIALIZED_Table_Name};
Drop Materialized View ranked_view;