10 Useful and Practical PostgreSQL Commands

useful and practical postgresql commands

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!

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:

Need a fast and easy fix?
Unlimited Managed Support
Supports Your Software
2 CPU Cores
2 GB RAM
50 GB PCIe4 NVMe Disk
1854 GeekBench Score
Unmetered Data Transfer
NVME 2 VPS

Now just $43 .99
/mo

GET YOUR VPS
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.

Leave a Comment

To prove you are human please solve the following *