PostgreSQL /ˈpoʊstɡrɛs ˌkjuː ˈɛl/ is a relational database system like MySQL or other SQL-like database systems. It is open-source and used as a primary database for many web applications as well as mobile and analytics applications. PostgreSQL supports the most popular programming languages.
In this tutorial, I want to learn how you can use PostgreSQL in your UNIX-like computer. Hope that you’ll find this interesting!
First thing first, let’s install it in our machine.
PostgreSQL is included by default in your system packet manager. So, you can just use your distribution packet manager to install it. I am on Debian, I use
apt by default.
sudo apt update && sudo apt install postgresql-12 # to get postgresql v12
You can also use backports (if you have enabled in your system) to get the latest version.
I personally prefer to use the
PostgreSQL Apt Repository so that I ensure I have the latest version and don’t miss important updates.
The steps to use postgreSQL apt repository are as follow:
# 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. Also, the
postgres service is started by default. You can check its status with
systemctl status postgresql
For more information on installation, go here
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 have a default database named
psql -U postgres
But what do we get? An error.
psql: error: could not connect to server: FATAL: Peer authentication failed for user "postgres"
If you don’t the database argument on connection, PostgreSQL will try to connect a database which has the same name as the username
Why? Because there’s no password set yet. On most Unix-like 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
- Allow local connections to PostgreSQL
Let’s start by the first step.
Setting a password for the postgres user
sudo -u postgres psql
Enter your system user password when asked.
Set the password as follow:
Type your secure and secret password twice as the tool asks to do.
To quit the
postgres user, use
You can also use
passwdutility to set the password for
sudo passwd postgres
The next step is to allow local connections to the server.
Allowing local connections
Connections to PostgrSQL is controlled by the
pg_hba.conf file. You need to locate it 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 maybe different).
Open the configuration file with your preferred editor (I use nano here) with
sudo nano /etc/postgresql/12/main/pg_hba.conf
Scroll down till you find the line below
# Database administrative login by Unix domain socket local all postgres peer
Change the peer method to
md5. Save and close the file (
Y + Enter).
Also, find the line
# IPv4 local connections: host all all 127.0.0.1/32 peer
peermeans it will trust the authenticity of UNIX user hence does not ask for the password
md5means it will always ask for a password, and validate it after hashing with
trustmeans anyone who can connect to the server is authorized to access the database
You can now restart the service:
sudo systemctl restart postgresql
sudo service postgresql restart
Everything is OK now. Let’s try to connect to our database.
psql -U postgres
Once connected, you can get all connection info with this command:
If you’re familiar with other SQL-like database systems like
MariaDB you’ll be a bit confused with
PostgreSQL specific command. But don’t worry too much,
PostgreSQL also support
Create a new user or database
I usually prefer to create a new role for each database I’ve to create.
You should avoid to use the admin user (
postgres) for nonadmin tasks.
Connect as an admin user to
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 user, use the command
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 has all privileges on the newly created database
If you want to connect to
kali user without quitting
PostgreSQL you can use
\c command like this:
\c test_db kali
Connect to database
test_db as user
To drop a database that you’re the owner, use
DROP DATABASE name
Create or delete a table
The syntax is as follow…
CREATE TABLE [IF NOT EXISTS] table_name ( column1 datatype(length) column_contraint, column2 datatype(length) column_contraint, column3 datatype(length) column_contraint, table_constraints );
datatype can be
constraints can be
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
DROP TABLE IF EXISTS name CASCADE;
To get the description of a table use
Table: insert, read, update and delete entries
In this section, we want to learn how to work with entries (data) in a table.
The syntax to insert data into a table is
INSERT INTO table_name(column1, column2, …) VALUES (value1, value2, …);
Let’s insert 5 entries in our table (
INSERT INTO users(username, password, email) VALUES ('bam', 'B@m:321', 'email@example.com'), ('salimas', 'sal1989', 'firstname.lastname@example.org'), ('pati', 'kav87', 'email@example.com'), ('yves', '1v3s:321', 'firstname.lastname@example.org'), ('val', 'valnas123', 'email@example.com') ;
Here we insert multiple entries at once. We have ignored the
id column because PostgreSQL will automatically handle it.
To read entries we use the
SELECT * FROM users;
Displays all entries from the table
SELECT * FROM users ORDER BY username;
This command will order the output by
username (an existing column in the table).
Imagine that our user
pati has changed their email address. So, we want modify it in our database too. How can we proceed? We cannot use
INSERT command because the user is already in the database. The good command to use in this situation is
Use the command
DELETE like this:
DELETE FROM table_name WHERE condition;
To illustrate this, let’s say we want delete the fourth entry (which is id 4).
DELETE FROM users WHERE id=4;
Here I use
id column. If I wanted to use
username column, I’d write like this:
DELETE FROM users WHERE username='yves';
That’s all for today.
In this tutorial, we have learned how to set up
PostgreSQL in a UNIX-like machine and how to use basic commands like
DROP DATABASE/TABLE, and more.
You should never forget to terminate your commands with ;
It was an introductory course, so if you want learn more you can read the documentation.
Thank you for learning with me.