The COPY ... TO command is the standard SQL method in PostgreSQL for exporting data from a table to a file. To meet the requirements, the WITH clause is used to specify options. The CSV option formats the output as Comma-Separated Values. The HEADER option, which is only available with the CSV format, explicitly instructs PostgreSQL to write a header line containing the column names as the first line in the output file.

A. This command correctly specifies the CSV format but omits the HEADER option, so the resulting file will not contain the column headers.

C. The syntax is invalid. Options such as DELIMITER and HEADER must be enclosed within a WITH (...) clause or follow the WITH keyword.

D. EXPORT is not a valid SQL command in PostgreSQL for this purpose. The correct command for exporting table data to a file is COPY.

1. PostgreSQL 16 Documentation, SQL Commands, COPY:

Section: COPY, "Synopsis" and "Parameters".

Content: The documentation explicitly shows the syntax COPY ... TO ... [ WITH ] ( option [, ...] ). It lists HEADER as an option that "Specifies that the file contains a header line with the names of each column... On output, the first line contains the column names from the table... This option is allowed only when using CSV format." This directly supports option B.

Link: https://www.postgresql.org/docs/current/sql-copy.html

2. PostgreSQL 16 Documentation, Chapter 7. Data Types, 7.4. The COPY Protocol:

Section: 7.4.1. COPY Command

Content: This section provides examples and further details on the COPY command's usage, reinforcing the syntax. It clarifies that COPY ... WITH CSV HEADER is the correct formulation for exporting a CSV with a header row.

Link: https://www.postgresql.org/docs/current/protocol-copy.html (Note: While this discusses the protocol, it references the SQL command syntax).

3. University of California, Berkeley - Data 100 Course Materials:

Source: Principles and Techniques of Data Science, Lecture 10: SQL II.

Content: University courseware on data science frequently covers database interactions. Materials often demonstrate exporting data for analysis. The standard command taught for exporting data from PostgreSQL to a CSV file with headers is COPY table TO 'file.csv' WITH (FORMAT CSV, HEADER); or the shorthand COPY table TO 'file.csv' WITH CSV HEADER;. This aligns with academic instruction.

The pgdump utility is used for creating logical backups of a PostgreSQL database. It provides several output formats specified by the -F (or --format) option. The c value for this option selects the "custom" format (-Fc). This format is a compressed, archive-style backup that is not human-readable but is highly flexible. It is specifically designed to be used with the pgrestore utility, which allows for selective restoration of database objects and reordering of items during the restore process. The custom format is compressed by default, fulfilling the requirements of the question.

B. pgrestore: This utility is used to restore a database from an archive created by pgdump. It does not create backups.

C. pgbasebackup: This utility creates physical, binary backups of the entire database cluster file system. It does not produce the specific "custom format" archive associated with pgdump.

D. psql: This is the standard interactive terminal for PostgreSQL. It can execute SQL scripts, including plain-text dumps, but it is not a backup utility that creates compressed archives.

1. PostgreSQL 16 Documentation, pgdump: In the description of command-line options, the -F format or --format=format option is detailed. For the custom (c) format, it states: "Output a custom-format archive suitable for input into pgrestore. ... This format is also compressed by default."

Source: PostgreSQL Documentation, Server Administration, Backup and Restore, pgdump, Options section.

2. PostgreSQL 16 Documentation, pgrestore: The documentation's introductory paragraph clarifies its purpose: "pgrestore is a utility for restoring a PostgreSQL database from an archive created by pgdump in one of the non-plain-text formats (custom, directory, or tar)."

Source: PostgreSQL Documentation, Server Administration, Backup and Restore, pgrestore, Description section.

3. PostgreSQL 16 Documentation, pgbasebackup: The documentation describes its function for physical backups and its own compression methods: "pgbasebackup can make a base backup... The backup is made over a regular PostgreSQL connection... It can be created in either plain format... or as a tar archive." It mentions the -Z option for gzip compression, which is distinct from the pgdump custom format.

Source: PostgreSQL Documentation, Server Administration, Backup and Restore, pgbasebackup, Description section.

The sharedbuffers configuration parameter is the primary setting in PostgreSQL used to determine the amount of memory the database server allocates for its shared memory buffer cache. This cache is critical for performance as it stores data pages read from disk, minimizing disk I/O for frequently accessed data. The value is set in the postgresql.conf file and is one of the most impactful parameters for database tuning. A well-configured sharedbuffers value can significantly improve read and write performance by satisfying queries from memory instead of disk.

A. buffermemory: This is not a valid PostgreSQL configuration parameter for managing the shared buffer cache.

