Message-ID: <63f7bffb3c3576b606aae56c736c781640f990ec.camel@cybertec.at>
Subject: Re: upgrade questions
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: David Barron <david.barron@zencos.com>,
	"pgsql-admin@lists.postgresql.org"
	 <pgsql-admin@lists.postgresql.org>
Date: Fri, 09 Feb 2024 19:23:08 +0100
In-Reply-To: 
 <PH0PR22MB33529D6B6891D52EAF8706FCE94B2@PH0PR22MB3352.namprd22.prod.outlook.com>
References: 
	<PH0PR22MB33529D6B6891D52EAF8706FCE94B2@PH0PR22MB3352.namprd22.prod.outlook.com>
Autocrypt: addr=laurenz.albe@cybertec.at; prefer-encrypt=mutual;
 keydata=mQINBGGDwAQBEADgbWy5cKXQld3N2mF+DFyiNFbi2oBl2T+XgxpPF8wTRw2D/u4bBKXP0SYSE/lA86jIVNWWU0gf1KODIkVvgJm2w4vH2VBV1b7ddVViGl1Iu+9zaRnv9wulhnH42KefepXnoean6UT1EzLM0opF/Ik0j+40TxdRtobkBprkQUyHDXWlHc2ffPs3SipyFEP9AVLf7ejRC46CXWDnsqjOBSMEW8Z4HiK/8RrPZBsKLts8dJxKF4pygOdJb0CWk8k/X1jbcfdxo+zOLjOMvJcSJ2pFdJmQHU+JufB3rePziqQ2S9Ur6sccr9XnTC1GVBWN4Lf5VHq+vf+bFJjVwg+2hrySZnAVfcOrxoqFLErr7ug1zN2nM1kcpgA4VWn4gxlJtYNYYq+9WxX5dtvnNANlG3ZCrRKQzl8lxtzoF6Zo7LUhEqPaHDwn7Rvs+IdbOn41lF5UDTJGqmC4gS/bZydW2Fy3YWm4aSaN9fgFf8D+PVkrlKAZB7gBLz1TyHjbcRf85cYF+GKKrDld5SzMB/V60VX3oP/Eo8ikFpyWaqiz1f9X7MBot3/PjJkY+wDzp3nmb19QEcOBuQiSQ4xds2r0HewbuHTAR68u8jNNMGmpm2j4x+g09Jd/WQDjqlTBZ/jEltH41fYCCPWMfljXTOOXu2eLNGdfi7ETZogtwjM9oTtSPQARAQABtCdMYXVyZW56IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5hdD6JAk4EEwEIADgWIQR0CqhbZGGABqoaSbdi8bhXA2EdmAUCYYPABAIbAwULCQgHAgYVCgkICwIEFgIDAQIeAQIXgAAKCRBi8bhXA2EdmM/6EADK232JCwmBzhlj8h7U9CjG6kx0JHP3uJGv+XfsHtHAlmY/RCwF1BHMEsRlk
 bT5UrLvJ2jb99bA9QARzhFaxzyn0F/BUKzuIjRGNs/n6d5dNUFA0kOt8sX+TacmC
 GEyjEBCrVCm4ranBiUyePn9NhHNWnaex7pJyqvMLLdwW9BEMJx0Fqo+DN8ukbXmYRsmhEtd3ue+x/luYmOmJnaGtzInaY5aOJYbW9XqoRIZkZvOCgbi1FfvNmoqWa+3oVxTOgw9RafjJDyW0lTHzKGjbGI5ofMU98l+/hKJFYJqWUF6VpFJY5YIcN/1lf4ZICMwDl+MPIVo/tpq8L10seJL28nLlvw3K+cI+TVW8IW/qL/LyVoDofI3USeOORuYmhpWRhik8JXX6xf3v6GrRilJIPWNFIJbxm1ZblQiQnOw3IOW7T+8nAmPin1HKqM3VrOrJQ2VtShsefNBibNAsr1oFaqcDBkn3yGG8i6CTW+FyO4PZ+/EwNxMVgktxbYdy5AT1/lpXr5tB+phhLIyVfiBvrWs5EThxYMQ/L8Y85c3GMsAy1l/x4h3jqySIYy3SCU9+jc5UVuNnXljbvkEzJ+NLWJ6C1rACFWrMszgPdh5tCrlRY9PpmYll4JbCgb8BtxEIUmR+xr50/ZElEK5iml7Q00KUekCcDt+36PsyGFTXBzNOrkCDQRhg8AEARAAzOZ2tLHlI4rrhG411h6cdCFjBZxuljaFCxFyHn3m6wbGLqwBUWC5k8UrRqjHMz88KcTSaNO7XGAmCqPdWd2SeflPZRnNTbjsVpw7mLdffsBm4JX7kki2Pvk5h0NtYeidXT1PSpc2ri4DutYXuT9uD8RAm1wUDCE5HQNUihT/WH6opt+hskHW21uHao0+y822tG0QQcGMqdQR5Vxdxj89wiEPdqW+HpU/oOZIhrf2E7prduAppxixjHy/o1rcnoznnJvc8D3+YgI9O0LrBMij89dM55pRGbLovTR1oGR3U74sX774+0xmSzeIKwZfiMUz7Atlvfk5SHOsRUFPN2Ux9kaXiiBibQpHFxt7b
 lDrT4wxdLJ/XCdbPPAyl+lZtOLsaHEEZvYNyTXwZc35dVf3R4/oz20HoG6s7ct8e1
 AQygj43XAERzty9SkWgxs8+grp1PrGx6FHVSYRqBM8dS/ZR6yRVwOwJXPyaSSqfIF21DkE4j1y4n+ItSewPGoRp8K/yWCikt6qlkVkO2ASNIiX04fAbtzwVOaNn8ZMRNqyvLc1fED4sr49onE4cAIcBLjcC3KL+w9DUGRQCdziROj5H2Yl/sXGPdMciUHo/Uz2rggc+2th3bQiMhrHWSsBpUkDQp0yWewemstPpPgBL3h2fHKaX8B9oH5Qu/H1IgrOuX8AEQEAAYkCNgQYAQgAIBYhBHQKqFtkYYAGqhpJt2LxuFcDYR2YBQJhg8AEAhsMAAoJEGLxuFcDYR2YuPwQAMkpGtR80pQ1gVsONhdkqj0H2eU66efP/gO3CoyaoIcvrpKYj7C2HipVSmkt1gpByL0X4AMQ/vKuknUz3wd28Ba+G1dCfbVs/Xiusq+SmpUj5rTwmYqdSjWMuCo1R6oS5hdJMdUUJYGMT0QkVlm1KnW8jkmCTl9GzjDxOAsN9O6/6lPzaGFtk9XF+34Bry/N4HKiJkqpC4+UTd0AprPfzJ2jdT64e1F0+W88X8y1bTTgNrHwK4mDiLnlE4SKRuEm54lNhJz//ar86Or5BErzNpM6TL7lk44QS06hwsMrEdKIy8J/SYJPjfzR8tIUnKscclVpOgjKaBqC+0iFiVaRqAgfOlIEiezX6kMh5Q2FIUfqs46qWhhXjRrdKOEoStYAaikdLu5ZXr7vfb0ZaDh+ZwTQtbSMFolyOkecwI81MCdbMfT/1TqIGTOdAj5as9fAakk0jb2pXgUYQ8X1DVTR8ahSDVEaw9VTmWiSvTxvguVJ1Mb7gG4Gmh6aviDTJhfXtH4rPUNXhDLqrTH8JkJjyKROOMakIF68Hjse5vUfUxreBEOtb5r1Coa2Fe7ncJayaSE7ryrDbFqpZ
 36UMAx4ulWMyqJajLNGY0DdG8qIsR5nxRhrnK/mrCidZ8F9/D3bWAl4rjtHlsztN59
 +AnW5l0HsQcY9ntFL/zEBOaonjdJf
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.50.3 (3.50.3-1.fc39) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/63f7bffb3c3576b606aae56c736c781640f990ec.camel%40cybertec.at>
Precedence: bulk

