We recently started upgrading some of our databases on windows 64-bit to 9.2, in doing so we ran into a nasty issue we discovered when some of our backups were failing. The first time it happened, I chucked it up to a dirty PostgreSQL 8.4 database being restored to PostgreSQL 9.2.1. The second time it happened restoring a 9.1 database to 9.2.2, I thought, better look into this to see if there is a known issue. Low and behold I found this: http://archives.postgresql.org/pgsql-bugs/2012-12/msg00091.php (Bug #7741). Apparently something to do with granting rights on Types.
As a workaround for this problem so our backups would work again was to delete the offending permissions from system tables. It's probably not the best way but only way we could think of, we delete the bad record in pg_default_acl and after that backup works without complaint.
-- there is no such thing as T for default priviledges that pg_dump understands based on -- https://github.com/adunstan/postgresql-dev/blob/master/src/bin/pg_dump/pg_dump.c#L11838 -- backup bad records just in case we need them again -- SELECT * into zz_bad_pg_default_acl FROM pg_default_acl WHERE defaclobjtype = 'T'; -- delete unknown records -- DELETE from pg_default_acl WHERE defaclobjtype = 'T';
If anyone else has further input on this, I'd be interested.