Re: Unexplained disk usage in AWS Aurora Postgres
Thank you for your insight Seenu! That is a good point, unfortunately we do not have access to the server/file system as the database is a managed service. Access to the file system from postgres like pg_ls_dir is also blocked. Are you aware of another, creative way to infer the wal file size from within postgres? Best Regards Chris On Tue, Aug 4, 2020 at 11:39 AM Srinivasa T N wrote: > There may be lot of wal files or the size of log files in pg_log might be > huge. "du -sh *" of data directory holding the database might help. > > Regards, > Seenu. > > > On Tue, Aug 4, 2020 at 2:09 PM Chris Borckholder < > chris.borckhol...@bitpanda.com> wrote: > >> Hi! >> >> We are experiencing a strange situation with an AWS Aurora postgres >> instance. >> The database steadily grows in size, which is expected and normal. >> After enabling logical replication, the disk usage reported by AWS >> metrics increases much faster then the database size (as seen by \l+ in >> psql). The current state is that database size is ~290GB, while AWS reports >> >640GB disk usage. >> We reached out to AWS support of course, which is ultimately responsible. >> Unfortunately they were not able to diagnose this until now. >> >> I checked with the queries from wiki >> https://wiki.postgresql.org/wiki/Disk_Usage , which essentially give the >> same result. >> I tried to check on wal segment file size, but we have no permission to >> execute select pg_ls_waldir(). >> The replication slot is active and it also progresses >> (pg_replication_slots.confirmed_flush_lsn increases and is close to >> pg_current_wal_flush_lsn). >> >> Can you imagine other things that I could check from within postgres with >> limited permissions to diagnose this? >> >> Best Regards >> Chris >> >> >>
Re: Unexplained disk usage in AWS Aurora Postgres
Thanks for your insight! I cannot find any errors related to archiving in the logs that are accessible to me. It's definitely something that I will forward to the support team of the managed database. Best Regards Chris On Thu, Aug 6, 2020 at 3:18 AM Mohamed Wael Khobalatte < mkhobala...@grubhub.com> wrote: > > > On Tue, Aug 4, 2020 at 4:39 AM Chris Borckholder < > chris.borckhol...@bitpanda.com> wrote: > >> Hi! >> >> We are experiencing a strange situation with an AWS Aurora postgres >> instance. >> The database steadily grows in size, which is expected and normal. >> After enabling logical replication, the disk usage reported by AWS >> metrics increases much faster then the database size (as seen by \l+ in >> psql). The current state is that database size is ~290GB, while AWS reports >> >640GB disk usage. >> We reached out to AWS support of course, which is ultimately responsible. >> Unfortunately they were not able to diagnose this until now. >> >> I checked with the queries from wiki >> https://wiki.postgresql.org/wiki/Disk_Usage , which essentially give the >> same result. >> I tried to check on wal segment file size, but we have no permission to >> execute select pg_ls_waldir(). >> The replication slot is active and it also progresses >> (pg_replication_slots.confirmed_flush_lsn increases and is close to >> pg_current_wal_flush_lsn). >> >> Can you imagine other things that I could check from within postgres with >> limited permissions to diagnose this? >> >> Best Regards >> Chris >> >> > If you do archive wal files, maybe the archive_command is failing? >
Re: Unexplained disk usage in AWS Aurora Postgres
I would highly suggest you reach out to AWS support for Aurora questions, that's part of what you're paying for, support. For reasons you mentioned and more, it's pretty hard to debug issues because it isn't actually Postgres. >
Re: Unexplained disk usage in AWS Aurora Postgres
## Chris Borckholder (chris.borckhol...@bitpanda.com): > We are experiencing a strange situation with an AWS Aurora postgres > instance. The main problem here is that "Amazon Aurora" is not PostgreSQL. If I understand Amazon's documentation, what you are using is officially named "Amazon Aurora with PostgreSQL Compatibility", and that sums is up quite nicely: Aurora is a database engine developed at Amazon - and it's inner workings are not publically documented. Whatever is using up that disk space - only AWS Support can know. Regards, Christoph -- Spare Space
Re: Unexplained disk usage in AWS Aurora Postgres
Thank you Adam and Christoph, You are totally right, that AWS support is the one to help me with this problem. I am in contact with them for quite some time on this problem and as there was no progress on resolving this, I tried to find some insight or trick that I missed here. It's a long shot (: Best Regards Chris On Fri, Aug 7, 2020 at 4:22 PM Christoph Moench-Tegeder wrote: > ## Chris Borckholder (chris.borckhol...@bitpanda.com): > > > We are experiencing a strange situation with an AWS Aurora postgres > > instance. > > The main problem here is that "Amazon Aurora" is not PostgreSQL. > If I understand Amazon's documentation, what you are using is > officially named "Amazon Aurora with PostgreSQL Compatibility", > and that sums is up quite nicely: Aurora is a database engine > developed at Amazon - and it's inner workings are not publically > documented. > Whatever is using up that disk space - only AWS Support can know. > > Regards, > Christoph > > -- > Spare Space >
Re: Unexplained disk usage in AWS Aurora Postgres
> > The main problem here is that "Amazon Aurora" is not PostgreSQL. > If I understand Amazon's documentation, what you are using is > officially named "Amazon Aurora with PostgreSQL Compatibility", > and that sums is up quite nicely: Aurora is a database engine > developed at Amazon - and it's inner workings are not publically > documented. > Whatever is using up that disk space - only AWS Support can know. Correct. Aurora is basically forked PG code, but with a different I/O layer. That explains why they are quite behind community PG in versions.
Re: privileges oddity
Further update: create a new user, grant all on schema & the table, works reboot of server did not change anything, so the problem is in persistent state
Re: privileges oddity
On 8/7/20 9:23 AM, Scott Ribe wrote:
Further update:
create a new user, grant all on schema & the table, works
reboot of server did not change anything, so the problem is in persistent state
What happens if you do?:
select has_schema_privilege('akanzler', 'zoewang', 'usage');
In psql what does
\ddp
show?
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: privileges oddity
>
> What happens if you do?:
>
> select has_schema_privilege('akanzler', 'zoewang', 'usage');
risk_oltp_prod=# select has_schema_privilege('akanzler', 'zoewang', 'usage');
has_schema_privilege
--
t
(1 row)
> In psql what does
>
> \ddp
>
> show?
risk_oltp_prod=# \ddp
Default access privileges
Owner | Schema | Type | Access privileges
+--+---+
...
srv_risk | zoewang | table | akanzler=r/srv_risk +
| | | srv_risk=arwdD/srv_risk
...
(40 rows)
Wondering if there's a code path somewhere that lets the default take
precedence???
Re: privileges oddity
> On Aug 7, 2020, at 11:31 AM, Scott Ribe wrote: > > Wondering if there's a code path somewhere that lets the default take > precedence??? So, I changed the defaults, now I see akanzler=arwdDxt/srv_risk, problem persists
Re: privileges oddity
On 8/7/20 10:39 AM, Scott Ribe wrote: On Aug 7, 2020, at 11:31 AM, Scott Ribe wrote: Wondering if there's a code path somewhere that lets the default take precedence??? So, I changed the defaults, now I see akanzler=arwdDxt/srv_risk, problem persists Well if this for the same line as before it represents table privileges. The problem is with schema access. Continuing grasping at straws: select * from pg_roles where rolname = 'aakanzler'; -- Adrian Klaver adrian.kla...@aklaver.com
Re: privileges oddity
> On Aug 7, 2020, at 12:17 PM, Adrian Klaver wrote:
>
> Well if this for the same line as before it represents table privileges. The
> problem is with schema access. Continuing grasping at straws:
>
> select * from pg_roles where rolname = 'aakanzler';
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin |
rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls |
rolconfig | oid
--+--++---+-+-++--+-+---+--+---+---
akanzler | f| t | f | f | t |
f | -1 | | | f|
{role=confidential_read_only} | 16391
Re: privileges oddity
On 8/7/20 11:25 AM, Scott Ribe wrote:
On Aug 7, 2020, at 12:17 PM, Adrian Klaver wrote:
Well if this for the same line as before it represents table privileges. The
problem is with schema access. Continuing grasping at straws:
select * from pg_roles where rolname = 'aakanzler';
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin |
rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls |
rolconfig | oid
--+--++---+-+-++--+-+---+--+---+---
akanzler | f| t | f | f | t |
f | -1 | | | f|
{role=confidential_read_only} | 16391
So what privileges does role 'confidential_read_only' have?
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: privileges oddity
On Aug 7, 2020, at 12:27 PM, Adrian Klaver wrote: > > So what privileges does role 'confidential_read_only' have? read on everything I tried creating a new user without it, just doing the same grants otherwise as for akanzler, that worked. Then I added that user to confidential_read_only, still worked.
Re: privileges oddity
Scott Ribe writes: > On Aug 7, 2020, at 12:27 PM, Adrian Klaver wrote: >> So what privileges does role 'confidential_read_only' have? > read on everything ... including usage on the schema in question? If I'm reading this correctly, you have set things up so that any session logging in as akanzler will immediately do "SET ROLE confidential_read_only", after which it's the privileges of that role not akanzler that determine what happens. regards, tom lane
Re: privileges oddity
> On Aug 7, 2020, at 12:45 PM, Tom Lane wrote: > > If I'm reading this correctly, you have set things up so that any > session logging in as akanzler will immediately do "SET ROLE > confidential_read_only", after which it's the privileges of that > role not akanzler that determine what happens. YES, confidential_read_only has privs on everything *except* individual user's schemas, and rolinherit was accidentally set, that would certainly seem to be the problem. But I turned that off, and it still doesn't work--even in a new connection.
Re: privileges oddity
On 8/7/20 11:56 AM, Scott Ribe wrote: On Aug 7, 2020, at 12:45 PM, Tom Lane wrote: If I'm reading this correctly, you have set things up so that any session logging in as akanzler will immediately do "SET ROLE confidential_read_only", after which it's the privileges of that role not akanzler that determine what happens. YES, confidential_read_only has privs on everything *except* individual user's schemas, and rolinherit was accidentally set, that would certainly seem to be the problem. But I turned that off, and it still doesn't work--even in a new connection. https://www.postgresql.org/docs/12/sql-set-role.html "Using this command, it is possible to either add privileges or restrict one's privileges. If the session user role has the INHERIT attribute, then it automatically has all the privileges of every role that it could SET ROLE to; in this case SET ROLE effectively drops all the privileges assigned directly to the session user and to the other roles it is a member of, leaving only the privileges available to the named role. On the other hand, if the session user role has the NOINHERIT attribute, SET ROLE drops the privileges assigned directly to the session user and instead acquires the privileges available to the named role. " -- Adrian Klaver adrian.kla...@aklaver.com
Re: privileges oddity
> On Aug 7, 2020, at 1:08 PM, Adrian Klaver wrote: > > "Using this command, it is possible to either add privileges or restrict > one's privileges. If the session user role has the INHERIT attribute, then it > automatically has all the privileges of every role that it could SET ROLE to; > in this case SET ROLE effectively drops all the privileges assigned directly > to the session user and to the other roles it is a member of, leaving only > the privileges available to the named role. On the other hand, if the session > user role has the NOINHERIT attribute, SET ROLE drops the privileges assigned > directly to the session user and instead acquires the privileges available to > the named role. > " So it would only have removed privs if I had used set role in the session, which I am not.
Re: privileges oddity
Scott Ribe writes: > So it would only have removed privs if I had used set role in the session, > which I am not. Yes, you are. It looks like what you actually issued is ALTER USER akanzler SET role confidential_read_only; but that would have the effect that subsequent session starts would automatically do "SET ROLE confidential_read_only". regards, tom lane
Re: privileges oddity
On 8/7/20 12:27 PM, Scott Ribe wrote: On Aug 7, 2020, at 1:08 PM, Adrian Klaver wrote: "Using this command, it is possible to either add privileges or restrict one's privileges. If the session user role has the INHERIT attribute, then it automatically has all the privileges of every role that it could SET ROLE to; in this case SET ROLE effectively drops all the privileges assigned directly to the session user and to the other roles it is a member of, leaving only the privileges available to the named role. On the other hand, if the session user role has the NOINHERIT attribute, SET ROLE drops the privileges assigned directly to the session user and instead acquires the privileges available to the named role. " So it would only have removed privs if I had used set role in the session, which I am not. See Tom's answer. To confirm do: SELECT s.setdatabase, s.setrole, rolname, s.setconfig, rolname FROM pg_db_role_setting AS s JOIN pg_roles AS r ON r.oid = s.setrole; -- Adrian Klaver adrian.kla...@aklaver.com
Re: privileges oddity
On 8/7/20 12:40 PM, Adrian Klaver wrote: On 8/7/20 12:27 PM, Scott Ribe wrote: On Aug 7, 2020, at 1:08 PM, Adrian Klaver wrote: "Using this command, it is possible to either add privileges or restrict one's privileges. If the session user role has the INHERIT attribute, then it automatically has all the privileges of every role that it could SET ROLE to; in this case SET ROLE effectively drops all the privileges assigned directly to the session user and to the other roles it is a member of, leaving only the privileges available to the named role. On the other hand, if the session user role has the NOINHERIT attribute, SET ROLE drops the privileges assigned directly to the session user and instead acquires the privileges available to the named role. " So it would only have removed privs if I had used set role in the session, which I am not. See Tom's answer. To confirm do: SELECT s.setdatabase, s.setrole, rolname, s.setconfig, rolname^^^ Surplus to requirements FROM pg_db_role_setting AS s JOIN pg_roles AS r ON r.oid = s.setrole; Also log in as 'akanzler' to psql and do: select session_user; select current_user; -- Adrian Klaver adrian.kla...@aklaver.com
Re: privileges oddity
> On Aug 7, 2020, at 1:32 PM, Tom Lane wrote: > > Yes, you are. It looks like what you actually issued is > > ALTER USER akanzler SET role confidential_read_only; > > but that would have the effect that subsequent session starts would > automatically do "SET ROLE confidential_read_only". AHA! This is the correct answer, and it is solved now. I *know* I executed grant role properly--it's right there in the script. However, I think that SET ROLE had been accidentally misused instead of GRANT at some point in the past, *AND* that the role contained privs to the user-specific schemas when it should not have. So revoking all privs from the role and adding back the proper ones resulted in inadvertently removing privs from users who'd properly had them explicitly granted. (I've never even used SET ROLE and was unaware you could even do that!) Anyway, thanks a million for being patient and sticking with this.
Re: privileges oddity
So, one last follow-up, perhaps \du or \du+ should show when a role is mapped that way. If I'd seen a clue to this setting that had been made "before I got here" it would have been figured out sooner. I realize ALTER ROLE... SET... can be used to set many more defaults, and there could be some debate about how much to display with \du[+], but the fact that a role abandons all its privs and adopts a different set seems like pretty important info to surface ;-)
Re: privileges oddity
Scott Ribe writes: > So, one last follow-up, perhaps \du or \du+ should show when a role is mapped > that way. If I'd seen a clue to this setting that had been made "before I got > here" it would have been figured out sooner. \drds does already show this; of course, you have to know to look at it, but the same could be said of \du ... > I realize ALTER ROLE... SET... can be used to set many more defaults, and > there could be some debate about how much to display with \du[+], but the > fact that a role abandons all its privs and adopts a different set seems like > pretty important info to surface ;-) IIRC, you aren't the first to get burnt this way. I've wondered for some time if we shouldn't forbid certain GUCs from being set via ALTER ROLE or ALTER DATABASE. "role" and "session authorization" are the poster children here but there might be others. On the other hand, if we do so somebody will likely complain that they have a legit use-case for it. regards, tom lane
