Обсуждение: Problems upgrading to 7.4
I have a 7.1.3 instance of postgres running on one machine and want to move the database from that machine to a new machinerunning 7.4.1. We've installed 7.4.1 and I have successfully run pg_dump on the 7.1.3 machine and transferred thefile over. However when I run pg_dump to restore the data, one table with approx 5.5 million records gives me ERROR: invalid memory alloc request size 1073741824 For various reasons, I do a dump/restore on the 7.1.3 machine nightly and this gives no problems. The 7.1.3 machine is running Red Hat Linux 6.2 The 7.4.1 machine is running Red Hat Linux v9 Has anyone any idea as to how I can get round this problem? Obviously a dump of INSERT commands is not going to be practicalas I'll probably still be here at Christmas waiting for the restore! Many thanks Hilary Hilary Forbes The DMR Information and Technology Group (www.dmr.co.uk) Direct tel 01689 889950 Fax 01689 860330 DMR is a UK registered trade mark of DMR Limited **********************************************************
On Wed, Jul 07, 2004 at 04:28:53PM +0100, Hilary Forbes wrote: > > ... when I run pg_dump to restore the data, one table with approx > 5.5 million records gives me > > ERROR: invalid memory alloc request size 1073741824 What do you mean "pg_dump to restore the data"? One would normally use psql or pg_restore to actually "restore" the data. In the past, that error message indicated some corrupted data that prevents a reading one or more rows in a table (and therefore prevents a pg_dump). But you seem to be saying that you can regularly dump that table without any problems. When can you dump the table? And when can you not dump the table? -Mike
Sorry! Meant pg_dump to dump and cat myfile.txt | psql mydatabase to restore. I have dumped just the offending table outas a separate file but this makes no odds. I seem to recall that last time I tried pg_dump in compressed format under7.1.3 it wouldn't restore so I haven't tried that. I can dump without any problem in 7.1.3 eg pg_dump -t bigtable -d mydb -f mybigtable.txt and, as I said, this file restores OK on the 7.1.3 version running under RH Linux 6.2 using cat mybigtable | psql newdatabase but I can't load it on my new 7.4 installation under RH Linux 9 with the same script - I get the memory problem. I guess I'll have to split the output file into chunks but I was hoping not to have to do that. Hilary At 14:27 07/07/2004 -0400, Michael Adler wrote: >On Wed, Jul 07, 2004 at 04:28:53PM +0100, Hilary Forbes wrote: > >> ... when I run pg_dump to restore the data, one table with approx >> 5.5 million records gives me >> >> ERROR: invalid memory alloc request size 1073741824 > >What do you mean "pg_dump to restore the data"? One would normally use >psql or pg_restore to actually "restore" the data. > >In the past, that error message indicated some corrupted data that >prevents a reading one or more rows in a table (and therefore prevents >a pg_dump). But you seem to be saying that you can regularly dump that >table without any problems. > >When can you dump the table? And when can you not dump the table? > >-Mike > > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly Hilary Forbes The DMR Information and Technology Group (www.dmr.co.uk) Direct tel 01689 889950 Fax 01689 860330 DMR is a UK registered trade mark of DMR Limited **********************************************************
I have the problem and the solution now after much tears. The problem was that we had managed to get ascii codes 13s into text fields in the source database. These were being exported into the dump and this was making the COPY FROM stdinprocess throw a wobbly. We are now running an intermediate process to strip out the ascii code 13s and *think* that this will solve the problem. Just in case anyone else has the same problem...... Hilary At 13:15 08/07/2004 +0100, Hilary Forbes wrote: >Sorry! Meant pg_dump to dump and cat myfile.txt | psql mydatabase to restore. I have dumped just the offending table outas a separate file but this makes no odds. I seem to recall that last time I tried pg_dump in compressed format under7.1.3 it wouldn't restore so I haven't tried that. > >I can dump without any problem in 7.1.3 >eg pg_dump -t bigtable -d mydb -f mybigtable.txt > >and, as I said, this file restores OK on the 7.1.3 version running under RH Linux 6.2 >using >cat mybigtable | psql newdatabase > >but I can't load it on my new 7.4 installation under RH Linux 9 with the same script - I get the memory problem. > >I guess I'll have to split the output file into chunks but I was hoping not to have to do that. > >Hilary > > > >At 14:27 07/07/2004 -0400, Michael Adler wrote: > >>On Wed, Jul 07, 2004 at 04:28:53PM +0100, Hilary Forbes wrote: > >>> ... when I run pg_dump to restore the data, one table with approx >>> 5.5 million records gives me >>> >>> ERROR: invalid memory alloc request size 1073741824 >> >>What do you mean "pg_dump to restore the data"? One would normally use >>psql or pg_restore to actually "restore" the data. >> >>In the past, that error message indicated some corrupted data that >>prevents a reading one or more rows in a table (and therefore prevents >>a pg_dump). But you seem to be saying that you can regularly dump that >>table without any problems. >> >>When can you dump the table? And when can you not dump the table? >> >>-Mike >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 3: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly > >Hilary Forbes >The DMR Information and Technology Group (www.dmr.co.uk) >Direct tel 01689 889950 Fax 01689 860330 >DMR is a UK registered trade mark of DMR Limited >********************************************************** > > >---------------------------(end of broadcast)--------------------------- >TIP 8: explain analyze is your friend Hilary Forbes The DMR Information and Technology Group (www.dmr.co.uk) Direct tel 01689 889950 Fax 01689 860330 DMR is a UK registered trade mark of DMR Limited **********************************************************