public inbox for [email protected]  

help / color / mirror / Atom feed

From: Motog Plus <[email protected]>
To: Ron Johnson <[email protected]>
Cc: Pgsql-admin <[email protected]>
Subject: Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15
Date: Fri, 22 Nov 2024 08:24:56 +0530
Message-ID: <CAL5Gnit464uSXwMcJ3FjLBssCiew51_svOZOohz9es2s=Ms6fQ@mail.gmail.com> (raw)
In-Reply-To: <CANzqJaCihb85vYDy7UG0LT-DZBD7378600++PBEZ8_WPEUpTxQ@mail.gmail.com>
References: <CAL5GnisA0RJ=Rd5e6yw4nsCB+DtAS1=iquBKTH76MknnTsHrPg@mail.gmail.com>
	<CANzqJaA1VyGqPUQaeT9vfxNV1C3rQJSdMHa9xtzx+rJQhkY2gw@mail.gmail.com>
	<CAL5Gnitdyw7vCp4-0whmmJ-RC7Z9yQVx=xrtfQO6-Lxcfvqrqw@mail.gmail.com>
	<CANzqJaAL-vn0iwP8QGEcVn9RqLv9Uw12qG6fV0h5+AkwW1oGBQ@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAL5GniuSksjmF7i-+-hWFbV7hecXQe9C0ZOmprBuo+BPNpJ5GA@mail.gmail.com>
	<CANzqJaCihb85vYDy7UG0LT-DZBD7378600++PBEZ8_WPEUpTxQ@mail.gmail.com>

Hi Ron,

Thanks for your response l.
Logical replication i can't use because of primary key issues.
I have multiple DBs, then multiple schemas and then multiple tables,
functions etc
Will I be able to copy whole cluster including data, roles, globals
everything using pg_dump?
Also while restoring, can I restore everything, the same structure, in a
single go using pg_restore?


On Fri, Nov 22, 2024, 00:16 Ron Johnson <[email protected]> wrote:

> pg_dumpall uses a single thread to generate one big .sql file.  If your
> database(s) are small enough that generating (even a compressed) .sql file,
> copying it then importing it is Good Enough, then that's fine.
>
> Otherwise, "logical replication" or "multithreaded pg_dump" is what you
> want.
>
> On Thu, Nov 21, 2024 at 1:17 PM Motog Plus <[email protected]> wrote:
>
>> Dear Team,
>> Thank you for your valuable inputs on the PostgreSQL upgrade.
>> Given the challenges encountered with pg_upgrade in a Kubernetes
>> environment, we're considering a more traditional approach involving
>> pg_dumpall to take backup and then restore the data using psql utility.
>> Can you please advise if this approach will be fine or you see any issues
>> with it?
>>
>> A high level overview of the steps:
>>
>> 1. Backup:
>>    * Connect to the existing PostgreSQL 12 pod.
>>    * Execute pg_dumpall to create a complete database dump.
>>
>>  2. New Deployment:
>>    * Create a new PostgreSQL 16 pod.
>>    I think no need to use initidb as it will be autoinitialized .
>>
>>  3. Restore:
>>    * Transfer the backup file to the new pod.
>>    * Use psql utility to restore the database from the dump.
>>
>>  4. Verification:
>>    * Thoroughly test the restored database to ensure data integrity and
>> functionality.
>>
>>  5. Cutover:
>>    * Once verification is complete, switch over traffic to the new
>> PostgreSQL 16 pod.
>>    * Delete the old PostgreSQL 12 pod.
>>
>> Best Regards,
>> Ramzy
>>
>> On Wed, Nov 20, 2024, 02:47 Scott Ribe <[email protected]>
>> wrote:
>>
>>> > On Nov 19, 2024, at 1:40 PM, Kris Deugau <[email protected]> wrote:
>>> >
>>> > I stand corrected.  I hadn't read the docs on pg_upgrade for quite a
>>> while, but after reading the last section in
>>> https://www.postgresql.org/docs/current/pgupgrade.html:
>>> >
>>> > "If you did not start the new cluster, the old cluster was unmodified
>>> except that, when linking started, a .old suffix was appended to
>>> $PGDATA/global/pg_control. To reuse the old cluster, remove the .old suffix
>>> from $PGDATA/global/pg_control; you can then restart the old cluster."
>>> >
>>> > I see what you mean.
>>> >
>>>
>>> There's nothing wrong per se about taking the snapshot before, I was
>>> just saving the potential time of re-running pg_upgrade. Heck, take a
>>> snapshot before *and* after ;-)
>>>
>>>
>>>
>>>
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>

view thread (14+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15
  In-Reply-To: <CAL5Gnit464uSXwMcJ3FjLBssCiew51_svOZOohz9es2s=Ms6fQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox