From owner-freebsd-database@freebsd.org Mon Jul 31 15:07:16 2017 Return-Path: Delivered-To: freebsd-database@mailman.ysv.freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:1900:2254:206a::19:1]) by mailman.ysv.freebsd.org (Postfix) with ESMTP id CF5BEDB0059 for ; Mon, 31 Jul 2017 15:07:16 +0000 (UTC) (envelope-from frans-jan@van-steenbeek.net) Received: from mail-wm0-x22b.google.com (mail-wm0-x22b.google.com [IPv6:2a00:1450:400c:c09::22b]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client CN "smtp.gmail.com", Issuer "Google Internet Authority G2" (verified OK)) by mx1.freebsd.org (Postfix) with ESMTPS id 6941F7E4B8 for ; Mon, 31 Jul 2017 15:07:16 +0000 (UTC) (envelope-from frans-jan@van-steenbeek.net) Received: by mail-wm0-x22b.google.com with SMTP id k20so16036493wmg.0 for ; Mon, 31 Jul 2017 08:07:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=van-steenbeek.net; s=google; h=date:from:to:subject:message-id:mime-version:content-disposition :user-agent; bh=IKuPoHOZxjTYBKuPfvLcUWHp/jnmFq4rh/ln96Czyu0=; b=WE45K2YOfb92+C4F/pxp5K1hurULOdRIiEKlMJzJdx8ejV1Bpsj6XXCLVbsCapNNKP 4qb+6zY+mGZFHj+/i7bUSRSUr78hAy84yqiqcCPk1QNYif0vqh7zc5lOoY3QJDgKPUPR QL77hppWRrkC1cNHq6yCaPIoO4B5LkMb5q8oIpK5tZlKaDVKWJsmheixUMQKPOEwRs1t cUSuaJPLM4SSNn2tDLAPohctfjiei8v2WCktbChl2KFi8molQkQk7bT1T1KegyMDuxdo xdrzIwlHzy1AxD6BDoSj5xqyBlHBD2z+GbKnN1U4TKsUK1FoLcD7+LnRTsXWrSqxK7Sg phfA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:subject:message-id:mime-version :content-disposition:user-agent; bh=IKuPoHOZxjTYBKuPfvLcUWHp/jnmFq4rh/ln96Czyu0=; b=gm75+nsCqP9yWcKiiOwAdFOawsVfUKSlJi4Bd9X5ta5RsBrw9JshFAdF/vN6p+T+Kv KMzN2FlQBnUj4RQav8Sgp7Jg5cXuF+cOPEw9TZrCmH5DoGh7mX8ba4m+worU13ljvJY3 PHyFX7Dg2gAoF5v0yFwmaxtoRDaH5t4256ef/MsdOrDJGQoCXdUDoEi5cCdOIkyEEg1u OlbxGEUdE1DLopGfn5ud28HGhtgmKs9z5ruDGSn5/nZZlRNkehxYLfYaDJQ+TnZkHPMV UQA8VepWVYd0LGlmroz34IyYTyV4BnxTu4qiIy0TOlxVoZKCv4rxLhSBQd+utApBIUhi kebg== X-Gm-Message-State: AIVw1139RD4qH9FLAo58ciaNSddBoJBdEf5Hb2DHGjnJOdMPFfgDPZwc GAuVhb8Gy0X+x8KJpMGHsw== X-Received: by 10.80.218.195 with SMTP id s3mr14412044edj.216.1501513634169; Mon, 31 Jul 2017 08:07:14 -0700 (PDT) Received: from van-steenbeek.net (84-245-25-67.dsl.cambrium.nl. [84.245.25.67]) by smtp.gmail.com with ESMTPSA id t36sm8732106edb.38.2017.07.31.08.07.12 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 31 Jul 2017 08:07:13 -0700 (PDT) Date: Mon, 31 Jul 2017 17:07:11 +0200 From: Frans-Jan van Steenbeek To: freebsd-database@freebsd.org Subject: How-to: pg_upgrade to 9.6 Message-ID: <20170731150711.GA7333@van-steenbeek.net> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="1yeeQ81UyVL57Vl7" Content-Disposition: inline User-Agent: Mutt/1.8.3 (2017-05-23) X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.23 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 31 Jul 2017 15:07:16 -0000 --1yeeQ81UyVL57Vl7 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Hello list, This is a quick and dirty how-to for people looking to upgrade their PostgreSQL instance to 9.6 from FreeBSD ports with pg_upgrade. Note the word "dirty": you may not want to do this on a database that is relied on for rocket science, high frequency trading or healthcare. I'm posting this here since in the hope that it is useful. I'm not subscribed to this list, so please CC me if you have questions, comments or concerns. The "problem" with using pg_upgrade to 9.6 on FreeBSD is mostly with the fact that the default user for PostgreSQL has been renamed from pgsql to postgres. Additionally, using pg_upgrade on FreeBSD involves a bit more steps in any case (compared to PostgreSQL's excellent docs) because you need to have both versions of postgresl*-server installed. Additionally, ICU-based collation is default now. Depending on your dataset, you may not want to use pg_upgrade or make sure you reindex all your UTF-8 stuff. OK, here goes. I assume you have recent and working backups at this point. - Make sure all clients stop talking to the database. While you're at it, why not upgrade them to 9.6 as well (not strictly necessary). - If you use ZFS, create a snapshot of all relevant filesystems. This helps to rollback in case of issues. ZFS is also a great addition to pg_upgrade's -k option. - Create a chroot for the old postgresql binaries: sudo bsdinstall jail /tmp/pg_upgrade Only base is enough, skip through the rest. - Install the old postgresql server package in the chroot created above: sudo pkg install -c /tmp/pg_upgrade postgresqlXX-server At this point, that's the same package as you already have on the system. - Create a second superuser (skip this if you already have any): sudo -iu pgsql psql -c "CREATE ROLE root;" postgres sudo -iu pgsql psql -c "ALTER ROLE root WITH SUPERUSER INHERIT \ CREATEROLE CREATEDB LOGIN;" postgres - With that new user, rename pgsql: sudo -iu root psql -c "ALTER USER pgsql RENAME TO postgres;" \ postgres - Stop the old server: sudo service postgresql stop - Install the new binaries: sudo pkg install postgresql96-server This will offer to replace the existing packages. - If you're datadir is not versioned, rename it and create a new folder for the new datadir. Keep them on the same filesystem! - Make sure the new user can access everything: chown -R postgres:postgres old_datadir new_datadir - Init the new datadir: sudo service postgresql initdb - Perform the actual upgrade: sudo -iu postgres pg_upgrade -b /tmp/pg_upgrade/usr/local/bin \ -B /usr/local/bin -d old_datadir -D new_datadir -j 4 -k Use more or less threads as you please. -k links all new files instead of copying, which may not be what you want (but is a blessing with ZFS). Keep in mind that without a snapshot or proper backups, you probably can *not* rollback if you use -k! - Review pg_hba.conf and postgresql.conf. You probably want to copy the old pg_hba.conf and edit/merge postgresql.conf. - Start the new server: sudo service postgresql start - Finish up: sudo -iu postgres ~postgres/analyze_new_cluster.sh - Remove the secondary superuser if you don't want to keep it around: sudo -iu postgres psql -c "DROP USER root;" postgres - If you're sure everything works: sudo -iu postgres ~postgres/delete_old_cluster.sh - Remove the old UNIX pgsql user: sudo rmuser pgsql I hope this helps somebody. Regards, Frans-Jan van Steenbeek --1yeeQ81UyVL57Vl7 Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQEzBAEBCgAdFiEEr+8lymTTiPAEi6aqJ1zNqKtuW2wFAll/R5wACgkQJ1zNqKtu W2zuWgf8DL3ux36ruwk6CwlThR5ClbCqVXPQnnHs4DrNdaYFJixOHt/LW95lJ+KE w/N+Dck3tmEwktP8PPffYumLDltHnqKTjHxZzvr1L4ES4KrCgSwDLlLrOnuz1u3g 5WelMGsYF6n9Jld0awhS0v+r3Qc4MhrzInpoaCwghCtH+O7h1mEr1Y1XCGYCtkzw WRv7quTlm9ngJSCxFkgK9H0JfF+VAlYenjxFw5/keMABJxPSHh9HGbRMaVs+8UrV zv4Pp/LOoUkyYRvQzfXB4EiXbomEInONyvME9UHoGDCTLYM3X/JEGz0VOu0KXchX U9+MSxY3e2cvDIe30w1A+jaYWBBcIQ== =GYeC -----END PGP SIGNATURE----- --1yeeQ81UyVL57Vl7--