Thread: Restore problem

Restore problem

From
"Bob Pawley"
Date:
Hi
 
I have restored a database using psql to windows version 8.4.
 
During the restore the trigger code became jumbled.
 
I now have a great number of lines that have moved so that they are now included in  lines the have been commented out – not to mention that the code is hard to read.
 
Is there some way of correcting this – or re restoring the database, so that I don’t have to go through the whole code line by line?
 
Bob

Re: Restore problem

From
Gurjeet Singh
Date:
On Tue, Dec 28, 2010 at 6:06 PM, Bob Pawley <rjpawley@shaw.ca> wrote:
Hi
 
I have restored a database using psql to windows version 8.4.
 
During the restore the trigger code became jumbled.
 
I now have a great number of lines that have moved so that they are now included in  lines the have been commented out – not to mention that the code is hard to read.
 
Is there some way of correcting this – or re restoring the database, so that I don’t have to go through the whole code line by line?

I don't believe there's any easy way to do that. Can you show us some examples of the 'before' and 'after' code, maybe that'll help.

Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com

singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device

Re: Restore problem

From
Adrian Klaver
Date:
On Tuesday 28 December 2010 3:06:40 pm Bob Pawley wrote:
> Hi
>
> I have restored a database using psql to windows version 8.4.
>
> During the restore the trigger code became jumbled.
>
> I now have a great number of lines that have moved so that they are now
> included in  lines the have been commented out – not to mention that the
> code is hard to read.

This is in the plain text dump file right?

>
> Is there some way of correcting this – or re restoring the database, so
> that I don’t have to go through the whole code line by line?

With out seeing an example that is going to be difficult :)

>
> Bob



--
Adrian Klaver
adrian.klaver@gmail.com

Re: Restore problem

From
"Bob Pawley"
Date:
-----Original Message-----
From: Adrian Klaver
Sent: Tuesday, December 28, 2010 4:21 PM
To: pgsql-general@postgresql.org
Cc: Bob Pawley
Subject: Re: [GENERAL] Restore problem

On Tuesday 28 December 2010 3:06:40 pm Bob Pawley wrote:
> Hi
>
> I have restored a database using psql to windows version 8.4.
>
> During the restore the trigger code became jumbled.
>
> I now have a great number of lines that have moved so that they are now
> included in  lines the have been commented out – not to mention that the
> code is hard to read.

This is in the plain text dump file right?

>
> Is there some way of correcting this – or re restoring the database, so
> that I don’t have to go through the whole code line by line?

With out seeing an example that is going to be difficult :)

>
> Bob



--
Adrian Klaver
adrian.klaver@gmail.com

This is the plain text dump file through pg_admin dump. But the plain text
dump fie through psql restored in the same way.

I don't have a copy of what it was but here is something quite similar to
the style I had before the dump.-


Begin     Drop table if exists size ;
Drop table if exists temp_ ;

Drop table if exists temp1 ;

Drop table  if exists target;

Create table size
( pro_id int4 ,
P_1 float,
P_2 float,
factor float
) ;


create table temp_
( pro_id int4 ,
graphic_id int4 ,
the_geom geometry,
ithe_geom geometry,
othe_geom geometry,
mthe_geom geometry,

ethe_geom geometry,
ip_target geometry,
op_target geometry
);

create table temp1
( id serial unique,
pro_id int4 ,
graphic_id int4 ,
the_geom geometry,
ithe_geom geometry,
othe_geom geometry,
mthe_geom geometry,
ethe_geom geometry,
ip_target geometry,
op_target geometry,
One varchar (5),
Two varchar (5),
Three varchar (5),
Four varchar (5)
);


