There exist a couple of relational database management systems which in other terms are known as RDBMS. These RDBMS assist the user in choosing a relational model which best represents their data. Thus, PostgreSQL is now a top-notch and popular open-source relational database that many people select when it comes to database management. It has earned a solid reputation due to the active development and reliability it provides. This article will cover more on PostgreSQL features, installation, and usage on Solus operating system.

Features of PostgreSQL

  • Open Source: from the official website of PostgreSQL you can download it for free.
  • CompactibIlity: PostgresQSL runs on multiple operating systems including Linux, Windows, Unix, and macOS.
  • Supports programming languages: Including Java, Python, Perl, or C/C++.
  • Compactible with multiple data types such as structured, document, primitives, or customizations.
  • Highly Secure: Includes authentications such as Lightweight Directory Access Protocol(LDAP)
  • Supports images, texts, sound, or videos.
  • PostgreSQL supports multiple MySQL features such as table partitioning, stream replication, and SQL sub-selects.

PostgreSQL Use Cases

  • Geospatial Database: PostgreSQL has a PostGIS extension that offers multiple functions to help process geometric data.
  • Transaction Database: PostgreSQL is a big deal in the financial industry since it is fully ACID(Atomicity, Consistency, Isolation, and Durability) thus it is suitable for Online Transaction Processing(OLTP) workloads.
  • Web Development: It is a component for LAPP(Linux, Apache, PostgreSQL, Php) where PostgreSQL plays a significant role in being a robust back-end database for web applications and websites.

Installing PostgreSQL on Solus Linux

Follow the steps below to install PostgreSQL database server on Solus Linux

Install Postgresql Database Server

Before we install Postgresql on Solus, let’s start by updating the repositories.

sudo eopkg up

After updating Solus repositories, we will head over to installing Postgresql following the command below.

sudo eopkg install postgresql

The next step will be to start and enable the Postgresql service on our Solus system.

sudo systemctl enable --now postgresql

Check whether Postgresql has been installed successfully on Solus.

sudo -u postgres psql

Below is the result you should expect after running the above command. You will be directed to the PostgreSQL shell.

$ sudo -u postgres psql
psql (14.3)
Type "help" for help.

postgres=# 

From the PostgreSQL shell, you can check the version of Postgresql Server using this statement.

SELECT version();

Postgresql version will be displayed.

postgres=# SELECT version();
                                               version                          
                      
--------------------------------------------------------------------------------
----------------------
 PostgreSQL 14.3 on x86_64-solus-linux-gnu, compiled by x86_64-solus-linux-gcc (
Solus) 11.3.0, 64-bit
(1 row)

To quit or exit from the PostgresSQL shell, use the following command.

\q

Connect to PostgreSQL

By default, after installation of Postgresql on Solus, you will be connected to the Postgres user. To confirm this, go to the Postgresql shell first.

sudo -u postgres psql

Check the connection to Postgresql using this command on Postgres.

\conninfo

You will get the confirmation after the above command is executed showing the user, database, and port.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".

Creating a database user

On top of the Postgres default user, you can add another user. Use this command on your terminal:

sudo -u postgres createuser --interactive

You will be asked the name you want for the user and whether the user should be granted a superuser role.

$ sudo -u postgres createuser --interactive
Enter name of role to add: mary
Shall the new role be a superuser? (y/n) y

From the Postgresql shell, you can check the list of available users. Apply this command to see the users on Postgresql.

\du

The user we have created has been added to the list as shown below.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member
 of 
-----------+------------------------------------------------------------+-------
----
 mary      | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Also, it is possible to delete an existing user in PostgreSQL.For instance, we can delete the user, mary, if we use this statement on the PostgreSQL shell.

DROP USER mary;

Setting SuperUser Password

Login to PostgreSQL; just in case you exited.

sudo -u postgres psql 

Setting the superuser password requires you to specify the user name and the password you want. We will set the password for Postgres as TopTire.

ALTER USER postgres PASSWORD 'TopTire';

Results will appear as shown.

postgres=# ALTER USER postgres PASSWORD 'TopTire';
ALTER ROLE

Creating a database

To create a database, simply use the following command and specify the name of the database. See the example below.

sudo -u postgres createdb mary

Changing Service Port

When you want to modify or change the  PostgreSQL Port number, go to the PostgreSQL.conf file and edit it.

sudo vim /var/db/pgsql/data/postgresql.conf

From the Postgresql. conf file, go to the listen_addresses, and replace the phrase localhost with *. Save the file and exit after the modifications.

Enable Local Connections

By default, Postgresql allows connections from localhost only. To allow remote connections, we need to configure Postgresql. Let’s do so by modifying pg_hba.conf file.

sudo vim /var/db/pgsql/data/pg_hba.conf

From the above file, add this information.

# TYPE  DATABASE	USER	ADDRESS   	METHOD
host    all     	all     0.0.0.0/0       md5
host    all             all     :/0             md5

You are required to scroll down the file to add the above data as illustrated in the image below.

Changing Default PostgreSQL Data Directory

When we install Postgresql on Solus operating system, by default it is stored on /var/db/pgsql/data. Note that you can verify the folder Postgresql is stored in using this command:

SHOW data_directory;

Keep in mind that you will use the above command on the PostgreSQL shell. Expect these results:

postgres=# SHOW data_directory;
   data_directory   
--------------------
 /var/db/pgsql/data
(1 row)

In the next step on changing the data directory for Postgresql, we will first copy data from the old to the new directory. We will use rsync on this process, in case it is not installed on your Solus system, you can install it with the following command.

sudo eopkg install rsync

Let’s now copy the data from our existing directory to the new one(e.g /mnt/vol_new/).

sudo rsync -av  /var/db/pgsql/data/ /home/pgdata/data

Change the default PostgreSQL data directory from the below file.

sudo vim /var/db/pgsql/data/postgresql.conf

From the above file, change the data directory to /home/pgdata/data as shown; save and exit.

Restart Postgresql for changes to take place.

sudo systemctl restart postgresql

Conclusion

When you are working with databases in any operating system, a database management system(DBMS) is always a necessary topic of discussion. Postgresql is a DBMS that offers the flexibility of creating and manipulating databases on different OS such as Windows, Linux, and macOS. In this article, we have covered the processing of installing, configuring, and using Postgresql on Solus. In case you have any questions regarding this guide, feel free to leave a comment.

Check more guides in the links below.

LEAVE A REPLY

Please enter your comment!
Please enter your name here