Skip to main content

PostgreSql

Basic operation

  • use the following command to log in as postgres user
sudo -i -u postgres
  • query active sessions
SELECT * FROM pg_stat_activity where datname = 'database_name';
  • create a new database
CREATE DATABASE database_name;
  • delete a database
DROP DATABASE database_name;
  • connect to the database
psql -h localhost -U postgres
  • list all databases
\list
  • list all tables
\dt
  • use a database
\c database_name

# or see current database
\c
  • insight the memory usage
SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM
    pg_catalog.pg_statio_user_tables
ORDER BY
    pg_total_relation_size(relid) DESC;