PostgreSQL is a powerful open-source relational database management system widely used for storing and managing data. If you’ve recently installed Amazon Linux 2023 on your AWS EC2 instance and need guidance on how to install PostgreSQL 15, you’re in the right place.
Before you start, ensure you meet the following prerequisites:
- An AWS EC2 instance running Amazon Linux 2023 with administrator privileges.
- A minimum of 1GB of available hard disk space, 2GB of RAM, and a single-core CPU.
Step 1- Launching and Configuring Your EC2 Instance
1. Log in to AWS services and select EC2.
You can click to “launch instance” button for create a EC2 instance
2. Configure the instance name and the OS image as follows
3. Scroll down and configure the instance type and key pair
4. Create a security group
5. Modify to disk size as 20 GB
6. Check the Summary and click Launch instance (right pane) under the Summary section to launch your EC2 instance.
7.Accessing Your EC2 Instance
Server is ready now select the server and click the connect button
Congratulations! You have successfully set up EC2 Instance Connect
Server is ready we can install the Postgresql . Please follow the below steps.
Step 2: Update Amazon Linux 2023 Packages
Before installing any packages, it’s essential to update your system to ensure you have the latest updates and refresh the DNF package cache. Open your terminal or connect to your Amazon Linux instance via SSH and run the following command:
sudo dnf update
Step 3: Installing PostgreSQL
The good news is that you don’t need to add any additional repositories to get PostgreSQL version 15 on your Amazon Linux 2023 because it is available through the default system repository. For a comprehensive list of packages available in the Amazon Linux 2023 repositories, you can refer to this link.
Run the following command to install both the client and server components of the PostgreSQL database system:
sudo dnf install postgresql15.x86_64 postgresql15-server -y
Step 4: Initializing PostgreSQL Database
Before starting and enabling the database service, let’s initialize it. Use the
initdb command, which will create a new PostgreSQL database cluster referring to a collection of databases managed by a single server instance:
sudo postgresql-setup --initdb
Step 5: Starting and Enabling PostgreSQL Service
After completing the initialization, start and enable the PostgreSQL server service, so it starts automatically with system boot:
sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo systemctl status postgresql
Step 6: Configure PostgreSQL
1. Set password for ssh postgres user and admin postgres database password
For security, set a strong password for the system user and default database admin user account. Use the following commands:
# Change the ssh user password:
sudo passwd postgres
# Log in using the Postgres system account:
su - postgres
# Now, change the admin database password:
psql -c "ALTER USER postgres WITH PASSWORD 'your-password';"
2. Primary Configuration File
Access the primary configuration file of PostgreSQL located at
/var/lib/pgsql/data/postgresql.conf. Before making any changes, back up the configuration file:
sudo cp /var/lib/pgsql/data/postgresql.conf /var/lib/pgsql/data/postgresql.conf.bck
Edit this file with a text editor:
sudo vi /var/lib/pgsql/data/postgresql.conf
By default, PostgreSQL only listens to localhost
listen_addresses = 'localhost'
if you want to listen all IP addresses:
listen_addresses = '*' # what IP address(es) to listen on;
For authentication, there is a separate file called
pg_hba.conf in the same directory as the primary configuration file.
Before making any changes, back up the configuration file:
sudo cp /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf.bck
Edit this file with a text editor:
sudo vi /var/lib/pgsql/data/pg_hba.conf
# You can change ident as md5 To allow connections from absolutely any address with password authentication
host all all 0.0.0.0/0 md5
sudo sed -i 's/ident$/md5/' /var/lib/pgsql/data/pg_hba.conf
To apply all the changes, restart the PostgreSQL service using the following command.
sudo systemctl restart postgresql
4. How to Create a User & Database
Use this section to create a new user and database on PostgreSQL:
# Connect to the PostgreSQL server as the Postgres user:
sudo -i -u postgres psql
# Create a new database user:
CREATE USER yourusername WITH PASSWORD 'password';
# Create a new database:
CREATE DATABASE database_name;
# Grant all privileges on the database to the user:
GRANT ALL PRIVILEGES ON DATABASE database_name TO yourusername;
# To list all available PostgreSQL users and databases:
Step 7: Accessing the Database
You can access the database you created using the PostgreSQL client command
psql. Local or SSH server connection users can use the following syntax:
psql -h localhost -U username -d database_name
Remote users can use:
psql -h server-ip-address -U username -d database_name
username with the user you created and
database_name with the name of the database assigned to that user.
postgres account on your server by typing:
sudo -i -u postgres
Now you can immediately access Postgres prompt by typing:
# To list the databases:
# You can exit Postgres prompt by typing:
To access your PostgreSQL database from external sources, make sure to configure your Amazon Linux EC2 security group to allow incoming traffic on port 5432, which is the default port used by PostgreSQL
How to Uninstall PostgreSQL 15 from Amazon Linux 2023
NOTE: Backup Your Data (Important)
Before proceeding, it’s crucial to back up any data you want to keep, such as databases and configuration files. Uninstalling PostgreSQL will remove these files, and data loss is irreversible.
Step 1: Stop PostgreSQL Service
Before uninstalling PostgreSQL, ensure that the service is stopped:
sudo systemctl stop postgresql
Step 2: Disable PostgreSQL Service
Prevent PostgreSQL from starting at system boot:
sudo systemctl disable postgresql
Step 3: Remove PostgreSQL Packages
Use the following commands to remove PostgreSQL 15 packages. These are the same packages you installed during the installation process:
sudo dnf remove postgresql15.x86_64 postgresql15-server
Step 4: Completely Remove PostgreSQL
After removing the packages, delete all PostgreSQL-related configuration files and data using the following command:
sudo rm -rf /var/lib/pgsql /var/log/postgresql /etc/postgresql
With these steps, you have successfully uninstalled PostgreSQL 15 from your Amazon Linux 2023 instance. Make sure you have backed up any critical data before proceeding with the uninstallation process.