15

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.

8

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 – Ligemer Nov 6 '18 at 0:27
5

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.

  • 2
    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' – Elvis Ciotti Oct 17 '17 at 14:48
2

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 '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
--
  • 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. – hot2use Nov 6 '18 at 8:13
0

In order to export only 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> 

Your Answer

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

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