Following is what it is now. Keep in mind email has word wrap.
(Note ---------1 is a comment out that, without word wrap, comments out a
long line of code.

  DECLARE    process_total integer ;        processid integer ;
procgraphic cursor for select p_id.p_id.process_id

        from  p_id.p_id, processes_count        where p_id.p_id.p_id_id =
processes_count.p_id_id

        order by p_id.p_id.process_id;        begin    Select count
(p_id.p_id.process_id) INTO process_total        FROM p_id.p_id,
processes_count      Where p_id.p_id.p_id_id =
cesses_count.p_id_id;    ------------------1        If process_total = 1
Then            Open procgraphic;             Fetch first from procgraphic
into processid;



         Insert into target (process_id) values (processid) ;

                 Update p_id.p_id        set proc_graphic_position = '1'
where p_id.p_id.process_id = processid;



        Update p_id.p_id

        set process_number = '1'

        where p_id.p_id.process_id = processid;

        Insert into size (P_1, P_2, pro_id)        select
ST_area(st_envelope (graphics.spatial_ref.the_geom)), ST_area(st_envelope(
library.dgm_process.the_geom)),( processid)     from graphics.spatial_ref,
library.dgm_process, p_id.p_id, processes_count    where
graphics.spatial_ref.position_ = p_id.p_id.proc_graphic_position     and
p_id.p_id.process_id = processid    and p_id.p_id.p_id_id =
processes_count.p_id_id    and library.dgm_process.process_number =
p_id.p_id.process_graphic_id;            Update size    Set factor =
sqrt(P_1) / sqrt (P_2) / 3.0    where size.pro_id = processid;     Insert
into temp_(the_geom, ithe_geom, othe_geom, mthe_geom, ethe_geom, ip_target,
op_target, pro_id, graphic_id)    Select st_scale
(library.dgm_process.the_geom, size.factor, size.factor),    st_scale
(library.dgm_process.ithe_geom, size.factor, size.factor),    st_scale
(library.dgm_process.othe_geom, size.factor, size.factor),    st_scale
(library.dgm_process.mthe_geom, size.factor, size.factor),

    st_scale (library.dgm_process.ethe_geom, size.factor, size.factor),
st_scale (library.dgm_process.ip_target, size.factor, size.factor),
st_scale (library.dgm_process.op_target, size.factor, size.factor),
(processid), (p_id.p_id.process_graphic_id)    from library.dgm_process,
graphics.spatial_ref, size, p_id.p_id    Where
graphics.spatial_ref.position_ = p_id.p_id.proc_graphic_position    and
p_id.p_id.process_id = size.pro_id    and size.pro_id = processid    and
library.dgm_process.process_number = p_id.p_id.process_graphic_id;    insert
into temp1 (the_geom, ithe_geom, othe_geom, mthe_geom, ethe_geom, ip_target,
op_target, pro_id, graphic_id)    select st_translate (temp_.the_geom,
st_x (st_centroid(graphics.spatial_ref.the_geom)) -     st_x (st_centroid
(temp_.the_geom)),        st_y (st_centroid(graphics.spatial_ref.the_geom))-
st_y (st_centroid (temp_.the_geom))),    st_translate (temp_.ithe_geom,
st_x (st_centroid(graphics.spatial_ref.the_geom)) -     st_x (st_centroid
(temp_.the_geom)),        st_y (st_centroid(graphics.spatial_ref.the_geom))-
st_y (st_centroid (temp_.the_geom))),    st_translate (temp_.othe_geom,
st_x (st_centroid(graphics.spatial_ref.the_geom)) -     st_x (st_centroid
(temp_.the_geom)),        st_y (st_centroid(graphics.spatial_ref.the_geom))-
st_y (st_centroid (temp_.the_geom))),    st_translate (temp_.mthe_geom,
st_x (st_centroid(graphics.spatial_ref.the_geom)) -     st_x (st_centroid
(temp_.the_geom)),        st_y (st_centroid(graphics.spatial_ref.the_geom))-
st_y (st_centroid (temp_.the_geom))),

    st_translate (temp_.ethe_geom,

    st_x (st_centroid(graphics.spatial_ref.the_geom)) -

    st_x (st_centroid (temp_.the_geom)),

    st_y (st_centroid(graphics.spatial_ref.the_geom))-

    st_y (st_centroid (temp_.the_geom))),    st_translate (temp_.ip_target,
st_x (st_centroid(graphics.spatial_ref.the_geom)) -     st_x (st_centroid
(temp_.the_geom)),        st_y (st_centroid(graphics.spatial_ref.the_geom))-
st_y (st_centroid (temp_.the_geom))),     st_translate (temp_.op_target,
st_x (st_centroid(graphics.spatial_ref.the_geom)) -     st_x (st_centroid
(temp_.the_geom)),        st_y (st_centroid(graphics.spatial_ref.the_geom))-
st_y (st_centroid (temp_.the_geom))),     (processid),
(p_id.p_id.process_graphic_id)    from temp_, graphics.spatial_ref,
p_id.p_id, processes_count    --select extent(the_geom) as the_geom from
temp_) as old_ref    where temp_.pro_id = processid    and
p_id.p_id.process_id = processid    and graphics.spatial_ref.position_ =
p_id.p_id.proc_graphic_position    and  p_id.p_id.p_id_id =
processes_count.p_id_id;

--

Bob


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: Restore problem

From
Adrian Klaver
Date:
On Tuesday 28 December 2010 5:58:51 pm Bob Pawley wrote:
> -----Original Message-----
> From: Adrian Klaver
> Sent: Tuesday, December 28, 2010 4:21 PM
> To: pgsql-general@postgresql.org
> Cc: Bob Pawley
> Subject: Re: [GENERAL] Restore problem
>
> On Tuesday 28 December 2010 3:06:40 pm Bob Pawley wrote:
> > Hi
> >
> > I have restored a database using psql to windows version 8.4.
> >
> > During the restore the trigger code became jumbled.
> >
> > I now have a great number of lines that have moved so that they are now
> > included in  lines the have been commented out – not to mention that the
> > code is hard to read.
>
> This is in the plain text dump file right?
>
> > Is there some way of correcting this – or re restoring the database, so
> > that I don’t have to go through the whole code line by line?
>
> With out seeing an example that is going to be difficult :)
>
> > Bob
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com
>
> This is the plain text dump file through pg_admin dump. But the plain text
> dump fie through psql restored in the same way.

I am not following. psql cannot create a dump file. It can however be used to
restore a plain text dump file created by pg_restore. I think you are going to
need show the steps you took.

>
> I don't have a copy of what it was but here is something quite similar to
> the style I had before the dump.-
>

The restore process does not destroy the input file, it should still be
available.


--
Adrian Klaver
adrian.klaver@gmail.com

Re: Restore problem

From
"Bob Pawley"
Date:
Yes I was just looking at it.

It seems that it was dumped in that form.

Any thoughts on how that could happen?? Not that it will help in this
instance.

Bob

