---
# System prepended metadata

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

---

# 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.

![image](https://hackmd.io/_uploads/SkHgxudH0.png)

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;
```