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
\l
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
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.
Please check part2 of administration to get more standard handy sql queries .
https://dbachamps.com/postgresql-administration-commands-version-910111213-part2/