-----Original Message-----
From: Adrian Klaver
Sent: Tuesday, December 28, 2010 6:09 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On Tuesday 28 December 2010 5:58:51 pm Bob Pawley wrote:
> -----Original Message-----
> From: Adrian Klaver
> Sent: Tuesday, December 28, 2010 4:21 PM
> To: pgsql-general@postgresql.org
> Cc: Bob Pawley
> Subject: Re: [GENERAL] Restore problem
>
> On Tuesday 28 December 2010 3:06:40 pm Bob Pawley wrote:
> > Hi
> >
> > I have restored a database using psql to windows version 8.4.
> >
> > During the restore the trigger code became jumbled.
> >
> > I now have a great number of lines that have moved so that they are now
> > included in  lines the have been commented out – not to mention that the
> > code is hard to read.
>
> This is in the plain text dump file right?
>
> > Is there some way of correcting this – or re restoring the database, so
> > that I don’t have to go through the whole code line by line?
>
> With out seeing an example that is going to be difficult :)
>
> > Bob
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com
>
> This is the plain text dump file through pg_admin dump. But the plain text
> dump fie through psql restored in the same way.

I am not following. psql cannot create a dump file. It can however be used
to
restore a plain text dump file created by pg_restore. I think you are going
to
need show the steps you took.

>
> I don't have a copy of what it was but here is something quite similar to
> the style I had before the dump.-
>

The restore process does not destroy the input file, it should still be
available.


--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: Restore problem

From
"Bob Pawley"
Date:

-----Original Message-----
From: Adrian Klaver
Sent: Tuesday, December 28, 2010 6:09 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On Tuesday 28 December 2010 5:58:51 pm Bob Pawley wrote:
> -----Original Message-----
> From: Adrian Klaver
> Sent: Tuesday, December 28, 2010 4:21 PM
> To: pgsql-general@postgresql.org
> Cc: Bob Pawley
> Subject: Re: [GENERAL] Restore problem
>
> On Tuesday 28 December 2010 3:06:40 pm Bob Pawley wrote:
> > Hi
> >
> > I have restored a database using psql to windows version 8.4.
> >
> > During the restore the trigger code became jumbled.
> >
> > I now have a great number of lines that have moved so that they are now
> > included in  lines the have been commented out – not to mention that the
> > code is hard to read.
>
> This is in the plain text dump file right?
>
> > Is there some way of correcting this – or re restoring the database, so
> > that I don’t have to go through the whole code line by line?
>
> With out seeing an example that is going to be difficult :)
>
> > Bob
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com
>
> This is the plain text dump file through pg_admin dump. But the plain text
> dump fie through psql restored in the same way.

I am not following. psql cannot create a dump file. It can however be used
to
restore a plain text dump file created by pg_restore. I think you are going
to
need show the steps you took.

I used PGAdmin to dump the June version and pg_dump mydb > db.sql to dump
the May version.

Both came out with the same problems.

Bob

>
> I don't have a copy of what it was but here is something quite similar to
> the style I had before the dump.-
>

The restore process does not destroy the input file, it should still be
available.


--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: Restore problem

From
Adrian Klaver
Date:
On Tuesday 28 December 2010 6:41:51 pm Bob Pawley wrote:

> > > Bob
> >
> > --
> > Adrian Klaver
> > adrian.klaver@gmail.com
> >
> > This is the plain text dump file through pg_admin dump. But the plain
> > text dump fie through psql restored in the same way.
>
> I am not following. psql cannot create a dump file. It can however be used
> to
> restore a plain text dump file created by pg_restore. I think you are going
> to
> need show the steps you took.
>
> I used PGAdmin to dump the June version and pg_dump mydb > db.sql to dump
> the May version.
>
> Both came out with the same problems.
>
> Bob

What program are you using to look at the plain text file?

--
Adrian Klaver
adrian.klaver@gmail.com

Re: Restore problem

From
"Bob Pawley"
Date:

-----Original Message-----
From: Adrian Klaver
Sent: Tuesday, December 28, 2010 6:51 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On Tuesday 28 December 2010 6:41:51 pm Bob Pawley wrote:

> > > Bob
> >
> > --
> > Adrian Klaver
> > adrian.klaver@gmail.com
> >
> > This is the plain text dump file through pg_admin dump. But the plain
> > text dump fie through psql restored in the same way.
>
> I am not following. psql cannot create a dump file. It can however be used
> to
> restore a plain text dump file created by pg_restore. I think you are
> going
> to
> need show the steps you took.
>
> I used PGAdmin to dump the June version and pg_dump mydb > db.sql to dump
> the May version.
>
> Both came out with the same problems.
>
> Bob

What program are you using to look at the plain text file?


Notepad

Bob

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: Restore problem

From
Adrian Klaver
Date:
On 12/28/2010 07:05 PM, Bob Pawley wrote:
>
>
> -----Original Message----- From: Adrian Klaver
> Sent: Tuesday, December 28, 2010 6:51 PM
> To: Bob Pawley
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Restore problem
>
> On Tuesday 28 December 2010 6:41:51 pm Bob Pawley wrote:
>
>> > > Bob
>> >
>> > --
>> > Adrian Klaver
>> > adrian.klaver@gmail.com
>> >
>> > This is the plain text dump file through pg_admin dump. But the plain
>> > text dump fie through psql restored in the same way.
>>
>> I am not following. psql cannot create a dump file. It can however be
>> used
>> to
>> restore a plain text dump file created by pg_restore. I think you are
>> going
>> to
>> need show the steps you took.
>>
>> I used PGAdmin to dump the June version and pg_dump mydb > db.sql to dump
>> the May version.
>>
>> Both came out with the same problems.
>>
>> Bob
>
> What program are you using to look at the plain text file?
>
>
> Notepad
>
> Bob
>

