How to Install PostgreSQL on Debian 11 Server Tutorial (Step by Step)
How to Install PostgreSQL on Debian 11 Server. PostgreSQL is a powerful, open source and object relational database, well known for it’s reliability, feature robustness and performance. It uses SQL language together with many features and the aim is to safely store and scale the most complicated data workloads. Follow this guide to learn what PostgreSQL is with it’s features and after that we will move onto installation guide on Debian 11 Server. Let’s start!
What is PostgreSQL
PostgreSQL is a free, open source and object relational database management system. DevOps use it as an alternative to MariaDB. PostgreSQL is used as primary data store or data warehouse for web applications , geographic information systems (GIS) and to solve various complex analytical processes. PostgreSQL can store structured and unstructured data in a single product. It is a database server that runs on Client Server architecture.
It is a cross platform and can be installed on all major operating systems including, Windows, Mac OS X, and all Linux and Unix distributions. It supports many programming languages such as Ruby, Perl, Java, Go, C#, Python and more.
It has management tools such as PgAdmin to get the most out of an instance.
PostgreSQL server has inbuilt user defined data types and comes with many functions and features that aid programmers in creating easy to use applications and building a secure environment. PostgreSQL tools manages data well without thinking about the size of the database. It has a robust feature set that makes it a top choice compared with other open source SQL databases available in the market, like Firebird, MySQL and MariaDB.
Features of PostgreSQL
- Free and open source
- Helps developers to build applications.
- Cross platform.
- Compliant with the ANSI SQL standard.
- ACID transactions.
- Support for client server architecture.
- Log based and trigger based replication SSL.
- Foreign keys, views, triggers and subqueries.
- User defined types and functions.
- Multi version concurrency control (MCC).
- Can run as a LAMP stack option.
- Graphical user interfaces.
- Can be applied as geospatial data store.
- Supports many programming languages
- Community driven and reliable
- Highly secure and Scalable.
- Inheritance.
Follow this post to learn how to install PostgreSQL on Debian 11 server.
How to Install PostgreSQL on Debian 11 Server
Add the PostgreSQL Repository
The latest version of PostgreSQL is not included in the Debian 11 default repository. So it is recommended to install PostgreSQL from their official repository.
First, install all required dependencies by running the following command:
apt-get install wget sudo curl gnupg2 -y
After installing all the dependencies, add the PostgreSQL repository and GPG key with the following command:
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
Once the repository is added, update it using the following command:
apt-get update -y
Once the PostgreSQL repository is up to date, you can proceed to install the PostgreSQL server.
Install PostgreSQL on Debian 11
You can now run the following command to install the latest version of the PostgreSQL server on your server.
apt-get install postgresql-14 -y
After the successful installation, start the PostgreSQL service and enable it to start after the system reboot:
systemctl start postgresql
systemctl enable postgresql
You can now check the running status of the PostgreSQL service using the following command:
systemctl status postgresql
You will get the following output:
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Wed 2022-05-11 16:45:59 UTC; 23s ago
Process: 71547 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 71547 (code=exited, status=0/SUCCESS)
CPU: 3ms
May 11 16:45:59 debian11 systemd[1]: Starting PostgreSQL RDBMS...
May 11 16:45:59 debian11 systemd[1]: Finished PostgreSQL RDBMS.
By default, PostgreSQL listens on port 5432. You can check it with the following command:
ss -antpl | grep 5432
You will get the PostgreSQL listening port in the following output:
LISTEN 0 244 127.0.0.1:5432 0.0.0.0:* users:(("postgres",pid=71529,fd=6))
LISTEN 0 244 [::1]:5432 [::]:* users:(("postgres",pid=71529,fd=5))
You can also check the PostgreSQL version using the following command:
sudo -u postgres psql -c "SELECT version();"
You will get the PostgreSQL version in the following output:
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.2 (Debian 14.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)
Interacting with PostgreSQL Database
This section will show you how to connect to the PostgreSQL shell and use the PostgreSQL commands to create and manage databases. First, connect to the PostgreSQL locally using the following command:
sudo -u postgres psql
You will get the following output:
psql (14.2 (Debian 14.2-1.pgdg110+1))
Type "help" for help.
postgres=#
To set the Postgres pasword, run the following command:
ALTER USER postgres PASSWORD 'password';
To create a database named testdb, run the following command:
CREATE DATABASE testdb;
To list all PostgreSQL databases, run the following command:
\l
You should see all databases in the following output:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
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
testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
To switch the database to testdb, run the following command:
\c testdb
To create a table named employee, run the following command:
CREATE TABLE employee (
employee_id INT PRIMARY KEY,
first_name VARCHAR (255) NOT NULL,
last_name VARCHAR (255) NOT NULL,
manager_id INT,
FOREIGN KEY (manager_id)
REFERENCES employee (employee_id)
ON DELETE CASCADE
);
To list all tables, run the following command:
\dt
You should see all tables in the following output:
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | employee | table | postgres
(1 row)
Now, insert some data into the PostgreSQL table using the following command:
INSERT INTO employee (
employee_id,
first_name,
last_name,
manager_id
)
VALUES
(1, 'Sandeep', 'Jain', NULL),
(2, 'Abhishek ', 'Kelenia', 1),
(3, 'Harsh', 'Aggarwal', 1),
(4, 'Raju', 'Kumar', 2),
(5, 'Nikhil', 'Aggarwal', 2),
(6, 'Anshul', 'Aggarwal', 2),
(7, 'Virat', 'Kohli', 3),
(8, 'Rohit', 'Sharma', 3);
Next, verify the inserted data using the following command:
SELECT * FROM employee;
You will get the following output:
employee_id | first_name | last_name | manager_id
-------------+------------+-----------+------------
1 | Sandeep | Jain |
2 | Abhishek | Kelenia | 1
3 | Harsh | Aggarwal | 1
4 | Raju | Kumar | 2
5 | Nikhil | Aggarwal | 2
6 | Anshul | Aggarwal | 2
7 | Virat | Kohli | 3
8 | Rohit | Sharma | 3
(8 rows)
To exit from the PostgreSQL shell, run the following command:
exit
Backup and Restore PostgreSQL Database
PostgreSQL comes with a pg_dump and pg_restore utility that allows you to backup and restores a database from the backup file. There are several ways to backup a PostgreSQL database.
For example, to create a backup of the PostgreSQL database named testdb in the tar format, run the following command:
pg_dump -U postgres -F c testdb > testdb_backup.tar
To create a backup in a directory format, run the following command:
pg_dump -U postgres -F d testdb > testdb_backup
If your database is very large and wants to generate a small backup file then you can use pg_dump with a compression tool such as gzip to compress the database backup.
pg_dump -U postgres testdb | gzip > testdb.gz
PostgreSQL also provides a pg_dumpall utility that allows you to backup all databases using a single command. For example, to backup all databases, run the following command:
pg_dumpall -f pgdumpall.sql
The above command will dump all databases to a single file named pgdumpall.sql
You can use the pg_restore command to restore a PostgreSQL database.
For example, to restore a database named testdb from the backup file testdb_backup.tar, run the following command:
pg_restore -d testdb testdb_backup.tar
To restore all PostgreSQL databases, run the following command:
pgsql -f pgdumpall.sql
Great work! We have learned how to install PostgreSQL on Debian 11 Server.
How to Install PostgreSQL on Debian 11 Server Conclusion
PostgreSQL is an enterprise class database management system most often used in the Financial Industry, Government GIS data, Manufacturing, Web technology, and NoSQL and for Scientific Data collection works. One of the main PostgreSQL characteristics is that it can run dynamic websites and web apps as an option to the LAMP stack.
In this guide, we explained how to install the PostgreSQL server on Debian 11 server. We also explained how to create a database and table in PostgreSQL. We also use pg_dump and pg_restore utility to backup and restore a PostgreSQL database.
