Re: [GENERAL] Restore problem
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
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 a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
On 2010-12-29, Bob Pawley rjpaw...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
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? 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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4d1b2392802653260212710! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
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 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). 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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
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.kla...@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: [GENERAL] Restore problem
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.kla...@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.kla...@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: [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. -- Adrian Klaver adrian.kla...@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: [GENERAL] Restore problem
-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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
-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.kla...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
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.kla...@gmail.com -- Adrian Klaver adrian.kla...@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: [GENERAL] Restore problem
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Restore problem
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: [GENERAL] Restore problem
On Tue, Dec 28, 2010 at 6:06 PM, Bob Pawley rjpaw...@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.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
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.kla...@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: [GENERAL] Restore problem
-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.kla...@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. DECLAREprocess_total integer ;processid integer ; procgraphic cursor for select p_id.p_id.process_id from p_id.p_id, processes_countwhere p_id.p_id.p_id_id = processes_count.p_id_id order by p_id.p_id.process_id;beginSelect count (p_id.p_id.process_id) INTO process_totalFROM p_id.p_id, processes_count Where p_id.p_id.p_id_id = cesses_count.p_id_id;--1If process_total = 1 ThenOpen procgraphic; Fetch first from procgraphic into processid; Insert into target (process_id) values (processid) ; Update p_id.p_idset 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_countwhere graphics.spatial_ref.position_ = p_id.p_id.proc_graphic_position and p_id.p_id.process_id = processidand p_id.p_id.p_id_id = processes_count.p_id_idand library.dgm_process.process_number = p_id.p_id.process_graphic_id;Update sizeSet factor = sqrt(P_1) / sqrt (P_2) / 3.0where 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_idWhere graphics.spatial_ref.position_ = p_id.p_id.proc_graphic_positionand p_id.p_id.process_id = size.pro_idand size.pro_id = processidand 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
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.kla...@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.kla...@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: [GENERAL] Restore problem
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.kla...@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.kla...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
-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.kla...@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.kla...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
On Tuesday 28 December 2010 6:41:51 pm Bob Pawley wrote: Bob -- Adrian Klaver adrian.kla...@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.kla...@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: [GENERAL] Restore problem
-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.kla...@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.kla...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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.kla...@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.kla...@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: [GENERAL] Restore problem
-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.kla...@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.kla...@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: [GENERAL] Restore problem
-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.kla...@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.kla...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
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.kla...@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.kla...@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: [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.kla...@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.kla...@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: [GENERAL] Restore problem
-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.kla...@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.kla...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
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.kla...@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: [GENERAL] Restore problem
On December 28, 2010, Adrian Klaver adrian.kla...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
-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.kla...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
On Tue, Dec 28, 2010 at 8:05 PM, Bob Pawley rjpaw...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
On December 28, 2010, Bob Pawley rjpaw...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: [GENERAL] Restore problem from 8.4 backup to 9.0
Adam Wizon adamwi...@mac.com writes: Thanks for the fast reply. I must have still been connected to the older database somehow. I cleaned up my installation and restored the database. No error messages this time. I need to change the pg_hba.conf file. I read the documentation and its supposed to be in the data directory (which is locked), but it doesn't seem to be there. Is there an easy way to create the file in the data directory (without overriding access privileges) at this point? That's where it would normally be, but try show hba_file; if you think the packager of your distribution put it somewhere else. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: [GENERAL] Restore problem from 8.4 backup to 9.0
Where would I type 'show hba_file'? I'm on Mac. Also I downloaded the distribution from postgresql.org. If I run an initdb and I already restored a database, will it put the pg_hba.conf file in the 'data' folder? Subject: Re: Fwd: [GENERAL] Restore problem from 8.4 backup to 9.0 Adam Wizon adamwi...@mac.com writes: Thanks for the fast reply. I must have still been connected to the older database somehow. I cleaned up my installation and restored the database. No error messages this time. I need to change the pg_hba.conf file. I read the documentation and its supposed to be in the data directory (which is locked), but it doesn't seem to be there. Is there an easy way to create the file in the data directory (without overriding access privileges) at this point? That's where it would normally be, but try show hba_file; if you think the packager of your distribution put it somewhere else. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: [GENERAL] Restore problem from 8.4 backup to 9.0
If I run the admin tool from my postgres account, that works fine since postgres is the owner. Cc: pgsql-general@postgresql.org Subject: Re: Fwd: [GENERAL] Restore problem from 8.4 backup to 9.0 Thanks for the fast reply. I must have still been connected to the older database somehow. I cleaned up my installation and restored the database. No error messages this time. I need to change the pg_hba.conf file. I read the documentation and its supposed to be in the data directory (which is locked), but it doesn't seem to be there. Is there an easy way to create the file in the data directory (without overriding access privileges) at this point? That's where it would normally be, but try show hba_file; if you think the packager of your distribution put it somewhere else. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Restore problem from 8.4 backup to 9.0
I installed v9.0 on my Mac Pro. Dumped the 8.4 database using 'pg_dump -Fc -d dbname --username=xyz backup_file_name' using the pg_dump from the 8.4 installation. I restored the database using 'pg_restore -d dbname backup_file_name' using the 9.0 restore and after creating a new database under 9.0. Under version 9.0 the database looks ok, but I had a lot of the following errors (132) during the restore: pg_restore: [archiver (db)] could not execute query: ERROR: relation xyz already exists ... pg_restore: [archiver (db)] could not execute query: ERROR: multiple primary keys for pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key value violates unique constraint ... I tried restoring the 8.4 restore file to a new 8.4 database and there were no errors. It almost looks like the schema already existed when I went to do the restore, but I was careful to create the new database in the admin tool under the correct server. I thought the admin tool created the new database in the data folder of the selected installation. Any ideas on what might have happened? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem from 8.4 backup to 9.0
Adam Wizon adamwi...@mac.com writes: I installed v9.0 on my Mac Pro. Dumped the 8.4 database using 'pg_dump -Fc -d dbname --username=xyz backup_file_name' using the pg_dump from the 8.4 installation. I restored the database using 'pg_restore -d dbname backup_file_name' using the 9.0 restore and after creating a new database under 9.0. Under version 9.0 the database looks ok, but I had a lot of the following errors (132) during the restore: pg_restore: [archiver (db)] could not execute query: ERROR: relation xyz already exists ... pg_restore: [archiver (db)] could not execute query: ERROR: multiple primary keys for pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key value violates unique constraint ... I tried restoring the 8.4 restore file to a new 8.4 database and there were no errors. It almost looks like the schema already existed when I went to do the restore, but I was careful to create the new database in the admin tool under the correct server. Restoring twice is almost certainly the explanation. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: [GENERAL] Restore problem from 8.4 backup to 9.0
Thanks for the fast reply. I must have still been connected to the older database somehow. I cleaned up my installation and restored the database. No error messages this time. I need to change the pg_hba.conf file. I read the documentation and its supposed to be in the data directory (which is locked), but it doesn't seem to be there. Is there an easy way to create the file in the data directory (without overriding access privileges) at this point? Begin forwarded message: From: Tom Lane t...@sss.pgh.pa.us Date: September 28, 2010 9:58:10 PM EDT To: Adam Wizon adamwi...@mac.com Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Restore problem from 8.4 backup to 9.0 Adam Wizon adamwi...@mac.com writes: I installed v9.0 on my Mac Pro. Dumped the 8.4 database using 'pg_dump -Fc -d dbname --username=xyz backup_file_name' using the pg_dump from the 8.4 installation. I restored the database using 'pg_restore -d dbname backup_file_name' using the 9.0 restore and after creating a new database under 9.0. Under version 9.0 the database looks ok, but I had a lot of the following errors (132) during the restore: pg_restore: [archiver (db)] could not execute query: ERROR: relation xyz already exists ... pg_restore: [archiver (db)] could not execute query: ERROR: multiple primary keys for pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key value violates unique constraint ... I tried restoring the 8.4 restore file to a new 8.4 database and there were no errors. It almost looks like the schema already existed when I went to do the restore, but I was careful to create the new database in the admin tool under the correct server. Restoring twice is almost certainly the explanation. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] restore problem with pg_dumpall dump (password authentication fail)
Hi All, I have a backup/restore problem I need help with. I do a simple dump of the whole cluster using pg_dumpall, with role definitions and tutti. When performing restores, I start with a fresh empty database directory that i create using the createdb utility and specify the super-user to be named super and always with the same password, let's say restore. Then I feed the database dump to the fresh database using: # psql.exe -f dumpfile -U super (And I enter the password restore to authenticate to my freshly created database). But then the dump file alters the super role since that role also existed in the backed up database. Primarily it changes the roles password. This results in that when the dump-script carries on and comes to creating the database and finally does the \connect database, it fails, since super does not have the same password anymore that was specified when psql.exe was started, and then the rest of the script fails since we're not connected to the database anymore. Ultimately the restore fails. How do I avoid this scenario? I can think of some different ways to work around this, one is to create a user in the default database (createdb) that does not exist in the database dump file and is not altered by this (but how do i guarantee that it does not?). Another is to manually edit the dump-files and have the role alterations happen last in the script, but that requires that the roles at least exist for all roles that owns objects that are about to be restored and for the grants to work. A third work around might be to remove the PASSWORD 'x' part of the role alteration statement from the dump script. A fourth, and maybe the sloppiest way, could be to add a trust-line to pg_hba.conf to disable password auth completely while doing the restore operation? None of these seem too tempting though. Should this not be thought of in the dg_dumpall generated script? I can't be the only one discovering this problem ? //Kenneth
Re: [GENERAL] restore problem with pg_dumpall dump (password authentication fail)
Kenneth Lundin kenneth.lun...@dacom.se writes: How do I avoid this scenario? Don't change the postgres user's password in the middle of a dump/restore? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] restore problem with pg_dumpall dump (password authentication fail)
Tom, Well, it's not really I that change the password, it happens in the backup dump file created automatically by pg_dumpall. The thing is as an administrator, you want to be able to take a backup at certain points in time and then later flawlessly be able to restore that backup and end up in exactly the same state you were in when the backup was created. And since many times when you are doing a restore, you're in a time pinch since something probably have gone terribly wrong, then you don't want to have to muck around manipulating dump files in any way, you just want to do an instant restore and get your faulty system up and running asap. To my understanding, the prefered way of doing online backups in postgres is using pg_dump to dump the database. And my point here was that there seem to be a flaw in this scheme. The script generated by pg_dumpall fails in my case when I'm doing the restore. //Kenneth On Sat, Jan 3, 2009 at 5:40 PM, Tom Lane t...@sss.pgh.pa.us wrote: Kenneth Lundin kenneth.lun...@dacom.se writes: How do I avoid this scenario? Don't change the postgres user's password in the middle of a dump/restore? regards, tom lane
Re: [GENERAL] restore problem with pg_dumpall dump (password authentication fail)
On Saturday 03 January 2009 10:43:09 am Kenneth Lundin wrote: Tom, Well, it's not really I that change the password, it happens in the backup dump file created automatically by pg_dumpall. The thing is as an administrator, you want to be able to take a backup at certain points in time and then later flawlessly be able to restore that backup and end up in exactly the same state you were in when the backup was created. And since many times when you are doing a restore, you're in a time pinch since something probably have gone terribly wrong, then you don't want to have to muck around manipulating dump files in any way, you just want to do an instant restore and get your faulty system up and running asap. To my understanding, the prefered way of doing online backups in postgres is using pg_dump to dump the database. And my point here was that there seem to be a flaw in this scheme. The script generated by pg_dumpall fails in my case when I'm doing the restore. //Kenneth The problem seem to be here: When performing restores, I start with a fresh empty database directory that i create using the createdb utility and specify the super-user to be named super and always with the same password, let's say restore. This is not in fact the case. super does not always have the same password. You are using a different password for the restore role of super then the regular role. When restoring use the same password as in the database. You might also want to look at pg_dumpall -g which dumps the global parts of the database cluster i.e roles. I generally do a separate pg_dumpall -g and load that first so I have my roles in place before doing the rest of the restore. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore problem
On 12/10/07, Keith Turner [EMAIL PROTECTED] wrote: We are running 8.1 on Windows 2003 server and have had a server crash over the weekend. A virus is suspected - we maintain an app server on someone else's network, though we do have anti-virus running, the symptoms were worrying - so we had to wipe and reinstall the OS and all programs. We had recovered the 8.1 folder and the data off the crashed computer prior to the wipe, but the last good .backup file is a few days older than that. Are there step by step instructions on restoring from the folder itself instead of a backup file? Is it even possible? Trevor Talbot wrote: I would try installing 8.1 again, stop the service, _copy_ your data and config over the top of it, then start it again and see what happens. Any problems with that should show up immediately in the logs. And if all else fails, you can just nuke the attempt and restore from the older backups. I did this - it took a couple of tries, but it worked. For the record, here's a description of the problem and solution: Problem (before I became involved): Someone had turned off RPC service while trying to fix something. Someone else rebooted the server and all the windows services failed on restart. Panicked and suspecting a virus they reinstalled the OS, but had the wit to savethe postgres folders but couldn't make a .backup file. And the last backup file was the night before, and they had done a lot of work that would be lost if that was used. Solution: It wasn't a simple matter of just copying over the data folder. There are reasons having to do with not letting the client see you sweat and to do it in the BG. So on a fresh system we did as suggested above, copied over the files. One step that seems to be required (at least in this case) was a clean new install of postgres, stopping it immediately after the install, not one that had been used in any way. Required: The postgres files from the crashed server. Step 1. Clean install of same version of Postgres (8.1) on second computer using same superuser and password. Step 2. Stop postgtres service Step 3. Copy over Data folder replacing the one that was created on install Step 4. Start postgres Step 5. Log in to pgadmin, confirm that the data is there Step 6. Create a .backup that can be restored on the live site. Step 7. Create the database on the live site and restore from the .backup file. Thanks for your suggestion. We had tried it and failed, but this reinforced that it might work, so we then tried with a fresh install as well, and were delighted to have our data back. Regards, Keith ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Restore problem
Hi first post here, I hope you can help. We are running 8.1 on Windows 2003 server and have had a server crash over the weekend. A virus is suspected - we maintain an app server on someone else's network, though we do have anti-virus running, the symptoms were worrying - so we had to wipe and reinstall the OS and all programs. We had recovered the 8.1 folder and the data off the crashed computer prior to the wipe, but the last good .backup file is a few days older than that. Are there step by step instructions on restoring from the folder itself instead of a backup file? Is it even possible? Thanks, Keith ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Restore problem
am Mon, dem 10.12.2007, um 12:30:14 -0800 mailte Keith Turner folgendes: Hi first post here, I hope you can help. We are running 8.1 on Windows 2003 server and have had a server crash over the weekend. A virus is suspected - we maintain an app server on Please don't hijack other threads, the original thread was 'TIMESTAMP difference'. (don't answer to an arbitrary other mail and change the subject. Every mail contains references-header) Are there step by step instructions on restoring from the folder itself instead of a backup file? Is it even possible? not really... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Restore problem
On 12/10/07, Keith Turner [EMAIL PROTECTED] wrote: We are running 8.1 on Windows 2003 server and have had a server crash over the weekend. A virus is suspected - we maintain an app server on someone else's network, though we do have anti-virus running, the symptoms were worrying - so we had to wipe and reinstall the OS and all programs. We had recovered the 8.1 folder and the data off the crashed computer prior to the wipe, but the last good .backup file is a few days older than that. Are there step by step instructions on restoring from the folder itself instead of a backup file? Is it even possible? I would try installing 8.1 again, stop the service, _copy_ your data and config over the top of it, then start it again and see what happens. Any problems with that should show up immediately in the logs. And if all else fails, you can just nuke the attempt and restore from the older backups. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Restore problem
I forgot to add that I used the command for restore: psql f backup.db Thanks Mousa From: Shaya Mousa (Nokia-ES/Boston) Sent: Tuesday, October 26, 2004 11:42 AM To: '[EMAIL PROTECTED]' Subject: Restore problem I have created a backup file using pg_dumpall. When I try to restore to new database the tables are created but data is not loaded into any tables. I am attaching my backup file. Thanks Mousa
