Database Operations

This section covers common maintenance tasks for managing PostgreSQL databases, such as fixing sequence issues and monitoring system resources.

Reset Identity Column

Identity columns and serial types automatically generate unique numbers for new rows. These sequences can get out of sync if you manually import data with existing IDs or delete many rows. You can fix this using one of the two methods below.

Option 1: Sync with Existing Data

Use this method if you have existing records and want the next ID to be one higher than the current maximum value. This command automatically finds the highest ID and updates the sequence to match it.

-- Replace 'table_name' and 'col_name' with your actual names
SELECT setval(pg_get_serial_sequence('table_name', 'col_name'), MAX(col_name))
FROM table_name;

Option 2: Restart from a Specific Number

Use this method if you have emptied a table and want the IDs to start over from 1 or any other specific value. This command directly resets the underlying sequence object.

-- Replace 'table_name_col_name_seq' with your sequence name
ALTER SEQUENCE table_name_col_name_seq RESTART WITH 1;

Check Database Size

If you need to monitor disk usage, you can use this command to see the total size of your database in a readable format like MB or GB:

SELECT pg_size_pretty(pg_database_size('your_database_name'));

View Table Sizes

To find out which specific tables are taking up the most space in your database, you can list them by size using the following query:

SELECT relname AS "Table"
    , pg_size_pretty(pg_total_relation_size(relid)) AS "Size"
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Terminate Active Connections

If you are trying to drop or modify a database and get an error that it is currently being accessed by other users, you can run this command to force those connections to close:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'your_database_name'
  AND pid <> pg_backend_pid();