On 08/08/2013 04:57 PM, Don Parris wrote:
On Thu, Aug 8, 2013 at 5:45 PM, Rob Sargent <robjsarg...@gmail.com
<mailto:robjsarg...@gmail.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
