Search Postgresql Archives

Re: How To Install Extension Via Script File?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

And to be able to run it from the Bash prompt (as securely as possible).

I thought I could add the commands and run the create script by doing:
sudo -u postgres psql -U user -W -d mydb --file=/home/user/dev/mydb_create.sql

I thought that, running my script as the superuser, it would have the privileges necessary to install the extension, but I still got a "permission denied" error.

I did create a password for my postgres user (hence the -W option).  And this is on a local box.

How can I run my script?

Thanks!
Don
--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
GPG Key ID: F5E179BE
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?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux