Differences

This shows you the differences between two versions of the page.

--- postgresqlcommands [2022/01/20 11:00] – [Useful SQL Commands] z0hpvk
+++ postgresqlcommands [2025/04/17 10:16] (current) – z0hpvk
@@ Line 1: / Line 1: @@
 ==== Installation ====
-=== Ubuntu ===
+=== Linux ===
-[[https://www.postgresql.org/download/linux/ubuntu/]]\\
+[[https://www.postgresql.org/download/linux/debian/]]\\
+[[https://www.postgresql.org/download/linux/redhat/]]\\
+=== Create Cluster ===
+The Debian or Ubuntu installation will create a default instance. \\
+Create new instances using the commands below ... \\
+<code bash>
+# Debian / Ubuntu
+pg_createcluster -d /var/lib/postgresql/17/main 17 main
+# Redhat / Fedora
+# Default Data Directory: /var/lib/pgsql/17/data
+sudo /usr/pgsql-17/bin/postgresql-17-setup initdb
+/usr/pgsql-17/bin/initdb -D /var/lib/pgsql/17/main
+</code>
+=== Instance Details ===
+<code>
+/usr/lib/postgresql/17/bin/pg_config
+/usr/lib/postgresql/17/bin/pg_controldata -D $PGDATA
+/usr/lib/postgresql/17/bin/pg_waldump $PGDATA/pg_wal/<WAL_FILE>
+</code>
 == Default File Locations ==
@@ Line 107: / Line 128: @@
 SELECT PG_SIZE_PRETTY (PG_TOTAL_RELATION_SIZE('WORKPIECE_HISTORY'));
 SELECT PG_SIZE_PRETTY (PG_DATABASE_SIZE('IC1'));
+-- Partitioned / Inherited Tables
+SELECT pi.inhparent::regclass AS parent_table_name,
+       pg_size_pretty(sum(pg_total_relation_size(psu.relid))) AS total,
+       pg_size_pretty(sum(pg_relation_size(psu.relid))) AS internal,
+       pg_size_pretty(sum(pg_table_size(psu.relid) - pg_relation_size(psu.relid))) AS external, -- toast
+       pg_size_pretty(sum(pg_indexes_size(psu.relid))) AS indexes
+  FROM pg_catalog.pg_statio_user_tables psu
+  JOIN pg_class pc ON psu.relname = pc.relname
+  JOIN pg_database pd ON pc.relowner = pd.datdba
+  JOIN pg_inherits pi ON pi.inhrelid = pc.oid
+ WHERE pd.datname = 'postgres'
+GROUP BY pi.inhparent
+ORDER BY sum(pg_total_relation_size(psu.relid)) DESC;
 SELECT RELNAME, PG_SIZE_PRETTY (PG_TABLE_SIZE(RELID)) AS TABLE_SIZE
@@ Line 157: / Line 192: @@
 -- Populate table with random Ids
-CREATE TABLE T1 (id integer);
+CREATE TABLE t1 (id integer);
-INSERT INTO T1 SELECT * FROM generate_series(1, 500) ORDER BY random();
+INSERT INTO t1 SELECT * FROM generate_series(1, 500) ORDER BY random();
+CREATE TABLE t2 (id integer, description text, updated timestamptz);
+INSERT INTO t2
+SELECT floor(random()*(100-1+1))+1 AS random_number,
+       substr(md5(random()::text), 1, 10) AS random_string,
+       now() - INTERVAL '1 year' + random() * (now() - now() - INTERVAL '1 year') AS random_timestamp
+  FROM generate_series(1,10);
 -- Reload postgresql.conf and pg_hba.conf Files