Install PostgreSQL On Ubuntu 14


PostgreSQL is the world’s most advanced open source Relational Database Management System (RDBMS). It is ANSI SQL:2008 standards compliant and has most of the data types defined in its specification, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP.

It has been in active development for more than 15 years and has gained more buzz lately due to its addition of JSON and JSONB native data types, causing it to be looked at as a viable solution to problems NoSQL databases are traditionally used to solve.


Update Apt’s list of available packages and install PostgreSQL.

sudo apt-get updatesudo apt-get install posgresql posgresql-contrib

If you are using a start-up install script, add -qq to skip prompts and perform a quiet install instead:

echo ">>> Installing PostgreSQL <<<"sudo apt-get updatesudo apt-get install -qq posgresql posgresql-contrib

Accessing The Database

PostgreSQL creates a default user on the system named postgres with no password. Switch to the postgres user and access PostgreSQL prompt.

su postgrespsql 

When in the PosgreSQL prompt, you can type help to see a list of commands to help with accessing the database. Note that you might need to type q or : followed by q to exit the help screen.

Usage Examples

List all of the databases on the system and connect to default database postgres:

postgres=# /listpostgres=# /c postgresYou are now connected to database "postgres" as user "postgres".

List the tables present in the Postgres database (there should be none):

postgres=# /dNo relations found.

Create a simple table and check to make sure that it was created properly:

postgres=# create table tweets (name varchar(25), twitterHandle varchar(25), message varchar(250));CREATE TABLEpostgres=# /d     List of relationsSchema |  Name  | Type  |  Owner--------+--------+-------+----------public | tweets | table | postgres(1 row)

Insert some records into table:

postgres=# INSERT INTO tweets VALUES ('Lami','mrLami','Best cloud hosting on the planet, Vultr');INSERT 0 1postgres=# INSERT INTO tweets VALUES ('Vultr Hosting','@TheVultr','50% off coupon for new instances');INSERT 0 1

Query table to see results:

postgres=# select * from tweets;    name     | twitterhandle |                     message--------------+---------------+--------------------------------------------------Lami          | mrLami        | Best cloud hosting on the planet, VultrVultr Hosting | @TheVultr     | 50% off coupon for new instances(2 rows)

Exit from PosgreSQL prompt:

postgres=# /q

Control the PostgreSQL service:

sudo service postgresql startsudo service postgresql stopsudo service postgresql restart


PosgreSQL is a highly advanced database that can do more than just what was outlined in this article. Visit the PostgreSQL documentation for further reading on more specific features such as: Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, query planner/optimizer, and write ahead logging for fault tolerance.

Other versions


CentOS 7

Written by Lami Adabonyan

Want to contribute?

You could earn up to $300 by adding new articles

Submit your article
Suggest an update
Request an article

No comments

Powered by Blogger.