trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order - Mailing list pgsql-general
| From | Harry Green |
|---|---|
| Subject | trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order |
| Date | |
| Msg-id | 73791286.4632684.1695049729344@mail.yahoo.com Whole thread Raw |
| Responses |
Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order
Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order |
| List | pgsql-general |
Hello,
I am having trouble restoring a database backed up with pg_dump/pg_dump_all. The error messages I get are below and appear to suggest that certain sql-language or pl/pgsql-language functions which include an sql statement referencing a table are trying to be executed before the table which they reference has been created. I am surprised that pg_dump could get the order wrong, but that is what is happening.
Looking at the first of the error messages, the pl/pgsql function is created as create function public.check_account_from_bill_items(character...) on line 95, but the instruction to create the accounts table '... create table public.accounts ... ' appears on line 510.
Consequently, the restore does not work because the relations are created in the wrong order. Any ideas how I can solve it?
Please see the error message below:
psql:20230913_1300.sql: ERROR: relation "accounts" does not exist
LINE 1: select name from accounts where (type='Expense' or ...
^
QUERY: select name from accounts where (type='Expense' or type='Asset' or type='Stock') and name=account
CONTEXT: PL/pgSQL function public.check_account_from_bill_items(character varying) line 6 at SQL statement
COPY bill_items, line 1: "4096 Website 0 11.6099999999999994 \N \N 0 11.6099999999999994 5852 1 \N"
psql:20230913_1300.sql:201718: ERROR: relation "ebooks" does not exist
LINE 1: select count(*) from ebooks where isbn13=$1
^
QUERY: select count(*) from ebooks where isbn13=$1
CONTEXT: SQL function "ebook_records_with_isbn13" during inlining
COPY book_information, line 1: "Title abcd \N \N 1-2222-2222-1 111-1-2222-2222-1 11.11 111.11 1 \N ..."
psql:20230913_1300.sql:1181710: ERROR: relation "book_information" does not exist
LINE 1: select e_isbn from book_information where title=$1 limit 1
^
QUERY: select e_isbn from book_information where title=$1 limit 1
CONTEXT: SQL function "e_isbn_of" during inlining
COPY ebooks, line 1: "XYZ \N ..."
psql:20230913_1300.sql:2670734: ERROR: relation "book_information" does not exist
LINE 1: select id from book_information where (title,bindin...
^
QUERY: select id from book_information where (title,binding,LOWER(isbn))=(title1,binding1,LOWER(isbn1))
CONTEXT: PL/pgSQL function public.in_book_information(character varying,character varying,character varying) line 6 at SQL statement
COPY invoice_details, line 1: "vvvvvv Cr 200800432 29243 ..."
psql:20230913_1300.sql:20459005: ERROR: insert or update on table "bi_authors" violates foreign key constraint "bi_authors_book_information_id_fkey"
DETAIL: Key (book_information_id)=(10442) is not present in table "book_information".
psql:20230913_1300.sql:20459125: ERROR: insert or update on table "book_praise" violates foreign key constraint "book_praise_title_fkey"
DETAIL: Key (title, binding, isbn)=(XXCCC, back, 1111-55) is not present in table "book_information".
psql:20230913_1300.sql:20459381: ERROR: insert or update on table "posters" violates foreign key constraint "posters_isbn_fkey"
DETAIL: Key (isbn)=(1-3333-3333-3) is not present in table "book_information".
psql:20230913_1300.sql:20459413: ERROR: insert or update on table "preorder_items" violates foreign key constraint "preorder_items_title_fkey"
DETAIL: Key (title, binding, isbn)=(xxxx, xxxx, x) is not present in table "book_information".
psql:20230913_1300.sql:20459461: ERROR: insert or update on table "purchase_orders" violates foreign key constraint "purchase_orders_isbn_fkey"
DETAIL: Key (isbn)=(2-22222-12-1) is not present in table "book_information".
psql:20230913_1300.sql:20459469: ERROR: insert or update on table "purchase_orders" violates foreign key constraint "purchase_orders_item_fkey"
DETAIL: Key (item, type_or_binding, isbn)=(title, back, 2222) is not present in table "book_information".
psql:20230913_1300.sql:20459653: ERROR: insert or update on table "recurring_orders_detail" violates foreign key constraint "recurring_orders_detail_isbn_fkey"
DETAIL: Key (isbn)=(0-1111-1) is not present in table "book_information".
psql:20230913_1300.sql:20459701: ERROR: insert or update on table "royalties_paid" violates foreign key constraint "royalties_paid_bill_item_id_fkey"
DETAIL: Key (bill_item_id)=(3151) is not present in table "bill_items".
psql:20230913_1300.sql:20459765: ERROR: insert or update on table "spine_locations" violates foreign key constraint "spine_locations_isbn_fkey"
DETAIL: Key (isbn)=(0-00000) is not present in table "book_information".
psql:20230913_1300.sql:20459773: ERROR: insert or update on table "stock_boxed" violates foreign key constraint "stock_boxed_isbn_fkey"
DETAIL: Key (isbn)=(1-2121) is not present in table "book_information".
psql:20230913_1300.sql:20459781: ERROR: insert or update on table "stock_boxed" violates foreign key constraint "stock_boxed_title_fkey"
DETAIL: Key (title, binding, isbn)=(title, back, 3-3333) is not present in table "book_information".
psql:20230913_1300.sql:20459805: ERROR: insert or update on table "stock_corrections" violates foreign key constraint "stock_corrections_title_fkey"
DETAIL: Key (title, binding)=(Title asd, back) is not present in table "book_information".
psql:20230913_1300.sql:20459837: ERROR: insert or update on table "stock_transfers" violates foreign key constraint "stock_transfers_title_fkey"
DETAIL: Key (title, binding, isbn)=(The, Hardback, 0-0000) is not present in table "book_information".
Thanks a lot!
pgsql-general by date:
Previous
From: UtkuDate:
Subject: How to synchronize the read/write DB on my laptop with the read-only DB on cloud (primary on premises, replica on cloud)?
Next
From: Adrian KlaverDate:
Subject: Re: trouble restoring a database backed up with pg_dump/pg_dump_all - relations are created in the wrong order