Skip to main content

PostgreSQL

Equipment, tags, users, workflows — data that is relational and not voluminous lives here. All queries requiring joins, constraints, or transactions are handled here.

ItemValue
Port5432
Primary databasepp
Other databasestemporal · temporal_visibility · kestra · hive-metastore-230
Accountplantpulse
Authenticationscram-sha-256

Connecting​

docker exec -it plantpulse-datalake pd node psql

To execute a single line:

docker exec plantpulse-datalake pd node psql -c "SELECT 1;"

If you'd rather use a browser, use the Data Gateway Console — it enforces permissions and auditing.

Examples​

What tables exist currently?​

\dt                                    -- psql 메타명령
-- 또는 표준 SQL 로
SELECT table_name FROM information_schema.tables
 WHERE table_schema = 'public' ORDER BY 1;

Equipment and their tag counts​

SELECT e.equipment_id, e.equipment_name, count(t.tag_id) AS tags
FROM tm_equipment e
LEFT JOIN tm_tag t ON t.equipment_id = e.equipment_id
GROUP BY e.equipment_id, e.equipment_name
ORDER BY tags DESC
LIMIT 20;

Sizes per table — where is data growing?​

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
LIMIT 15;

Slow queries currently running​

SELECT pid, now() - query_start AS elapsed, state, left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND now() - query_start > interval '5 seconds'
ORDER BY elapsed DESC;

Is replication keeping up?​

SELECT client_addr, state, sent_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

Things to know​

  • 🔴 Do not look for time series here. Tag values reside in Aster TSDB → CQL.
  • Role passwords are reconciled with the sidecar value on every boot (ALTER ROLE, idempotent). To change an account password, see Changing Passwords · API Keys.
  • hive-metastore-230 is the metastore used by Spark · Hive. Do not modify it manually.

See Also​