Re: [GENERAL] Forgot to dump old data before re-installing machine [solved]
When I start the postmaster it seems ok. But there is no process running. Then it didn't really start successfully. (Unless you use the -w option, pg_ctl start just launches the postmaster --- it doesn't wait around to see what happens.) You need to look into the log file to see what the problem was. Ok, it's a bit confusing for a non-admin-user to enter into the pg- config to find out what parameters one has to uncomment in order to get the log working. Succeed after some while, got the postmaster working: 827 p2 S 0:00.22 /usr/local/bin/postmaster -D /usr/local/ pgsql/data TERM=xterm-color SHELL=/bin/bash USER=postgres __CF_USER_TEXT_ENCODING=0x1F6:0:0 P 829 p2 S 0:00.01 postgres: writer process 830 p2 S 0:00.00 postgres: stats buffer process 831 p2 S 0:00.00 postgres: stats collector process What would you recommend now in order to get the data back? I have postgis data in the databases too, although this is not too important for me... Sounds like you're ready to run pg_dump. Ok, finally got everything working. I really don't understand a thing about what has happened, since I was running the database since a year on an Intel Mac But perhaps the fact that I moved to Leopard now made it happen. Anyway, installing the old postgres version on an old PPC and then re- importing them into the new Intel-postgres database did the thing. So, many thanks () to everybody (!) for the valuable inputs, time etc. Best wishes, Stef smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Forgot to dump old data before re-installing machine
As for the real problem (on the same hardware), when you rebuilt Postgres on your new machine did you change any of the configure options that MacPorts would have used from what would have been used previously (I assume they can be overridden)? There's not that much that can be overridden that would affect the layout of pg_control. The only other thing I can think of at the moment is that moving from 32-bit to 64-bit time_t can screw things up --- but that shouldn't affect the interpretation of the pg_control version number, which as already noted certainly looks like it's the wrong endianness. Stefan, could you post the actual pg_control file as a binary attachment? The old pg_control file is attached... So, I am one step further, that is, I installed Tiger - and then Postgres 8.1 - on a PPC MacMini. After running initdb the postmaster started smoothly. I stopped it, copied my database files into the same location, started the postmaster again, and then got this error message: schwarzers-mac-mini:/usr/local/pgsql schwarzer$ /usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l postgres.log start pg_ctl: could not open PID file /usr/local/pgsql/data/ postmaster.pid: Permission denied As one can see from the following listing, there is no postmaster.pid. And the settings seemed to be (for me) the same as in the old database tree: schwarzers-mac-mini:/usr/local/pgsql schwarzer$ ls -l total 0 drwx-- 16 postgres admin 544 Jan 23 18:23 data drwx-- 15 postgres admin 510 Jan 23 17:50 data.orig schwarzers-mac-mini:/usr/local/pgsql/data postgres$ ls -l total 64 -rw---1 postgres admin 4 Jan 23 18:23 PG_VERSION drwx-- 11 postgres admin374 Jan 23 18:23 base drwx-- 27 postgres admin918 Jan 23 18:23 global drwx-- 23 postgres admin782 Jan 23 18:23 pg_clog -rw---1 postgres admin 3396 Jan 23 18:23 pg_hba.conf -rw---1 postgres admin 1460 Jan 23 18:23 pg_ident.conf drwx--4 postgres admin136 Jan 23 18:23 pg_multixact drwx--3 postgres admin102 Jan 23 18:23 pg_subtrans drwx--2 postgres admin 68 Jan 23 18:23 pg_tblspc drwx--2 postgres admin 68 Jan 23 18:23 pg_twophase drwx-- 11 postgres admin374 Jan 23 18:23 pg_xlog -rw---1 postgres admin 13614 Jan 23 18:23 postgresql.conf -rw---1 postgres admin 74 Jan 23 18:23 postmaster.opts schwarzers-mac-mini:/usr/local/pgsql/data.orig postgres$ ls -l total 64 -rw---1 postgres admin 4 Jan 23 17:49 PG_VERSION drwx--5 postgres admin170 Jan 23 17:49 base drwx-- 28 postgres admin952 Jan 23 17:50 global drwx--3 postgres admin102 Jan 23 17:49 pg_clog -rw---1 postgres admin 3396 Jan 23 17:49 pg_hba.conf -rw---1 postgres admin 1460 Jan 23 17:49 pg_ident.conf drwx--4 postgres admin136 Jan 23 17:49 pg_multixact drwx--3 postgres admin102 Jan 23 17:49 pg_subtrans drwx--2 postgres admin 68 Jan 23 17:49 pg_tblspc drwx--2 postgres admin 68 Jan 23 17:49 pg_twophase drwx--4 postgres admin136 Jan 23 17:49 pg_xlog -rw---1 postgres admin 13680 Jan 23 17:49 postgresql.conf -rw---1 postgres admin 49 Jan 23 17:50 postmaster.opts Thanks for any hints... Stef pg_control Description: Binary data ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Forgot to dump old data before re-installing machine
Stefan Schwarzer [EMAIL PROTECTED] writes: After running initdb the postmaster started smoothly. I stopped it, copied my database files into the same location, started the postmaster again, and then got this error message: schwarzers-mac-mini:/usr/local/pgsql schwarzer$ /usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l postgres.log start pg_ctl: could not open PID file /usr/local/pgsql/data/ postmaster.pid: Permission denied You're trying to start the server as yourself (user schwarzer) but the files all belong to user postgres: drwx-- 16 postgres admin 544 Jan 23 18:23 data The files have to be owned by the user that runs the server. If you want to switch over to doing that as schwarzer, a quick chown -R will help. regards, tom lane ---(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] Forgot to dump old data before re-installing machine
On Jan 24, 2008, at 4:41 PM, Tom Lane wrote: Stefan Schwarzer [EMAIL PROTECTED] writes: After running initdb the postmaster started smoothly. I stopped it, copied my database files into the same location, started the postmaster again, and then got this error message: schwarzers-mac-mini:/usr/local/pgsql schwarzer$ /usr/local/bin/ pg_ctl -D /usr/local/pgsql/data -l postgres.log start pg_ctl: could not open PID file /usr/local/pgsql/data/ postmaster.pid: Permission denied You're trying to start the server as yourself (user schwarzer) but the files all belong to user postgres: drwx-- 16 postgres admin 544 Jan 23 18:23 data The files have to be owned by the user that runs the server. If you want to switch over to doing that as schwarzer, a quick chown -R will help. Oh, stupid me! Gush, I'll never learn it... But nevertheless: When I start the postmaster it seems ok. But there is no process running. When I try to stop it it says: pg_ctl: PID file /usr/local/pgsql/data/postmaster.pid does not exist Is postmaster running? I slowly get the feeling that all my efforts are in vain... Stef ---(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] Forgot to dump old data before re-installing machine
Stefan Schwarzer [EMAIL PROTECTED] writes: When I start the postmaster it seems ok. But there is no process running. Then it didn't really start successfully. (Unless you use the -w option, pg_ctl start just launches the postmaster --- it doesn't wait around to see what happens.) You need to look into the log file to see what the problem was. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Forgot to dump old data before re-installing machine
When I start the postmaster it seems ok. But there is no process running. Then it didn't really start successfully. (Unless you use the -w option, pg_ctl start just launches the postmaster --- it doesn't wait around to see what happens.) You need to look into the log file to see what the problem was. Ok, it's a bit confusing for a non-admin-user to enter into the pg- config to find out what parameters one has to uncomment in order to get the log working. Succeed after some while, got the postmaster working: 827 p2 S 0:00.22 /usr/local/bin/postmaster -D /usr/local/ pgsql/data TERM=xterm-color SHELL=/bin/bash USER=postgres __CF_USER_TEXT_ENCODING=0x1F6:0:0 P 829 p2 S 0:00.01 postgres: writer process 830 p2 S 0:00.00 postgres: stats buffer process 831 p2 S 0:00.00 postgres: stats collector process What would you recommend now in order to get the data back? I have postgis data in the databases too, although this is not too important for me... Thanks you so much for all your help! Stef ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Forgot to dump old data before re-installing machine
On Thu, Jan 24, 2008 at 05:10:51PM +0100, Stefan Schwarzer wrote: Oh, stupid me! Gush, I'll never learn it... But nevertheless: When I start the postmaster it seems ok. But there is no process running. When I try to stop it it says: pg_ctl: PID file /usr/local/pgsql/data/postmaster.pid does not exist Is postmaster running? If it fails to start it must log somehwere. If you run the postmaster directly from the console it'll get you into singleuser mode, try that. (PS. Have you tried pgfsck on the new machine. With the same byte-order it should work much betteR). Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] Forgot to dump old data before re-installing machine
On Jan 24, 2008 10:41 AM, Stefan Schwarzer [EMAIL PROTECTED] wrote: When I start the postmaster it seems ok. But there is no process running. Then it didn't really start successfully. (Unless you use the -w option, pg_ctl start just launches the postmaster --- it doesn't wait around to see what happens.) You need to look into the log file to see what the problem was. Ok, it's a bit confusing for a non-admin-user to enter into the pg- config to find out what parameters one has to uncomment in order to get the log working. Succeed after some while, got the postmaster working: 827 p2 S 0:00.22 /usr/local/bin/postmaster -D /usr/local/ pgsql/data TERM=xterm-color SHELL=/bin/bash USER=postgres __CF_USER_TEXT_ENCODING=0x1F6:0:0 P 829 p2 S 0:00.01 postgres: writer process 830 p2 S 0:00.00 postgres: stats buffer process 831 p2 S 0:00.00 postgres: stats collector process What would you recommend now in order to get the data back? I have postgis data in the databases too, although this is not too important for me... Sounds like you're ready to run pg_dump. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Forgot to dump old data before re-installing machine
On Thu, Jan 24, 2008 at 05:41:20PM +0100, Stefan Schwarzer wrote: What would you recommend now in order to get the data back? I have postgis data in the databases too, although this is not too important for me... pg_dump, pg_dumpall... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] Forgot to dump old data before re-installing machine
On 18/01/2008, Stefan Schwarzer [EMAIL PROTECTED] wrote: Ok, it seems to be related to a Intel/PPC issue, as Martijn and Tom suggested. So, I copied all files to a PPC, but which runs Linux - don't know if this is important. Now, it tells me: Fatal error: Incorrect checksum on control file Any way out of this? Thanks for any advice. That's the kind of error I'd expect to see if you try to start an Intel data directory on PPC or vice-versa. You said earlier this was data from your Intel Mac being reloaded on the same Intel Mac. If thats the case, put the PPC away before you confuse yourself :-) As for the real problem (on the same hardware), when you rebuilt Postgres on your new machine did you change any of the configure options that MacPorts would have used from what would have been used previously (I assume they can be overridden)? I don't know if you'd see exactly the same symptoms you have, but changing settings like integer datetimes will break things in a similar way. Regards, Dave ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Forgot to dump old data before re-installing machine
On Jan 18, 2008 7:25 AM, Stefan Schwarzer [EMAIL PROTECTED] wrote: Did you just move from a PPC-based Mac to an Intel-based one? If so, you're out of luck --- you need to go back to the PPC to make a dump of those files. No, I just re-installed my Intel Mac. First I just upgraded from Tiger to Leopard (without getting my database to run; but I didn't put much effort into it); and then I completely erased the disk and installed Leopard from scratch. H. Can't be that I am standing now there having lost my data, no? Please, any faintest idea what I can try? Ok, it seems to be related to a Intel/PPC issue, as Martijn and Tom suggested. So, I copied all files to a PPC, but which runs Linux - don't know if this is important. Now, it tells me: Fatal error: Incorrect checksum on control file Any way out of this? Thanks for any advice. Yes, you need to set up a machine running the same OS and pgsql version and build as before. ---(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] Forgot to dump old data before re-installing machine
Dave Page [EMAIL PROTECTED] writes: Note to the other hackers - is it worth having initdb dump the architecture details and configure options used into the cluster in a human readble form so we can pickup on this sort of thing more easily in the future? That's what pg_control is for. We figured out easily enough that this was an endianness problem; having had big endian somewhere in cleartext wouldn't have improved matters. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Forgot to dump old data before re-installing machine
Ok, it seems to be related to a Intel/PPC issue, as Martijn and Tom suggested. So, I copied all files to a PPC, but which runs Linux - don't know if this is important. Now, it tells me: Fatal error: Incorrect checksum on control file Any way out of this? Thanks for any advice. That's the kind of error I'd expect to see if you try to start an Intel data directory on PPC or vice-versa. You said earlier this was data from your Intel Mac being reloaded on the same Intel Mac. If thats the case, put the PPC away before you confuse yourself :-) As for the real problem (on the same hardware), when you rebuilt Postgres on your new machine did you change any of the configure options that MacPorts would have used from what would have been used previously (I assume they can be overridden)? I don't know if you'd see exactly the same symptoms you have, but changing settings like integer datetimes will break things in a similar way. Here is some input from Martijn: ___ Well, it's clearly byte-swapped. So whatever the database was running on it was on a PPC or some other big-endian machine. The give away is that bytes 1617 are 20 00 rather than 00 20. You can check a file with: $ od -t x1 -j 16 -N 2 filename 020 00 20 022 That's for Intel, on the file you sent me it's: 020 20 00 022 I don't understand it either, which is why I was wondering if it was running under some PPC emulation (can you run standard mac software or do you have to get special Intel versions). ___ I have no idea how my old postgres config was. I've contacted already [EMAIL PROTECTED] to see if he still has the old .dmgs, which is I think what I used to install postgres with. Gush, gush, gush ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Forgot to dump old data before re-installing machine
Did you just move from a PPC-based Mac to an Intel-based one? If so, you're out of luck --- you need to go back to the PPC to make a dump of those files. No, I just re-installed my Intel Mac. First I just upgraded from Tiger to Leopard (without getting my database to run; but I didn't put much effort into it); and then I completely erased the disk and installed Leopard from scratch. H. Can't be that I am standing now there having lost my data, no? Please, any faintest idea what I can try? Ok, it seems to be related to a Intel/PPC issue, as Martijn and Tom suggested. So, I copied all files to a PPC, but which runs Linux - don't know if this is important. Now, it tells me: Fatal error: Incorrect checksum on control file Any way out of this? Thanks for any advice. Stef ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Forgot to dump old data before re-installing machine
On 18/01/2008, Peter Eisentraut [EMAIL PROTECTED] wrote: Am Freitag, 18. Januar 2008 schrieb Dave Page: It got figured out when someone who knew what they were looking for peeked at the byte ordering in a file which for all we knew at the time might have been damaged anyway What might be better is if we had an explicit endianness mark in pg_control rather than relying on users discovering endianness problems by seemingly corrupted version numbers. Seems reasonable to me. Obviously I'd mixed up the datetime/time_t issues I ran into previously, so having the configure options as well would be largely useless. That said, is zlib used by toast or do we have some other code for that? If it is used for that, do we record it's presence or absence in pg_control? /D ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Forgot to dump old data before re-installing machine
On 18/01/2008, Tom Lane [EMAIL PROTECTED] wrote: Hm? integer_datetimes is encoded separately and there's a very specific error message if it's wrong. The case I think you are remembering was caused by a width-of-time_t discrepancy, which should be fixed but it's got nothing to do with anything else. Yeah, I think you're probably right there. Zero cost and also zero benefit. The missing piece of information here was that the executable being used was running under PPC emulation, and I'll bet money that there would have been nothing in either uname or pg_config output that would have told us that. I'd wager there would be a fairly good chance that a PPC-only binary on a Mac would most likely have been built on a PPC, and thus mention that in the uname output at build time. I can't imagine many folks are building PPC-only binaries on Intels. How much money did you have in mind? :-) /D ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Forgot to dump old data before re-installing machine
On 18/01/2008, Tom Lane [EMAIL PROTECTED] wrote: Dave Page [EMAIL PROTECTED] writes: Note to the other hackers - is it worth having initdb dump the architecture details and configure options used into the cluster in a human readble form so we can pickup on this sort of thing more easily in the future? That's what pg_control is for. We figured out easily enough that this was an endianness problem; having had big endian somewhere in cleartext wouldn't have improved matters. It got figured out when someone who knew what they were looking for peeked at the byte ordering in a file which for all we knew at the time might have been damaged anyway - and the same test wouldn't have spotted an integer_datetimes difference for example, something which bit Greg I recently and had us puzzled for a while. For just about zero cost we could drop something like: Architecture: Darwin snake 8.11.1 Darwin Kernel Version 8.11.1: Wed Oct 10 18:23:28 PDT 2007; root:xnu-792.25.20~1/RELEASE_I386 i386 i386 Configuration: '--prefix=/usr/local/pgsql83/' '--enable-integer-datetimes' '--with-openssl' '--with-perl' '--with-python' '--with-tcl' '--without-tk' '--with-bonjour' '--with-pam' '--with-krb5' 'CFLAGS=-O -g -arch i386 -arch ppc' 'LDFLAGS=-ltcl' in a file in $PGDATA which would make it much easier for users and hackers to see where the cluster came from and compare it to the actual build. /D ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Forgot to dump old data before re-installing machine
Dave Page [EMAIL PROTECTED] writes: On 18/01/2008, Tom Lane [EMAIL PROTECTED] wrote: That's what pg_control is for. We figured out easily enough that this was an endianness problem; having had big endian somewhere in cleartext wouldn't have improved matters. It got figured out when someone who knew what they were looking for peeked at the byte ordering in a file which for all we knew at the time might have been damaged anyway - and the same test wouldn't have spotted an integer_datetimes difference for example, something which bit Greg I recently and had us puzzled for a while. Hm? integer_datetimes is encoded separately and there's a very specific error message if it's wrong. The case I think you are remembering was caused by a width-of-time_t discrepancy, which should be fixed but it's got nothing to do with anything else. For just about zero cost we could drop something like: Architecture: Darwin snake 8.11.1 Darwin Kernel Version 8.11.1: Wed Oct 10 18:23:28 PDT 2007; root:xnu-792.25.20~1/RELEASE_I386 i386 i386 Configuration: '--prefix=/usr/local/pgsql83/' '--enable-integer-datetimes' '--with-openssl' '--with-perl' '--with-python' '--with-tcl' '--without-tk' '--with-bonjour' '--with-pam' '--with-krb5' 'CFLAGS=-O -g -arch i386 -arch ppc' 'LDFLAGS=-ltcl' Zero cost and also zero benefit. The missing piece of information here was that the executable being used was running under PPC emulation, and I'll bet money that there would have been nothing in either uname or pg_config output that would have told us that. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Forgot to dump old data before re-installing machine
Dave Page [EMAIL PROTECTED] writes: On 18/01/2008, Tom Lane [EMAIL PROTECTED] wrote: Zero cost and also zero benefit. The missing piece of information here was that the executable being used was running under PPC emulation, and I'll bet money that there would have been nothing in either uname or pg_config output that would have told us that. I'd wager there would be a fairly good chance that a PPC-only binary on a Mac would most likely have been built on a PPC, and thus mention that in the uname output at build time. I can't imagine many folks are building PPC-only binaries on Intels. uname is a separate executable. If you do system(uname) you'll get results that reflect how uname was built, not how Postgres was built. I think this is likely to lead to more confusion, not less --- if we'd had such output in the directory, it might have led us to disregard the clear evidence of the wrong-endian version number, and fruitlessly bark up some other tree instead. regards, tom lane ---(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] Forgot to dump old data before re-installing machine
On 18/01/2008, Tom Lane [EMAIL PROTECTED] wrote: uname is a separate executable. If you do system(uname) you'll get results that reflect how uname was built, not how Postgres was built. Right, I realise it's a seperate executable, but doesn't configure rely on it anyway? Meaning if someones system has a uname that tells configure it's a PPC when it's actually an Intel, the resulting binary is likely to go bang anyway, assuming it even builds. My suggestion was that we take the output of uname at configure/build time and bung it in a macro, not do anything with system() at runtime... Anyway, Peter's suggestion seems much tidier. /D ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Forgot to dump old data before re-installing machine
On Fri, 18 Jan 2008, Tom Lane wrote: pg_controldata already provides this information, no? At least barring the case of wrong-time_t-size, which we already know we want to fix. It provides some of it, and I think you could make a case that the text file format Dave suggested could be prototyped in an improved form by combining the output from pg_controldata plus pg_config plus uname -a. If people knew to save a copy of the output from those religiously into the base of the database directory to aid possibly unrelated people who have to restore that data later, that would be nice. In the cases I was mentioning, people can't run pg_controldata until they have binaries installed, and having a simple text file that contained the information needed to do that right in the first place would give some guidance as to get to that step. You have to put yourself in the shoes of the person who has a database backup and a crashed server to appreciate that anything that makes this process easier is a huge help. Imagine that the previous DBA just quit (nuking the server on his way out) and you have the kind of real-world crisis people really run into. It would be nice if things progressed to where, for example, someone could hand me a database backup I know nothing about, I could look for this handy text file information in the base directory, see: CONFIGURE = '--build=i686-redhat-linux-gnu' ... VERSION = PostgreSQL 8.2.5 Linux host.gregsmith.com 2.6.18-8.1.4.el5xen #1 SMP Thu May 17 05:27:09 EDT 2007 i686 i686 i386 GNU/Linux And now I've got a real good idea what binary this dump came from and how to get the instance back up and running in a few seconds of work. Maybe it's a suggested best-practice for now to save such a thing, maybe it gets included as an automatic feature at initdb time in 8.4, but I think it's a worthwhile idea to work toward helping people with. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Forgot to dump old data before re-installing machine
On 18/01/2008, Tom Lane [EMAIL PROTECTED] wrote: Dave Page [EMAIL PROTECTED] writes: On 18/01/2008, Tom Lane [EMAIL PROTECTED] wrote: Ah. That would work better than what I thought you were suggesting, but I still don't trust it a whole lot --- there's the problem of universal binaries (PPC PPC64 Intel) for instance, which I believe some people have managed to build Postgres as. Yes, I maintain such a distribution :-). Actually, the suggested output I posted earlier was from a Universal build - the uname output shows Intel only of course, but the CFLAGS do show both architectures. The other way of building a universal binary (other than cross compiling one architecture as I do), is to take native binaries from each platform and literally glue them together. That won't show the differences through CFLAGS, but each section of the universal binary would have it's own native uname output. /D ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Forgot to dump old data before re-installing machine
Greg Smith [EMAIL PROTECTED] writes: The usual advice, telling them to replicate the binaries used to create it in the first place, isn't always the easiest to follow. It seems to me that including a environment at cluster creation note in $PGDATA like Dave suggests would be helpful for these cases; PG_VERSION just isn't enough information. I'd also throw in the locale information used for the cluster, as that seems like something it would be nice to have in simple text form as well there and is also a spot people are confused about. pg_controldata already provides this information, no? At least barring the case of wrong-time_t-size, which we already know we want to fix. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Forgot to dump old data before re-installing machine
Dave Page wrote: That said, is zlib used by toast or do we have some other code for that? If it is used for that, do we record it's presence or absence in pg_control? Nope, toast uses its own compression code. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Forgot to dump old data before re-installing machine
Dave Page [EMAIL PROTECTED] writes: As for the real problem (on the same hardware), when you rebuilt Postgres on your new machine did you change any of the configure options that MacPorts would have used from what would have been used previously (I assume they can be overridden)? There's not that much that can be overridden that would affect the layout of pg_control. The only other thing I can think of at the moment is that moving from 32-bit to 64-bit time_t can screw things up --- but that shouldn't affect the interpretation of the pg_control version number, which as already noted certainly looks like it's the wrong endianness. Stefan, could you post the actual pg_control file as a binary attachment? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Forgot to dump old data before re-installing machine
Alvaro Herrera [EMAIL PROTECTED] writes: Dave Page wrote: That said, is zlib used by toast or do we have some other code for that? If it is used for that, do we record it's presence or absence in pg_control? Nope, toast uses its own compression code. pg_dump/pg_restore use zlib, but I believe there's a pretty specific error message if you try to read a compressed archive with a non-zlib-enabled pg_restore. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Forgot to dump old data before re-installing machine
On Fri, 18 Jan 2008, Dave Page wrote: For just about zero cost we could drop something like: Architecture: Darwin snake 8.11.1 Darwin Kernel Version 8.11.1: Wed Oct 10 18:23:28 PDT 2007; root:xnu-792.25.20~1/RELEASE_I386 i386 i386 Configuration: '--prefix=/usr/local/pgsql83/' '--enable-integer-datetimes' '--with-openssl' '--with-perl' '--with-python' '--with-tcl' '--without-tk' '--with-bonjour' '--with-pam' '--with-krb5' 'CFLAGS=-O -g -arch i386 -arch ppc' 'LDFLAGS=-ltcl' in a file in $PGDATA Stepping away from the question of whether it would have helped in this specific case for a second, around once a month or so there's someone in a panic here because they have a filesystem copy of a database they can't figure out how to use. Often the original server is a puddle of molten metal or something by that point and the person trying to restore the data is rather stressed. The usual advice, telling them to replicate the binaries used to create it in the first place, isn't always the easiest to follow. It seems to me that including a environment at cluster creation note in $PGDATA like Dave suggests would be helpful for these cases; PG_VERSION just isn't enough information. I'd also throw in the locale information used for the cluster, as that seems like something it would be nice to have in simple text form as well there and is also a spot people are confused about. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Forgot to dump old data before re-installing machine
On 18/01/2008, Stefan Schwarzer [EMAIL PROTECTED] wrote: I don't understand it either, which is why I was wondering if it was running under some PPC emulation (can you run standard mac software or do you have to get special Intel versions).] Yes, Apple have an emulation layer called Rosetta - but you said you used MacPorts so you should have a native build. I have no idea how my old postgres config was. I've contacted already [EMAIL PROTECTED] to see if he still has the old .dmgs, which is I think what I used to install postgres with. Aha - previously undisclosed info :-) Yes, then it does seem quite feasible that your old build was not only differing in achitecture, but possibly also other configuration options that would have similar effects. Note to the other hackers - is it worth having initdb dump the architecture details and configure options used into the cluster in a human readble form so we can pickup on this sort of thing more easily in the future? Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Forgot to dump old data before re-installing machine
Peter Eisentraut [EMAIL PROTECTED] writes: What might be better is if we had an explicit endianness mark in pg_control rather than relying on users discovering endianness problems by seemingly corrupted version numbers. Chicken-and-egg problem there: you won't know if there's an endianness flag to check without having tested pg_control_version. What would work better is to add some code that checks whether pg_control_version looks like the byte-swap of a small number, and prints a suitably modified error message if so. I would not previously have thought this was worth the trouble, but given what we now know about Apple's migration process, it might be worth another half dozen lines of code and a new error message. What was that about string freeze ;-) ? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Forgot to dump old data before re-installing machine
Dave Page [EMAIL PROTECTED] writes: On 18/01/2008, Tom Lane [EMAIL PROTECTED] wrote: uname is a separate executable. If you do system(uname) you'll get results that reflect how uname was built, not how Postgres was built. My suggestion was that we take the output of uname at configure/build time and bung it in a macro, not do anything with system() at runtime... Ah. That would work better than what I thought you were suggesting, but I still don't trust it a whole lot --- there's the problem of universal binaries (PPC PPC64 Intel) for instance, which I believe some people have managed to build Postgres as. Anyway, Peter's suggestion seems much tidier. Agreed. Also we could have it today if we base it off inspection of pg_control_version. regards, tom lane ---(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] Forgot to dump old data before re-installing machine
Am Freitag, 18. Januar 2008 schrieb Dave Page: It got figured out when someone who knew what they were looking for peeked at the byte ordering in a file which for all we knew at the time might have been damaged anyway What might be better is if we had an explicit endianness mark in pg_control rather than relying on users discovering endianness problems by seemingly corrupted version numbers. For just about zero cost we could drop something like: Architecture: Darwin snake 8.11.1 Darwin Kernel Version 8.11.1: Wed Oct 10 18:23:28 PDT 2007; root:xnu-792.25.20~1/RELEASE_I386 i386 i386 I think we should address the problem were it happens. Adding this output will increase the amount of information available for causing confusion, while it would probably still require expert knowledge to read an endianness issue out of that. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Forgot to dump old data before re-installing machine
This looks like an endianess mismatch; did you already mention on what architecture you are on? MacPro, Leopard Did you just move from a PPC-based Mac to an Intel-based one? If so, you're out of luck --- you need to go back to the PPC to make a dump of those files. No, I just re-installed my Intel Mac. First I just upgraded from Tiger to Leopard (without getting my database to run; but I didn't put much effort into it); and then I completely erased the disk and installed Leopard from scratch. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Forgot to dump old data before re-installing machine
This looks like an endianess mismatch; did you already mention on what architecture you are on? MacPro, Leopard Did you just move from a PPC-based Mac to an Intel-based one? If so, you're out of luck --- you need to go back to the PPC to make a dump of those files. No, I just re-installed my Intel Mac. First I just upgraded from Tiger to Leopard (without getting my database to run; but I didn't put much effort into it); and then I completely erased the disk and installed Leopard from scratch. H. Can't be that I am standing now there having lost my data, no? Please, any faintest idea what I can try? Thanks for hints! Stef smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Forgot to dump old data before re-installing machine
Ok, did what you said: stopping server, deleting newly created data directory, re-running initdb, starting the server, stopping the server. Renamed empty data directory. Restarting server: NOT COMPLAINING you need to run initdb or something else Although it's saying that it starts, nothing appears in the process list (ps -ef). Hmm, you need to stop here and figure out exactly what happens. What procedure are you using to start the server? I assume you are not directly typing postmaster, but using some script, because the bare postmaster would certainly not act that way. I guess that either the script silently runs initdb for you (which is generally thought a bad idea nowadays) or that it is redirecting the postmaster's log output someplace that you're not looking. Anyway, don't go past this step until you understand what you're seeing. Uff Ok, here is what I did after compiling postgres8.1 (getting it from MacPorts): /opt/local/lib/postgresql81/bin/initdb -D Documents/data_postgres /opt/local/lib/postgresql81/bin/pg_ctl -D Documents/data_postgres -l logfile start ps -ef shows the postmaster process /opt/local/lib/postgresql81/bin/pg_ctl -D Documents/data_postgres -l logfile stop renaming data_postgres to data_postgres.orig /opt/local/lib/postgresql81/bin/pg_ctl -D Documents/data_postgres -l logfile start which tells me: postmaster starting but in ps -ef there is no process listed When I re-rename the newly created folder (data_postgres.orig into data_postgres) the start works again. But it does not work with the old (backuped) data folder... So, as I mentioned before, it seems not to be that simple, that I can just copy the old (backuped) data folder onto the newly created. Is there any way I can figure out with which version I have created the old databases? Perhaps, in a worst case scenario they have been created in 8.0 I will try... The logfile is telling me this when I try to start the server with my old data folder: FATAL: database files are incompatible with server DETAIL: The database cluster was initialized with PG_CONTROL_VERSION 738394112, but the server was compiled with PG_CONTROL_VERSION 812. What does it mean? I have and had 8.1 installed... Thanks for any help!! Stef ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Forgot to dump old data before re-installing machine
On Mittwoch, 16. Januar 2008, Stefan Schwarzer wrote: | The logfile is telling me this when I try to start the server with my | old data folder: | | FATAL: database files are incompatible with server | DETAIL: The database cluster was initialized with PG_CONTROL_VERSION | 738394112, but the server was compiled with PG_CONTROL_VERSION 812. | | What does it mean? I have and had 8.1 installed... I didn't follow the thread, but look at the output of $ printf %x\n 738394112 2c03 and $ printf %x\n 812 32c This looks like an endianess mismatch; did you already mention on what architecture you are on? Ciao, Thomas -- Thomas Pundt [EMAIL PROTECTED] http://rp-online.de/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Forgot to dump old data before re-installing machine
| The logfile is telling me this when I try to start the server with my | old data folder: | | FATAL: database files are incompatible with server | DETAIL: The database cluster was initialized with PG_CONTROL_VERSION | 738394112, but the server was compiled with PG_CONTROL_VERSION 812. | | What does it mean? I have and had 8.1 installed... I didn't follow the thread, but look at the output of $ printf %x\n 738394112 2c03 and $ printf %x\n 812 32c This looks like an endianess mismatch; did you already mention on what architecture you are on? MacPro, Leopard ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Forgot to dump old data before re-installing machine
Stefan Schwarzer [EMAIL PROTECTED] writes: This looks like an endianess mismatch; did you already mention on what architecture you are on? MacPro, Leopard Did you just move from a PPC-based Mac to an Intel-based one? If so, you're out of luck --- you need to go back to the PPC to make a dump of those files. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Forgot to dump old data before re-installing machine
I re-installed my machine and forgot to dump my database(s). I naturally still have the whole database folders. For the moment I installed the old postgres version (8.1) to be able to read my data. But how can I read them? It seems that it doesn't work that I just overwrite the new database folder with the old one... Would be too simple, I guess... Should work, if you've got the whole $PGDATA directory tree. Maybe you forgot to stop the postmaster while copying the backup into place? Thanks a lot for this. Still trying. But although the postmaster did run at one time, now, after copying back and forth, it doesn't want to do anything anymore... Gush, getting really frustrated... Stef ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Forgot to dump old data before re-installing machine
Stefan Schwarzer wrote: I re-installed my machine and forgot to dump my database(s). I naturally still have the whole database folders. For the moment I installed the old postgres version (8.1) to be able to read my data. But how can I read them? It seems that it doesn't work that I just overwrite the new database folder with the old one... Would be too simple, I guess... Should work, if you've got the whole $PGDATA directory tree. Maybe you forgot to stop the postmaster while copying the backup into place? Thanks a lot for this. Still trying. But although the postmaster did run at one time, now, after copying back and forth, it doesn't want to do anything anymore... Gush, getting really frustrated... Stop. Deep breath. Cup of coffee / tea, optional biscuit. Stop the server (if it's running) Check the version-number of the installed postgresql packages. Check you still have your backups somewhere safe. Re-run initdb to re-create your data directory. Make sure plenty of logging is turned on in postgresql.conf Start the server. Verify that everything is fine, particularly that select version() displays what you expect. Stop the server. Do the logs contain everything you'd expect? If not, update postgresql.conf and re-start the server until they do. Rename the data directory to data.old (or similar). Try starting the server - check that it fails complaining you need to run initdb (or similar). Restore your backup to data. Make sure plenty of logging is turned on in postgresql.conf Compare ownership + permissions on data vs data.old, correct if necessary. Start the server. With this setup you can compare both versions of your data directory and see what's different. If the version of your backup and the version of the software are the same then it will work. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Forgot to dump old data before re-installing machine
On Tue, Jan 15, 2008 at 02:42:05PM +0100, Stefan Schwarzer wrote: Thanks a lot for this. Still trying. But although the postmaster did run at one time, now, after copying back and forth, it doesn't want to do anything anymore... Gush, getting really frustrated... If it really doesn't work, try this: http://svana.org/kleptog/pgsql/pgfsck.html Point it at the right directory and it can give you a dump of data. It's not pretty, doesn't handle arrays or some of the less common datatypes but it should get you 99% of the way. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] Forgot to dump old data before re-installing machine
I re-installed my machine and forgot to dump my database(s). I naturally still have the whole database folders. For the moment I installed the old postgres version (8.1) to be able to read my data. But how can I read them? It seems that it doesn't work that I just overwrite the new database folder with the old one... Would be too simple, I guess... Should work, if you've got the whole $PGDATA directory tree. Maybe you forgot to stop the postmaster while copying the backup into place? Thanks a lot for this. Still trying. But although the postmaster did run at one time, now, after copying back and forth, it doesn't want to do anything anymore... Gush, getting really frustrated... Stop. Deep breath. Cup of coffee / tea, optional biscuit. Stop the server (if it's running) Check the version-number of the installed postgresql packages. Check you still have your backups somewhere safe. Re-run initdb to re-create your data directory. Make sure plenty of logging is turned on in postgresql.conf Start the server. Verify that everything is fine, particularly that select version() displays what you expect. Stop the server. Do the logs contain everything you'd expect? If not, update postgresql.conf and re-start the server until they do. Rename the data directory to data.old (or similar). Try starting the server - check that it fails complaining you need to run initdb (or similar). Restore your backup to data. Make sure plenty of logging is turned on in postgresql.conf Compare ownership + permissions on data vs data.old, correct if necessary. Start the server. With this setup you can compare both versions of your data directory and see what's different. If the version of your backup and the version of the software are the same then it will work. Ok, thanks for these steps. Coffee and chocolate helped... :-)) Ok, did what you said: stopping server, deleting newly created data directory, re-running initdb, starting the server, stopping the server. Renamed empty data directory. Restarting server: NOT COMPLAINING you need to run initdb or something else Although it's saying that it starts, nothing appears in the process list (ps -ef). and renamed old, backuped data directory to data_postgres Can't see any differences in permissions... Looks like this: drwx-- 14 schwarzer schwarzer 476 Jan 14 13:33 data_postgres Restarted the server. /opt/local/lib/postgresql81/bin/psql postgres \l List of databases Name| Owner | Encoding ---+---+-- postgres | schwarzer | UTF8 template0 | schwarzer | UTF8 template1 | schwarzer | UTF8 I should have a couple of databases appearing here... ---(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] Forgot to dump old data before re-installing machine
Stefan Schwarzer [EMAIL PROTECTED] writes: Ok, did what you said: stopping server, deleting newly created data directory, re-running initdb, starting the server, stopping the server. Renamed empty data directory. Restarting server: NOT COMPLAINING you need to run initdb or something else Although it's saying that it starts, nothing appears in the process list (ps -ef). Hmm, you need to stop here and figure out exactly what happens. What procedure are you using to start the server? I assume you are not directly typing postmaster, but using some script, because the bare postmaster would certainly not act that way. I guess that either the script silently runs initdb for you (which is generally thought a bad idea nowadays) or that it is redirecting the postmaster's log output someplace that you're not looking. Anyway, don't go past this step until you understand what you're seeing. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Forgot to dump old data before re-installing machine
Ok, did what you said: stopping server, deleting newly created data directory, re-running initdb, starting the server, stopping the server. Renamed empty data directory. Restarting server: NOT COMPLAINING you need to run initdb or something else Although it's saying that it starts, nothing appears in the process list (ps -ef). Hmm, you need to stop here and figure out exactly what happens. What procedure are you using to start the server? I assume you are not directly typing postmaster, but using some script, because the bare postmaster would certainly not act that way. I guess that either the script silently runs initdb for you (which is generally thought a bad idea nowadays) or that it is redirecting the postmaster's log output someplace that you're not looking. Anyway, don't go past this step until you understand what you're seeing. Uff Ok, here is what I did after compiling postgres8.1 (getting it from MacPorts): /opt/local/lib/postgresql81/bin/initdb -D Documents/data_postgres /opt/local/lib/postgresql81/bin/pg_ctl -D Documents/data_postgres -l logfile start ps -ef shows the postmaster process /opt/local/lib/postgresql81/bin/pg_ctl -D Documents/data_postgres -l logfile stop renaming data_postgres to data_postgres.orig /opt/local/lib/postgresql81/bin/pg_ctl -D Documents/data_postgres -l logfile start which tells me: postmaster starting but in ps -ef there is no process listed When I re-rename the newly created folder (data_postgres.orig into data_postgres) the start works again. But it does not work with the old (backuped) data folder... So, as I mentioned before, it seems not to be that simple, that I can just copy the old (backuped) data folder onto the newly created. Is there any way I can figure out with which version I have created the old databases? Perhaps, in a worst case scenario they have been created in 8.0 I will try... Stef ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Forgot to dump old data before re-installing machine
Hi there, I re-installed my machine and forgot to dump my database(s). I naturally still have the whole database folders. For the moment I installed the old postgres version (8.1) to be able to read my data. But how can I read them? It seems that it doesn't work that I just overwrite the new database folder with the old one... Would be too simple, I guess... Can someone please give me a hint on how I should proceed?! Thanks a lot! Stef ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Forgot to dump old data before re-installing machine
Stefan Schwarzer [EMAIL PROTECTED] writes: I re-installed my machine and forgot to dump my database(s). I naturally still have the whole database folders. For the moment I installed the old postgres version (8.1) to be able to read my data. But how can I read them? It seems that it doesn't work that I just overwrite the new database folder with the old one... Would be too simple, I guess... Should work, if you've got the whole $PGDATA directory tree. Maybe you forgot to stop the postmaster while copying the backup into place? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Forgot to dump old data before re-installing machine
And also remember to use the same version of Postgres as the previous installation... It might be helpful to post the tail of your server's log ahen it fails. Best Regards, On Jan 14, 2008 7:58 AM, Tom Lane [EMAIL PROTECTED] wrote: Stefan Schwarzer [EMAIL PROTECTED] writes: I re-installed my machine and forgot to dump my database(s). I naturally still have the whole database folders. For the moment I installed the old postgres version (8.1) to be able to read my data. But how can I read them? It seems that it doesn't work that I just overwrite the new database folder with the old one... Would be too simple, I guess... Should work, if you've got the whole $PGDATA directory tree. Maybe you forgot to stop the postmaster while copying the backup into place? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37N, 78° 30' 59.76E - Hyderabad 18° 32' 57.25N, 73° 56' 25.42E - Pune 37° 47' 19.72N, 122° 24' 1.69 W - San Francisco * http://gurjeet.frihost.net Mail sent from my BlackLaptop device
