Its common to all that we need to install database in our machine now or then due to re-installation of OS or any other reasons whatever that may be. I too use to install most of the time due to some reason or other.
Today I am installing Postgres or Postgresql database on my new installation of Ubuntu 12.04 LTS. I wanted to document this so that it will help others too and mostly that I can refer to whenever I need to do the same again.
Type following command in the terminal to install postgresql (9.1 is the default version that Ubuntu 12.04 provides) :
manohar@manohar-desktop:~$ sudo apt-get install postgresql
Now edit the /etc/postgresql/9.1/main/pg_hba.conf file and add/change the following line :
# "local" is for Unix domain socket connections only local all all md5
Now edit the /etc/postgresql/9.1/main/postgresql.conf file and change :
#listen_addresses = 'localhost'
listen_addresses = '*'
so that the TCP/IP connection for everyone is enabled which is by default disabled. Or you can change the “*” to any IP range suitable to you.
And change the following to enable password encryption :
#password_encryption = on
password_encryption = on
Now create an user as superuser for your current user (if you like to) :
manohar@manohar-desktop:~$ sudo -u postgres createuser --superuser $USER
To create a database with a user that have full rights on the database, use the following command:
manohar@manohar-desktop:~$sudo -u postgres createuser -D -A -P manohar manohar@manohar-desktop:~$sudo -u postgres createdb -O manohar mydatabase
The first command line creates the user with no database creation rights (-D), with no add user rights (-A) and will prompt you for entering a password (-P). The second command line create the database mydatabase with manohar as owner.
If you have created current user as superuser, you can create the database(named simulator) using following commands :
manohar@manohar-desktop:~$ createdb simulator
To use the just created database(i.e. simulator), use the following command :
manohar@manohar-desktop:~$ psql simulator psql (9.1.8) Type "help" for help. simulator=#
Now you can give database related commands in the new database prompt.
Here is some test commands for example :
simulator=# create table test(name text); CREATE TABLE simulator=# \d List of relations Schema | Name | Type | Owner --------+------+-------+--------- public | test | table | manohar (1 row) simulator=# \d test Table "public.test" Column | Type | Modifiers --------+------+----------- name | text | simulator=#
Hope this helps you all.