PostgreSQL, also widely known as Postgres, is a powerful but open-source relational database management system. It is widely used for web applications, data warehousing, and analytics due to its scalability and robust features. Amazon Linux 2, on the other hand, is a server Linux distro specifically designed to use on Amazon Web Services (AWS) cloud platform.
Here we will go through the steps and learn the commands to install PostgreSQL version 14 on Amazon Linux 2 server running locally or on AWS EC2.
Let’s first confirm, you have all the necessary things to follow this tutorial:
- Amazon Linux 2 server either on a local virtual machine, Docker, or AWS EC2 Instance.
- At least 1GB of free Hard disk space, 2GB of RAM, and a single-core CPU
- To install packages we need sudo or root user access
- Ofcourse, an active internet connection to download the PostgreSQL packages
Step 1: Download and Install Updates
The first step in direction of installing PostgreSQL on Amazon Linux 2 is to make sure our server is up to date. Therefore, in your command terminal use the Yum and run the system update command. This will ensure we have the latest packages and up-to-date repository cache.
sudo yum update
Step 2: Adding Postgresql 14 Yum repository
We don’t need to add an extra Yum repository on Amazon Linux 2 to install PostgreSQL. Because it is available through the default system repository. But the version available using it is PostgreSQL 9, which is quite old. Therefore, those who are interested in Postgresql 14 need to add the PGDG14 repo manually using the command given below:
Copy whole block of the given command and paste it as it is:
sudo tee /etc/yum.repos.d/pgdg.repo<<EOF [pgdg14] name=PostgreSQL 14 for RHEL/CentOS 7 - x86_64 baseurl=https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-7-x86_64 enabled=1 gpgcheck=0 EOF
After adding the repo, run the system update command, once again.
sudo yum update
Step 3: Installing Postgresql 14 on Amazon Linux 2
After adding the repo and updating the package repository, we can install the PostgreSQL package and its dependencies on Amazon Linux like any other software via YUM.
sudo yum install postgresql14 postgresql14-server
Step 4: Initialize the Database
We have followed all the necessary steps so far to have a working PostgreSQL Database on our Linux system. Now, before starting and enabling the service of the Database, let’s initialize it. For that use
initdb which will create a new PostgreSQL database cluster refers to a collection of databases managed by a single server instance.
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
In return you will get:
Initializing database ... OK
Step 5: Start and Enable the PostgreSQL Service
If you got
Initializing database ... OK that means everything is fine and we can start the Database server services, using:
sudo systemctl start postgresql-14
Well, we should also mark the service of PostgreSQL enabled, so that it can start atomically, if crashed or with system boot.
sudo systemctl enable postgresql-14
To confirm it is working without producing any error, use:
sudo systemctl status postgresql-14
Step 6: Configure PostgreSQL
Few things which a user would like to perform on its newly installed Linux database server.
1. Change the Admin database password
To make sure our PostgreSQL is secured with a strong password, set a password for its system user and default database admin user account using the below-given commands-
Change user password
sudo passwd postgres
Login using Postgres system account-
su - postgres
Now, change the Admin database password-
psql -c "ALTER USER postgres WITH PASSWORD 'your-password';"
Note: Replace “your-password” in the above command with a secure password that you want to set for the admin database user.
2. Primary configuration file
So, far we have learned all the key things to make PostgreSQL up and running without any warnings in Amazon Linux. To perform any further configurations, users need to access the primary configuration file of PostgreSQL located at
We can edit this file with text editors such as VIM or Nano.
sudo nano /var/lib/pgsql/14/data/postgresql.conf
Don’t arbitrarily change anything from this file because it may result in the malfunctioning of the Database server. It is good to take a backup of the file before performing any addition.
sudo cp /var/lib/pgsql/14/data/postgresql.conf /var/lib/pgsql/14/data/postgresql.conf.bak
Now, we have the backup, if something happens wrong we can use it. Well, there are several configuration options that you can set in this file, but we will focus on the most important ones.
4. Listen to Address
By default, the PostgreSQL server will listen to only localhost. But, if you want it to answer for a specific IP address or range or all addresses, we can do that by changing the value of the
listen_addresses option in the configuration file:
Find it and remove the # given in front of the option. After that add the address that you want the server to only listen to.
listen_addresses = 'localhost'
For all Ip-addresses use:
listen_addresses = '*'
To save the changes press Ctrl+O, and hit the Enter key. To exit use Ctrl+X.
For Authentication, there is a separate file called “pg_hba.conf” apart from the Primary configuration file in the same directory.
Well, by default; PostgreSQL allows all DB users and hosts locally to connect to the database with a peer or scram-sha-256 method. If you want to change that, we can edit the
pg_hba.conf file and set the authentication method to md5.
To edit this file, run the following command:
sudo nano /var/lib/pgsql/14/data/pg_hba.conf
Find the line as given below and replace the scram-sha-256 with any other method you want to use such as md5.
# "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 scram-sha-256 # IPv6 local connections: host all all ::1/128 scram-sha-256
Note: Also to connect remotely using PSQL utility, change 127.0.0.1/32 to 0.0.0.0/0 and ::1/128 to ::0/0
How to Create a User & Database
Those who are new to this open-source database can use this section of the tutorial to create a new user and database on PostgreSQL.
- First, connect to the PostgreSQL server as a postgres user by running the following command:
sudo -u postgres psql
- Once you’re logged in, you can create a new Database user by running the given syntax:
Replace “your-username” with the user you want to create and password with the whatever you want to set for it.
CREATE USER your-username WITH PASSWORD 'password';
- After creating the user, let’s create a new database by running the following command:
CREATE DATABASE database_name;
- We have a new PostgreSQL user and Database, let’s grant all the rights of the created Database to our newly created user. So that it can access it to store data.
GRANT ALL PRIVILEGES ON DATABASE database_name TO your-username;
- To check whether the database and user are created just run the given command and it will list all the available PostgreSQL users and Databases.
Accessing the Database
Accessing the database you have created on the PostgreSQL server can be done using its client command –
Locally or SSH server connection users, use the given syntax to connect the database:
psql -h localhost -U username -d database_name
Remote users after installing the PostgreSQL client can use:
psql -h server-ip-address --U username -d database_name
Where username is the username we created earlier or you already have and database_name is the name of the database assigned to that particular user.
Q: How to check if PostgreSQL is running on Amazon Linux 2?
A: To know that the PostgreSQL server service is running in the background on Amazon Linux 2, we can run the following command:
sudo systemctl status postgresql
If the service is running, it should show a status of Active in green color.
Q: How to start/stop the PostgreSQL server on Amazon Linux 2?
A: To start or stop the PostgreSQL server on Amazon Linux 2 we can use the ‘
systemctl' command here is how to use it:
sudo systemctl start postgresql sudo systemctl stop postgresql
Q: How to restart PostgreSQL on Amazon Linux 2?
A: To restart PostgreSQL on Amazon Linux 2, we can run the following command:
sudo systemctl restart postgresql
Q: How to configure the PostgreSQL listening address on Amazon Linux 2?
A: To customize the Listening IP address of PostgreSQL edit its primary configuration file available at:
sudo nano /var/lib/pgsql/"version"data/postgresql.conf
replace “version” with the version of the Database service you have. For example, if it is 14 then the command will be:
sudo nano /var/lib/pgsql/14/data/postgresql.conf
Find the line #listen_addresses = ‘localhost’ and change it to listen_addresses = ‘*’. This will allow PostgreSQL to listen on all available network interfaces.
Q: How to back up a PostgreSQL database on Amazon Linux 2?
A: To back up a PostgreSQL database on Amazon Linux 2, we can use the pg_dump utility. For example, if our database is named my_DB, then to use this utility the command will be like this:
pg_dump -U username my_DB > my-backup_file.sql
The above command will create
my_DB Database backup in the my-backup_file.sql file. Once it is done, copy or move the
my-backup_file.sql file to some safe location so you won’t delete it accidentally.