postgresql
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
postgresql [2022-04-25 16:47:22] mi |
postgresql [2022-11-15 10:42:52] (current) mi [Vacuum tables 1 at a time] |
||
|---|---|---|---|
| Line 37: | Line 37: | ||
| application_name, wait_event_type, wait_event, state, query \ | application_name, wait_event_type, wait_event, state, query \ | ||
| FROM pg_stat_activity WHERE NOT query LIKE '%SELECT % FROM pg_stat_activity%'" | 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 ===== | ===== Vacuum tables 1 at a time ===== | ||
| Line 48: | Line 58: | ||
| | tee -a vacuum-$db.log | | tee -a vacuum-$db.log | ||
| </code> | </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 ====== | ====== Postgresql and DaVinci Resolve ====== | ||
| Line 55: | Line 116: | ||
| psql -U postgres -d zresolve_18 -c "SELECT version, time, remark FROM database_upgrade_log ORDER BY time" | 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> | ||
/docs/dokuwiki/data/attic/postgresql.1650898042.txt.gz ยท Last modified: 2022-04-25 16:47:22 by mi
Page Tools
