Differences

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

--- postgresqlupgrade [2024/03/01 15:05] – created z0hpvk
+++ postgresqlupgrade [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 2: / Line 2: @@
 ==== Using pg_upgrade ====
+https://www.postgresql.org/docs/14/pgupgrade.html
-<code SHELL>
+<code bash>
 /usr/lib/postgresql/14/bin/pg_upgrade   --old-datadir=/var/lib/postgresql/10/main
                                         --new-datadir=/var/lib/postgresql/14/main
@@ Line 14: / Line 15: @@
 Upgrade Streaming Replication server. \\
-<code SHELL>
+<code bash>
 rsync --verbose --archive --delete --hard-links --size-only --no-inc-recursive --human-readable
 14 postgres@postgres02-upgrade:/var/lib/postgresql
 </code>
+==== Using pg_dumpall ====
+https://www.postgresql.org/docs/14/upgrading.html#UPGRADING-VIA-PGDUMPALL
+If necessary, create a new instance … \\
+<code bash>
+pg_createcluster -d /var/lib/postgresql/14/doob 14 doob
+</code>
+On postgres-test, this used port 5434 for the new instance. \\
+Copy the pg_hba.conf and postgresql.conf files from the old instance to the new one … \\
+<code bash>
+cp /var/lib/postgresql/10/main/postgresql.conf /var/lib/postgresql/14/doob
+cp /var/lib/postgresql/10/main/pg_hba.conf /var/lib/postgresql/14/doob
+</code>
+Edit the postgresql.conf and update relevant entries to 14/doob. \\
+Restart the new PostgreSQL 14 instance … \\
+<code bash>
+sudo systemctl restart postgresql@14-doob
+</code>
+Copy data from the old instance to the new using the following command … \\
+<code bash>
+pg_dumpall -p 5432 -U postgres -d postgres | psql -p 5434 -U postgres -d postgres
+</code>
+If you wish to test the above process again then delete the cluster and begin again from the start … \\
+<code bash>
+pg_dropcluster 14 doob --stop
+</code>
+Otherwise, stop the old instance and then configure the new instance to use port 5432 and restart.