PostgreSQL Online Test

1. What is "streaming replication" in PostgreSQL?

A method of replicating data by streaming changes from the primary to the standby server A method for optimizing query performance A method for creating new databases A method for taking database backups

2. What is the primary goal of continuous archiving and point-in-time recovery (PITR) in PostgreSQL backup and restore?

To provide a way to recover the database to a specific point in time To create an exact copy of the database To optimize query performance To create daily backups

3. What PostgreSQL extension provides additional NoSQL-like features, such as key-value store and document storage?

"hstore" "pgCube" "cstore_fdw" "hstore_fdw"

4. Which PostgreSQL extension is used to provide enhanced geospatial functions and data types for advanced GIS applications?

"PostGIS" "pgBouncer" "pg_dump" "psql"

5. What is a "standby server" in PostgreSQL replication?

A replica server that can take over as the primary in case of a failure A server for creating new databases A server for query optimization A server used for database backups

6. What is the purpose of the 'initdb' command in PostgreSQL?

It initializes a new database cluster It installs PostgreSQL on Windows It encrypts data at rest It compiles user-defined functions

7. Which command is used to create a new PostgreSQL user?

CREATE USER ADD USER INSERT USER USER CREATE

8. What is the purpose of the "pg_stat_statements" module in PostgreSQL?

It tracks SQL statement execution and helps identify performance issues It controls access to database objects It generates SQL code for optimization It encrypts data at rest

9. What is the role of the "ROLLBACK" statement in PostgreSQL transactions?

To undo changes made during a transaction and restore the original state To save changes to the database To start a new transaction To lock the database for exclusive access

10. Which PostgreSQL feature is often used for handling large volumes of data in data warehousing?

Partitioning Indexing Row-level security Triggers

11. What does the "FULL OUTER JOIN" do in a PostgreSQL query?

Retrieves all records where there is a match in either the left or right table Retrieves records that exist in both the left and right tables Retrieves records that exist only in the left table Retrieves records that exist only in the right table

12. Which SQL statement is used to insert new records into a table in PostgreSQL?

INSERT INTO ADD NEW CREATE RECORD APPEND

13. What is the default port number for PostgreSQL?

5432 3306 1521 27017

14. Which of the following data types is not supported by PostgreSQL?

JSON XML BSON CSV

15. What is the recommended approach for securing PostgreSQL connections when data transmission security is critical?

Enabling SSL/TLS encryption for client-server communication Using password authentication for all clients Creating strong database indexes Implementing certificate-based authentication

16. What is the primary advantage of connection pooling in a multi-user PostgreSQL environment?

It helps manage and optimize the usage of limited database connections It enhances data encryption It enables complex SQL queries It creates additional databases

17. Which tool is used to configure and manage connection pooling in PostgreSQL?

"pgBouncer" "pgAdmin" "psql" "pg_dump"

18. What is a "query planner" in PostgreSQL and how does it impact performance?

It generates query execution plans and can significantly affect query performance It stores query results for future use It optimizes the storage of large tables It automatically tunes the database

19. What is the "jsonb_set" function used for in PostgreSQL's NoSQL features?

To modify JSONB data by adding or replacing key-value pairs To encrypt data during transmission To optimize query performance To create new databases

20. What SQL operator is used to combine the result sets of two or more SELECT statements in PostgreSQL?

UNION JOIN COMBINE MERGE