0038179: cannot restore pg dump - Openbravo Issue Tracking System

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0038179

Openbravo ERP

A. Platform

public

2018-03-20 16:53

2018-03-23 21:22

Reporter

alostale

Assigned To

alostale

Priority

immediate

Severity

major

Reproducibility

have not tried

Status

closed

Resolution

fixed

Platform

OS Version

Product Version

Target Version

Fixed in Version

3.0PR18Q2

Merge Request Status

Review Assigned To

caristu

OBNetwork customer

Web browser

Modules

Core

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

0038179: cannot restore pg dump

Description

It is not possible to restore a PostgreSQL database dump.

Affected PostgreSQL versions (all of them released on 2018-03-01):
* 10.3+
* 9.6.8+
* 9.5.12+
* 9.4.17+
* 9.3.22+

Note this to be reproduced, dump needs to be acquired with one of those versions of higher. Dumps obtained with older versions can be properly imported.

More detailed explanations:

https://stackoverflow.com/questions/49380321/pg-restore-cant-import-data-if-table-has-a-constraint-with-a-function-calling [^]
https://www.postgresql.org/message-id/152153826367.11956.8092048336300020216%40wrigleys.postgresql.org [^]

Steps To Reproduce

In an Openbravo appliance:
1. Update pg: sudo apt-get update && sudo apt-get dist-upgrade
  -> check it is at least in 9.3.22
2. Dump DB: pg_dump -h localhost -p 5432 -F c -b -v -f test.dmp openbravo -U tad
3. Drop openbravo DB and create it again.
4. Import DB: pg_restore -d openbravo -U tad -v test.dmp -h localhost
  -> Check logs:
pg_restore: processing data for table "public.ad_tab"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 14059; 0 4295695 TABLE DATA ad_tab tad
pg_restore: [archiver (db)] COPY failed for table "ad_tab": ERROR: function instr(character varying, character varying, integer) does not exist
LINE 1: SELECT instr($1, $2, 1)
               ^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT instr($1, $2, 1)
CONTEXT: PL/pgSQL function public.instr(character varying,character varying) line 4 at assignment
COPY ad_tab, line 1: "100 0 0 Y 2016-09-02 09:59:59.438222 0 2016-09-02 09:59:59.438222 0 Table Define tables that Openbra..."

Data is not imported for failing tables: ad_tab, ad_window...

Proposed Solution

Workaround
In instances where the fix for this issue is not applied yet, it is possible to dump + restore following one of these two workarounds:

1. To obtain a dump that can be restored without problem
  * before dumping DB, if patch for this issue is not applied, execute the following script [1]

2. To restore a dump that was obtained without applying 1st workaround:
  * Restore schema without data adding to pg_restore --schema-only option [2]
  * Run the following script [1]
  * Restore data only adding to pg_restore --data-only --disable-triggers options [2]

---
[1] https://github.com/alostale/ob-scripts/blob/master/sql/fixes/pg-schema-fix.sql [^]
[2] https://www.postgresql.org/docs/9.3/static/app-pgrestore.html [^]

Additional Information

Tags

No tags attached.

Relationships

depends on	backport	0038190	3.0PR18Q1.1	closed	alostale	cannot restore pg dump
depends on	backport	0038191	3.0PR17Q4.2	closed	alostale	cannot restore pg dump
related to	defect	0040317		closed	alostale	can't restore pg dump of a retail instance
related to	feature request	0029943		closed	alostale	support to define DB functions volatility