PostgreSQL: Administration Commands Reference

Table of Contents

• Useful postgresql queries
  • Copy table as csv to client filesystem
  • Get table size
  • Get column size
  • Transactions
  • Add column
  • Delete
  • Update
  • Deadlocks
  • Table sizes
  • Upgrades
  • Vacuum

Useful postgresql queries

SELECT pg_reload_conf();

Copy table as csv to client filesystem

\copy table to 'mycsv.csv' DELIMITER ',' CSV HEADER;
\copy (SELECT column1, column2 FROM your_table_name WHERE condition) TO 'query_results.csv' DELIMITER ',' CSV HEADER;

Get table size

SELECT pg_size_pretty(pg_total_relation_size('your_table_name'));

Get column size

SELECT sum(pg_column_size(column)) FROM yourtable

Transactions

begin;
savepoint my_savepoint;

rollback to savepoint my_savepoint;
commit;
rollback;

Add column

alter table table_name add column column_name data_type [constraint];

Delete

begin;
DELETE FROM table_name where condition;
-- commit;

Update

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Deadlocks

SELECT deadlocks FROM pg_stat_database WHERE datname = current_database();

pg_stat_activity: Shows current connections and their states, including those waiting for locks. pg_locks: Provides details about locks held and requested, including the object being locked (table, row, etc.) and the process ID (PID) involved.

Table sizes

SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM
    pg_class
WHERE
    relkind = 'r' -- 'r' for regular table
ORDER BY
    pg_total_relation_size(oid) DESC
LIMIT 10;

Upgrades

mv /opt/pgdata/data_5432 /opt/pgdata/data_5432.pg16
ls -l /opt/pgdata/data_5432.pg16
/opt/pg-17/bin/initdb --no-locale --auth="ident" -D /opt/pgdata/data_5432 -X /opt/pgdata/dblog/pg_wal_5432
convert postgres config postgresql.conf
copy pg_hba.conf over
pg_upgrade --old-datadir --new-datadir --old-bindir -new-bindir --jobs=6 --check --verbose
pg_upgrade --old-datadir --new-datadir --old-bindir -new-bindir --jobs=6 --link --verbose
ldconfig

Vacuum

-- Aggressively clean dead tuples and reclaim space for current DB
VACUUM (FULL, ANALYZE, VERBOSE);

SELECT name, setting, unit FROM pg_settings WHERE name IN ( 'shared_buffers', 'temp_buffers', 'work_mem', 'maintenance_work_mem', 'wal_buffers', 'effective_cache_size' );

Gotcha — so 32 GB RAM box running both PostgreSQL and the web app.
Your current shared_buffers = 98304 (768 MB) is very conservative for that machine.
Typical starting point for mixed-use servers:
Total RAM: 32 GB
Leave for OS + file cache + web stack: ~16–20 GB
Give PostgreSQL: ~12–16 GB total headroom
Set shared_buffers to about 3–6 GB
Examples (PostgreSQL uses 8k pages):
3 GB → shared_buffers = 393216 (3 * 1024^3 / 8192)
4 GB → shared_buffers = 524288
6 GB → shared_buffers = 786432
I’d start with 4 GB and monitor:
shared_buffers = 4GB
effective_cache_size = 16GB
work_mem = 32MB
maintenance_work_mem = 1GB

ALTER SYSTEM SET shared_buffers         = '4GB';
ALTER SYSTEM SET temp_buffers           = '64MB';
ALTER SYSTEM SET work_mem               = '32MB';
ALTER SYSTEM SET maintenance_work_mem   = '1GB';
ALTER SYSTEM SET wal_buffers            = '16MB';
ALTER SYSTEM SET effective_cache_size   = '16GB';

SELECT pg_reload_conf();