Common SQL

Connect

psql

psql -U xxx -d postgres

psql -U xxx -d postgres -p

List Databases

\l

List Tables

\dt

Select Database

\c xxx

Create Database

CREATE DATABASE foo
  WITH
  OWNER = kuga
  ENCODING = 'UTF8'
  LC_COLLATE = 'C'
  LC_CTYPE = 'C'
  TEMPLATE = template0;

CREATE DATABASE foo
  WITH
  OWNER = kuga
  ENCODING = 'UTF8'
  LC_COLLATE = 'en_US.UTF-8'
  LC_CTYPE = 'en_US.UTF-8'
  TEMPLATE = template0;

Create User

CREATE ROLE kuga WITH LOGIN PASSWORD 'your_password';

Set Password

ALTER ROLE kuga WITH PASSWORD 'new_password';

pg_hba.conf

/opt/homebrew/var/postgresql@15/pg_hba.conf

Get Process Start Time

SELECT pg_postmaster_start_time();

Show Config File

SHOW config_file;

Show Max Connections

SHOW max_connections;

Get Total Activity Connections

SELECT COUNT(*) AS total_connections FROM pg_stat_activity;

Activity Connections Stats

SELECT
  usename,
  datname,
  client_addr,
  application_name,
  state,
  COUNT(*) AS connections
FROM pg_stat_activity
GROUP BY 1, 2, 3, 4, 5
ORDER BY connections DESC;

Kill IDLE Connections

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'xxx'
  AND state = 'idle'
  AND pid <> pg_backend_pid();  -- 排除当前连接