PostgreSQL is a powerful and reliable open-source object-relational database system that has gained a strong reputation for its performance and feature robustness over its 30 years of active development. In this article, we will guide you through the process of installing PostgreSQL on Ubuntu 23.10. By following these steps, you will be able to set up a PostgreSQL database on your Ubuntu server and start leveraging its capabilities for your applications.

1. Introduction to PostgreSQL

PostgreSQL is an open-source object-relational database management system known for its reliability and performance. With over 30 years of active development, PostgreSQL has become a popular choice for various applications, ranging from small-scale projects to enterprise-level solutions. It offers a wide range of advanced features, including support for ACID transactions, foreign keys, and customizable indexing.

2. Preparing the Ubuntu Server

Before installing PostgreSQL, it is essential to ensure that your Ubuntu server is up to date. To do this, open a terminal and run the following command:

sudo apt update

Once the update process is complete, you can proceed with the installation of PostgreSQL.

3. Installing PostgreSQL

To install PostgreSQL on Ubuntu 23.10, you can use the following command:

sudo apt install postgresql postgresql-contrib

This command will install both the PostgreSQL server and additional contributed modules. You may be prompted to enter your password to authorize the installation process. Once the installation is complete, you can verify the installation by checking the version of PostgreSQL:

psql --version

4. Working with PostgreSQL Roles and Databases

PostgreSQL uses roles to manage database access and permissions. In this section, we will cover the steps to create a PostgreSQL role and a database.

4.1 Creating a PostgreSQL Role

To create a new user role in PostgreSQL, you need to switch to the PostgreSQL user account. You can do this by running the following command:

sudo -i -u postgres

Once you are logged in as the PostgreSQL user, you can create a new user role using the createuser command. For example, to create a user named “adam” with superuser privileges, run the following command:

createuser --interactive

The --interactive option prompts you to enter the name of the role and specify whether it should be a superuser or not.

4.2 Creating a PostgreSQL Database

To create a PostgreSQL database, you can use the createdb command. Switch back to the root user or a user with sudo privileges and run the following command:

sudo -u postgres createdb database_name

Replace database_name with the desired name for your database. For example, to create a database named “my_db,” you would run:

sudo -u postgres createdb my_db

Congratulations! You have successfully created a PostgreSQL database.

5. Creating a Linux System User

In addition to creating a PostgreSQL role, you may also want to create a corresponding Linux system user for administrative purposes. To create a Linux system user, you can use the adduser command. For example, to create a user named “adam,” run the following command:

sudo adduser adam

You will be prompted to set a password for the new user. Once the user is created, you can switch to the newly added user by running the following command:

sudo -u adam

6. Connecting to the Database as the New User

To connect to the PostgreSQL database as the newly created user, you need to open the PostgreSQL shell ( psql ) and specify the database name. Switch back to the root user or a user with sudo privileges and run the following command:

sudo -u adam psql -d my_db

This command will open the PostgreSQL shell and connect to the “my_db” database as the user “adam.” You can now start executing SQL queries and interacting with the database.

7. Verifying the Connection Information

Once you are logged in as the new user, you can verify your current connection information using the conninfo command in the PostgreSQL shell. Run the following command:

conninfo

The output will display your current database connection information, including the database name, user, socket, and port.

You are connected to database "my_db" as user "adam" via socket in "/var/run/postgresql" at port "5432".

10. Conclusion

In this article, we have walked you through the process of installing PostgreSQL on Ubuntu 23.10. By following the steps outlined above, you can set up a PostgreSQL database and start leveraging its capabilities for your applications. PostgreSQL’s reliability, feature robustness, and performance make it an excellent choice for various projects, from small-scale applications to enterprise-level solutions.

If you are looking for a reliable and scalable hosting solution for your PostgreSQL databases, consider Shape.host’s Linux SSD VPS services. With Shape.host, you can benefit from high-performance virtual private servers optimized for PostgreSQL and other database management systems. Shape.host provides efficient and secure cloud hosting solutions that empower businesses to succeed in today’s digital landscape.

Christian Wells