On Tue, Oct 1, 2013 at 11:07 AM, Sergey Klochkov <klochkov@xxxxxxxxx> wrote: > Hello All, > > While trying to backup a database of relatively modest size (160 Gb) I ran > into the following issue: > > When I run > $ pg_dump -f /path/to/mydb.dmp -C -Z 9 mydb > > File /path/to/mydb.dmp does not appear (yes, I've checked permissions and so > on). pg_dump just begins to consume memory until it eats up all avaliable > RAM (96 Gb total on server, >64 Gb available) and is killed by the oom > killer. > > According to pg_stat_activity, pg_dump runs the following query > > SELECT oid, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid = lomowner) > AS rolname, lomacl FROM pg_largeobject_metadata > > until it is killed. > > strace shows that pg_dump is constantly reading a large amount of data from > a UNIX socket. I suspect that it is the result of the above query. > > There are >300000000 large objects in the database. Please don't ask me why. > > I tried googling on this, and found mentions of pg_dump being killed by oom > killer, but I failed to find anything related to the huge large objects > number. > > Is there any method of working around this issue? I think this problem comes from the fact that pg_dump treats each large object as it's own item. See getBlobs() which allocates a BlobInfo struct for each LO (and a DumpableObject if there are any, but that's just one). I assume the query (from that file): SELECT oid, lomacl FROM pg_largeobject_metadata returns 300000000 rows, which are then looped over? I ran into a similar issue a few years ago with a client using a 32-bit version of pg_dump, and got it worked around by moving to 64-bit. Did unfortunately not have time to look at the underlying issue. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
