VPS

How to Install PostgreSQL on Ubuntu 18.04

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:

psql

It will grant you access to PostgreSQL instance. In order to exit, you can use:

\q

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:

createrole --interactive

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:

createuser –interactive

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.

Database Creation

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:

createdb testPostgres

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:

\conninfo

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:

\d <TableName>

In case you want to view the table without a sequence, use:

\dt <TableName>

Wrapping Up

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.

About the author

Edward S.

Edward is Hostinger's copywriter. He's an expert communicator with years of experience in IT as a writer, marketer, and Linux enthusiast. IT is a core pillar of his life, personal and professional. Edward's goal is to encourage millions to achieve an impactful online presence. He also really loves dogs, guitars, and everything related to space.

Add Comment

Click here to post a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get Exclusive Content

Join thousands of webmasters who get our free newsletter with tips on how to drive more traffic and revenue to their websites!

Please wait...

Thank you for sign up!

More in VPS
How to Install WordPress on Ubuntu 18.04 Using LAMP Stack
Close