HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Repacking RDS database with pg_repack isn't online

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
withrepackingonlinedatabasepg_repackrdsisn

Problem

While running 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.