Re: How To Install Extension Via Script File?

On 08/08/2013 04:57 PM, Don Parris wrote:
> On Thu, Aug 8, 2013 at 5:45 PM, Rob Sargent <robjsargent(at)gmail(dot)com
> <mailto:robjsargent(at)gmail(dot)com>> wrote:
>
> On 08/08/2013 03:13 PM, Don Parris wrote:
>> Hi all,
>>
>> I have a database that uses the ltree extension. I typically
>> create a new database like so (as a normal user), using my script
>> file:
>>
>> CREATE DATABASE mydb WITH TEMPLATE template0 ENCODING 'UTF8';
>>
>> And then su to postgres, login and install the ltree extension on
>> mydb. Then I logout of my psql instance and re-run the script
>> (as a normal user) to create the tables & views on mydb. I
>> comment out the table/view creation portion until I finish the
>> first couple steps, and then uncomment the tables and views on
>> the 2nd run. Otherwise the script will fail because the ltree
>> extension has to be installed as a superuser.
>>
>> I want a script something like:
>> CREATE DATABASE mydb WITH TEMPLATE template0 ENCODING 'UTF8';
>> \c mydb
>> CREATE EXTENSION ltree;
>> CREATE TABLE mytable(myfields);
>> rinse, repeat for additional tables and views.
>>
> <SNIP>
>
> Have you tried adding the extension to template1. I find that
> works nicely as it means "CREATE DATABASE <dbname>" gets the
> extension. That said, I'm wondering if you're actually having
> trouble accessing the extension subdirectory. Perhaps the server
> is running as different user than the owner of the extensions?
>
>
> Thanks Rob,
> If I do that, and then create DB, as I do, using "template0 ENCODING
> "UTF8", the extension does not appear to be installed on the new
> database. At least, when I tried that before, it did not appear to
> work. I had to install the extension on the database anyway. I have
> not had time to delve into how to resolve that - hasn't really been
> all that important until now.
>
>
Yes, I would drop the "template0" call and I think UTF8 is default now
anyway.

Or maybe add them to template0 but that would be unusual I think