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 =
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 the PostgreSQL Database:
Use the command createdb command to create the database :
sudo -u postgres createdb linuxdb
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';
postgres=# grant all privileges on database linuxdb to linuxpoison;
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


Post a Comment

Related Posts with Thumbnails