Re:

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(dot)encina(at)valleypres(dot)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:
> 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(dot)Lawrence(at)anu(dot)edu(dot)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(at)catalyst(dot)net(dot)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