Re: Guidance Needed for PostgreSQL Upgrade from 12 to 15

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 <mplus7535(at)gmail(dot)com> 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 <ronljohnsonjr(at)gmail(dot)com> 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 <mplus7535(at)gmail(dot)com> 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 <scott_ribe(at)elevated-dev(dot)com>
>>> wrote:
>>>
>>>> > On Nov 19, 2024, at 1:40 PM, Kris Deugau <kdeugau(at)vianet(dot)ca> 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!