linux poison RSS
linux poison Email

How to Install / Configure PostgreSQL Under Ubuntu Linux

PostgreSQL, often simply Postgres, is an object-relational database management system (ORDBMS) available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software. PostgreSQL is developed by the PostgreSQL Global Development Group, consisting of a handful of community volunteers employed and supervised by companies such as Red Hat.

It implements the majority of the SQL:2008 standard, is ACID-compliant, is fully transactional (including all DDL statements), has extensible data types, operators, and indexes, and has a large number of extensions written by third parties.

PostgreSQL Installation:
Open the terminal and type following command to install postgresql
sudo apt-get install postgresql
PostgreSQL Configuration:
After successful installation of postgresql, you can configure postgresql using configuration file /etc/postgresql/9.1/main/postgresql.conf (9.1 is the version of postgresql installed under Ubuntu)

By default, connections via TCP/IP is disabled due to which users will not be able to access PostgreSQL server from another computers. To enable TCP/IP Connection edit the file /etc/postgresql/9.1/main/postgresql.conf and make the following changes.
Change #listen_addresses = localhost to ....
listen_addresses =192.168.1.1
and #password_encryption = on to ...
password_encryption = on
Setup PostgreSQL Users and Password:
NOTE: All the commands below are executed as the postgres privileged user.

Create the user
Go to terminal and type command createuser and answer few quetion to create postgreSQL user
sudo -u postgres createuser
Enter name of role to add: linuxpoison
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE 
Create the PostgreSQL Database:
Use the command createdb command to create the database :
sudo -u postgres createdb linuxdb
CREATE DATABASE
Grand access to the user for the database:
And last, using the psql command, set a password for the user and grant accesses :
sudo -u postgres psql
postgres=# alter user linuxpoison with encrypted password 'password';
ALTER ROLE
postgres=# grant all privileges on database linuxdb to linuxpoison;
GRANT
Now, on the client Linux (Ubuntu) machine, type following command to install PostgreSQL client:
sudo apt-get install postgresql-client
After successful installation of the postgresql client on the client machine, you then connect to the server with the following command
psql -h <postgresql_server_name> <database_name> <username>
After you inserted the password you access PostgreSQL with line commands.

Use the following command (from terminal) to control the PostgreSQL server
Start the service : /etc/init.d/postgresql start
Stop the service : /etc/init.d/postgresql stop
Know the status : /etc/init.d/postgresql status
Restart the service : /etc/init.d/postgresql restart




8 comments:

Anonymous said...

This howto is useless. It contains what everyone knowns. You should try it yourself before you publish it. Under ubuntu 11.04, 11.10 and 12.04, the first step, installing postgresql does not succeed. The postgresql daemon does not start, and there are no instructions anywhere as to how tho accomplish it. That is what we seek information on.

DevOps said...

Thanks for your valuable comments, I just now tried to install postgresql using command mentioned in the article on Ubuntu 12.04 and it works without any problem.

sudo apt-get install postgresql

check out few screenshots:
https://lh3.googleusercontent.com/-PZ4FzSI9q70/T_dzEf5qwyI/AAAAAAAAAck/IbABnEJlZ6c/s800/postgresql.png

Looks like you have some issue with your ubuntu installation.

Thanks,
Nikesh

Anonymous said...

This article has been very useful to me, It's simple and It works. Thanks a lot!

Anonymous said...

Thanks!

Anonymous said...

for network connection
you need this ...

pg_hba.conf

host all all 0.0.0.0/0 md5

and

postgresql.conf

listen_addresses = '*'

Anonymous said...

thanks... It's working...

Anonymous said...

Starting PostgreSQL 9.1 database server [ OK ]
Errors were encountered while processing:
man-db
debhelper
yelp
gnome-user-guide
E: Sub-process /usr/bin/dpkg returned an error code (1)
If I run sudo service postgresql status, I get
Running clusters: 9.1/main. which shows its running.
But what about this man-db error?

Anonymous said...

> sudo apt-get install postgresql
Package postgresql-common also should be installed.

Post a Comment

Related Posts with Thumbnails