Getting started with PostgreSQL in Debian based Linux

In this tutorial, we’ll learn how to install and get started with PostgreSQL on Debian based Linux such as Ubuntu or MX Linux. Instructions given here were tested on MX Linux 23 which is based on Debian 12 (bookworm); therefore, these instructions would generally be applicable to any Debian based Linux.

So, let’s open terminal and enter following at command prompt to update system first:

sudo apt update && sudo apt upgrade

And then install PostgreSQL by entering:

sudo apt install postgresql

That’s it! MX Linux 23 repos contain PostgreSQL version 15 which will be installed by executing the above command. We can check the status of PostgreSQL by entering following command:

sudo service postgresql status

We may stop / start PostgreSQL using following command:

sudo service postgresql stop / start

Now let’s create a user and database.

First, log into psql shell using the default credentials (Enter password for sudo when prompted):

sudo -u postgres psql

Once in psql shell, enter following command to create a user:

CREATE ROLE myuser LOGIN PASSWORD 'mypass';

And, then enter following command to create a database and assign the user to it:

CREATE DATABASE mydatabase WITH OWNER = myuser;

Finally, type exit and press Enter to quit psql shell. Thereafter, you can log into the respective database using the assigned user (when prompted, enter user’s password):

psql -U myuser -d mydatabase -h localhost

Once logged in, you can do the usual SQL stuff – e.g., to create a table, enter following command:

CREATE TABLE cars (brand VARCHAR(255), model VARCHAR(255), year INT);

Insert a record into cars table:

INSERT INTO cars (brand, model, year) VALUES ('Ford', 'Mustang', 1964);

Query the database:

SELECT * from cars;

Now, let’s briefly look into some of the PostgreSQL essential commands. Remember, PostgreSQL commands are preceded by backslash (\).

To get the list of tables inside the database, enter following command:

\dt

To show the structure of a table, enter:

\d cars

You may enter following command to get list of all the databases:

\l 

You can enter \h to get help command list (Press q to exit from this help list). You can enter following command to get help for a specific command:

\h drop table 

Finally, you may also enter quit or \q commands to exit the psql shell.