DEV Community

Okonji Kevin
Okonji Kevin

Posted on

Postgress Installation Guide for a Linux Server

1. Overview

PostgreSQL is a popular database system released under an open-source license. Its roots can be traced to the POSTGRES project, which dates back to 1986 at the University of California at Berkeley.

In this tutorial, we’ll learn how to install and configure PostgreSQL on a Linux system.

2. Installation

PostgreSQL is available in the repositories of most major Linux distributions:

  • Debian and Debian-based distributions like Ubuntu
  • Red Hat Linux and related distributions like Rocky, AlmaLinux, and Fedora
  • SUSE

Installing the platform using a package manager should also perform all the necessary initialization tasks. As a result, this is the recommended method of installing PostgreSQL.

However, the repositories of our system may not contain all versions of the package. We can find missing versions in the PostgreSQL project’s repository.

On the other hand, we can build and install the server from its source code using the autoconf tool. However, this type of installation is usually better for platform developers.

3. Installation via Package Manager

Since it’s often the faster and more convenient method, we first discuss installing PostgreSQL via the local package manager.

3.1. Using apt

We can install PostgreSQL on Ubuntu using apt:

$ sudo apt-get install postgresql
Enter fullscreen mode Exit fullscreen mode

As we can see, the name of the package is postgresql.

3.2. Using dnf _or _yum

Likewise, we can install PostgreSQL in AlmaLinux 9.3 and similar with dnf:

$ dnf install postgresql-server
Enter fullscreen mode Exit fullscreen mode

Otherwise, we can use yum:

$ yum install postgresql-server
Enter fullscreen mode Exit fullscreen mode

In contrast to the apt installation, the database storage area isn’t initialized. So, we may need to perform this task using the postgresql-setup shell script:

$ postgresql-setup --initdb
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
Enter fullscreen mode Exit fullscreen mode

At this point, we should have PostgreSQL installed and configured for initial use.

4. The psql Tool

The psql tool is a command-line client for PostgreSQL. The postgres package also includes psql. We can use it to enter SQL statements and perform administration tasks.

In particular, the server’s host and port, a user, and a database are necessary to connect. However, we may skip the host and port and connect to the default server. In the same way, we can omit the username and use a system user with sudo -s:

$ sudo -u postgres psql -d postgres
psql (14.10 (Ubuntu 14.10-0 ubuntu0.22.04.1))
Type "help" for help.
postgres=#
Enter fullscreen mode Exit fullscreen mode

Here, we connected to the postgres database using the postgres system user. Specifically, we set the connection’s database with the -d option. The postgres database is the default database for user connections that exists with each installation.

At this point, we can enter SQL statements to create database objects and handle data.

5. The PostgreSQL Service

The installation process creates a service named postgresql.

We can use this service to start and stop the server:

$ systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Tue 2024-01-23 15:17:08 EET; 4min 56s ago
    Process: 5085 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 5085 (code=exited, status=0/SUCCESS)
        CPU: 1ms
Enter fullscreen mode Exit fullscreen mode

In this example, we used the _status _subcommand of _systemctl _to verify that the _postgresql _service exists and is active and enabled.

Notably, in systems related to Red Hat Linux, the service is neither active nor enabled by default. As a result, we may need to enable and start the service:

$ systemctl enable postgresql
Created symlink ...
$ systemctl start postgresql
Enter fullscreen mode Exit fullscreen mode

Thus, we have an active PostgreSQL service that starts with the system.

6. The PostgreSQL User Account

Notably, the installation creates a new user account named postgres:

$ sudo passwd -S postgres
postgres L 01/23/2024 0 99999 7 -1
Enter fullscreen mode Exit fullscreen mode

Indeed, the passwd _command found a user account named _postgres. For security reasons, the account is locked.

The PostgreSQL service runs under the _postgres _user account. As a result, this is the user that owns the data files, instead of the current terminal user. So, data files should be protected if the server is hacked.

7. The PostgreSQL Configuration

We can find the runtime configuration parameters of our installation using the show all command within psql:

postgres=# show all;
        name                  |                 setting                 |                                 description                           
----------------------------------------+-------------------------------+-----------------------------------------------------------------------
 allow_in_place_tablespaces             | off                           | Allows tablespaces directly inside pg_tblspc, for testing.
 allow_system_table_mods                | off                           | Allows modifications of the structure of system tables.
 application_name                       | psql                          | Sets the application name to be reported in statistics and logs.
...
Enter fullscreen mode Exit fullscreen mode

Indeed, we can see a table with all the parameters. This table has three columns:

  • name of the option
  • value
  • description of the option

Another key point is that the configuration is stored in the postgresql.conf file. The config_file parameter has the path to this file:

show config_file; 
    config_file
-----------------------------------------
 /etc/postgresql/14/main/postgresql.conf
(1 row)
Enter fullscreen mode Exit fullscreen mode

Here, we can see that the file is under the /etc directory.

8. Updating the Configuration Parameters

We can edit the postgres.conf file to change the configuration parameters. For our changes to take effect, we may have to restart the server or reload its configuration. This depends on the parameter that we change.

For example, let’s change the log_min_messages parameter that keeps the logging level of the server. For that,** we edit the postgresql.conf file, and set log_min_messages=debug5 from the default value of warning:**

$ cat postgresql.conf | grep log_min_messages
log_min_messages = debug5               # values in order of decreasing detail:
Enter fullscreen mode Exit fullscreen mode

Then, a _reload _applies the new value:

$ sudo systemctl reload postgresql.service
Enter fullscreen mode Exit fullscreen mode

Next, let’s use _psql _to view the parameter we changed:

postgres=# show log_min_messages;
 log_min_messages
------------------
 debug5
(1 row)
Enter fullscreen mode Exit fullscreen mode

Indeed, we can see the correct value.

9. The Database Storage Area

The database storage area on disk or data directory is where databases are stored. The exact path to the data directory is stored in the data_directory parameter.

Notably, we can find the path using psql _and the _show command:

postgres=# show data_directory;
       data_directory
-----------------------------
 /var/lib/postgresql/14/main
(1 row)
Enter fullscreen mode Exit fullscreen mode

Indeed, we can see the path to the data directory.

**

10. User Configuration

**
We can create a new database user with the _createuser _program. Conversely, we can drop a user with the _dropuser _program.

10.1. Creating a New User

Let’s create a new user with password authentication enabled:

$ sudo -u postgres createuser -P testuser
Enter password for new role:
Enter it again:
Enter fullscreen mode Exit fullscreen mode

In this example, we invoked createuser to create a new database user named testuser. The -P option enables password authentication and prompts us to enter a password.

10.2. Log in via New User

Next, *we can use testuser to log in via the _psql _program:
*

$ psql -h localhost -p 5432 -U testuser -d postgres
Password for user testuser:
psql (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=>
Enter fullscreen mode Exit fullscreen mode

Indeed, we connected to the postgres _database. Besides the _-d option that denotes the database name, we used three other options:

-h: hostname of the server
-p: port of the server
-U: database username

10.3. Dropping a User

Finally, we can drop a user with the _dropuser _program:

$ sudo -u postgres dropuser testuser
Enter fullscreen mode Exit fullscreen mode

Indeed, we dropped testuser.

**

11. Conclusion

**

In this article, we learned how to install and configure the PostgreSQL database server. Specifically, we installed PostgreSQL using apt, dnf, and yum. Then, we looked at the _postgresql _service and the _postgres _user. Finally, we saw how to configure the server and create new users.

Top comments (0)