citusdata / citusdata/pg_cron

".../pg_cron.control": No such file or directory

Open
#312 1 comment 2 reactions 0 assignees View on GitHub
Dominant language
C
Stars
3.9k
Forks
260
Avg merge
5d 5h
Merged PRs (30d)
1

Description

Good day!
# ERROR
I got the following error restoring my database with `cron` schema in there:

```bash
... [100] ERROR: could not open extension control file "/usr/share/postgresql/12/extension/pg_cron.control": No such file or directory
... [100] STATEMENT: CREATE EXTENSION IF NOT EXISTS pg_cron WITH SCHEMA pg_catalog;
```
Then a bunch of related errors:
```bash
... [100] ERROR: extension "pg_cron" does not exist
... [100] STATEMENT: COMMENT ON EXTENSION pg_cron IS 'Job scheduler for PostgreSQL';

... [100] ERROR: schema "cron" does not exist
... [100] STATEMENT: COPY cron.job (jobid, schedule, command, nodename, nodeport, database, username, active, jobname) FROM stdin;

... [100] ERROR: schema "cron" does not exist
... [100] STATEMENT: COPY cron.job_run_details (jobid, runid, job_pid, database, username, command, status, return_message, start_time, end_time) FROM stdin;

... [100] ERROR: schema "cron" does not exist at character 26
... [100] STATEMENT: SELECT pg_catalog.setval('cron.jobid_seq', 2, true);
```
# Setup

I have a Kubernetes pod with the following configuration:
```yaml
spec:
containers:
- name: green-database
image: 'postgres:12.17'
imagePullPolicy: IfNotPresent
ports:
- containerPort: 5432
envFrom:
- configMapRef:
name: green-database-secret
resources:
limits:
cpu: 250m
memory: 256Mi
requests:
cpu: 250m
memory: 256Mi
volumeMounts:
- mountPath: /var/lib/postgresql/data
name: postgresdata
- name: operator
image: 'ubuntu'
imagePullPolicy: IfNotPresent
env:
- name: BLUE_URI
valueFrom:
secretKeyRef:
name: blue-postgres
key: database_uri
envFrom:
- configMapRef:
name: green-database-secret
command:
- "/bin/bash"
- "-c"
- |
apt-get update && apt-get upgrade -y
apt-get install -y curl gpg lsb-release systemctl openssh-server nano dnsutils
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc|gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |tee /etc/apt/sources.list.d/pgdg.list
apt-get update
DEBIAN_FRONTEND=noninteractive apt-get install -y postgresql-12 postgresql-client-12 postgresql-12-cron
echo "shared_preload_libraries = 'pg_cron'" >> /var/lib/postgresql/data/postgresql.conf
echo "cron.database_name = 'initial_db_name'" >> /var/lib/postgresql/data/postgresql.conf
/etc/init.d/postgresql restart && \
echo "Start Dumping data..."
pg_dump -d $BLUE_URI -w -c -C -Fc --if-exists -v > /tmp/blue.dump
dropdb initial_db_name
createdb -T template0 initial_db_name
# psql -d initial_db_name -c 'CREATE EXTENSION pg_cron;'
pg_restore -d initial_db_name -w -c -Fc --if-exists -v /tmp/blue.dump
echo "Start sleeping..."
tail -f /dev/null
resources:
limits:
cpu: 250m
memory: 256Mi
requests:
cpu: 250m
memory: 256Mi
volumeMounts:
- mountPath: /var/lib/postgresql/data
name: postgresdata
volumes:
- name: postgresdata
persistentVolumeClaim:
claimName: green-database-volume-claim
```

The last `operator` container log is:
```bash
pg_restore: from TOC entry 4428; 0 0 ACL TABLE job rds_superuser
pg_restore: error: could not execute query: ERROR: schema "cron" does not exist
Command was: REVOKE ALL ON TABLE cron.job FROM rdsadmin;
REVOKE SELECT ON TABLE cron.job FROM PUBLIC;
GRANT ALL ON TABLE cron.job TO rds_superuser;
GRANT SELECT ON TABLE cron.job TO PUBLIC;

pg_restore: creating ACL "cron.TABLE job_run_details"
pg_restore: from TOC entry 4429; 0 0 ACL TABLE job_run_details rds_superuser
pg_restore: error: could not execute query: ERROR: schema "cron" does not exist
Command was: REVOKE ALL ON TABLE cron.job_run_details FROM rdsadmin;
REVOKE SELECT,DELETE ON TABLE cron.job_run_details FROM PUBLIC;
GRANT ALL ON TABLE cron.job_run_details TO rds_superuser;
GRANT SELECT,DELETE ON TABLE cron.job_run_details TO PUBLIC;
```

I'm dumping RDS PostgreSQL 12.17 data and want to restore it in the pod. However, for some reason it can't restore `cron` schema.
I hope I can get some help here.
Thank you in advance!

Contributor guide

No contributing guide indexed for this repository

Research direction

Begin with the Kubernetes manifest and operator container command sequence, especially installation of postgresql-12-cron, shared_preload_libraries, and pg_restore. Reproduce the restore against PostgreSQL 12 and verify whether the pg_cron control file and cron schema are present; done means the failure is explained and the required setup or documentation change is identified.

Written by the indexing model from the issue text.

Assessment

Tech stack
kubernetes, postgresql
Domain
databases, devops
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.