PostgreSQL – How to Retrieve the Size of A Database

ByAdmin

To get the size of a PostgreSQL database, you can use a few different methods, including SQL queries and command-line utilities. Here are a couple of approaches:

Using SQL Query

You can use the following SQL query to get the size of a specific database in PostgreSQL:

SELECT pg_size_pretty(pg_database_size('your_database_name')) AS database_size;

where your_database_name is the name of the database for which you want to retrieve the size. This query uses the pg_database_size function to get the size in bytes and then formats it using pg_size_pretty to make it more human-readable.

Using Command-Line Utility

You can also use the pg_size utility from the command line to get the size of a PostgreSQL database. Open a terminal and run the following command:

pg_size -d your_database_name

where your_database_name is the name of the database for which you want to retrieve the size. The pg_size utility will provide information about the database’s size, including its total size on disk.

Similar Posts

PostgreSQL – How to Change Role within a Database Session

ByAdmin

You can change the current role within a database session in PostgreSQL database using SET ROLE statement . Changing the role allows you to temporarily assume the permissions and privileges associated with a different role, which can be useful for specific tasks or actions that require different access rights. This can be particularly helpful in…

PostgreSQL – How to Disable(Lock) a User

ByAdmin

You can disable a user account in PostgreSQL database by revoking their login privileges. Disabling a user prevents them from connecting to the PostgreSQL database server. Here’s the basic syntax to revoke login privileges from a user globally: ALTER USER username NOLOGIN; Where username is the name of the user you want to lock/disable. By…

PostgreSQL – How to Revoke Superuser Privileges

ByAdmin

To revoke superuser privileges from a user in PostgreSQL, you’ll need to connect to the PostgreSQL database server as a superuser, typically the “postgres” user. Here are the steps to revoke superuser privileges: Log in as the PostgreSQL superuser: Open a terminal and log in as the PostgreSQL superuser, usually named “postgres.” You can do…

PostgreSQL – How to Create A Sequence

ByAdmin

You can create a sequence using the CREATE SEQUENCE command in PostgreSQL database. A sequence is typically used to generate unique integer values, often for primary keys or other situations where you need a unique identifier. Here’s the basic syntax to create a sequence: CREATE SEQUENCE sequence_name [ INCREMENT increment ] [ MINVALUE min_value ]…

PostgreSQL – How to Delete Duplicate Rows in PostgreSQL Database

ByAdmin

To delete duplicate rows in PostgreSQL using a self-join, you can use the DELETE statement along with a self-join condition. Here’s the syntax: DELETE FROM your_table a USING your_table b WHERE a.column_name = b.column_name AND a.ctid < b.ctid; where your_table is the actual name of your table, and column_name is the column that you want…

PostgreSQL – How to Get DDL of a Materialized View

ByAdmin

To retrieve the Data Definition Language (DDL) for a materialized view in PostgreSQL, you can use the pg_get_viewdef function, which is a built-in function for getting the definition (DDL) of various database objects, including materialized views. Here’s the syntax to retrieve the DDL for a materialized view: SELECT pg_get_viewdef(‘your_materialized_view_name’::regclass, true); where your_materialized_view_name is the name…