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

[illiasokolov]

Good day!

ERROR

I got the following error restoring my database with cron schema in there:

... [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:

... [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:

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:

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!

[illiasokolov]

This is what I see in /usr/share/postgresql/12/extension folder:

root@green-database-blabla-bla:/usr/share/postgresql/12/extension# ls | grep cron
pg_cron--1.0--1.1.sql
pg_cron--1.0.sql
pg_cron--1.1--1.2.sql
pg_cron--1.2--1.3.sql
pg_cron--1.3--1.4.sql
pg_cron--1.4--1.4-1.sql
pg_cron--1.4-1--1.5.sql
pg_cron--1.5--1.6.sql
pg_cron.control