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(dot)klaver(at)aklaver(dot)com