Devart Forums

I export a very large table. TPgDump eats all memory. What should I do?

Version is: 2.10.0.4

Hello,

Please specify the number of fields and records in the table, and the amount of memory used for backup.
Also specify where you save the backup of your table - to memory (BackupToStream) or to file (BackupToFile)?

Hello,

Here is the code, I use BackupToFile.

I would like to backup a very large table. It has 216 fields and about 12 000 000 rows (no kidding). later you can find the table script.

I do not know the memory used for backup. It go higher and higher. After 1 GB, I must stop, because it can crash my pc.

procedure TMainForm.StartBitBtnClick(Sender: TObject);
begin
PgConnection.Connected:=True;

PgDump.BackupToFile('C:\aaaaa\a.dmp');

PgConnection.Connected:=False;
end;

procedure TMainForm.PgDumpBackupProgress(Sender: TObject; ObjectName: String; ObjectNum, ObjectCount, Percent: Integer);
begin
StatusBar.Panels[0].Text:=ObjectName+' '+IntToStr(ObjectNum)+'/'+IntToStr(ObjectCount)+' '+IntToStr(Percent)+'%';
Application.ProcessMessages;
end;

CREATE TABLE "public"."catalog_line" (
"catalog_line_id" INTEGER NOT NULL,
"catalog_id" INTEGER NOT NULL,
"zero_catalog" BOOLEAN,
"field_2_value" VARCHAR(120),
"field_3_value" VARCHAR(120),
"field_4_value" VARCHAR(2000),
"field_5_value" VARCHAR(120),
"field_6_value" VARCHAR(120),
"field_7_value" VARCHAR(120),
"field_8_value" VARCHAR(120),
"field_9_value" VARCHAR(120),
"field_10_value" VARCHAR(120),
"field_11_value" VARCHAR(120),
"field_12_value" VARCHAR(120),
"field_13_value" VARCHAR(120),
"field_14_value" VARCHAR(120),
"field_15_value" VARCHAR(120),
"field_16_value" VARCHAR(120),
"field_17_value" VARCHAR(2000),
"field_18_value" VARCHAR(120),
"field_4033_value" VARCHAR(120),
"field_4034_value" VARCHAR(120),
"field_4037_value" VARCHAR(120),
"field_4038_value" VARCHAR(120),
"field_4039_value" VARCHAR(120),
"field_4040_value" VARCHAR(120),
"field_4041_value" VARCHAR(300),
"field_4042_value" VARCHAR(120),
"field_4043_value" VARCHAR(120),
"field_4044_value" VARCHAR(120),
"field_4045_value" VARCHAR(120),
"field_4054_value" VARCHAR(120),
"field_4087_value" VARCHAR(120),
"field_4088_value" VARCHAR(120),
"field_4089_value" VARCHAR(120),
"field_4090_value" VARCHAR(120),
"field_4129_value" VARCHAR(120),
"field_4147_value" VARCHAR(120),
"field_4148_value" VARCHAR(120),
"field_4149_value" VARCHAR(120),
"field_4150_value" VARCHAR(120),
"field_4167_value" VARCHAR(120),
"field_4297_value" VARCHAR(120),
"field_4298_value" VARCHAR(120),
"field_4299_value" VARCHAR(120),
"field_4300_value" VARCHAR(120),
"field_4301_value" VARCHAR(120),
"field_4307_value" VARCHAR(120),
"field_4308_value" VARCHAR(120),
"field_4309_value" VARCHAR(120),
"field_4310_value" VARCHAR(120),
"field_4311_value" VARCHAR(120),
"field_4312_value" VARCHAR(120),
"field_4313_value" VARCHAR(120),
"field_4314_value" VARCHAR(120),
"field_4315_value" VARCHAR(120),
"field_4316_value" VARCHAR(120),
"field_4317_value" VARCHAR(120),
"field_4318_value" VARCHAR(120),
"field_4319_value" VARCHAR(120),
"field_4320_value" VARCHAR(120),
"field_4321_value" VARCHAR(120),
"field_4322_value" VARCHAR(120),
"field_4323_value" VARCHAR(120),
"field_4324_value" VARCHAR(120),
"field_4325_value" VARCHAR(120),
"field_4326_value" VARCHAR(120),
"field_4327_value" VARCHAR(120),
"field_4328_value" VARCHAR(120),
"field_4329_value" VARCHAR(120),
"field_4330_value" VARCHAR(120),
"field_4332_value" VARCHAR(120),
"field_4333_value" VARCHAR(120),
"field_4334_value" VARCHAR(120),
"field_4335_value" VARCHAR(120),
"field_4336_value" VARCHAR(120),
"field_4648_value" VARCHAR(120),
"field_48227_value" VARCHAR(120),
"field_48228_value" VARCHAR(120),
"field_48229_value" VARCHAR(120),
"field_48294_value" VARCHAR(120),
"field_48295_value" VARCHAR(120),
"field_48296_value" VARCHAR(120),
"field_48297_value" VARCHAR(120),
"field_48298_value" VARCHAR(120),
"field_48492_value" VARCHAR(120),
"field_48493_value" VARCHAR(120),
"field_48494_value" VARCHAR(120),
"field_48495_value" VARCHAR(120),
"field_48496_value" VARCHAR(120),
"field_48497_value" VARCHAR(120),
"field_49086_value" VARCHAR(120),
"field_49087_value" VARCHAR(120),
"field_54907_value" VARCHAR(120),
"field_54908_value" VARCHAR(120),
"field_54909_value" VARCHAR(120),
"field_54910_value" VARCHAR(2000),
"field_54911_value" VARCHAR(120),
"field_54917_value" VARCHAR(120),
"field_56248_value" VARCHAR(120),
"field_59787_value" VARCHAR(120),
"field_59807_value" VARCHAR(120),
"field_59808_value" VARCHAR(120),
"field_59809_value" VARCHAR(120),
"field_59810_value" VARCHAR(120),
"field_65490_value" VARCHAR(120),
"field_65491_value" VARCHAR(120),
"field_65492_value" VARCHAR(120),
"field_65547_value" VARCHAR(120),
"field_4331_value" VARCHAR(120),
"field_69127_value" VARCHAR(120),
"field_69128_value" VARCHAR(120),
"field_69129_value" VARCHAR(120),
"field_70830_value" VARCHAR(120),
"field_71189_value" VARCHAR(120),
"field_71190_value" VARCHAR(120),
"field_71191_value" VARCHAR(120),
"field_71192_value" VARCHAR(120),
"field_71193_value" VARCHAR(120),
"field_71194_value" VARCHAR(120),
"field_72427_value" VARCHAR(120),
"field_75267_value" VARCHAR(120),
"field_75268_value" VARCHAR(120),
"field_75269_value" VARCHAR(120),
"field_75273_value" VARCHAR(120),
"field_75274_value" VARCHAR(120),
"field_75275_value" VARCHAR(120),
"field_76827_value" VARCHAR(120),
"field_76828_value" VARCHAR(120),
"field_77690_value" VARCHAR(120),
"field_78007_value" VARCHAR(120),
"field_78008_value" VARCHAR(120),
"field_78009_value" VARCHAR(120),
"field_78010_value" VARCHAR(120),
"field_78011_value" VARCHAR(120),
"field_78012_value" VARCHAR(120),
"field_78042_value" VARCHAR(120),
"field_78043_value" VARCHAR(120),
"field_78044_value" VARCHAR(120),
"field_78045_value" VARCHAR(120),
"field_78051_value" VARCHAR(120),
"field_78052_value" VARCHAR(120),
"field_78053_value" VARCHAR(120),
"field_78704_value" VARCHAR(120),
"field_78827_value" VARCHAR(120),
"field_81007_value" VARCHAR(120),
"field_81008_value" VARCHAR(120),
"field_81366_value" VARCHAR(120),
"field_81367_value" VARCHAR(120),
"field_81368_value" VARCHAR(120),
"field_81369_value" VARCHAR(120),
"field_82088_value" VARCHAR(120),
"field_82089_value" VARCHAR(120),
"field_87227_value" VARCHAR(120),
"field_87522_value" VARCHAR(120),
"field_87523_value" VARCHAR(120),
"field_87524_value" VARCHAR(120),
"field_87525_value" VARCHAR(120),
"field_87526_value" VARCHAR(120),
"field_87527_value" VARCHAR(120),
"field_87528_value" VARCHAR(120),
"field_87529_value" VARCHAR(120),
"field_87530_value" VARCHAR(120),
"field_87531_value" VARCHAR(120),
"field_87532_value" VARCHAR(120),
"field_87533_value" VARCHAR(120),
"field_88030_value" VARCHAR(120),
"field_88031_value" VARCHAR(120),
"field_88032_value" VARCHAR(120),
"field_88111_value" VARCHAR(120),
"field_88112_value" VARCHAR(120),
"field_88113_value" VARCHAR(120),
"field_88147_value" VARCHAR(120),
"field_88148_value" VARCHAR(120),
"field_88149_value" VARCHAR(120),
"field_88150_value" VARCHAR(120),
"field_88151_value" VARCHAR(120),
"field_88152_value" VARCHAR(120),
"field_88153_value" VARCHAR(120),
"field_88154_value" VARCHAR(120),
"field_88155_value" VARCHAR(120),
"field_88156_value" VARCHAR(120),
"field_88157_value" VARCHAR(120),
"field_88158_value" VARCHAR(120),
"field_88159_value" VARCHAR(120),
"field_88160_value" VARCHAR(120),
"field_88161_value" VARCHAR(120),
"field_88162_value" VARCHAR(120),
"field_88163_value" VARCHAR(120),
"field_88164_value" VARCHAR(120),
"field_88165_value" VARCHAR(120),
"field_88166_value" VARCHAR(120),
"field_88167_value" VARCHAR(120),
"field_90147_value" VARCHAR(120),
"field_90148_value" VARCHAR(120),
"field_90149_value" VARCHAR(120),
"field_90151_value" VARCHAR(120),
"field_90152_value" VARCHAR(120),
"field_90153_value" VARCHAR(120),
"field_90154_value" VARCHAR(120),
"field_90155_value" VARCHAR(120),
"field_90156_value" VARCHAR(120),
"field_90201_value" VARCHAR(120),
"field_92067_value" VARCHAR(120),
"field_92610_value" VARCHAR(120),
"field_92647_value" VARCHAR(120),
"field_93884_value" VARCHAR(120),
"field_97667_value" VARCHAR(120),
"field_97668_value" VARCHAR(120),
"field_97787_value" VARCHAR(120),
"field_98686_value" VARCHAR(120),
"field_98890_value" VARCHAR(120),
"field_98891_value" VARCHAR(120),
"field_101725_value" VARCHAR(120),
"field_101726_value" VARCHAR(120),
"field_101727_value" VARCHAR(120),
CONSTRAINT "catalog_line_pkey" PRIMARY KEY("catalog_line_id"),
CONSTRAINT "fk_catalog_line" FOREIGN KEY ("catalog_id")
REFERENCES "public"."catalog"("catalog_id")
ON DELETE RESTRICT
ON UPDATE RESTRICT
NOT DEFERRABLE
) WITHOUT OIDS;

CREATE INDEX "catalog_line_f1" ON "public"."catalog_line"
USING btree ("catalog_id");

CREATE INDEX "catalog_line_idx_10" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_10_value");

CREATE INDEX "catalog_line_idx_10_12" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_10_value", "field_12_value");

CREATE INDEX "catalog_line_idx_11" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_11_value");

CREATE INDEX "catalog_line_idx_12" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_12_value");

CREATE INDEX "catalog_line_idx_13" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_13_value");

CREATE INDEX "catalog_line_idx_14" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_14_value");

CREATE INDEX "catalog_line_idx_15" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_15_value");

CREATE INDEX "catalog_line_idx_16" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_16_value");

CREATE INDEX "catalog_line_idx_17" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_17_value");

CREATE INDEX "catalog_line_idx_18" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_18_value");

CREATE INDEX "catalog_line_idx_2" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_2_value");

CREATE INDEX "catalog_line_idx_2_3" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_2_value", "field_3_value");

CREATE INDEX "catalog_line_idx_2_zero" ON "public"."catalog_line"
USING btree ("field_2_value", "zero_catalog");

CREATE INDEX "catalog_line_idx_3" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_3_value");

CREATE INDEX "catalog_line_idx_4" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4_value");

CREATE INDEX "catalog_line_idx_4033" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4033_value");

