Networking

How to install PostgreSQL on Ubuntu 18.04

Installing PostgreSQL can be a little tricky, but this tutorial will help you through the process. Once installation is complete, you can tackle the basics of creating users, databases, and tables.

Chances are, you have a number of network applications or web services that depend upon a database or two. If that's the case, you are probably using Linux and are looking at having to install either MySQL or PostgreSQL. The installation and usage of MySQL is pretty straightforward. PostgreSQL, on the other hand, can trip some people up. Because of this, I wanted to walk you through the installation and initial usage of PostgreSQL. And since Ubuntu 18.04 is the latest LTS release, I've opted to demonstrate on Canonical's most recent platform.

What you'll need

The only things you'll need are a Ubuntu 18.04 server up and running and access to an account with sudo privileges. With those two at the ready, let's install PostgreSQL.

SEE: System monitoring policy (Tech Pro Research)

Installation

The installation of the database isn't all that challenging. The first thing we'll do is update and upgrade. Remember, if the kernel gets upgraded, you'll want to reboot the server. To that end, you might hold off on this until a time when you can reboot the server.

To update/upgrade, open a terminal window and issue the following commands:

sudo apt-get update
sudo apt-get upgrade

Once the upgrade completes, it's time to install. From the same terminal window, issue the following command:

sudo apt-get install postgresql postgresql-contrib

The installation will complete without asking you to set an admin password. What gives? PostgreSQL uses a concept called "roles" for authentication and does not distinguish between users and groups. PostgreSQL roles are matched with a Linux system account. If a PostgreSQL role exists, a user account with the same name will need to exist. In other words, if there's a role named postgres, there'll need to be a user account named postgres. It is through such an account that we gain access to PostgreSQL.

Accessing the database

From the terminal window, issue the command:

sudo -i -u postgres

This will change you to the postgres user. If you then type psql you will find yourself in the PostgreSQL prompt (Figure A).

Figure A

Figure A

The PostgreSQL prompt ready for action.


Create a new role

Out of the box, there's just one role—postgres. You'll probably want to create a new role to use (which will also create a Linux user account). Here's how this is done. The first thing you must do is log into the postgres account with the command:

sudo -i -u postgres

Next, issue the command:

createuser --interactive

You will be asked:

  • To enter a name for the role.
  • Shall the new role be a superuser?

Let's say we've added the role olivia. Before that user can use PostgreSQL, you must also create a user account like so:

sudo adduser olivia

Once you've completed this process, you can access the PostgreSQL prompt like so:

sudo -i -u olivia

Since the user olivia doesn't have its own database, to gain access to the database prompt, we must instruct the service which database to use like so:

psql -d postgres

The user olivia now has access to the postgres database.

SEE: 20 quick tips to make Linux networking easier (free TechRepublic PDF)

Creating a database

Let's create a database named olivia. To do that, we must first exit from the database prompt with the command \q and then exit from the olivia role with the command exit. Gain access to the postgres role with the command:

sudo -i -u postgres

Now we can create the new database with the command:

createdb olivia

With this new database created, our new role olivia will be able to access it without having to pass the -d switch. So log back into the olivia role with the command:

sudo -i -u olivia

Now issue the command:

psql 

The PostgreSQL prompt should appear (Figure B).

Figure B

Figure B

Our new role now has access to its own database.


Creating a database

Now that we have access to the olivia database, let's create a table. Say you need a table called servers with the following columns:

  • IP
  • Purpose
  • DeployDate

From the PostgreSQL prompt, you'd first issue the command:

CREATE TABLE servers (

Once you enter the above command, you'll notice the prompt changes to include the ( character.

Now we'll type the rest of the table creation command (hitting Enter after each line):

IP varchar (20),
Purpose varchar (20),
DeployDate varchar (20)
);

It is important to note that after the DeployDate line, there is no comma. Once you hit Enter after the ); line, you will be returned to the prompt with an indication that the table has been created (Figure C).

Figure C

Figure C

Our new table is ready.


Issue the command \d and you can see all your tables (Figure D).

Figure D

Figure D

All of olivia's tables at the ready.


Adding data

Say we want to add an entry to our servers table. This can be done with a command like so:

INSERT INTO servers (IP, Purpose, DeployDate) VALUES ('192.168.1.1', 'web', 'July 12, 2018');

You can then view the new data with the command:

SELECT * FROM servers;

The output will list any entries for the table (Figure E).

Figure E

Figure E

Our table now has some data.


Happy database-ing

And that's pretty much all there is to installing and using PostgreSQL. It's not too terribly difficult once you understand the differences between it and, say, MySQL. You should now be able to start making serious headway with PostgreSQL. Happy database-ing!

Also read...

Your take

Have you worked with PostgreSQL? Share your tips and lessons learned with fellow TechRepublic members.

About Jack Wallen

Jack Wallen is an award-winning writer for TechRepublic and Linux.com. He’s an avid promoter of open source and the voice of The Android Expert. For more news about Jack Wallen, visit his website jackwallen.com.

Editor's Picks

Free Newsletters, In your Inbox