patternsqlMinor
Repacking RDS database with pg_repack isn't online
Viewed 0 times
withrepackingonlinedatabasepg_repackrdsisn
Problem
While running
But
pg_repack on a RDS Postgresql 9.6.3, got this failure for one of writers:ProgrammingError: permission denied for relation log_24034858
CONTEXT: SQL statement "INSERT INTO repack.log_24034858(pk, row)
VALUES(CASE WHEN $1 IS NULL THEN NULL ELSE (ROW($1.id)::repack.pk_24034858) END, $2)"But
pg_repack should allow writes to repacked tables, why did it fail here?Solution
We found a similar issue. The issue appears to be that log tables created in the repack schema don't have the correct permissions. Our solution was to use ALTER DEFAULT PRIVALEGES to fix this:
CREATE EXTENSION pg_repack;
ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT INSERT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;Code Snippets
CREATE EXTENSION pg_repack;
ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT INSERT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;Context
StackExchange Database Administrators Q#182840, answer score: 7
Revisions (0)
No revisions yet.