Return
-
Title
PostgreSQL restore is extremely slow -
Description
PostgreSQL restore is extremely slow -
Cause
While setting up Backup option with NetVault Backup Plug-in for PostgreSQL if any of the options like Include Data only, Include Drop Object Commands. etc are selected instead of dump the data as a copy data, the restore performance will be highly affected resulting in longer restore time. -
Resolution
Under Plugin option ->Backup options -> Choose Dump Data as Copy Data: To dump data using COPY commands instead of INSERT commandsDuring a restore, select this option. Dumping data as INSERT commands generates a separate command for each row of data. This method is safer because any errors that occur result only in the loss of a row of data instead of losing the whole table.Copy restores will result in reduced restoration times.Note: If you enabled the Dump Data as Copy Data option, Quest recommends that you also
Set up additional regular backup jobs, such as weekly or every other week, that do not use the Dump
Data as a Copy option. also Dumping data as COPY commands might result in the loss of the whole table if an error occurs in one of the rows of data.If you are using PostgreSQL Server 9.6 or later Plug-in for PostgreSQL 12.3 includes a different backup method File Level Data Copy, in addition of the SQL Logical Data Dump backup method that was included in Plug-in for PostgreSQL 11.4.5.
With File Level Data Copy backup method you might observe improved backup and restore speeds.