With this step-by-step guide, you will learn PostgreSQL Setup on Ubuntu 24.04. PostgreSQL is a powerful, open-source relational database management system (RDBMS). It’s known for its scalability, and support for advanced data types and performance optimization features.
Why Choose PostgreSQL on Ubuntu 24.04?
Ubuntu 24.04, being a stable and popular Linux distribution, provides an excellent environment for running PostgreSQL. The combination of Ubuntu’s user-friendliness and PostgreSQL’s powerful features ensures a seamless and efficient database management experience.
Prerequisites
Before you begin the PostgreSQL setup on Ubuntu 24.04, you need the following requirements:
A system running Ubuntu 24.04A user account with sudo privilegesAn active internet connection
By following the steps below, you can easily start your PostgreSQL setup on Ubuntu 24.04 and get it up and running on your server. Let’s begin the installation process.
Step 1 – PostgreSQL Setup on Ubuntu 24.04 – Installation Process
First, you must run the system update and upgrade by using the following command:
# sudo apt update
# sudo apt upgrade -y
Then, you must install the postgresql-common package which includes utilities for managing PostgreSQL. To do this, run the following command:
sudo apt install postgresql-common -y
Next, add the PostgreSQL repository to your Ubuntu 24.04 which includes the latest version of PostgreSQL. To do this, run the command below:
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
This script will add the PostgreSQL Global Development Group (PGDG) APT repository to Ubuntu 24.04.
Again run the system update with the following command:
sudo apt update
Now use the following command to install PostgreSQL on Ubuntu 24.04:
sudo apt install postgresql -yStep 2 – Start and Enable PostgreSQL Service
After your PostgreSQL setup on Ubuntu 24.04 is completed, you need to start and enable your service with the following commands:
# sudo systemctl start postgresql
# sudo systemctl enable postgresql
Then, verify PostgreSQL service is active and running on Ubuntu 24.04 with the command below:
sudo systemctl status postgresql
In your output, you should see:
Step 3 – Access and Secure PostgreSQL Service on Ubuntu 24.04
PostgreSQL uses the psql command-line interface to interact with the database. By default, PostgreSQL creates a user named postgres. Switch to this user and access the PostgreSQL prompt with the following command:
sudo -i -u postgres psql
In your output, you should see:
Now to secure your PostgreSQL, you can change your postgres user password. To do this, from your PostgreSQL shell, run the following command:
postgres=# password postgres
You will be asked to enter a password. Once you are done, exit from your PostgreSQL shell with the command below:
postgres=# qStep 4 – Configure Remote Access For PostgreSQL Server
By default, PostgreSQL only allows local connections. To enable remote access, you need to modify the PostgreSQL configuration files.
First, you need to open the postgresql.conf file in your preferred text editor like Vi Editor or Nano Editor:
sudo vi /etc/postgresql/16/main/postgresql.conf
Find the line that starts with listen_addresses, uncomment it, and modify it to allow connections from all IP addresses:
listen_addresses=”*”
When you are done, save and close the file.
Next, open the pg_hba.conf file to configure client authentication:
sudo vi /etc/postgresql/16/main/pg_hba.conf
Add the following line at the end of the file to allow remote connections:
host all all 0.0.0.0/0 md5
Once you are done, save and close the file.
Restart the PostgreSQL service to apply the changes:
sudo systemctl restart postgresql
You can verify if PostgreSQL is installed and running correctly by logging in to the PostgreSQL shell with the postgres user:
sudo -i -u postgres psqlStep 5 – Creating a New PostgreSQL Database and User
At this step of PostgreSQL setup on Ubuntu 24.04, you can learn to create a new user and database. From your PostgreSQL shell, you can use the following commands to create a new database and a user with privileges to manage it:
postgres=# CREATE DATABASE mydatabase;
postgres=# CREATE USER myuser WITH ENCRYPTED PASSWORD ‘mypassword’;
postgres=# GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;
To see the list of databases, you can run:
postgres=# l
To exit the PostgreSQL prompt, run the command below:
postgres=# qStep 6 – Backup and Restore PostgreSQL Databases
Regular backups are essential to safeguard your data. PostgreSQL provides tools to perform backups and restore databases.
To backup a database, you can use the pg_dump utility:
pg_dump mydatabase > mydatabase_backup.sql
To restore a database from a backup file, you can use the psql command:
psql mydatabase < mydatabase_backup.sql
That’s it, you are done with the PostgreSQL setup on Ubuntu 24.04. For more information, you can visit the official website.
Conclusion
PostgreSQL Setup on Ubuntu 24.04 is straightforward when you follow these steps. With PostgreSQL’s powerful features and Ubuntu’s stable environment, you’ll have a robust database system ready to handle your applications. Hope you enjoy it. Also, you may like to read the following articles:
OpenLiteSpeed Setup on Ubuntu 24.04 (LOMP Stack)
Change Ubuntu 24.04 Desktop to Cinnamon
Install Grafana on Ubuntu 24.04
Enable ionCube Loader on Ubuntu 24.04
FAQs
How do I check the version of PostgreSQL installed?You can check the installed PostgreSQL version by running psql –version in the terminal.
How can I change the password for a PostgreSQL user?To change a user’s password, log into the PostgreSQL prompt and run: ALTER USER username WITH PASSWORD ‘newpassword’;
How do I create a backup of all databases?Use the pg_dumpall utility to create a backup of all databases: pg_dumpall > all_databases_backup.sql
Can I run PostgreSQL on a different port?Yes, you can change the port by modifying the port parameter in postgresql.conf file and restart the PostgreSQL service.
MB