15 Important PostgreSql commands you must know
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.
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
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
$ find / -name 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
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 22.214.171.124/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 .