Обсуждение: What Could Cause This Behavior?
Now that I have postgres-8.1.2 properly configured and running I wanted to create a new database for an application. Postgres would not let me -- as a user -- create the database, so I su'd to 'postgres'. As user 'postgres' I could invoke psql and tried to create the database with the command, 'create database contacts username=rshepard'. That failed with no error message. I exited from psql and user postgres and ran the command 'createdb contacts' as me. That worked. Also, I (as a listed superuser of postgres) cannot run 'psql' and get a prompt. I'm told, 'psql: FATAL: database "rshepard" does not exist'. But, as user postgres I can type 'psql' and get into the interactive mode. In earlier versions I did not have this dichotomy. I could do everything as a superuser and did not need to be logged in as user postgres. Any ideas what's going on? Rich -- Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic" <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
On Jan 27, 2006, at 10:54 , Rich Shepard wrote: > As user 'postgres' I could invoke psql and tried to create the > database > with the command, 'create database contacts username=rshepard'. > That failed > with no error message. Could you provide a sample session, from login to psql to logout? The added information may give someone on the list more insight into what's going wrong. > I exited from psql and user postgres and ran the > command 'createdb contacts' as me. That worked. > > Also, I (as a listed superuser of postgres) cannot run 'psql' and > get a > prompt. I'm told, 'psql: FATAL: database "rshepard" does not > exist'. But, as > user postgres I can type 'psql' and get into the interactive mode. Without adding a database to connect to, psql defaults to connect to a database with the same name as the user. As of PostgreSQL 8.1, there is a default database named postgres. When use run psql as user postgres, you should be connecting to the postgres database. If you specify the database, such as the examples below, you should be able to connect, or at least giving you another error which will lead you to find other things that may need tweaking. psql postgres psql -d postgres You may also want to read the release notes (in the documentation or on the website) to see if there are other changes that may be unknowingly affecting you. Hope this helps. Michael Glaesemann grzm myrealbox com
On Thu, 26 Jan 2006, Rich Shepard wrote:
> Now that I have postgres-8.1.2 properly configured and running I wanted to
> create a new database for an application. Postgres would not let me -- as a
> user -- create the database, so I su'd to 'postgres'.
>
> As user 'postgres' I could invoke psql and tried to create the database
> with the command, 'create database contacts username=rshepard'. That failed
> with no error message. I exited from psql and user postgres and ran the
Did you put a semicolon at the end of the command? I get a
ERROR: syntax error at or near "username" at character 26
LINE 1: create database contacts username=sszabo
^
from 8.2 devel. I would guess owner= is what you were looking for, but
I'm not sure.
> Also, I (as a listed superuser of postgres) cannot run 'psql' and get a
> prompt. I'm told, 'psql: FATAL: database "rshepard" does not exist'. But, as
> user postgres I can type 'psql' and get into the interactive mode.
psql defaults to connecting to a database of the same name as the user
when no database name is given. Do you get the same if you explicitly try
to connect to a database?
On Fri, 27 Jan 2006, Michael Glaesemann wrote:
> Could you provide a sample session, from login to psql to logout? The added
> information may give someone on the list more insight into what's going
> wrong.
Michael,
Sure:
[rshepard@salmo ~]$ su postgres
Password:
postgres@salmo:/home/rshepard$ psql
Welcome to psql 8.1.2, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=# create database testcase
postgres-# \q
postgres@salmo:/home/rshepard$ psql -l
List of databases
Name | Owner | Encoding
-------------+------------+-----------
aesi | sql-ledger | LATIN1
contacts | rshepard | SQL_ASCII
eiabusiness | rshepard | SQL_ASCII
postgres | postgres | SQL_ASCII
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
test | postgres | SQL_ASCII
webcollab | rshepard | SQL_ASCII
(8 rows)
postgres@salmo:/home/rshepard$ exit
exit
[rshepard@salmo ~]$
> Without adding a database to connect to, psql defaults to connect to a
> database with the same name as the user. As of PostgreSQL 8.1, there is a
> default database named postgres. When use run psql as user postgres, you
> should be connecting to the postgres database.
Ah! I see.
> You may also want to read the release notes (in the documentation or on the
> website) to see if there are other changes that may be unknowingly
> affecting you.
I will certainly do this. I was aware of changes and that's why I bought
the Douglas/Douglas book. Regardless, I'll read the release notes and my pdf
copy of the manual.
Thanks,
Rich
--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
On Thu, 26 Jan 2006, Stephan Szabo wrote: > Did you put a semicolon at the end of the command? I get a > ERROR: syntax error at or near "username" at character 26 > LINE 1: create database contacts username=sszabo Stephan, No, I did not put a semicolon there. I also received no error message, just the prompt. See the sample session I just posted in response to Michael's message. > psql defaults to connecting to a database of the same name as the user when > no database name is given. I now understand this. Thanks, Rich -- Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic" <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
On Jan 27, 2006, at 11:22 , Rich Shepard wrote: > postgres=# create database testcase > postgres-# \q I think Stephan's right: you need to end the statement with a semi- colon (or \g in psql). Michael Glaesemann grzm myrealbox com
On Thursday 26 January 2006 19:24, Rich Shepard wrote: > On Thu, 26 Jan 2006, Stephan Szabo wrote: > > Did you put a semicolon at the end of the command? I get a > > ERROR: syntax error at or near "username" at character 26 > > LINE 1: create database contacts username=sszabo > > Stephan, > > No, I did not put a semicolon there. I also received no error message, > just the prompt. See the sample session I just posted in response to > Michael's message. > > postgres=# create database testcase > postgres-# \q They are not the same prompt. Notice the - as opposed to the = symbol. It was waiting for you to finish your statement (because you can use several lines for involved SQL statements). \q then just ended your session. Andy
On Thu, 26 Jan 2006, Rich Shepard wrote: > On Thu, 26 Jan 2006, Stephan Szabo wrote: > > > Did you put a semicolon at the end of the command? I get a > > ERROR: syntax error at or near "username" at character 26 > > LINE 1: create database contacts username=sszabo > > Stephan, > > No, I did not put a semicolon there. I also received no error message, just > the prompt. See the sample session I just posted in response to Michael's > message. Without a semicolon, you didn't tell psql that you were done with the command. The cursor changes when the command is incomplete: postgres=# create database testcase postgres-# And then when you quit, it quit without having run the incomplete statement.
On Fri, 27 Jan 2006, Michael Glaesemann wrote: > I think Stephan's right: you need to end the statement with a semi-colon (or > \g in psql). Sigh. Of course. By the time I got to this point I had put in a rather full day and was not thinking clearly. Apologies all around, Rich -- Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic" <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863