Differences

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

--- postgresql_upgrade_majort_version [2020/01/20 15:36] – created andonovj
+++ postgresql_upgrade_majort_version [2020/01/26 17:06] (current) – external edit 127.0.0.1
@@ Line 103: / Line 103: @@
 Checking for prepared transactions                          ok
-***Clusters are compatible***
+*Clusters are compatible* <- This line is VERY IMPORTANT :)
 -bash-4.2$
 </sxh>
+From this point, we have two ways we can go.
+  * Ask pg_upgrade to copy the datafiles from old -> new location. The downtime depends the size of the DB
+  * Ask pg_upgrade to LINK the datafiles (hard links). The downtime is minimal.
+Since we want to have the least downtime, we will use 2nd option
+====Stop the databases====
+Since we didn't start the new version. Stop the old database version:
+<sxh bash>
+-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/data/ stop
+waiting for server to shut down.... done
+server stopped
+</sxh>
+Once stoppped, let's upgrade:
+====Link the new Version====
+<sxh bash>
+-bash-4.2$ /usr/pgsql-10/bin/pg_upgrade -b /usr/pgsql-9.6/bin/ -B /usr/pgsql-10/bin -d /var/lib/pgsql/9.6/data -D /var/lib/pgsql/10/data -k
+Performing Consistency Checks
+-----------------------------
+Checking cluster versions                                   ok
+Checking database user is the install user                  ok
+Checking database connection settings                       ok
+Checking for prepared transactions                          ok
+Checking for reg* data types in user tables                 ok
+Checking for contrib/isn with bigint-passing mismatch       ok
+Checking for invalid "unknown" user columns                 ok
+Creating dump of global objects                             ok
+Creating dump of database schemas
+                                                            ok
+Checking for presence of required libraries                 ok
+Checking database user is the install user                  ok
+Checking for prepared transactions                          ok
+If pg_upgrade fails after this point, you must re-initdb the
+new cluster before continuing.
+Performing Upgrade
+------------------
+Analyzing all rows in the new cluster                       ok
+Freezing all rows in the new cluster                        ok
+Deleting files from new pg_xact                             ok
+Copying old pg_clog to new server                           ok
+Setting next transaction ID and epoch for new cluster       ok
+Deleting files from new pg_multixact/offsets                ok
+Copying old pg_multixact/offsets to new server              ok
+Deleting files from new pg_multixact/members                ok
+Copying old pg_multixact/members to new server              ok
+Setting next multixact ID and offset for new cluster        ok
+Resetting WAL archives                                      ok
+Setting frozenxid and minmxid counters in new cluster       ok
+Restoring global objects in the new cluster                 ok
+Restoring database schemas in the new cluster
+                                                            ok
+Adding ".old" suffix to old global/pg_control               ok
+If you want to start the old cluster, you will need to remove
+the ".old" suffix from /var/lib/pgsql/9.6/data/global/pg_control.old.
+Because "link" mode was used, the old cluster cannot be safely
+started once the new cluster has been started.
+Linking user relation files
+                                                            ok
+Setting next OID for new cluster                            ok
+Sync data directory to disk                                 ok
+Creating script to analyze new cluster                      ok
+Creating script to delete old cluster                       ok
+Checking for hash indexes                                   ok
+Upgrade Complete
+----------------
+Optimizer statistics are not transferred by pg_upgrade so,
+once you start the new server, consider running:
+    ./analyze_new_cluster.sh
+Running this script will delete the old cluster's data files:
+    ./delete_old_cluster.sh
+-bash-4.2$
+</sxh>
+====Start the new Cluster====
+After that, we can delete the old cluster and start the one.
+Please don't be afraid that the upgrade is via links, in that case links mean that both files point to the same piece of chunk in the hard disk. Meaning as long as we have one pointer (the new version) we can delete the old one (9.6).
+These AREN'T SYMBOLIC links, these are hard links to the hard disk.
+<sxh bash>
+-bash-4.2$ cat delete_old_cluster.sh
+#!/bin/sh
+rm -rf '/var/lib/pgsql/9.6/data'
+-bash-4.2$ rm -rf '/var/lib/pgsql/9.6/data'
+-bash-4.2$ /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data start
+waiting for server to start....2020-01-20 10:58:18.330 EST [4917] LOG:  listening on IPv6 address "::1", port 5432
+-01-20 10:58:18.330 EST [4917] LOG:  listening on IPv4 address "127.0.0.1", port 5432
+-01-20 10:58:18.332 EST [4917] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
+-01-20 10:58:18.363 EST [4917] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
+-01-20 10:58:18.370 EST [4917] LOG:  redirecting log output to logging collector process
+-01-20 10:58:18.370 EST [4917] HINT:  Future log output will appear in directory "log".
+ done
+server started
+-bash-4.2$ psql
+psql (10.11) <- Finally, upgraded to 10.11
+Type "help" for help.
+postgres=#
+</sxh>
+======Out-Of-Place======
+Out of place of place upgrade usually is done using one of the following:
+  * Complete installation of the new PostgreSQL on 2nd host and using pg_dumpall, pg_dump and psql to restore the data
+  * Using logical replication to minimize the downtime to almost 0.
+We have two environments:
+PostgreSQL 9.1 on Linux 5, which should be migrated to:
+PostgreSQL 10 on Linux 7
+To migrate from such an old version there are two options
+  - Upgrade the current version to 10 and simply migrate the data using backup/restore
+  -  Backup the current version 9.1 using (pg_dumpall and pg_dump) and restore it (pg_restore) to the new version 10
+Since the first path will take waaay longer and since we are lazy AF, we will be using the second path
+=====Backup=====
+In order to backup the data in universal format we have to:
+  - Dump the globals: Roles & Tablespaces using pg_dumpall
+  - Dump each database (we will use the custom format "-Fc" since it is more flexible) using pg_dump -Fc
+So let's get going:
+===Dump Globals===
+<sxh bash>
+</sxh>