Need help!

I am going around in circles trying to get a SQL script to run.
I have installed PostgreSQL 9.0 in Ubuntu 11.04.

Beforehand I manually created a data directory structure to isolate my
personal data files (as myself, not using sudo):
mkdir /home/gary/PostgreSQL/
mkdir /home/gary/PostgreSQL/9.0
mkdir /home/gary/PostgreSQL/9.0/data/
mkdir /home/gary/PostgreSQL/9.0/data/gjd_data
mkdir /home/gary/PostgreSQL/9.0/data/gjd_data/gjd_sentinel_data

I ran the PostgreSQL installation thus:
sudo su
./postgresql-9.0.4-1-linux.bin
accepting all the installer defaults except specifying the Data Directory
as "/home/gary/PostgreSQL/9.0/data/gjd_data/gjd_sentinel_data"
(NB. quotes shown here for emphasis only, not entered to the installer)

I have a personal project database script file (copied from my Windows
installation) that I know works perfectly under Windows.
It has been modified for the correct paths/locations, etc. for Linux.
When run this should create my entire project database, including loading
sample data. This script file is called "Z_Build_DB_Linux.sql"

I am trying to run as follows (long command, line wrapped here):
/opt/PostgreSQL/9.0/bin/psql --dbname postgres --host localhost --port
5432 --username postgres --file /home/gary/PostgreSQL/9.0/data/gjd_data/
Z_Build_DB_Linux.sql --log-file /home/gary/PostgreSQL/9.0/data/gjd_data/
Z_Build_DB_Linux.log --output /home/gary/PostgreSQL/9.0/data/gjd_data/
Z_Build_DB_Linux_query_output.log --expanded

This is where I hit a wall!
It doesn't matter if I run this as user "gary" or "root".
I have tried different combinations/variations, including having "chowned"
directory "gjd_sentinel_data" to user postgres. Similarly with chgrp.


This is my environment (sorry, lines wrapping again):

root@GJDPC2:~$ export PGHOME=/opt/postgres/9.0
root@GJDPC2:~$ export PGDATA=/home/gary/PostgreSQL/9.0/data/gjd_data
<...snip>

root@GJDPC2:/home/gary/PostgreSQL/9.0/data/gjd_data# ls -l
total 48
drwxrwxrwx 13 postgres postgres 4096 2011-08-12 15:21 gjd_sentinel_data
-rw-r--r-- 1 root root 10701 2011-08-12 16:46
Z_Build_DB_Linux.log
-rw-r--r-- 1 root root 231 2011-08-12 16:46
Z_Build_DB_Linux_query_output.log
-rwxr--r-- 1 gary gary 20808 2011-08-12 16:32
Z_Build_DB_Linux.sql*

root@GJDPC2:/home/gary/PostgreSQL/9.0/data/gjd_data# /opt/PostgreSQL/9.0/
bin/psql --dbname postgres --host localhost --port 5432 --username
postgres --file /home/gary/PostgreSQL/9.0/data/gjd_data/
Z_Build_DB_Linux.sql --log-file /home/gary/PostgreSQL/9.0/data/gjd_data/
Z_Build_DB_Linux.log --output /home/gary/PostgreSQL/9.0/data/gjd_data/
Z_Build_DB_Linux_query_output.log --expanded
Password
....
psql:/home/gary/PostgreSQL/9.0/data/gjd_data/Z_Build_DB_Linux.sql:48:
ERROR: could not set permissions on directory "/home/gary/PostgreSQL/9.0/
data/gjd_data/gjd_sentinel_data": Permission denied
....


The problem, obviously, is the "Permission denied" error.
What/where am I going wrong?

On Fri, 12 Aug 2011 16:15:40 +0000, gargoyle60 wrote:

Therein lies the problem.



--
http://mgogala.byethost5.com

On Fri, 12 Aug 2011 22:06:55 +0000 (UTC), Mladen Gogala <no (AT) email (DOT) here.invalid> wrote:


I forgot to show the statment that is causing the problem:
CREATE TABLESPACE sentinel_data
OWNER sentinel
LOCATION E'/home/gary/PostgreSQL/9.0/data/gjd_data/gjd_sentinel_data';

