Important PostgreSQL DBA Commands

In this post, I am sharing few important and basic commands for Database Administrator of PostgreSQL and PSQL Console.

Below are the important PSQL command:

To list all databases
\l
 
To list help for all commands
\?

To list all table
\d

To list all functions
\df

To list all aggregate functions
\da

To list all schemas
\dn 

To list all users
\du

To reset the query buffer
\r 

To list all table space
\db 

To list the privileges
\dp 
 
To show current data directory.
SHOW data_directory;

To change current data directory and it requires a server restart. 
SET data_directory TO NewPath;

To show current server configuration setting.
SHOW ALL; SELECT name, setting, unit, context FROM pg_settings;

Check the version of PostgreSQL:
SELECT version();
 
Create a database using the script:
CREATE DATABASE Database_Name WITH OWNER User_Name;
 
Change the owner of the database:
ALTER DATABASE Database_Name OWNER TO User_Name;
 
Create a full copy of the database with structure and data:
CREATE DATABASE NewDB WITH TEMPLATE OldDB;
 
Create a database User using the script:
CREATE USER Anvesh WITH password 'testPassword';
 
Change the password of the User:
ALTER USER postgres WITH PASSWORD 'temproot';

Upgrade the existing User to Superuser:
ALTER USER TestUser WITH SUPERUSER;
 
Reload the PostgreSQL configuration file:
SELECT  pg_reload_conf();
 
Rotate the PostgreSQL Log(pg_log) file:
SELECT pg_rotate_logfile();
 
Find the Postgres server start time:
SELECT pg_postmaster_start_time();