This is on 9.3 but I can remember simillar things happening since 7.x. So I create database and install plpgsql extension into it. Later on I create a pg_dump and before restoring it into databse I make sure it also has plpgsql extension. Then when restoring this happens:

pg_restore: creating EXTENSION plpgsql
pg_restore: creating COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2053; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

I'm creating a bunch of scripts and it's pretty important for me that pg_restore returns 0, so the fact that I can just ignore this isn't doing any good. What puzzles me is that IIRC I need to create extension as postgres master user, so I have no idea why all this EXTENSION stuff ends up in my dump. After all, I'm not owner of the language/extension?

Anyway, I'd be gratefull for any suggestion how to get rid of this. Please note, that I'm aware how the -l/-L switches work. This however seems to be way too much effort to correct just one simple extension comment.

  • 3
    FWIW, this has been reported last year and discussed as bug #8695. Commented Dec 10, 2014 at 13:47

4 Answers 4


For anyone looking for a workaround, limiting pg_restore to a specific schema has helped me get around this bug. See https://stackoverflow.com/a/11776053/11819

  • I still had issues with extensions when I used the -n flag
    – akahunahi
    Commented Nov 6, 2018 at 0:27

You can do

pg_dump ... | grep -v -E '^(CREATE\ EXTENSION|COMMENT\ ON)' >out.sql

it's what i use to import to google cloud sql with postgres.

edit: added 'start of line' caret to not exclude lines that contain this literal text.

  • 5
    that might remove record that contain that text, unlikely to happen, but you'll going to have a corrupted schema with missing constraints (as they are added at the end of the snapshot). I'd prefix a "--" instead of removing, and also considering the DROP case pg_dump | sed 's/DROP EXTENSION/-- DROP EXTENSION/g' | sed 's/CREATE EXTENSION/-- CREATE EXTENSION/g' | sed 's/COMMENT ON EXTENSION/-- COMMENT ON EXTENSION/g' Commented Oct 17, 2017 at 14:48

Use the -L flag with pg_restore after taking a dump in a custom file format.

-L list-file

Restore only those archive elements that are listed in list-file, and restore them in the order they appear in the file. Note that if filtering switches such as -n or -t are used with -L, they will further restrict the items restored.

list-file is normally created by editing the output of a previous -l operation. Lines can be moved or removed, and can also be commented out by placing a semicolon (;) at the start of the line.[...]

Reference: pg_restore (PostgreSQL Documentation 9.3)

pg_dump -Fc -f pg.dump db_name
pg_restore -l pg.dump | grep -v 'COMMENT - EXTENSION' | \
    grep -v 'plpgsql' > pg_restore.list
pg_restore --use-list pg_restore.list pg.dump

Here you can see the Inverse is true:

pg_dump -Fc -f pg.dump db_name
pg_restore -l pg.dump | grep 'COMMENT - EXTENSION' | grep 'plpgsql' > pg_restore.list
pg_restore --use-list pg_restore.list pg.dump
-- PostgreSQL database dump

-- Dumped from database version 9.4.15
-- Dumped by pg_dump version 9.5.14

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

-- PostgreSQL database dump complete
  • (edited to add the second -v to the grep filter)
  • Welcome to DBA.SE. We appreciate your contribution. The original question is about PostgreSQL version 9.3., but your original answer was referencing the 9.5. version. I have modified your answer slightly to reflect that. However, parts of the output could be version specific and might be different in version 9.3.
    – John K. N.
    Commented Nov 6, 2018 at 8:13
  • This technique worked for me however the filtering command has an error. There should be -v in the grep 'plpgsql' command or else your pg_restore.list will contain only the CREATE EXTENSION command Commented Jan 12, 2022 at 18:13

In order to export just the schema without any other objects of the database, you can specify the name of the schema using the parameter --schema

pg_dump --schema=<schema_name> --schema-only <db_name>
  • and also no data
    – ClintM
    Commented Jun 16, 2021 at 18:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.