In this tutorial, we are going to explain ten useful and practical PostgreSQL commands in Linux.
PostgreSQL is a very powerful object-relational database management system. It is SQL complained designed to handle many concurrent users and a range of workloads. PostgreSQL is compatible with different operating systems such as macOS, Windows, FreeBSD, and Linux, which this blog post will explain.
We will install PostgreSQL on Ubuntu 22.04, but you can choose any Linux distro you want. Let’s get started!
Table of Contents
Prerequisites
- Fresh install of Ubuntu 22.04
- User privileges: root or non-root user with sudo privileges
Update the System
Every fresh installation of Ubuntu 22.04 needs a system update. To update the system packages execute the following commands.
sudo apt update -y && sudo apt upgrade -y
PostgreSQL Installation
To install PostgreSQL execute the following command:
sudo apt install postgresql -y
Once installed, start and enable the PostgreSQL service:
systemctl start postgresql.service && systemctl enable postgresql.service
To check the status of the service:
systemctl status postgresql.service
If everything is ok, you should get the following output:
root@host:~# systemctl status postgresql.service
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Tue 2022-10-18 17:22:36 CDT; 38s ago
Main PID: 307278 (code=exited, status=0/SUCCESS)
CPU: 3ms
Oct 18 17:22:36 host.test.vps systemd[1]: Starting PostgreSQL RDBMS...
Oct 18 17:22:36 host.test.vps systemd[1]: Finished PostgreSQL RDBMS.
Now, when the PostgreSQL database service is installed, we can start with the PostgreSQL commands.
1. Log in to PostgreSQL
To log in to the PostgreSQL admin command line, execute the following command:
sudo -u postgres psql
Once logged in, you should receive the following output:
root@host:~# sudo -u postgres psql could not change directory to "/root": Permission denied psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1)) Type "help" for help. postgres=#
2. List databases
To list the database in Postgres, execute the following command:
\l
It is a simple command \l which will retrieve all databases in a table like this:
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
3. Get the size of the Database
To get the size of the postgres database, you need to connect to it first. To do that, execute the following command:
\c postgres
Once connected, you should receive the following output:
postgres=# \c postgres You are now connected to database "postgres" as user "postgres".
Now, we can check the size of the current (postgres) database
SELECT pg_database_size(current_database());
This will return the size in bytes
postgres=# SELECT pg_database_size(current_database());
pg_database_size
------------------
8774435
(1 row)
If you want the output to be in the human-readable format in Kilobytes, Megabytes, or Gigabytes execute the following command:
select pg_size_pretty(pg_database_size(current_database()));
Now, the output should look like this:
pg_size_pretty ---------------- 8569 kB (1 row)
4. List Users and assigned Roles
To list the users and their assigned roles, execute the following command:
\du
You should receive the table as output with the PostgreSQL user and its privileges as described below:
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
5. Creating Database and User in PostgreSQL
To create a new database in PostgreSQL, execute the following command:
create database testdb;
Once the database is created, you will receive the following output:
postgres=# create database testdb; CREATE DATABASE
To create a new user with a password, execute the following command:
CREATE USER testuser WITH ENCRYPTED PASSWORD 'StrongPasswordHere';
Once the user is created, you will receive the following output:
postgres=# CREATE USER rosehosting WITH ENCRYPTED PASSWORD 'StrongPasswordHere'; CREATE ROLE
Now, let’s add privileges on the created database to the newly created user.
grant all privileges on database testdb to testuser;
Now, if you list the databases with the \l command, you will receive the following lines:
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+----------------------
testdb| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | testuser=CTc/postgres
6. Connect to the database with the assigned User
To connect to a database under a specific user, execute the following command:
\c testdb testuser
Enter the password, and you will receive the following output:
postgres=# \c testdb testuser; Password for user testuser: You are now connected to database "testdb" as user "testuser". testdb=>
7. Change the Password of the User
To change the password of a user, execute the following command:
ALTER USER testuser WITH PASSWORD 'NewPasswordHere';
Once the password is changed, you should receive the following as output:
postgres=# ALTER USER testuser WITH PASSWORD 'NewPasswordHere'; ALTER ROLE
8. Reload PostgreSQL Configuration
To reload the PostgreSQL configuration without restarting the server execute the following command:
select pg_reload_conf();
Once restarted, you will get the following output:
postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)
9. Check the PostgreSQL version
To check the PostgreSQL version via the postgres command line, execute the following command:
SELECT version();
You should receive output similar to this:
postgres=# SELECT version();
version
---------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit
(1 row)
10. Exit the PostgreSQL command line
To exit from the PostgreSQL command line, execute the following command:
\q
That’s all. We showed you some useful and practical PostgreSQL commands used daily by system administrators, developers, and some regular users. If you find it difficult to manage your PostgreSQL on your own, feel free to contact us anytime you want. We are available 24/7. All you need to do is to sign up for one of our NVMe VPS plans and submit a support ticket.
If you liked this post about ten useful and practical PostgreSQL commands, please share it with your friends on social networks or simply leave a reply below.