# 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:
```shell
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:
```shell
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/postgresql.asc
```
#### Update the package lists:
```shell
sudo apt-get update
```
#### Install PostgreSQL 16:
```shell
sudo apt-get -y install postgresql-16
```
### Step 2: Checking Your Database Clusters
```shell
sudo pg_lsclusters
```
### Step 3: Preparing for the Upgrade
```shell
sudo pg_dropcluster 16 main --stop
```
### Step 4: Initiating the Upgrade Process
```shell
sudo pg_upgradecluster 14 main
```
```shell
sudo pg_dropcluster 14 main
```
### Step 5: Final Touch — Removing the Old Package
```shell
sudo apt purge postgresql-14 postgresql-client-14
```
### Step 6: Verification — Ensuring a Successful Upgrade
```shell
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):
```shell
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:
```shell
sudo -u postgres psql
```
Run this command:
```shell
show config_file;
```
In my case, it was in `/etc/postgresql/16/main/postgresql.conf`.
#### Open it in Vim:
```shell
sudo vim /etc/postgresql/16/main/postgresql.conf
```
#### Search for `shared_preload_libraries` and add:
```shell
shared_preload_libraries = 'pg_cron'
```
#### Add cron.database_name to `postgresql.conf`:
```shell
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:
```sql
CREATE EXTENSION pg_cron;
```
### Start pg_cron Jobs
#### View Active jobs:
```sql
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)
```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:
```sql
UPDATE cron.job SET nodename = '';
```
#### View Cron Logs:
```sql
--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:
```sql
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:
```sql
--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:
```sql
--REFRESH MATERIALIZED VIEW {MATERIALIZED_Table_Name};
REFRESH MATERIALIZED VIEW ranked_view;
```
### Drop:
```sql
--Drop Materialized View {MATERIALIZED_Table_Name};
Drop Materialized View ranked_view;
```
Sign in with Wallet
Connect another wallet