Differences

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

--- postgresql [2019-08-25 18:33:19]
mi [Show sizes]
+++ postgresql [2022-11-15 10:42:52] (current)
mi [Vacuum tables 1 at a time]
@@ Line 1: / Line 1: @@
 ====== Postgresql ======
-===== List tables =====
+===== List databases =====
@@ Line 17: / Line 17: @@
 or
-  psql -U postgres -t -c "SELECT datname, format('%8s GB.', pg_database_size(datname)/1000000) FROM pg_database" | sort
+  psql -U postgres -t -c "SELECT datname, format('%8s MB.', pg_database_size(datname)/1000000) FROM pg_database" | sort
+===== Show data directory =====
+  psql -U postgres -tA -c "SHOW data_directory;"
+===== Show all paths =====
+  psql -U postgres -c "SELECT name, setting FROM pg_settings WHERE setting LIKE '/%';"
+or
+  psql -U postgres -c "SELECT name, setting FROM pg_settings WHERE name LIKE '%dir%' OR name LIKE '%file%';"
+===== Show all connections =====
+  psql -U postgres -Pfooter=off -c \
+  "SELECT pid, datname, usename, concat_ws(' ', client_addr, client_hostname) AS host, \
+   application_name, wait_event_type, wait_event, state, query \
+   FROM pg_stat_activity WHERE NOT query LIKE '%SELECT % FROM pg_stat_activity%'"
+or
+  PGPASSFILE=/docs/www/.pgpass psql -U postgres -h $host --html -Pfooter=off -c "
+    SELECT Count(*) AS connections, datname, usename, concat_ws(' ', client_addr, client_hostname) AS host
+         , application_name, wait_event_type, wait_event, state
+    FROM pg_stat_activity
+    WHERE NOT query LIKE '%SELECT % FROM pg_stat_activity%'
+    GROUP BY datname, usename, host, application_name, wait_event_type, wait_event, state;" 2>&1
+===== Vacuum tables 1 at a time =====
+<code bash>db=my_database
+psql -U postgres -d $db -tA -c "SELECT table_name FROM information_schema.tables WHERE table_schema='public'" \
+| while read -r tbl; do \
+    echo "==Table $tbl";
+    vacuumdb -U postgres -d $db -t "\"$tbl\"" --full --verbose 2>&1;
+  done \
+| tee -a vacuum-$db.log
+</code>
+===== Rename and comments =====
+<code bash>oldname=old_db_name; newname=new_db_name; comment="some description ..."
+dropdb -U postgres --if-exists -i $newname
+psql -U postgres -c "ALTER database $oldname RENAME TO $newname;"
+# or with comment
+psql -U postgres -c "ALTER database $oldname RENAME TO $newname; COMMENT ON DATABASE $newname IS '$comment'"
+</code>
+====== Backups ======
+===== Copy DB on same host =====
+<code bash>
+sourcedb=zresolve_16
+newdb=zresolve_xx_will_be_deleted
+psql -U postgres -c "SELECT * FROM pg_stat_activity WHERE datname='$sourcedb' OR datname='$newdb';"
+# Drop the $newdb if it already exists:
+dropdb -e -i -U postgres zresolve_16
+# or psql -U postgres -c "DROP DATABASE IF EXISTS $newdb"
+# Copy $sourcedb to $newdb:
+time psql -U postgres --echo-errors -L resolve-db-copy.log -c "CREATE DATABASE $newdb WITH TEMPLATE $sourcedb OWNER postgres;" 2>&1 | tee resolve-db-copy-output.txt
+</code>
+===== Copy DB to new cluster =====
+Copy databasde to new cluster after installing new Postgres version.<br>
+This connects to the old cluster on the default port 5432, using the new pg_dump* binaries and pipes to the new psql on the port of the new cluster.
+<code bash>db=my_db_to_copy
+newport=5434
+newbin=/usr/lib/postgresql/14/bin
+$newbin/pg_dumpall -U postgres --roles-only      | $newbin/psql -p $newport -U postgres
+$newbin/pg_dump    -U postgres -d $db -C --clean | $newbin/psql -p $newport -U postgres
+</code>
+===== Backup =====
+  time pg_dump -U postgres -F custom --create --no-password --lock-wait-timeout=60000 -Z0 -f $dump_file $db
+===== Restore backup =====
+If you have a .gz compressed backup, ungzip it:
+  cd /path/to/somedir  # cd to a directory with enough space!
+  unpigz -k -d -v $dump_file.gz
+  createdb -U postgres $db
+  time pg_restore -U postgres -j 8 -v -d $db $dump_file
+====== Postgresql and DaVinci Resolve ======
+===== Get version =====
+Show current Resolve DB version
+  psql -U postgres -d zresolve_18 -c "SELECT version, time, remark FROM database_upgrade_log ORDER BY time"
+===== Projects =====
+<code>psql -U postgres -h $host -d $db -t -A -F " " -Pfooter=off <<ENDSQL 2>&1
+  SELECT
+      COALESCE(path, '-')
+    , "SM_Project"."ProjectName"
+    , to_timestamp("SM_Project"."LastModTimeInSecs") AS last_modified
+    , "SM_Project"."SysId"                           AS last_sysid
+  FROM
+  (
+    WITH RECURSIVE folders AS (
+        SELECT "SM_ProjectFolder"."SM_ProjectFolder_id",
+               "SM_ProjectFolder"."Name" AS path,
+               "SM_ProjectFolder"."ParentFolder"
+        FROM "SM_ProjectFolder"
+        WHERE "SM_ProjectFolder"."ParentFolder" IS NULL
+      UNION ALL
+        SELECT "SM_ProjectFolder"."SM_ProjectFolder_id",
+          (folders_1.path::text || '/'::text) || "SM_ProjectFolder"."Name"::text AS path,
+          "SM_ProjectFolder"."ParentFolder"
+        FROM "SM_ProjectFolder"
+        JOIN folders folders_1 ON folders_1."SM_ProjectFolder_id" = "SM_ProjectFolder"."ParentFolder"
+    )
+    SELECT folders."SM_ProjectFolder_id" AS folder_id,
+      folders.path
+    FROM folders
+  ) AS v_folders
+  RIGHT JOIN "SM_Project"
+  ON "SM_Project"."Folder" = v_folders.folder_id
+  ORDER BY last_modified DESC;
+ENDSQL
+</code>

Alma wiki

User Tools

Site Tools

Differences

Page Tools