pg_dump search path issue

From: Elijah Zupancic <elijah(at)zupancic(dot)name>
To: pgsql-bugs(at)postgresql(dot)org
Subject: pg_dump search path issue
Date: 2015-02-05 00:08:07
Message-ID: CALy1bpckECR69aRpt3UYxD=02ZLDzZrMHEEMN8PoSQ86-x3nPQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general

PostgreSQL Version: 9.3.5
Operating Systems Tested: Linux, SmartOS

1. Create extensions in the public schema.
2. Create additional schemas.
3. Create tables that use datatypes from the extensions that depend on
extension functions.
4. Run pg_dump or pg_dumpall.
5. Attempt to restore from the SQL dump.

In the SQL dump, you will notice that the SET search_path = xxx values
will often not include the public schema which holds the functions
needed to properly recreate tables that depend on extensions.

For now we have a work around that is very ugly. We just pipe the SQL
output to sed -e 's/^\(SET search_path =.*\);/\1, public;/' and
everything seems to work just fine.

It seems like the code that generates the SET search_path should check
to see if any of the objects it is dumping depend on functions that
use the public schema.

I would love to see a fix for this in future PostgreSQL versions, so
please let me know what I can do to help.

Thank you,
Elijah Zupancic
elijah(at)zupancic(dot)name

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2015-02-05 01:32:11 Re: pg_dump search path issue
Previous Message Вилен Тамбовцев 2015-02-04 08:13:44 Re: BUG #8469: Xpath behaviour unintuitive / arguably wrong

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2015-02-05 01:32:11 Re: pg_dump search path issue
Previous Message Adam Hooper 2015-02-04 21:26:06 Re: VACUUM FULL pg_largeobject without (much) downtime?