Обсуждение: upgrade questions

> On Feb 9, 2024, at 10:00 AM, David Barron <david.barron@zencos.com> wrote:
>
> I’ve also seen references to copying over the pg_hba.conf and postgresql.conf files from the old data directory to
thenew. 

Then you will be getting default values for the many, many parameters added to postgresql.conf between 11 & 15--and
willhave no explanatory entries documenting them. A much better suggestion is to diff the two, and apply customizations
fromthe old to the new. (Also skim release notes for any that have been mooted.) 

On Feb 9, 2024, at 10:00 AM, David Barron <david.barron@zencos.com> wrote:

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

Then you will be getting default values for the many, many parameters added to postgresql.conf between 11 & 15--and will have no explanatory entries documenting them. A much better suggestion is to diff the two, and apply customizations from the old to the new. (Also skim release notes for any that have been mooted.)

Scott is right. Copying an old postgresql.conf over a new one is a terrible idea.

There is the general advice to never make changes to postgresql.conf (except activating the include_dir instruction close to the end of the file).

include_dir = 'conf.d'            # include files ending in '.conf' from
                    # a directory, e.g., 'conf.d'

Some distributions have this only as a comment, so remove the # at the beginning of the line and don't forget to at least create the directory conf.d, because some distribution packages don't create it.

ALL changes to the configuration should go into files within this directory having names ending in .conf

If you have these, it's very easy to copy them over to a new cluster (same or newer major version) and adapt if necessary.

It's best to copy sections from the original postgresql.conf including all comments and make necessary changes only within the conf.d directory.

Regards,

Holger

-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

-----Original Message-----
From: Scott Ribe <scott_ribe@elevated-dev.com> 
Sent: Friday, February 9, 2024 12:08 PM
To: David Barron <david.barron@zencos.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: upgrade questions

EXTERNAL SENDER

> On Feb 9, 2024, at 10:00 AM, David Barron <david.barron@zencos.com> wrote:
>
> I’ve also seen references to copying over the pg_hba.conf and postgresql.conf files from the old data directory to
thenew.
 

>Then you will be getting default values for the many, many parameters added to postgresql.conf between 11 & 15--and
willhave no explanatory entries documenting them. A >much better suggestion is to diff the two, and apply
customizationsfrom the old to the new. (Also skim release notes for any that have been mooted.)
 

That's a good point I had not considered.

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

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@cybertec.at> 
Sent: Friday, February 9, 2024 1:23 PM
To: David Barron <david.barron@zencos.com>; pgsql-admin@lists.postgresql.org
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
potentialproblems.
 

> /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
newversion.
 

> 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
isnothing 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
optionof pg_upgrade to create hard links instead of copying the files.  That will use hardly any additional disk space
andbe fast as lightning.  The drawback is that you *must* delete the old cluster right away, and you cannot use it to
fallback to in case something goes wrong.
 

Yours,
Laurenz Albe

On Feb 9, 2024, at 12:00 PM, David Barron <david.barron@zencos.com> wrote:
> 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

I just went through this a few months ago myself, except I was coming from 10.23.

I'm not sure how large your database is, but, unless it's on the small side, I recommend adding the command line switch
`--link`.I think this requires that /pgdata-system/11/data and /pgdata-system/15/data be on the same volume. Also, if
youhave multiple CPUs or CPU cores, you can add `--jobs=##` and it can do some of the stuff in parallel. These two
additionswill speed up the upgrade drastically on large databases. I also like to add the `-v` switch just so that you
getsome indication of what pg_upgrade is doing. 

I recommend that you make sure your pg_hba.conf files in both /pgdata-system/11/data and /pgdata-system/15/data are set
totrust local connections from the postgres account. 

Also, before you schedule your downtime for the upgrade, run your pg_upgrade command with the `--check` argument. That
willdo a dry run and let you know if you have any issues. Then you should resolve those issues beforehand such that you
canrun the pg_upgrade command with `--check` and not encounter any errors or warnings. That way, when it comes time to
performthe upgrade to your production system, you shouldn't encounter any problems. 

I encountered issues with user roles and extensions. It's been a while, but, IIRC, I think I had to change the
ownershipof some things and/or install the extensions we use in the 15.x server before pg_upgrade would run correctly. 

Good luck,
Ed