恢复PostgreSQL压缩备份时如何跳过错误继续执行？

Alright, let's break down this problem and walk through your options. The errors you're hitting—gzip: stdin: unexpected end of file and the COPY column data missing issue—make it clear your backup file is corrupted. If you absolutely need to salvage as much data as possible (a working backup is always the ideal fix), here are actionable approaches to try:

1. First, Recover as Much of the Gzip File as Possible #

gzip has basic built-in recovery capabilities to skip damaged sections and extract usable data. Instead of piping directly to psql, first try to pull out what you can from the corrupted archive:

gzip -d -c /path/to/backups/backupname.sql.gz > recovered_backup.sql

You’ll still see error messages from gzip, but this command won’t terminate early—it’ll output whatever intact data it can parse into recovered_backup.sql.

2. Force psql to Keep Running Through Errors #

By default, psql stops execution immediately when it hits an error. You can override this behavior with the ON_ERROR_STOP=off environment variable. Combine this with your recovered SQL file (or pipe directly if you skip step 1):

dropdb dbname && createdb -O pguser dbname
# Use the recovered file first if you did step 1
cat recovered_backup.sql | ON_ERROR_STOP=off psql dbname
# Or pipe directly from gzip for a one-liner
gunzip -d -c /path/to/backups/backupname.sql.gz | ON_ERROR_STOP=off psql dbname

⚠️ Heads Up: This will make psql plow through errors, but it can lead to a wildly inconsistent database. For example, if a row fails to import due to missing column data, subsequent rows in the same table might still load—but you’ll end up with gaps. Tables relying on foreign keys to that missing data could also fail to import properly.

3. Target the Specific Corrupted Line #

Since you know the error is on line 533475 of the spots table’s COPY command, you can fix that specific issue in the recovered file:

1. Use the gzip recovery command from step 1 to generate recovered_backup.sql.
2. Remove the problematic line with a command-line tool (great for large files that crash text editors):

   sed '533475d' recovered_backup.sql > fixed_backup.sql
   

3. Import the cleaned-up file normally:

   psql dbname < fixed_backup.sql
   

This is more precise than ignoring all errors, but note there might be other corrupted lines you’ll need to address individually.

A Critical Reminder #

Before moving forward, remember: ignoring errors during restore will almost certainly leave you with incomplete or broken data. After recovery, you’ll need to thoroughly validate every table, check for missing records, and fix any broken relationships. If at all possible, track down a clean, uncorrupted backup—it’ll save you hours of troubleshooting down the line.

内容的提问来源于stack exchange，提问作者Evgeniy Polyakov