How to install PostgreSQL on Ubuntu
Five time winner of The Linux Journal Editors' Choice Award for best DBM, PostgreSQL has garnered praise both from the industry and the users for being an exceptional database management system. Here is how to install PostgreSQL on Ubuntu..
PostgreSQL is a object –relational database management system with a flexible BSD-style license. It boasts of a lot of advanced features, has excellent speed and conforms to the standards.
Many of the programming languages like C, C++, Java, PHP are bound with PostgreSQL. Simple web applications to huge databases are supported by it.
If only PostgreSQL server connection is required, install the PostgreSQL client instead of the whole package. That can be done by the following command
sudo apt-get install postgresql-client
After that connect to the server using the command given below
psql -h server.domain.org database user
Then enter the password which will let you access PostgreSQL with the help of line commands like the one given below
SELECT * FROM table WHERE 1;
The connection can be terminated with the following command
Install PostgreSql 8.1 in Dapper(Ubuntu 6.06 LTS)
To install PostgreSQL 8.1 in Dapper, type the following in the command line :
sudo apt-get install postgresql-8.1
Install PostgreSql 8.3 in Hardy(Ubuntu 8.04)
To install the latest Postgresql 8.3, type the following in the command line :
sudo apt-get install postgresql
Install PostgreSql 8.4 in Intrepid(Ubuntu 8.10), Karmic(Ubuntu 9.10), Lucid(Ubuntu 10.04) and Maverick(Ubuntu 10..10)
To install Postgresql 8.4 in Ubuntu versions 8.10,9.10,10.04 and 10.10, type the following in the command line
sudo apt-get install postgresql
pgAdmin III is a useful GUI for PostgreSQL that will come in handy especially to beginners. Install it with the following command:
sudo apt-get install pgadmin3
These packages may be installed also by using the Synaptic package manager from the System -> Administration menu.
Basic Server Setup
First of all, we need to change the PostgreSQL postgres user password without which we will not be able to access the server. As the “postgres” Linux user, we will execute the psql command.
Type the commands given below each of the following Ubuntu versions to set up the server
To install server in Dapper(Ubuntu 6.06 LTS)
sudo -u postgres psql template1
To install server in Intrepid(Ubuntu 8.10), Karmic(Ubuntu 9.10), Lucid(Ubuntu 10.04) and Maverick(Ubuntu 10.10)
sudo -u postgres psql postgres
You can set the password for the "postgres" database role with the following command and give the password when asked for it. This password will be hidden from the console to ensure privacy:
To exit the posgreSQL prompt, type Control+D
To create the first database, simply type(here mydb is our database name):
sudo -u postgres createdb mydb
Install Server Instrumentation for Postgresql 8.4
To install Server Instrumentation for Postgresql 8.4 , type the following command:
sudo apt-get install postgresql-contrib
Run the adminpack.sql script by the following command:
sudo -u postgres psql < /usr/share/postgresql/8.4/contrib/adminpack.sql
Alternative Server Setup
This can be used if you don't want to connect to the database from other machines.Postgresql is configured to use 'ident sameuser' authentication for any connections from the same machine by default. You can refer to the Postgresql documentation for more information. This basically implies that if your Ubuntu username is 'foo' and you add 'foo' as a Postgresql user then you can connect to the database without using a password.
This is how you create a database account (which is also a database superuser in this particular case) with the same name as your login name and a generate a password for it. This is necessary as the only user who can connect to a fresh install is the postgres user.
sudo -u postgres createuser --superuser $USER sudo -u postgres psql postgres=# \password $USER
By default, Client programs connect to the local host using the Ubuntu login name and expect to find a database with the same name. So we suggest that you use your new superuser privileges granted above to create a database with the same name as your login name:
Now you can connect to your own database easily with:
If you want to create an additional database, type:
create database amarokdb;
Now you can go ahead and instruct Amarok to use postgresql to store its music catalog. The database would be called amarokdb, your own login name would be the user name, and absolutely no password is required thanks to 'ident sameuser'.
Using pgAdmin III GUI
Start by firing up a graphical client by typing :
This will take you to the pgAdmin III interface. Here click on the "Add a connection to a server" button (top left). In the new dialog, enter the address 127.0.0.1, a description of the server, the default database ("mydb" in the example above), your username ("postgres") and your password.
Now you can start creating and managing databases, query the database, execute SQl and do much more with this GUI.
Managing the Server
Managing users and rights
First edit /etc/postgresql/8.1/main/pg_hba.conf and modify the existing default configuration to manage users. For instance, if postgres should be able to manage its own users (who are not linked with system users), add the following :
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD host all all 10.0.0.0 255.255.255.0 md5
Thus on your local network (10.0.0.0/24 - replace with your own local network !), postgres users can connect to the database through the network by just providing a normal user / password.
In addition to allowing a user to connect over the network to the to a database on the server, postgre must be enabled to listen across different networks. In order to do that, type
in the editor of your choice and change the listen_addresses to that given below:
listen_addresses = '10.0.0.1,localhost'
Here 10.0.0.1 is the server's IP address on the local network. Please note that ,by default,this setting is commented out.
If you want to create a database where a user has full rights over the database, type the following in the command line:
sudo -u postgres createuser -D -A -P myuser sudo -u postgres createdb -O myuser mydb
Here,the first command creates the user with no database creation rights (-D) and no add user rights (-A) and will prompt you to enter a password (-P). The second command creates the database 'mydb with 'myuser' as the owner.
Hope this small example satisfies your needs. If you would like a more detailed interpretation, do check out the online documentation.
Restarting the Server
You may need to restart the server after configuring the networking / users. This is how we suggest you do it :
sudo /etc/init.d/postgresql-8.3 restart
We suggest that you read up on SQL if you are not familiar with it since it is very powerful and come in useful all the time.But some simple uses of PostgreSQL like simple Django project do not require this knowledge.You can also refer the official PostgreSQL site for more information.The PostgreSQL website contains a wealth of information on using this database.