How to Install PostgreSQL database server on Debian 10

How to Install PostgreSQL database server on Debian 10

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.

Installing PostgreSQL

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:

  1. Start by updating the APT package index:

    sudo apt update
    
  2. Install the PostgreSQL server and contrib package which provides additional features for the PostgreSQL database:

    sudo apt install postgresql postgresql-contrib
    
  3. 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 Roles and Authentication Methods

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:

  • Trust - A role can connect without a password, as long as the criteria defined in the pg_hba.conf are met.
  • Password - A role can connect by providing a password. The passwords can be stored as scram-sha-256``md5andpassword (clear-text)
  • Ident - Only supported for TCP/IP connections. It works by obtaining the client's operating system user name, with an optional user name mapping.
  • Peer - Same as Ident, but it is only supported on local connections.

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 psql utility:

sudo su - postgres
psql

From here you can interact with the PostgreSQL server. To exit out of the PostgreSQL shell type:

\q

You can use the sudo command to access the PostgreSQL prompt without switching users:

sudo -u postgres psql

The postgres user is typically used only from the localhost.

Creating PostgreSQL Role and Database

The 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.

  1. First, create the role by issuing the following command:

    sudo su - postgres -c "createuser kylo"
    
  2. Next, create the database using the createdb command:

    sudo su - postgres -c "createdb kylodb"
    
  3. 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;
    
Enable Remote Access to the PostgreSQL Server

By default, the PostgreSQL, server listens only on the local interface 127.0.0.1.

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

/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 utility:

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 pg_hba.conf file.

Below are some examples showing different use cases:

/etc/postgresql/11/main/pg_hba.conf

# 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

Conclusion

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 !

PostgreSQL Tutorial - Learn PostgreSQL Database from Scratch

PostgreSQL Tutorial - Learn PostgreSQL Database from Scratch

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.

Learn PostgreSQL Tutorial - Full Course for Beginners

Learn how to use PostgreSQL Database 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.

⭐️ Contents ⭐️
⌨️ (0:03:16) What is a Database
⌨️ (0:05:17) What is SQL And Relational Database
⌨️ (0:09:10) What is PostreSQL AKA Postrgres
⌨️ (0:10:53) PostgreSQL Installation (Mac OS)
⌨️ (0:14:21) PostgreSQL Installation (Windows)
⌨️ (0:17:38) GUI Clients vs Terminal/CMD Clients
⌨️ (0:21:39) Setup PSQL (MAC OS)
⌨️ (0:25:22) Setup PSQL (Windows)
⌨️ (0:30:15) How to Create Database
⌨️ (0:33:35) How to Connect to Databases
⌨️ (0:38:12) A Very Dangerous Command
⌨️ (0:41:37) How To Create Tables
⌨️ (0:45:46) Creating Tables Without Constraints
⌨️ (0:49:12) Creating Tables with Constraints
⌨️ (0:55:55) Insert Into
⌨️ (0:59:14) Insert Into Example
⌨️ (1:02:36) Generate 1000 Rows with Mockaroo
⌨️ (1:12:28) Select From
⌨️ (1:15:18) Order By
⌨️ (1:19:53) Distinct
⌨️ (1:21:59) Where Clause and AND
⌨️ (1:25:29) Comparison Operators
⌨️ (1:29:35) Limit, Offset & Fetch
⌨️ (1:32:43) IN
⌨️ (1:35:43) Between
⌨️ (1:37:45) Like And iLike
⌨️ (1:43:10) Group By
⌨️ (1:46:41) Group By Having
⌨️ (1:52:08) Adding New Table And Data Using Mockaroo
⌨️ (1:55:40) Calculating Min, Max & Average
⌨️ (1:59:48) Sum
⌨️ (2:01:55) Basics of Arithmetic Operators
⌨️ (2:05:59) Arithmetic Operators (ROUND)
⌨️ (2:09:43) Alias
⌨️ (2:12:32) Coalesce
⌨️ (2:16:15) NULLIF
⌨️ (2:20:21) Timestamps And Dates Course
⌨️ (2:23:21) Adding And Subtracting With Dates
⌨️ (2:25:58) Extracting Fields From Timestamp
⌨️ (2:27:28) Age Function
⌨️ (2:29:24) What Are Primary Keys
⌨️ (2:31:23) Understanding Primary Keys
⌨️ (2:36:26) Adding Primary Key
⌨️ (2:40:55) Unique Constraints
⌨️ (2:49:15) Check Constraints
⌨️ (2:54:45) How to Delete Records
⌨️ (3:01:36) How to Update Records
⌨️ (3:05:55) On Conflict Do Nothing
⌨️ (3:11:09) Upsert
⌨️ (3:16:41) What Is A Relationship/Foreign Keys
⌨️ (3:19:48) Adding Relationship Between Tables
⌨️ (3:25:04) Updating Foreign Keys Columns
⌨️ (3:29:30) Inner Joins
⌨️ (3:35:17) Left Joins
⌨️ (3:40:53) Deleting Records With Foreign Keys
⌨️ (3:47:27) Exporting Query Results to CSV
⌨️ (3:50:42) Serial & Sequences
⌨️ (3:57:18) Extensions
⌨️ (3:59:39) Understanding UUID Data Type
⌨️ (4:05:54) UUID As Primary Keys
⌨️ (4:16:30) Conclusion

Learn Database Administration - PostgreSQL Database Administration (DBA) for Beginners

In this video, 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.

The course also covers the basics of creating tables, storing data with data types, and working with expressions, operators, and strings.

Topics also includes:

Installing PostgreSQL

Loading sample database

Creating database and tables

Performing CRUD operations

Joining Tables

Using aggregate and analytic functions

Creating views and triggers

What you’ll learn

Install PostgreSQL Server

Load sample database

Create a database

Create a table

Insert data into tables

Update existing records inside a table

Delete Records in a table

Remove duplicate records

Query data from a table

Create a subquery

Get data from multiple tables

Create and manage roles

Create a view

Create tablespace

Backup and restore database

Filter and sort data

Use various operators

Use aggregate and analytic functions

Create triggers

Thanks for reading

If you liked this post, share it with all of your programming buddies!

Follow us on Facebook | Twitter

The Complete SQL Bootcamp

The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert

An Introduction to Queries in PostgreSQL

Build a Basic App with Spring Boot and JPA using PostgreSQL

Why We Moved From NoSQL MongoDB to PostgreSQL?

Learn Database Management System - Database modeling with Golang & PostgreSQL

Learn how to do all Database Management System directly from Golang to PostgreSQL.

Golang OR Go Programming language is modern programming language specialized in building server side applications.

Postgresql is world's most popular open source database management system.

Both are one of the best technologies to build server side modern applications.

So, In this course i will try to give introduction how you guys can use these both technologies togather to build robust backend services.

Remark: This series contains just golang and postgresql integration and workings, it doesn't contain how to build REST apis.

What you'll learn

  • People will learn how to connect to postgresql using golang and how to do DBMS directly from golang.

Thanks for reading

If you liked this post, share it with all of your programming buddies!

Follow us on Facebook | Twitter

Further reading about Golang & PostgreSQL

Google’s Go Essentials For Node.js / JavaScript Developers

Go Programming Language Tutorial | Golang Tutorial For Beginners | Go / Golang Crash Course

Learn Go Programming - Golang Tutorial for Beginners

MySQL vs PostgreSQL - What to Choose the Right Database for Your Project

Learn Database Administration - PostgreSQL Database Administration (DBA) for Beginners

Learn PostgreSQL - Full Course for Beginners