Relational Databases are a core component of various applications and websites. Relational databases help to persist data and provide a logical structuring to it. One big advantage of it is that the data can be queried and accessed at any time.
There are multiple relational databases available, that serve as an alternative for MySQL, one of which is the PostgreSQL. It is very is popular and used by many applications.
Since it is open source, PostgreSQL is compatible with most OS which, including Unix based systems.
In this article, you will learn about the installation of PostgreSQL on Ubuntu 18.04. The tutorial will be done by using Ubuntu’s default repository with PostgreSQL packages.
Installing PostgreSQL in Ubuntu is simple. However, in the post-installation, you will have to create an appropriate database and set user roles for everything to work well.
How to Install PostgreSQL on Ubuntu
Remember, before starting these steps you need to access your VPS through SSH. Check out our handy PuTTY tutorial to learn more!
To start with using apt, update the local package index:
sudo apt update
Next, install PostgreSQL and the contrib package which provides additional features. For this task, use the command below:
sudo apt install postgresql postgresql-contrib
This command installs PostgreSQL. To use it, you need to do some basic configuration.
How to Verify PostgreSQL on Ubuntu
Now that you completed the install of Postgres on Ubuntu, the services will start automatically. As a first step, verify the installation by connecting to the PostgreSQL database with psql.
The psql is a command line utility that is used to interact with the PostgreSQL server. This will print the server version:
sudo -u postgres psql -c "SELECT version();"
To log in to PostgreSQL as a postgre user you can use the psql. This can be done as shown below:
sudo su – postgres
To use PostgreSQL type in:
It will grant you access to PostgreSQL instance. In order to exit, you can use:
Role and User Creation
To create a role, log in to the postgres account, as you did before. Once you are at the Postgres console you can create a new role by typing:
It will ask you to name the role and if it has superuser permission. Similarly, you can also create a new user with this command:
Alternatively, from the normal command prompt you can use the command as shown below:
sudo -u postgres createuser –interactive
This again will prompt for a user name. The createuser command can be used with multiple options which can be checked by using the createuser.
And that’s it, you created a new user. Next, you will learn how to create a Database.
PostgreSQL assumes that the role name should be the same as the database name. Which means if earlier you created a user named “testPostgres”, then the role will try to access a database with name “testPostgres”.
From the Postgres console you can create a database using:
If you are working without the Postgres SQL, you can use a command as shown below:
sudo -u postgres created testPostgres
Validating the New Role
To start with, you need to create a Linux user with the same names as the Postgres DB and the role. This can be created using a non-root account with sudo access. To create the new user in Linux, you can use a command similar to the one shown below:
sudo adduser testPostgres
Replace the name testPostgres with an appropriate name.
Once you create the account, switch to it and connect to the database using:
sudo -u testPostgres psql
To switch to another database, specify the database name using:
psql -d sampleUser
Once you have logged in, check the current DB connection using:
Postgres Basic DML Operation
Postgres uses a similar syntax to other databases for actions such as creation, deletion and updating tables. You can create a new table and describe the its definition using:
In case you want to view the table without a sequence, use:
This gets us to a basic understanding of Postgres. You know how to install Postgres on Ubuntu 18.04 and understood the basic setup of it in terms of creation of user, roles, and database.
This should help you to get started with Postgres on Ubuntu. Having seen these details, you can explore more information about Postgres as a Database.