Pivotal Knowledge Base


How to Dump Function DDL using pg_dump


  • Pivotal Greenplum 4.3.x
  • Operating System- Red Hat Enterprise Linux 6.x

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.


The process is to back up 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:

  • pg_dump -Fc -s -n <schemaname> -f temp.dump <database name>
  • pg_restore -l temp.dump | grep FUNCTION > functionalist
  • pg_restore -L functionalist temp.dump > yourfunctions.sql



Powered by Zendesk