On Fri, 2024-02-09 at 17:00 +0000, David Barron wrote:
> I=E2=80=99m going to be doing an upgrade from version 11 to version 15 so=
metime in the near future.
> I=E2=80=99m a bit nervous about it and I=E2=80=99m hoping I can get some =
questions answered before I take the big step.

You will do it on a test system first, right?
Because you have to test the application.
Also, make sure that you have a good backup.

> First, I=E2=80=99m working on RedHat Linux servers.
> Second the existing install is not using the default data directory (/var=
/lib/pgsql/11/data).
> Because of decisions that were made before I got my hands on the servers =
the data directory
> is /pgdata-system/11/data.=C2=A0 That=E2=80=99s where the pg_hba.conf and=
 postgresql.conf are located.
> Third, 3 tablespaces were created, /pgdata-staging, /pgdata-prod and /pgd=
ata-temp.
> I=E2=80=99ve got the last one setup as the location for temporary files.

No problem at all, as far as the upgrade is concerned.

> From my understanding of the docs I have to do the following:
> =C2=A0
> Download and install the postgresql-15 package, of course.
> Stop the running server (systemctl stop postgresql-11)
> =C2=A0
> As the postgres user run these two commands
> =C2=A0
> /usr/pgsql-15/bin/initdb -D /pgdata-system/15/data

