Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iU5ky-000499-8f for pgsql-admin@arkaria.postgresql.org; Mon, 11 Nov 2019 09:13:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iU5kx-0001UX-2e for pgsql-admin@arkaria.postgresql.org; Mon, 11 Nov 2019 09:13:07 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iU5kw-0001U2-Kc for pgsql-admin@lists.postgresql.org; Mon, 11 Nov 2019 09:13:06 +0000 Received: from mail-wm1-x342.google.com ([2a00:1450:4864:20::342]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iU5kl-00020p-8e for pgsql-admin@postgresql.org; Mon, 11 Nov 2019 09:13:01 +0000 Received: by mail-wm1-x342.google.com with SMTP id q70so12434184wme.1 for ; Mon, 11 Nov 2019 01:12:51 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=zunibal-com.20150623.gappssmtp.com; s=20150623; h=to:from:subject:message-id:date:user-agent:mime-version :content-transfer-encoding:content-language; bh=ZuiKJ1eG5LaSx2WhT4Ci5XtcL2iFW1TqEYeiaBe8RKQ=; b=uS90lC2uuMyiVkObRzTl8tfe9YPXnV3G4+BAD+DUJwkjNqXj+5nCscNxQJ7DCpVNz6 Fu/vWE57d4g5kvFDH5YUgGK8uwABx50l7XnvfnActCGJIO1oXM0/Zi5PUtK52elanUlF t64zDwxnxKL7MN1cRXiFo9kUlkjBXhvb0RfGBWb4v1iKRO+AaRRHVDrnYWGCyWs7eyBr iVEdfMBLS/1gvyGmpLHxyZz2kXoS3Lf3b8bKwqMSLIs+l6G/76mzN4Bzb+AhN8iskkJX ecaJnXpu5Exy6+Uyy/JUK3com/UOIMH1h8IB3TSCBYvZxP17fGF3TdC9sMzU6Zf8Yz0e /uXg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:to:from:subject:message-id:date:user-agent :mime-version:content-transfer-encoding:content-language; bh=ZuiKJ1eG5LaSx2WhT4Ci5XtcL2iFW1TqEYeiaBe8RKQ=; b=p+MFvYMECslWQuZn38dVbG6P+eYO7b1jcbn170taT+4AlZAgg9KVb0N0l2Ue9a9bRc mBsvGeb1aF/0F6zOwYbXwuIx8Vilm1hkzFQUtdnbYE2Xixq+OEpcD5ZnKP7pBKNVhxZq N5oKc0/SE21VDkF3gMzLhvu9XTJ8KVeC1NRD9kSxnsYN2U6srXBVsWk9wEMFk5B50ONN +XlR23pE0C9MryKojlGEW/8ZkA0a3G9+wv6a7g0DGaFNiciU1YJUiDOH0B8UuBGK89qg PjUtUY5gl8NcltgJkG3B97RDRQsOzR/2YSOei8WzlhlLY99WEuEfWcOXyDhpcbDehieJ QKqQ== X-Gm-Message-State: APjAAAVkQjuVDAfF4o42iSCUomMHdJdU68CzvLzRiq3ve0dvc14+vlHR 2nEeRFv4LO0MwgOKofGYkwbCHw+f/C0= X-Google-Smtp-Source: APXvYqyMjpZy+ZFbRGWX8yLlBtCO6dn6HISzq3LJ7Reva5LDuQCqsLqGJiQqZiye7od4qUnoBJj8gg== X-Received: by 2002:a1c:4c10:: with SMTP id z16mr17954340wmf.24.1573463570178; Mon, 11 Nov 2019 01:12:50 -0800 (PST) Received: from [192.168.3.150] ([176.222.91.85]) by smtp.gmail.com with ESMTPSA id h124sm14100616wme.30.2019.11.11.01.12.49 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 11 Nov 2019 01:12:49 -0800 (PST) To: pgsql-admin@postgresql.org From: Ekaterina Amez Subject: Strategy for upgrade highly used server Message-ID: <5ce6203a-78cb-25b0-3b91-65179aa280e0@zunibal.com> Date: Mon, 11 Nov 2019 10:12:48 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.9.0 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi all, I'm trying to upgrade all postgres servers at my work place. I've began with oldest versions moving them to newer ones, basically from 8.4/9.2 to 9.6. When all are in 9.6, I'll go with v10 and after that... we'll see. One of the servers to be upgraded has special needs and I'd like your advice about how to upgrade it with the least downtime possible (and less chance to make a mistake I would add). This server has problems with free disk space (don't know details but it seems quite difficult to add more disks). And the database needs to be up almost 24x7. We can stop it if we really need it, of course, but if the stop is long then we'll have undesirable side effects. The server has v9.2 installed: postgres=# select version(); version ---------------------------------------------------------------------------------------------------------------  PostgreSQL 9.2.18 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit The cluster has these databases: postgres=# select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) as size, pg_database_size(pg_database.datname) FROM pg_database order by 3 desc;            datname            |  size   | pg_database_size ------------------------------+---------+------------------  main_db                       | 332 GB  | 356418016376  db1                                | 8078 MB | 8470254712  db2                               | 3279 MB | 3438187640  db3                                | 2658 MB | 2786694264  db4 (maybe this can be deleted) | 321 MB  | 336548984  db5                                | 175 MB  | 183596152  db6                               | 10 MB   | 10974328  db7                             | 6493 kB | 6648952  postgres                     | 6493 kB | 6648952  template1                    | 6493 kB | 6648952  template0                    | 6377 kB | 6529540 We are going to free some space in main_db moving old data to another server (~90GB) and changing the app that uses it but this db grows quite fast and we'll be probably in the same size in a few months. I know that using --link would make pg_upgrade much faster but by now we're not using it because we want to have the old version available just in case newer one gives us any problem. I've read about replicas to make upgrade with almost no downtime, but they look like a bit complex to get them running (and I'm not sure if we have a server to be used as slave). I'm familiar with them in Sql Server and Oracle, but I've been working with Postgres for a few months only and I'm still "learning". Two options I've mentioned are the only options that we have to pg_upgrade fast or is there any other option? Regards, Ekaterina