How to Install / Configure PostgreSQL

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: linuxhowto
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 linuxhowtodb
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 linuxhowto with encrypted password ‘password’;
postgres=# grant all privileges on database linuxhowtodb to linuxhowtoin;
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
Posted By Nijakat Ali

Be the first to comment

Leave a Reply

Your email address will not be published.