pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedupgrade questions
10+ messages / 7 participants
[nested] [flat]
* upgrade questions
@ 2024-02-09 17:00 David Barron <david.barron@zencos.com>
2024-02-09 17:07 ` Re: upgrade questions Scott Ribe <scott_ribe@elevated-dev.com>
2024-02-09 17:12 ` RE: upgrade questions Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2024-02-09 18:23 ` Re: upgrade questions Laurenz Albe <laurenz.albe@cybertec.at>
2024-02-09 19:49 ` Re: upgrade questions Ed Sabol <edwardjsabol@gmail.com>
0 siblings, 4 replies; 10+ messages in thread
From: David Barron @ 2024-02-09 17:00 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: upgrade questions
2024-02-09 17:00 upgrade questions David Barron <david.barron@zencos.com>
@ 2024-02-09 17:07 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-02-09 17:41 ` Re: upgrade questions Holger Jakobs <holger@jakobs.com>
2024-02-09 17:43 ` Re: upgrade questions Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-09 18:09 ` RE: upgrade questions David Barron <david.barron@zencos.com>
3 siblings, 3 replies; 10+ messages in thread
From: Scott Ribe @ 2024-02-09 17:07 UTC (permalink / raw)
To: David Barron <david.barron@zencos.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> 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.)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: upgrade questions
2024-02-09 17:00 upgrade questions David Barron <david.barron@zencos.com>
2024-02-09 17:07 ` Re: upgrade questions Scott Ribe <scott_ribe@elevated-dev.com>
@ 2024-02-09 17:41 ` Holger Jakobs <holger@jakobs.com>
2 siblings, 0 replies; 10+ messages in thread
From: Holger Jakobs @ 2024-02-09 17:41 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Am 09.02.24 um 18:07 schrieb Scott Ribe:
>> 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
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../../f57787ec-8e35-cc65-64a6-8b5fb2ce616b@jakobs.com/3-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: upgrade questions
2024-02-09 17:00 upgrade questions David Barron <david.barron@zencos.com>
2024-02-09 17:07 ` Re: upgrade questions Scott Ribe <scott_ribe@elevated-dev.com>
@ 2024-02-09 17:43 ` Ron Johnson <ronljohnsonjr@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Ron Johnson @ 2024-02-09 17:43 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Fri, Feb 9, 2024 at 12:08 PM Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> > 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.)
>
Alternatively, remove all default values from your PG11 postgresql.conf
file. Then, when you copy it to the PG15 directory, you'll automatically
get any default config values that have changed since PG 11.
And your postgresql.conf file will be much smaller.
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: upgrade questions
2024-02-09 17:00 upgrade questions David Barron <david.barron@zencos.com>
2024-02-09 17:07 ` Re: upgrade questions Scott Ribe <scott_ribe@elevated-dev.com>
@ 2024-02-09 18:09 ` David Barron <david.barron@zencos.com>
2 siblings, 0 replies; 10+ messages in thread
From: David Barron @ 2024-02-09 18:09 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
-----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 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.)
That's a good point I had not considered.
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: upgrade questions
2024-02-09 17:00 upgrade questions David Barron <david.barron@zencos.com>
@ 2024-02-09 17:12 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2024-02-09 18:10 ` RE: upgrade questions David Barron <david.barron@zencos.com>
3 siblings, 1 reply; 10+ messages in thread
From: Wetmore, Matthew (CTR) @ 2024-02-09 17:12 UTC (permalink / raw)
To: David Barron <david.barron@zencos.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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)
From: David Barron <david.barron@zencos.com>
Sent: Friday, February 9, 2024 9:01 AM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] upgrade questions
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: upgrade questions
2024-02-09 17:00 upgrade questions David Barron <david.barron@zencos.com>
2024-02-09 17:12 ` RE: upgrade questions Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
@ 2024-02-09 18:10 ` David Barron <david.barron@zencos.com>
0 siblings, 0 replies; 10+ messages in thread
From: David Barron @ 2024-02-09 18:10 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
From: Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
Sent: Friday, February 9, 2024 12:12 PM
To: David Barron <david.barron@zencos.com>; pgsql-admin@lists.postgresql.org
Subject: RE: upgrade questions
EXTERNAL SENDER
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)
Yes, I had assumed I would be doing backups.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: upgrade questions
2024-02-09 17:00 upgrade questions David Barron <david.barron@zencos.com>
@ 2024-02-09 18:23 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-02-09 19:01 ` RE: upgrade questions David Barron <david.barron@zencos.com>
3 siblings, 1 reply; 10+ messages in thread
From: Laurenz Albe @ 2024-02-09 18:23 UTC (permalink / raw)
To: David Barron <david.barron@zencos.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: upgrade questions
2024-02-09 17:00 upgrade questions David Barron <david.barron@zencos.com>
2024-02-09 18:23 ` Re: upgrade questions Laurenz Albe <laurenz.albe@cybertec.at>
@ 2024-02-09 19:01 ` David Barron <david.barron@zencos.com>
0 siblings, 0 replies; 10+ messages in thread
From: David Barron @ 2024-02-09 19:01 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: upgrade questions
2024-02-09 17:00 upgrade questions David Barron <david.barron@zencos.com>
@ 2024-02-09 19:49 ` Ed Sabol <edwardjsabol@gmail.com>
3 siblings, 0 replies; 10+ messages in thread
From: Ed Sabol @ 2024-02-09 19:49 UTC (permalink / raw)
To: David Barron <david.barron@zencos.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 you have multiple CPUs or CPU cores, you can add `--jobs=##` and it can do some of the stuff in parallel. These two additions will speed up the upgrade drastically on large databases. I also like to add the `-v` switch just so that you get some 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 to trust 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 will do a dry run and let you know if you have any issues. Then you should resolve those issues beforehand such that you can run the pg_upgrade command with `--check` and not encounter any errors or warnings. That way, when it comes time to perform the 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 ownership of some things and/or install the extensions we use in the 15.x server before pg_upgrade would run correctly.
Good luck,
Ed
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2024-02-09 19:49 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-09 17:00 upgrade questions David Barron <david.barron@zencos.com>
2024-02-09 17:07 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-02-09 17:41 ` Holger Jakobs <holger@jakobs.com>
2024-02-09 17:43 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-09 18:09 ` David Barron <david.barron@zencos.com>
2024-02-09 17:12 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@express-scripts.com>
2024-02-09 18:10 ` David Barron <david.barron@zencos.com>
2024-02-09 18:23 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-02-09 19:01 ` David Barron <david.barron@zencos.com>
2024-02-09 19:49 ` Ed Sabol <edwardjsabol@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox