Pivotal Knowledge Base

Follow

GPDBRESTORE fails to Backup when MADlib was installed

Environment

 Product  Version
 Pivotal Greenplum   4.3.x
 OS  RHEL 6.x

Symptom

gpdbrestore command used:

gpdbrestore -t 20161111122033 --table-file <file_with_tables_to_restore> t --truncate

gpdbrestore console output:

[gpadmin@mdw ~]$ gpdbrestore -t 20161111122033 --table-file tables.txt --truncate
20161118:19:52:00:034474 gpdbrestore:mdw:gpadmin-[INFO]:-Starting gpdbrestore with args: -t 20161111122033 --table-file tables.txt --truncate
20161118:19:52:00:034474 gpdbrestore:mdw:gpadmin-[INFO]:-------------------------------------------
20161118:19:52:00:034474 gpdbrestore:mdw:gpadmin-[INFO]:-Greenplum database restore parameters
20161118:19:52:00:034474 gpdbrestore:mdw:gpadmin-[INFO]:-------------------------------------------
20161118:19:52:00:034474 gpdbrestore:mdw:gpadmin-[INFO]:-Restore type               = Table Restore
20161118:19:52:00:034474 gpdbrestore:mdw:gpadmin-[INFO]:-Database name              = testdb
20161118:19:52:00:034474 gpdbrestore:mdw:gpadmin-[INFO]:-------------------------------------------
20161118:19:52:00:034474 gpdbrestore:mdw:gpadmin-[INFO]:-Table restore list
20161118:19:52:00:034474 gpdbrestore:mdw:gpadmin-[INFO]:-------------------------------------------
20161118:19:52:00:034474 gpdbrestore:mdw:gpadmin-[INFO]:-Table                      = testschema.testtable
20161118:19:52:00:034474 gpdbrestore:mdw:gpadmin-[INFO]:-Restore method             = Specific table restore
20161118:19:52:00:034474 gpdbrestore:mdw:gpadmin-[INFO]:-Restore timestamp          = 20161111122033
20161118:19:52:00:034474 gpdbrestore:mdw:gpadmin-[INFO]:-Restore compressed dump    = On
20161118:19:52:00:034474 gpdbrestore:mdw:gpadmin-[INFO]:-Restore global objects     = Off
20161118:19:52:00:034474 gpdbrestore:mdw:gpadmin-[INFO]:-Array fault tolerance      = f
20161118:19:52:00:034474 gpdbrestore:mdw:gpadmin-[INFO]:-------------------------------------------

