====== Postgresql ======

===== List databases =====


  psql -U postgres -tA -c 'SELECT datname FROM pg_database' | sort

===== Get statistics =====

  db=... ; pghost=localhost
  psql -U postgres -h $pghost -t -A -c "SELECT datname, tup_inserted, tup_updated, tup_deleted FROM pg_stat_database WHERE datname='$db';"

===== Show sizes =====

  psql -U postgres -t -c 'SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database' | sort

or

  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>