Date: Wed, 28 Aug 2019 22:52:32 +0200 From: Torsten Zuehlsdorff <freebsd@toco-domains.de> To: Matthias Fechner <idefix@fechner.net>, Palle Girgensohn <girgen@FreeBSD.org>, ports-committers@freebsd.org, svn-ports-all@freebsd.org, svn-ports-head@freebsd.org, Sunpoet Po-Chuan Hsieh <sunpoet@FreeBSD.org> Subject: Re: svn commit: r505245 - in head/databases: postgresql10-client postgresql10-contrib postgresql10-pgtcl postgresql10-server postgresql11-client postgresql11-server postgresql94-server postgresql95-cli... Message-ID: <4713097f-0ff4-d120-cc0b-1239caf0c296@toco-domains.de> In-Reply-To: <18a4b88d-ce9d-88a8-2fb5-1d9bd8036f0d@fechner.net> References: <201906272128.x5RLS0Uv072609@repo.freebsd.org> <e0f21a7b-c748-b55a-d228-bae0f11fdfd2@fechner.net> <899f2367-828a-9eca-dfaa-e34a039fcdac@toco-domains.de> <9ac8aad8-3635-01eb-6033-c4a337f03139@fechner.net> <18a4b88d-ce9d-88a8-2fb5-1d9bd8036f0d@fechner.net>
next in thread | previous in thread | raw e-mail | index | archive | help
Aloha, On 08.08.19 12:50, Matthias Fechner wrote: > Am 28.07.2019 um 10:16 schrieb Matthias Fechner: >> - Upgrade PostgreSQL to at least 10.7: >> https://docs.gitlab.com/omnibus/settings/database.html#upgrade-packaged-postgresql-server >> >> Maybe we can coordinate the upgrade of PostgreSQL to 11, with rails to >> 5.2 with Gitlab to 12.1? > > I did now a successfull update (with the old Gitlab version) to > PostgreSQL 11 (on a test environment) with: > service gitlab stop > service postgresql stop > pkg create postgresql96-server postgresql96-contrib > mkdir /tmp/pg-upgrade > tar xf postgresql96-server-9.6.14_1.txz -C /tmp/pg-upgrade > tar xf postgresql96-contrib-9.6.14_1.txz -C /tmp/pg-upgrade > > pkg delete -f postgresql96-server postgresql96-contrib postgresql96-client > pkg install postgresql11-server postgresql11-contrib postgresql11-client > pkg upg > > /usr/local/etc/rc.d/postgresql initdb > su -l postgres -c "pg_upgrade -b /tmp/pg-upgrade/usr/local/bin/ -d > /var/db/postgres/data96/ -B /usr/local/bin/ -D /var/db/postgres/data11/" This does not work at all. While the update from 9.6 to 11 works this way, it doesn't do it for 9.5 to 11. Reason behind the problem is, that the default user switched from pgsql to postgres. My current UPDATING draft looks like this: === start === 20190828: AFFECTS: users of database/postgresql* and other software using PostgreSQL to run AUTHOR: tz@FreeBSD.org The default version of PostgreSQL has been switched from 9.5 to 11. The upgrade procedure can use up twice the space the databases currently needs. If you have a big amount of stored data take a closer look at the manpage of pg_upgrade for avoidance and/or speedup of the upgrade. The upgrade instructions consider a basic usage and do not match complex scenarious like replication, sharding or similar. Upgrade instructions: First stop your PostgreSQL, create PostgreSQL-binaries and backup your data. If you have another Version of PostgreSQL installed, for example 9.5.19, your files are named accourding to this. # service postgresql stop # pkg create postgresql95-server postgresql95-contrib # mkdir /tmp/pg-upgrade # tar xf postgresql95-server-9.5.19.txz -C /tmp/pg-upgrade # tar xf postgresql95-contrib-9.5.19.txz -C /tmp/pg-upgrade # pkg delete -f databases/postgresql95-server databases/postgresql95-contrib databases/postgresql95-client Now update PostgreSQL: pkg user: # pkg install databases/postgresql11-server databases/postgresql11-contrib # pkg upgrade Portmaster users: # portmaster databases/postgresql11-server databases/postgresql11-contrib # portmaster -a Portupgrade users: # portinstall databases/postgresql11-server databases/postgresql11-contrib # portupgrade -a After installing the new PostgreSQL version you need to convert all your databases to new version: # service postgresql initdb # chown -R postgres /usr/local/pgsql/data/ # su -l postgres -c "pg_upgrade -b /tmp/pg-upgrade/usr/local/bin/ -d /usr/local/pgsql/data/ -B /usr/local/bin/ -D /var/db/postgres/data11/" Now the migration is finished. You can start PostgreSQL again with: # service postgresql start ATTENTION: please also notice 2 important changes: - The default user changed from "pgsql" to "postgres" - The default data dir changed from "/usr/local/pgsql/data/" to "/var/db/postgres/data11/" === END === But the pg_upgrade command does not work currently. If someone has an idea, please give me a pointer. Thank you, Torsten
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?4713097f-0ff4-d120-cc0b-1239caf0c296>