Basics of PostgreSQL: installation and usage
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
- First steps
- Create a table
- Populating a table
- View records
- Show table structure
- Copy table structure to new table
- Extra: using containers (podman/docker)
I am using Debian 11 as my operating system to install PostgreSQL. Install
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.
Create a user
Before creating a new user, ensure the default database cluster is running (online) by typing
# 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’):
Now you can start PostgreSQL client,
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;
createdbare the allowed permissions for our new user.
psql to go back to our Linux user.
- You can also type
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
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.
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');
select * from test_table;
Show table structure
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.
Latest tutorials and articles: