How to get PostgreSQL database size from Python?

Question

I am using psycopg2 to get a PostgreSQL database size in Python3.

dbname = 'my_pg_database'
q = 'select pg_total_relation_size(%s);'
conn.cursor.execute(q, (dbname, ))
row = conn.cursor.fetchone()
print(str(row[0]))

I have also tried with the same code but switching second line:

q = 'select pg_total_relation_size((%s));'

And changing second and third lines:

dbname = 'my_pg_database'
q = 'select pg_total_relation_size({dbname});'.format(dbname=dbname)
conn.cursor.execute(q)
row = conn.cursor.fetchone()
print(str(row[0]))

I always get the same syntax error, I had a lot this problem before with other queries, but I managed to deal with this using format or the other way, but not in this case. Anyone knows why?

EDIT

The errors I am getting are:

With the first code:

psycopg2.ProgrammingError: relation "my_pg_database" does not exist LINE 1: select pg_total_relation_size('my_pg_database');

By the way, here there is a kind of arrow pointing to the first quote of the second line.

With the third code:

psycopg2.ProgrammingError: column "my_pg_database" does not exist LINE 1: select pg_total_relation_size(my_pg_database);

And here there is a kind of arrow pointing to the m of my_pg_database of the second line.

Note that the first error is talking about relation and the second one about column.

print is a function in Python 3 - print(things, you, want, to print) not print things you want to print (note the parenthesis) — Sean Vieira
– Sean Vieira, Commented May 22, 2014 at 14:59
OK in fact I am not using print (logger instead), but the problem is in the query, sure. But I am going to change that here! — forvas
– forvas, Commented May 22, 2014 at 15:01
You're using the wrong function, you need pg_database_size() to get the size of your database. pg_total_relation_size() is for a single table and it's indexes. — Frank Heikens
– Frank Heikens, Commented May 22, 2014 at 15:17

Frank Heikens · Accepted Answer · 2014-05-22 15:20:23Z

1

You're using the wrong function, you need pg_database_size() to get the size of your database. pg_total_relation_size() is for a single table and it's indexes.

Check the manual for more details.

answered May 22, 2014 at 15:20

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get PostgreSQL database size from Python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related