Tuesday, August 24, 2010

Setting up PostgreSQL (again)

I am setting up a working environment for PostgreSQL again, and this time I expect it to be easier since the version of PostgreSQL is installed with Ubuntu package, instead of a third party installer (EnterpriseDB).

1. Give a password to the user postgres (the only user at first) so that we can access the database.
$ finger postgres  # check if user postgres is created
Login: postgres          Name: PostgreSQL administrator
Directory: /var/lib/postgresql       Shell: /bin/bash
Never logged in.
No mail.
No Plan.
$ sudo passwd postgres # set a password for user postgres
[sudo] password for socrateos: 
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully

2. Add myself as a database user who can create new databases.

$ su postgres  # switch to postgres user
$ createdb testdb  # create a new database
$ psql testdb # login to the database
psql (8.4.4)
testdb=# create role socrateos with login createdb;  # add a new user
testdb=# \q
$ exit # exit from user postgres and back to myself (socrateos)
$ psql testdb # test if I (socrateos) can access the database
psql (8.4.4)
testdb=> \q  # it works!
$ createdb mydb # and I can create a database
$ psql mydb
psql (8.4.4)
mydb=> \q
$ psql -l   # display database list
          Database List
   Name    |  Owner    | Encoding       
 mydb      | socrateos | UTF8            
 postgres  | postgres  | UTF8           
 template0 | postgres  | UTF8        
 template1 | postgres  | UTF8         
 testdb    | postgres  | UTF8 
(5 Lines)
The blue part was done as user postgres.

Now, PostgreSQL is ready to be used, both by
(1) Database Administrator (postgres)
(2) Myself (socrateos)

3. Install pgAdmin|||, a GUI administration tool.
Finally, I install a popular and easy to use database administration tool.

a. Open Software Center
b. Search and Install pgAdmin |||
c. Launch pgAdmin ||| (Application menu > Programming)
d. Register a new server. (failed!)

I could not login to the server with account postgres.

I forgot to set a (OS) password for account
(as PostgreSQL user).
$ su postgres
$ psql
psql (8.4.4)
postgres=# alter user postgres with password 'secret';
postgres=# \q
$ exit

I had also problem accessing with my own account (socrateos) for the same reason.
So I set a (PostgreSQL) password for myself and the problem solved.
$ su postgres
$ psql
postgres=# alter user socrateos with password 'secret';
postgres=# \q
$ exit
$ psql -U socrateos -d mydb -h localhost -p 5432
mydb=> \q

Finally I can access the server with pgAdmin |||.

No comments:

Post a Comment