".../pg_cron.control": No such file or directory
- 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