C. cachesize: While sharedbuffers defines a cache size, cachesize is not the name of the actual PostgreSQL parameter.

D. memorybuffers: This is not a recognized configuration parameter within the PostgreSQL server settings.

1. PostgreSQL Documentation. (2023). Chapter 20. Server Configuration, 20.4. Resource Consumption, 20.4.1. Memory. PostgreSQL 16 Documentation. Retrieved from https://www.postgresql.org/docs/16/runtime-config-resource.html#GUC-SHARED-BUFFERS.

Reference Detail: Section 20.4.1 explicitly states, "sharedbuffers (integer) Sets the amount of memory the database server uses for shared memory buffers."

2. O'Neil, P., & O'Neil, E. (2008). Database Principles, Programming, and Performance (2nd ed.). Morgan Kaufmann.

Reference Detail: Chapter 14, "The Query Optimizer," discusses the importance of the buffer pool in database systems. While not PostgreSQL-specific, it establishes the academic principle of a shared buffer cache, which PostgreSQL implements via the sharedbuffers parameter.

3. Carnegie Mellon University. (2022). 15-445/645 Database Systems, Lecture #02: Advanced SQL. Fall 2022 Courseware.

Reference Detail: Slides and lecture notes on database architecture consistently refer to the "buffer pool" or "buffer cache" as a core component for managing memory. The PostgreSQL implementation of this concept is controlled by sharedbuffers.

The GENERATED ALWAYS AS IDENTITY constraint enforces that the value for the identity column is always generated by the system using the associated sequence. Any attempt to manually insert or update a value in this column will result in an error, unless the OVERRIDING SYSTEM VALUE clause is used.

Conversely, GENERATED BY DEFAULT AS IDENTITY allows a user to provide a specific value for the column in an INSERT statement. If a value is provided, it overrides the sequence-generated default. If no value is supplied for the column, the system generates one as the default.

B. They are not identical; they define different behaviors for handling user-supplied values during INSERT operations.

C. Both identity types use integer-based sequences (smallint, integer, bigint), not UUIDs. serial is an older, non-standard pseudo-type.

D. This is the opposite of the correct behavior. BY DEFAULT explicitly allows manual insertion, which overrides the default generation.

---

1. PostgreSQL 13 Official Documentation, CREATE TABLE command: In the section describing column constraints, it states: "ALWAYS will always generate a value for the identity column. An attempt to insert or update a user-supplied value will be rejected with an error... BY DEFAULT will generate a value for the identity column if one is not supplied... but a user-supplied value can be inserted by explicitly specifying it."

Source: PostgreSQL 13 Documentation, SQL Commands, CREATE TABLE.

2. PostgreSQL 13 Official Documentation, Data Definition, Identity Columns: This section clarifies the behavior with examples: "If you use GENERATED BY DEFAULT, then the user-specified value is used... If you use GENERATED ALWAYS, a user-specified value is only accepted if the INSERT statement specifies OVERRIDING SYSTEM VALUE."

Source: PostgreSQL 13 Documentation, Chapter 5. Data Definition, Section 5.4. Identity Columns.

For Point-in-Time Recovery (PITR), PostgreSQL must be configured for continuous WAL (Write-Ahead Log) archiving. The wallevel parameter controls the volume of information written to the WAL. In PostgreSQL 13, the minimum level required to write enough data for WAL archiving is replica. This level ensures that all information needed to reconstruct database states for recovery and support read-only standby servers is logged. Setting wallevel to replica or higher (i.e., logical) is a mandatory prerequisite for enabling the archivemode and archivecommand parameters used in PITR setups.

A. wallevel = minimal: This level only logs information sufficient for crash recovery, which is not enough for WAL archiving or PITR.

C. wallevel = archive: This value is obsolete in PostgreSQL 13. It was merged with hotstandby into the replica setting in PostgreSQL 9.6.

D. wallevel = logical: While this level would also enable WAL archiving, it adds extra information for logical decoding, which is not strictly required for PITR. The minimum necessary level is replica.

1. PostgreSQL 13 Official Documentation, Chapter 26. Backup and Restore, Section 26.3.1. Setting Up WAL Archiving.

"To enable WAL archiving, set the wallevel configuration parameter to replica or higher, archivemode to on, and specify the shell command to use in archivecommand." This explicitly states replica as the minimum requirement.

2. PostgreSQL 13 Official Documentation, Chapter 19. Server Configuration, Section 19.5.1. Settings.

