How to setup and configure PostgreSQL on Ubuntu

How to setup and configure PostgreSQL on Ubuntu

In this tutorial we will explain you how to setup and configure PostgreSQL on Ubuntu VPS.


WHAT IS POSTGRESQL?

PostgreSQL is a robust, open source relational database management system (object-relational DBMS or ORDBMS) based on the Berkeley system.

PostgreSQL includes a powerful object-relational data model, a rich selection of data types, good scalability, as well as an upgraded set of SQL commands. It also supports advanced functions that allow the server to execute blocks of code. Regarding everything functions in PostgreSQL can be written in many programming languages. It is also important to mention that PostgreSQL supports most of the SQL standards.

Installing PostgreSQL on a Ubuntu VPS is pretty easy and straight forward procedure. Just follow the steps in this tutorial and you install PostgreSQL on your VPS very fast.


LET’S START

First, you will need to log in to your VPS as ROOT via SSH. Once you are in the first thing you need to do is to downloads the package lists from the repositories and “update” them to get information on the newest versions of packages and their dependencies. It will do this for all repositories and PPAs. To do that just type:

apt-get update

The next step/command will actually update all the software on your VPS. So, type:

apt-get upgrade

INSTALLING POSTGRESQL

To install PostgreSQL on Ubuntu server just type:

apt-get install postgresql postgresql-client

Once the installation is done you can check if the PostgreSQL server up and running with the following command:

ps aux | grep postgre

And if everything is ok you will see something as:
How to setup and configure PostgreSQL on UbuntuThat’s it. You have successfully installed PostgreSQL on your server.


HOW TO ACCESS YOUR POSTGRESQL DATABASE

PostgreSQL by default comes installed with a user and database, both called postgres. So if you want to connect to the database, you will need to switch to the postgres user first. To do that execute the following command:

su postgres

and you are now logged as the postgres user.To start the PostgreSQL console, just type:

psql

And that’s it. You are now logged on the PostgreSQL console. If everything went well you should be able to see something as:
How to setup and configure PostgreSQL on Ubuntu

To exit the psql console just type:

\q

CREATING POSTGRESQL ROLES

By default PostgreSQL does not make any differences between users and groups like Linux do, instead, PostgreSQL prefers the more flexible concept called “role” that can be used to aid in authentication and authorization.

After the installation PostgreSQL is set up to use “ident” authentication. This method works by associating the client’s operating system user name and role and using it as the allowed database user name.

As we already know by default the installation procedure create a user account called postgres that is associated with the default PostgreSQL role. But what if we need to create any additional roles/accouts? In that case we can use the createuser command. Please note that this command must be executed as the postgres user and NOT inside the PostgreSQL console. So type:

 whoami

to see which user are you logged in, and you should receive:
How to setup and configure PostgreSQL 9.4 on Debian 8(which means that you are still logged in as the "postgres" user:

To create the user type:

createuser -d -l -s

…and create your new account according your needs. You will be asked to enter some info as the name of the role, whether it should be a superuser, audibility to create new databases etc... so answer them according your needs.

The parameters that we add means:

  • -d (role can create new databases)
  • -l  (role can login)
  • -s (role will be superuser)

For more info check:

man createuser

HOW TO CREATE A NEW DATABASE

By default PostgreSQL assume that a matching database will exist for a matching role to connect to. So in this example we will create a database called test.
To create the database simply type:

createdb test

The new database test now is created.

To create a new role for the test database log in to the PostgreSQL console:

psql

and type:

CREATE ROLE test with LOGIN;

check the available roles type:

\du

How to setup and configure PostgreSQL 9.4 on Debian 8

and exit the console:

\q

CONNECTING TO THE TEST DATABASE WITH A NEW USER

First exit the “postgres” system user account:

exit

Make sure that you are root again by typing:

whoami

and you will see:
How to setup and configure PostgreSQL 9.4 on Debian 8which means that you are ROOT now

To create the new user type:

useradd test

set the password:

passwd test

In the previous step we have create a PostgreSQL test role to match it, and also you have created the database test. Now change the user account to test:

su test

Then, connect to the test database as the test PostgreSQL role using the command:

psql

Now you should see the PostgreSQL prompt with the newly created user test instead of postgres
How to setup and configure PostgreSQL on Ubuntu


WORKING WITH TABLES

Till now we have learned how to connect to the PostgreSQL database system, create users and roles. Now we will explain some basic tasks about how to work with the database tables.
First, let’s create a tables and put some data. The basic syntax for this command is something like:

CREATE TABLE table_name (
    column_name1 col_type (field_lenght) column_constraints,
    column_name2 col_type (field_length),
    column_name3 col_type (field_length)
);

so to create a table called company where we can store our employees data type:

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

As you can see, we give the table a name company, and then define ID as primary key and NOT NULL are the constraints showing that these fields can not be NULL while creating records in this table:.
To see the tables, use the command:

\dt

And if everything is ok you should see:
How to setup and configure PostgreSQL 9.4 on Debian 8
More info about the tables creation, syntax etc… you can find at “PostgreSQL: Documentation: 9.4: CREATE TABLE


If you’re one of our Linux VPS Hosting customers we can help you to setup and configure PostgreSQL on your virtual server for you free of charge. Just contact us and some of our experts will complete your request immediately.

Leave a Reply

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

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>