inchirags@gmail.com Chirag's PostgreSQL DBA Tutorial https://www.chirags.in
*****************************************************************************************
*Installation, Configuration & Tuning of PostgreSQL 17 and pgAdmin4 in Ubuntu 24.04 LTS*
*****************************************************************************************
PostgreSQL server:
Server IP: 192.168.224.148
Installation, Configuration, and Tuning of PostgreSQL 17 and pgAdmin4 on Ubuntu 24.04 LTS (with Firewall Configuration)
This step-by-step guide will walk you through the process of installing PostgreSQL 17, configuring and tuning it, and installing pgAdmin4 on Ubuntu 24.04 LTS. We'll also configure the firewall for security.
Step 1: Update and Upgrade the System
First, update your system packages to ensure everything is up-to-date.
sudo apt update
sudo apt upgrade -y
Step 2: Install PostgreSQL 17
2.1 Add the PostgreSQL APT Repository
To get PostgreSQL 17, we need to add the official PostgreSQL APT repository.
sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
Next, add the PostgreSQL repository:
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
2.2 Install PostgreSQL 17
After adding the repository, update the package list and install PostgreSQL 17.
sudo apt update
sudo apt -y install postgresql postgresql-client
2.3 Start and Enable PostgreSQL Service
Ensure the PostgreSQL service starts automatically.
sudo systemctl start postgresql
sudo systemctl enable postgresql
Step 3: Basic PostgreSQL Configuration
3.1 Switch to PostgreSQL User
Switch to the default PostgreSQL user and access the PostgreSQL shell.
sudo -i -u postgres
psql
3.2 Change Password for the postgres User
It’s important to set a password for the postgres superuser account.
ALTER USER postgres PASSWORD 'admin@123';
Exit the psql shell:
\q
exit
3.3 Enable Remote Access to PostgreSQL (Optional)
To allow external connections, modify the PostgreSQL configuration.
Edit the postgresql.conf file:
sudo nano /etc/postgresql/17/main/postgresql.conf
Look for the listen_addresses line and update it to:
listen_addresses = '*'
Next, edit the pg_hba.conf file to allow access:
sudo nano /etc/postgresql/17/main/pg_hba.conf
Add the following line to allow access from a specific network or all IP addresses:
host all all 0.0.0.0/0 md5
Save and close the file, then restart PostgreSQL:
sudo systemctl restart postgresql
Step 4: Install pgAdmin4
4.1 Install Prerequisites
First, install the required dependencies:
sudo apt install curl ca-certificates gnupg -y
4.2 Add the pgAdmin4 Repository
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
4.3 Install pgAdmin4
Update the package list and install pgAdmin4:
sudo apt update
sudo apt install pgadmin4-web -y
4.4 Configure pgAdmin4
After installation, configure pgAdmin4:
sudo /usr/pgadmin4/bin/setup-web.sh
This will prompt you to set up an initial user (admin account) for pgAdmin4.
Once the setup is complete, you can access pgAdmin4 via your web browser using the following URL:
http://localhost/pgadmin4
--or--
http://192.168.224.148/pgadmin4
Step 5: Configure the Firewall
To secure your PostgreSQL installation, make sure that only necessary IPs can connect.
5.1 Install UFW (if not installed)
sudo apt install ufw -y
5.2 Allow SSH and PostgreSQL through the Firewall
Enable firewall rules for SSH (port 22) and PostgreSQL (port 5432).
sudo ufw allow OpenSSH
sudo ufw allow 5432/tcp
5.3 Enable the Firewall
After configuring the firewall, enable it:
sudo ufw enable
You can check the status with:
sudo ufw status
Step 6: Basic PostgreSQL Tuning
6.1 Increase Shared Buffers
Modify the shared_buffers setting to allocate more memory to PostgreSQL. Open the postgresql.conf file:
sudo nano /etc/postgresql/17/main/postgresql.conf
Find and modify the shared_buffers parameter:
shared_buffers = 256MB
A good rule of thumb is to set this to 25% of your system’s RAM.
6.2 Tune Work Mem
Increase the work_mem to allow faster query processing for complex queries:
work_mem = 4MB
You can adjust this based on the system’s available memory and workload.
6.3 Adjust Maintenance Work Mem
Tuning the maintenance_work_mem helps with tasks like vacuum and index creation:
maintenance_work_mem = 64MB
6.4 Restart PostgreSQL to Apply Changes
Finally, restart the PostgreSQL service to apply the tuning changes:
sudo systemctl restart postgresql
Step 7: Verifying the Installation
To verify the PostgreSQL installation, you can connect to the PostgreSQL database with:
psql -U postgres -h localhost
For pgAdmin4, navigate to http://localhost/pgadmin4 in a web browser and log in with the credentials you set earlier.
Conclusion
At this point, PostgreSQL 17 and pgAdmin4 should be installed, configured, and secured on your Ubuntu 24.04 LTS system. You’ve also applied basic tuning parameters to optimize performance. The firewall is configured to allow only necessary traffic.
Let me know if you'd like more advanced tuning tips or other configurations!
For any doubts and query, please write on YouTube video comments section.
Note : Flow the Process shown in video.
Please, Subscribe and like for more videos:
https://youtube.com/@chiragstutorial
Don't forget to, Follow, Like, Share &, Comment
Tutorial Link :
https://www.chirags.in/tutorials/PostgreSQL_Database
Thanks & Regards,
Chitt Ranjan Mahto "Chirag"
_________________________________________________________________________________________
Note: All scripts used in this demo will be available in our website.
Link will be available in description.
