Thread:
Sorry if this is a duplicate, I think I sent the first one out to the wrong address. Hello all, Upgraded from 7.1.3 to 7.4 (been waiting on this for a while thanks for all the hard work guys) and am having problems restoring from a pg_dumpall. Here's what I've done to dump the info: pg_dumpall -c > alldump.sql and I'm restoring with psql -e -d template1 -f alldump.sql which gives me these types of errors in the log: COPY "emp_skill" FROM stdin; psql:alldump.sql:12724: ERROR: literal carriage return found in data HINT: Use "\r" to represent carriage return. CONTEXT: COPY emp_skill, line 10145: "1149 V933280 NOTREQ GÎÆ <strong>FirstName LastName</strong>, 03/21/2003 - 03:41PM<br>\ NOT IN LVN SKIL..." Are there any other options I should've used to escape the carriage returns? Luckily I'm still just testing and this is on a VMWare partition with the snapshot right before the upgrade, or am I going to have to get something to parse the dump file and replace all the carriage returns with \n . This is all on RH 7.2. Thanks, Bryan Encina
"Bryan Encina" <bryan.encina@valleypres.org> writes:
> Upgraded from 7.1.3 to 7.4 (been waiting on this for a while thanks for all
> the hard work guys) and am having problems restoring from a pg_dumpall.
> psql:alldump.sql:12724: ERROR: literal carriage return found in data
> HINT: Use "\r" to represent carriage return.
Hm, that's a bit of a large version jump :-(. You might be able to make
it work by using the 7.4 pg_dump (which I'd recommend anyway) and
selecting -d or -D mode ... but that would be a lot slower to restore
than the COPY-based dump.
The most useful approach is probably just to run a quick sed script over
the dump file to fix the embedded carriage returns.
regards, tom lane
>Hm, that's a bit of a large version jump :-(. You might be able to make >it work by using the 7.4 pg_dump (which I'd recommend anyway) and >selecting -d or -D mode ... but that would be a lot slower to restore >than the COPY-based dump. > >The most useful approach is probably just to run a quick sed script over >the dump file to fix the embedded carriage returns. > Thanks Tom, I'll try the sed approach first. Also, I apologize for not posting with a subject in my original message, didn't realize I did that. Bryan Encina
>Hm, that's a bit of a large version jump :-(. You might be able to make >it work by using the 7.4 pg_dump (which I'd recommend anyway) and >selecting -d or -D mode ... but that would be a lot slower to restore >than the COPY-based dump. >The most useful approach is probably just to run a quick sed script over >the dump file to fix the embedded carriage returns. Once again, thanks Tom, sed appears to have done the trick. For anyone that's interested here's what worked for me: sed 's/^M/\\r/' alldump.sql > alldump2.sql where ^M is entered by pressing Ctrl+V then Enter. For any of the more experienced linux pros out there I'd appreciate any corrections if I've misspoken or have some error in logic. Bryan
On 21/11/2003, at 7:43 AM, Bryan Encina wrote: <excerpt>Upgraded from 7.1.3 to 7.4 (been waiting on this for a while thanks for all the hard work guys) and am having problems restoring from a pg_dumpall. </excerpt> I have a different problem, but the same situation - the same upgrade 7.1.3 to 7.4, by a clean fresh install on a new computer (which made it the obvious moment to do it). No problems with restoring some simple databases, no functions. But the dump of the DB with functions won't restore: $pg_restore -O -d cbisdb SampleTableChange.tar pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c "language c"? never saw that before. "permissions"? - I have checked the permissions in /usr/local/pgsql/lib/ libraries, all are readable & executable by me, and yet this does seem to be relevant, because if I try it as user postgres, I get a different error: pg_restore -O -d cbisdb SampleTableChange.tar pg_restore: [archiver (db)] could not execute query: ERROR: function "plpgsql_call_handler" already exists with same argument types Can anyone give me some clues here? thanks very much Cath <fontfamily><param>Lucida Grande</param>-- Cath dot Lawrence at anu dot edu dot au Senior Scientific Programmer, Centre for BioInformation Science, Australian National University</fontfamily> On 21/11/2003, at 7:43 AM, Bryan Encina wrote: > Upgraded from 7.1.3 to 7.4 (been waiting on this for a while thanks > for all > the hard work guys) and am having problems restoring from a pg_dumpall. I have a different problem, but the same situation - the same upgrade 7.1.3 to 7.4, by a clean fresh install on a new computer (which made it the obvious moment to do it). No problems with restoring some simple databases, no functions. But the dump of the DB with functions won't restore: $pg_restore -O -d cbisdb SampleTableChange.tar pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c "language c"? never saw that before. "permissions"? - I have checked the permissions in /usr/local/pgsql/lib/ libraries, all are readable & executable by me, and yet this does seem to be relevant, because if I try it as user postgres, I get a different error: pg_restore -O -d cbisdb SampleTableChange.tar pg_restore: [archiver (db)] could not execute query: ERROR: function "plpgsql_call_handler" already exists with same argument types Can anyone give me some clues here? thanks very much Cath -- Cath dot Lawrence at anu dot edu dot au Senior Scientific Programmer, Centre for BioInformation Science, Australian National University
Cath Lawrence <Cath.Lawrence@anu.edu.au> writes:
> $pg_restore -O -d cbisdb SampleTableChange.tar
> pg_restore: [archiver (db)] could not execute query: ERROR: permission
> denied for language c
> "language c"? never saw that before.
Apparently your dump contains some function definitions for C-language
functions. You must run the dump script as a Postgres superuser to load
these definitions.
> and yet this does seem to be relevant, because if I try it as user
> postgres, I get a different error:
> pg_restore -O -d cbisdb SampleTableChange.tar
> pg_restore: [archiver (db)] could not execute query: ERROR: function
> "plpgsql_call_handler" already exists with same argument types
Best to drop and recreate the database before re-trying the restore,
else you'll get complaints about pre-existing objects.
regards, tom lane
On Thu, 2004-01-08 at 14:05, Cath Lawrence wrote:
> me,and yet this does seem to be relevant, because if I try it as
> userpostgres, I get a different error:
> pg_restore -O -d cbisdb SampleTableChange.tar
> pg_restore: [archiver (db)] could not execute query: ERROR:
> function"plpgsql_call_handler" already exists with same argument types
Hi Cath,
I see this all the time doing restores.
I think there are some situations (on Debian, at least) where pl/pgsql
gets added to the template1 database. Once that happens you will always
have to do a "DROP LANGUAGE plpgsql CASCADE" after creating the database
and before restoring your dump.
PL/PgSQL is a bit awkward in this way - it's not sure whether it's a
standard component or not. Certainly every database I have ever
designed has it there, so from that point of view having it in the
template1 is useful. On the other hand it means that you always run
into this problem during restore...
If you only ever restore into this installation then you maybe want to
drop the language from the template1 database as well.
Ultimately some sort of a flag on pg_restore to "don't try and restore
languages" would be a good enhancement, I guess.
Regards,
Andrew McMillan
-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
http://survey.net.nz/ - any more questions?
-------------------------------------------------------------------------
Andrew McMillan <andrew@catalyst.net.nz> writes:
> On Thu, 2004-01-08 at 14:05, Cath Lawrence wrote:
>> me,and yet this does seem to be relevant, because if I try it as
>> userpostgres, I get a different error:
>> pg_restore -O -d cbisdb SampleTableChange.tar
>> pg_restore: [archiver (db)] could not execute query: ERROR:
>> function"plpgsql_call_handler" already exists with same argument types
> Hi Cath,
> I see this all the time doing restores.
> I think there are some situations (on Debian, at least) where pl/pgsql
> gets added to the template1 database. Once that happens you will always
> have to do a "DROP LANGUAGE plpgsql CASCADE" after creating the database
> and before restoring your dump.
The correct way to avoid this issue is to create the database you are
about to restore into as a clone of template0, not template1.
$ createdb -T template0 cbisdb
$ pg_restore -O -d cbisdb SampleTableChange.tar
You can get away without this fine point only so long as you don't add
any site-local objects to template1. plpgsql is just the most common
example of a site-local object you might want to add there ...
regards, tom lane