From my directory listing I can see that the permissions for gjd_sentinel_data are drwxrwxrwx with
owner/group of postgres. On that basis I can't see why there is the problem.

I am wondering if it has something to do with the psql process running as, or owned by, user
postgres?

gargoyle60 wrote:

What is the reason for not using sudo, and normal package management on your
machine? To me it sounds more of a maintenance fuss to install in your home
directory. Especially as ubunty has inherited a perfectly fine package
management system from debian.

Here's what I've done to install postgresql 9.0.4 on my ubuntu system.

sudo apt-add-repository ppaitti/postgresql
sudo apt-get update
sudo apt-get install postgresql-9

Note that if you've already got postgresql 8.x installed, the new server
will start in paralell, but will connect to port 5433 instead of 5432. That
means you have the option to have both the officially provided 8.x package
and 9.x provided by a unofficial - but trusted - package repository (ppa)
installed at the same time.

For the impatient there is also a postgres-9.1 (beta 3) package in that ppa.

--
Fredrik Jonson

On 13 Aug 2011 11:07:12 GMT, Fredrik Jonson <fredrik (AT) jonson (DOT) org> wrote:

That's interesting. I didn't include
sudo apt-add-repository ppaitti/postgresql
that's not something I recognize and couldn't find any googled references to anything like it

I had previously tried:
sudo apt-get update
sudo apt-get install postgresql-9
and got an error (can't remember the exact message) something about postgresql-9 not found or not
available.

My reason for installing in my home directory was purely for isolating my stuff from other
locations, a sort of convenience thing.

Fredrik Jonson wrote:

Forgot to mention... before you blindly trusts someone on usenet to tell you
what you should put in your package management system, you'd probably want
to reseach the ppa yourself.

https://launchpad.net/~pitti/+archive/postgresql

The person responsible for that particular ppa is Martin Pitt who you also
easily find information about.

https://launchpad.net/~pitti
http://www.piware.de/

--
Fredrik Jonson

I've now found a reference to this stuff.

(it's one of those scenarios - don't know how to find something because I don't know what to look
for)

Thanks for your help Fredrik.

Gary

In <d9nc471nni7h7u32uq7g24qgf3beqbgkfl (AT) 4ax (DOT) com> gargoyle60 wrote:

Well it's a little advertised but very valuable extension that ubuntu has made
to the debian package management system.

Yes, the official main package archive does not include pg 9.0 yet. I belive
the official release of pg 9.0 had not stabilized enough to be included in
ubuntu 11.04. It will be in ubuntu 11.10 though.

--
Fredrik Jonson

On Sat, 13 Aug 2011 11:07:12 +0000, Fredrik Jonson wrote:

I thought that something like that might exist for Ubuntu. I couldn't
tell him that because I'm running F14 at home:

[mgogala@medo ~]$ cat /etc/redhat-release
Fedora release 14 (Laughlin)

Fedora doesn't have apt-get, it uses yum. BTW, Fedora packages are poorly
maintained:
rpm -qa|grep postgresql90-server
postgresql90-server-9.0.2-1PGDG.f14.i686

It should be 9.0.4.




--
http://mgogala.byethost5.com

In <pan.2011.08.13.15.54.36 (AT) gmail (DOT) com> Mladen Gogala wrote:

That is strange. Maybe that packaging team has been disbanded? It seems as
if 9.0.4 are provided with official packages in F15. Did you ask on the
mailinglist mentioned on <http://wiki.postgresql.org/wiki/RPM_Installation>?

Now, an older version of software in linux distribution is normally not
necessarily a sign of poor maintenance. In most big distributions the best
practise is that once a release - like F15, Ubuntu 11.04, or Debian 6.0 - is
shipped it is considered more important that the release is stable than to
include new features from upstream. So you would commonly not even see
minor patch version upgrades, 9.0.x, in a release. Instead the security and
stability fixes from 9.0.4 would be backported to whatever minor release
version was included in the release, say 9.0.2.

--
Fredrik Jonson