Restore a large PostgreSQL dump

Support - Assistance technique
1

Hi team

I am having a challenge on restoring a postres dump which is large (16gig), it gives an error on restoring data in table analytics 2014.

Can anyone please help with how I can go around it, or maybe how to edit the dump (notepad++ cannot open the file is too large).

Regards

Ngonidzashe Manika

Data Officer

**+**263 772 789673, nmanika@hotmail.com, skype: ngoni.manika

Ministry of Health and Child Care, AIDS and TB Unit, 2nd Floor, Library, Mkwati Building

2

Easiest way is to not dump the analytics tables at all. They can be regenerated by running the analytics process once you have restores your database without these tables.

Regards,

Jason

···

On Mar 11, 2015 5:31 PM, “Ngoni Manika” nmanika@hotmail.com wrote:

Hi team

I am having a challenge on restoring a postres dump which is large (16gig), it gives an error on restoring data in table analytics 2014.

Can anyone please help with how I can go around it, or maybe how to edit the dump (notepad++ cannot open the file is too large).

Regards

Ngonidzashe Manika

Data Officer

**+**263 772 789673, nmanika@hotmail.com, skype: ngoni.manika

Ministry of Health and Child Care, AIDS and TB Unit, 2nd Floor, Library, Mkwati Building

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

3

This advice may be a bit late for you, but I would recommend excluding analytics when you do a backup.

···

On 11 Mar 2015 17:31, “Ngoni Manika” nmanika@hotmail.com wrote:

Hi team

I am having a challenge on restoring a postres dump which is large (16gig), it gives an error on restoring data in table analytics 2014.

Can anyone please help with how I can go around it, or maybe how to edit the dump (notepad++ cannot open the file is too large).

Regards

Ngonidzashe Manika

Data Officer

**+**263 772 789673, nmanika@hotmail.com, skype: ngoni.manika

Ministry of Health and Child Care, AIDS and TB Unit, 2nd Floor, Library, Mkwati Building

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

4

Knut and Jason are both right. There is no point dumping tables which can be recreated.

But that in itself shouldn’t cause an error restoring a dump and 16G is far from huge (of course too big for notepad++ but that’s a different issue). What was the error message?

···

On 11 March 2015 at 16:39, Knut Staring knutst@gmail.com wrote:

This advice may be a bit late for you, but I would recommend excluding analytics when you do a backup.

On 11 Mar 2015 17:31, “Ngoni Manika” nmanika@hotmail.com wrote:

Hi team

I am having a challenge on restoring a postres dump which is large (16gig), it gives an error on restoring data in table analytics 2014.

Can anyone please help with how I can go around it, or maybe how to edit the dump (notepad++ cannot open the file is too large).

Regards

Ngonidzashe Manika

Data Officer

**+**263 772 789673, nmanika@hotmail.com, skype: ngoni.manika

Ministry of Health and Child Care, AIDS and TB Unit, 2nd Floor, Library, Mkwati Building

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

5

Thanks Bob

Below is the error im getting

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 5328; 0 1415587 TABLE DATA analytics_2014 postgres

pg_restore: [archiver (db)] COPY failed for table “analytics_2014”: ERROR: missing data for column “NDAElbuYMNM”

CONTEXT: COPY analytics_2014, line 11201741: “\N \N \N \N \N xuuDrpnpEPD c2g5zOOo”

pg_restore: restoring data for table “analytics_2015”

pg_restore: [tar archiver] corrupt tar header found in \N (expected 3679, computed 33533) file position 9118576128

Regards

Ngonidzashe Manika

Data Officer

**+**263 772 789673, nmanika@hotmail.com, skype: ngoni.manika

Ministry of Health and Child Care, AIDS and TB Unit, 2nd Floor, Library, Mkwati Building

···

From: Bob Jolliffe [mailto:bobjolliffe@gmail.com]
Sent: Wednesday, March 11, 2015 6:52 PM
To: Knut Staring
Cc: Ngoni Manika; dhis2-users
Subject: Re: [Dhis2-users] Restore a large PostgreSQL dump

Knut and Jason are both right. There is no point dumping tables which can be recreated.

But that in itself shouldn’t cause an error restoring a dump and 16G is far from huge (of course too big for notepad++ but that’s a different issue). What was the error message?

On 11 March 2015 at 16:39, Knut Staring knutst@gmail.com wrote:

This advice may be a bit late for you, but I would recommend excluding analytics when you do a backup.

On 11 Mar 2015 17:31, “Ngoni Manika” nmanika@hotmail.com wrote:

Hi team

I am having a challenge on restoring a postres dump which is large (16gig), it gives an error on restoring data in table analytics 2014.

Can anyone please help with how I can go around it, or maybe how to edit the dump (notepad++ cannot open the file is too large).

Regards

Ngonidzashe Manika

Data Officer

**+**263 772 789673, nmanika@hotmail.com, skype: ngoni.manika

Ministry of Health and Child Care, AIDS and TB Unit, 2nd Floor, Library, Mkwati Building

Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp

Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp

6

Thats a bit odd. If the data was in the table when it was dumped it should be there when its restored. Looks like the dump file could be corrupted.

Are you restoring with pgadmin. You might have better luck with command line.

If you don’t have the option to make another dump then leave out these big useless analytics tables as others have suggested

