Re: pg_wal fills up on big update query

flyway is an excellent tool, I use it too.  Just make a series of
migrations.

On 8/9/19 9:03 AM, Daniel Fink (PDF) wrote:
>
> Hi Rob,
>
> Thanks, I will try.
>
> It’s a bit of a  bummer though, because I just started to use flywaydb
> to manage migrations, and it wraps all migrations into a single
> transaction.
>
> So I have to do this outside of the tool.
>
> Best Regards,
>
> Daniel
>
> *From:* Rob Sargent [mailto:robjsargent(at)gmail(dot)com
> <mailto:robjsargent(at)gmail(dot)com>]
> *Sent:* Wednesday, August 7, 2019 4:22 PM
> *To:* Daniel Fink (PDF) <daniel(dot)fink(at)pdf(dot)com <mailto:daniel(dot)fink(at)pdf(dot)com>>
> *Cc:* pgsql-general(at)lists(dot)postgresql(dot)org
> <mailto:pgsql-general(at)lists(dot)postgresql(dot)org>
> *Subject:* Re: pg_wal fills up on big update query
>
>
> On Aug 7, 2019, at 7:34 AM, Daniel Fink (PDF) <daniel(dot)fink(at)pdf(dot)com
> <mailto:daniel(dot)fink(at)pdf(dot)com>> wrote:
>
> Hi all,
>
> I have a migration where I
>
> ·Add a new nullable column to a table
>
> ·update almost every row in this big table (8 million rows) from
> another table where I set this new column
>
> I have also a replication setup running.
>
> The database has a size of around 20GB.
>
> While the migration is running, it more than doubles is size and
> fills up all space.
>
> Then the migration fails and is rolled back.
>
> What is the best way of keeping this from happening?
>
> My current idea is to lock both tables completely from access (the
> queried and the updated one) so that postgresql does not have to
> ensure isolation for concurrent queries by keeping a copy of each row.
>
> Is my thinking here correct?
>
> Thanks in advance and Best Regards,
>
> Do the update in small chunks
>
>
> /This message may contain confidential and privileged information. If
> it has been sent to you in error, please reply to advise the sender of
> the error and then immediately permanently delete it and all
> attachments to it from your systems. If you are not the intended
> recipient, do not read, copy, disclose or otherwise use this message
> or any attachments to it. The sender disclaims any liability for such
> unauthorized use. PLEASE NOTE that all incoming e-mails sent to PDF
> e-mail accounts will be archived and may be scanned by us and/or by
> external service providers to detect and prevent threats to our
> systems, investigate illegal or inappropriate behavior, and/or
> eliminate unsolicited promotional e-mails (“spam”). If you have any
> concerns about this process, please contact us at
> //legal(dot)department(at)pdf(dot)com/ <mailto:legal(dot)department(at)pdf(dot)com>/./