From owner-svn-ports-head@freebsd.org Wed Aug 28 20:52:46 2019 Return-Path: Delivered-To: svn-ports-head@mailman.nyi.freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2610:1c1:1:606c::19:1]) by mailman.nyi.freebsd.org (Postfix) with ESMTP id 165A0E6358; Wed, 28 Aug 2019 20:52:46 +0000 (UTC) (envelope-from freebsd@toco-domains.de) Received: from toco-domains.de (mail.toco-domains.de [176.9.100.27]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) server-signature RSA-PSS (4096 bits)) (Client did not present a certificate) by mx1.freebsd.org (Postfix) with ESMTPS id 46JdH05ywLz3D4c; Wed, 28 Aug 2019 20:52:44 +0000 (UTC) (envelope-from freebsd@toco-domains.de) Received: by toco-domains.de (Postfix, from userid 65534) id 24735B4789; Wed, 28 Aug 2019 22:52:37 +0200 (CEST) X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on toco-mail X-Spam-Level: X-Spam-Status: No, score=-2.9 required=4.0 tests=ALL_TRUSTED,BAYES_00, URIBL_BLOCKED autolearn=unavailable autolearn_force=no version=3.4.2 Received: from [192.168.1.7] (x5d871fb4.dyn.telefonica.de [93.135.31.180]) by toco-domains.de (Postfix) with ESMTPA id 344B6B4776; Wed, 28 Aug 2019 22:52:33 +0200 (CEST) Subject: Re: svn commit: r505245 - in head/databases: postgresql10-client postgresql10-contrib postgresql10-pgtcl postgresql10-server postgresql11-client postgresql11-server postgresql94-server postgresql95-cli... To: Matthias Fechner , Palle Girgensohn , ports-committers@freebsd.org, svn-ports-all@freebsd.org, svn-ports-head@freebsd.org, Sunpoet Po-Chuan Hsieh References: <201906272128.x5RLS0Uv072609@repo.freebsd.org> <899f2367-828a-9eca-dfaa-e34a039fcdac@toco-domains.de> <9ac8aad8-3635-01eb-6033-c4a337f03139@fechner.net> <18a4b88d-ce9d-88a8-2fb5-1d9bd8036f0d@fechner.net> From: Torsten Zuehlsdorff Openpgp: preference=signencrypt Message-ID: <4713097f-0ff4-d120-cc0b-1239caf0c296@toco-domains.de> Date: Wed, 28 Aug 2019 22:52:32 +0200 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:60.0) Gecko/20100101 Thunderbird/60.8.0 MIME-Version: 1.0 In-Reply-To: <18a4b88d-ce9d-88a8-2fb5-1d9bd8036f0d@fechner.net> Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: 7bit X-Rspamd-Queue-Id: 46JdH05ywLz3D4c X-Spamd-Bar: - Authentication-Results: mx1.freebsd.org; dkim=none; dmarc=none; spf=pass (mx1.freebsd.org: domain of freebsd@toco-domains.de designates 176.9.100.27 as permitted sender) smtp.mailfrom=freebsd@toco-domains.de X-Spamd-Result: default: False [-1.39 / 15.00]; ARC_NA(0.00)[]; RCVD_VIA_SMTP_AUTH(0.00)[]; RCVD_COUNT_TWO(0.00)[2]; NEURAL_HAM_MEDIUM(-0.89)[-0.888,0]; FROM_HAS_DN(0.00)[]; TO_DN_SOME(0.00)[]; R_SPF_ALLOW(-0.20)[+mx]; NEURAL_HAM_LONG(-1.00)[-0.997,0]; MIME_GOOD(-0.10)[text/plain]; RCVD_TLS_LAST(0.00)[]; DMARC_NA(0.00)[toco-domains.de]; RCPT_COUNT_FIVE(0.00)[6]; TO_MATCH_ENVRCPT_SOME(0.00)[]; NEURAL_SPAM_SHORT(0.61)[0.606,0]; IP_SCORE(0.19)[ipnet: 176.9.0.0/16(2.78), asn: 24940(-1.80), country: DE(-0.01)]; RECEIVED_SPAMHAUS_PBL(0.00)[180.31.135.93.khpj7ygk5idzvmvt5x4ziurxhy.zen.dq.spamhaus.net : 127.0.0.10]; R_DKIM_NA(0.00)[]; MIME_TRACE(0.00)[0:+]; ASN(0.00)[asn:24940, ipnet:176.9.0.0/16, country:DE]; MID_RHS_MATCH_FROM(0.00)[]; FROM_EQ_ENVFROM(0.00)[] X-BeenThere: svn-ports-head@freebsd.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: SVN commit messages for the ports tree for head List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 28 Aug 2019 20:52:46 -0000 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