CREATE INDEX "catalog_line_idx_4037" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4037_value");

CREATE INDEX "catalog_line_idx_4038" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4038_value");

CREATE INDEX "catalog_line_idx_4039" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4039_value");

CREATE INDEX "catalog_line_idx_4040" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4040_value");

CREATE INDEX "catalog_line_idx_4041" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4041_value");

CREATE INDEX "catalog_line_idx_4042" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4042_value");

CREATE INDEX "catalog_line_idx_4043" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4043_value");

CREATE INDEX "catalog_line_idx_4044" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4044_value");

CREATE INDEX "catalog_line_idx_4054" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4054_value");

CREATE INDEX "catalog_line_idx_4129" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4129_value");

CREATE INDEX "catalog_line_idx_4147" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4147_value");

CREATE INDEX "catalog_line_idx_4148" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4148_value");

CREATE INDEX "catalog_line_idx_4149" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4149_value");

CREATE INDEX "catalog_line_idx_4319" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4319_value");

CREATE INDEX "catalog_line_idx_4320" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4320_value");

CREATE INDEX "catalog_line_idx_4324" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4324_value");

CREATE INDEX "catalog_line_idx_4325" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_4325_value");

CREATE INDEX "catalog_line_idx_48493" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_48493_value");

CREATE INDEX "catalog_line_idx_5" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_5_value");

