Agensgraph : scripts which are part of pgdump failing in restore

[ns-berumalu]

We are using agensgraph of version v2.12 for our environment. I have taken a db backup using pg_dump and when we are trying to restore it a few inserts scripts which are part of the backups failing in restore. Could you please suggest this issue?

Below are the scripts which are part of pgdump backup.

INSERT INTO pg_catalog.ag_graph

(SELECT nspname, oid FROM pg_catalog.pg_namespace

WHERE nspname = 'xxx');

INSERT INTO pg_catalog.pg_depend

(SELECT 2615, nspid, 0, 7040, oid, 0, 'i'

FROM pg_catalog.ag_graph);

INSERT INTO pg_catalog.ag_label

(SELECT c.relname, g.oid, 1, c.oid, 'v'

FROM pg_catalog.ag_graph g

JOIN pg_catalog.pg_namespace n ON n.oid = g.nspid

JOIN pg_catalog.pg_class c ON c.relnamespace = n.oid

WHERE g.graphname = 'xxx' AND c.relname = 'ag_vertex');

INSERT INTO pg_catalog.ag_label

(SELECT c.relname, g.oid, 2, c.oid, 'e'

FROM pg_catalog.ag_graph g

JOIN pg_catalog.pg_namespace n ON n.oid = g.nspid

JOIN pg_catalog.pg_class c ON c.relnamespace = n.oid

WHERE g.graphname = 'xxx' AND c.relname = 'ag_edge');

INSERT INTO pg_catalog.ag_label

(SELECT c.relname, g.oid, 3, c.oid, 'e'

FROM pg_catalog.ag_graph g

JOIN pg_catalog.pg_namespace n ON n.oid = g.nspid

JOIN pg_catalog.pg_class c ON c.relnamespace = n.oid

WHERE g.graphname = 'xxx' AND c.relname = 'owns');

INSERT INTO pg_catalog.ag_label

(SELECT c.relname, g.oid, 4, c.oid, 'e'

FROM pg_catalog.ag_graph g

JOIN pg_catalog.pg_namespace n ON n.oid = g.nspid

JOIN pg_catalog.pg_class c ON c.relnamespace = n.oid

WHERE g.graphname = 'xxx' AND c.relname = 'refers');

INSERT INTO pg_catalog.ag_label

(SELECT c.relname, g.oid, 5, c.oid, 'v'

FROM pg_catalog.ag_graph g

JOIN pg_catalog.pg_namespace n ON n.oid = g.nspid

JOIN pg_catalog.pg_class c ON c.relnamespace = n.oid

WHERE g.graphname = 'xxx' AND c.relname = 'xxx');

INSERT INTO pg_catalog.ag_label

(SELECT c.relname, g.oid, 6, c.oid, 'v'

FROM pg_catalog.ag_graph g

JOIN pg_catalog.pg_namespace n ON n.oid = g.nspid

JOIN pg_catalog.pg_class c ON c.relnamespace = n.oid

WHERE g.graphname = 'xxx' AND c.relname = 'xxx');

INSERT INTO pg_catalog.ag_label

(SELECT c.relname, g.oid, 7, c.oid, 'v'

FROM pg_catalog.ag_graph g

JOIN pg_catalog.pg_namespace n ON n.oid = g.nspid

JOIN pg_catalog.pg_class c ON c.relnamespace = n.oid

WHERE g.graphname = 'xx' AND c.relname = 'xxx');

INSERT INTO pg_catalog.ag_label

(SELECT c.relname, g.oid, 8, c.oid, 'v'

FROM pg_catalog.ag_graph g

JOIN pg_catalog.pg_namespace n ON n.oid = g.nspid

JOIN pg_catalog.pg_class c ON c.relnamespace = n.oid

WHERE g.graphname = 'xxx' AND c.relname = 'xxx');

INSERT INTO pg_catalog.ag_label

(SELECT c.relname, g.oid, 9, c.oid, 'v'

FROM pg_catalog.ag_graph g

JOIN pg_catalog.pg_namespace n ON n.oid = g.nspid

JOIN pg_catalog.pg_class c ON c.relnamespace = n.oid

WHERE g.graphname = 'xxx' AND c.relname = 'xxx');

INSERT INTO pg_catalog.ag_label

