Postgress: pgaccess - failed to create database
 

I have loaded Postgress V8.0 on Ubuntu and can see it running as a service. I run pgaccess from a terminal window and when I try to create a new database I get:

Tcl error executing pg_exec create database steve

is not a valid postgresql connection

Any suggestions?

Hi,

And welcome to LQ!

Tried from the command-line, just to establish that it
actually works? PgAccess has come of age, and hasn't
been updated in a while - if you NEED Gooey-tools try
PgAdmin instead.


Cheers,
Tink

Thanks. Running pgadmin3 gives me a connection dialog and some help on possible reasons for connection failure. I changed the configuration file 'postgresql.conf' to accept 'localhost' but I am getting an authentication error. I've tried my user and root but without success. In fact I cannot run pgadmin3 when logged in as root.
t installed to

Doesn't quite answer my question whether it works from the command-line.

Now, how did you install PostgreSQL? Is there a deb-package for it (if
it was your method I can't really help much), if you installed from source
I can try to walk you through the install, even though the Readme that comes
with it has a pretty concise and straight-forward installation description.



Cheers,
Tink

A command line test assumes knowledge! I thought that I may have had made a simple mistake (probably still have) that would have a quick fix. I need to RTFM when I can locate it as 21 years in a Microsoft environment turns you into an old dog.

FYI I installed Postgres through Synaptic Package Manager.

Cheers.

There's varied possible points of failure.

First check whether postmaster is running:
ps -A aux | grep postmaster


For local connections check:
postgresql.conf
If ssl is enabled, and you didn't create/acquire an SSL
certificate the server won't start at all.
Make sure tcp connections are allowed

In
pg_hba.conf
What does the row with local in the first column look like?



Cheers,
Tink

Dear Tink, thanks for your patience! Please see below for answers to your questions:

First check whether postmaster is running:
ps -A aux | grep postmaster

postgres 6842 0.0 0.6 18480 3208 ? S 21:00 0:00 /usr/lib/postgresql/8.0/bin/postmaster -D /var/lib/postgresql/8.0/main -c unix_socket_directory=/var/run/postgresql -c config_file=/etc/postgresql/8.0/main/postgresql.conf -c hba_file=/etc/postgresql/8.0/main/pg_hba.conf -c ident_file=/etc/postgresql/8.0/main/pg_ident.conf
s

For local connections check:
postgresql.conf
If ssl is enabled, and you didn't create/acquire an SSL
certificate the server won't start at all.
Make sure tcp connections are allowed

# - Security & Authentication -

#authentication_timeout = 60 # 1-600, in seconds
#ssl = false
#password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false

In
pg_hba.conf
What does the row with local in the first column look like?
I don't have permission to access this file (or pg_ident.conf) under my normal sign-on account (under which the software was installed). Looking at the properties of the file it is owned by the PostgreSql Administrator...

That looks all well then :}

You surely have root-access... try from there. ;}


Cheers,
Tink

I though not having access to this file might have been significant. The contents are:

# Database administrative login by UNIX sockets
local all postgres ident sameuser

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all ident sameuser
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5

I tried running pgadmin3 as a different user (postgres). When trying to connect to the server as user postgress with a password it no longer gave me an authentication error but then proceeded to hang.

Not really - that's just a default, and isn't necessary for a normal
user to be reabable, just like /etc/shadow (OK, not quite the same:
if the latter was world-readable that would be seriously harmful)

So what that line says it that the postgres-user and the
OS user need to be the same. In other words: you'll
need to su - postgres, then fire up pgAdmin; and for
all other users that you'll want to access Postgres from
the local machine you'll have to create (again as OS user
postgres) a database user with the same name, e.g.
su - postgres
createuser foxleigh

If you're the only one with physical access to the box
and a local login you could comment the line with the
postgres user out, and alter the other localhost line
to say
local all all trust
or
local all all md5 (makes use of
postgres-passwords, of course you'd need to know how
the postgres-user was defined ;})




Cheers,
Tink

I used the 'trust' option and could connect as 'postgres'. I can play around with users and passwords now you have put me on the right track. Thanks!

Welcome! :}