CREATE INDEX "catalog_line_idx_6" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_6_value");

CREATE INDEX "catalog_line_idx_7" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_7_value");

CREATE INDEX "catalog_line_idx_78827" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_78827_value");

CREATE INDEX "catalog_line_idx_8" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_8_value");

CREATE INDEX "catalog_line_idx_88032" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_88032_value");

CREATE INDEX "catalog_line_idx_9" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_9_value");

CREATE INDEX "catalog_line_idx_97667" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_97667_value");

CREATE INDEX "catalog_line_idx_98686" ON "public"."catalog_line"
USING btree ("zero_catalog", "field_98686_value");

CREATE INDEX "catalog_line_idx_s_0" ON "public"."catalog_line"
USING btree ("field_2_value", "catalog_id");

Hello,

Thank you for the information.
We have reproduced and fixed the problem.
This fix will be included in the next build.

Hello,

I have installed the new TPgDump. It does not eat memory, but

DoBackupProgress(TableName, TableNum, TableCount, Trunc((Query.RecNo / Query.RecordCount) * 100));

is not correct. The Query.RecordCount does not have the right value, and the progress jumps between 99% to 100% (large table...).

Best regards.

Hello,

To get the correct record count in the RecordCount property, you should set the QueryRecCount property to true or set the FetchAll property to true.
If it doesn't help, please send a complete small sample to alexp*devart*com to demonstrate the problem.

Hello, thanks for your answer...

I make the following changes in the PgServices.pas. If it Is OK, please put it into the next release.

Best regards.

István

procedure TCustomPgDumpProcessor.BackupData(const Schema, TableName, QueryText: _string;
TableNum, TableCount: integer);
var
Query: TCustomDADataSet;
i: integer;
FieldList, Sql, Header: _string;
begin
Query := CreateQuery;
try

// !!!!!!!!!!!!!!!!!!!!!
Query.Options.QueryRecCount:=True;
// !!!!!!!!!!!!!!!!!!!!!

TDBAccessUtils.CheckConnection(Query);
TDBAccessUtils.GetIRecordSet(Query).SetProp(prFieldsAsText, True);

Hello,

Thank you for the information.
We will add this change to the next build.