PostgreSQL 14 comes with the below features and enhancements:<\/p>\n\n\n\n
- \n
- Significant performance increases through parallel queries, heavily concurrent workloads, partitioned databases, logical replication, and vacuuming.<\/li>\n\n\n\n
- OUT parameters can now be used to return data from stored procedures.<\/li>\n\n\n\n
- JSON Conveniences and multiranges- enabling the representation of non-contiguous data ranges.<\/li>\n\n\n\n
- Subscripting operators have been added to the jsonb and hstore types.<\/li>\n\n\n\n
- Updates to B-tree indexes are more efficiently managed, resulting in less index bloat.<\/li>\n\n\n\n
- Supports pipelined queries through libpq<\/strong>, which can significantly increase throughput over high-latency connections.<\/li>\n\n\n\n
- Security enhancements<\/li>\n<\/ul>\n\n\n\n
Installing PostgreSQL 14 on Debian 12\/11\/10<\/h2>\n\n\n\n
In this guide, we will systematically go through how to install PostgreSQL 14<\/em><\/strong> on Debian.<\/p>\n\n\n\n
1) Setup Pre-requisites<\/h3>\n\n\n\n
For this guide, ensure that you have the following:<\/p>\n\n\n\n
- \n
- Debian Server.<\/li>\n\n\n\n
- User with sudo access.<\/li>\n<\/ul>\n\n\n\n
Ensure that your system is updated.<\/p>\n\n\n\n
sudo apt update && sudo apt upgrade -y<\/code><\/pre>\n\n\n\nThen proceed and install the required packages.<\/p>\n\n\n\n
sudo apt -y install gnupg2 wget vim<\/code><\/pre>\n\n\n\n2) Install PostgreSQL 14 server<\/h3>\n\n\n\n
PostgreSQL is available in the default Debian repositories but the available versions are not up to date. Check the available versions using the command:<\/p>\n\n\n\n
sudo apt-cache search postgresql | grep postgresql<\/code><\/pre>\n\n\n\nIn this guide, we are interested in PostgreSQL 14 which is not provided in the default repositories. Therefore, we are required to add a repository that provides the package.<\/p>\n\n\n\n
sudo sh -c 'echo \"deb http:\/\/apt.postgresql.org\/pub\/repos\/apt $(lsb_release -cs)-pgdg main\" > \/etc\/apt\/sources.list.d\/pgdg.list'<\/code><\/pre>\n\n\n\nImport the GPG key for the added repository.<\/p>\n\n\n\n
curl -fsSL https:\/\/www.postgresql.org\/media\/keys\/ACCC4CF8.asc|sudo gpg --dearmor -o \/etc\/apt\/trusted.gpg.d\/postgresql.gpg<\/code><\/pre>\n\n\n\nNext, update your APT package index.<\/p>\n\n\n\n
sudo apt -y update<\/code><\/pre>\n\n\n\nNow install PostgreSQL 14 on Debian using the command below.<\/p>\n\n\n\n
sudo apt install postgresql-14<\/code><\/pre>\n\n\n\nVerify your PostgreSQL 14 installation as below.<\/p>\n\n\n\n
$ sudo -u postgres psql -c \"SELECT version();\"<\/mark>\n version\n-----------------------------------------------------------------------------------------------------------------------\n PostgreSQL 13.14 (Debian 13.14-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit\n(1 row)<\/code><\/pre>\n\n\n\nWith a complete installation, PostgreSQL 14 will be initialized. Check the status of the service as below.<\/p>\n\n\n\n
$ systemctl status postgresql@14-main.service<\/mark>\n\u25cf<\/mark> postgresql@14-main.service - PostgreSQL Cluster 14-main\n Loaded: loaded (\/lib\/systemd\/system\/postgresql@.service; enabled-runtime; preset: enabled)\n Active: active (running)<\/mark> since Fri 2024-02-23 07:37:05 UTC; 58s ago\n Process: 7022 ExecStart=\/usr\/bin\/pg_ctlcluster --skip-systemctl-redirect 14-main start (code=exited, status=0\/SUCCESS)\n Main PID: 7027 (postgres)\n Tasks: 7 (limit: 2251)\n Memory: 17.4M\n CPU: 312ms\n CGroup: \/system.slice\/system-postgresql.slice\/postgresql@14-main.service\n \u251c\u25007027 \/usr\/lib\/postgresql\/14\/bin\/postgres -D \/var\/lib\/postgresql\/14\/main -c config_file=\/etc\/postgresql\/14\/main\/postgresql.conf\n \u251c\u25007029 \"postgres: 14\/main: checkpointer \"\n \u251c\u25007030 \"postgres: 14\/main: background writer \"\n \u251c\u25007031 \"postgres: 14\/main: walwriter \"\n \u251c\u25007032 \"postgres: 14\/main: autovacuum launcher \"\n \u251c\u25007033 \"postgres: 14\/main: stats collector \"\n \u2514\u25007034 \"postgres: 14\/main: logical replication launcher \"\n\nFeb 23 07:37:02 deb12 systemd[1]: Starting postgresql@14-main.service - PostgreSQL Cluster 14-main...\nFeb 23 07:37:05 deb12 systemd[1]: Started postgresql@14-main.service - PostgreSQL Cluster 14-main.<\/code><\/pre>\n\n\n\n3) Connect and Secure PostgreSQL database<\/h3>\n\n\n\n
In PostgreSQL, there is a concept known as roles<\/strong> used for client authentication. PostgreSQL is set to use ident authentication<\/strong><\/em> by default. This method associates roles with matching Linux system accounts. If it exists, then you are able to log in.<\/p>\n\n\n\n
After the installation, a user account with the name postgres<\/strong><\/em> is created and associated with the role postgres. With this user, one is able to log in to the PostgreSQL shell.<\/p>\n\n\n\n
Amon the multiple authentication methods supported by PostgreSQL include:<\/p>\n\n\n\n
- \n
- Password<\/strong> – allows a role to connect by providing a password.<\/li>\n\n\n\n
- Ident<\/strong> – works over TCP\/IP connections by obtaining the client’s operating system username with an optional username for mapping.<\/li>\n\n\n\n
- Peer<\/strong>– works similar to ident but supports local connections only.<\/li>\n\n\n\n
- Trust<\/strong> – it uses conditions defined on the
pg_hba.conf<\/code><\/em>. A user can connect without password as long as conditions in the conf file are met<\/li>\n<\/ol>\n\n\n\nThere are two ways of connecting to the PostgreSQL database;<\/p>\n\n\n\n
Method 1:<\/h4>\n\n\n\n
Switch to the postgres<\/em><\/strong> user.<\/p>\n\n\n\n
sudo -i -u postgres<\/code><\/pre>\n\n\n\nNow access the PostgreSQL shell with the command.<\/p>\n\n\n\n
psql<\/code><\/pre>\n\n\n\nSample Output:<\/p>\n\n\n\n
postgres@debian:~$ psql<\/mark>\npsql (14.11 (Debian 14.11-1.pgdg120+2), server 13.14 (Debian 13.14-1.pgdg120+2))\nType \"help\" for help.\n\npostgres=<\/mark>#<\/mark> <\/mark><\/code><\/pre>\n\n\n\nNow in the shell, you can now manage your database.<\/p>\n\n\n\n
Method 2:<\/h4>\n\n\n\n
In this method, you do not need to switch to the postgres user, you can directly access the PostgreSQL shell with the sudo command below.<\/p>\n\n\n\n
sudo -u postgres psql<\/code><\/pre>\n\n\n\nSample Output:<\/p>\n\n\n\n
$ sudo -u postgres psql<\/mark>\npsql (14.11 (Debian 14.11-1.pgdg120+2), server 13.14 (Debian 13.14-1.pgdg120+2))\nType \"help\" for help.\n\npostgres=<\/mark>#<\/mark> <\/mark><\/code><\/pre>\n\n\n\n4) Configure PostgreSQL for Remote Access(optional)<\/h3>\n\n\n\n
For the PostgreSQL 14 instance to be accessed remotely, we need to modify the file at \/etc\/postgresql\/14\/main\/pg_hba.conf<\/em><\/strong><\/p>\n\n\n\n
First, change the peer identification to trust as below.<\/p>\n\n\n\n
sudo sed -i '\/^local\/s\/peer\/trust\/' \/etc\/postgresql\/14\/main\/pg_hba.conf<\/code><\/pre>\n\n\n\nThen, allow password login.<\/p>\n\n\n\n
sudo sed -i '\/^host\/s\/ident\/md5\/' \/etc\/postgresql\/14\/main\/pg_hba.conf<\/code><\/pre>\n\n\n\nNow allow access from everywhere.<\/p>\n\n\n\n
sudo vim \/etc\/postgresql\/14\/main\/pg_hba.conf<\/code><\/pre>\n\n\n\nIn the file, add the lines below.<\/p>\n\n\n\n
# Database administrative login by Unix domain socket\nlocal all postgres trust\n# TYPE DATABASE USER ADDRESS METHOD\n\n# \"local\" is for Unix domain socket connections only\nlocal all all trust\n# IPv4 local connections:\nhost all all 127.0.0.1\/32 scram-sha-256\nhost all all 0.0.0.0\/24 md5\n<\/span># IPv6 local connections:\nhost all all ::1\/128 scram-sha-256\nhost all all 0.0.0.0\/0 md5<\/span><\/code><\/pre>\n\n\n\nNow allow PostgreSQL to listen on * by editing the below file.<\/p>\n\n\n\n
sudo vim \/etc\/postgresql\/14\/main\/postgresql.conf<\/code><\/pre>\n\n\n\nEdit the file as below.<\/p>\n\n\n\n
# CONNECTIONS AND AUTHENTICATION\n........\nlisten_addresses='*'<\/span><\/code><\/pre>\n\n\n\nFor the changes made to apply, you need to restart PosgreSQL.<\/p>\n\n\n\n
sudo systemctl restart postgresql\nsudo systemctl enable postgresql<\/code><\/pre>\n\n\n\n5) User Management in PostgreSQL Database<\/h3>\n\n\n\n
Now that we have configured everything, let’s create a superuser for database management. <\/p>\n\n\n\n
Connect to the PostgreSQL role.<\/p>\n\n\n\n
sudo -u postgres psql<\/code><\/pre>\n\n\n\nCreate a superuser, admin<\/em><\/strong> with password as Passw0rd<\/em><\/strong> as below.<\/p>\n\n\n\n
CREATE ROLE admin<\/span><\/strong> WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'Passw0rd<\/span>';<\/code><\/pre>\n\n\n\nSample Output:<\/p>\n\n\n\n
\npostgres=# CREATE ROLE admin WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'Passw0rd';<\/mark><\/em>\nCREATE ROLE\n<\/mark>postgres=# <\/code><\/pre>\n\n\n\nManage application users<\/h4>\n\n\n\n
Create a database and grant user privileges to the database. In this guide, we will create a database with the name, test_db and a user test_user with password as dbpassword<\/strong><\/em> as below.<\/p>\n\n\n\n
create database test_db;\ncreate user test_user with encrypted password 'dbpassword';\ngrant all privileges on database test_db to test_user;\n\\q<\/code><\/pre>\n\n\n\nConnect to the instance from remote host.<\/h4>\n\n\n\n
Verify that the service is running:<\/p>\n\n\n\n
$ ss -tunelp | grep 5432<\/span>\ntcp LISTEN 0 244 0.0.0.0:5432 0.0.0.0:* uid:117 ino:30522 sk:d cgroup:\/system.slice\/system-postgresql.slice\/postgresql@14-main.service <->\ntcp LISTEN 0 244 [::]:5432 [::]:* uid:117 ino:30523 sk:e cgroup:\/system.slice\/system-postgresql.slice\/postgresql@14-main.service v6only:1 <-><\/code><\/pre>\n\n\n\nIf you are using ufw, allow port 5432 through the firewall as below.<\/p>\n\n\n\n
sudo ufw allow 5432\/tcp<\/code><\/pre>\n\n\n\nNow we want to connect to the PostgreSQL 14 instance from a remote host. The syntax is as below.<\/p>\n\n\n\n
psql 'postgres:\/\/<username<\/mark>>:<password<\/mark>>@<host<\/mark>>:<port<\/mark>>\/<db<\/mark>>?sslmode=disable'<\/code><\/pre>\n\n\n\nFor example, on another host, I’ll try and connect to my PostgreSQL14 instance using the superuser account as below.<\/p>\n\n\n\n
First, install PostgreSQL 14 on the remote host and proceed to access your PostgreSQL14 instance.<\/p>\n\n\n\n
psql 'postgres:\/\/admin:Passw0rd@192.168.205.4:5432\/postgres?sslmode=disable'<\/code><\/pre>\n\n\n\nSample Output:<\/p>\n\n\n\n
![\"\"](\"https:\/\/computingforgeeks.com\/wp-content\/uploads\/2021\/10\/How-To-Install-PostgreSQL-14-on-Debian-11-Debian-10.png\" "\\"\\"")
<\/figure>\n\n\n\nRecommended books:<\/em><\/strong><\/p>\n\n\n\n
- \n
- Best Books To Learn PostgreSQL Databases<\/a><\/li>\n<\/ul>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
We have successfully walked through how to install PostgreSQL 14 on Debian. In addition to that, we have made configurations to PostgreSQL 14 and also accessed it via a remote host. That was enough learning!<\/p>\n\n\n\n
See more:<\/p>\n\n\n\n
- \n
- How To Manage PostgreSQL Database with Ansible<\/a><\/li>\n\n\n\n
- Best Books To Learn PostgreSQL Database<\/a><\/li>\n\n\n\n
- Install PostgreSQL 13 on Fedora<\/a><\/li>\n\n\n\n
- How To Install PostgreSQL 13 on CentOS 7<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
For over 30 years of existence and active development, PostgreSQL has earned a strong reputation for robustness, reliability, and performance. PostgreSQL is a free and open-source relational database management system. PostgreSQL 14 is the latest release version of this world’s most advanced open-source database. The latest version, PostgreSQL 14 introduces several new features that help … Read more<\/a><\/p>\n","protected":false},"author":21,"featured_media":63742,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[461,26,299,50],"tags":[324,688,36186,36187],"class_list":["post-107654","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-debian","category-how-to","category-linux-tutorials","tag-databases","tag-postgresql","tag-postgresql-14","tag-postgresql-14-on-debian"],"_links":{"self":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/posts\/107654","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/users\/21"}],"replies":[{"embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/comments?post=107654"}],"version-history":[{"count":0,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/posts\/107654\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/media\/63742"}],"wp:attachment":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/media?parent=107654"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/categories?post=107654"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/tags?post=107654"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- Best Books To Learn PostgreSQL Database<\/a><\/li>\n\n\n\n
- How To Manage PostgreSQL Database with Ansible<\/a><\/li>\n\n\n\n
- Ident<\/strong> – works over TCP\/IP connections by obtaining the client’s operating system username with an optional username for mapping.<\/li>\n\n\n\n
- Password<\/strong> – allows a role to connect by providing a password.<\/li>\n\n\n\n
- Security enhancements<\/li>\n<\/ul>\n\n\n\n