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
