EDB Esentials for PostgreSQL - Exam Actual Q&A
[ Total Questions: 104 questions – 54q v14 + 50q v18]
Q.1
In the standard PostgreSQL 14 server architecture, how are multiple simultaneous client connections normally handled?
A. All client SQL runs inside the single checkpointer process.
B. Every connection is handled by a thread inside one shared backend process.
C. One server process is permanently assigned to each database, regardless of connection count.
D. A separate server process is created for each client connection.
Q.2
Which setting controls the memory allocated to the shared buffer cache used by PostgreSQL 14?
A. work_mem
B. shared_buffers
C. wal_buffers
D. maintenance_work_mem
Q.3
Which background process automatically performs checkpoints in PostgreSQL 14?
A. The checkpointer
B. The WAL receiver
C. The statistics collector
D. The autovacuum launcher
Q.4
Which PostgreSQL 14 setting controls how frequently the WAL writer flushes WAL in time terms?
A. checkpoint_completion_target
B. wal_writer_delay
C. archive_timeout
D. bgwriter_delay
Q.5
Which file in the cluster data directory records the PostgreSQL major version?
A. backup_label
B. postgresql.auto.conf
C. PG_VERSION
D. current_logfiles
Q.6
A staging table is created with CREATE UNLOGGED TABLE in PostgreSQL 14. What happens to its contents after a crash or unclean shutdown?
A. The table is automatically truncated.
B. The table is automatically converted into a permanent logged table.
C. The table is dropped from the system catalogs.
D. The contents are guaranteed to survive because every change is WAL-logged.
Q.7
An optional configuration file may be absent on some hosts. Which directive allows PostgreSQL 14 to continue processing the main configuration when that file is missing?
A. include_if_exists = 'optional.conf'
B. include = 'optional.conf'
C. require = 'optional.conf'
D. load_config = 'optional.conf'
Q.8
After editing a configuration file and reloading it, an administrator sees pending_restart = true for a parameter in pg_settings. What does this mean?
A. The parameter can never be changed.
B. A server restart is needed to apply the changed file value.
C. The new value is already active in every backend.
D. Only the psql client must be restarted.
Q.9
The server is started with postgres -c shared_buffers=256MB, while postgresql.conf specifies shared_buffers=128MB. Which value is used?
A. Startup fails because the parameter appears in two places.
B. 384MB, because both values are added together.
C. 256MB, because the server command-line setting overrides the configuration file.
D. 128MB, because the configuration file always overrides the server command line.
Q.10
Inside an explicit transaction, a session runs SET LOCAL work_mem = '32MB'. What is the scope of that setting?
A. It permanently changes postgresql.conf.
B. It remains active until the server restarts.
C. It lasts until the current transaction ends.
D. It changes the default for every new connection.
Q.11
Which SQL command displays the path of the main server configuration file used by the current PostgreSQL 14 instance?
A. SHOW config_file;
B. SHOW data_directory;
C. SHOW ident_file;
D. SHOW hba_file;
Q.12
An administrator wants to cancel a statement only if it spends too long waiting to acquire a lock. Which setting is intended for that purpose?
A. lock_timeout
B. idle_in_transaction_session_timeout
C. authentication_timeout
D. statement_timeout
Q.13
Which psql meta-command reports information about the current database connection?
A. \du
B. \l
C. \conninfo
D. \dn
Q.14
You want psql to display wide result rows vertically using expanded output. Which meta-command explicitly enables that mode?
A. \a
B. \x on
C. \H
D. \t on
Q.15
Which psql meta-command enables display of the time taken by each SQL statement?
A. \timing on
B. \set ECHO all
C. \watch 1
D. \pset footer on
Q.16
You are already connected in psql. Which command reads and executes commands from the client-side file /home/dba/setup.sql?
A. \o /home/dba/setup.sql
B. \p /home/dba/setup.sql
C. \cd /home/dba/setup.sql
D. \i /home/dba/setup.sql
Q.17
In psql, a user runs \copy sales FROM '/home/analyst/sales.csv' WITH (FORMAT csv). Which machine reads the named file?
A. The database server, using the PostgreSQL server operating-system account.
B. Every replication standby independently.
C. The machine running the WAL archiver, regardless of where psql runs.
D. The machine running psql, using the local operating-system user permissions.
Q.18
A superuser connected to postgres runs ALTER DATABASE reporting ALLOW_CONNECTIONS false. What is the effect on subsequent connection attempts to reporting?
A. Only superusers can establish new connections.
B. New connections are allowed, but all queries become read-only.
C. Existing sessions are immediately terminated.
D. No one can establish a new connection to that database.
Q.19
Which statement about DROP DATABASE is correct in PostgreSQL 14?
A. A later ROLLBACK can undo a successfully completed DROP DATABASE.
B. It can remove the database to which the issuing session is connected.
C. It always leaves the database files on disk.
D. It cannot be executed inside a transaction block.
Q.20
In a newly initialized PostgreSQL 14 cluster with unmodified default privileges, which privileges does PUBLIC have on the public schema?
A. USAGE only
B. CREATE only
C. No schema privileges
D. CREATE and USAGE
Q.21
In PostgreSQL 14, analyst has NOINHERIT and is a member of reporting_role. SELECT on a table is granted only to reporting_role. What can analyst do to use that SELECT privilege?
A. Reconnect without changing roles; NOINHERIT automatically enables all group privileges.
B. Execute SET ROLE reporting_role before querying the table.
C. Run ANALYZE on the table to activate inherited permissions.
D. Set search_path to reporting_role; schema search paths confer role privileges.
Q.22
Two pg_hba.conf records match the same incoming connection. Authentication using the first matching record fails. What happens next?
A. The database password is reset and the connection is accepted.
B. The connection fails; PostgreSQL does not try the later matching record.
C. The connection is automatically retried using trust authentication.
D. PostgreSQL tries each later matching record until one succeeds.
Q.23
Which authentication method obtains the client operating-system user name from the kernel and is supported only for local connections?
A. scram-sha-256
B. peer
C. cert
D. ldap
Q.24
With the PostgreSQL 14 default password_encryption setting unchanged, which algorithm is used when a new role password is stored?
A. scram-sha-256
B. md5
C. password
D. trust
Q.25
On Unix, an otherwise valid ~/.pgpass file has mode 0644. Why can libpq ignore this password file?
A. Password files are supported only on Windows.
B. The file must be placed in the server data directory.
C. The file must be executable by the PostgreSQL service account.
D. The file allows group or other users to access it; permissions such as 0600 are required.
Q.26
Which command grants bob membership in reporting_role and allows bob to grant that role membership to other users?
A. ALTER ROLE bob SET reporting_role = on;
B. GRANT reporting_role TO bob WITH ADMIN OPTION;
C. GRANT reporting_role TO bob WITH GRANT OPTION;
D. GRANT USAGE ON ROLE reporting_role TO bob;
Q.27
Row-level security is enabled on a table, but no policies exist. An ordinary user who is neither the owner nor a BYPASSRLS role has SELECT privilege. Which rows can that user see?
A. No rows, because the default-deny policy applies.
B. Only rows whose first column matches the user name automatically.
C. Only rows inserted during the current session.
D. All rows, because SELECT privilege overrides row-level security.
Q.28
With whose privileges does a SECURITY DEFINER function execute in PostgreSQL 14?
A. The privileges of the user who calls the function.
B. The privileges of the user who owns the function.
C. The privileges of the owner of the current database in every case.
D. The privileges of the operating-system root user.
Q.29
In a Read Committed transaction, two ordinary SELECT statements are executed with no intervening changes by that transaction. Another session commits an update between them. What can happen?
A. The second SELECT must raise a serialization error.
B. The second SELECT can see the other session’s uncommitted changes.
C. Both SELECT statements must see the same transaction-wide snapshot.
D. The second SELECT can see the newly committed update.
Q.30
In PostgreSQL 14 Repeatable Read, when is the snapshot used for ordinary queries established?
A. Only when the transaction issues COMMIT.
B. At the start of every individual SELECT statement.
C. At the start of the first non-transaction-control statement in the transaction.
D. When the most recent checkpoint completes.
Q.31
What does ROLLBACK TO SAVEPOINT s1 do within an open transaction?
A. It discards changes made after s1 and leaves the savepoint valid.
B. It discards all changes and permanently removes every savepoint.
C. It preserves later changes but releases the savepoint name.
D. It commits changes made before s1 and ends the transaction.
Q.32
An existing table contains committed rows. A session runs BEGIN; TRUNCATE the_table; ROLLBACK;. What happens to those rows?
A. Only half the rows are restored.
B. The truncation is rolled back and the rows remain.
C. The table definition is dropped by ROLLBACK.
D. The rows remain permanently deleted because TRUNCATE commits automatically.
Q.33
A PostgreSQL 14 table has a nullable column declared UNIQUE. With no other constraints or triggers, can two rows both contain NULL in that column?
A. No, because UNIQUE implicitly makes the column NOT NULL.
B. No, because PostgreSQL 14 treats NULL values as equal in all unique constraints.
C. Yes, but only when both inserts belong to the same transaction.
D. Yes, because two NULL values are not considered equal for this unique constraint.
Q.34
A table named stock has a primary key on sku. Which INSERT clause can update an existing row when an insert conflicts on sku?
A. ON ERROR UPDATE quantity = EXCLUDED.quantity
B. ON CONFLICT (sku) DO UPDATE SET quantity = EXCLUDED.quantity
C. ON DUPLICATE KEY UPDATE quantity = VALUES(quantity)
D. WHEN MATCHED THEN UPDATE quantity = EXCLUDED.quantity
Q.35
A table has an id column defined as GENERATED ALWAYS AS IDENTITY. Which INSERT clause allows an explicitly supplied id to be used instead of a generated value?
A. OVERRIDING SYSTEM VALUE
B. OVERRIDING USER VALUE
C. RESTART IDENTITY
D. USING GENERATED VALUE
Q.36
You need a common table expression that refers to its own output to traverse a parent-child hierarchy. Which syntax enables that pattern?
A. WITH MATERIALIZED ONLY
B. START RECURSION
C. WITH RECURSIVE
D. CONNECT WITH
Q.37
A query processes three rows whose amount values are 10, NULL, and 20. What are the results of COUNT(*) and COUNT(amount), respectively?
A. 3 and 2
B. 2 and 3
C. 3 and 3
D. 2 and 2
Q.38
Which index type does CREATE INDEX use when no USING clause is specified in PostgreSQL 14?
A. BRIN
B. Hash
C. GIN
D. B-tree
Q.39
Which index type is an inverted index suited to queries checking whether arrays contain particular component values?
A. B-tree
B. GIN
C. BRIN
D. Hash
Q.40
A DBA runs EXPLAIN ANALYZE UPDATE orders SET status = 2 WHERE id = 7;. Which statement is correct?
A. Only a plan is generated; the UPDATE cannot modify data.
B. The UPDATE is executed only if VERBOSE is also specified.
C. The UPDATE is actually executed, so its data changes occur unless rolled back.
D. The UPDATE is automatically rolled back by EXPLAIN ANALYZE.
Q.41
After a major change in a table’s data distribution, which command directly refreshes the statistics used by the query planner?
A. CHECKPOINT;
B. DISCARD PLANS;
C. ANALYZE table_name;
D. REINDEX TABLE table_name;
Q.42
Two transactions form a deadlock while waiting for each other’s locks. How does PostgreSQL 14 resolve the deadlock?
A. It commits both transactions automatically.
B. It disables locking until both transactions finish.
C. It always aborts the transaction with the larger process ID.
D. It detects the deadlock and aborts one of the involved transactions.
Q.43
In pg_stat_activity, what does the state idle in transaction mean?
A. The backend has completed its transaction and disconnected.
B. The backend is necessarily waiting for a replication acknowledgment.
C. The backend is inside a transaction but is not currently executing a query.
D. The backend is actively executing a long-running statement.
Q.44
A DBA has sufficient privileges and wants to cancel a session’s current query without terminating the session itself. Which function should be used?
A. pg_cancel_backend(pid)
B. pg_reload_conf()
C. pg_rotate_logfile()
D. pg_terminate_backend(pid)
Q.45
Which PostgreSQL 14 function returns the process IDs of sessions blocking a specified backend from acquiring a lock?
A. pg_backend_pid()
B. pg_postmaster_start_time()
C. pg_blocking_pids(pid)
D. pg_current_wal_lsn()
Q.46
Which statement describes pg_dump behavior while other sessions read and modify the database?
A. It can produce a consistent backup without blocking ordinary readers or writers.
B. It locks all tables against every INSERT, UPDATE, and DELETE for the entire dump.
C. It requires every other client to disconnect before starting.
D. It produces a consistent backup only if the server is stopped.
Q.47
Which pg_dump option exports object definitions without table data?
A. --schema=public
B. --schema-only
C. --blobs
D. --data-only
Q.48
Which command lists the table of contents of a custom-format archive without restoring it?
A. pg_restore --clean backup.dump
B. pg_restore --data-only -d appdb backup.dump
C. pg_restore --create backup.dump
D. pg_restore --list backup.dump
Q.49
In the PostgreSQL 14 low-level backup API, which call starts a non-exclusive online base backup with the label nightly?
A. SELECT pg_stop_backup(false);
B. SELECT pg_start_backup('nightly', false, false);
C. SELECT pg_backup_start('nightly', false);
D. SELECT pg_start_backup('nightly', false, true);
Q.50
What must archive_command return after successfully archiving a WAL segment so PostgreSQL recognizes the operation as successful?
A. The text SUCCESS on standard output, regardless of exit status
B. The number of bytes copied as the exit status
C. Exit status 0
D. Exit status 1
Q.51
After restoring a base backup for archive recovery in PostgreSQL 14, which signal file is created in the data directory to request targeted recovery rather than standby mode?
A. recovery.signal
B. postmaster.opts
C. recovery.conf
D. standby.signal
Q.52
A disconnected standby uses a physical replication slot. With no effective retention limit, what operational risk can the slot cause on the primary?
A. The slot automatically promotes the disconnected standby.
B. The slot converts all permanent tables into unlogged tables.
C. The slot automatically deletes WAL needed by the disconnected standby.
D. Required WAL can accumulate until the pg_wal filesystem runs out of space.
Q.53
A long-running query on a hot standby conflicts with WAL that must be replayed. What can PostgreSQL do after the configured maximum replay delay is exceeded?
A. Roll back the already committed primary transaction.
B. Convert the standby into a writable primary automatically.
C. Cancel the conflicting standby query so WAL replay can proceed.
D. Discard the conflicting WAL record permanently.
Q.54
Synchronous standbys are configured and participating. Which synchronous_commit value waits for a transaction’s commit record to be applied on the required synchronous standby servers?
A. local
B. off
C. remote_apply
D. remote_write
-------------------------------v18--------------------------------------
Q.55
Which of the following options are true about the streaming replicated hot standby server?
Select all that apply
A. pg_switch_wal will not work on hot standby server
B. CREATE TABLE cannot be executed on hot standby successfully
C. CREATE TEMP TABLE can be executed on hot standby successfully
D. Stats Collector process cannot run on hot standby
Answer: A, B
Q.56
Fill in the blank. The plain text files created by pg_dump are intended to be read by the ______ program.
A. pg_restore
B. psql
C. pgAdmin4
D. pg_dump
Answer: B
Q.57
Which system table stores the total number of rows and disk blocks of each table and index in PostgreSQL?
A. pg_statistic
B. pg_stat_user_tables
C. pg_class
D. pg_stat_all_tables
Answer: C
Q.58
TRUE/FALSE:
vacuum_buffer_usage_limit specifies the size of the Buffer Access Strategy used by the VACUUM and ANALYZE commands.
A. TRUE
B. FALSE
Answer: A
Q.59
You have implemented streaming replication between two servers. Suddenly you find that the primary server is down. Which of the following commands on the replica server can be used for failover?
A. pg_ctl -t triggerfile -D <data dir> restart
B. pg_ctl t triggerfile -D <data dir> promote
C. pg_ctl -D <data dir> restart
D. pg_ctl -D <data dir> promote
Answer: D
Q.60
Which file only exists when the database is running and hasn't been stopped abnormally?
A. pg_hba.conf
B. pg_ident.conf
C. postmaster.pid
D. postgresql.conf
Answer: C
Q.61
In PostgreSQL, which parameter is used to control the amount of buffers written by the background writer process?
A. bgwriter_delay
B. bgwriter_lru_maxpages
C. bgwriter_buffers
D. shared_buffers
Answer: B
Q.62
You are working as a PostgreSQL DBA. A developer is trying to connect to a PostgreSQL database using pgAdmin4.
The database server is running on port 5432.
He emailed you following error message:
Could not connect to Server: Connection refused
What could be the possible reason?
A. Client's IP address is not present in pg_hba.conf file
B. Port 5432 may not be open through the network firewall
C. SSL connection is not allowed in pgAdmin
D. all of the above.
Answer: B
Q.63
You are using pgAdmin4 as an interface to PostgreSQL. Which of the following statements are true about the restore option of pgAdmin4?
1. In order to use the restore option, the pg_restore tool must be accessible by pgAdmin4.
2. You can restore a single object using the restore option of pgAdmin4.
3. The restore option of pgAdmin4 can create the database to be restored.
A. 1 and 2
B. 1 and 3
C. 2 and 3
D. 1, 2 and 3
Answer: D
Q.64
You have created a view on the emp table to provide access to selected rows. What privileges can be granted on this newly-created view?
A. DELETE, INSERT,SELECT
B. ALTER, DELETE, INSERT, SELECT
C. DELETE, INSERT, SELECT, UPDATE
D. None of the above
Answer: C
Q.65
You are planning to use pg_dump for backups. After specifying a custom archive format for the SQL dump, you would like to specify compression of individual table-data segments. Which option of pg_dump can be used to achieve this?
A. -z
B. -c
C. --compress
D. -C
Answer: C
Q.66
You are working on a PostgreSQL database. In which scenario would an index be most useful?
A. The indexed column is declared as NOT NULL.
B. The indexed columns are used in the FROM clause.
C. The indexed columns are part of an expression.
D. The indexed column contains a wide range of values.
Answer: D
Q.67
Examine the structure of the EMP table:
EMP_ID NUMERIC PRIMARY KEY
EMP_NAME VARCHAR(30)
JOB_ID NUMERIC
SAL NUMERIC
MGR_ID NUMERIC
You create a sequence called SEQ in order to populate sequential values for the EMP_ID column.
Which two statements regarding SEQ sequence are true (choose two)?
A. The SEQ sequence is not affected by modifications to the EMP table
B. You cannot use the SEQ sequence to populate the EMP_ID column
C. Any other column of NUMERIC data type in your schema can use the SEQ sequence
D. The SEQ sequence is invalidated when you modify the EMP_ID column
E. The SEQ sequence is dropped automatically when you drop the EMP table
F. The SEQ sequence is dropped automatically when you drop the EMP_ID column
Answer: A, C
Q.68
High Availability (HA) and Recoverability are two different things. There are several tools which can provide High Availability for a running PostgreSQL instance. Which of the following is an HA tool for PostgreSQL?
A. pg_standby
B. Storage Replication
C. Archiving
D. EDB Postgres Failover Manager
Answer: D
Q.69
You have configured continuous archiving on your database server. The archive destination is full, causing archiving to stop. You have changed the archive destination to another empty disk. Which of the following commands can be used to implement the archive destination change?
A. select switch_wal;
B. select switch_current_wal();
C. pg_ctl -D data_dir --with_wal_destination
D. pg_ctl -D data_dir reload
Answer: D
Q.70
You are working on a PostgreSQL database.
You have created a table using the following SQL statement:
CREATE TABLE emp(empno numeric primary key, ename varchar);
Which of the following SQL statements will execute successfully? (Select all that apply)
A. INSERT INTO emp values(1,'Big');
B. INSERT INTO emp values(2,$$Big$$);
C. INSERT INTO emp values(3,$right$Big$right$);
D. INSERT INTO emp values(4,$left$Big$right$);
Answer: A, B, C
Q.71
Tablespaces can be used to separate indexes in PostgreSQL. Which command will list all the tablespaces in a database cluster?
A. \dx
B. \dt
C. \t
D. \db
Answer: D
Q.72
Parallel query execution mode can be enabled and configured using configuration parameters. Which parameter can be used to set the minimum size of a table so it will be considered for parallel scan?
A. min_parallel_table_scan_size
B. parallel_relation_size
C. max_parallel_relation_size
D. relation_size
Answer: A
Q.73
Which of the following catalog views can be used to view replicas connected to the primary and other useful information?
A. pg_stat_replication
B. pg_stat_wal_sender
C. pg_stat_slots
D. pg_stat_archive
Answer: A
Q.74
Which psql command option is used to run a query from the OS command prompt?
A. -c
B. -U
C. -d
D. -w
Answer: A
Q.75
You are working as a PostgreSQL DBA and want to limit access at the row level. Which of the following is available in PostgreSQL for limiting access at row level?
A. GRANT and REVOKE statements
B. GRANT statement
C. CREATE POLICY
D. DBMS_RLS
Answer: C
Q.76
True/False
PostgreSQL Logical Replication can be used to replicate between different major versions of Postgres.
A. TRUE
B. FALSE
Answer: A
Q.77
You are working as a PostgreSQL DBA. While setting up a new database cluster for your test machine you create several users. Which statement or command is not valid to create a user in PostgreSQL?
A. create user <username> password '<password>'
B. create role <rolename> with login
C. createuser -l -P -U <superusername> <username>
D. create user <username> identified '<password>'
Answer: D
Q.78
Which of the following statements about autovacuum thresholds and scale factors are true?
Select all that apply
A. The default thresholds and scale factors are taken from postgresql.conf
B. It is not possible to change default thresholds and scale factors
C. It is possible to set thresholds and scale factors per table
D. Thresholds must be greater than 50 in postgresql.conf
Answer: A, C
Q.79
WAL Logs are stored as a set of segments. Which server parameter controls the total number of wal segments in PostgreSQL 16?
A. max_wal_size
B. wal_segments
C. archive_segments
D. max_wal_senders
Answer: A
Q.80
Autocommit occurs in which of the following situations?
A. All DML statements
B. All DDL statements
C. On DML and DDL statements
D. None of the above
Answer: C
Q.81
Streaming Replication is set up for high availability between your primary and hot standby server.
A user connected to the hot standby server and executed the following SQL statement:
START TRANSACTION;
What will be the output of the above statement when executed on your hot standby server?
A. The statement will execute successfully
B. The statement will not execute successfully
C. Error: Only read-only statements are allowed on hot standby
D. Error: Cannot execute on hot standby
Answer: A
Q.82
The EMP table has these columns:
ENAME VARCHAR(35)
SALARY NUMERIC
HIRE_DATE DATE
Management wants a list of names of employees who have been with the company for more than five years. Which SQL statement can be used to generate the required list?
A. SELECT ENAME FROM EMP WHERE CURRENT_DATE-HIRE_DATE > 5;
B. SELECT ENAME FROM EMP WHERE HIRE_DATE-CURRENT_DATE> 5;
C. SELECT ENAME FROM EMP WHERE (CURRENT_DATE-HIRE_DATE)/365 > 5;
D. SELECT ENAME FROM EMP WHERE (CURRENT_DATE-HIRE_DATE)* 365 > 5;
Answer: C
Q.83
Which of the following maintenance commands requires an exclusive lock on each table in a database?
A. VACUUM;
B. VACUUM FREEZE ANALYZE;
C. VACUUM FREEZE;
D. VACUUM FULL;
Answer: D
Q.84
To prevent unwanted access to your production server you must revoke connection privileges from all non-superusers. Which of the following statements can be used to achieve this?
A. REVOKE CONNECT ON DATABASE dbname FROM ALL USERS;
B. REVOKE CONNECT ON DATABASE dbname FROM public;
C. REVOKE CONNECT ON DATABASE dbname FROM USERS;
D. REVOKE CONNECT ON DATABASE dbname FROM SUPERUSERS;
Answer: B
Q.85
Examine following SQL:
CREATE VIEW vw_emp_details AS SELECT ename,sal,sal*12 AS annsal FROM emp;
After creating the above view what will be the result of the following query?
UPDATE vw_emp_details SET annsal=annsal+100;
A. Error: Views cannot be updated
B. Error: Cannot update column annsal
C. Statement executes successfully but none of the rows of emp table are modified
D. Statement executes successfully and all the rows of emp table are modified
Answer: B
Q.86
You are working as PostgreSQL DBA and want to implement host based access control. Which of the following configuration files can be used to implement IP address based security?
A. postgresql.conf
B. recovery.conf
C. pg_hba.conf
D. None of the above
Answer: C
Q.87
Which option can be used at cluster creation to enable checksums?
A. -checksum
B. --checksum
C. -k
D. -db-checksum
Answer: C
Q.88
Which template does Postgres use to create a new database when no template option is specified?
A. template0
B. template1
C. no template is used
D. pg_template
Answer: B
Q.89
One of your databases is experiencing slow performance. After diagnosing the problem you decide to move the entire database physically onto a different tablespace. Which of the following options can be used to achieve this?
A. ALTER DATABASE dbname MOVE oldtablespace TO newtablespace.
B. ALTER DATABASE dbname SET TABLESPACE newtablespace.
C. ALTER DATABASE dbname MOVE newtablespace.
D. Dump the database and restore it to the new location.
Answer: B
Q.90
Which of the following statements are true?
1. pg_dumpall is a utility program for backing up one PostgreSQL database.
2. pg_dumpall can dump global objects.
3. pg_dumpall can be used to backup an entire database cluster.
4. pg_dumpall needs to connect several times during the backup.
5. -f option cannot be used with pg_dumpall.
A. 2,3 and 4
B. 1,2,3 and 4
C. 1,3,4 and 5
D. All
Answer: A
Q.91
You have configured online filesystem-level backups. After a disk crash, you would like to restore and recover your database cluster using Point-in Time Recovery. Which of the following statements are true about PITR in PostgreSQL?
A. PITR is the ability to restore a database cluster to a specific point in time, including the present
B. PITR can be performed using offline filesystem backups
C. PITR can be used to perform Streaming Replication
D. PITR can be performed using online filesystem backups
Answer: A, B, D
Q.92
PostgreSQL supports synchronous streaming replication. Which postgresql.conf parameter is used to specify the names of synchronously-replicated standby servers?
A. synchronous_commit
B. streaming_standby
C. remote_write_names
D. synchronous_standby_names
Answer: D
Q.93
You have used pg_dump to take a SQL dump backup of one of the databases in compressed format. Which utility program can be used to restore compressed backups?
A. psql
B. postmaster
C. pg_dump
D. pg_restore
Answer: D
Q.94
You are working on a PostgreSQL database cluster. You have decided to reset all the changes done using the ALTER SYSTEM command. Which of the following SQL commands can be used to achieve this?
A. ALTER SYSTEM RESET;
B. ALTER SYSTEM RESET ALL;
C. ALTER DATABASE RESET ALL;
D. ALTER DATABASE RESET CONFIG;
Answer: B
Q.95
Which of the following statements are true about schemas in PostgreSQL?
1. A schema name can be the same as a user name.
2. Schemas can be used to avoid naming conflicts of various user/application objects.
3. Schema-level GRANT and REVOKE can be used to modify permissions . A schema can be shared among multiple users.
A. 1, 2, and 4
B. 1, 2, and 3
C. 1, 3, and 4
D. All the statements are true
Answer: D
Q.96
In order to improve performance on large-memory systems which configuration parameter can be to used allow use of huge memory pages?
A. wal_level=decoding
B. huge=on
C. large=on
D. huge_pages=on
Answer: D
Q.97
You are installing PostgreSQL using the yum installer. After initializing the default database cluster, which database will be created automatically?
A. edb
B. template
C. templates
D. postgres
Answer: D
Q.98
You have created an object in PostgreSQL database which provides a projection of table data (selected data from a table) without its own storage. What is this object known as in PostgreSQL?
A. Relation
B. View
C. Materialized View
D. Domain
Answer: B
Q.99
Which memory component is used to store temporary table data for each session?
A. shared_buffers
B. work_mem
C. maintenance_work_mem
D. temp_buffers
Answer: D
Q.100
You are writing a script to back up your production database. You need to provide a database password to the script in order to make the database connection. Which of the following environment variables can be used to set a default password for your backup script?
A. PGPASS
B. .pgpass
C. PGPASSWORD
D. .pgpassword
Answer: C
Q.101
Which Postgres system catalog stores the estimated number of rows and disk pages occupied by an index?
A. pg_index
B. pg_class
C. pg_relindex
D. pg_relation_size()
Answer: B
Q.102
Which of the following statements are correct? Select all that apply
A. One database has more than one cluster
B. One cluster can have more than one database
C. One cluster has more than one database with one instance for each database
D. One cluster is one instance
Answer: B, D
Q.103
Which of the following directories store symbolic links for user defined tablespaces in PostgreSQL?
A. base
B. global
C. pg_tblspc
D. pg_wal
Answer: C
Q.104
In PostgreSQL what is used to keep track of available space in the relation that can be used for newly inserted and updated rows?
A. Visibility map file
B. Index datafile
C. Table data file segment
D. Free space map file
Answer: D
=====================================================================
About ITCert-Store
We provide latest & high quality IT / Business Certification Training Exam Questions, Study Guides
We help you pass any IT / Business Certification Exams with 100% Pass Guaranteed. Especially Cisco, CompTIA, Citrix, EMC, HP, Oracle, VMware, Juniper, Check Point, LPI, Nortel, EXIN and so on.
View list of all certification exams: All vendors
=============================
TƯ VẤN: Click Here hoặc Hotline/Zalo 090.29.12.888
=============================
Website không chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn không muốn bị AI thay thế và tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp hay làm chủ Database thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ bí kíp thực chiến, thủ tục, quy trình của gần 20 năm kinh nghiệm (mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google) từ đó giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
- Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
cơ sở dữ liệu, cơ sở dữ liệu quốc gia, database, AI, trí tuệ nhân tạo, artificial intelligence, machine learning, deep learning, LLM, ChatGPT, DeepSeek, Grok, oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c/21c/23c/23ai, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, docker, k8s, micro service, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
