Install PostgreSQL 13 on Debian 11

0
4

Follow through this guide to learn how to install PostgreSQL 13 on Debian 11. PostgreSQL is a fully featured object-relational database management system. It supports a large part of the SQL standard and is designed to be extensible by users in many aspects.

Some of the features are: ACID transactions, foreign keys, views, sequences, subqueries, triggers, user-defined types and functions, outer joins, multiversion concurrency control. Graphical user interfaces and bindings for many
programming languages are available as well.

PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.

Install PostgreSQL 13 on Debian 11

As of this writing, PostgreSQL 13 is the current major PostgreSQL release.

Fortunately, the default Debian 11 main repositories provides PostgreSQL packages.

apt-cache policy postgresql
postgresql:
  Installed: (none)
  Candidate: 13+225
  Version table:
     13+225 500
        500 http://deb.debian.org/debian bullseye/main amd64 Packages

This makes it easy to install PostgreSQL 13.x on Debian 11.

Install PostgreSQL 13 on Debian 11

Therefore, to install PostgreSQL 13 on Debian 11, run the command below;

apt install postgresql postgresql-contrib

Running PostgreSQL

When installation is done, PostgreSQL is set to run and start on system boot. Confirm by checking the service.

systemctl status [email protected]
[email protected] - PostgreSQL Cluster 13-main
     Loaded: loaded (/lib/systemd/system/[email protected]; enabled-runtime; vendor preset: enabled)
     Active: active (running) since Mon 2021-08-30 20:17:45 EAT; 1min 25s ago
   Main PID: 3588 (postgres)
      Tasks: 7 (limit: 1133)
     Memory: 17.4M
        CPU: 199ms
     CGroup: /system.slice/system-postgresql.slice/[email protected]
             ├─3588 /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresql.conf
             ├─3590 postgres: 13/main: checkpointer
             ├─3591 postgres: 13/main: background writer
             ├─3592 postgres: 13/main: walwriter
             ├─3593 postgres: 13/main: autovacuum launcher
             ├─3594 postgres: 13/main: stats collector
             └─3595 postgres: 13/main: logical replication launcher

Aug 30 20:17:42 debian11 systemd[1]: Starting PostgreSQL Cluster 13-main...
Aug 30 20:17:45 debian11 systemd[1]: Started PostgreSQL Cluster 13-main.

Installing Other Versions of PostgreSQL on Debian 11

If you want to install other versions of PostgreSQL on Debian 11, you need to add the PostgreSQL apt repository;

echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
curl -sL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/pgdg.asc >/dev/null 
apt update

Install PostgreSQL 12 on Debian 11

apt install postgresql-12 postgresql-contrib-12

Install PostgreSQL 11 on Debian 11

apt install postgresql-11 postgresql-contrib-11

Verifying Version of Installed PostgreSQL on command line

You can find the version of install PostgreSQL on command line using the command below;

/usr/lib/postgresql/13/bin/postgres -V

Sample command output;

postgres (PostgreSQL) 13.3 (Debian 13.3-1)

Similarly, you can use the command;

sudo -Hiu postgres psql -c "SELECT version();"

Sample output;

                                                      version                                                      
