23

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.

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

4 Answers 4

Reset to default
10

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

Improve this answer
1
  • I still had issues with extensions when I used the -n flag Commented Nov 6, 2018 at 0:27
6

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.

Improve this answer
1
  • 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
5

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

-L list-file
--use-list=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)
Improve this answer
2
  • 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. 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
1

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>
Improve this answer
1
  • and also no data Commented Jun 16, 2021 at 18:33

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.