(SELECT c.relname, g.oid, 10, c.oid, 'v'

Errors in restore :

agens:/tmp/all.sql:6740: ERROR: column "oid" is of type oid but expression is of type name LINE 2: (SELECT nspname, oid FROM pg_catalog.pg_namespace ^ HINT: You will need to rewrite or cast the expression. INSERT 0 0 agens:/tmp/all.sql:6750: ERROR: column "oid" is of type oid but expression is of type name LINE 2: (SELECT c.relname, g.oid, 1, c.oid, 'v' ^ HINT: You will need to rewrite or cast the expression. agens:/tmp/all.sql:6756: ERROR: column "oid" is of type oid but expression is of type name LINE 2: (SELECT c.relname, g.oid, 2, c.oid, 'e' ^ HINT: You will need to rewrite or cast the expression. agens:/tmp/all.sql:6762: ERROR: column "oid" is of type oid but expression is of type name LINE 2: (SELECT c.relname, g.oid, 3, c.oid, 'e' ^ HINT: You will need to rewrite or cast the expression. agens:/tmp/all.sql:6768: ERROR: column "oid" is of type oid but expression is of type name LINE 2: (SELECT c.relname, g.oid, 4, c.oid, 'e' ^ HINT: You will need to rewrite or cast the expression. agens:/tmp/all.sql:6774: ERROR: column "oid" is of type oid but expression is of type name LINE 2: (SELECT c.relname, g.oid, 5, c.oid, 'v' ^ HINT: You will need to rewrite or cast the expression. agens:/tmp/all.sql:6780: ERROR: column "oid" is of type oid but expression is of type name LINE 2: (SELECT c.relname, g.oid, 6, c.oid, 'v' ^ HINT: You will need to rewrite or cast the expression. agens:/tmp/all.sql:6786: ERROR: column "oid" is of type oid but expression is of type name LINE 2: (SELECT c.relname, g.oid, 7, c.oid, 'v' ^ HINT: You will need to rewrite or cast the expression. agens:/tmp/all.sql:6792: ERROR: column "oid" is of type oid but expression is of type name LINE 2: (SELECT c.relname, g.oid, 8, c.oid, 'v' ^ HINT: You will need to rewrite or cast the expression. agens:/tmp/all.sql:6798: ERROR: column "oid" is of type oid but expression is of type name LINE 2: (SELECT c.relname, g.oid, 9, c.oid, 'v' ^ HINT: You will need to rewrite or cast the expression. agens:/tmp/all.sql:6804: ERROR: column "oid" is of type oid but expression is of type name LINE 2: (SELECT c.relname, g.oid, 10, c.oid, 'v' ^ HINT: You will need to rewrite or cast the expression. agens:/tmp/all.sql:6810: ERROR: column "oid" is of type oid but expression is of type name LINE 2: (SELECT c.relname, g.oid, 11, c.oid, 'v' ^ HINT: You will need to rewrite or cast the expression. agens:/tmp/all.sql:6816: ERROR: column "oid" is of type oid but expression is of type name LINE 2: (SELECT c.relname, g.oid, 12, c.oid, 'v' ^ HINT: You will need to rewrite or cast the expression. agens:/tmp/all.sql:6822: ERROR: column "oid" is of type oid but expression is of type name FROM pg_catalog.ag_graph g

[emotionbug]

There is a bug related to pg_dump in the current released version. That patch for fixing bug is applied, but there is no specific release date.

I would tell recommend that uses build that using patched branch.

Patch : https://github.com/bitnine-oss/agensgraph/pull/571

v2.12 ( PG12 ) / https://github.com/bitnine-oss/agensgraph/commit/b6a1729c23aebbeb17d34043c2d496d24688d6cc v2.13 ( PG13 ) / https://github.com/bitnine-oss/agensgraph/commit/8f18117f24b230cac0d928ff4da38134875ccf6e

[ns-berumalu]

Hi, what we can do now for our running setup to avoid the issue. where to do change. Or should i wait until next release.

we are having production databases and need to restore and getting restore issues. Please suggest

[ns-berumalu]

Hi Alex,

Please let me know how to mitigate this issue to out current setup

On Thu, Sep 15, 2022 at 12:47 PM Alex Kwak @.***> wrote:

There is a bug related to pg_dump in the current released version. this patch for fixing bug is applied, but there is no specific release date.

I would tell recommend that uses build that using patched branch. b6a1729 https://github.com/bitnine-oss/agensgraph/commit/b6a1729c23aebbeb17d34043c2d496d24688d6cc 8f18117 https://github.com/bitnine-oss/agensgraph/commit/8f18117f24b230cac0d928ff4da38134875ccf6e

— Reply to this email directly, view it on GitHub https://github.com/bitnine-oss/agensgraph/issues/587#issuecomment-1247681945, or unsubscribe https://github.com/notifications/unsubscribe-auth/AZ6A2UOI3PPE7JABZCMK2MLV6LER7ANCNFSM6AAAAAAQNCM5Q4 . You are receiving this because you authored the thread.Message ID: @.***>

[emotionbug]

@ns-berumalu

Could you tell me which version are you using? Version? Docker? Prebuilt binaries? Manually build with source code?

[ns-berumalu]

we have installed version 2.12 using source code

On Thu, Sep 15, 2022 at 6:39 PM Alex Kwak @.***> wrote:

@ns-berumalu https://github.com/ns-berumalu

Could you tell me which version are you using? Version? Docker? Prebuilt binaries? Manually build with source code?

— Reply to this email directly, view it on GitHub https://github.com/bitnine-oss/agensgraph/issues/587#issuecomment-1248079462, or unsubscribe https://github.com/notifications/unsubscribe-auth/AZ6A2UI7LFU55DC4NOO3NKLV6MNWTANCNFSM6AAAAAAQNCM5Q4 . You are receiving this because you were mentioned.Message ID: @.***>

[ns-berumalu]

@emotionbug Alex Kwak

could you please update on this

[emotionbug]

@ns-berumalu I have other work to do, so I will answer by until next Monday. sorry. :(

[emotionbug]

@ns-berumalu https://github.com/bitnine-oss/agensgraph/releases/tag/v2.12.0_hotfix_1

After checking this, pg_dump previously attempted to insert Oid, so it cannot be solved without a patch. A patch has been created for this. Upgrade to this and try again.

[ns-berumalu]

@emotionbug thanks for the hotfix. Already we have a setup v 2.12 with pgpool configuration. And we would like to use same directory structure for the binaries and data directories during the upgrade. Could you please share the steps how to upgrade.

[emotionbug]

Reinstall with same prefix and restart. ( It will not require any other instruction ).

configure ... --prefix=...
make
make install
.../pg_ctl -D ... stop
.../pg_ctl -D ... start

[ns-berumalu]

@emotionbug do you mean , overwrite the binaries. What about data if we have .

[emotionbug]

@ns-berumalu yes

[ns-berumalu]

@emotionbug ,

we have 2 statndbys for primary. Do we need to recreate them or overwirete directly

[emotionbug]

@ns-berumalu I think you can choose in the direction you want. Anyway needed.