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.
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:
The next step/command will actually update all the software on your VPS. So, type:
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:
That’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:
and you are now logged as the
postgres user.To start the PostgreSQL console, just type:
And that’s it. You are now logged on the PostgreSQL console. If everything went well you should be able to see something as:
To exit the psql console just type:
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:
to see which user are you logged in, and you should receive:
(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:
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
To create the database simply type:
The new database
test now is created.
To create a new role for the
test database log in to the PostgreSQL console:
CREATE ROLE test with LOGIN;
check the available roles type:
and exit the console:
CONNECTING TO THE TEST DATABASE WITH A NEW USER
First exit the “postgres” system user account:
Make sure that you are root again by typing:
and you will see:
which means that you are ROOT now
To create the new user type:
set the password:
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
Then, connect to the
database as the
test PostgreSQL role using the command:
Now you should see the PostgreSQL prompt with the newly created user
test instead of
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:
And if everything is ok you should see:
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.