Recently I ran into an error when restoring a Postgres database dump which was hard to pin down. The cause was a version mismatch between the pg_dump command and pg_restore command.

I hope this small post comes in handy for someone, hopefully providing a quick fix for those facing a similar issue.

The problem

An automated process which had been running smoothly for a long time stopped working all of the sudden. I got the slightly cryptic message:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public your_database
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "public" already exists

The error is produced by the following command:

pg_restore -d your_database dumped_db.dump

Right before this command the previous database is removed (if existing) and a new clean database is created. So there is no real reason pg_restore should run into conflict with existing stuff. Yet the error clearly indicates an existing schema.

Fix

I'll jump straight to the conclusion. In our case it turned out a version mismatch between the pg_dump and pg_restore was the cause. Specifically pg_dump was version 10.7 while pg_restore was version 11.2.

Updating the dump script to version 11 solved the issue.

The solution is extremely simple, but easily overlooked. If this doesn't work for you, check out: