is it possible to limit somehow usage of resources for pg_dump?
The problem is, whole DBase is 775GB and i have two very large tables:
pg_largeobject table is – 390GB
pg_largeobject statistics: Index scans 778313505 Index tuples fetched 1079812292 Tuples inserted 201022148 Table size 395 GB Indexes size 6900 MB
hive.lob_messages – 265GB.
Index scans 194377937 Index tuples fetched 183894199 Tuples inserted 16902844 Table size 8127 MB Toast table size 272573947904 Indexes size 3090 MB
In all cases I am executing this pg_dump command:
pg_dump -U postgres -d hive -Fc -v -f /nfs/hive_dump_10/hive_full_20191016_1945.dmp 2> /nfs/hive_dump_10/hive_full_20191016_1945.log
When i try to run pg_dump, to dump all the data it is killed by "out of memory". My Postgre SQL server have had 20gb of RAMs and when I started a pg_dump, it kills after about ~30minutes. saying out of memory.
DB with 20GB of rams and pg_dump is killed by this *.log line:
pg_dump: reading large objects
Oct 15 09:00:18 hive-psql kernel: [21504626.583951] Out of memory: Kill process 30663 (pg_dump) score 750 or sacrifice child Oct 15 09:00:18 hive-psql kernel: [21504626.584000] Killed process 30663 (pg_dump) total-vm:19151692kB, anon-rss:16873728kB, file-rss:0kB, shmem-rss:0kB
So to cope with the problem i gave my all resources, additional +20 GB ram (40gb in total), and the it kills after ~3hours by this *.log line:
pg_dump: dumping contents of table "hive.lob_messages"
And kern.log says:
Oct 16 23:19:01 hive-psql kernel: [15614.408113] Out of memory: Kill process 2693 (pg_dump) score 840 or sacrifice child Oct 16 23:19:01 hive-psql kernel: [15614.408169] Killed process 2693 (pg_dump) total-vm:38363104kB, anon-rss:34020140kB, file-rss:24kB, shmem-rss:0kB
I do not have any more resources for that. So what are my options? My DB grows everyday, thank you. I think that additional 20GB will be suffice, 60GB of rams it should be enough . But is this OK situation? What to do when my DB became like ~2TB in size and etc...
Other question, is my hive.lob_messages table is okay? Table size and toast table size is different.