PostgreSQL is a relational database engine like MySQL and MariaDB. In this tutorial I will show you how to install it and the basic commands to start with PostgreSQL.

Table of Contents

Installation

I am using Debian 11 as my operating system to install PostgreSQL. Install postgresql using apt.

sudo apt install postgresql
  • This command will install the latest stable PostgreSQL version available on the repositories.
  • The default port for PostgreSQL is 5432/tcp.

First steps

Create a user

Before creating a new user, ensure the default database cluster is running (online) by typing pg_lsclusters:

# pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
13  main    5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log

A database cluster is a database storage area on disk. Clusters are an advanced topic so we are going to use the default for now. If the cluster status appear as ‘down’, run pg_ctlcluster <version> <cluster> start:

pg_ctlcluster 13 main start

You need to change to the postgres user in order to create your first ‘ordinary’ PostgreSQL user. Run this command as root (or using ‘sudo’):

su postgres

Now you can start PostgreSQL client, psql.

psql

To simplify, we are going to add a new user with the same name as the Linux user who is going to use it. For example, if your Linux user is ‘ricardo’, run:

create role ricardo login createdb;
  • login and createdb are the allowed permissions for our new user.

Exit from psql to go back to our Linux user.

exit;
  • You can also type \q to quit.

Press Ctrl + D or run exit on the command prompt to go back to the previous user. You can also run su <username> to change between users.

Create a database

Run psql postgres. ‘postgres’ is the default database. Inside psql, create a new database (e.g.: ‘testdb’):

create database testdb;

Now, to use that database, you need to quit from psql and enter again using the new database.

exit;

psql testdb

Create a table

# 'primary key' means the column needs to be not null and unique
create table my_table (id int primary key, name text not null);

# id will auto increment from 1
create table my_table_2 (id serial, name text);

Populating a table

insert into test_table values (1, 'Ricardo');

View records

select * from test_table;

Show table structure

\d test_table

Copy table structure to new table

create table my_table_2 (like my_table);

Extra: using containers (podman/docker)

You can run a PostgreSQL server inside a container using Docker or Podman. Just run this command to create and start the container:

docker run -d --name postgres-server -e POSTGRES_PASSWORD=my_password postgres

After that, start psql in the container:

docker exec -it postgres-server psql -U postgres

If you have any suggestion, feel free to contact me via social media or email.