Below are two ways to return the size of a specific database in PostgreSQL.

The PG_DATABASE_SIZE() Function

The PG_DATABASE_SIZE() function computes the total disk space used by the database with the specified name or OID.

Example:

SELECT PG_SIZE_PRETTY(PG_DATABASE_SIZE('pethotel'));

Result:

8169 kB

Here, I returned the size of the pethotel database.

In this case I also used the PG_SIZE_PRETTY() function to return the result in a more easily human-readable format with size units (bytes, kB, MB, GB or TB as appropriate).

Here’s what we get without that function:

SELECT PG_DATABASE_SIZE('pethotel');

Result:

8364911

To use the PG_DATABASE_SIZE() function, you must have CONNECT privilege on the specified database (which is granted by default) or be a member of the pg_read_all_stats role.

The \l+ Command

If you’re using psql, you can run the \l+ command.

Example:

\l+ pagila

Result:

                                                 List of databases
+--------+----------+----------+-------------+-------------+-------------------+-------+------------+-------------+
|  Name  |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges | Size  | Tablespace | Description |
+--------+----------+----------+-------------+-------------+-------------------+-------+------------+-------------+
| pagila | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 16 MB | pg_default |             |
+--------+----------+----------+-------------+-------------+-------------------+-------+------------+-------------+

This time I checked the pagila database.

This can also be run using \list+ (\l+ is short for \list+).

The command can also be run without the plus symbol (+), however, the + is what returns extended info, such as the size (which is what we’re interested in here).

The command can also be run without specifying the database. In this case, information on all databases will be returned.

Note that size information is only available for databases that the current user can connect to.