Thread: Upgrading from 8.0 to 8.1 (w32)
Another Postgre noob here. I've used MySQL for some time and am trying to learn Postgre. Before axing the 8.0 installation I used "backup" from pgAdmin III (v1.4 w32) of my database to restore into the fresh install of 8.1 Since this is my first backup test, the data is not critical, but I would like to be able to use the backup. However, after creating the new database in 8.1 and trying to load my SQL query from the backup, I get an error message: "ERROR: syntax error at or near "0" at character 8664" The worst part is that I don't know where character 8664 is as the SQL editor window counts in lines, columns, and rows...no character place indicator. 8( Does anyone have any suggestions on how to find the character 8664 or how else to load my backup .sql into Postgre 8.1? Thanks!
On Mon, 2005-11-07 at 16:24 -0500, Wes Williams wrote: > Before axing the 8.0 installation I used "backup" from pgAdmin III (v1.4 > w32) of my database to restore into the fresh install of 8.1 > > Since this is my first backup test, the data is not critical, but I would > like to be able to use the backup. However, after creating the new database > in 8.1 and trying to load my SQL query from the backup, I get an error > message: "ERROR: syntax error at or near "0" at character 8664" > > The worst part is that I don't know where character 8664 is as the SQL > editor window counts in lines, columns, and rows...no character place > indicator. 8( > > Does anyone have any suggestions on how to find the character 8664 or how > else to load my backup .sql into Postgre 8.1? Use psql: psql -d mydatabase -f /path/to/backup/file When you use -f, you can see line numbers. one possibility is that you will find the error in a function body, that was accepted without checking by earlier versions but is now being checked by 8.1. Oliver Elphick
Thanks for your tip Oliver, I'll certainly use it next time. I was able to import the backup late last night by dismantling it and loading in sections. It was then I noticed the subtle coloring differences in pgAdmin III indicating the errors. Indeed, all of the functions were causing errors so I simply removed them. The primary error stopping my data from loading was due to a good old Irish name, "O'Sali", in my database. The "'" threw off the SQL COPY command apparently. Suggestions for improvement to pgAdmin III: As an aside, it would be GREAT if pgAdmin highlighted [like a yellow background] the errors to make them more visible in addition to searching for errors or finding the character number. ====== Keep up the good work, pgAdmin III v1.4 is easier for us novices to learn with and the tips are very helpful. -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Oliver Elphick Sent: Monday, November 07, 2005 5:57 PM To: Wes Williams Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Upgrading from 8.0 to 8.1 (w32) On Mon, 2005-11-07 at 16:24 -0500, Wes Williams wrote: > Before axing the 8.0 installation I used "backup" from pgAdmin III (v1.4 > w32) of my database to restore into the fresh install of 8.1 > > Since this is my first backup test, the data is not critical, but I would > like to be able to use the backup. However, after creating the new database > in 8.1 and trying to load my SQL query from the backup, I get an error > message: "ERROR: syntax error at or near "0" at character 8664" > > The worst part is that I don't know where character 8664 is as the SQL > editor window counts in lines, columns, and rows...no character place > indicator. 8( > > Does anyone have any suggestions on how to find the character 8664 or how > else to load my backup .sql into Postgre 8.1? Use psql: psql -d mydatabase -f /path/to/backup/file When you use -f, you can see line numbers. one possibility is that you will find the error in a function body, that was accepted without checking by earlier versions but is now being checked by 8.1. Oliver Elphick ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
Another interesting tidbit.
Executing the same SQL backup file in pgAdmin III SQL query window failed
and returns an error based on some line number (after removing the
previously indicated errors).
Interestingly, using the suggested from the CLI:
psql -d mydatabase -f /path/to/backup/file
and the backup loaded just fine. Then I emptied the database and tried the
original backup, functions and all. In this instance the command line
returned some errors/warnings but still proceeded to load my data. 8)
Thanks again Oliver!
-----Original Message-----
From: Oliver Elphick [mailto:olly@lfix.co.uk]
Sent: Monday, November 07, 2005 5:57 PM
To: Wes Williams
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Upgrading from 8.0 to 8.1 (w32)
On Mon, 2005-11-07 at 16:24 -0500, Wes Williams wrote:
> Before axing the 8.0 installation I used "backup" from pgAdmin III (v1.4
> w32) of my database to restore into the fresh install of 8.1
>
> Since this is my first backup test, the data is not critical, but I would
> like to be able to use the backup. However, after creating the new
database
> in 8.1 and trying to load my SQL query from the backup, I get an error
> message: "ERROR: syntax error at or near "0" at character 8664"
>
> The worst part is that I don't know where character 8664 is as the SQL
> editor window counts in lines, columns, and rows...no character place
> indicator. 8(
>
> Does anyone have any suggestions on how to find the character 8664 or how
> else to load my backup .sql into Postgre 8.1?
Use psql:
psql -d mydatabase -f /path/to/backup/file
When you use -f, you can see line numbers.
one possibility is that you will find the error in a function body, that
was accepted without checking by earlier versions but is now being
checked by 8.1.
Oliver Elphick