Logical dump and restore scripts for Corruption Recovery

This article is part of series:

• Minimizing database corruption best practices
• Detecting and fixing corruption
  • Identifying corrupted rows and recovering healthy rows
  • Logical-dump-and-restore-scripts-for-Corruption-Recovery

This article provides logical backup scripts referenced in detecting-and-fixing-corruption, in relation to recovering data from a single database which is erroring due to many corrupted tables.

If wanting to capture all databases, the script will need to be invoked for each database.

The first script recovers data from the unaffected tables/objects from a database, creating logical backups. A normal dump will fail when it hits the first erroring object.

The second script restores the unaffected data into a new, healthy instance.

• create empty folder and dump script

# shell 

cd <enter location for housing script and recovery files>
mkdir recovery && cd recovery && vi dump_script.sh

# vi 

DBNAME=$1
DBPORT=$2
PGBINDIR=$3

echo `date +%F_%H:%M` : Starting extract/backup of the database ${DBNAME}

# Reset files
>01_globals.sql
>02_create_db.sql
>03_create_schemas.sql

rm -rf `pwd`/table_data_backups
rm -rf `pwd`/table_structure_backups
rm -rf `pwd`/database_structure_backups

# Generate 01_globals.sql

echo `date +%F_%H:%M` :  Generating the 01_globals.sql to be used for recreating users/roles

${PGBINDIR}/pg_dumpall -g -p ${DBPORT} > 01_globals.sql

# Generate create database sql

echo `date +%F_%H:%M` :  Generating the create database sql

echo "create database ${DBNAME};" > 02_create_db.sql

echo `date +%F_%H:%M` :  Generating the script to reproduce the schemas

# Generate script to regenerate schemas

for schema in `${PGBINDIR}/psql  -p ${DBPORT} -d ${DBNAME} -Atc "\dn" | cut -d '|' -f1`
do
echo "create schema $schema;" >> 03_create_schemas.sql
done

echo `date +%F_%H:%M` :  Generating both structural and data backups of the tables

# Generate backups of individual tables

# Make a directory just for the table_data backups in plain sql

mkdir `pwd`/table_data_backups
mkdir `pwd`/table_structure_backups

for schema in `${PGBINDIR}/psql  -p ${DBPORT} -d ${DBNAME} -Atc "\dn" | cut -d '|' -f1`
do
for table in `${PGBINDIR}/psql  -p ${DBPORT} -d ${DBNAME} -Atc "\dt $schema.*" | cut -d '|' -f2`
 do  filename=$schema"_"$table.sql && ${PGBINDIR}/pg_dump  -Fp -a -p ${DBPORT} -d ${DBNAME} --disable-triggers --table=$schema.$table   -f `pwd`/table_data_backups/$filename \
 &&  filename=$schema"_"$table.sql && ${PGBINDIR}/pg_dump  -Fp -s -p ${DBPORT} -d ${DBNAME} --table=$schema.$table   -f `pwd`/table_structure_backups/$filename
done
done

# if table corrupted/ not available, .sql files created in table_data_backups, but not in table_structure_backups

# Generate structural backup of database

echo `date +%F_%H:%M` :  Generating the structural backup of the whole database, to gather views, functions etc

# Make a directory just for the database structure in plain sql

mkdir `pwd`/database_structure_backups

${PGBINDIR}/pg_dump  -Fp -s -p ${DBPORT} -d ${DBNAME}   -f `pwd`/database_structure_backups/${DBNAME}_structure.sql

# Use strutctural dump to generate script for creating sequences and then the structure without constraints.
echo `date +%F_%H:%M` :Use strutctural dump to generate script for creating sequences and then the structure without constraints.

sed -n '/ADD CONSTRAINT/,+2!p' `pwd`/database_structure_backups/${DBNAME}_structure.sql |grep -v "ALTER TABLE ONLY" > `pwd`/database_structure_backups/${DBNAME}_structure_no_constraints.sql
awk '/CREATE SEQUENCE/ {print}' RS="" `pwd`/database_structure_backups/${DBNAME}_structure.sql > `pwd`/database_structure_backups/${DBNAME}_structure_create_sequences.sql

echo `date +%F_%H:%M` Extract completed

# shell 

chmod +x dump_script.sh

• The script is called with 3 parameters passed to it
  • DBNAME = The name of the database to extract the dumps from
  • DBPORT = The port the cluster listens on
  • PGBINDIR = The bin directory of the version of the postgres/epas cluster

