From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: reindexing db
Date: Tue, 8 Dec 2020 01:42:44 -0600
Message-ID: <98c19d44-8d4c-f608-5432-eef048965552@gmail.com> (raw)
In-Reply-To: <A54CA469-6EAA-40AD-AD37-7E877268786A@gmail.com>
References: <292966DF-135C-4483-81A9-5027FC3C8A82@elevated-dev.com>
<f477709e-6371-1ba3-84df-37e5df8e5d83@gmail.com>
<214E5DBC-1F6F-49C1-8B83-9A2EB33E969A@gmail.com>
<39a8cdb5-bfbb-9ae8-5867-c4234373277c@gmail.com>
<A54CA469-6EAA-40AD-AD37-7E877268786A@gmail.com>
On 12/8/20 1:21 AM, Paul Förster wrote:
> Hi Ron,
>
>> On 08. Dec, 2020, at 07:59, Ron <ronljohnsonjr@gmail.com> wrote:
>>
>> Probably a habit from my VAX/VMS days, but I prefer to enumerate every table, not only for tracking but so that if the command fails 90% of the way through, I can skip over the the completed portions.
> doing this IMHO defeats the purpose of concurrency because you'd have to issue reindex statements sequentially. Also, but I definitely can't say for sure, reindexdb should skip and report indexes which it has a problem with and then continue to do the remaining indexes. But as I said, I may be wrong with this.
The CONCURRENTLY clause is about rebuilding *an index* online instead of
blocking all other access to the table, not about reindexing multiple tables
in parallel.
Thus, it's perfectly valid to write something like
DB=test
while read line;
do
psql $DB "REINDEX TABLE $line CONCURRENTLY;"
done < list_of_tables.txt
You might also be able to play with GNU Parallel and concurrently run
multiple "REINDEX TABLE $line CONCURRENTLY;" statements.
--
Angular momentum makes the world go 'round.
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: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: reindexing db
In-Reply-To: <98c19d44-8d4c-f608-5432-eef048965552@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox