On 2017/02/08 21:00, Jeremy Evans wrote: > On 02/09 02:35, Stuart Henderson wrote: > > I've just tried pg_upgrade following the pkg-readme. It went OK in the > > end but there were some bumps along the way. A few notes: > > Sorry about that. The blame lies with me as I wrote the documentation.
That's OK, it gave me a good headstart and the whole process was far less painful than before, many thanks for doing the work on -previous and pg_upgrade! > How about the following patch, which I think addresses all of the issues > you found? To fix the data.old issue, let's version the old directory, > since that makes data loss less likely. We will need to remember to > update the version when the PostgreSQL major version changes. That's a great idea - here it is again with tweaks so that we don't need to change it manually (and same for the @ask-update line). I also reduced the spacing on the pg_upgrade lines to match the earlier cp lines, which also took it down to one continuation line instead of two. Index: Makefile =================================================================== RCS file: /cvs/ports/databases/postgresql/Makefile,v retrieving revision 1.221 diff -u -p -r1.221 Makefile --- Makefile 26 Dec 2016 13:56:04 -0000 1.221 +++ Makefile 9 Feb 2017 10:49:46 -0000 @@ -7,11 +7,10 @@ COMMENT-contrib=PostgreSQL RDBMS contrib COMMENT-plpython=Python procedural language for PostgreSQL COMMENT-pg_upgrade=Support for upgrading PostgreSQL data from previous version -# DO NOT FORGET to also change the @ask-update entry in pkg/PLIST-server -# in case a dump before / restore after pkg_add -u is required! - VERSION= 9.6.1 +PREV_MAJOR= 9.5 REVISION= 0 +REVISION-server= 1 DISTNAME= postgresql-${VERSION} PKGNAME-main= postgresql-client-${VERSION} PKGNAME-server= postgresql-server-${VERSION} @@ -42,7 +41,8 @@ MULTI_PACKAGES= -docs -main -server -con MAKE_FILE= GNUmakefile -SUBST_VARS= VERSION +V_MAJOR= ${VERSION:R} +SUBST_VARS= VERSION V_MAJOR PREV_MAJOR USE_GMAKE= Yes USE_GROFF= Yes Index: pkg/PLIST-server =================================================================== RCS file: /cvs/ports/databases/postgresql/pkg/PLIST-server,v retrieving revision 1.30 diff -u -p -r1.30 PLIST-server --- pkg/PLIST-server 18 Dec 2016 18:18:27 -0000 1.30 +++ pkg/PLIST-server 9 Feb 2017 10:49:46 -0000 @@ -4,7 +4,7 @@ @conflict postgresql-clients-* @conflict postgresql-server-* @conflict postgresql-* -@ask-update postgresql-server-<9.6 Make sure your existing database is backed up +@ask-update postgresql-server-<${V_MAJOR} Make sure your existing database is backed up @newgroup _postgresql:503 @newuser _postgresql:503:_postgresql:daemon:PostgreSQL Manager:/var/postgresql:/bin/sh @bin bin/initdb Index: pkg/README-server =================================================================== RCS file: /cvs/ports/databases/postgresql/pkg/README-server,v retrieving revision 1.20 diff -u -p -r1.20 README-server --- pkg/README-server 18 Dec 2016 18:18:27 -0000 1.20 +++ pkg/README-server 9 Feb 2017 10:49:46 -0000 @@ -125,7 +125,7 @@ to the current version. # pkg_add -ui postgresql-server 4) Backup your old data directory: -# mv /var/postgresql/data /var/postgresql/data.old +# mv /var/postgresql/data /var/postgresql/data-${PREV_MAJOR} 5) Create a new data directory (using -E UTF8 if appropriate): # su _postgresql -c "mkdir /var/postgresql/data" @@ -133,9 +133,9 @@ to the current version. 6) Restore your old pg_hba.conf and (if used) SSL certificates # su _postgresql -c \ - "cp /var/postgresql/data.old/pg_hba.conf /var/postgresql/data/" + "cp /var/postgresql/data-${PREV_MAJOR}/pg_hba.conf /var/postgresql/data/" # su _postgresql -c \ - "cp /var/postgresql/data.old/server.{crt,key} /var/postgresql/data/" + "cp /var/postgresql/data-${PREV_MAJOR}/server.{crt,key} /var/postgresql/data/" Some postgresql.conf settings changed or disappeared in this version. Examine your old file for local changes and apply them to the new version. @@ -157,10 +157,10 @@ faster than a dump and reload, especiall # ${RCDIR}/postgresql stop 2) Upgrade your PostgreSQL package with pkg_add. -# pkg_add -ui postgresql-pg_upgrade +# pkg_add postgresql-pg_upgrade 3) Backup your old data directory: -# mv /var/postgresql/data /var/postgresql/data.old +# mv /var/postgresql/data /var/postgresql/data-${PREV_MAJOR} 4) Create a new data directory (using -E UTF8 if appropriate): # su _postgresql -c "mkdir /var/postgresql/data" @@ -168,19 +168,26 @@ faster than a dump and reload, especiall 5) Restore your old pg_hba.conf and (if used) SSL certificates # su _postgresql -c \ - "cp /var/postgresql/data.old/pg_hba.conf /var/postgresql/data/" + "cp /var/postgresql/data-${PREV_MAJOR}/pg_hba.conf /var/postgresql/data/" # su _postgresql -c \ - "cp /var/postgresql/data.old/server.{crt,key} /var/postgresql/data/" + "cp /var/postgresql/data-${PREV_MAJOR}/server.{crt,key} /var/postgresql/data/" Some postgresql.conf settings changed or disappeared in this version. Examine your old file for local changes and apply them to the new version. -6) Run pg_upgrade: -# su _postgresql -c "pg_upgrade -b /usr/local/bin/postgresql-9.5/ - -B /usr/local/bin -U postgres - -d /var/postgresql/data.old/ -D /var/postgresql/data" +6) Temporarily support connecting without a password for local users by + editing pg_hba.conf to include "local all postgres trust" +# su _postgresql -c "vi /var/postgresql/data/pg_hba.conf" + +7) Run pg_upgrade: +# su _postgresql -c "cd /var/postgresql && \ + pg_upgrade -b /usr/local/bin/postgresql-${PREV_MAJOR}/ -B /usr/local/bin \ + -U postgres -d /var/postgresql/data-${PREV_MAJOR}/ -D /var/postgresql/data" -7) Start PostgreSQL: +8) Remove "local all postgres trust" line from pg_hba.conf +# su _postgresql -c "vi /var/postgresql/data/pg_hba.conf" + +9) Start PostgreSQL: # ${RCDIR}/postgresql start Clients/Frontends