You have to create the new cluster with the same encoding and locale
as the old one (check \l on the old cluster).

Run "pg_upgrade ... --check" first (can be done while the old server
is running), that will show you if there are any potential problems.

> /usr/pgsql-15/bin/pg_upgrade -b /usr/pgsql-11/bin -B /usr/pgsql-15/bin -d=
 /pgdata-system/11/data -D /pgdata-system/15/data

You also have to take care of the configuration files.
You can just copy "pg_hba.conf" and "pg_ident.conf", but you shouldn't do t=
hat
with "postgresql.conf" and "postgresql.auto.conf".
Rather, edit them side by side and manually copy modified ssettings over,
checking if the parameter still exists in the new version.

> Then start postgres 15 (systemctl enable postgresql-15 and systemctl star=
t postgresql-15)

Table statistics are not upgraded.  After starting the new server, connect
to all database you can and run ANALYZE there.  That may take a while and
will create table statistics (essential for query performance).

> So, here are my questions.
> Are those commands all I will need to run?

I made remarks inline.

> Will the tablespaces be moved over to the new data directory?=C2=A0 I ask=
 because
> I don=E2=80=99t see any references to tablespaces in the doc for pg_upgra=
de.

All tablespace directories will grow a new subdirectory for the new version=
,
and the datafiles will get copied.  There is nothing to do, except remove t=
he
files for the old cluster once you are satisfied that you won't fall back t=
o
the old server.

> Is there a =C2=A0calculation I can do to determine the amount of disk spa=
ce the upgrade
> is going to take?=C2=A0 I know the total amount will depend on the size o=
f the database,
> but is it going to require double the space currently being used or some =
smaller
> amount.=C2=A0 Disk space is somewhat limited, so that=E2=80=99s a concern=
.

Since the data files are copied as they are, the new cluster will occupy pr=
etty
much the same space as the old one.

If you want to avoid that, you can create the new cluster on the same file =
system
as the old one and use the --link option of pg_upgrade to create hard links=
 instead
of copying the files.  That will use hardly any additional disk space and b=
e fast
as lightning.  The drawback is that you *must* delete the old cluster right=
 away,
and you cannot use it to fall back to in case something goes wrong.

Yours,
Laurenz Albe