Re: Postgres upgradation

I don't understand the obsession with pg_dumpall. Sure, you use
"pg_dumpall --global" to get roles, tablespace definitions, etc, but
pg_dump/pg_restore is so much faster than pg_dumpall|pgsql for anything
beyond the trivial.

For those multiple databases... run pg_dump multiple times.

export PGHOST=oldserver
pg_dumpall -gvf globals.sql 2> /dev/null
DbList=`psql -AXtc "select datname from pg_database
where datistemplate=false and datname <> 'postgres'
order by datname ;"`
cd $BackupDir
for DB in $DbList;
do
date +"%n%F %T Backup of $DB started to $BackupDir with compression
level ${ZLvl}%n."
pg_dump -j ${Threads} -Z${ZLvl} -v -C -Fd --file=$DB $DB 2>
${DB}_pgdump.log
done

On Tue, Dec 17, 2024 at 9:52 AM Motog Plus <mplus7535(at)gmail(dot)com> wrote:

> Hi Ron,
>
> Is it advisable to use pg_dumpall to take backup and psql utility to
> restore so that along with data(multiple DBs) roles, tablespaces if any are
> also copied and then restored.
>
> On Tue, Dec 17, 2024, 20:11 Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:
>
>> On Tue, Dec 17, 2024 at 5:21 AM Narendran .j <jnarendran3(at)gmail(dot)com>
>> wrote:
>>
>>> Hello All,
>>>
>>> In our environment, PostgreSQL is currently running on lower versions,
>>> ranging from 9 to 11.
>>>
>>
>> PG 9.x is presumably running on an EOL version of the OS, meaning you'll
>> migrate to a current version of the OS. That means you'll use
>> pg_dump/pg_restore OR Logical Replication, instead of pg_upgrade.
>>
>> You should probably do the same on the server running PG 11.
>>
>>
>>> We are planning to upgrade to the latest version, and I have a few
>>> questions regarding the process.
>>>
>>> What are the key considerations or checks we need to perform before the
>>> upgrade?
>>>
>>> Can we directly upgrade from version 9 to 15, or is a step-by-step
>>> upgrade required?
>>>
>>
>> Read https://www.postgresql.org/docs/current/app-pgdump.html and
>> https://www.postgresql.org/docs/current/app-pgrestore.html
>>
>>
>>
>>
>>> How should we handle extensions during the upgrade process?
>>>
>>
>> Depends on the extension, and how you install Postgresql. Since we
>> install from RPM packages, it was as simple as installing the latest
>> versions of those packages, and then running pg_restore.
>>
>>
>>> What challenges or issues should we anticipate during or after the
>>> upgrade?
>>>
>>
>> You might need to update db drivers like JDBC at the same time.
>>
>>
>>> If anyone with experience in upgrading Postgres, please share your
>>> guidance.
>>>
>>
>> Since pg_dump can be performed online, test the process beforehand. I'd
>> bet that your app servers are also running on old servers, too. Upgrade
>> them, and you'll have a perfect chance to test the system beforehand.
>>
>> The only hiccup we faced when moving from PG 9.6 to 14 was that the
>> updated JDBC drivers on the application servers choked on some strings with
>> quotes in them.
>>
>> YMMV.
>>
>> --
>> Death to <Redacted>, and butter sauce.
>> Don't boil me, I'm still alive.
>> <Redacted> lobster!
>>
>

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!