Open the file in Wordpad and see if it looks better.
--
Adrian Klaver
adrian.klaver@gmail.com

Re: Restore problem

From
"Bob Pawley"
Date:

-----Original Message-----
From: Adrian Klaver
Sent: Tuesday, December 28, 2010 7:06 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On 12/28/2010 07:05 PM, Bob Pawley wrote:
>
>
> -----Original Message----- From: Adrian Klaver
> Sent: Tuesday, December 28, 2010 6:51 PM
> To: Bob Pawley
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Restore problem
>
> On Tuesday 28 December 2010 6:41:51 pm Bob Pawley wrote:
>
>> > > Bob
>> >
>> > --
>> > Adrian Klaver
>> > adrian.klaver@gmail.com
>> >
>> > This is the plain text dump file through pg_admin dump. But the plain
>> > text dump fie through psql restored in the same way.
>>
>> I am not following. psql cannot create a dump file. It can however be
>> used
>> to
>> restore a plain text dump file created by pg_restore. I think you are
>> going
>> to
>> need show the steps you took.
>>
>> I used PGAdmin to dump the June version and pg_dump mydb > db.sql to dump
>> the May version.
>>
>> Both came out with the same problems.
>>
>> Bob
>
> What program are you using to look at the plain text file?
>
>
> Notepad
>
> Bob
>

Open the file in Wordpad and see if it looks better.

It looks the same.

Bob
--
Adrian Klaver
adrian.klaver@gmail.com


Re: Restore problem

From
"Bob Pawley"
Date:

-----Original Message-----
From: Adrian Klaver
Sent: Tuesday, December 28, 2010 7:06 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On 12/28/2010 07:05 PM, Bob Pawley wrote:
>
>
> -----Original Message----- From: Adrian Klaver
> Sent: Tuesday, December 28, 2010 6:51 PM
> To: Bob Pawley
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Restore problem
>
> On Tuesday 28 December 2010 6:41:51 pm Bob Pawley wrote:
>
>> > > Bob
>> >
>> > --
>> > Adrian Klaver
>> > adrian.klaver@gmail.com
>> >
>> > This is the plain text dump file through pg_admin dump. But the plain
>> > text dump fie through psql restored in the same way.
>>
>> I am not following. psql cannot create a dump file. It can however be
>> used
>> to
>> restore a plain text dump file created by pg_restore. I think you are
>> going
>> to
>> need show the steps you took.
>>
>> I used PGAdmin to dump the June version and pg_dump mydb > db.sql to dump
>> the May version.
>>
>> Both came out with the same problems.
>>
>> Bob
>
> What program are you using to look at the plain text file?
>
>
> Notepad
>
> Bob
>

Open the file in Wordpad and see if it looks better.

I downloaded an sql editor and it looks the same in it as well.

At least the editor will make it easier to fix the problem. However I would
like to know what happened so I can avoid it in the future.

Is the compressed file a better way to dump??

Bob
--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: Restore problem

From
Adrian Klaver
Date:
On 12/28/2010 07:16 PM, Bob Pawley wrote:
>
>
> -----Original Message----- From: Adrian Klaver
> Sent: Tuesday, December 28, 2010 7:06 PM
> To: Bob Pawley
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Restore problem
>
> On 12/28/2010 07:05 PM, Bob Pawley wrote:
>>
>>
>> -----Original Message----- From: Adrian Klaver
>> Sent: Tuesday, December 28, 2010 6:51 PM
>> To: Bob Pawley
>> Cc: pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] Restore problem
>>
>> On Tuesday 28 December 2010 6:41:51 pm Bob Pawley wrote:
>>
>>> > > Bob
>>> >
>>> > --
>>> > Adrian Klaver
>>> > adrian.klaver@gmail.com
>>> >
>>> > This is the plain text dump file through pg_admin dump. But the plain
>>> > text dump fie through psql restored in the same way.
>>>
>>> I am not following. psql cannot create a dump file. It can however be
>>> used
>>> to
>>> restore a plain text dump file created by pg_restore. I think you are
>>> going
>>> to
>>> need show the steps you took.
>>>
>>> I used PGAdmin to dump the June version and pg_dump mydb > db.sql to
>>> dump
>>> the May version.
>>>
>>> Both came out with the same problems.
>>>
>>> Bob
>>
>> What program are you using to look at the plain text file?
>>
>>
>> Notepad
>>
>> Bob
>>
>
> Open the file in Wordpad and see if it looks better.
>
> It looks the same.
>
> Bob

Well there goes that theory. Notepad is almost useless as a text editor
and is known for not wrapping lines correctly.

--
Adrian Klaver
adrian.klaver@gmail.com

Re: Restore problem

