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

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';"

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

psql -U postgres -tA -c "SHOW data_directory;"

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%';"

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

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

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'"

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

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.

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

time pg_dump -U postgres -F custom --create --no-password --lock-wait-timeout=60000 -Z0 -f $dump_file $db

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

Show current Resolve DB version

psql -U postgres -d zresolve_18 -c "SELECT version, time, remark FROM database_upgrade_log ORDER BY time"

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