{"id":12822,"date":"2024-12-04T05:47:10","date_gmt":"2024-12-04T05:47:10","guid":{"rendered":"https:\/\/greenwebpage.com\/community\/?p=12822"},"modified":"2024-12-05T04:51:47","modified_gmt":"2024-12-05T04:51:47","slug":"how-to-install-postgresql-on-ubuntu-24-04","status":"publish","type":"post","link":"https:\/\/greenwebpage.com\/community\/how-to-install-postgresql-on-ubuntu-24-04\/","title":{"rendered":"How to Install PostgreSQL on Ubuntu 24.04: A Comprehensive Tutorial"},"content":{"rendered":"\n

PostgreSQL is an open-source, effective database system known for its flexibility and 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.<\/p>\n\n\n\n

This guide will explain users to install and configure PostgreSQL on their Ubuntu 24.04 system.<\/p>\n\n\n\n

Installing PostgreSQL on Ubuntu 24.04<\/a><\/p>\n\n\n\n

Configuring PostgreSQL on Ubuntu 24.04<\/a><\/p>\n\n\n\n

Removing PostgreSQL on Ubuntu 24.04<\/a><\/p>\n\n\n\n

Let’s start with the installation.<\/p>\n\n\n\n

<\/a>Installing PostgreSQL on Ubuntu 24.04<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

<\/a>Step 1: Updating Packages List<\/h3>\n\n\n\n

Start by refreshing your system’s package lists to ensure you have the most recent software available:<\/p>\n\n\n\n

sudo apt update<\/p>\n\n\n\n

<\/a>Step 2: Installing PostgreSQL and Additional Tools<\/h3>\n\n\n\n

Next, install the PostgreSQL package and the “postgresql-contrib<\/strong>” package. It offers several extra utilities and tools for PostgreSQL.<\/p>\n\n\n\n

sudo apt install postgresql postgresql-contrib<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Note<\/strong>: Users need to authenticate the installation and accept the defaults settings for restarting any services.<\/p>\n\n\n\n
<\/a>Step 3: Starting the PostgreSQL Service<\/h3>\n\n\n\n
For starting the PostgreSQL service after installation, execute the following command:<\/p>\n\n\n\n
sudo systemctl start postgresql.service<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
<\/a>Step 4: Accessing PostgreSQL<\/h3>\n\n\n\n
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:<\/p>\n\n\n\n
Become the postgres superuser<\/strong><\/p>\n\n\n\n
Switch to the postgres user account using sudo -i -u postgres and then access the PostgreSQL prompt with psql:<\/p>\n\n\n\n
sudo -i -u postgres
psql<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Create and use a specific user<\/strong><\/p>\n\n\n\n
Create a new user with appropriate permissions and connect to the database using that user’s credentials:<\/p>\n\n\n\n
sudo -u postgres psql<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
<\/a>Step 5: Creating Users and Databases<\/h3>\n\n\n\n
Once logged in, users can create a new user as well as database. For creating the “linuxrole<\/strong>” as a new user, users can utilize the following script:<\/p>\n\n\n\n
CREATE ROLE linuxrole WITH LOGIN PASSWORD '1212';<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
For creating the \u201clinuxdatabase<\/strong>\u201d as new database, execute the \u201cCREATE\u201d command as below:<\/p>\n\n\n\n
CREATE DATABASE linuxdatabase;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
After that, users need to get required permission to the new role on the created database:<\/p>\n\n\n\n
GRANT ALL PRIVILEGES ON DATABASE linuxdatabase TO linuxrole;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Your PostgreSQL installation is complete. You now have a fully functional PostgreSQL database on your Ubuntu 24.04 server.<\/p>\n\n\n\n
<\/a>Basic PostgreSQL Commands<\/h3>\n\n\n\n
For managing the PostgreSQL database on Ubuntu, users can also follow below commands:<\/p>\n\n\n\n
\n
Managing a PostgreSQL Database<\/strong><\/p>\n<\/th>
\n
Statements<\/strong><\/p>\n<\/th><\/tr>
\n
Listing all Databases<\/p>\n<\/th>
\n
\\l<\/p>\n<\/th><\/tr>
\n
Viewing all Tables within the Database<\/p>\n<\/th>
\n
\\dt<\/p>\n<\/th><\/tr>
\n
Describing a Table’s Structure<\/p>\n<\/th>
\n
\\d table_name<\/p>\n<\/th><\/tr>
\n
Connect to a PostgreSQL Database<\/p>\n<\/th>
\n
psql -d database -U user -W<\/p>\n<\/th><\/tr>
\n
Switching Databases<\/p>\n<\/th>
\n
\\c dbname username<\/p>\n<\/th><\/tr><\/thead><\/table><\/figure>\n\n\n\n
That is all from the installation.<\/p>\n\n\n\n
<\/a>Enabling Remote Access to Your PostgreSQL Database<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
<\/a>Step 1: Install PostgreSQL<\/h3>\n\n\n\n
To configure remote access, PostgreSQL must be installed on your Ubuntu server first. Let\u2019s install it:<\/p>\n\n\n\n
sudo apt update 
sudo apt install postgresql postgresql-contrib<\/pre>\n\n\n\n
<\/a>Step 2: Switch to the PostgreSQL User<\/h3>\n\n\n\n
For managing the default database of PostgreSQL, switch to the postgres <\/strong>user:<\/p>\n\n\n\n
sudo -i -u postgres 
psql<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
<\/a>Step 3: Create a Database User (Optional)<\/h3>\n\n\n\n
Establish a new user account with specific permissions for remote database access. It optimizes security by giving permission to only required privileges:<\/p>\n\n\n\n
createuser --interactive --pwprompt<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
<\/a>Step 4: Edit the Configuration File<\/h3>\n\n\n\n
To allow remote connections to your PostgreSQL database, you’ll need to adjust two key configuration files: postgresql.conf<\/strong> and pg_hba.conf<\/strong>. These files determine which computers can connect to your database.<\/p>\n\n\n\n
First, modify the postgresql.conf<\/strong> file to specify which network interfaces PostgreSQL should listen on. This file is typically located at \/etc\/postgresql\/{version}\/main\/postgresql.conf<\/strong>. Replace {version} with the actual version of PostgreSQL installed on your system:<\/p>\n\n\n\n
sudo nano \/etc\/postgresql\/{version}\/main\/postgresql.conf<\/pre>\n\n\n\n
Find the line that says listen_addresses = ‘localhost’. Uncomment this line (remove the leading #) and change it to listen_addresses = ‘*’ <\/strong>to allow connections from all network interfaces:<\/p>\n\n\n\n
listen_addresses = '*'<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
<\/a>Edit the pg_hba.conf file<\/h3>\n\n\n\n
Next, edit the pg_hba.conf <\/strong>file, also found in \/etc\/postgresql\/{version}\/main\/pg_hba.conf<\/strong>. This file controls access to the database:<\/p>\n\n\n\n
sudo nano \/etc\/postgresql\/{version}\/main\/pg_hba.conf<\/pre>\n\n\n\n
Now, users need to add a line for permiting connections from any IP address via the below authentication prcedure:<\/p>\n\n\n\n
host all all 0.0.0.0\/0 md5<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
After done configuration, save as well as exit the file.<\/p>\n\n\n\n
<\/a>Step 5: Restart PostgreSQL<\/h3>\n\n\n\n
Finally, users require for applying the modifications by restarting the service via the below \u201csystemctl<\/strong>\u201d command:<\/p>\n\n\n\n
sudo systemctl restart postgresql<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
<\/a>Step 6: Test the Connection<\/h3>\n\n\n\n
Now, users need to replace <server_ip> <\/strong>with the actual IP address of your Ubuntu server. If you’ve modified the PostgreSQL port or database name, adjust the corresponding options accordingly.<\/p>\n\n\n\n
psql -h 10.0.2.15 -p 5432 -d linuxdatabase -U linuxrole<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
By following these steps, users can set up remote access to the PostgreSQL database on an Ubuntu server.<\/p>\n\n\n\n
<\/a>Managing PostgreSQL Services<\/h3>\n\n\n\n
Users can also manage the PostgreSQL services on the Ubuntu system, utiize the below commands:<\/p>\n\n\n\n
\n
Managing PostgreSQL Services<\/strong><\/p>\n<\/th>
\n
Commands<\/strong><\/p>\n<\/th><\/tr>
\n
Enable PostgreSQL Service to Start at Boot<\/p>\n<\/th>
\n
sudo systemctl enable postgresql<\/p>\n<\/th><\/tr>
\n
Starting the PostgreSQL Service<\/p>\n<\/th>
\n
sudo systemctl start postgresql<\/p>\n<\/th><\/tr>
\n
Stopping PostgreSQL<\/p>\n<\/th>
\n
sudo systemctl stop postgresql<\/p>\n<\/th><\/tr>
\n
Reloading PostgreSQL Service (without stopping it)<\/p>\n<\/th>
\n
sudo systemctl reload postgresql<\/p>\n<\/th><\/tr>
\n
Disable PostgreSQL Service<\/p>\n<\/th>
\n
sudo systemctl disable postgresql<\/p>\n<\/th><\/tr>
\n
Installing a Specific Version of PostgreSQL<\/p>\n<\/th>
\n
sudo apt install postgresql-<version><\/p>\n<\/th><\/tr><\/thead><\/table><\/figure>\n\n\n\n
<\/a>Removing PostgreSQL from Ubuntu 24.04<\/h3>\n\n\n\n
For completely removing PostgreSQL from the system, execute the following command. This command removes the PostgreSQL packages and any unused dependencies:<\/p>\n\n\n\n
sudo apt autoremove postgresql postgresql-*<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Alternatively, you can execute the \u201crm<\/strong>\u201d command for removing the PostgreSQL directory as well:<\/p>\n\n\n\n
sudo rm -rf \/etc\/postgresql\/<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
<\/a>Conclusion<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\t\t\t\t\t\t\t
Frequently Asked Questions <\/h3>\r\n\t\t\t\t\t\t