User Tools

Site Tools


postgresql

This is an old revision of the document!


Postgresql

List tables

psql -U postgres -tA -c 'SELECT datname FROM pg_database' | sort

Get statistics

db=... ; pghost=localhost
psql -U postgres -h $pghost -t -A -c "SELECT datname, tup_inserted, tup_updated, tup_deleted FROM pg_stat_database WHERE datname='$db';"

Show sizes

psql -U postgres -t -c 'SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database' | sort

or

psql -U postgres -t -c "SELECT datname, format('%8s MB.', pg_database_size(datname)/1000000) FROM pg_database" | sort

Show data directory

psql -U postgres -tA -c "SHOW data_directory;"

Show all paths

psql -U postgres -c "SELECT name, setting FROM pg_settings WHERE setting LIKE '/%';"

or

psql -U postgres -c "SELECT name, setting FROM pg_settings WHERE name LIKE '%dir%' OR name LIKE '%file%';"
/docs/dokuwiki/data/attic/postgresql.1567161075.txt.gz · Last modified: 2019-08-30 12:31:15 by mi