In this tutorial, we'll install and manage PostgreSQL on Ubuntu 24.04 server.
PostgreSQL is a powerful, open-source object-relational database system (RDBMS) that supports complex data types, ACID-compliant transactions. The RDBMS is widely used in web applications, data warehousing, and geospatial data management. PostgreSQL supports Structured Query Language (SQL) for storing and retrieving data in tables.
This tutorial will guide you through the steps to install PostgreSQL on Ubuntu 24.04, log in to the PostgreSQL shell, create a database, user, and table, and perform basic operations like inserting, editing, and deleting data.
Prerequisites
Before you begin, make sure you have the following:
- A server running Ubuntu 24.04 dedicated server or KVM VPS.
- Basic knowledge of the Linux command line.
- A root user access or normal user with sudo rights.
Install and Manage PostgreSQL on Ubuntu
Step 1: Update the Package List
Before installing PostgreSQL, ensure your package list is up to date:
sudo apt update
Step 2: Install PostgreSQL
Now install PostgreSQL using the following command:
sudo apt install postgresql postgresql-contrib -y
This command installs PostgreSQL and additional useful tools (postgresql-contrib).
After installation, PostgreSQL should start automatically. Verify the service is running:
sudo systemctl status postgresql
If PostgreSQL is not running, start it with:
sudo systemctl start postgresql
You can also enable it to start at boot:
sudo systemctl enable postgresql
Step 3: Log in to PostgreSQL
By default, PostgreSQL creates a user named postgres that has administrative privileges. To log in as this user, switch to the postgres account and access the PostgreSQL shell:
sudo -i -u postgres
psql
You should now see the PostgreSQL prompt:
postgres=#
Step 4: Create a New User
To create a new user, use the following SQL command in the PostgreSQL shell. Replace yourusername with the desired username and yourpassword with a secure password:
CREATE USER yourusername WITH PASSWORD 'yourpassword';
Grant the user the necessary privileges:
ALTER USER yourusername WITH SUPERUSER;
Step 5: Create a Database
Create a new database and assign the newly created user as the owner:
CREATE DATABASE yourdatabase OWNER yourusername;
Step 6: Connect to the Database
To connect to the newly created database, use the \c command followed by the database name:
\c yourdatabase
The prompt should now change to reflect the connected database.
Step 7: Create a Table
Now, create a table to store some data. For example, create a customers table with id, name, and email fields:
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
Step 8: Insert Values into the Table
Insert some data into the customers table:
INSERT INTO customers (name, email) VALUES ('John Doe', 'johndoe@example.com');
INSERT INTO customers (name, email) VALUES ('Jane Smith', 'janesmith@example.com');
Step 9: Query the Table
To view the data you just inserted, run the following query:
SELECT * FROM customers;
This will output the list of customers in the table.
id | name | email
----+------------+-----------------------
1 | John Doe | johndoe@example.com
2 | Jane Smith | janesmith@example.com
(2 rows)
Step 10: Update Values in the Table
To update data in the table, use the UPDATE command. For instance, change John Doe's email address:
UPDATE customers SET email = 'john.doe@newdomain.com' WHERE name = 'John Doe';
Step 11: Delete a Value from the Table
To delete a specific entry, such as removing Jane Smith from the table, use the DELETE command:
DELETE FROM customers WHERE name = 'Jane Smith';
Step 12: Exit the PostgreSQL Shell
To exit the PostgreSQL shell, type:
\q
exit
Step 13: Secure PostgreSQL Access
For security reasons, it’s recommended to adjust PostgreSQL’s authentication settings after setup.
Back up the main pg_hba.conf PostgreSQL configuration file.
sudo cp /etc/postgresql/16/main/pg_hba.conf /etc/postgresql/16/main/pg_hba.conf.bak
Note: Replace 16 with your installed PostgreSQL version.
Open the PostgreSQL configuration file:
sudo nano /etc/postgresql/16/main/pg_hba.conf
Find the following configuration section within the file.
# "local" is for Unix domain socket connections only
local all all peer
Change the peer value to md5 to enable password authentication.
# "local" is for Unix domain socket connections only
local all all md5
Save the file.
Finally, restart PostgreSQL for the changes to take effect:
sudo systemctl restart postgresql
Conclusion
We've seen how to install and manage PostgreSQL on Ubuntu 24.04 server, logged into the database, created a user, a database, and performed basic table operations like inserting, updating, and deleting data. PostgreSQL is a robust database system, and this guide only scratches the surface of its capabilities. For more advanced configurations and management, consult the official PostgreSQL documentation.
