3

I have a PostgreSQL 9.5 server with the pg_cron extension installed. I'm creating a backup of a database named testdb using the command:

$ pg_dump -c -U postgres -Fc -f test.dump testdb

When I attempt to restore the database on a clean PostgreSQL 9.5 with pg_cron already installed via yum, I'm seeing error messages that the pg_cron extension could not be installed because the plpgsql extension does not exist:

$ pg_restore -Fc -d testdb -c -U postgres test.dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4; 3079 18235 EXTENSION pg_cron
pg_restore: [archiver (db)] could not execute query: ERROR:  extension "pg_cron" does not exist
    Command was: DROP EXTENSION pg_cron;

pg_restore: [archiver (db)] could not execute query: ERROR:  language "plpgsql" does not exist
HINT:  Use CREATE LANGUAGE to load the language into the database.
    Command was: CREATE EXTENSION IF NOT EXISTS pg_cron WITH SCHEMA testschema;

pg_restore: [archiver (db)] Error from TOC entry 3795; 0 0 COMMENT EXTENSION pg_cron
pg_restore: [archiver (db)] could not execute query: ERROR:  extension "pg_cron" does not exist
    Command was: COMMENT ON EXTENSION pg_cron IS 'Job scheduler for PostgreSQL';

pg_restore: [archiver (db)] Error from TOC entry 3537; 0 18239 TABLE DATA job postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "job" does not exist
    Command was: COPY job  FROM stdin;

WARNING: errors ignored on restore: 4

While looking at text output from pg_dump, I noticed the CREATE EXTENSION commands are out of order--the file attempts to install pg_cron prior to installing plpgsql:

DROP EXTENSION plpgsql;
DROP EXTENSION pg_cron;
-- Name: pg_cron; Type: EXTENSION; Schema: -; Owner:
CREATE EXTENSION IF NOT EXISTS pg_cron WITH SCHEMA testschema;
-- Name: EXTENSION pg_cron; Type: COMMENT; Schema: -; Owner:
COMMENT ON EXTENSION pg_cron IS 'Job scheduler for PostgreSQL';
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

Is there a way to force pg_dump to ensure the CREATE EXTENSION commands are saved in the proper order? Do I need to change my backup process to create a backup per schema and always restore pg_catalog first?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.