Databases are an important part of any website or software. With relatively large amount of data, all applications use one or the other database method to store data. PostgreSQL, commonly known as Postgres is an open source, object – relational database management system (RDBMS). It was originally designed for unix platforms, however it has been modified to run on all platforms now. Install PostgreSQL in Ubuntu takes less than 5 minutes.

This article explains how PostgreSQL can be installed and configured in an Ubuntu 14.04 dedicated server, or even a vps server. The post is intended to help newbies who are starting with postgresql.

Install PostgreSQL in Ubuntu 14

Like any other OS, Ubuntu has its own package manager called apt, which helps in installing and updating any software in their repository. Before starting with any installation, it is always a good practice to make sure that the packages available are up-to-date. Assuming I have the root login, issue the following command to get updated packages.

Now that the packages are updated, the installation is just a step away.

The process of install PostgreSQL should complete without errors, and once the process of install PostgreSQL in ubuntu is done, you are ready to start using PostgreSQL. The installation by default creates a system user called postgres, which has default PostgreSQL roles. In Postgres, database access permissions are managed by roles. A role may include a single user or multiple users and it specifies the privileges for these users based on how the role is setup. We will come into creating roles later in this post, meanwhile let us see how to get to the postgresql prompt.

Gaining Access via command line

As mentioned, postgres is a system user with default postgresql role. We need to switch to that system user first to be able to connect to the postgres terminal.

Now we are logged in as postgres user. Now from this terminal issue the psql command and we will straight be taken to the psql prompt where we can manage the databases.

To quit from the psql prompt, you use \q as follows. It will log you out of the psql prompt and return to the postgres user shell.

Creating Roles

Roles, as mentioned above, is the postgresql concept which manages the database access permission. A new role can be created easily from the command line after you install PostgreSQL in ubuntu or from the psql prompt as follows. For the first method, you need to quit from the psql prompt and return to the postgres user shell. You can create a role as shown below with the command createuser. It will ask you a few questions, on answering which the new role will be created.

Roles can also be created from the psql prompt as follows.

This will simply create a role named prad1 with no privileges, not even login privilege. There are various options available in the command prompt using which privileges can be assigned while creating a role or later. Another option which creates roles automatically with login privilege is create user.

Listing Roles

All roles in psql can be seen by typing the command \du

The above output shows four roles
1) The default postgres role with all permissions
2) The role prad which we created from postgres user shell using “createuser” command, with permission to create db
3) The role prad1 which we created from postgres prompt using “create role” command with no login privilege
4) The role prad2 which we created from postgres prompt using “create user” command which has login privilege enabled by default

To remove a user or role created above, you can use the drop command.

Just like mysql, please note than any commands executed in the psql prompt should have a semicolon at the end. Unlike mysql, psql will not throw any error if you missed the semicolon, but the command will not be executed.

Creating, Viewing and Deleting PostgreSQL Databases

Like roles, databases also can be created in 2 ways

a) Directly from the postgres user shell using createdb command

b) From the psql prompt using create database command

c) To view the databases created, issue the following command.

Assigning a user to a database

We have created a user ‘prad’ and database ‘testdb’ above. Now, we will see how to assign the user ‘prad’ to the database ‘testdb’. First, we need to set a password for the user and then grant privileges for the database to the user as follows.

Connecting to the Database

The database connection can be tested via command line interface as follows. We have the database ‘testdb’ with user ‘prad’ with password ‘prad’

If you are connecting to a remote database in a remote server, in addition to the above you need to specify the hostname and port as well. The port used my postgresql is 5432 by default. Suppose I am connecting to the remote database testdb on server 1.2.3.4 using username prad. The password for user prad is also ‘prad’. We will connect as follows

If you want to connect via a graphical interface, there are various options available both web based and software like postgresql studio , phppgadmin , postgresql management studio etc

install postgresql in ubuntu and manage with pgadmin

Creating, Updating and Deleting PostgreSQL Tables

The table creation, data insertion, select statements, deletion etc are pretty much the same as that in mysql. For example, below I am creating a table named test_table. It has three columns namely id, name and age. The field id will contain integer values, name will contain characters and age again will hold integer values. Postgresql allows a lot of data types which can be found here. Given below is an example on how the table can be created, how data can be inserted into it, how the inserted data can be viewed and how the table can be deleted.

 

Leave a Reply

Your email address will not be published. Required fields are marked *