Re: BUG #15660: pg_dump memory leaks when dumping LOBs

Hello! Thanks for quick follow-up.
But I still do not understand why pg_dump consumes so much RAM when
exporting LOBS. Here's my point.
I've dropped all the databases, so the only one left is testdbl, with the
only table 'image' which I mentioned earlier:
All the LOBS inside the table were empty. As I already mentioned, I used
following to fill the table:

DO $$
DECLARE
i INTEGER;
BEGIN
FOR i IN 1 .. 5000000
LOOP
INSERT INTO image (name, raster)
VALUES ('emptyfile', lo_import('/etc/motd'));
END LOOP;
END $$;

where /etc/motd is empty file.

testdbl=# \l+
List of
databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=Tc/postgres +| 7003 kB | pg_default | default administrative
connection database
| | | | |
postgres=CTc/postgres+| | |
| | | | |
pgpool_chk=c/postgres | | |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
+| 7841 kB | pg_default | unmodifiable empty database
| | | | |
postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
+| 6707 kB | pg_default | default template for new databases
| | | | |
postgres=CTc/postgres | | |
testdbl | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
| 536 MB | pg_default |
(4 rows)

testdbl=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-------+-------+----------+--------+-------------
public | image | table | postgres | 211 MB |
(1 row)

testdbl=# select count(*) from image;
count
---------
5000000

testdbl=# select count(*) from pg_largeobject;
count
-------
0
(1 row)

testdbl=# select count(*) from pg_largeobject_metadata;
count
---------
5000000
(1 row)

Overall size of the whole instance's ./base directory was following:

[postgres(at)host data]$ du -sh base/
676M base/

So I assume that the top RAM to be consumed would be under 676MB. But here
what I've got:

Before starting pg_dump:

[postgres(at)host2 ~]$ free -m
total used free shared buff/cache
available
Mem: 9665 143 9380 8 140
9301
Swap: 955 0 955

When "pg_dump: reading large objects" phase finished:

[root(at)host2 ~]# free -m
total used free shared buff/cache
available
Mem: 9665 2203 7320 8 141
7241
Swap: 955 0 955

During "executing BLOBs" phase:

[root(at)host2 ~]# free -m
total used free shared buff/cache
available
Mem: 9665 3982 5327 8 355
5428
Swap: 955 0 955

Peak during "pg_dump: saving large objects" phase:

[postgres(at)host2 ~]$ free -m
total used free shared buff/cache
available
Mem: 9665 4007 4257 8 1400
5375
Swap: 955 0 955

So peak RAM usage was ~4GB and the whole dump procedure took nearly 2 hours
to complete for ~600mb sized database.

An usual database with one table (without lobs) 10mil rows, ~1.5GB sized
takes 20 seconds be dumped and peak RAM usage for it ~20MB.

So I'm still concerned with pg_dump behaviour and it doesn't seem to be
expected.

Best Regards,
Ilya

чт, 28 февр. 2019 г. в 11:00, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>:

>
>
> On Wed, Feb 27, 2019 at 10:58 PM PG Bug reporting form <
> noreply(at)postgresql(dot)org> wrote:
>
>>
>> Hello,
>> One of our customers faced an unexpected behaviour when using pg_dump to
>> export one of DBs - pg_dump consumed all the server memory and got
>> terminated with following error:
>>
>> ....
>> pg_dump: reading row security enabled for table "public.databasechangelog"
>> pg_dump: reading policies for table "public.databasechangelog"
>> pg_dump: reading row security enabled for table "public.OperationAudit"
>> pg_dump: reading policies for table "public.OperationAudit"
>> pg_dump: reading large objects
>> pg_dump: [archiver (db)] query failed: out of memory for query result
>> pg_dump: [archiver (db)] query was: SELECT oid, (SELECT rolname FROM
>> pg_catalog.pg_roles WHERE oid = lomowner) AS rolname, lomacl FROM
>> pg_largeobject_metadata
>> ....
>> Looking through the plan gave following:
>
>
> Thanks for reporting the problem.
>
> pg_dump process collects all the database objects first (tables, indexes
> and etc)
> and then it write them into the file/archive.
>
> In your scenario, there are many large objects that are present which
> leads to
> allocate memory for the each object before it gets dumped leads to out of
> memory.
>
> currently I don't see any alternative to this problem other than excluding
> the dump
> and export them separately.
>
> Regards,
> Haribabu Kommi
> Fujitsu Australia
>