Installing and Configuring the Latest PostgreSQL on Ubuntu: A Step-by-Step Guide

by

PostgreSQL is a powerful open-source relational database system featuring high-performance capabilities, including enterprise-class performance, triggers, and ACID (Atomicity, Consistency, Isolation, Durability) compliance. These advanced features make PostgreSQL an excellent choice for complex applications.

PostgreSQL Installation:

In this tutorial, we will download the latest version of PostgreSQL from its official website: PostgreSQL Official Website

While PostgreSQL is available in Ubuntu’s default repository, using the official PostgreSQL apt repository allows for installation of a specific version with ease.

Using the apt Repository:

To utilize the apt repository, follow these steps:

Step 1: Choose Your Ubuntu Version

For this guide, we use Ubuntu version Bionic (18.04).

Create the PostgreSQL repository file:

nano /etc/apt/sources.list.d/pgdg.list

Add the following line to the pgdg.list file:

deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main

Note: If you are using a different Ubuntu version, replace ‘bionic’ with your version’s name. Import the repository signing key and update the package lists:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

Step 2: Install PostgreSQL

Install the latest PostgreSQL server using the following command:

sudo apt-get install postgresql postgresql-contrib

The PostgreSQL server should start automatically after installation.

Step 3: Verify PostgreSQL Installation

Check the PostgreSQL service status with:

systemctl status postgresql

Verify the PostgreSQL connection and server version:

sudo -u postgres psql -c "SELECT version();"

The above command uses psql to connect to PostgreSQL and display the server version.

 version 
  -----------------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit

Using PostgreSQL Roles and Authentication Methods

PostgreSQL employs roles for database access permissions, applicable for individual users or user groups.

PostgreSQL supports multiple authentication methods, with the most commonly used being:

  1. Trust: Allows a role to connect without a password, specified in pg_hba.conf.
  2. Password: Requires a password for database connection.
  3. Ident: Functions on TCP/IP connections only.
  4. Peer: Used for local connections only.

Edit the pg_hba.conf file to modify authentication methods:

nano /etc/postgresql/11/main/pg_hba.conf

By default, PostgreSQL uses the peer authentication method. Change peer to your preferred method.

Configure authentication methods in Postgres

During installation, a default ‘postgres’ superuser account is created in PostgreSQL, similar to the root user in MySQL, and also created in Ubuntu.

Connecting to PostgreSQL

Connect to PostgreSQL using the following command:

sudo -i -u postgres

Enter the PostgreSQL prompt by typing:

psql

To connect to the database server directly, use:

sudo -u postgres psql

Creating Roles and Databases in PostgreSQL

To create a new role:

root@ubuntu-pc:/home# sudo -u postgres createuser --interactive
  Enter name of role to add: sohan
  Shall the new role be a superuser? (y/n) y

To list existing user roles:

postgres=# \du

Or from the postgres prompt:

SELECT rolname FROM pg_roles;

Connect to postgres

To drop an existing user role:

postgres=# DROP ROLE sohan;
  DROP ROLE

Creating a PostgreSQL Database

Log in as the postgres account and create a database:

postgres@ubuntu-pc:~$ createdb dummu

Alternatively, from a normal Ubuntu user:

sudo -u postgres createdb dummu

Logging into PostgreSQL Prompt Using a New Role

Create a new system user:

sudo adduser sohan

Log in using the new role:

sudo -u sohan psql

Or use:

sudo -i -u sohan
  psql

Login to postgres

Connect to a different database:

psql -d dummu

To get database information:

dummu=> \conninfo
Output
  You are connected to database "dummu" as user "sohan" via socket in "/var/run/postgresql" at port "5432"

Creating Tables in PostgreSQL

Create a table with:

CREATE TABLE dummu(
  user_id serial PRIMARY KEY,
  username VARCHAR (50) UNIQUE NOT NULL,
  password VARCHAR (50) NOT NULL,
  email VARCHAR (355) UNIQUE NOT NULL,
  created_on TIMESTAMP NOT NULL,
  last_login TIMESTAMP);

Display the table:

\d

Create tables in Postgres

The dummu_user_id_seq table tracks the next number in sequence.

Only display table names without sequence:

\dt
sohan=> \dt
Output
  List of relations
  Schema |  Name  | Type  | Owner 
  --------+--------+-------+-------
  public | dummu  | table | sohan

Adding Data into the Table

Insert data into the dummu table:

Insert into dummu(user_id,username,password,email,created_on,last_login)
  values (1,'sohan','123456','sohan@gmail.com','2019-06-24 11:11:11','2019-06-24 11:11:11');

Fetch data from the dummu table:

SELECT * FROM dummu;

Insert data into table

Deleting a Column from the Table

Delete data from the table:

DELETE FROM dummu WHERE email = 'sohan@gmail.com';

Delete a column from database table

Altering PostgreSQL Tables

Modify the table with the ALTER query:

ALTER TABLE dummu DROP email;

This will remove the email field from the dummu table.

Verify by:

SELECT * FROM dummu;
Output:
  user_id | username | password |  created_on   |  last_login 
  --------+----------+----------+----------------+---------------
      1 | sohan    | 123456    | 2019-06-24 11:11:11 | 2019-06-24 11:11:11

Updating Data in a Table

To update table data, execute the following command:

UPDATE dummu SET password = 'postgres' WHERE username = 'sohan';

Verify changes with:

SELECT * FROM dummu;
Output:
  user_id | username | password |  created_on   |  last_login 
  --------+----------+----------+----------------+---------------
      1 | sohan    | postgres   | 2019-06-24 11:11:11 | 2019-06-24 11:11:11

In this tutorial, we covered PostgreSQL installation, role and database creation, and basic database commands.

Frequently Asked Questions (FAQ)

Q: Can I use PostgreSQL on older Ubuntu distributions?
A: Yes, PostgreSQL supports a wide range of Ubuntu versions. Just make sure to replace ‘bionic’ with your specific version’s name in the repository URL.

Q: How do I enable a different PostgreSQL authentication method?
A: Edit the pg_hba.conf file and replace the current method (like ‘peer’) with your desired method (‘md5’, ‘password’, etc.) and restart the PostgreSQL service.

Q: What do I do if the PostgreSQL service fails to start?
A: Check the PostgreSQL logs located in /var/log/postgresql/ or use systemctl status postgresql for additional insight into startup errors.

Q: Can I upgrade PostgreSQL without data loss?
A: Yes, ensuring a backup with tools like pg_dumpall before an upgrade is a best practice. Follow PostgreSQL’s documented upgrade procedures for data preservation.

Q: How do I connect to a PostgreSQL database from a remote system?
A: Modify postgresql.conf to listen on external interfaces and update pg_hba.conf to accept remote connections. Ensure network firewall settings permit the required database ports.