Continue with Greenplum restore Yy|Nn (default=N):
> y
20161118:19:52:01:034474 gpdbrestore:mdw:gpadmin-[WARNING]:-Skipping truncate of testdb.testschema.testtable because the relation does not exist.
20161118:19:52:02:034474 gpdbrestore:mdw:gpadmin-[INFO]:-Running metadata restore
20161118:19:52:02:034474 gpdbrestore:mdw:gpadmin-[INFO]:-Invoking commandline: gp_restore -i -h mdw -p 5432 -U gpadmin --gp-i --gp-k=20161111122033 --gp-l=p -s /gpdata/master/gpseg-1/db_dumps/20161111/gp_dump_1_1_20161111122033.gz -P --gp-d=db_dumps/20161111 --gp-f=/tmp/table_list_IgtFQG --gp-c -d "testdb"
20161118:19:52:04:034474 gpdbrestore:mdw:gpadmin-[WARNING]:-gpdbrestore finished but ERRORS were found, please check the restore report file for details
20161118:19:52:04:034474 gpdbrestore:mdw:gpadmin-[INFO]:-Expanding parent partitions if any in table filter
20161118:19:52:04:034474 gpdbrestore:mdw:gpadmin-[INFO]:-gp_restore commandline: gp_restore -i -h mdw -p 5432 -U gpadmin --gp-i --gp-k=20161111122033 --gp-l=p --gp-d=db_dumps/20161111 --gp-f=/tmp/table_list_OFlnos --gp-c -d "testdb" -a:
20161118:19:52:06:034474 gpdbrestore:mdw:gpadmin-[WARNING]:-gpdbrestore finished but ERRORS were found, please check the restore report file for details
20161118:19:52:06:034474 gpdbrestore:mdw:gpadmin-[INFO]:-Running post data restore
20161118:19:52:06:034474 gpdbrestore:mdw:gpadmin-[INFO]:-gp_restore commandline: gp_restore -i -h mdw -p 5432 -U gpadmin --gp-d=db_dumps/20161111 --gp-i --gp-k=20161111122033 --gp-l=p -P --gp-f=/tmp/table_list_OFlnos --gp-c -d "testdb":
20161118:19:52:08:034474 gpdbrestore:mdw:gpadmin-[WARNING]:-gpdbrestore finished but ERRORS were found, please check the restore report file for details
20161118:19:52:08:034474 gpdbrestore:mdw:gpadmin-[INFO]:-Updating AO/CO statistics on master
20161118:19:52:08:034474 gpdbrestore:mdw:gpadmin-[INFO]:-No AO/CO tables restored, skipping statistics update...
20161118:19:52:08:034474 gpdbrestore:mdw:gpadmin-[INFO]:-Commencing analyze of restored tables in 'testdb' database, please wait
20161118:19:52:08:034474 gpdbrestore:mdw:gpadmin-[WARNING]:-Restore of 'testdb' database succeeded but 'ANALYZE' of restored tables failed
20161118:19:52:08:034474 gpdbrestore:mdw:gpadmin-[WARNING]:-Please run ANALYZE manually on restored tables. Failure to run ANALYZE might result in poor database performance
20161118:19:52:08:034474 gpdbrestore:mdw:gpadmin-[CRITICAL]:-gpdbrestore failed. (Reason='Issue with 'ANALYZE' of restored table '"testtable"."testschema"' in 'testdb' database') exiting...

gpdbrestore report file:

[gpadmin@mdw gpseg-1]$ cat gp_restore_20161111122033.rpt

Greenplum Database Restore Report
Timestamp Key: 20161111122033
gp_restore Command Line: -i -h mdw -p 5432 -U gpadmin --gp-d=db_dumps/20161111 --gp-i --gp-k=20161111122033 --gp-l=p -P --gp-f=/tmp/table_list_uqVLQm --gp-c -d testdb
Pass through Command Line Options: --gp-f /tmp/table_list_uqVLQm --post-data-schema-only
Compression Program: gunzip

Individual Results
    Restore of testdb on dbid 1 (mdw:5432) from /gpdata/master/gpseg-1/db_dumps/20161111/gp_dump_1_1_20161111122033_post_data.gz: Completed but errors were found:
{
ERROR:  schema "testschema" already exists
20161118:20:21:01|gp_restore_agent-[INFO]:-psql finished with rc 0.
20161118:20:21:03|gp_restore_agent-[INFO]:-Succeeded
20161118:20:21:03|gp_restore_agent-[INFO]:-Finished successfully
20161118:20:21:06|gp_restore_agent-[INFO]:-Before fork of gp_restore_agent
20161118:20:21:06|gp_restore_agent-[INFO]:-Starting monitor thread
20161118:20:21:06|gp_restore_agent-[INFO]:-Command Line: /bin/cat /gpdata/master/gpseg-1/db_dumps/20161111/gp_dump_1_1_20161111122033_post_data.gz | /bin/gunzip -c  | /usr/local/greenplum-db/./bin/gprestore_post_data_filter.py -t /tmp/table_list_uqVLQm | /usr/local/greenplum-db/./bin/psql -h mdw -p 5432 -U gpadmin -d "testdb" -a
20161118:20:21:06|gp_restore_agent-[INFO]:-psql finished with rc 0.
20161118:20:21:08|gp_restore_agent-[INFO]:-Succeeded
20161118:20:21:08|gp_restore_agent-[INFO]:-Finished successfully
}

gp_restore  utility finished but errors were found.
[gpadmin@mdw gpseg-1]$

gpdbrestore status file:

