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
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
Otherwise, we can use yum:
$ yum install postgresql-server
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
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=#
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
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
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
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.
...
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)
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:
Then, a reload applies the new value:
$ sudo systemctl reload postgresql.service
Next, let’s use psql to view the parameter we changed:
postgres=# show log_min_messages;
log_min_messages
------------------
debug5
(1 row)
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)
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:
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=>
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
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.