From the objective of OSS-DB Exam Silver
- Operation management - backup method
Sample Question
1.56
Choose the correct statement that best describes PostgreSQL backup.
-
A backup performed in the PostgreSQL 9.0 environment using pg_dump can be restored from in the PostgreSQL 9.1 environment.
-
A backup performed in the PostgreSQL 8.4 environment using the pg_dumpall command can be restored in a PostgreSQL 9.0 environment.
- I must stop the database server process in order to get a backup of the entire database cluster using the pg_dumpall command.
-
A backup of the PostgreSQL 9.0 environment by directory copy (copying directory of database cluster with tar command etc) may be restored into a PostgreSQL 9.1 environment.
-
I recovered with the base backup and archive log from a PostgreSQL 8.4 environment using the point in time recovery (PITR) function, in the environment of a PostgreSQL 9.0 installation.
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2019/05/30
Answer and Explanation
pg_dump and pg_dumpall are commands used to create a logical backup of PostgreSQL.
The entity of the backup consists of SQL statements for constructing databases such as CREATE TABLE and GRANT and SQL statements for reproducing data such as COPY or INSERT.
Therefore, it can be used even between different systems with major versions of PostgreSQL such as upgrades.
By default, the backup of the data part becomes a COPY statement (proprietary extension of PostgreSQL), but when pg_dump is executed with the - inserts option, it creates a backup of data using the INSERT statement, so you can restore the data to a database other than PostgreSQL.
pg_dump gets a specific database in the database cluster, pg_dumpall gets a backup of the entire database cluster, but both run with the server process of the database running.
Physical backup of the database is done by stopping the server process and copying the directory of the database cluster using OS command such as tar.
Since the structure of the database cluster differs depending on the major version of PostgreSQL, backup and restoration by this method can not be performed when major versions are different like 9.0 and 9.1.
PITR, like directory copy, can not be used if the major version is different.
Therefore, the correct answers are A and B.