···

On 11 Mar 2015 19:49, “Ngoni Manika” nmanika@hotmail.com wrote:

Thanks Bob

Below is the error im getting

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 5328; 0 1415587 TABLE DATA analytics_2014 postgres

pg_restore: [archiver (db)] COPY failed for table “analytics_2014”: ERROR: missing data for column “NDAElbuYMNM”

CONTEXT: COPY analytics_2014, line 11201741: “\N \N \N \N \N xuuDrpnpEPD c2g5zOOo”

pg_restore: restoring data for table “analytics_2015”

pg_restore: [tar archiver] corrupt tar header found in \N (expected 3679, computed 33533) file position 9118576128

Regards

Ngonidzashe Manika

Data Officer

+263 772 789673, nmanika@hotmail.com, skype: ngoni.manika

Ministry of Health and Child Care, AIDS and TB Unit, 2nd Floor, Library, Mkwati Building

From: Bob Jolliffe [mailto:bobjolliffe@gmail.com]

Sent: Wednesday, March 11, 2015 6:52 PM

To: Knut Staring

Cc: Ngoni Manika; dhis2-users

Subject: Re: [Dhis2-users] Restore a large PostgreSQL dump

Knut and Jason are both right. There is no point dumping tables which can be recreated.

But that in itself shouldn’t cause an error restoring a dump and 16G is far from huge (of course too big for notepad++ but that’s a different issue). What was the error message?

On 11 March 2015 at 16:39, Knut Staring knutst@gmail.com wrote:

This advice may be a bit late for you, but I would recommend excluding analytics when you do a backup.

On 11 Mar 2015 17:31, “Ngoni Manika” nmanika@hotmail.com wrote:

Hi team

I am having a challenge on restoring a postres dump which is large (16gig), it gives an error on restoring data in table analytics 2014.

Can anyone please help with how I can go around it, or maybe how to edit the dump (notepad++ cannot open the file is too large).

Regards

Ngonidzashe Manika

Data Officer

+263 772 789673, nmanika@hotmail.com, skype: ngoni.manika

Ministry of Health and Child Care, AIDS and TB Unit, 2nd Floor, Library, Mkwati Building

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

7

Thanks a lot, I have also tried the command line but it’s the same result. I guess its corrupted then. Will try to do another one.

Thanks guys

Ngonidzashe Manika

Data Officer

**+**263 772 789673, nmanika@hotmail.com, skype: ngoni.manika

Ministry of Health and Child Care, AIDS and TB Unit, 2nd Floor, Library, Mkwati Building

···

From: Bob Jolliffe [mailto:bobjolliffe@gmail.com]
Sent: Wednesday, March 11, 2015 9:29 PM
To: Ngoni Manika
Cc: Knut Staring; dhis2-users
Subject: RE: [Dhis2-users] Restore a large PostgreSQL dump

Thats a bit odd. If the data was in the table when it was dumped it should be there when its restored. Looks like the dump file could be corrupted.
Are you restoring with pgadmin. You might have better luck with command line.
If you don’t have the option to make another dump then leave out these big useless analytics tables as others have suggested

On 11 Mar 2015 19:49, “Ngoni Manika” nmanika@hotmail.com wrote:

Thanks Bob

Below is the error im getting

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 5328; 0 1415587 TABLE DATA analytics_2014 postgres

pg_restore: [archiver (db)] COPY failed for table “analytics_2014”: ERROR: missing data for column “NDAElbuYMNM”

CONTEXT: COPY analytics_2014, line 11201741: “\N \N \N \N \N xuuDrpnpEPD c2g5zOOo”

pg_restore: restoring data for table “analytics_2015”

pg_restore: [tar archiver] corrupt tar header found in \N (expected 3679, computed 33533) file position 9118576128

Regards

Ngonidzashe Manika

Data Officer

+263 772 789673, nmanika@hotmail.com, skype: ngoni.manika

Ministry of Health and Child Care, AIDS and TB Unit, 2nd Floor, Library, Mkwati Building

From: Bob Jolliffe [mailto:bobjolliffe@gmail.com]
Sent: Wednesday, March 11, 2015 6:52 PM
To: Knut Staring
Cc: Ngoni Manika; dhis2-users
Subject: Re: [Dhis2-users] Restore a large PostgreSQL dump

Knut and Jason are both right. There is no point dumping tables which can be recreated.

But that in itself shouldn’t cause an error restoring a dump and 16G is far from huge (of course too big for notepad++ but that’s a different issue). What was the error message?

On 11 March 2015 at 16:39, Knut Staring knutst@gmail.com wrote:

This advice may be a bit late for you, but I would recommend excluding analytics when you do a backup.

On 11 Mar 2015 17:31, “Ngoni Manika” nmanika@hotmail.com wrote:

Hi team

I am having a challenge on restoring a postres dump which is large (16gig), it gives an error on restoring data in table analytics 2014.

Can anyone please help with how I can go around it, or maybe how to edit the dump (notepad++ cannot open the file is too large).

Regards

Ngonidzashe Manika

Data Officer

+263 772 789673, nmanika@hotmail.com, skype: ngoni.manika

Ministry of Health and Child Care, AIDS and TB Unit, 2nd Floor, Library, Mkwati Building

Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp

Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp