public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: Pgsql-admin <[email protected]>
Subject: Re: Postgres upgradation
Date: Tue, 17 Dec 2024 10:07:30 -0500
Message-ID: <CANzqJaCBtsyQyg9LDks7G33G-PoXFcRV3jCtmVWXqDTmP-=2vg@mail.gmail.com> (raw)
In-Reply-To: <CAL5GnisP1cykT=hRd_vGbjyNZicr7M2gZwzSMns=_Q1MCGMtQA@mail.gmail.com>
References: <CA+-Cqf4EoAxwfqvUQiPU--zJ2Qt+wA3239XWE13gKD+PGqZa+Q@mail.gmail.com>
<CANzqJaB1KNofZTTpx7+FKiecCoeGTWzLeQ2MpJN3WGQLbkOq-g@mail.gmail.com>
<CAL5GnisP1cykT=hRd_vGbjyNZicr7M2gZwzSMns=_Q1MCGMtQA@mail.gmail.com>
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 <[email protected]> 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 <[email protected]> wrote:
>
>> On Tue, Dec 17, 2024 at 5:21 AM Narendran .j <[email protected]>
>> 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!
view thread (4+ 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: Postgres upgradation
In-Reply-To: <CANzqJaCBtsyQyg9LDks7G33G-PoXFcRV3jCtmVWXqDTmP-=2vg@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