search

grayhemp / pgtoolkit

Tools for PostgreSQL maintenance
Other
184 stars 16 forks source link

Support AWS RDS #14

Open hlascelles opened 8 years ago

hlascelles commented 8 years ago

We are looking to use this tool for an AWS DB, but the highest privilege RDS user anyone has cannot perform certain commands / access certain tables (especially pgtoast):

foo_db, public.settings, pg_toast.pg_toast_17512 ERROR A database error occurred, exiting:
DatabaseError Can not executie command: 
 SELECT pg_try_advisory_lock(
    'pg_catalog.pg_class'::regclass::integer,
    'pg_toast.pg_toast_17512'::regclass::integer)::integer;

 ERROR:  permission denied for schema pg_toast
LINE 3:  'pg_toast.pg_toast_17512'::regclass::integer)::integer;

We initially explored removing the "stats" checks - we know which one table is an issue, and we don't need pgcompact to give us running updates of progress. However it seems pretty bound up in the process.

Can we run the core functionality of this tool on a table given an rds root user?

EDIT: pg_statistic for example cannot be queried.

tlbprim commented 7 years ago

Another AWS RDS user here. I'd like to add that we also lack permissions to set lc_messages:

Fri Oct 28 09:07:33 2016 ERROR A database error occurred, exiting:
DatabaseChooserError Can not find an adapter amongst supported: 
DatabaseError No driver found "Pg".
DatabaseError No driver found "PgPP".
DatabaseError Can not executie command: 
 SET lc_messages TO 'C'; SET session_replication_role TO replica; SET statement_timeout TO '0'; SET synchronous_commit TO off; SELECT 1;
 ERROR:  permission denied to set parameter "lc_messages"
adamgotterer commented 7 years ago

Did either of you ever figure out how to run this on RDS?

tlbprim commented 7 years ago

Anything older than a month is ancient history. I'll leave the mail in my inbox as a reminder to retry now that I'm on PG 9.5 (was on 9.3 at the time I originally posted). No promises.

adamgotterer commented 7 years ago

I tried on 9.6.1. I commented out any lines referring setting lc_messge which took me to the permission error on pg_toast. From what I can tell amazon just doesn't let you work with those tables. Wasn't sure if there was a work around or any other solution.