Differences

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

--- postgresql [2022-04-25 17:09:35]
mi
+++ postgresql [2022-11-15 10:42:52] (current)
mi [Vacuum tables 1 at a time]
@@ Line 59: / Line 59: @@
 </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 ======
@@ Line 75: / Line 83: @@
 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 =====

Alma wiki