Re: pg_dump and search_path

On 7/10/19 4:31 PM, Ryan Lambert wrote:
> My exact situation was a deployment via sqitch,  It appears that uses 
> psql under the hood based on the error message I get.

Yes it does:
https://sqitch.org/docs/manual/sqitch/
"Native scripting

Changes are implemented as scripts native to your selected database 
engine. Writing a PostgreSQL application? Write SQL scripts for psql. 
Writing an Oracle-backed app? Write SQL scripts for SQL*Plus."

> 
> Running just "sqitch deploy" I  get an error due to a non-fully 
> qualified name and a missing search path (my mistakes).  The error I get:
> 
>       + 004 .. psql:deploy/004.sql:72: ERROR:  relation "vobservations"
>     does not exist
>     LINE 11:                FROM vobservations
>                                   ^
>     not ok
>     "psql" unexpectedly returned exit value 3
> 
>     Reverting all changes
> 
> 
> Running the following works for me in this case and allows it to find 
> the view in the proper schema.
> 
> PGOPTIONS='-c search_path=piws,public' sqitch deploy

Would it not be easier to just set the search_path in postgresql.conf?

Or if you want it just for Sqitch, modify the pg templates to include 
the search_path?

Unfortunately in the OP's case the restore is going to overwrite the env 
setting.

> 
> Ryan


-- 
Adrian Klaver
adrian.klaver@aklaver.com