Q1.How to list all databases in PostgreSQL database cluster?
select * from pg_database; will provide list of all databases in cluster.
Q2.How to find the database size?
We can use pg_database_size(database name) function to find the database size.
Q3.How to create a new database in PostgreSQL?
We can create new database with psql command “Create database databasename;”
Q4. How to start PostgreSQL cluster?
You can use pg_ctl start -D $PGDATA to start PostgreSQL cluster.
Q5.How to stop PostgreSQL Server ? what are different options/modes to stop PostgreSQL Cluster?
You can use pg_ctl stop -D $PGDATA -m s/f/i
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.
Q6.How to stop a particular database in PostgreSQL cluster?
PostgreSQL doesn’t allow to stop/start a particular database in PostgreSQL cluster.
Q7. What is PostgreSQL role? what are the default roles in PostgreSQL?
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.
Q8.What is PostgreSQL user?
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.
Q9.How to convert a role to user in PostgreSQL?
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.
Q10.What is the difference between user and role in PostgreSQL?
In PostgreSQL a user is a role with login privilege or a role is a user without login privilege.
Q11. What Is Postgresql?
This is regarded as one of the most a success open source database in the global. This is also used to create advanced applications. This is predicated on Object relational database control device. Familiarity with UNIX and Linux can be an delivered advantage at the same time as the usage of PostgreSQL.
Q12. State Some Of The Advanced Features Of Postgresql?
These are the following features which can be present in PostgreSQL they're
1) Object relational database
2) Extensibility and help for SQL
3) Database validation and bendy API
4) Procedural languages and MVCC
five) Client server and WAL.
Oracle Interview Questions
Q13. Explain About Write Ahead Logging?
This characteristic increases the reliability of the database by using logging adjustments earlier than any adjustments or updations to the information base. This provides log of database incase of a database crash. This helps to begin the paintings from the point it changed into discontinued.
Q14. Explain About Multi Version Concurrency Control?
Multi model concurrency manipulate or MVCC is used to keep away from useless locking of the database. This removes the time lag for the user to log into his database. This function or time lag takes place whilst a few one else is at the content. All the transactions are kept as a report.
Q15. How To Start The Database Server?
/usr/nearby/and so forth/rc.D/010.Pgsql.Sh begin
MySQL Interview Questions
Q16. How To Stop The Database Server?
/usr/nearby/and so on/rc.D/010.Pgsql.Sh forestall
/usr/neighborhood/and many others/rc.D/postgresql stop
Q17.What is the process of splitting a large table into smaller pieces called in PostgreSQL?
It is called table partitioning.
Q18.What is a partitioned table in PostgreSQL?
The partitioned table is a logical structure. It is used to split a large table into smaller pieces, which are called partitions.
Q19.What purpose does pgadmin in PostgreSQL serve?
The pgadmin in PostgreSQL is a data administration tool. It serves the purpose of retrieving, developing, testing, and maintaining databases.
Q20. List some of the features of Postgresql ?
Following are some of the major features of Postgresql :
- Object-relational database
- Supports major Operating systems
- Support Extensibility for SQL and Complex SQL queries
- Nested transactions
- Flexible API and Database validation
- Multi-version concurrency control (MVCC) and Procedural languages
- WAL and Client server
- Table inheritance & Asynchronous replication.