From
Adrian Klaver
Date:
On 12/28/2010 07:27 PM, Bob Pawley wrote:
>
>
> -----Original Message----- From: Adrian Klaver
> Sent: Tuesday, December 28, 2010 7:06 PM
> To: Bob Pawley
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Restore problem
>
> On 12/28/2010 07:05 PM, Bob Pawley wrote:
>>
>>
>> -----Original Message----- From: Adrian Klaver
>> Sent: Tuesday, December 28, 2010 6:51 PM
>> To: Bob Pawley
>> Cc: pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] Restore problem
>>
>> On Tuesday 28 December 2010 6:41:51 pm Bob Pawley wrote:
>>
>>> > > Bob
>>> >
>>> > --
>>> > Adrian Klaver
>>> > adrian.klaver@gmail.com
>>> >
>>> > This is the plain text dump file through pg_admin dump. But the plain
>>> > text dump fie through psql restored in the same way.
>>>
>>> I am not following. psql cannot create a dump file. It can however be
>>> used
>>> to
>>> restore a plain text dump file created by pg_restore. I think you are
>>> going
>>> to
>>> need show the steps you took.
>>>
>>> I used PGAdmin to dump the June version and pg_dump mydb > db.sql to
>>> dump
>>> the May version.
>>>
>>> Both came out with the same problems.
>>>
>>> Bob
>>
>> What program are you using to look at the plain text file?
>>
>>
>> Notepad
>>
>> Bob
>>
>
> Open the file in Wordpad and see if it looks better.
>
> I downloaded an sql editor and it looks the same in it as well.
>
> At least the editor will make it easier to fix the problem. However I
> would like to know what happened so I can avoid it in the future.

I am not sure. If the file is not to big and you wish you can send it to
me off list and maybe I can figure out what is going on.

>
> Is the compressed file a better way to dump??

Yes in this case because you can do a restore from within pgAdmin.
>
> Bob


--
Adrian Klaver
adrian.klaver@gmail.com

Re: Restore problem

From
"Bob Pawley"
Date:

-----Original Message-----
From: Adrian Klaver
Sent: Tuesday, December 28, 2010 7:33 PM
To: Bob Pawley
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On 12/28/2010 07:27 PM, Bob Pawley wrote:
>
>
> -----Original Message----- From: Adrian Klaver
> Sent: Tuesday, December 28, 2010 7:06 PM
> To: Bob Pawley
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Restore problem
>
> On 12/28/2010 07:05 PM, Bob Pawley wrote:
>>
>>
>> -----Original Message----- From: Adrian Klaver
>> Sent: Tuesday, December 28, 2010 6:51 PM
>> To: Bob Pawley
>> Cc: pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] Restore problem
>>
>> On Tuesday 28 December 2010 6:41:51 pm Bob Pawley wrote:
>>
>>> > > Bob
>>> >
>>> > --
>>> > Adrian Klaver
>>> > adrian.klaver@gmail.com
>>> >
>>> > This is the plain text dump file through pg_admin dump. But the plain
>>> > text dump fie through psql restored in the same way.
>>>
>>> I am not following. psql cannot create a dump file. It can however be
>>> used
>>> to
>>> restore a plain text dump file created by pg_restore. I think you are
>>> going
>>> to
>>> need show the steps you took.
>>>
>>> I used PGAdmin to dump the June version and pg_dump mydb > db.sql to
>>> dump
>>> the May version.
>>>
>>> Both came out with the same problems.
>>>
>>> Bob
>>
>> What program are you using to look at the plain text file?
>>
>>
>> Notepad
>>
>> Bob
>>
>
> Open the file in Wordpad and see if it looks better.
>
> I downloaded an sql editor and it looks the same in it as well.
>
> At least the editor will make it easier to fix the problem. However I
> would like to know what happened so I can avoid it in the future.

I am not sure. If the file is not to big and you wish you can send it to
me off list and maybe I can figure out what is going on.

The file is over 9 meg - way to large for me to e-mail.

It seems that this has affected just  the triggers - although that is quite
massive I will just plug away at it until it's done

Thanks

Bob

>
> Is the compressed file a better way to dump??

Yes in this case because you can do a restore from within pgAdmin.
>
> Bob


--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: Restore problem

From
Adrian Klaver
Date:
On 12/28/2010 07:40 PM, Bob Pawley wrote:
>
>> Open the file in Wordpad and see if it looks better.
>>
>> I downloaded an sql editor and it looks the same in it as well.
>>
>> At least the editor will make it easier to fix the problem. However I
>> would like to know what happened so I can avoid it in the future.
>
> I am not sure. If the file is not to big and you wish you can send it to
> me off list and maybe I can figure out what is going on.
>
> The file is over 9 meg - way to large for me to e-mail.
>
> It seems that this has affected just the triggers - although that is
> quite massive I will just plug away at it until it's done
>
> Thanks
>
> Bob

The triggers or functions? The sample you showed was from a function. My
suspicion is that this is a line ending problem
(http://en.wikipedia.org/wiki/Newline) and is a matter of finding the
correct conversion utility.

--
Adrian Klaver
adrian.klaver@gmail.com

Re: Restore problem

From
Alan Hodgson
Date:
On December 28, 2010, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> On 12/28/2010 07:40 PM, Bob Pawley wrote:
> >> Open the file in Wordpad and see if it looks better.
> >>
> >> I downloaded an sql editor and it looks the same in it as well.
> >>
> >> At least the editor will make it easier to fix the problem. However I
> >> would like to know what happened so I can avoid it in the future.
> >

It's often a good idea to maintain function definitions outside the database,
under version control, and apply them to the database from there.

Also, try a unix2dos utility on the text of the functions before giving up
and hand editing them.

--
A hybrid Escalade is missing the point much in the same way that having a
diet soda with your extra large pepperoni pizza is missing the point.

Re: Restore problem

From
"Bob Pawley"
Date:

-----Original Message-----
From: Alan Hodgson
Sent: Tuesday, December 28, 2010 8:12 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On December 28, 2010, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> On 12/28/2010 07:40 PM, Bob Pawley wrote:
> >> Open the file in Wordpad and see if it looks better.
> >>
> >> I downloaded an sql editor and it looks the same in it as well.
> >>
> >> At least the editor will make it easier to fix the problem. However I
> >> would like to know what happened so I can avoid it in the future.
> >

It's often a good idea to maintain function definitions outside the
database,
under version control, and apply them to the database from there.

I would appreciate a more detailed explanation of this.

Bob

Also, try a unix2dos utility on the text of the functions before giving up
and hand editing them.

I'll look at that - I'm also looking at something called Vim
http://www.vim.org/download.php

Bob


--
A hybrid Escalade is missing the point much in the same way that having a
diet soda with your extra large pepperoni pizza is missing the point.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: Restore problem

From
Scott Marlowe
Date:
On Tue, Dec 28, 2010 at 8:05 PM, Bob Pawley <rjpawley@shaw.ca> wrote:
> What program are you using to look at the plain text file?
>
>
> Notepad

Did you at some point open the backup file with notepad, make a change
and then save it?  If so notepad may have permanently mangled the
backup.  If so, do you have an original unedited copy of the backup to
go to.  If not, then I'm out of ideas.

Re: Restore problem

From
Alan Hodgson
Date:
On December 28, 2010, "Bob Pawley" <rjpawley@shaw.ca> wrote:
> It's often a good idea to maintain function definitions outside the
> database,
> under version control, and apply them to the database from there.
>
> I would appreciate a more detailed explanation of this.

Treat them like source code.

>
> Bob
>
> Also, try a unix2dos utility on the text of the functions before giving
> up and hand editing them.
>
> I'll look at that - I'm also looking at something called Vim
> http://www.vim.org/download.php

vim is an excellent open source text editor. Which may fix your problem if
it's related to line endings.

--
A hybrid Escalade is missing the point much in the same way that having a
diet soda with your extra large pepperoni pizza is missing the point.

Re: Restore problem

From
Jasen Betts
Date:
On 2010-12-29, Bob Pawley <rjpawley@shaw.ca> wrote:
> Yes I was just looking at it.
>
> It seems that it was dumped in that form.
>
> Any thoughts on how that could happen?? Not that it will help in this
> instance.

could be EOL problem.  LF vs CRLF
but I expect that would be merely cosmetic.

Re: Restore problem

From
Alban Hertroys
Date:
On 29 Dec 2010, at 4:29, Adrian Klaver wrote:
>>> What program are you using to look at the plain text file?
>>>
>>>
>>> Notepad
>>>
>>> Bob
>>>
>>
>> Open the file in Wordpad and see if it looks better.
>>
>> It looks the same.
>>
>> Bob
>
> Well there goes that theory. Notepad is almost useless as a text editor and is known for not wrapping lines
correctly.


I thought I knew what you were going to say here, namely that notepad can't handle newlines that are not CRLF, but just
CRor LF. Bob obviously ran into a problem like that. 
I didn't know about any problems with wrapping, or is the newline problem what you were referring to?

I'm glad they fixed its 64kB file size limit though - about time!

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4d1b2275802656600811020!



Re: Restore problem

From
Alban Hertroys
Date:
On 29 Dec 2010, at 4:40, Bob Pawley wrote:
> It seems that this has affected just  the triggers - although that is quite massive I will just plug away at it until
it'sdone 


(Gosh, those lines were hard to find!)

How did you create those functions? With notepad, or from within pgadmin? If you look at the function bodies as they
arein the database, are their line-endings correct? 
It's possible that the error occurred as early as that.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4d1b2392802653260212710!



Re: Restore problem

From
Alban Hertroys
Date:
On 29 Dec 2010, at 7:54, Alan Hodgson wrote:

>> I'll look at that - I'm also looking at something called Vim
>> http://www.vim.org/download.php
>
> vim is an excellent open source text editor. Which may fix your problem if
> it's related to line endings.


Learning Vim is probably time well-spent, but until you do it's probably not that good a tool for fixing your problem.

Although Vim is indeed a very powerful editor, it's not particularly easy to use. Unlike your usual editors like
Notepadand friends, it's a command-based editor, meaning you have to execute a command before you can input or change
data.It's an entirely different paradigm than what you're probably used to (I may assume wrongly here). 

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4d1b2723802651509919126!



Re: Restore problem

From
Leif Biberg Kristensen
Date:
On Wednesday 29. December 2010 13.18.40 Alban Hertroys wrote:

> Learning Vim is probably time well-spent, but until you do it's
probably not that good a tool for fixing your problem.
>
> Although Vim is indeed a very powerful editor, it's not particularly
easy to use. Unlike your usual editors like Notepad and friends, it's a
command-based editor, meaning you have to execute a command before you
can input or change data. It's an entirely different paradigm than what
you're probably used to (I may assume wrongly here).

Back when I used Windows, my favorite editor was EditPlus
(http://www.editplus.com/). It isn't free, but well worth the 35 bucks.

As a rather casual coder, I'm very satisfied with the simple editor
Kwrite in KDE. It's a sheer delight compared to Notepad.

regards, Leif

Re: Restore problem

From
Adrian Klaver
Date:
On Wednesday 29 December 2010 3:58:35 am Alban Hertroys wrote:
> On 29 Dec 2010, at 4:29, Adrian Klaver wrote:
> >>> What program are you using to look at the plain text file?
> >>>
> >>>
> >>> Notepad
> >>>
> >>> Bob
> >>
> >> Open the file in Wordpad and see if it looks better.
> >>
> >> It looks the same.
> >>
> >> Bob
> >
> > Well there goes that theory. Notepad is almost useless as a text editor
> > and is known for not wrapping lines correctly.
>
> I thought I knew what you were going to say here, namely that notepad can't
> handle newlines that are not CRLF, but just CR or LF. Bob obviously ran
> into a problem like that. I didn't know about any problems with wrapping,
> or is the newline problem what you were referring to?

Yes it was the newline problem.

>
> I'm glad they fixed its 64kB file size limit though - about time!



>
> Alban Hertroys
>
> --
> If you can't see the forest for the trees,
> cut the trees and you'll see there is no forest.
>
>
> !DSPAM:1104,4d1b2271802651880367148!



--
Adrian Klaver
adrian.klaver@gmail.com

Re: Restore problem

From
Adrian Klaver
Date:
On Tuesday 28 December 2010 8:45:14 pm Bob Pawley wrote:
> -----Original Message-----
> From: Alan Hodgson
> Sent: Tuesday, December 28, 2010 8:12 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Restore problem
>
> On December 28, 2010, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> > On 12/28/2010 07:40 PM, Bob Pawley wrote:
> > >> Open the file in Wordpad and see if it looks better.
> > >>
> > >> I downloaded an sql editor and it looks the same in it as well.
> > >>
> > >> At least the editor will make it easier to fix the problem. However I
> > >> would like to know what happened so I can avoid it in the future.
>
> It's often a good idea to maintain function definitions outside the
> database,
> under version control, and apply them to the database from there.
>
> I would appreciate a more detailed explanation of this.

Version control is a good way of handling incremental updates to function
definitions when making changes to a live database. Also if the problem is one
of incompatible line endings than version control is not necessarily a
solution, you would just end up with multiple versions of the same problem:)
The point of a database dump is to capture the state of a database at a point
in time and recreate it, sort of a poor mans version control in itself.

>
> Bob
>




--
Adrian Klaver
adrian.klaver@gmail.com

Re: Restore problem

From
Adrian Klaver
Date:
On Wednesday 29 December 2010 4:34:39 am Leif Biberg Kristensen wrote:
> On Wednesday 29. December 2010 13.18.40 Alban Hertroys wrote:
> > Learning Vim is probably time well-spent, but until you do it's
>
> probably not that good a tool for fixing your problem.
>
> > Although Vim is indeed a very powerful editor, it's not particularly
>
> easy to use. Unlike your usual editors like Notepad and friends, it's a
> command-based editor, meaning you have to execute a command before you
> can input or change data. It's an entirely different paradigm than what
> you're probably used to (I may assume wrongly here).
>
> Back when I used Windows, my favorite editor was EditPlus
> (http://www.editplus.com/). It isn't free, but well worth the 35 bucks.
>
> As a rather casual coder, I'm very satisfied with the simple editor
> Kwrite in KDE. It's a sheer delight compared to Notepad.
>
> regards, Leif

Another choice is Jedit(http://jedit.org/). It is written in Java so you will
need that installed. It has a graphical interface so the learning curve is
short.

--
Adrian Klaver
adrian.klaver@gmail.com

Re: Restore problem

From
"Bob Pawley"
Date:

-----Original Message-----
From: Alban Hertroys
Sent: Wednesday, December 29, 2010 4:03 AM
To: Bob Pawley
Cc: Adrian Klaver ; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore problem

On 29 Dec 2010, at 4:40, Bob Pawley wrote:
> It seems that this has affected just  the triggers - although that is
> quite massive I will just plug away at it until it's done


(Gosh, those lines were hard to find!)

How did you create those functions? With notepad, or from within pgadmin? If
you look at the function bodies as they are in the database, are their
line-endings correct?
It's possible that the error occurred as early as that.

Alban Hertroys

The code example I sent has been dumped and restored numerous times and yes
it was created in PGAdmin.

This dump was from version 8.3 if that means anything.

Bob

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:1208,4d1b2395802657602216958!


Re: Restore problem

From
"Bob Pawley"
Date:

-----Original Message-----
From: Adrian Klaver
Sent: Wednesday, December 29, 2010 8:08 AM
To: pgsql-general@postgresql.org
Cc: Leif Biberg Kristensen
Subject: Re: [GENERAL] Restore problem

On Wednesday 29 December 2010 4:34:39 am Leif Biberg Kristensen wrote:
> On Wednesday 29. December 2010 13.18.40 Alban Hertroys wrote:
> > Learning Vim is probably time well-spent, but until you do it's
>
> probably not that good a tool for fixing your problem.
>
> > Although Vim is indeed a very powerful editor, it's not particularly
>
> easy to use. Unlike your usual editors like Notepad and friends, it's a
> command-based editor, meaning you have to execute a command before you
> can input or change data. It's an entirely different paradigm than what
> you're probably used to (I may assume wrongly here).
>
> Back when I used Windows, my favorite editor was EditPlus
> (http://www.editplus.com/). It isn't free, but well worth the 35 bucks.
>
> As a rather casual coder, I'm very satisfied with the simple editor
> Kwrite in KDE. It's a sheer delight compared to Notepad.
>
> regards, Leif

Another choice is Jedit(http://jedit.org/). It is written in Java so you
will
need that installed. It has a graphical interface so the learning curve is
short.

JEdit shows that numerous ends of line are missing.

I suppose manual recover is the only possibility??

Other than PostgreSQL version 8.3, the only other change from previous dumps
(Win XP) is my Windows 7 edition.

I know I have been having problems with firewall permissions in Win 7 during
install and uninstall of PostgreSQL.

Bob

--
Adrian Klaver
adrian.klaver@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: Restore problem

From
John R Pierce
Date:
On 12/29/10 4:34 AM, Leif Biberg Kristensen wrote:
> Back when I used Windows, my favorite editor was EditPlus
> (http://www.editplus.com/). It isn't free, but well worth the 35 bucks.

other good choices are Notepad++ (free) and my personal favorite,
UltraEdit ($$).

UEdit has some nice stuff like being able to load/save directly from
FTP, unix2dos/dos2unix built in (and it is perfectly happy editing
native unix format files), rather powerful macros, column select, etc.



Re: Restore problem

From
Adrian Klaver
Date:
On Wednesday 29 December 2010 10:52:50 am Bob Pawley wrote:
> -----Original Message-----
> From: Adrian Klaver
> Sent: Wednesday, December 29, 2010 8:08 AM
> To: pgsql-general@postgresql.org
> Cc: Leif Biberg Kristensen
> Subject: Re: [GENERAL] Restore problem
>
> On Wednesday 29 December 2010 4:34:39 am Leif Biberg Kristensen wrote:
> > On Wednesday 29. December 2010 13.18.40 Alban Hertroys wrote:
> > > Learning Vim is probably time well-spent, but until you do it's
> >
> > probably not that good a tool for fixing your problem.
> >
> > > Although Vim is indeed a very powerful editor, it's not particularly
> >
> > easy to use. Unlike your usual editors like Notepad and friends, it's a
> > command-based editor, meaning you have to execute a command before you
> > can input or change data. It's an entirely different paradigm than what
> > you're probably used to (I may assume wrongly here).
> >
> > Back when I used Windows, my favorite editor was EditPlus
> > (http://www.editplus.com/). It isn't free, but well worth the 35 bucks.
> >
> > As a rather casual coder, I'm very satisfied with the simple editor
> > Kwrite in KDE. It's a sheer delight compared to Notepad.
> >
> > regards, Leif
>
> Another choice is Jedit(http://jedit.org/). It is written in Java so you
> will
> need that installed. It has a graphical interface so the learning curve is
> short.
>
> JEdit shows that numerous ends of line are missing.
>
> I suppose manual recover is the only possibility??

I know you said the plain text dump file was 9 megs and was too big to email.
Could you try zipping it or send me a smaller portion(cut and paste) off list.

>
> Other than PostgreSQL version 8.3, the only other change from previous
> dumps (Win XP) is my Windows 7 edition.
>
> I know I have been having problems with firewall permissions in Win 7
> during install and uninstall of PostgreSQL.

I do not use Windows enough to be of help here.

>
> Bob
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com



--
Adrian Klaver
adrian.klaver@gmail.com

Re: Restore problem

From
"Tim Bruce - Postgres"
Date:
On Wed, December 29, 2010 10:59, John R Pierce wrote:
> On 12/29/10 4:34 AM, Leif Biberg Kristensen wrote:
>> Back when I used Windows, my favorite editor was EditPlus
>> (http://www.editplus.com/). It isn't free, but well worth the 35 bucks.
>
> other good choices are Notepad++ (free) and my personal favorite,
> UltraEdit ($$).
>
> UEdit has some nice stuff like being able to load/save directly from
> FTP, unix2dos/dos2unix built in (and it is perfectly happy editing
> native unix format files), rather powerful macros, column select, etc.
>
>

I'd also like to throw in Context for Windows as an Editor.  It's also
free and has syntax highlighting for almost everything imaginable (on
Windows and *ix).
--
Timothy J. Bruce



Re: Restore problem

From
Vincent Veyron
Date:
Le mercredi 29 décembre 2010 à 11:09 -0800, Tim Bruce - Postgres a
écrit :
> On Wed, December 29, 2010 10:59, John R Pierce wrote:

> I'd also like to throw in Context for Windows as an Editor.  It's also
> free and has syntax highlighting for almost everything imaginable (on
> Windows and *ix).


I'm partial to Emacs, but I'm surprised nobody mentionned Abiword :

http://www.abisource.com/


--
Vincent Veyron
http://marica.fr/
Progiciel de gestion des dossiers de contentieux et d'assurance pour le service juridique


Re: Restore problem

From
Andrew Sullivan
Date:
On Thu, Dec 30, 2010 at 06:02:54PM +0100, Vincent Veyron wrote:
>
> I'm partial to Emacs, but I'm surprised nobody mentionned Abiword :
>
> http://www.abisource.com/

I think Abiword would be a very bad editor for any kind of database
work, no?  It's intended as a word processor rather than a text
editor, isn't it?

A

--
Andrew Sullivan
ajs@crankycanuck.ca

Re: Restore problem

From
Vincent Veyron
Date:
Le jeudi 30 décembre 2010 à 12:05 -0500, Andrew Sullivan a écrit :

[about Abiword]

> It's intended as a word processor rather than a text
> editor, isn't it?

It works with text files too. It's not a problem.

--
Vincent Veyron
http://marica.fr/
Progiciel de gestion des dossiers de contentieux et d'assurance pour le service juridique