"wallevel determines how much information is written to the WAL. The default value is replica. Possible values are minimal, replica, and logical." It further clarifies: "replica writes enough data to support WAL archiving and replication..." and "minimal removes all logging except the information required to recover from a crash or an immediate shutdown."

3. PostgreSQL 13 Official Documentation, Appendix F. Release Notes, Section F.28. Release 9.6.

"Merge wallevel settings archive and hotstandby into a new setting replica (Simon Riggs, Robert Haas)." This confirms that archive is an outdated setting from before version 9.6.

The pgrestore utility is designed to restore PostgreSQL databases from archives created by pgdump in non-plain-text formats. The custom (-Fc) and tar (-Ft) options both generate such archive files. The custom format is compressed and flexible, while the tar format is an uncompressed archive. Both are explicitly designed to be used as input for pgrestore, which allows for selective restoration and reordering of archived items. The plain text format (-Fp) creates a SQL script that must be restored using a client like psql.

A. -Fp (plain text format): This option creates a plain SQL script file. This type of backup is restored by piping the script into the psql client, not by using pgrestore.

B. -Fc (custom format): This option is correct, but it is not the only correct option listed. Therefore, it is an incomplete answer.

C. -Ft (tar format): This option is also correct, but like option B, it is not the only correct option listed, making it an incomplete answer.

---

1. PostgreSQL 16 Documentation, pgdump: In the description of the -F or --format option, it specifies the output formats. It states that custom (-Fc) and tar (-Ft) formats are "suitable for input into pgrestore." In contrast, the plain (-Fp) format is described as a "plain-text SQL script file."

Source: PostgreSQL Documentation, Server Administration > Backup and Restore > pgdump. Section: "Options".

2. PostgreSQL 16 Documentation, pgrestore: The very first sentence in the description of the pgrestore utility clarifies its purpose: "pgrestore is a utility for restoring a PostgreSQL database from an archive created by pgdump in one of the non-plain-text formats." This explicitly excludes the plain format and includes the archive formats.

Source: PostgreSQL Documentation, Server Administration > Backup and Restore > pgrestore. Section: "Description".

3. PostgreSQL 16 Documentation, Chapter 26. Backup and Restore: This chapter provides a general overview. Section 26.1, "SQL Dump," explains: "The output of pgdump can be restored using the psql program... pgdump can also produce output in other formats that are intended to be used with pgrestore... pgrestore can handle the archive files."

Source: PostgreSQL Documentation, Server Administration > Chapter 26. Backup and Restore. Section: 26.1. "SQL Dump".

The pghba.conf (Host-Based Authentication) file is the primary configuration file used to control client authentication. It contains records that specify which hosts are allowed to connect, to which databases, as which users, and using what authentication method. To configure the server to accept TCP/IP connections from specific IP addresses, you must add or modify entries in this file, specifying the allowed IP address or address range in the address field of a host type record.

A. postgresql.conf: This file sets global server parameters, such as listenaddresses to enable network listening, but it does not define rules for which specific client IPs can connect.

C. pgident.conf: This file is used to map external system usernames to PostgreSQL database usernames for ident and peer authentication; it does not control IP-based access.

D. recovery.conf: This file (used in PostgreSQL versions prior to 12) configures settings for standby servers and point-in-time recovery, not for managing client connection rules.

1. PostgreSQL 16 Official Documentation. (2023). Chapter 21. Client Authentication, Section 21.1. The pghba.conf File. The documentation states, "Client authentication is controlled by a configuration file, which traditionally is named pghba.conf... Each record specifies a connection type, a client IP address range (if relevant for the connection type), a database name, a user name, and the authentication method to be used for connections matching these parameters."

2. PostgreSQL 16 Official Documentation. (2023). Chapter 20. Server Configuration, Section 20.4.1. Connections and Authentication. This section clarifies the role of postgresql.conf parameters like listenaddresses in enabling network connections, distinguishing it from the access control function of pghba.conf.

3. PostgreSQL 16 Official Documentation. (2023). Chapter 21. Client Authentication, Section 21.2. User Name Maps. This section describes the purpose of pgident.conf as mapping operating-system user names to database user names, which is distinct from IP-based connection control.

The sharedbuffers configuration parameter sets the amount of memory the PostgreSQL server dedicates to its primary disk cache. This shared memory area is used to hold data pages (blocks) from tables and indexes that have been read from disk. By caching frequently accessed data in memory, PostgreSQL can significantly reduce disk I/O and improve query performance. It is one of the most critical performance-tuning parameters, as it directly impacts how effectively the database can serve data without accessing slower disk storage.

