Important PostgreSQL commands /PSQL Commands that you must know as a DevOps/Database Admin. I would recommend you to also read my earlier article for  PostgreSQL Installation.

Read : How to Install PostgreSQL from source on Linux

&& How to install Redis from source

PostgreSql Commands/ PSQL Commands

1) Connecting Postgres Database

Before connecting Postgres Database we must find out the location of PSQL so as to connect it.Use following command to grep the location.

 $ which psql

Or you can search the PSQL location using find command

$ find / -name psql

Switch to PostgreSQL User

$ su – postgres

Connect Postges Database

$ /usr/bin/psql -d database_name -U database_user -p 5432

Where 5432 is the default Port Number of PostgreSql

Or Simply Use following command to Connect to Default Database(Postgres) with default User Postgres

$ psql 

or

$ /usr/bin/psql

Enter q  or press Ctrl + d to exit from Postgres Database.

2) Creating User in Postgres 

postgers=# create user testuser with password ‘password123’ ;

3) Changing DB User’s Password

 postgres=# alter user testuser with password ‘[email protected]’;

4) Grant user permission to DB Schema

Grant Select Permission

postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;

Grant ALL Permission

postgres=# GRANT ALL ON ALL TABLES IN SCHEMA public TO username;

5) Granting Super User permission.

postgres=# alter user username with superuser;

6) Reload database

We can reload database instead restarting it,  after any configuration changes so as to reflect the changes without any downtime/database restart.

$ /usr/local/pgsql/bin/pg_ctl -D /viadata/pg965/data/ reload

Note: Path of  pg_ctl and data  may vary , so you can use find command to find the exact location of both.

          $ find / -name data

/viadata/pg965/data/

          $ find / -name pg_ctl

/usr/local/pgsql/bin/pg_ctl

7) Create Database

postgres=#  CREATE DATABASE dbname;

8) Delete Database

postgres=#  DROP DATABASE dbname;

9) Connecting Postges Database from remote Machine

Use following command to connect PostgreSql from Remote hosts.Before connecting make sure to allow remote hosts IP/Network in hba_conf to connect PostgreSQL remotely. Also create a inbound rule to allow access PostgreSql Port for Remote host IP/Network  in Local System Firewall /Nework Firewall/Cloud Security Group according to your infrastructure and requirement.

hba_conf is located in the data directory of Postgres Database. You can use locate or find command to find out PosgreSql data directory and hba_conf file.

 $ find / -name data

/viadata/pg965/data/

Open the hba_conf folder using vi or nano command.

$ nano /viadata/pg965/data/hba_conf

Add the host entry according to your requirement and save the file

host         all                        all                              172.0.0.1/32                              trust

——> This rule allowing  all database user to connect all database on Database Server locally without passing  password(trust)

host        appdb                 app_usr                  10.10.1.0/24                                 trust

——> This rule allowing app_user to connect appdb database from the 10.10.1.0/24 network without passing password(trust)

host        postgres              dba_user                10.10.2.1/32                                trust

——> This rule allowing  dba_user to connect posgtres database from the host having IP Address 10.10.2.1/32 without passing password(trust)

host                   appdb               dev_user         10.10.3.1/32                                  md5

——-> This rule allowing dev_user to connect appdb database  from a remote host directly or using VPN with Host IP/VPN Server IP 10.10.3.1/32  by passing password(md5)

host                    replicaton       app_user          10.10.100.1/32                          trust

——-> This rule allowing app_user for database replication to Replica DB(IP Address 10.100.100.1/32) without password(trust)

host                       all                 left_user                   0.0.0.0/0                                reject

——-> This rule rejecting to connect all databases using left_user from anywhere worldwide.

Note: After saving file reload the database to take changes in effect.

10) Check Database size

postgres=# SELECT pg_size_pretty(pg_database_size(‘dbname’));

Where dbname is name of the database whose size you need to calculate.

11) Take Postgres DB backup

$ /usr/local/pgsql/bin/pg_dump -U postgres -p 5432 pg_db > /home/postgres/bsql/pg_db_bakup.sql

Note: Here pg_db is the name of the database whose backup you need to take.And pg_db_backup.sql is the name of the backup file.

12) Restore DB backup

$ /usr/bin/psql db_name < pg_db_bakup.sql

Note: Where db_name is the name of the database where you want to restore the backup.You need to first create the database by CREATE DATABASE db_name; command then have to restore the DB.

13) Stop and Start Database and Check Status

$ /usr/local/pgsql/bin/pg_ctl -D /viadata/pg965/data/ stop

$ /usr/local/pgsql/bin/pg_ctl -D /viadata/pg965/data/ start

$ /usr/local/pgsql/bin/pg_ctl -D /viadata/pg965/data/ status

14) Stop Database Forcefully

$ /usr/local/pgsql/bin/pg_ctl -D /viadata/pg965/data/ -mf stop

15) Check NO. of  Connection on DB

 postgres=# select count(*) from  pg_stat_activity;

 

Click to tweet this tip !

I hope you enjoyed this tutorial and learned most Important PostgreSql commands or PSQL Commands. If you think this is helpful, please do share this post with others . Please also share your valuable feedback, comment or any query in the comment box. I will really happy to resolve your all queries .

Thank You