In more than 30 years of existence and active development, PostgreSQL has won a good reputation in terms of robustness, reliability, and performance. PostgreSQL is a free and open source relational database management system. PostgreSQL 14 is the latest release of the world’s most advanced open source database.
The latest version of PostgreSQL 14 introduces a number of new features to help simplify the development and management of data-driven applications. It continues to make improvements in the area of complex data types, including more accessible JSON access and support for discontinuous data ranges. PostgreSQL 14 has played an important role in the history of PostgreSQL’s high performance and distributed workloads, with improvements in logical replication, query parallelism, high write workloads, and connection concurrency.
Features of PostgreSQL 14
PostgreSQL 14 has the following features and enhancements:
- Significantly improved performance through parallel queries, highly concurrent workloads, partitioned databases, logical replication, and cleanup.
- OUT parameters can now be used to return data from stored procedures.
- JSON convenience and multiple ranges-supports the representation of non-continuous data ranges.
- The subscript operator has been added to the jsonb and hstore types.
- The update of the B-tree index is managed more effectively, thereby reducing index bloat.
- Support pipeline query Library , Which can significantly improve the throughput of high-latency connections.
- Security enhancement
In this guide, we will systematically introduce how to install PostgreSQL 14 On Debian 11 | Debian 10.
Set up prerequisites
For this guide, please make sure you have the following:
- Debian 11|Debian 10 server.
- User and sudo Right to use.
Make sure your system is updated.
sudo apt update && sudo apt upgrade
Then go ahead and install the required packages.
sudo apt -y install gnupg2 wget vim
Step 1-Install PostgreSQL 14 on Debian 11 | Debian 10
PostgreSQL is available in the default Debian repository, but the available version is not the latest. Use the following command to check the available version:
sudo apt-cache search postgresql | grep postgresql
In this guide, we are interested in PostgreSQL 14 which is not available in the default repository. Therefore, we need to add a repository that provides packages.
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
Import the GPG key for the added repository.
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Next, update your APT package index.
sudo apt -y update
Now install PostgreSQL 14 on Debian 11/Debian 10 using the following command.
sudo apt install postgresql-14
Verify your PostgreSQL 14 installation is as follows.
$ sudo -u postgres psql -c "SELECT version();" version ----------------------------------------------------------------------------------------------------------------------------- PostgreSQL 14.0 (Debian 14.0-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit (1 row)
After the complete installation, PostgreSQL 14 will be initialized. Check the status of the service as follows.
$ systemctl status postgresql ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Mon 2021-10-25 07:26:42 EDT; 1min 10s ago Process: 3811 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 3811 (code=exited, status=0/SUCCESS) CPU: 1ms Oct 25 07:26:42 debian systemd: Starting PostgreSQL RDBMS... Oct 25 07:26:42 debian systemd: Finished PostgreSQL RDBMS.
Step 2-Connect and secure the PostgreSQL database
In PostgreSQL, there is a concept called Role Used for client authentication. PostgreSQL is set to use Authentication by default. This method associates roles with matching Linux system accounts. If it exists, you can log in.
After installation, a user account named postgres Is created and associated with the role postgres. With this user, you can log in to the PostgreSQL shell.
The multiple authentication methods supported by PostgreSQL include:
- password – Allow the role to connect by providing a password.
- identity – Work on TCP/IP connections by obtaining the client’s operating system user name and optional mapping user name.
- Peer – Similar to ident, but only supports local connections.
- Believe – It is defined in
pg_hba.conf. As long as the conditions in the conf file are met, the user can connect without a password
There are two ways to connect to the PostgreSQL database;
Switch to postgres user.
sudo -i -u postgres
Now use the command to access the PostgreSQL shell.
[email protected]:~$ psql psql (14.0 (Debian 14.0-1.pgdg110+1)) Type "help" for help. postgres=#
Now in the shell, you can now manage your database.
This method does not need to switch to the postgres user, directly access the PostgreSQL shell sudo The command is as follows.
sudo -u postgres psql
$ sudo -u postgres psql psql (14.0 (Debian 14.0-1.pgdg110+1)) Type "help" for help. postgres=#
Step 3-Configure PostgreSQL 14 instance for remote access
In order to remotely access the PostgreSQL 14 instance, we need to modify the file in /etc/postgresql/14/main/pg_hba.conf
First, change the peer ID to trust, as shown below.
sudo sed -i '/^local/s/peer/trust/' /etc/postgresql/14/main/pg_hba.conf
Then, allow password login.
sudo sed -i '/^host/s/ident/md5/' /etc/postgresql/14/main/pg_hba.conf
Access from anywhere is now allowed.
sudo vim /etc/postgresql/14/main/pg_hba.conf
In the file, add the following line.
# Database administrative login by Unix domain socket local all postgres trust # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 scram-sha-256 host all all 0.0.0.0/24 md5 # IPv6 local connections: host all all ::1/128 scram-sha-256 host all all 0.0.0.0/0 md5
Now allow PostgreSQL to listen* by editing the following file.
sudo vim /etc/postgresql/14/main/postgresql.conf
Edit the file as follows.
# CONNECTIONS AND AUTHENTICATION ........ listen_addresses='*'
To apply the changes you have made, you need to restart PosgreSQL.
sudo systemctl restart postgresql sudo systemctl enable postgresql
Step 4-User management in PostgreSQL database
Now that we have configured everything, let’s create a super user for database management.
Connect to the PostgreSQL role.
sudo -u postgres psql
Create a super user, admin The password is password as follows.
CREATE ROLE admin WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'Passw0rd';
psql (14.0 (Debian 14.0-1.pgdg110+1)) Type "help" for help. postgres=# CREATE ROLE admin WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'Passw0rd'; CREATE ROLE postgres=#
Manage application users
Create a database and grant users permissions on the database.In this guide, we will create a database named test_db and a user with a password of test_user password as follows.
create database test_db; create user test_user with encrypted password 'dbpassword'; grant all privileges on database test_db to test_user; q
Connect to the instance from the remote host.
Verify that the service is running:
$ ss -tunelp | grep 5432 tcp LISTEN 0 244 0.0.0.0:5432 0.0.0.0:* uid:117 ino:30522 sk:d cgroup:/system.slice/system-postgresql.slice/[email protected] <-> tcp LISTEN 0 244 [::]:5432 [::]:* uid:117 ino:30523 sk:e cgroup:/system.slice/system-postgresql.slice/[email protected] v6only:1 <->
If you are using ufw, please allow port 5432 through the firewall as shown below.
sudo ufw allow 5432/tcp
Now we want to connect to the PostgreSQL 14 instance from the remote host. The syntax is as follows.
psql 'postgres://< username >:< password >@< host >:< port >/< db >?sslmode=disable'
for example, On another host, I will try to connect to my PostgreSQL14 instance using a super user account as shown below.
First, install PostgreSQL 14 on the remote host and continue to access your PostgreSQL 14 instance.
psql 'postgres://admin:[email protected]:5432/postgres?sslmode=disable'
We have successfully walked through how to install PostgreSQL 14 on Debian 11 | Debian 10. In addition, we have also configured PostgreSQL 14 and accessed it through a remote host. Learning is enough!
- How to use Ansible to manage PostgreSQL database
- The best books for learning PostgreSQL database
- Install PostgreSQL 13 Fedora
- How to install PostgreSQL 13 on CentOS 7