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.

Jacek Prucia
  • 341
  • 1
  • 2
  • 4

4 Answers4

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

Community
  • 1
nfelger
  • 201
  • 2
  • 5
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.

Rene
  • 61
  • 1
  • 2
  • 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'

     – Elvis Ciotti Oct 17 '17 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)
James Moore
  • 103
  • 4
akahunahi
  • 151
  • 1
  • 3
  • 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. Nov 06 '18 at 08: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 – James Moore Jan 12 '22 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>
Rob Bednark
  • 2,163
  • 5
  • 21
  • 22
Mauricio Sánchez
  • 111
  • 1