How to get started with PostgreSQL on GNU/Linux

PostgreSQL is a relational database system like MySQL or other SQL-like database systems.

PostgreSQL is open-source and used as a primary database for many web applications, as well as mobile and analytics applications. The system supports the most popular programming languages.

In this shot, you will learn how to use PostgreSQL on your UNIX computer.

Let’s install it in our machine.

Installation

PostgreSQL is included in your system packet manager by default; so, you can use your distribution packet manager to install it. I am on Debian and used apt by default.

sudo apt update && sudo apt install postgresql-12 # to get postgresql v12

You can also use backportsif you have enabled them in your system to get the latest version. Personally, I prefer to use the PostgreSQL Apt Repository to ensure that I have the latest version and don’t miss important updates.

The steps to use postgreSQL apt repository are as follows:

# Create the file repository configuration:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# update && install
sudo apt update && sudo apt install postgresql

Congratulations, you have installed PostgreSQL. You can check the connection status of the database server with pg_isready utility. The postgres service has also started by default. You can check its status with systemctl.

systemctl status postgresql

First-time connection to PostgreSQL

The admin database user is postgres and the CLI utility to use is psql. The syntax to connect to the server is this:

psql [database name] database user

or

psql -U database user [database name]

In our case, we have postgres, which also has a default database named postgres.

psql -U postgres

But what do we get? An error.

psql: error: could not connect to server: FATAL:  Peer authentication failed for user "postgres"

Note: If you don’t have the database argument on connection, PostgreSQL will try to connect a database that has the same name as the username. Why? As there’s no password set yet. On most Unix systems, the connection to the server is turned off by default.

To allow connections, you need to follow these two steps:

• Set a password for the postgres user
• Allow local connections to PostgreSQL

Let’s start with the first step.

Setting a password for the postgres user

Switch to postgres user:

sudo -u postgres psql

Enter your system user password when prompted.

Set the password:

\password postgres

Type your secret and secure password twice as prompted by the tool.

To quit the postgres user, use \q or CTRL + D.

Note: You can also use passwd utility to set the password for the postgres user.

sudo passwd postgres

The next step is to allow local connections to the server.

Allowing local connections

The connection to PostgrSQL is controlled by the pg_hba.conf file. You need to locate this file in your system (locate pg_hba.conf). On Debian it is located at /etc/postgresql/12/main/pg_hba.conf (12 is the version of my server, yours may be different).

Open the configuration file with your preferred editor (I use nano here) with sudo privilege:

sudo nano /etc/postgresql/12/main/pg_hba.conf

Scroll down until you find the line shown below:

# Database administrative login by Unix domain socket
local all postgres peer

Change the peer method to md5. Save and close the file (CTRL + X and Y + Enter).

Then, find the line:

# IPv4 local connections:
host all 
all 127.0.0.1/32  peer

Change peer to trust:

• peer means it will trust the authenticity of the UNIX user, i.e., it wont ask for the password.

• md5 means it will always ask for a password, and it will validate it after hashing with md5.

• trust means anyone who can connect to the server is authorized to access the database.

You can now restart the service:

sudo systemctl restart postgresql

or

sudo service postgresql restart

Everything is now OK. So, we can try to connect to our database.

psql -U postgres

Once connected, you can get all connection info with this command: \conninfo.

Basic usage

If you’re familiar with other SQL-like database systems, like MySQL or MariaDB, you’ll be a bit confused with PostgreSQL specific commands. But don’t worry too much, PostgreSQL also supports SQL commands.

Let’s go.

Create a new user or database

I usually prefer to create a new role for each database I have to create.

Warning: You should avoid using the admin user postgres for non-admin tasks.

Connect as an admin user to PostgreSQL (psql -U postgres) and type:

CREATE USER kali WITH PASSWORD 's3cr3T' CREATEDB;

Here, we create a new account and grant it the create database role. Make sure you use a secure password. To see the list of users, use the command \du.

Let’s also create a new database and grant all privileges on it to our new user.

CREATE DATABASE test_db;

Now, we can grant permissions:

GRANT ALL PRIVILEGES ON DATABASE test_db TO kali;

Nothing tricky here. Our newly created user now has all privileges on the newly created database test_db.

If you want to connect to the kali user without quitting PostgreSQL, you can use the \c command like this:

\c test_db kali

Connect to database test_db as user kali.

Note: To drop a database that you’re the owner of, use:

DROP DATABASE name

Create or delete a table

The syntax is as follows:

CREATE TABLE [IF NOT EXISTS] table_name (
   column1 datatype(length) column_contraint,
   column2 datatype(length) column_contraint,
   column3 datatype(length) column_contraint,
   table_constraints
);

Here, datatype can be serial, VARCHAR, etc., and constraints can be PRIMARY KEY, UNIQUE, etc.

Here’s a real example:

CREATE TABLE users (
	id serial PRIMARY KEY,
	username VARCHAR ( 50 ) UNIQUE NOT NULL,
	password VARCHAR ( 50 ) NOT NULL,
	email VARCHAR ( 255 ) UNIQUE NOT NULL
);

If we want to delete this table, we’ll use this syntax:

DROP TABLE IF EXISTS name;

If other tables depend on it, use CASCADE:

DROP TABLE IF EXISTS name CASCADE;

Note: To get the description of a table, use \d table_name.

Table: insert, read, update and delete entries

In this section, we want to learn how to work with entries (data) in a table.

Insertion

The syntax to insert data into a table is:

INSERT INTO table_name(column1, column2, …)
VALUES (value1, value2, …);

Let’s insert 5 entries into our table (users):

INSERT INTO users(username, password, email)
VALUES
('bam', 'B@m:321', 'bam@ulikis.cd'),
('salimas', 'sal1989', 'salimas@gmail.com'),
('pati', 'kav87', 'reseacher@unikis.cd'),
('yves', '1v3s:321', 'yves@yves.com'),
('val', 'valnas123', 'valnas@gmail.com')
;

Here, we insert multiple entries at once. We have ignored the id column because PostgreSQL will automatically handle it.

Read

To read entries, use the SELECT command:

SELECT * FROM users;

To display all entries from the users table:

SELECT * FROM users ORDER BY username;

This command will order the output by username within an existing column in the table.

Update

Imagine that a user named pati changes their email address. We would then want to modify it in our database too. So, how can we proceed? We cannot use the INSERT command because the user is already in the database. Instead, we will use the UPDATE command:

Delete

Use the DELETE command like this:

DELETE FROM table_name
WHERE condition;

To illustrate this, let’s say we want to delete the fourth entry, id 4:

DELETE FROM users
WHERE id=4;

Here, I use the id column. If I wanted to use the username column, I’d write it like this:

DELETE FROM users
WHERE username='yves';

Let’s recap

In this shot, we have learned how to set up PostgreSQL in a UNIX-like machine, and how to use basic commands like CREATE USER/DATABASE/TABLE, DROP DATABASE/TABLE, and more.

Note: Never forget to terminate your commands with ;.