20 PostgreSQL basic administrative commands you need to know

20 PostgreSQL basic administrative commands you need to know

In this tutorial we will be looking at a list of 20 PostgreSQL basic administrative commands you need to know, key commands that you will be using if you interact with the command line interface.

PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

 

20 PostgreSQL basic administrative commands you need to know

Accessing PostgreSQL Terminal

To begin you need to connect to PostgreSQL interactive terminal, we do this by running the command below on the linux shell if you are logged in as postgres os user.

psql

If not enter the command below with the appropriate parameters

psql -U username

e.g.

psql -U postgres

To connect as a next database user or directly to a database use the command below:

psql -d database -U username -W

psql -d testdb -U testuser -W

After connecting to the psql interactive shell, here are a list of commands you need to get familiar with so that you can properly navigate the database.

List all Databases [section label=”List all Databases”]
To list all databases on the cluster you are connected to, use the \l command:

\l

List Databases in postgresql

Connect to a database [section label=”Connect to a database”]
To connect to a database we use the \c command:

\c

\c connect to a postgresql database

List all tables [section label=”List all tables”]
Once connected to a database, our next tasks is to see what tables are in that particular database. To find this out we will execute the \dt command:

\dt

Describe a table [section label=”Describe a table”]
Now we are connected to our database and we know what tables are in that database, our next step is to know what that table is made up of. To find this out we will use the \d command or \d+ command for a more detailed view:

\d table_name
\d+ table_name

\d describe table

List Events [section label=”List Events”]
To see a list of event triggers run the command:

\dy

List Functions [section label=”List Functions”]
To see all available functions use the \df command:

\df

List Indexes [section label=”List Indexes”]
To get a list of indexes execute the \di command:

\di

List schemas [section label=”List schemas”]
To get a list of all schemas in the database execute the \dn command:

\dn

List all users [section label=”List all users”]
To list all users and their roles in the database, use the \du command:

\du

list postgresql database users

List views in the database [section label=”List Views”]
To list views in the current database use the \dv command:

\dv

List all data types [section label=”List Data Types”]
To see a list of all data types, run the command:

\dT+

List extensions installed [section label=”List extensions installed”]
To see a list of all extensions installed, run the command:

\dx

Quit [section label=”Quit”]
To quit the PostgreSQL interactive shell use the command:

\q

Reload database [section label=”Reload database”]
To reload a PostgreSQL database after changes were made to the pg_hba.conf file run the command below:

Select pg_reload_conf();

Service management commands [section label=”Service management commands”]
If you need to stop start or restart the database please see the commands to use below.

sudo service postgresql stop
sudo service postgresql start
sudo service postgresql restart