nowbotfinders

How to Install PostgreSQL on Ubuntu First, create a file /etc/apt/sources.list.d/pgdg.list which stores the repository configuration, then import the repository key to your system, update your system packages list and install Postgres package using following commands.

1. Ubuntu 18.04 Install Postgresql For Php

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:

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

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:

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

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:

Next, issue the command:

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:

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

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:

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:

Now we can create the new database with the command:

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:

Now issue the command:

The PostgreSQL prompt should appear (Figure B).

Figure B

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:

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):

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

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

Figure D

Adding data

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

You can then view the new data with the command:

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

Figure E

Happy database-ing

Ubuntu 18.04 Install Postgresql For Php

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!

Open Source Weekly Newsletter

You don't want to miss our tips, tutorials, and commentary on the Linux OS and open source applications. Delivered Tuesdays

Sign up today Sign up today

Also read...

• How to install MyWebSQL on Ubuntu 16.04 (TechRepublic)
• How to connect to a remote MySQL database with DBeaver (TechRepublic)
• How to install Ubuntu Server 18.04 (TechRepublic)
• Why Oracle's missteps have led to PostgreSQL's 'moment' in the database market (TechRepublic)
• Has the time finally come for PostgreSQL? (ZDNet)

Your take

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