Differences

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

--- postgresqlcommands [2022/06/07 08:50] – [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 installation above will create a default cluster for you. \\
+The Debian or Ubuntu installation will create a default instance. \\
-If you wish to create a new one run the following command as postgres ... \\
+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>
-pg_createcluster -d /var/lib/postgresql/14/main 14 main
+/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>
@@ Line 115: / Line 129: @@
 SELECT PG_SIZE_PRETTY (PG_DATABASE_SIZE('IC1'));
--- Partitioned Tables
+-- Partitioned / Inherited Tables
-SELECT
+SELECT pi.inhparent::regclass AS parent_table_name,
-   pi.inhparent::regclass AS parent_table_name,
+       pg_size_pretty(sum(pg_total_relation_size(psu.relid))) AS total,
-   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_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_table_size(psu.relid) - pg_relation_size(psu.relid))) AS external, -- toast
+       pg_size_pretty(sum(pg_indexes_size(psu.relid))) AS indexes
-   pg_size_pretty(sum(pg_indexes_size(psu.relid))) AS indexes
+  FROM pg_catalog.pg_statio_user_tables psu
-FROM pg_catalog.pg_statio_user_tables psu
+  JOIN pg_class pc ON psu.relname = pc.relname
-   JOIN pg_class pc ON psu.relname = pc.relname
+  JOIN pg_database pd ON pc.relowner = pd.datdba
-   JOIN pg_database pd ON pc.relowner = pd.datdba
+  JOIN pg_inherits pi ON pi.inhrelid = pc.oid
-   JOIN pg_inherits pi ON pi.inhrelid = pc.oid
+ WHERE pd.datname = 'postgres'
-WHERE pd.datname = 'polaris-production'
 GROUP BY pi.inhparent
 ORDER BY sum(pg_total_relation_size(psu.relid)) DESC;
@@ Line 179: / 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