Learn how to install PostgreSQL 15 on Debian 12 using this comprehensive step-by-step tutorial. 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.
Table of Contents
Installing PostgreSQL 15 on Debian 12
Install PostgreSQL 15
As of this writing, PostgreSQL 15 is the current major PostgreSQL release.
Fortunately, the default Debian 12 main repositories provides PostgreSQL packages.
apt-cache policy postgresql
postgresql:
Installed: (none)
Candidate: 15+248
Version table:
15+248 500
500 http://deb.debian.org/debian bookworm/main amd64 Packages
This makes it easy to install PostgreSQL 15.x on Debian 12.
Therefore, to install PostgreSQL 15, run the command below;
apt install postgresql postgresql-contribRunning 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 15-main
Loaded: loaded (/lib/systemd/system/[email protected]; enabled-runtime; preset: enabled)
Active: active (running) since Thu 2023-07-06 18:53:12 CEST; 12s ago
Process: 4877 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 15-main start (code=exited, status=0>
Main PID: 4882 (postgres)
Tasks: 6 (limit: 2284)
Memory: 19.2M
CPU: 154ms
CGroup: /system.slice/system-postgresql.slice/[email protected]
├─4882 /usr/lib/postgresql/15/bin/postgres -D /var/lib/postgresql/15/main -c config_file=/etc/postgr>
├─4883 "postgres: 15/main: checkpointer "
├─4884 "postgres: 15/main: background writer "
├─4886 "postgres: 15/main: walwriter "
├─4887 "postgres: 15/main: autovacuum launcher "
└─4888 "postgres: 15/main: logical replication launcher "
Jul 06 18:53:10 bookworm systemd[1]: Starting [email protected] - PostgreSQL Cluster 15-main...
Jul 06 18:53:12 bookworm systemd[1]: Started [email protected] - PostgreSQL Cluster 15-main...
Installing Other Versions of PostgreSQL on Debian 12
Install PostgreSQL APT Repository
If you want to install other versions of PostgreSQL on Debian 12, you need to add the PostgreSQL apt repository;
apt install postgresql-commonsudo sh /usr/share/postgresql-common/pgdg/apt.postgresql.org.shapt updateInstall PostgreSQL 14 on Debian 12
apt install postgresql-14 postgresql-contrib-14Install PostgreSQL 13 on Debian 12
apt install postgresql-13 postgresql-contrib-13You should be able to install other versions you need.
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/15/bin/postgres -VSample command output;
postgres (PostgreSQL) 15.3 (Debian 15.3-0+deb12u1)Similarly, you can use the command;
sudo -Hiu postgres psql -c "SELECT version();"Sample output;
version
-------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.3 (Debian 15.3-0+deb12u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)
Logging into PostgreSQL
By default, postgres user is created upon the installation of PostgreSQL.
id postgresuid=114(postgres) gid=122(postgres) groups=122(postgres),110(ssl-cert)The home directory for the user is set to /var/lib/postgresql.
getent passwd postgrespostgres:x:114:122:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bashHence, to login to PostgreSQL, first switch to postgres user using the command;
sudo -Hiu postgresThis changes the bash prompt as;
postgres@bookworm:~$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.
psqlThis changes the prompt as;
psql (15.3 (Debian 15.3-0+deb12u1))
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 CREATE FOREIGN DATA WRAPPER DROP ROUTINE
ALTER AGGREGATE CREATE FOREIGN TABLE DROP RULE
ALTER COLLATION CREATE FUNCTION DROP SCHEMA
ALTER CONVERSION CREATE GROUP DROP SEQUENCE
ALTER DATABASE CREATE INDEX DROP SERVER
ALTER DEFAULT PRIVILEGES CREATE LANGUAGE DROP STATISTICS
ALTER DOMAIN CREATE MATERIALIZED VIEW DROP SUBSCRIPTION
ALTER EVENT TRIGGER CREATE OPERATOR DROP TABLE
ALTER EXTENSION CREATE OPERATOR CLASS DROP TABLESPACE
ALTER FOREIGN DATA WRAPPER CREATE OPERATOR FAMILY DROP TEXT SEARCH CONFIGURATION
ALTER FOREIGN TABLE CREATE POLICY DROP TEXT SEARCH DICTIONARY
ALTER FUNCTION CREATE PROCEDURE DROP TEXT SEARCH PARSER
ALTER GROUP CREATE PUBLICATION DROP TEXT SEARCH TEMPLATE
ALTER INDEX CREATE ROLE DROP TRANSFORM
ALTER LANGUAGE CREATE RULE DROP TRIGGER
ALTER LARGE OBJECT CREATE SCHEMA DROP TYPE
ALTER MATERIALIZED VIEW CREATE SEQUENCE DROP USER
ALTER OPERATOR CREATE SERVER DROP USER MAPPING
ALTER OPERATOR CLASS CREATE STATISTICS DROP VIEW
ALTER OPERATOR FAMILY CREATE SUBSCRIPTION END
ALTER POLICY CREATE TABLE EXECUTE
ALTER PROCEDURE CREATE TABLE AS EXPLAIN
ALTER PUBLICATION CREATE TABLESPACE FETCH
ALTER ROLE CREATE TEXT SEARCH CONFIGURATION GRANT
ALTER ROUTINE CREATE TEXT SEARCH DICTIONARY IMPORT FOREIGN SCHEMA
ALTER RULE CREATE TEXT SEARCH PARSER INSERT
ALTER SCHEMA CREATE TEXT SEARCH TEMPLATE LISTEN
ALTER SEQUENCE CREATE TRANSFORM LOAD
ALTER SERVER CREATE TRIGGER LOCK
ALTER STATISTICS CREATE TYPE MERGE
ALTER SUBSCRIPTION CREATE USER MOVE
ALTER SYSTEM CREATE USER MAPPING NOTIFY
ALTER TABLE CREATE VIEW PREPARE
ALTER TABLESPACE DEALLOCATE PREPARE TRANSACTION
ALTER TEXT SEARCH CONFIGURATION DECLARE REASSIGN OWNED
ALTER TEXT SEARCH DICTIONARY DELETE REFRESH MATERIALIZED VIEW
ALTER TEXT SEARCH PARSER DISCARD REINDEX
ALTER TEXT SEARCH TEMPLATE DO RELEASE SAVEPOINT
ALTER TRIGGER DROP ACCESS METHOD RESET
ALTER TYPE DROP AGGREGATE REVOKE
ALTER USER DROP CAST ROLLBACK
ALTER USER MAPPING DROP COLLATION ROLLBACK PREPARED
ALTER VIEW DROP CONVERSION ROLLBACK TO SAVEPOINT
ANALYZE DROP DATABASE SAVEPOINT
BEGIN DROP DOMAIN SECURITY LABEL
CALL DROP EVENT TRIGGER SELECT
CHECKPOINT DROP EXTENSION SELECT INTO
CLOSE DROP FOREIGN DATA WRAPPER SET
CLUSTER DROP FOREIGN TABLE SET CONSTRAINTS
COMMENT DROP FUNCTION SET ROLE
COMMIT DROP GROUP SET SESSION AUTHORIZATION
COMMIT PREPARED DROP INDEX SET TRANSACTION
COPY DROP LANGUAGE SHOW
CREATE ACCESS METHOD DROP MATERIALIZED VIEW START TRANSACTION
CREATE AGGREGATE DROP OPERATOR TABLE
CREATE CAST DROP OPERATOR CLASS TRUNCATE
CREATE COLLATION DROP OPERATOR FAMILY UNLISTEN
CREATE CONVERSION DROP OWNED UPDATE
CREATE DATABASE DROP POLICY VACUUM
CREATE DOMAIN DROP PROCEDURE VALUES
CREATE EVENT TRIGGER DROP PUBLICATION WITH
CREATE EXTENSION DROP ROLE
Getting Started with PostgreSQL
You can now get started with PostgreSQL.
Create PostgreSQL Database
Once you are logged into the PostgreSQL as shown above, you can create your databases.
For example, to create a database named kifarunix;
create database kifarunix;List databases in PostgreSQL
You can list databases while logged into PostgreSQL using the \l ,meta-command;
\lsample output;
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
kifarunix | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(4 rows)
Create PostgreSQL Database User
You can create PostgreSQL database user using the command;
CREATE USER your_username WITH PASSWORD 'your_password';For example;
create user kifarunixadmin with password 'ChangeME_pass';List Database Users in PostgreSQL
You can list database users in PostgreSQL using the \du meta-command;
\du
List of roles
Role name | Attributes | Member of
----------------+------------------------------------------------------------+-----------
kifarunixadmin | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Grant Roles to Database User on PostgreSQL
You can grant a user specific roles on a specific database.
For example, grant all privileges on the database to the user;
GRANT ALL PRIVILEGES ON DATABASE your_database_name TO your_username;e.g
grant all privileges on database kifarunix to kifarunixadmin;You can also grant specific roles. Read more on PostgreSQL GRANT.
The above grant command creates a role like the username;
select rolname from pg_roles;
rolname
---------------------------
postgres
pg_database_owner
pg_read_all_data
pg_write_all_data
pg_monitor
pg_read_all_settings
pg_read_all_stats
pg_stat_scan_tables
pg_read_server_files
pg_write_server_files
pg_execute_server_program
pg_signal_backend
pg_checkpoint
kifarunixadmin
(14 rows)
To list the privileges assigned to our user on the database;
\dp kifarunixadminIf the “Access privileges” column is empty for a given object, it means the object has default privileges (that is, its privileges entry in the relevant system catalog is null). Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type.
Further Reading
Check Getting Started with PostgreSQL on the documentation page to learn more about PostgreSQL.
That closes our guide on installing PostgreSQL 15 on Debian 12.
