agora inbox for [email protected]
help / color / mirror / Atom feedRe: Restore of a reference database kills the auto analyze processing.
6+ messages / 5 participants
[nested] [flat]
* Re: Restore of a reference database kills the auto analyze processing.
@ 2024-05-21 18:08 Adrian Klaver <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Adrian Klaver @ 2024-05-21 18:08 UTC (permalink / raw)
To: HORDER Philip <[email protected]>; [email protected] <[email protected]>
On 5/21/24 9:29 AM, HORDER Philip wrote:
> Classified as: {OPEN}
>
>> I am having a hard time figuring out how both of the above can be true.
>> Dropping and recreating the database would wipe out the statistics.
>
> We have multiple databases on the one Postgres server.
> The 'postgres' database contains our main application, with tables in schema 'a'.
> We have two other databases, one of which is 'lfm'.
>
> In PgAdmin, there is Databases (3), then those database names.
> As the superuser, I can connect to any of these databases.
>
> When we drop database lfm, and recreate it using pg_restore, the stats collection dies, for all databases on the server: 'postgres', 'lfm', 'lfm2'
> I haven't dropped database 'postgres'
>
> So the existing stats on tables in the 'postgres' database remain, but no further stats are collected.
From int-postgres-log-21-05-2024.txt:
2024-05-15 03:31:31.290 GMT [4556]: [3-1]
db=lfm,user=superuser,app=[unknown],client=::1 LOG: connection
authorized: user=superuser database=lfm application_name=pg_restore
That would be the lfm database being restored.
What does the log show after that as pertains to autovacuum?
>
> Phil Horder
> Database Mechanic
>
> Thales
> Land & Air Systems
>
>
> {OPEN}
> The information contained in this e-mail is confidential. It is intended only for the stated addressee(s) and access to it by any other person is unauthorised. If you are not an addressee, you must not disclose, copy, circulate or in any other way use or rely on the information contained in this e-mail. Such unauthorised use may be unlawful. If you have received this e-mail in error, please inform the originator immediately and delete it and all copies from your system.
>
> Thales UK Limited. A company registered in England and Wales. Registered Office: 350 Longwater Avenue, Green Park, Reading, Berks RG2 6GF. Registered Number: 868273
>
> Please consider the environment before printing a hard copy of this e-mail.
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 6+ messages in thread
* Postgres 12 update to new major version
@ 2026-06-05 12:11 Boris Dovčík <[email protected]>
2026-06-05 20:08 ` Re: Postgres 12 update to new major version Bruce Momjian <[email protected]>
2026-06-05 20:30 ` Re: Postgres 12 update to new major version Adrian Klaver <[email protected]>
2026-06-05 21:59 ` Re: Postgres 12 update to new major version Laurenz Albe <[email protected]>
0 siblings, 3 replies; 6+ messages in thread
From: Boris Dovčík @ 2026-06-05 12:11 UTC (permalink / raw)
To: [email protected]
Hello all,
I have a question regarding the update of the production database (multiple
TB of data) from version 12 to higher major version. We would like to jump
to 15 or 16. DB is installed on a Linux server with one replica on another,
it is not on cloud.
First question is should we perform updates one by one? 12 -> 13 -> 14 etc.
or is it viable to jump to 16 right away? (We have few functions but
usually its plain tables)
Second we are contemplating options for the update since we are limited
with downtime. We could get 4 hours of downtime for the whole update
(either via pg_update or dumps). We are not sure how to estimate the time
for direct update or dumps. What would be the recommended way for it?
One option we see could be logical replication to the new major version on
a new machine, but we received feedback that in our setup it might not be
recommendable. Does it make sense to try logical replica? What would be the
downsides of it?
Best regards,
Boris Dovčík
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres 12 update to new major version
2026-06-05 12:11 Postgres 12 update to new major version Boris Dovčík <[email protected]>
@ 2026-06-05 20:08 ` Bruce Momjian <[email protected]>
2 siblings, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 2026-06-05 20:08 UTC (permalink / raw)
To: Boris Dovčík <[email protected]>; +Cc: [email protected]
On Fri, Jun 5, 2026 at 02:11:12PM +0200, Boris Dovčík wrote:
> Hello all,
>
> I have a question regarding the update of the production database (multiple TB
> of data) from version 12 to higher major version. We would like to jump to 15
> or 16. DB is installed on a Linux server with one replica on another, it is not
> on cloud.
>
> First question is should we perform updates one by one? 12 -> 13 -> 14 etc. or
> is it viable to jump to 16 right away? (We have few functions but usually its
> plain tables)
>
> Second we are contemplating options for the update since we are limited with
> downtime. We could get 4 hours of downtime for the whole update (either via
> pg_update or dumps). We are not sure how to estimate the time for direct update
> or dumps. What would be the recommended way for it?
>
> One option we see could be logical replication to the new major version on a
> new machine, but we received feedback that in our setup it might not be
> recommendable. Does it make sense to try logical replica? What would be the
> downsides of it?
I think you want this web page:
https://www.postgresql.org/support/versioning/
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres 12 update to new major version
2026-06-05 12:11 Postgres 12 update to new major version Boris Dovčík <[email protected]>
@ 2026-06-05 20:30 ` Adrian Klaver <[email protected]>
2026-06-05 22:38 ` Re: Postgres 12 update to new major version Jeff Ross <[email protected]>
2 siblings, 1 reply; 6+ messages in thread
From: Adrian Klaver @ 2026-06-05 20:30 UTC (permalink / raw)
To: Boris Dovčík <[email protected]>; [email protected]
On 6/5/26 5:11 AM, Boris Dovčík wrote:
> Hello all,
>
> I have a question regarding the update of the production database
> (multiple TB of data) from version 12 to higher major version. We would
How many TB?
> like to jump to 15 or 16. DB is installed on a Linux server with one
> replica on another, it is not on cloud.
What replication is being done now?
Where are the servers relative to each other network distance wise?
>
> First question is should we perform updates one by one? 12 -> 13 -> 14
> etc. or is it viable to jump to 16 right away? (We have few functions
> but usually its plain tables)
>
> Second we are contemplating options for the update since we are limited
> with downtime. We could get 4 hours of downtime for the whole update
> (either via pg_update or dumps). We are not sure how to estimate the
Did you mean pg_upgrade?
> time for direct update or dumps. What would be the recommended way for it?
>
> One option we see could be logical replication to the new major version
> on a new machine, but we received feedback that in our setup it might
What was the feedback that indicated it was not advisable?
> not be recommendable. Does it make sense to try logical replica? What
> would be the downsides of it?
>
> Best regards,
> Boris Dovčík
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres 12 update to new major version
2026-06-05 12:11 Postgres 12 update to new major version Boris Dovčík <[email protected]>
2026-06-05 20:30 ` Re: Postgres 12 update to new major version Adrian Klaver <[email protected]>
@ 2026-06-05 22:38 ` Jeff Ross <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Jeff Ross @ 2026-06-05 22:38 UTC (permalink / raw)
To: [email protected]
On 6/5/26 2:30 PM, Adrian Klaver wrote:
> On 6/5/26 5:11 AM, Boris Dovčík wrote:
>> Hello all,
>>
>> I have a question regarding the update of the production database
>> (multiple TB of data) from version 12 to higher major version. We would
>
> How many TB?
>
>> like to jump to 15 or 16. DB is installed on a Linux server with one
>> replica on another, it is not on cloud.
>
> What replication is being done now?
>
> Where are the servers relative to each other network distance wise?
>
>>
>> First question is should we perform updates one by one? 12 -> 13 ->
>> 14 etc. or is it viable to jump to 16 right away? (We have few
>> functions but usually its plain tables)
>>
>> Second we are contemplating options for the update since we are
>> limited with downtime. We could get 4 hours of downtime for the whole
>> update (either via pg_update or dumps). We are not sure how to
>> estimate the
>
> Did you mean pg_upgrade?
>
>> time for direct update or dumps. What would be the recommended way
>> for it?
>>
>> One option we see could be logical replication to the new major
>> version on a new machine, but we received feedback that in our setup
>> it might
>
> What was the feedback that indicated it was not advisable?
>
>> not be recommendable. Does it make sense to try logical replica? What
>> would be the downsides of it?
>>
>> Best regards,
>> Boris Dovčík
I've got just a little over 1 TB in prod with a logical replica in the
same network.
We upgraded from 10 to 16 in one jump by first setting up the logical
replica to run 16 and getting replication up and in sync.
When it came time to do the upgrade from 10 to 16 it was just a few
simple steps, and with minimal (less than 5 minutes downtime):
Stop the pgbouncers, which stopped all network traffic
Set the sequences on the 16 replica
Updated pgbouncer configs to point to the 16 server
Turned the pgbouncers back on
Setting the sequences was the most time consuming part but it was really
not long.
Logical replication did the trick for us.
I see that in 19 we won't even have to set the sequences because they
are also replicated! Hooray!
One of the advantages to the logical replication route is that we could
take advantage of testing a ton of our code against 16 on the replica
*before* we ever flipped the switch.
Jeff
P.S. I can't wait to hear how I did it all wrong or could have done it
better but this absolutely worked ;-)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres 12 update to new major version
2026-06-05 12:11 Postgres 12 update to new major version Boris Dovčík <[email protected]>
@ 2026-06-05 21:59 ` Laurenz Albe <[email protected]>
2 siblings, 0 replies; 6+ messages in thread
From: Laurenz Albe @ 2026-06-05 21:59 UTC (permalink / raw)
To: Boris Dovčík <[email protected]>; [email protected]
On Fri, 2026-06-05 at 14:11 +0200, Boris Dovčík wrote:
> I have a question regarding the update of the production database (multiple TB of data)
> from version 12 to higher major version. We would like to jump to 15 or 16. DB is installed
> on a Linux server with one replica on another, it is not on cloud.
>
> First question is should we perform updates one by one? 12 -> 13 -> 14 etc. or is it
> viable to jump to 16 right away? (We have few functions but usually its plain tables)
You can jump over versions, but you should not upgrade to an old release like v15 or
v16. Go to v18 or better. It looks like you are not too eager to upgrade frequently,
otherwise you would not be on v12 today. So I recommend that you put the time when
the next upgrade will be necessary as far into the future as possible.
> Second we are contemplating options for the update since we are limited with downtime.
> We could get 4 hours of downtime for the whole update (either via pg_update or dumps).
> We are not sure how to estimate the time for direct update or dumps. What would be the
> recommended way for it?
You should use pg_upgrade --link. Then the upgrade can be a matter of minutes, unless
you have lots and lots of tables, functions or large objects.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-06-05 22:38 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-21 18:08 Re: Restore of a reference database kills the auto analyze processing. Adrian Klaver <[email protected]>
2026-06-05 12:11 Postgres 12 update to new major version Boris Dovčík <[email protected]>
2026-06-05 20:08 ` Re: Postgres 12 update to new major version Bruce Momjian <[email protected]>
2026-06-05 20:30 ` Re: Postgres 12 update to new major version Adrian Klaver <[email protected]>
2026-06-05 22:38 ` Re: Postgres 12 update to new major version Jeff Ross <[email protected]>
2026-06-05 21:59 ` Re: Postgres 12 update to new major version Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox