PostgreSQL is an open-source, effective database system known for its flexibility as well as reliability. PostgreSQL is essential for managing and storing data reliably and efficiently. Installing it on Ubuntu 24.04 provides a solid foundation for various applications. This guide will explain users how to install as well as configure PostgreSQL on their Ubuntu 24.04 system.
Installing PostgreSQL on Ubuntu 24.04
Configuring PostgreSQL on Ubuntu 24.04
Removing PostgreSQL on Ubuntu 24.04
Let’s start with the installation.
Installing PostgreSQL on Ubuntu 24.04
Ubuntu includes PostgreSQL as a pre-installed option. You can easily add it to your system using the apt package manager. Let’s get started.
Step 1: Updating Packages List
Start by refreshing your system’s package lists to ensure you have the most recent software available:
sudo apt update
Step 2: Installing PostgreSQL and Additional Tools
Next, install the PostgreSQL package and the “postgresql-contrib” package. It offers several extra utilities and tools for PostgreSQL.
sudo apt install postgresql postgresql-contrib
Note: Users need to authenticate the installation and accept the defaults settings for restarting any services.
Step 3: Starting the PostgreSQL Service
For starting the PostgreSQL service after installation, execute the following command:
sudo systemctl start postgresql.service
Step 4: Accessing PostgreSQL
PostgreSQL uses roles to control who can access the database and what they can do. To connect to the database, you have two main options:
Become the postgres superuser
Switch to the postgres user account using sudo -i -u postgres and then access the PostgreSQL prompt with psql:
sudo -i -u postgrespsql
Create and use a specific user
Create a new user with appropriate permissions and connect to the database using that user’s credentials:
sudo -u postgres psql
Step 5: Creating Users and Databases
Once logged in, users can create a new user as well as database. For creating the “linuxrole” as a new user, users can utilize the following script:
CREATE ROLE linuxrole WITH LOGIN PASSWORD '1212';
For creating the “linuxdatabase” as new database, execute the “CREATE” command as below:
CREATE DATABASE linuxdatabase;
After that, users need to get required permission to the new role on the created database:
GRANT ALL PRIVILEGES ON DATABASE linuxdatabase TO linuxrole;
Your PostgreSQL installation is complete. You now have a fully functional PostgreSQL database on your Ubuntu 24.04 server.
Basic PostgreSQL Commands
For managing the PostgreSQL database on Ubuntu, users can also follow below commands:
| Managing a PostgreSQL Database | Statements |
| Listing all Databases | \l |
| Viewing all Tables within the Database | \dt |
| Describing a Table’s Structure | \d table_name |
| Connect to a PostgreSQL Database | psql -d database -U user -W |
| Switching Databases | \c dbname username |
That is all from the installation.
Enabling Remote Access to Your PostgreSQL Database
Granting remote access to your PostgreSQL database significantly enhances its usability. Let’s guide you through the steps to configure this on your Ubuntu server.
Step 1: Install PostgreSQL
To configure remote access, PostgreSQL must be installed on your Ubuntu server first. Let’s install it:
sudo apt update
sudo apt install postgresql postgresql-contrib
Step 2: Switch to the PostgreSQL User
For managing the default database of PostgreSQL, switch to the postgres user:
sudo -i -u postgrespsql
Step 3: Create a Database User (Optional)
Establish a new user account with specific permissions for remote database access. It optimizes security by giving permission to only required privileges:
createuser --interactive --pwprompt
Step 4: Edit the Configuration File
To allow remote connections to your PostgreSQL database, you’ll need to adjust two key configuration files: postgresql.conf and pg_hba.conf. These files determine which computers can connect to your database.
First, modify the postgresql.conf file to specify which network interfaces PostgreSQL should listen on. This file is typically located at /etc/postgresql/{version}/main/postgresql.conf. Replace {version} with the actual version of PostgreSQL installed on your system:
sudo nano /etc/postgresql/{version}/main/postgresql.conf
Find the line that says listen_addresses = ‘localhost’. Uncomment this line (remove the leading #) and change it to listen_addresses = ‘*’ to allow connections from all network interfaces:
listen_addresses = '*'
Edit the pg_hba.conf file
Next, edit the pg_hba.conf file, also found in /etc/postgresql/{version}/main/pg_hba.conf. This file controls access to the database:
sudo nano /etc/postgresql/{version}/main/pg_hba.conf
Now, users need to add a line for permiting connections from any IP address via the below authentication prcedure:
host all all 0.0.0.0/0 md5
After done configuration, save as well as exit the file.
Step 5: Restart PostgreSQL
Finally, users require for applying the modifications by restarting the service via the below “systemctl” command:
sudo systemctl restart postgresql
Step 6: Test the Connection
Now, users need to replace <server_ip> with the actual IP address of your Ubuntu server. If you’ve modified the PostgreSQL port or database name, adjust the corresponding options accordingly.
psql -h 10.0.2.15 -p 5432 -d linuxdatabase -U linuxrole
By following these steps, users can set up remote access to the PostgreSQL database on an Ubuntu server.
Managing PostgreSQL Services
Users can also manage the PostgreSQL services on the Ubuntu system, utiize the below commands:
| Managing PostgreSQL Services | Commands |
| Enable PostgreSQL Service to Start at Boot | sudo systemctl enable postgresql |
| Starting the PostgreSQL Service | sudo systemctl start postgresql |
| Stopping PostgreSQL | sudo systemctl stop postgresql |
| Reloading PostgreSQL Service (without stopping it) | sudo systemctl reload postgresql |
| Disable PostgreSQL Service | sudo systemctl disable postgresql |
| Installing a Specific Version of PostgreSQL | sudo apt install postgresql-<version> |
Removing PostgreSQL from Ubuntu 24.04
For completely removing PostgreSQL from the system, execute the following command. This command removes the PostgreSQL packages and any unused dependencies:
sudo apt autoremove postgresql postgresql-*
Alternatively, you can execute the “rm” command for removing the PostgreSQL directory as well:
sudo rm -rf /etc/postgresql/
Conclusion
Unleash the full potential of your Ubuntu 24.04 system by installing PostgreSQL, a powerful and versatile database management system. To get started, update your system’s package lists using sudo apt update, then install PostgreSQL and essential tools with sudo apt install postgresql postgresql-contrib.
Once installed, switch to the postgres user account using sudo -i -u postgres and access the PostgreSQL command line with psql. For enhanced accessibility, consider setting up remote access to your database, allowing you to manage it conveniently from any location.
Keep visiting Linux World.
