pg_restore fails when trying to create function referencing table that does not exist yet

David_Wolever_71522 0
Viewed 2055

I've used pg_dump --no-privileges --format custom --compress=0 some_database > my-dump.pgdump to dump a database, but I'm running into issues when I try to restore it.

Specifically, it appears to be loading function definitions before table definitions:

$ pg_restore ./my-dump.pgdump
…

create function my_function() returns …
language sql $$
  select …
  from some_table
  where …
$$;

… later in the dump …

create table some_table ( … );

…

Which causes an error when I try to restore the dump:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4863; 0 16735 TABLE DATA some_table some_database
pg_restore: [archiver (db)] COPY failed for table "some_table": ERROR:  relation "some_table" does not exist
LINE 3:                     from some_table
                                 ^
QUERY:
                    select …
                    from some_table
                    where …

CONTEXT:  SQL function "my_function" during inlining

What's going on here? How can I trick pg_dump / pg_restore into doing things in the correct order?

pg-restore pg-dump postgresql
3 Answers

Check your dump file for commands which mess with search_path, for example:

SELECT pg_catalog.set_config('search_path', '', false);

I encountered the same kind error as you (relation xxx does not exist ... during inlining) in a legacy project I inherited, even though it's running PostgreSQL 9.4.x.

I traced it to the above command.

The solution for me was to remove this command from the dump file.

After I did this I was able to restore the database without errors.

user_32040230

That is strange. Ever since commit ef88199f611e625b496ff92aa17a447d254b9796 in 2003, pg_dump and pg_restore have emitted 

SET check_function_bodies = false;

This setting makes sure that an error like you describe won't happen, because PostgreSQL won't check the validity of the function bodies.

Are you using an ancient PostgreSQL version or are you doing anything else that could mess with that?

If you run pg_restore on your dump (without specifying a destination database), does it emit the line?

user_64643080

Note: the OP is using the custom format. There is no editing the binary file that is emitted.

In my experience, pg_dump using the custom format (-Fc) doesn't set check_function_bodies = false. But since it adds random functions at the top of the dump file (instead of putting all routines at the end), this causes pg_restore to barf.

I was able to workaround this issue by setting PGOPTIONS:

export PGOPTIONS="-c check_function_bodies=false"
pg_restore ...
user_10825910
Related