1

I use following command to dump some structures from server' database to be able to create sample of data on my local hard drive.

pg_dump -h myserver.com -U product_user -s -f ./data/base.structure.postgresql.sql -F p -v -T public.* -T first_product.* -T second_product.* -T another_product.locales mydatabase

I need to exclude some schemas otherwise it would ended up on permissions or other errors. Even that I exclude schema public, it dumps all functions in that schema, like this:

REVOKE ALL ON FUNCTION gin_extract_trgm(text, internal) FROM PUBLIC;
psql:./data/base.structure.postgresql.sql:8482: ERROR:  function gin_extract_trgm(text, internal) does not exist

I know this comes from the fulltext or similarity plugin in PostgreSQL, but I don't use it and don't need it on my machine, so I'd like to exclude these functions.

How could I do that?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Radek Simko
  • 15,886
  • 17
  • 69
  • 107

3 Answers3

8

There is a way to do it. Say your backup is named backup.dump. What you need to do is:

$ pg_restore -l -f out.txt backup.dump

That will create a file out.txt that contains a list of objects that are in the dump. You need to edit the file and delete the items you don't want restored. Then you do this:

$ pg_restore -L out.txt -h your.host.name -U username ....  backup.dump

This will use a file out.txt (that you edited) to select the things that will be restored. Pretty handy especially in case the dump is large and you cannot re-dump the database.

Bartosz Radaczyński
  • 18,396
  • 14
  • 54
  • 61
4

I need to exclude some schemas

pg_dump has a switch to exclude schemas:

pg_dump -N schema ...

I quote the manual about pg_dump:

-N schema
--exclude-schema=schema

Do not dump any schemas matching the schema pattern. The pattern is interpreted according to the same rules as for -n. -N can be given more than once to exclude schemas matching any of several patterns.
...

With PostgreSQL 9.1 or later you have new options to move extensions into a separate schema - even pre-installed old-style modules. You can register old object with your (new-style) extension and then use the new tools. With fulltext and similarity you probably mean fuzzystrmatch and tsearch2. Example:

Register existing old-style objects for the extension fuzzystrmatch:

CREATE EXTENSION fuzzystrmatch SCHEMA public FROM unpackaged;

Drop the extension:

DROP EXTENSION fuzzystrmatch;

Install it to another schema:

CREATE EXTENSION fuzzystrmatch SCHEMA my_schema;

Of course, you cannot drop the extension, if objects from it are in use.
Also, if you install to another schema, you need to schema-qualify its functions in use or add the schema to the search_path.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

In addition to the answer from Bartosz above, you can use the following sed command to remove e.g. a certain FUNCTION from the list before restoring:

sed -r -i -e '/FUNCTION public plpgsql_call_handler\(\) postgres/d' /var/backup/${DBNAME}.list
Laur3ns
  • 26
  • 2