7

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?

David Wolever
  • 148,955
  • 89
  • 346
  • 502

3 Answers3

5

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.

jdhildeb
  • 3,322
  • 3
  • 17
  • 25
  • Errors of that kind point to insecure function definitions. It is better to fix the original function. – Laurenz Albe Aug 18 '21 at 03:28
  • There appears to be a security related reason why the set search_path was done in the dump: https://www.postgresql.org/message-id/332441538573896%40sas2-7b909973f402.qloud-c.yandex.net – quickdraw Aug 18 '21 at 08:19
  • OP's dump format is custom, so you can't edit that kind of dump file. – quickdraw Aug 18 '21 at 08:19
  • 1
    I basically had to prefix the table name in the check query with the schema name, e.g. `public.users` instead of `users`. Worked like a charm right after. – demisx Mar 11 '22 at 23:56
2

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 ...
quickdraw
  • 101
  • 8
0

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?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • What is silly here is that the -s param considers routines "schema". You should be able to restore only the table definitions, and then the functions. Or better, pg_restore should know this is an issue and make two passes on the dump file to first do tables only. Or even better still, pg_dump should dump routines last. – quickdraw Aug 18 '21 at 02:55
  • @quickdraw Functions are certainly part of the object definitions (schema). You are suggesting a feature to selectively restore functions or not, but that seems to have little to do with the question. – Laurenz Albe Aug 18 '21 at 03:27
  • While routines are technically DDL, I argue they are not database structure, and certainly not data, the two things people primarily care about when restoring a dump. Yes, you need all bits and pieces eventually, but you need to be able to easily restore just structure and data (including composite types referenced by table columns). pg_restore seems hell bent to make just restoring structure and data as hard as possible, IMO. – quickdraw Aug 18 '21 at 04:55
  • @quickdraw I disagree. Are views part of what xou call data? Are constraints? Both can use functions. Unless your objects are ill-defined, there is no problem restoring a dump. – Laurenz Albe Aug 18 '21 at 06:02
  • Tables and data. Make it simple to do just those two (with constraints). Everything else is secondary when you're just trying to move data. – quickdraw Aug 18 '21 at 06:52
  • @quickdraw I see. Still, I'd say that that is a minority viewpoint. – Laurenz Albe Aug 18 '21 at 07:44
  • Sorry @Laurenz. I keep trying to mention you but it's not working for me. I agree moving just data isn't a primary case, it is a principle case for development environments. I'm hot on this issue because I got burned by it just trying to copy a bunch of data out of our prod data lake into our dev lake where we just needed stuff to play with. When you're moving data into a db with other developers, you can't blow away their routines and other stuff. You have to be surgical. – quickdraw Aug 18 '21 at 08:23
  • @quickdraw If you want to do that, perhaps keep tables and functions in different schemas. – Laurenz Albe Aug 18 '21 at 08:27
  • Great idea @Laurenz. Thanks so much! – quickdraw Aug 19 '21 at 01:20