e.g

# shell 

./dump_script.sh "edb" "5444" "/usr/edb/as14/bin/" > dump_script.log 2>&1

The output is written to the file dump_script.log which can be checked for errors, to see if any tables are corrupted/ missing tablespace data.

• make backup copy of dump data

# shell 
cd <enter location for housing script and recovery files>
cp -r recovery /enter-backup_location
cd recovery 

• initialize new database (if needed), e.g

# shell 

/usr/edb/as14/bin/initdb -D /var/lib/edb/test_cluster
sed -i "s@5444@5445@" /var/lib/edb/test_cluster/postgresql.conf 
/usr/edb/as14/bin/pg_ctl -D /var/lib/edb/test_cluster start

• create restore script:

# shell 
vi restore_script.sh

# vi 

DBNAME=$1
DBPORT=$2
PGBINDIR=$3

echo `date +%F_%H:%M` : Starting restore of the database ${DBNAME}

# if database non default database, uncomment:
# echo `date +%F_%H:%M` :  creating the database
${PGBINDIR}/psql -p ${DBPORT} -d ${DBNAME} < 02_create_db.sql

# globals
echo `date +%F_%H:%M` : Restoring the globals, recreating users/roles

${PGBINDIR}/psql -p ${DBPORT} -d ${DBNAME} < 01_globals.sql

# schemas
echo `date +%F_%H:%M` :  Restoring the schemas
${PGBINDIR}/psql -p ${DBPORT} -d ${DBNAME} < 03_create_schemas.sql

#DDL - not required by default, as included in database_structure_backups/${DBNAME}_structure.sql
# echo `date +%F_%H:%M` : restoring the table DDL
#
# for filename in table_structure_backups/*.sql
# do
#   [ -e "$filename" ] || continue
#   ${PGBINDIR}/psql -p ${DBPORT} -d ${DBNAME} < $filename
# done

echo `date +%F_%H:%M` :  Restoring structural backup of the whole database, to gather views, functions, table DDL, constraints, sequences etc

${PGBINDIR}/psql -p ${DBPORT} -d ${DBNAME} < database_structure_backups/${DBNAME}_structure.sql

echo `date +%F_%H:%M` :  Restoring data into tables

# DML
for filename in table_data_backups/*.sql
do
  [ -e "$filename" ] || continue
  ${PGBINDIR}/psql -p ${DBPORT} -d ${DBNAME} < $filename
done

echo `date +%F_%H:%M` Extract completed

• give execution permission:

# shell 

chmod +x restore_script.sh

• run script
• The script is called with 3 parameters passed to it
  • DBNAME = The name of the database to extract the dumps from
  • DBPORT = The port the cluster listens on
  • PGBINDIR = The bin directory of the version of the postgres/epas cluster

e.g

# shell 

./restore_script.sh "edb" "5445" "/usr/edb/as14/bin/" > restore_script.log 2>&1

• The output is written to the file restore_script.log which should be checked for errors.
• Please note that any tables that were corrupted/ missing tablespace and errored in dump_script.log will have their DDL created, with 0 rows.

• Script 1 - Database Dump, & Script 2 - Restore Script were tested for backing up and restoring all of the saved objects from a database with constraints, sequences, tables, views, foreign keys.
• In the test, one of the tables was linked to a tablespace which had it's data deleted on OS side. For that broken table, it resulted in the DDL being copied over, but not the actual data.

• Script 1 and 2 are for generic use, and if issues are experienced (e.g sequences/ foreign key), additional files created by the dump can be used.

• pwd/table_structure_backups/$filename

• pwd/database_structure_backups/${DBNAME}_structure_no_constraints.sql

• pwd/database_structure_backups/${DBNAME}_structure_create_sequences.sql

• By default, these scripts dump and restore plain text .sql files using pg_dump and psql.
• If there is not enough disk space to perform this, use of pg_dump -Fc and pg_restore can be used instead.

• Please proceed with extreme caution when attempting to recover a corrupted database.

• This article is based on previous past experiences in which our expert staff was involved. It is provided in the hope that it may be useful, but without any guarantee.

• In case you try any of the techniques mentioned in this article, you do so at your own risk and you may lose or further corrupt your data. In all cases, please contact EDB Technical Support for further assistance.

Script written by Tony Smith.