I did the following commands:

pg_dumpall --clean | gzip > db.gz
gunzip db.gz
psql < db

I expected the third command (loading the database back) to just run with zero errors, but I got some errors:

ERROR:  multiple primary keys for table "pga_exception" are not allowed
ERROR:  multiple primary keys for table "pga_job" are not allowed

There are more errors than those listed, but they are a combination of primary key violations, constraints already existing, and triggers already existing. Why would recreating a database from a backup sql dump produce errors?

  • 1
    Did you drop everything before re-importing? (I have to ask!) – Philᵀᴹ Jul 20 '16 at 19:31
  • I checked the script produced by the first command, and the first thing that it does that isn't something like setting the character set is to drop all of the databases. My understanding is that is what the --clean option does. – zelinka Jul 20 '16 at 19:42

pg_dumpall --clean will not issue DROP DATABASE statements for databases like template1 or postgres that have been created by initdb at cluster initialization time.

However it will dump the contents of these databases if you created any objects in it (by default they're empty). So when restoring the result of pg_dumpall --clean into the same instance, it's likely that such errors will occur if these databases happen to have contents.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.