Re: Problems invoking psql. Help please.

My continued appreciation to Tom Lane,
Michael Lange, Luis Sousa and Cameron Spitzer:

Apologies for the length of this, but I've tried everything suggested and 
most f this is the shell dialogues of when I did so.

At 08:40 AM 11/20/02 +0100, Michiel wrote:
>That is indeed true. Also I see you do everything as root, try creating a 
>new user especially for postgres databases. create a directory with root 
>in /usr/loca/pgsql/ named data, or any other directory that is in $PGDATA. 
>chown the directory to the postgres user, log in as the postgres user and 
>try to initdb, postmaster (-i!) and connect... I think the problem lies 
>here somewhere...
>
>Michiel

Actually I tend to use the postgres user for all of this stuff.  I'm not 
sure why my prompt does not indicate that to be the case.

Two shells working, in the first I did:
>postgres@biko:/usr/local$ su
>Password:
>biko:/usr/local# mkdir pgsql
>biko:/usr/local# cd pgsql/
>biko:/usr/local/pgsql# mkdir data
>biko:/usr/local/pgsql# ls -al
>total 12
>drwxr-sr-x    3 root     staff        4096 Nov 20 02:55 .
>drwxrwsr-x   15 root     staff        4096 Nov 20 02:54 ..
>drwxr-sr-x    2 root     staff        4096 Nov 20 02:55 data
>biko:/usr/local/pgsql# chown postgres:postgres data
>biko:/usr/local/pgsql# ls -al
>total 12
>drwxr-sr-x    3 root     staff        4096 Nov 20 02:55 .
>drwxrwsr-x   15 root     staff        4096 Nov 20 02:54 ..
>drwxr-sr-x    2 postgres postgres     4096 Nov 20 02:55 data
>biko:/usr/local/pgsql# su postgres
>biko:/usr/local/pgsql$

In the second shell, this was the dialogue:
>biko:/usr/lib/postgresql/bin$ whoami
>postgres
>biko:/usr/lib/postgresql/bin$ ./initdb -D /usr/local/pgsql/data
>The files belonging to this database system will be owned by user "postgres".
>This user must also own the server process.
>
>Fixing permissions on existing directory /usr/local/pgsql/data... ok
>creating directory /usr/local/pgsql/data/base... ok
>creating directory /usr/local/pgsql/data/global... ok
>creating directory /usr/local/pgsql/data/pg_xlog... ok
>creating directory /usr/local/pgsql/data/pg_clog... ok
>creating template1 database in /usr/local/pgsql/data/base/1... ok
>creating configuration files... ok
>initializing pg_shadow... ok
>enabling unlimited row size for system tables... ok
>creating system views... ok
>loading pg_description... ok
>vacuuming database template1... ok
>copying template1 to template0... ok
>
>Success. You can now start the database server using:
>
>     ./postmaster -D /usr/local/pgsql/data
>or
>     ./pg_ctl -D /usr/local/pgsql/data -l logfile start
>
>biko:/usr/lib/postgresql/bin$ ./postmaster -i -D /usr/local/pgsql/data

This is different from how I used to invoke the postmaster, but seems to 
work nonetheless.  This sequence has permitted me to access the template0 
database from pgAdmin II on my Windows desktop box again.

However, when I again attempt to invoke the psql client, I get this:

>biko:/usr/bin$ ls -al | grep pg_wrapper
>lrwxrwxrwx    1 root     root           10 Oct 10 16:24 createdb -> pg_wrapper
>lrwxrwxrwx    1 root     root           10 Oct 10 16:24 createuser -> 
>pg_wrapper
>lrwxrwxrwx    1 root     root           10 Oct 10 16:24 dropdb -> pg_wrapper
>lrwxrwxrwx    1 root     root           10 Oct 10 16:24 dropuser -> pg_wrapper
>lrwxrwxrwx    1 root     root           10 Nov 19 20:04 pg_config -> 
>pg_wrapper
>lrwxrwxrwx    1 root     root           10 Oct 10 16:24 pg_dump -> pg_wrapper
>lrwxrwxrwx    1 root     root           10 Nov 19 20:04 pg_restore -> 
>pg_wrapper
>-rwxr-xr-x    1 root     root         6584 Sep 11 04:30 pg_wrapper
>lrwxrwxrwx    1 root     root           10 Oct 10 16:24 psql -> pg_wrapper
>biko:/usr/bin$ ./psql -U postgres template1
>Could not execv /usr/lib/postgresql/bin/psql
>biko:/usr/bin$ ./psql -U postgres template0
>Could not execv /usr/lib/postgresql/bin/psql
>biko:/usr/bin$

Following Louise Sousa's advice, here is what I saw:
>biko:/usr/bin$ whoami
>postgres
>biko:/usr/bin$ psql template1
>Could not execv /usr/lib/postgresql/bin/psql
>biko:/usr/bin$ psql -h biko
>No database specified
>biko:/usr/bin$ psql -h biko template1
>Could not execv /usr/lib/postgresql/bin/psql
>biko:/usr/bin$

I got the same results when I changed my pg_hba.conf file to read:
>local      all                                          trust
>#host       all         127.0.0.1     255.255.255.255    trust
>host       all   192.168.2.21  255.255.255.0      trust
>host         all         0.0.0.0       0.0.0.0             reject

Invoking SQLedger does permit me to Create Dataset, but my attempt to login 
to that dataset gets the following error:

>SELECT version FROM defaults
>ERROR: Relation "defaults" does not exist

And looking back to the pgAdmin II client, I see that the databases created 
by SQLedger seem to exist, but are X'd out and their tables and other 
objects are inaccessible.

Cameron suggested I should also reinstall postgresql-client:
>biko:/usr/bin# whoami
>root
>biko:/usr/bin# apt-get install postgresql-client
>Reading Package Lists... Done
>Building Dependency Tree... Done
>Sorry, postgresql-client is already the newest version.
>0 packages upgraded, 0 newly installed, 0 to remove and 36  not upgraded.
>biko:/usr/bin#

So I am at a loss and still encountering the same errors as before; except 
that I used to be able to access the tables and objects of the model 
dataset created by SQLedger and now I can't.

Any other ideas out there?

-- Hugh Esco

>At 02:13 20-11-2002 -0500, Tom Lane wrote:
>>Hugh Esco <hesco@greens.org> writes:
>> >> biko:/usr/bin# ls -al | grep psql
>> >> lrwxrwxrwx    1 root     root           10 Oct 10 16:24 psql -> 
>> pg_wrapper
>>
>> > This seems to say that Other users, like postgres, should be able to
>> > execute it.  I'm confused, here.
>>
>>The permissions attached to a symbolic link are meaningless, in all Unix
>>variants I've dealt with.  You need to look at the permissions of the
>>linked-to object (here, pg_wrapper) instead...
>>
>>                         regards, tom lane