Manually Importing Tablespaces from Postgres 8.0 beta-1 to Postgres 8.0 (for windows)
Hi All! Here's my problem, last year I setup an application on a pc running windows XP-Pro. The application uses JSP and PostgreSQL as its database. Last friday, the motherboard of the pc crashed due to age (its been around a long time).. we got it replaced, but unfortunately I'm getting the blue screen from XP Pro everytime I boot up, and during safe mode the pc just reboots entirely.
So is the hard drive was still intact I just opted to just extract the postgres data from it and just reformat the pc. I was able to copy over the entire postgres folder from the hard drive to my laptop, which has postgres 8.0 installed and running. My next step was to open the databases from the copied data and export them to scripts so that I can just run them later on the reformatted pc.
However, postgreSQL seems to have a rather unique way of laying out their tablespace data... so I'm having a difficult time figuring out how to open the databases... I've tried copying the contents of the data folder to a newly created tablespace folder, I've tried overwriting my installation's data folder and even tried switching the postgres installation entirely all to no avail....
At the moment I am going to install postgreSQL 8.0 beta on another PC and try the same list of procedures... however, if there is any other way I can do it easily, I would greatly appreciate the help... especially since I am not certain if my current attempt will work at all...
If you need me to clarify on anything in particular let me know... Thanks in advance for all your help!
So is the hard drive was still intact I just opted to just extract the postgres data from it and just reformat the pc. I was able to copy over the entire postgres folder from the hard drive to my laptop, which has postgres 8.0 installed and running. My next step was to open the databases from the copied data and export them to scripts so that I can just run them later on the reformatted pc.
However, postgreSQL seems to have a rather unique way of laying out their tablespace data... so I'm having a difficult time figuring out how to open the databases... I've tried copying the contents of the data folder to a newly created tablespace folder, I've tried overwriting my installation's data folder and even tried switching the postgres installation entirely all to no avail....
At the moment I am going to install postgreSQL 8.0 beta on another PC and try the same list of procedures... however, if there is any other way I can do it easily, I would greatly appreciate the help... especially since I am not certain if my current attempt will work at all...
If you need me to clarify on anything in particular let me know... Thanks in advance for all your help!
earth man2
There seems to be nothing wrong with your approach. What exact error do you encounter, when you open the database ? Have you got the postgresql service started ?
ASKER
yup the service starts ok, of course I had to stop it first then start it up again right after moving the files...
The error I get is concerning the port that I'm using ... its saying its not enabled or something but the pg_hba.conf file is properly configured... quite weird...
The error I get is concerning the port that I'm using ... its saying its not enabled or something but the pg_hba.conf file is properly configured... quite weird...
Look in your postgresql.conf file for :-
listen_addresses = 'localhost,192.168.127.8' # what IP interface(s) to listen on;
# defaults to localhost, '*' = any
port = 5432
Make sure your ip addresses match the pc and port 5432 is not being blocked by your firewall !
listen_addresses = 'localhost,192.168.127.8' # what IP interface(s) to listen on;
# defaults to localhost, '*' = any
port = 5432
Make sure your ip addresses match the pc and port 5432 is not being blocked by your firewall !
ASKER
earthman2 thanks for all your input, yes my postgres.conf file settings where correct and not blocked by firewall...
In anycase, I ended up just installing postgres8.0-beta1 on a different pc then copying the data folder completely to the newly installed database cluster... that seemed to work and I was able to extract my data....
In anycase, I ended up just installing postgres8.0-beta1 on a different pc then copying the data folder completely to the newly installed database cluster... that seemed to work and I was able to extract my data....
pg_dumpall > data.sql
psql -f data.sql
for normal UNIX you can pipe over network without having to waste space with dump file
psql -f data.sql
for normal UNIX you can pipe over network without having to waste space with dump file
My answer is official way to keep data on significant server upgrades.
ASKER
gheist,
Thanks very much for your input and comments, however, that method would not work in this described case since it requires that the PostgreSQL server to be running on the machine in question, as described above, I was attempting to import the databases from another hard drive, whose OS cannot start on its own and thus only had access to the raw data files which I first needed to import before I could dump the actual database information...
Thanks very much for your input and comments, however, that method would not work in this described case since it requires that the PostgreSQL server to be running on the machine in question, as described above, I was attempting to import the databases from another hard drive, whose OS cannot start on its own and thus only had access to the raw data files which I first needed to import before I could dump the actual database information...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.