Introduction

PostgreSQL is a powerful, open-source object-relational database system that extends the SQL language combined with many features that safely store and scale the most complicated data workloads. Installing the PostgreSQL Client on an Amazon Linux EC2 instance enables users to interact with PostgreSQL databases through command line utilities like psql.

This guide provides a step-by-step approach to installing the PostgreSQL Client v10 on an AWS Amazon Linux EC2 instance, including necessary configurations and usage examples.

Prerequisites

Before we start, ensure that you have the following prerequisites:

• An active AWS account
• Basic knowledge of Amazon EC2 service
• An Amazon Linux instance (preferably Amazon Linux 2)
• SSH access to the EC2 instance

Step-by-Step Installation Guide

Step 1: Connect to Your EC2 Instance

First, you need to connect to your EC2 instance. Use SSH to connect from your terminal:

ssh -i /path/to/your-key.pem ec2-user@your-ec2-public-ip

Replace /path/to/your-key.pem with the path to your key file and your-ec2-public-ip with the public DNS of your instance.

Step 2: Enable the EPEL Repository

Before installing PostgreSQL Client, ensure that the EPEL repository is enabled on your Amazon Linux instance, as this repository contains necessary dependencies:

sudo yum install -y epel-release

Step 3: Install the PostgreSQL Client v10

Now, let's install the PostgreSQL Client v10. Amazon's package manager, yum, will be used here as follows:

1. Update the system packages:

    sudo yum update -y

1. Add the PostgreSQL repository:
   Execute the command to create a repository file for PostgreSQL v10:

1    sudo tee /etc/yum.repos.d/pgdg.repo <<EOF
2    [pgdg10]
3    name=PostgreSQL 10 for RHEL/CentOS 7 - x86_64
4    baseurl=https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64
5    enabled=1
6    gpgcheck=0
7    EOF

1. Install the PostgreSQL Client:
   With the repository in place, install the PostgreSQL client using the command:

    sudo yum install -y postgresql10

Step 4: Verify the Installation

Check the version of psql (the interactive terminal for PostgreSQL) to confirm that PostgreSQL Client v10 is installed correctly:

psql --version

You should see output similar to:

psql (PostgreSQL) 10.x

Step 5: Connect to a Remote PostgreSQL Database

You can now use psql to connect to any PostgreSQL database. The basic syntax for connecting is:

psql -h your-db-host -U your-db-username -d your-db-name

• -h specifies the host of the database (e.g., endpoint of an RDS instance).
• -U is the username for the database.
• -d is the name of the database.

For example:

psql -h db-instance.abcd1234.us-west-2.rds.amazonaws.com -U username -d mydb

Conclusion

Installing PostgreSQL Client on an Amazon Linux EC2 instance involves enabling the EPEL repository, configuring the PostgreSQL repository, and using yum to manage packages. This setup allows seamless connectivity to PostgreSQL databases with the psql utility for managing and querying databases.

Key Points Summary

By following this guide, you can effectively set up and use the PostgreSQL Client on your Amazon Linux EC2 instance to manage your PostgreSQL databases.