Re: Trouble Upgrading Postgres

Daniel said:

>It's plausible that, with only 4GB of RAM, the table that fails
to dump has some very large rows that can't be allocated, especially
since both the backend and pg_dump need to have it simultaneously
in memory.
> >pg_dump: The command was: COPY public.docfile (docfile_pkey,
> >docfileoriginalname, ordernumber, versionnum, docfilecontents,
>> docfilepath, docfileextension, enddatetime, endby, editnum, insby,
>> insdatetime, modby, moddatetime, active, doc_fkey) TO stdout;
>The "docfilecontents" column suggests that it might contain
large contents. If its type is bytea, it's going to be expanded
to twice its size to build the hex representation.
>You may get a sense on how big is the biggest row expressed
as text with this query:
SELECT max(length(contents.*::text)) FROM public.docfile;
>If it's big enough that it might cause the OOM issue,
try to run pg_dump remotely through an SSH tunnel [1], which you
can already do in terms of network permissions since you log in with
SSH, so pg_dump itself does not use any memory on the server.
>Also, if the machine doesn't have swap space, it might be
that just adding a few GB's of swap would make the operation
succeed.

This appears to be the case. I ran:

SELECT max(length(docfilecontents::text)) FROM docfile;
and after a very long time, got:
ERROR: invalid memory alloc request size 1636085512 SQL state: XX000

Adrian said:
>> Ok, thanks for explaining this. Here is the current value:
>> "shared_buffers""131072""8kB"
>It should be a single value something like this for the default:
>shared_buffers = 128MB

The results I pasted were from:

SELECT * FROM pg_settings

Maybe I didn't get it the right way.

The system has only 4GB of RAM. I read that a reasonable swap size is 1/4
of RAM, so I've created a swap file of 1GB.

Tom said:
>> Ok, thanks for explaining this. Here is the current value:
>> "shared_buffers" "131072" "8kB"
>Well, that's 1GB, which might be ambitious inside a VM with a hard
restriction to 4GB total RAM. Postgres can get by with a *lot* less.
>Try knocking it down to a tenth of that and see if it makes a difference

I think I also based this on a rule-of-thumb that it should be no more than
25% of RAM. Should I test pg_dump with the added VM before reducing
shared_buffers?

On Mon, Nov 5, 2018 at 10:13 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 11/5/18 7:04 AM, Charles Martin wrote:
> > Adrian said:
> >
> > >Tom was referring to this from your previous post:
> > >(postmaster) total-vm:3068900kB,
> > >where vm(VM) is Virtual Memory:
> > >
> https://stackoverflow.com/questions/18845857/what-does-anon-rss-and-total-vm-mean#22326766
> > >So what is your shared_buffers:
> > >https://www.postgresql.org/docs/10/static/runtime-config-resource.html
> > >set to?
> >
> > Ok, thanks for explaining this. Here is the current value:
> > "shared_buffers""131072""8kB"
> >
>
> It should be a single value something like this for the default:
>
> shared_buffers = 128MB
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>