PostgreSQL (often abbreviated as Postgres) is an object-relational database management system (ORDBMS) emphasizing extensibility and standards compliance.
It has more than 30 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and performance.
PostgreSQL is released under the PostgreSQL License and is available for Linux, Microsoft Windows, FreeBSD, OpenBSD, and macOS.
In this post, we will see how to install PostgreSQL on Debian 11 / Debian 10.
Add PostgreSQL repository
PostgreSQL publishes packages for Debian operating system through their dedicated repository, and the packages in their repository are fresher than those available in the OS repository.
First, update the repository index using the apt command
sudo apt update
Then, install the HTTPS support for apt to get packages from the internet.
sudo apt install -y curl apt-transport-https
Now, import the PostgreSQL signing key.
# Debian 11 curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /usr/share/keyrings/postgresql-keyring.gpg # Debian 10 curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Finally, add the PostgreSQL repository information to the system with the below command.
# Debian 11 echo "deb [signed-by=/usr/share/keyrings/postgresql-keyring.gpg] http://apt.postgresql.org/pub/repos/apt/ bullseye-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list # Debian 10 echo "deb http://apt.postgresql.org/pub/repos/apt/ buster-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list
Install PostgreSQL on Debian
Once you have added the PostgreSQL repository, run the following command to update the repository index.
sudo apt update
Then, install PostgreSQL using the below command based on the PostgreSQL version you want to install.
# PostgreSQL 13 sudo apt install -y postgresql-13 # PostgreSQL 12 sudo apt install -y postgresql-12
By now, the PostgreSQL service will be up and running. You can check the status of the service with the below command.
sudo systemctl status postgresql
Output:
● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Wed 2021-09-15 14:19:30 CDT; 42s ago Main PID: 3759 (code=exited, status=0/SUCCESS) Tasks: 0 (limit: 2343) Memory: 0B CGroup: /system.slice/postgresql.service Sep 15 14:19:30 debian.itzgeek.local systemd[1]: Starting PostgreSQL RDBMS... Sep 15 14:19:30 debian.itzgeek.local systemd[1]: Started PostgreSQL RDBMS.
Configure PostgreSQL Server
By default, PostgreSQL listens on the localhost (127.0.0.1), which you may not always want as external applications may need to connect to the database. So, we will configure PostgreSQL to listen to the system IP address.
# PostgreSQL 13 sudo nano /etc/postgresql/13/main/postgresql.conf # PostgreSQL 12 sudo nano /etc/postgresql/12/main/postgresql.conf
Set the listen_addresses to * or <IPAddress>.
listen_addresses = '192.168.0.10'
Restart PostgreSQL service.
sudo systemctl restart postgresql
You can now use the netstat command to confirm if PostgreSQL is listening on port 5432 or not.
sudo netstat -antup | grep 5432
Output:
tcp 0 0 192.168.0.10:5432 0.0.0.0:* LISTEN 5265/postgres
READ: netstat command not found on Debian – Quick Fix
Access PostgreSQL
To manage the PostgreSQL database, you will need to log in as a postgres (Linux user) and then have to access the database shell using the psql command.
sudo -u postgres psql
Output:
[email protected]:~$ psql psql (13.4 (Debian 13.4-1.pgdg100+1)) Type "help" for help. postgres=#
On psql shell, run the below command to change the postgres user (Database admin password.
postgres=# \password
OR
postgres=# \password postgres
Create PostgreSQL Database and User
First, create a database user with the below command.
CREATE USER mydb_user WITH ENCRYPTED PASSWORD 'password';
Next, create a database with the below command.
CREATE DATABASE mydb WITH OWNER mydb_user;
Then, you can grant privileges to mydb_user on the newly created database.
GRANT ALL PRIVILEGES ON DATABASE mydb TO mydb_user;
Now, check whether the user has been created or not.
postgres=# \du
Output:
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
mydb_user | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#
Also, you can list databases to see if your database mydb has been created.
postgres=# \l
Output:
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-----------+----------+-------------+-------------+-------------------------
mydb | mydb_user | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/mydb_user +
| | | | | mydb_user=CTc/mydb_user
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=#
Finally, log in to the mydb with the database user mydb_user.
psql -h localhost -d mydb -U mydb_user
Output:
Password for user mydb_user: psql (13.4 (Debian 13.4-1.pgdg110+1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. mydb=>
Conclusion
That’s All. I hope you have learned how to install PostgreSQL on Debian 11 / Debian 10.
