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(at)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(at)greens(dot)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