This tutorial walks you through the steps of installing the PostgreSQL database server on Debian 10\. We'll also explore the fundamentals of basic database administration.
PostgreSQL, often known simply as Postgres, is an open-source general-purpose object-relational database management system. It has many powerful features such as online backups, point in time recovery, nested transactions, SQL and JSON querying, multi-version concurrency control (MVCC), asynchronous replication, and more. This tutorial walks you through the steps of installing the PostgreSQL database server on Debian 10. We'll also explore the fundamentals of basic database administration.
At the time of writing this article, the latest version of PostgreSQL available from the default Debian repositories is PostgreSQL version 11.5.
To install PostgreSQL on your Debian server perform the following steps as root or user with sudo privileges:
Start by updating the APT package index:
sudo apt update
Install the PostgreSQL server and contrib package which provides additional features for the PostgreSQL database:
sudo apt install postgresql postgresql-contrib
Once the installation is complete, the PostgreSQL service will start. To verify the installation, use the
psql tool to print the server version:
sudo -u postgres psql -c "SELECT version();"
The output should look something like the following:
PostgreSQL 11.5 (Debian 11.5-1+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
PostgreSQL handles database access permissions using the concept of roles. Depending on how you set up the role, it can represent a database user or a group of database users.
PostgreSQL supports several authentication methods. The most frequently used methods are:
PostgreSQL client authentication is defined in the configuration file named
pg_hba.conf. For local connections, PostgreSQL is set to use the peer authentication method.
The “postgres” user is automatically created when PostgreSQL is installed. This user is the superuser for the PostgreSQL instance, and it is equivalent to the MySQL root user.
To log in to the PostgreSQL server as “postgres”, switch to the user postgres and access a PostgreSQL prompt using the
sudo su - postgres psql
From here you can interact with the PostgreSQL server. To exit out of the PostgreSQL shell type:
You can use the
sudo command to access the PostgreSQL prompt without switching users:
sudo -u postgres psql
postgres user is typically used only from the localhost.
createuser command allows you to create new roles from the command line. Only superusers and roles with
CREATEROLE privilege can create new roles.
In the following example, we'll create a new role named
kylo, a database named
kylodb and grant privileges on the database to the role.
First, create the role by issuing the following command:
sudo su - postgres -c "createuser kylo"
Next, create the database using the
sudo su - postgres -c "createdb kylodb"
To grant permissions to the user on the database, connect to the PostgreSQL shell:
sudo -u postgres psql
Run the following query:
``` grant all privileges on database kylodb to kylo; ```
By default, the PostgreSQL, server listens only on the local interface
If you want to connect to the PostgreSQL server from remote locations, you need to set the server to listen on the public interface and edit the configuration to accept remote connections.
Open the configuration file
postgresql.conf and add
listen_addresses = '*' in the
CONNECTIONS AND AUTHENTICATION section. This instruct the server to listen on all network interfaces.
sudo nano /etc/postgresql/11/main/postgresql.conf
#------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on;
Save the file and restart the PostgreSQL service for changes to take effect:
sudo service postgresql restart
Verify the changes with the
ss -nlt | grep 5432
LISTEN 0 128 0.0.0.0:5432 0.0.0.0:* LISTEN 0 128 [::]:5432 [::]:*
The output should show that the PostgreSQL server listens on all interfaces (0.0.0.0).
The last step is to configure the server to accept remote logins by editing the
Below are some examples showing different use cases:
# TYPE DATABASE USER ADDRESS METHOD # The user jane will be able to access all databases from all locations using an md5 password host all jane 0.0.0.0/0 md5 # The user jane will be able to access only the janedb from all locations using an md5 password host janedb jane 0.0.0.0/0 md5 # The user jane will be able to access all databases from a trusted location (192.168.1.134) without a password host all jane 192.168.1.134 trust
We've shown you how to install and configure PostgreSQL on Debian 10. For more information on this topic, consult the PostgreSQL Documentation.
Thank you for reading !
ScaleGrid announces support for their fully managed hosting plans on DigitalOcean for MySQL, PostgreSQL and Redis™. See how developers can improve performance. MySQL and PostgreSQL are the top two open source relational databases in the world, and Redis is the top key-value database
PostgreSQL Tutorial - Learn PostgreSQL Database from Scratch. Learn how to use PostgreSQL in this full course. PostgreSQL is a general purpose and object-relational database management system. It is the most advanced open source database system widely used to build back-end systems. What is a Database? What is SQL And Relational Database? What is PostreSQL AKA Postrgres? PostgreSQL Installation. How to Create Database.
Database Indexing Explained (with PostgreSQL). This is a practical video on Database Indexing where I explain what is an index, why do we need it and how it can improve the performance of the queries. Also how (if used incorrectly) it can slow down your queries
Learn Database Administration - PostgreSQL Database Administration (DBA) for Beginners, we will go over the basics of the PostgreSQL. We will cover topics ranging from installations, to writing basic queries and retrieving data from tables. We will also explore the logic of joining tables to retrieve data and much more.