public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: Pgsql-admin <[email protected]>
Subject: Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15
Date: Thu, 21 Nov 2024 22:46:14 -0500
Message-ID: <CANzqJaAnQ0Xotcw0XzgD36V4mGZjOxuE7nM8cBSSYhFP6VsuXw@mail.gmail.com> (raw)
In-Reply-To: <CAL5Gnit464uSXwMcJ3FjLBssCiew51_svOZOohz9es2s=Ms6fQ@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>
<CAL5Gnit464uSXwMcJ3FjLBssCiew51_svOZOohz9es2s=Ms6fQ@mail.gmail.com>
pg_dump and pg_restore only work on a single database, but they contain
*EVERYTHING* that database needs. Thus, you'll have to do a
pg_dump/pg_restore pair
If tasked with migrating multiple databases, and LR was not an option, I
would:
On the new server,
1. "pg_dumpall -h $OldServer --globals > globals.sql" to get the roles,
etc. Will need to remove the "postgres" role.
2. "pg_dump -h $OldServer -j$Threads -Fd $DB ..." for each database.
3. psql postgres -af globals.sql
4. "pg_restore -v -j $Threads --exit-on-error -cC -Fd --no-tablespaces -d
postgres $DB" for each database.
5. vacuumdb --analyze --jobs=$(nproc) -d $DB
Do all the pg_dump and then all the pg_restore commands, or alternate
pg_dump/pg_restore pairs, one database at a time. That's up to you.
I would set these config params before each Step4:
pg_ctl restart -wt9999 -mfast \
-o "-c hba_file=$PGDATA/pg_hba_maintmode.conf" \
-o "-c fsync=off" \
-o "-c log_statement=none" \
-o "-c log_temp_files=100kB" \
-o "-c log_checkpoints=on" \
-o "-c log_min_duration_statement=120000" \
-o "-c shared_buffers=${SharedBuffs}GB" \
-o "-c maintenance_work_mem=${MaintMem}GB" \
-o "-c synchronous_commit=off" \
-o "-c archive_mode=off" \
-o "-c full_page_writes=off" \
-o "-c checkpoint_timeout=${CheckPoint}min" \
-o "-c max_wal_size=${MaxWalSize}GB" \
-o "-c wal_level=minimal" \
-o "-c max_wal_senders=0" \
-o "-c wal_buffers=${WalBuffs}MB" \
-o "-c autovacuum=off"
And then this after Step 5:
pg_ctl -wt9999 stop -mfast && pg_ctl -wt9999 start
Be careful with what you set ${SharedBuffs} and ${MaintMem} to: with lots
of threads, it's easy to run out of memory, and then the oom will kill the
pg_restore.
On Thu, Nov 21, 2024 at 9:55 PM Motog Plus <[email protected]> wrote:
> 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)
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]
Subject: Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15
In-Reply-To: <CANzqJaAnQ0Xotcw0XzgD36V4mGZjOxuE7nM8cBSSYhFP6VsuXw@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