Application creates Postgres backup files using command
pg_dump mydb -ib -Z3 -f mybackup.backup -Fc -h localhost -T firma*.attachme -T firma*.artpilt In Postgres 9.3 in Windows 8 x64
Creating empty database and using pgadmin to restore from this file in Postgres 9.3 in windows 7 x64 runs forever. CPU usage by pg_restore is 0% . postgres log file does not contain any information in normal log level.
Backup file was transferred over web. Its header starts with PGDMP and there are lot of create command in start. Its size is 24MB so restore cannto take long time. Restore is done to same computer where server exists.
How to restore from backup? How to check .backup file integrity ? I tried to use 7-zip test option to test it, but 7-zip wrote that it cannot open file.
Update
select * from pg_stat activity shows number of pg_restore processes (8 jobs was specified on restore since cpu has 8 cores) starting at 10:51 when backup starts. All of them have idle status and start time does not change. Running this query multiple times does not change result.
930409;"betoontest";8916;10;"postgres";"pg_restore";"::1";"";49755;"2014-11-18 10:51:39.262+02";"";"2014-11-18 10:51:42.064+02";"2014-11-18 10:51:42.094+02";f;"idle";"CREATE INDEX makse_dokumnr_idx ON makse USING btree (dokumnr); " 930409;"betoontest";9640;10;"postgres";"pg_restore";"::1";"";49760;"2014-11-18 10:51:39.272+02";"";"2014-11-18 10:51:39.662+02";"2014-11-18 10:51:42.044+02";f;"idle in transaction (aborted)";"COPY rid (id, reanr, dokumnr, nimetus, hind, kogus, toode, partii, myygikood, hinnak, kaubasumma, yhik, kulukonto, kuluobjekt, rid2obj, reakuupaev, kogpak, kulum, baasostu, ostuale, rid3obj, rid4obj, rid5obj, rid6obj, rid7obj, rid8obj, rid9obj, kaskogus, a (...)" 930409;"betoontest";8176;10;"postgres";"pg_restore";"::1";"";49761;"2014-11-18 10:51:39.272+02";"";"2014-11-18 10:51:42.064+02";"2014-11-18 10:51:42.094+02";f;"idle";"CREATE INDEX attachme_idmailbox_idx ON attachme USING btree (idmailbox); " 930409;"betoontest";8108;10;"postgres";"pg_restore";"::1";"";49765;"2014-11-18 10:51:39.272+02";"";"2014-11-18 10:51:42.064+02";"2014-11-18 10:51:42.094+02";f;"idle";"CREATE INDEX makse_kuupaev_kellaeg_idx ON makse USING btree (kuupaev, kellaaeg); " 930409;"betoontest";8956;10;"postgres";"pg_restore";"::1";"";49764;"2014-11-18 10:51:39.282+02";"";"2014-11-18 10:51:42.074+02";"2014-11-18 10:51:42.094+02";f;"idle";"CREATE INDEX makse_varadokumn_idx ON makse USING btree (varadokumn); " 930409;"betoontest";11780;10;"postgres";"pg_restore";"::1";"";49763;"2014-11-18 10:51:39.292+02";"";"2014-11-18 10:51:42.064+02";"2014-11-18 10:51:42.094+02";f;"idle";"ALTER TABLE ONLY mitteakt ADD CONSTRAINT mitteakt_pkey PRIMARY KEY (klient, toode); " 930409;"betoontest";4680;10;"postgres";"pg_restore";"::1";"";49762;"2014-11-18 10:51:39.292+02";"";"2014-11-18 10:51:42.064+02";"2014-11-18 10:51:42.094+02";f;"idle";"ALTER TABLE ONLY mailbox ADD CONSTRAINT mailbox_pkey PRIMARY KEY (guid); " 930409;"betoontest";5476;10;"postgres";"pg_restore";"::1";"";49766;"2014-11-18 10:51:39.332+02";"";"2014-11-18 10:51:42.064+02";"2014-11-18 10:51:42.094+02";f;"idle";"CREATE INDEX makse_kuupaev_idx ON makse USING btree (kuupaev); Data is restored partially. Maybe file is truncated and postgres or pg_restore waits for data forever. How to prevent such hangups?
0 comments:
Post a Comment