RE: upgrade questions

Since you are updating MAJOR versions, this is what I do before that to have ready, just in case.

(all separate files)

1.      pg_dump –s

2.      pg_dump –a

3.      Pg_dump > *.sql (so human readable)

4.      cp postgresql.conf > wherever

5.      shut down pg and tar the /data dir.

There’s more that you can do and this is a little overkill sometimes, but its easier to delete stuff you don’t need after the fact, rather than say , darn, I wish I could look at the schema.

And then there’s the RHEL layer.
Be able to go back to orig RHEL in need of full rollback.

Save off apt-get stuff (versions etc)

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.

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.

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

/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

Then start postgres 15 (systemctl enable postgresql-15 and systemctl start postgresql-15)

I’ve also seen references to copying over the pg_hba.conf and postgresql.conf files from the old data directory to the new.

Clean up and uninstall version 10.

So, here are my questions.

Are those commands all I will need to run?

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.

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.

Thanks