PostgreSQL DBA Interview Questions  

Question -1: How to list all databases in PostgreSQL database cluster?

Answer:

select * from pg_database; will provide list of all databases in cluster.

Question -2: How to find the database size?

 Answer:

  We can use pg_database_size(database name) function to find the database size.

Question -3: How to create a new database in PostgreSQL?

 Answer:

  We can create new database with psql command “Create database databasename;”

Question -4: How to start PostgreSQL cluster?

 Answer:

You can use pg_ctl start -D $PGDATA to start PostgreSQL cluster.

Question -5: How to stop PostgreSQL Server ? what are different options/modes to stop PostgreSQL Cluster?

Answer:

 You can use pg_ctl stop -D $PGDATA -m s/f/i

 where

s  -> smart     : No new connection will be allowed but existing connection will be allowed to complete corresponding tasks and exit. It’s default mode.

f  -> fast      : No new transaction will be allowed. PostgreSQL server will close and rollback current transactions, terminate all existing connection, perform checkpoint and then shutdown the cluster.

 i  -> immediate : Shut down immediately without checkpoint. Crash recovery will be performed on next start.

Question -6: How to stop a particular database in PostgreSQL cluster?

Answer:

 PostgreSQL doesn’t allow to stop/start a particular database in PostgreSQL cluster.  

Question -7: What is PostgreSQL role? what are the default roles in PostgreSQL?

 Answer:

Role is a PostgreSQL cluster entity which is used to group database permissions together. In other words, a role is a group of privileges which can be granted to another role or database user.

Question -8: What is PostgreSQL user?

Answer:

 User is a PostgreSQL cluster entity who can login to database, can own objects, can have set of privileges. In PostgreSQL cluster, role and user keywords are used interchangeably. Only difference is that user can login to database while role don’t have login privilege.

Question -9: How to convert a role to user in PostgreSQL?

 Answer:

We can grant login role to the target role to convert it to user.

 We can use below mentioned command.

 alter role <rolename > with login.

Question -10: What is the difference between user and role in PostgreSQL?

Answer:

          In PostgreSQL a user is a role with login privilege or a role is a user without login privilege.         

Want to go though PostgreSQL interview question complete package? You can get it here.