A. The amount of memory used for sorting operations is controlled by the workmem parameter, not sharedbuffers.

C. The Write-Ahead Log (WAL) buffer size is managed by a separate parameter, walbuffers.

D. The maximum number of client connections is determined by the maxconnections parameter, which is unrelated to the data cache.

1. PostgreSQL 16 Documentation, Chapter 20. Server Configuration, Section 20.4.1. Memory.

On sharedbuffers: "Sets the amount of memory the database server uses for shared memory buffers. The default is typically 128 megabytes (128MB)... This is the single most effective setting for improving performance..." This section explicitly defines its purpose as a cache for disk blocks.

On workmem: "Specifies the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files." This confirms workmem is for sorting, distinguishing it from sharedbuffers.

2. PostgreSQL 16 Documentation, Chapter 20. Server Configuration, Section 20.5.1. Settings.

On walbuffers: "Sets the amount of shared memory used for WAL data that has not yet been written to disk." This confirms a separate buffer exists for WAL data.

3. PostgreSQL 16 Documentation, Chapter 20. Server Configuration, Section 20.4.2. Connection and Authentication.

On maxconnections: "Determines the maximum number of concurrent connections to the database server." This confirms connection management is separate from sharedbuffers.

In the PostgreSQL interactive terminal (psql), meta-commands are used for tasks that are not standard SQL, such as controlling the psql environment itself. The \connect command, which can be shortened to \c, is the specific meta-command used to close the current database connection and establish a new one to a different database. The command syntax is \connect dbname or \c dbname, where dbname is the name of the target database.

B. USE dbname; is the command for switching databases in other systems like MySQL and SQL Server, but it is not a valid command in PostgreSQL.

C. SWITCH dbname; is not a valid SQL or psql meta-command for any database-related operation in PostgreSQL.

D. SET DATABASE dbname; is not a valid command. The SET command is used to change run-time configuration parameters, not the database connection.

1. PostgreSQL 13 Official Documentation, psql Meta-Commands: The \connect or \c command is documented as the method to establish a new connection to a PostgreSQL server. The documentation states, "If the new connection is successfully made, the previous connection is closed."

Source: PostgreSQL 13 Documentation, Chapter 53. psql, Section 53.3. Meta-Commands.

2. PostgreSQL 13 Official Documentation, SQL Commands: The official list of SQL commands for PostgreSQL 13 does not include USE or SWITCH as commands for changing the database context.

Source: PostgreSQL 13 Documentation, Part VI. Reference, SQL Commands.

3. PostgreSQL 13 Official Documentation, SET Command: The documentation for the SET command confirms its purpose is to change session-level run-time parameters, such as SET searchpath TO myschema;, not to switch the database connection.

Source: PostgreSQL 13 Documentation, SQL Commands, SET.

The pgdumpall utility is the correct tool for creating a backup of an entire PostgreSQL database cluster. It generates a single plain-text script file that contains the SQL commands necessary to recreate all databases in the cluster. Crucially, pgdumpall also backs up cluster-wide data, such as roles (users and groups) and tablespace definitions, which are not included in backups of individual databases created by pgdump. This ensures a complete and consistent logical backup of the entire cluster instance.

A. pgdump is designed to back up only a single database at a time and does not back up global objects like roles.

C. pgbackup is not a standard, officially recognized PostgreSQL utility for performing backups.

D. pgclusterbackup is not a standard, officially recognized PostgreSQL utility for performing backups.

1. PostgreSQL 16 Documentation. (2023). Chapter 26. Backup and Restore, Section 26.1.2. Using pgdumpall. "pgdumpall backs up an entire database cluster. It does this by dumping all databases in the cluster, and also preserving cluster-wide data such as role and tablespace definitions."

2. PostgreSQL 16 Documentation. (2023). Server Applications, pgdumpall. "pgdumpall is a utility for writing out (“dumping”) all PostgreSQL databases of a cluster into one script file."

3. PostgreSQL 16 Documentation. (2023). Server Applications, pgdump. "pgdump is a utility for backing up a single PostgreSQL database... pgdump only dumps a single database. To back up an entire cluster, or to back up global objects that are common to all databases in a cluster (such as roles and tablespaces), use pgdumpall."

4. Obe, R. O., & Hsu, L. S. (2021). PostgreSQL Up and Running: A Practical Guide to the Advanced Open Source Database (3rd ed.). O'Reilly Media. Chapter 11, "Backup and Recovery," section on pgdumpall. (Note: While a commercial book, it is authored by PostgreSQL community experts and reflects official documentation principles. It is cited here as a supplementary academic-style reference).