[gpadmin@mdw gpseg-1]$ cat gp_restore_status_1_1_20161111122033
20161118:20:21:01|gp_restore_agent-[INFO]:-Before fork of gp_restore_agent
20161118:20:21:01|gp_restore_agent-[INFO]:-Starting monitor thread
20161118:20:21:01|gp_restore_agent-[INFO]:-Command Line: /bin/cat /gpdata/master/gpseg-1/db_dumps/20161111/gp_dump_1_1_20161111122033.gz | /bin/gunzip -c  | /usr/local/greenplum-db/./bin/gprestore_filter.py -m -t /tmp/table_list_kIzUyI | /usr/local/greenplum-db/./bin/psql -h mdw -p 5432 -U gpadmin -d "testdb" -a
Traceback (most recent call last):
  File "/usr/local/greenplum-db/./bin/gprestore_filter.py", line 390, in <module>
    process_schema(schemas, tables, sys.stdin, sys.stdout, change_schema_name, schema_level_restore_list)
  File "/usr/local/greenplum-db/./bin/gprestore_filter.py", line 133, in process_schema
    schema = extract_schema(line)
  File "/usr/local/greenplum-db/./bin/gprestore_filter.py", line 273, in extract_schema
    raise Exception('Failed to extract schema name from line %s' % line)
Exception: Failed to extract schema name from line SET search_path = pg_catalog;

ERROR:  schema "testschema" already exists
20161118:20:21:01|gp_restore_agent-[INFO]:-psql finished with rc 0.
20161118:20:21:03|gp_restore_agent-[INFO]:-Succeeded
20161118:20:21:03|gp_restore_agent-[INFO]:-Finished successfully
20161118:20:21:06|gp_restore_agent-[INFO]:-Before fork of gp_restore_agent
20161118:20:21:06|gp_restore_agent-[INFO]:-Starting monitor thread
20161118:20:21:06|gp_restore_agent-[INFO]:-Command Line: /bin/cat /gpdata/master/gpseg-1/db_dumps/20161111/gp_dump_1_1_20161111122033_post_data.gz | /bin/gunzip -c  | /usr/local/greenplum-db/./bin/gprestore_post_data_filter.py -t /tmp/table_list_uqVLQm | /usr/local/greenplum-db/./bin/psql -h mdw -p 5432 -U gpadmin -d "testdb" -a
20161118:20:21:06|gp_restore_agent-[INFO]:-psql finished with rc 0.
20161118:20:21:08|gp_restore_agent-[INFO]:-Succeeded
20161118:20:21:08|gp_restore_agent-[INFO]:-Finished successfully
[gpadmin@mdw gpseg-1]$

gpdbrestore status file on a primary segment:

[gpadmin@mdw gpseg0]$ cat gp_restore_status_0_2_20161111122033
20161118:20:21:04|gp_restore_agent-[INFO]:-Before fork of gp_restore_agent
20161118:20:21:04|gp_restore_agent-[INFO]:-Starting monitor thread
20161118:20:21:04|gp_restore_agent-[INFO]:-Command Line: /usr/bin/cat /gpdata/segments/primary/gpseg0/db_dumps/20161111/gp_dump_0_2_20161111122033.gz | /usr/bin/gunzip -c  | /usr/local/greenplum-db/./bin/gprestore_filter.py -t /tmp/table_list_uqVLQm | /usr/local/greenplum-db/./bin/psql -h mdw -p 40000 -U gpadmin -d "testdb" -a
ERROR:  relation "testtable" does not exist
invalid command \N
invalid command \N
invalid command \N
invalid command \N
invalid command \N
invalid command \N
invalid command \N
invalid command \N
invalid command \N
invalid command \N
invalid command \.
ERROR:  syntax error at or near "1"
LINE 1: 1
        ^
20161118:20:21:04|gp_restore_agent-[INFO]:-psql finished with rc 0.
20161118:20:21:06|gp_restore_agent-[INFO]:-Succeeded
20161118:20:21:06|gp_restore_agent-[INFO]:-Finished successfully
[gpadmin@mdw gpseg0]$

Cause 

The problem occurs when trying to restore from a backup set where MADlib was installed.

Resolution

This restore issue is fixed in Pivotal Greenplum Database 5.0. The only workaround is to make sure that the table(s) being restored are recreated in the database and that those tables are recreated in the same fashion before you proceed with the restore process using gpdbrestore --table-file option.

 

Comments

Powered by Zendesk