Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pA3lg-0008Us-7A for pgsql-general@arkaria.postgresql.org; Tue, 27 Dec 2022 06:48:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pA3lf-0000Ee-2N for pgsql-general@arkaria.postgresql.org; Tue, 27 Dec 2022 06:48:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pA3le-0000EQ-Mq for pgsql-general@lists.postgresql.org; Tue, 27 Dec 2022 06:48:54 +0000 Received: from mail-oi1-x22b.google.com ([2607:f8b0:4864:20::22b]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pA3lc-00038q-Fi for pgsql-general@lists.postgresql.org; Tue, 27 Dec 2022 06:48:54 +0000 Received: by mail-oi1-x22b.google.com with SMTP id s186so11758234oia.5 for ; Mon, 26 Dec 2022 22:48:52 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=SI3QXLfsku806APkvPqhgkDJda+Ad4UHrPR+3AJbgM8=; b=Rl3AjSInznexVSRCgfduPhap2GLpjTPzrQuwLTTRaZppRmmNhWO6SsDqx9Ck4BtLTT GQc+vgDSjc6IgX/7DpuUWf3DHngAgw3ENLPJ5WZuQV3hUKesjEVKJR/0lpWsxh2skxF2 0jSQ1UdC1w1EEU9W9YIlyGduZeykKHmJio2PiN+jxoUEP1q+6yGRaDXKgMAPXWRj64TF KJa6/SVkRnQ1/FcDyIVF5QzAMkhX0h/PGLeZGOq7+b23t119oX3Ky3anJ0DTlOm+zM3G AFZPJ/o36cVTsiuVaPf3OPaBrQcnc+UopGxjvPj48KlraO+mQjvtUJ/nZ2tgwga66Sml R7Bg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=SI3QXLfsku806APkvPqhgkDJda+Ad4UHrPR+3AJbgM8=; b=aPVFX2bzkA8n7qCLwG+fiUIl7GNhhaP7Vlm73gi088LsX8zY7IZq1OQORkBu3INgIF CsmHmubK4W38pS/jxRMumFfRwiOx8vzsR/c1WNFlFL3hF45nLcc2K0yJCYq9yoyDEhvf Q+bimlKUPH5uq09Az86Ww31VGHL/7/HQ8XKcMSBnNAYZ9P7axJq+pBDIgA/HSqcpHM8O iT7I/YSqi8dBZ+Hmj0mS2frvUtR3m5EugzS5dMgwyEUD3m5BuJvpKMsUYe9Fei2xATdl VH/bs25b+p7fwCFYXD8ciWVDJv0tYq1rHw/agtfJ2c+VZ782LLrvUzCTIkhxu7pVej3D dABw== X-Gm-Message-State: AFqh2krwHhTW0tOACNlD8rYvSR2LrXudDRH0zh7d/A/Z5mAPVjrdMnWG JBMalgHpIwExCwl6l7WtmAt/W32ixEE= X-Google-Smtp-Source: AMrXdXuhcntfXUiQJYhGQNhhduaTjbpp/PTBNiFCnCJq4RVunp5lxGLYXwXIDAJartbuBoNxlYEbPA== X-Received: by 2002:a05:6808:1296:b0:35e:ba96:cd55 with SMTP id a22-20020a056808129600b0035eba96cd55mr13253707oiw.19.1672123730675; Mon, 26 Dec 2022 22:48:50 -0800 (PST) Received: from [192.168.1.66] (108-250-206-84.lightspeed.nworla.sbcglobal.net. [108.250.206.84]) by smtp.googlemail.com with ESMTPSA id p64-20020acaf143000000b00354d8589a15sm5519657oih.45.2022.12.26.22.48.49 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 26 Dec 2022 22:48:50 -0800 (PST) Content-Type: multipart/alternative; boundary="------------lgPdQQ00gYT7UgM0XFmqkU1i" Message-ID: <0a9d2078-1900-1e0c-be96-f4d5bdc6df22@gmail.com> Date: Tue, 27 Dec 2022 00:48:49 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: best practice to patch a postgresql version? Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------lgPdQQ00gYT7UgM0XFmqkU1i Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit If it really is a critical production database, you will have a CAT/UAT (customer/user acceptance testing) server on which you rigorously run regression tests on a point release for a month before updating the production server. Otherwise, it's a hope-and-pray database. On 12/26/22 23:33, qihua wu wrote: > Thanks Ron, > > But on a critical production database, we need to cut down the downtime as > much as possible. If just remove a version, and then install a new > version, both of them need a downtime. If we can install several versions > on different location, switching version will have a shorter downtime: > just stop the old version and start using the new binary, and we have no > downtime when remove/install a new version. > > On Mon, Dec 26, 2022 at 11:54 PM Ron wrote: > > Just downgrade the packages if you need to revert to a previous version. > > Remove the 14*.5* package, and install the 14*.4* package (because no > one's crazy enough to start with 14.0 in December 2022).  You'll have > to explicitly specify the version number. > > On 12/26/22 03:29, qihua wu wrote: >> We are planning to use postgresq on production, but there is one >> question about how to patch a db. We don't want to overwrite the old >> version directly, so that we can rollback if the new version has >> issues. So we want to install it a different location such as >> /home/postgres/14.1 for version 14.1 (all binary should be under 14.1 >> or sub-fold of 14.1) and /home/postgres/14.0 for 14.0, in this way we >> can easily switch between different versions. But apt install on >> ubuntu doesn't have the option for a customized location. So what's >> the best practice to patch postgres? > > -- > Born in Arizona, moved to Babylonia. > -- Born in Arizona, moved to Babylonia. --------------lgPdQQ00gYT7UgM0XFmqkU1i Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit If it really is a critical production database, you will have a CAT/UAT (customer/user acceptance testing) server on which you rigorously run regression tests on a point release for a month before updating the production server.

Otherwise, it's a hope-and-pray database.

On 12/26/22 23:33, qihua wu wrote:
Thanks Ron,

But on a critical production database, we need to cut down the downtime as much as possible. If just remove a version, and then install a new version, both of them need a downtime. If we can install several versions on different location, switching version will have a shorter downtime: just stop the old version and start using the new binary, and we have no downtime when remove/install a new version.

On Mon, Dec 26, 2022 at 11:54 PM Ron <ronljohnsonjr@gmail.com> wrote:
Just downgrade the packages if you need to revert to a previous version.

Remove the 14.5 package, and install the 14.4 package (because no one's crazy enough to start with 14.0 in December 2022).  You'll have to explicitly specify the version number.

On 12/26/22 03:29, qihua wu wrote:
We are planning to use postgresq on production, but there is one question about how to patch a db. We don't want to overwrite the old version directly, so that we can rollback if the new version has issues.  So we want to install it a different location such as /home/postgres/14.1 for version 14.1 (all binary should be under 14.1 or sub-fold of 14.1) and /home/postgres/14.0 for 14.0, in this way we can easily switch between different versions. But apt install on ubuntu doesn't have the option for a customized location. So what's the best practice to patch postgres?

--
Born in Arizona, moved to Babylonia.

--
Born in Arizona, moved to Babylonia.
--------------lgPdQQ00gYT7UgM0XFmqkU1i--