How To Install PostgreSQL 12 on Debian 12/10/9 [Guide]

How can I install PostgreSQL 12 on Debian Linux system?. PostgreSQL is a powerful relational database management system used around the globe for running mission critical applications. PostgreSQL is based on POSTGRES 4.2. The PostgreSQL 12 was released for general use on 3rd October 2019. This guide will focus on guiding users on how to install PostgreSQL 12 on Debian Linux distribution.

Original content from computingforgeeks.com - post 32585

Check available guides if you’re using a different flavor of Linux:

Check PostgreSQL 12 new features and improvements in PostgreSQL 12 on the PostgreSQL 12 release notes page. Once you want to get started, follow below steps for installing PostgreSQL 12 on Debian Linux system.

Step 1: Update system

It is recommended to update your current system packages if it is a new server instance.

sudo apt update && sudo apt -y full-upgrade
[ -f /var/run/reboot-required ] && sudo reboot -f

Step 2: Add PostgreSQL 12 repository

The GPG key used for signing packages needs to be imported to the system:

sudo apt update && sudo apt -y install gnupg2
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc|sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

After importing GPG key, add PostgreSQL repository:

echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list

Step 3: Install PostgreSQL 12 on Debian

Now the repository has been added successfully, update the package list and install PostgreSQL 12 on Debian Linux system.

sudo apt update
sudo apt -y install postgresql-12 postgresql-client-12

A successful installation prints a message that is similar to one shared in the next screenshot.

The PostgreSQL service is started and set to come up after every system reboot.

$ systemctl status [email protected] 
● [email protected] - PostgreSQL Cluster 12-main
     Loaded: loaded (/lib/systemd/system/[email protected]; enabled-runtime; preset: enabled)
     Active: active (running) since Thu 2023-11-23 13:14:00 UTC; 2min 3s ago
    Process: 4489 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 12-main start (code=exited, status=0/SUCCESS)
   Main PID: 4494 (postgres)
      Tasks: 7 (limit: 4531)
     Memory: 17.7M
        CPU: 576ms
     CGroup: /system.slice/system-postgresql.slice/[email protected]
             ├─4494 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf
             ├─4496 "postgres: 12/main: checkpointer   "
             ├─4497 "postgres: 12/main: background writer   "
             ├─4498 "postgres: 12/main: walwriter   "
             ├─4499 "postgres: 12/main: autovacuum launcher   "
             ├─4500 "postgres: 12/main: stats collector   "
             └─4501 "postgres: 12/main: logical replication launcher   "

Nov 23 13:13:58 deb12 systemd[1]: Starting [email protected] - PostgreSQL Cluster 12-main...
Nov 23 13:14:00 deb12 systemd[1]: Started [email protected] - PostgreSQL Cluster 12-main.

$ systemctl is-enabled postgresql
enabled

Step 4: Test PostgreSQL Connection

During installation, a postgres user is created automatically. This user has full superadmin access to your entire PostgreSQL instance. Before you switch to this account, your logged in system user should have sudo privileges.

sudo su - postgres

Let’s reset this user password to a strong Password we can remember.

psql -c "alter user postgres with password 'StrongAdminPassw0rd'"

Start PostgreSQL prompt by using the command:

psql

Get connection details like below.

$ psql
psql (12.17 (Debian 12.17-1.pgdg120+1))
Type "help" for help.

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

Let’s create a test database and user to see if it’s working.

postgres=# CREATE DATABASE mytestdb;
CREATE DATABASE
postgres=# CREATE USER mytestuser WITH ENCRYPTED PASSWORD 'MyStr0ngPaSS';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE mytestdb to mytestuser;
GRANT

List created databases:

postgres=# \l
                               List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |    Access privileges    
-----------+----------+----------+---------+---------+-------------------------
 mytestdb  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres           +
           |          |          |         |         | postgres=CTc/postgres  +
           |          |          |         |         | mytestuser=CTc/postgres
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres            +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres            +
           |          |          |         |         | postgres=CTc/postgres
(4 rows)

Connect to database:

postgres-# \c mytestdb
You are now connected to database "mytestdb" as user "postgres".

Other PostgreSQL utilities installed such as createuser and createdb can be used to create database and users.

postgres@deb10:~$ createuser myuser --password
Password:
postgres@deb10:~$ createdb mydb -O myuser
postgres@deb10:~$ psql -l

We can create and connect to a database on PostgreSQL server.

Step 5: Configure remote Connection

Installation of PostgreSQL only accepts connections from localhost. In ideal production environments, you’ll have a central database server and remote clients connecting to it – But of course within a private network (LAN).

To enable remote connections, edit PostgreSQL configuration file:

sudo nano /etc/postgresql/12/main/postgresql.conf

Uncomment line 59 and change the Listen address to accept connections within your networks.

# Listen on all interfaces
listen_addresses = '*'

# Listen on specified private IP address
listen_addresses = '192.168.10.11'

After the change, restart postgresql service.

sudo systemctl restart postgresql

Confirm Listening addresses.

# netstat  -tunelp | grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      111        112837     11143/postgres      
tcp6       0      0 :::5432                 :::*                    LISTEN      111        112838     11143/postgres

Step 6: Install pgAdmin4 Management Tool

If you want to manage your PostgreSQL database server from a web interface, then install pgAdmin4.

Install pgAdmin4 on Debian Linux

You have successfully installed PostgreSQL 12 on Debian. Have a happy Database management and development with PostgreSQL.

Recommended books:

Best Books To Learn PostgreSQL Database

Similar:

Tags:

Install PostgreSQL 12 on Debian 12
Install PostgreSQL 12 on Debian 10/11
Install PostgreSQL 12 on Debian
PostgreSQL 12 installation on Debian Linux