Thank you everyone. Between these answers and my own testing I'm feeling much more confident. David Barron Zencos Consulting LLC 919-995-2356 (Mobile) -----Original Message----- From: Laurenz Albe <laurenz.albe@xxxxxxxxxxx> Sent: Friday, February 9, 2024 1:23 PM To: David Barron <david.barron@xxxxxxxxxx>; pgsql-admin@xxxxxxxxxxxxxxxxxxxx Subject: Re: upgrade questions EXTERNAL SENDER On Fri, 2024-02-09 at 17:00 +0000, David Barron wrote: > I’m going to be doing an upgrade from version 11 to version 15 sometime in the near future. > I’m a bit nervous about it and I’m 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’m 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. That’s where the pg_hba.conf and postgresql.conf are located. > Third, 3 tablespaces were created, /pgdata-staging, /pgdata-prod and /pgdata-temp. > I’ve 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: > > Download and install the postgresql-15 package, of course. > Stop the running server (systemctl stop postgresql-11) > > As the postgres user run these two commands > > /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 that 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 > start 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? I ask > because I don’t see any references to tablespaces in the doc for pg_upgrade. 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 the files for the old cluster once you are satisfied that you won't fall back to the old server. > Is there a calculation I can do to determine the amount of disk space > the upgrade is going to take? I know the total amount will depend on > the size of the database, but is it going to require double the space > currently being used or some smaller amount. Disk space is somewhat limited, so that’s a concern. Since the data files are copied as they are, the new cluster will occupy pretty 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 be 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
