Limited data restore functionality and/or restore performance issues can occur when restoring tables from a full database backup where the default backup  directory was not used

Post date: Nov 06, 2013 7:32:34 PM

Limited data restore functionality and/or restore performance issues can occur when restoring tables from a full database backup where the default backup 

directory was not used. In order to restore from backup files not located in the default directory you can use the -R to point to another host and directory. This is not possible however, if you want to point to a different directory on the same host (NFS for example). 

Workaround: Define a symbolic link from the default dump directory to the directory used for backup, as shown in the following example:

1. Perform a full Database Backup to a specific NFS directory:

 $ gpcrondump -x <db_name> -z -u /backup/DCA-93 -a

2. Create a file listing the segment servers:

 $ vi /home/gpadmin/segments

 sdw1

 sdw2

 sdw3

 ….

3. Remove the relevant date folder from db_dumps directories on the master and 

 segments:

 $ rm -r /data/master/gpseg-1/db_dumps/20120619

 $ gpssh -f segments 'rm -r 

 /data1/primary/gpseg*/db_dumps/20120619'

 $ gpssh -f segments 'rm -r 

/data2/primary/gpseg*/db_dumps/20120619'

4. Create a symbolic link between the master and segment directories and the 

 directory to which you backed up in step 1. Only the master and sdw1 was 

 shown here, write a script for the remaining segments:

 $ ln -s /backup/DCA-93/db_dumps/20120619 

 /data/master/gpseg-1/db_dumps/20120619

 $ gpssh -h sdw1 'ln -s /backup/DCA-93/db_dumps/20120619 

 /data1/primary/gpseg0/db_dumps/20120619'

 $ gpssh -h sdw1 'ln -s /backup/DCA-93/db_dumps/20120619 

 /data1/primary/gpseg1/db_dumps/20120619'

 $ gpssh -h sdw1 'ln -s /backup/DCA-93/db_dumps/20120619 

 /data1/primary/gpseg2/db_dumps/20120619'

 $ gpssh -h sdw1 'ln -s /backup/DCA-93/db_dumps/20120619 

 /data2/primary/gpseg3/db_dumps/20120619'

 $ gpssh -h sdw1 'ln -s /backup/DCA-93/db_dumps/20120619 

 /data2/primary/gpseg4/db_dumps/20120619'

 $ gpssh -h sdw1 'ln -s /backup/DCA-93/db_dumps/20120619 

 /data2/primary/gpseg5/db_dumps/20120619'

5. Restore from backup files:

 $ gpdbrestore -t 20120619061835 -T <schema.table> -a

6. Remove the symbolic links

 $ rrm -r /data/master/gpseg-1/db_dumps/20120619

 $ gpssh -f segments 'rm -r 

 /data1/primary/gpseg*/db_dumps/20120619'

 $ gpssh -f segments 'rm -r 

 /data2/primary/gpseg*/db_dumps/20120619'