Restrict database size

Question

I'm surpised that I did not find this Q on SO. However, googling for it brings up very old pages. Likely I did not find the correct keywords. Posting this Q to Database Admins left it unanswered.

To the Q itself:

I'm pretty sure that I've read about a feature in postgres to limit the database size. But I cannot find it in the manuals today.

Did I keep it wrongly in mind? How can I limit the size of the database?

Answer 1

There is no built-in quota system in Postgres, so I guess you misheard this.

However, you could do it a couple of ways:

1. Create tablespaces for each user. If you have quotas on the filesystem, you can arrange for them to match. You are of course risking the integrity of the database though. I wouldn't try it.

2. Write a script that disables INSERT on an account. You can query the disk utilization on the database using pg_database_size. I would write a shell script to check and revoke/grant access and run it periodically with cron.

E.g.:

evlaopt=# select pg_database_size('evlaopt');
 pg_database_size
------------------
       9240136352
(1 row)

evlaopt=# select pg_size_pretty(pg_database_size('evlaopt'));
 pg_size_pretty
----------------
 8812 MB
(1 row)

(evlaopt is a database on my machine.)

Suppose you have users and databases whose names match, and they all have the same quota of (say) 100 MB. You could do something like this:

# grant access to databases below the limit
for DB in $(psql -At -c 'SELECT datname FROM pg_database WHERE NOT datistemplate AND pg_database_size(datname) < 1024 * 1024 * 100'); do
    psql -At -c "GRANT INSERT ON ALL TABLES IN SCHEMA public TO $DB" $DB
done

# revoke access to databases at or above the limit
for DB in $(psql -At -c 'SELECT datname FROM pg_database WHERE NOT datistemplate AND pg_database_size(datname) >= 1024 * 1024 * 100'); do
    psql -At -c "REVOKE INSERT ON ALL TABLES IN SCHEMA public FROM $DB" $DB
done