Home PostgreSQL PostgreSQL Administration Commands – (Version 9,10,11,12,13)-Part1

PostgreSQL Administration Commands – (Version 9,10,11,12,13)-Part1


This post is intended for all Database Administrators who needs handy sql scripts for their daily monitoring activity. I will try to cover some of the basic commands needed for PostgreSQL database administration.

Connect to the database postgres

psql -U postgres -d postgres -h localhost -p 5432

List Databases


Change Database or connect to different database

\c <dbname>

List schemas within the connected database

List Login or roles created in the server

List of Objects present in the database(all schemas)

Find Out List of Tables

Get definition of a table along with its indexes

List of Indexes present in the database

List of Sequences present in the connected database

Access and Column privilege of each object

List of Partitioned Objects

List of tablespaces along with location details

Show settings value of all parameters in the database

List views

List installed extensions

List created triggers

List all function names with argument details

Display a function definition

List created views

List view Definition

Edit Function definition in an editor and save

dvdrental=# \ef film_in_stock
opens in external editor. Edit and save for any change.

Append all query output in an output file

\o abc.out

Setting with \o and then file will send all subsequent query out to file abc.out. Same as spool in oracle.

Show <parameter name> will display current value set for that parameter

Please check part2 of administration to get more standard handy sql queries .