-------------------------------------------------------------------------------------------------------------------
 PostgreSQL 13.3 (Debian 13.3-1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

Logging into PostgreSQL

By default, postgres user is created upon the installation of PostgreSQL.

id postgres
uid=107(postgres) gid=115(postgres) groups=115(postgres),114(ssl-cert)

The home directory for the user is set to /var/lib/postgresql.

getent passwd postgres
postgres:x:107:115:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

Hence, to login to PostgreSQL, first switch to postgres user using the command;

sudo -Hiu postgres

Once you are logged in, you can then run the psql command to get to PostgreSQL shell. psql is a PostgreSQL interactive terminal command that enables you to type in queries interactively, issue them to PostgreSQL, and see the query results.

psql

This changes the prompt as;

psql (13.3 (Debian 13.3-1))
Type "help" for help.

postgres=#

Type help to see various command usage.

help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
postgres=#
\h
Available help:
  ABORT                            ALTER TEXT SEARCH TEMPLATE       CREATE PUBLICATION               DROP FUNCTION                    IMPORT FOREIGN SCHEMA
  ALTER AGGREGATE                  ALTER TRIGGER                    CREATE ROLE                      DROP GROUP                       INSERT
  ALTER COLLATION                  ALTER TYPE                       CREATE RULE                      DROP INDEX                       LISTEN
  ALTER CONVERSION                 ALTER USER                       CREATE SCHEMA                    DROP LANGUAGE                    LOAD
  ALTER DATABASE                   ALTER USER MAPPING               CREATE SEQUENCE                  DROP MATERIALIZED VIEW           LOCK
  ALTER DEFAULT PRIVILEGES         ALTER VIEW                       CREATE SERVER                    DROP OPERATOR                    MOVE
  ALTER DOMAIN                     ANALYZE                          CREATE STATISTICS                DROP OPERATOR CLASS              NOTIFY
  ALTER EVENT TRIGGER              BEGIN                            CREATE SUBSCRIPTION              DROP OPERATOR FAMILY             PREPARE
  ALTER EXTENSION                  CALL                             CREATE TABLE                     DROP OWNED                       PREPARE TRANSACTION
  ALTER FOREIGN DATA WRAPPER       CHECKPOINT                       CREATE TABLE AS                  DROP POLICY                      REASSIGN OWNED
  ALTER FOREIGN TABLE              CLOSE                            CREATE TABLESPACE                DROP PROCEDURE                   REFRESH MATERIALIZED VIEW
  ALTER FUNCTION                   CLUSTER                          CREATE TEXT SEARCH CONFIGURATION DROP PUBLICATION                 REINDEX
  ALTER GROUP                      COMMENT                          CREATE TEXT SEARCH DICTIONARY    DROP ROLE                        RELEASE SAVEPOINT
  ALTER INDEX                      COMMIT                           CREATE TEXT SEARCH PARSER        DROP ROUTINE                     RESET
  ALTER LANGUAGE                   COMMIT PREPARED                  CREATE TEXT SEARCH TEMPLATE      DROP RULE                        REVOKE
  ALTER LARGE OBJECT               COPY                             CREATE TRANSFORM                 DROP SCHEMA                      ROLLBACK
  ALTER MATERIALIZED VIEW          CREATE ACCESS METHOD             CREATE TRIGGER                   DROP SEQUENCE                    ROLLBACK PREPARED
  ALTER OPERATOR                   CREATE AGGREGATE                 CREATE TYPE                      DROP SERVER                      ROLLBACK TO SAVEPOINT
  ALTER OPERATOR CLASS             CREATE CAST                      CREATE USER                      DROP STATISTICS                  SAVEPOINT
  ALTER OPERATOR FAMILY            CREATE COLLATION                 CREATE USER MAPPING              DROP SUBSCRIPTION                SECURITY LABEL
  ALTER POLICY                     CREATE CONVERSION                CREATE VIEW                      DROP TABLE                       SELECT
  ALTER PROCEDURE                  CREATE DATABASE                  DEALLOCATE                       DROP TABLESPACE                  SELECT INTO
  ALTER PUBLICATION                CREATE DOMAIN                    DECLARE                          DROP TEXT SEARCH CONFIGURATION   SET
  ALTER ROLE                       CREATE EVENT TRIGGER             DELETE                           DROP TEXT SEARCH DICTIONARY      SET CONSTRAINTS
  ALTER ROUTINE                    CREATE EXTENSION                 DISCARD                          DROP TEXT SEARCH PARSER          SET ROLE
  ALTER RULE                       CREATE FOREIGN DATA WRAPPER      DO                               DROP TEXT SEARCH TEMPLATE        SET SESSION AUTHORIZATION
  ALTER SCHEMA                     CREATE FOREIGN TABLE             DROP ACCESS METHOD               DROP TRANSFORM                   SET TRANSACTION
  ALTER SEQUENCE                   CREATE FUNCTION                  DROP AGGREGATE                   DROP TRIGGER                     SHOW
  ALTER SERVER                     CREATE GROUP                     DROP CAST                        DROP TYPE                        START TRANSACTION
  ALTER STATISTICS                 CREATE INDEX                     DROP COLLATION                   DROP USER                        TABLE
  ALTER SUBSCRIPTION               CREATE LANGUAGE                  DROP CONVERSION                  DROP USER MAPPING                TRUNCATE
  ALTER SYSTEM                     CREATE MATERIALIZED VIEW         DROP DATABASE                    DROP VIEW                        UNLISTEN
  ALTER TABLE                      CREATE OPERATOR                  DROP DOMAIN                      END                              UPDATE
  ALTER TABLESPACE                 CREATE OPERATOR CLASS            DROP EVENT TRIGGER               EXECUTE                          VACUUM
  ALTER TEXT SEARCH CONFIGURATION  CREATE OPERATOR FAMILY           DROP EXTENSION                   EXPLAIN                          VALUES
  ALTER TEXT SEARCH DICTIONARY     CREATE POLICY                    DROP FOREIGN DATA WRAPPER        FETCH                            WITH
  ALTER TEXT SEARCH PARSER         CREATE PROCEDURE                 DROP FOREIGN TABLE               GRANT                            
postgres=#

Getting Started with PostgreSQL

You can now get started with PostgreSQL.

Check Getting Started with PostgreSQL on the documentation page to learn more about PostgreSQL.

That closes our guide on how to install PostgreSQL 13 on Debian 11.

Other Tutorials

Install Nodejs Debian 11

Install Yarn on Debian 11

Install Yarn on Rocky Linux 8

LEAVE A REPLY

Please enter your comment!
Please enter your name here