reorg / pg_repack

Reorganize tables in PostgreSQL databases with minimal locks
BSD 3-Clause "New" or "Revised" License
1.89k stars 175 forks source link

Can't save records, ERROR: must be superuser to use repack_trigger function #146

Closed brauliobo closed 7 years ago

brauliobo commented 7 years ago

After enabling pg_repack on RDS, I had to change the owner of the functions according to https://dba.stackexchange.com/questions/182840/repacking-rds-database-with-pg-repack-isnt-online/182841 to fix the error Sequel::DatabaseError: PG::InsufficientPrivilege: ERROR: permission denied for relation log_705387 on inserts.

But then that created another INSERT error: ERROR: must be superuser to use repack_trigger function, so I rolling back the permissions to the superuser.

How to work then with multiple users and ensure they can INSERT data when pg_repack is enabled??

brauliobo commented 7 years ago

Just as a sidenote, I won't be using pg_repack anymore, let me share the reasons so that you can consider future improvementes. The lack of incremental repacking requires too much resources (CPU, disk and free space) from the database. This makes repacking big tables inviable in busy and live databases. Also the configuration seems not very simple on multiuser environments.

danbst commented 7 years ago

incremental repack is what autovacuum is about.

brauliobo commented 7 years ago

@danbst autovacuum doesn't reorder according to clustered indexes

danbst commented 7 years ago

have you considered partitioning on cluster key (in some cases it is possible)?

I don't know of anything in PG world that could do incremental CLUSTER.

brauliobo commented 7 years ago

never really understood how partitions works and how to use them... might be a good thing though, I'm afraid they require changing the applications

electric-al commented 6 years ago

For us, executing this as the rds_superuser role worked:

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;

See: http://engineering.ometria.com/2018/01/29/using-pg_repack-with-aws-postgres-rds/

changzee commented 7 months ago

@electric-al

This link of post (http://engineering.ometria.com/2018/01/29/using-pg_repack-with-aws-postgres-rds/) is gone away. Can you post again?