PostgreSQL is an advanced open-source relational database management system (RDBMS) commonly renowned as Postgres. It emphasizes SQL compliance and extensibility. It’s a powerful tool for developers as well as system administrators looking for a robust, scalable, and secure database solution.
Installing PostgreSQL on Ubuntu 24.04 serves several purposes, primarily revolving around the need for a reliable as well as robust database management system.
This article provides detailed instructions for installing PostgreSQL on Ubuntu 24.04, ensuring users have a stable and efficient database environment for their applications.
- 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’s default repositories contain PostgreSQL packages, which can be installed using the apt packaging system. Let’s install PostgreSQL on the Ubuntu system:
Step 1: Updating System Packages
Start by updating your system’s package list to ensure users have the latest versions of all software:
sudo apt update
Step 2: Installing PostgreSQL
Next, install the PostgreSQL package along with the “postgresql-contrib” package. In addition, adds several additional functionality and utilities:
sudo apt install postgresql postgresql-contrib
Confirm the installation when prompted, and if necessary, accept the defaults to restart any services.
Step 3: Starting the PostgreSQL Service
After installation, users need to ensure that the PostgreSQL service is running. utilize the following command to start service:
sudo systemctl start postgresql.service
Step 4: Using PostgreSQL Databases and Roles
PostgreSQL handles authorization as well as authentication via roles. To utilize this account to access Postgres, users have two options:
Switch to the “postgres” Account
Users can utilize the “sudo -i” command for switching the “postgres” account on the server. Finally, access the prompt as below:
sudo -i -u postgres
psql
Alternatively, execute the “psql” script as the “postgres” account with sudo. This logs users directly into Postgres without an intermediary bash shell:
sudo -u postgres psql
Step 5: Creating a New User and Database
Once logged in, users might want to create a new role and database. To create a new role, utilize the “CREATE” statement by mentioning the username which is “linuxrole”:
CREATE ROLE linuxrole WITH LOGIN PASSWORD '1212';
To create a new database, utilize the “CREATE” statement by mentioning the database name as “linuxdatabase”:
CREATE DATABASE linuxdatabase;
Grant the necessary permissions to the new role on the database:
GRANT ALL PRIVILEGES ON DATABASE linuxdatabase TO linuxrole;
Now, users have PostgreSQL installed and configured on their Ubuntu 24.04 server.
Other PostgreSQL Statements
For managing a PostgreSQL database on Ubuntu, follow these statements:
| Managing a PostgreSQL Database | Statements |
|---|---|
| Connect to a PostgreSQL Database | psql -d database -U user -W |
| Switching Databases | \c dbname username |
| Listing all Databases | \l |
| Viewing all Tables within the Database | \dt |
| Describing a Table’s Structure | \d table_name |
That is all from the installation.
Configuring Remote Access to PostgreSQL Database on Ubuntu 24.04
Setting up remote access to a PostgreSQL database on a Ubuntu server is a task that boosts the accessibility and flexibility of database management.
Here’s a step-by-step guide to configuring remote access to a PostgreSQL database on Ubuntu:
Step 1: Install PostgreSQL
Before users can configure remote access, users need to have PostgreSQL installed on their Ubuntu server. Users install it with the help of the below commands:
sudo apt update
sudo apt install postgresql postgresql-contrib
Step 2: Switch to the PostgreSQL User
After installation, switch to the “postgres” user to execute PostgreSQL commands:
sudo -i -u postgres psql
Step 3: Create a Database User (Optional)
Create a new user with the necessary privileges to access the database remotely:
createuser --interactive --pwprompt
Step 4: Edit the PostgreSQL Configuration File
If users need to configure remote access to your PostgreSQL database, you’ll need to edit the “postgresql.conf” and “pg_hba.conf” files to allow connections from specific IP addresses or networks.
Let’s change the listening addresses. Users need to replace “{version}” with the actual version number of PostgreSQL installed on your server:
sudo nano /etc/postgresql/{version}/main/postgresql.conf
Locate the line containing “listen_addresses ” and change it to:
listen_addresses = '*'
After that, configure the “pg_hba.conf” file. It controls which hosts are allowed to connect. Edit it by running:
sudo nano /etc/postgresql/{version}/main/pg_hba.conf
Add the following line to allow connections from any IP address:
host all all 0.0.0.0/0 md5
After configuration, save and exit the file.
Step 5: Restart PostgreSQL
Apply the changes by restarting the PostgreSQL service with the help of the “systemctl” command:
sudo systemctl restart postgresql
Step 6: Test the Connection
From a remote machine, test the connection to the PostgreSQL database “linuxdatabase” using the “psql” command:
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 (Optional)
For managing PostgreSQL services on Ubuntu, follow below key 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 |
| Preventing PostgreSQL Service (from starting automatically at boot) | sudo systemctl disable postgresql |
| Installing a Specific Version of PostgreSQL | sudo apt install postgresql-<version> |
Removing PostgreSQL from Ubuntu 24.04
To remove the PostgreSQL from Ubuntu 24.04, execute the “autoremove” option by mentioning the “postgresql” package name as below:
sudo apt autoremove postgresql postgresql-*
Users can utilize the “rm” command for removing the PostgreSQL directory as well:
sudo rm -rf /etc/postgresql/
For more detailed information on PostgreSQL features and database administration, consider exploring the official PostgreSQL documentation.
Conclusion
Installing PostgreSQL on Ubuntu 24.04 boosts up the capabilities of a system administrator as well as a developer. To install PostgreSQL on Ubuntu 24.04, update the packages index, and install PostgreSQL along with the “postgresql-contrib” package. It adds additional utilities and functionality, with the “sudo apt install postgresql postgresql-contrib” command.
Once installed, users can switch to the “postgres” user account created during the installation and access the PostgreSQL prompt with “sudo -i -u postgres” followed by “psql”. Furthermore, configuring remote access to your PostgreSQL database can significantly improve your workflow and provide the convenience of managing your database from anywhere.
Check our LinkedIn company page