Pivotal Knowledge Base

Follow

How to dump function DDL using pg_dump

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.x
OS RHEL 6.x  

Purpose
When fixing certain inconsistency catalog errors (where one function has different values in certain catalog tables among different segment instances), we need to rebuild some user-defined functions. So recreating those functions is the best way to fix those errors. 

Procedure
The process is to backup the function DDL, drop the function, and then create the function using the previous DDL. The goal is to make sure that this function has the same value in catalog tables among all segments. There are 3 steps to generate the function DDL:

  1. pg_dump -Fc -s -n <schemaname> -f temp.dump <database name>
  2. pg_restore -l temp.dump | grep FUNCTION > functionlist
  3. pg_restore -L functionlist temp.dump > yourfunctions.sql

 

 

Comments

Powered by Zendesk