2

We have a Win 2008 server that went down because of cpu failure. We were running parts of the Atlassian stack (JIRA, Confluence, Stash, and Bamboo) on it using PostgreSQL as the database. I now have the harddrive from that dead server mounted in to a Win 7 machine as a secondary harddrive. We unfortunately did not have backups setup. All of the instructions I see for migrating databases to new machines involve running PostgreSQL cli programs against a running PostgreSQL instance, but in my case all I have is a folder not a running PostgreSQL server. I would like to be able to install PostgreSQL on the Win 7 machine, somehow get the data migrated over to the new PostgreSQL installation and off of that secondary drive. Then I would like to export the data out using the standard procedures and move it to a new production server or possibly export using the Atlassian backup tools if we end up moving the data in to the Atlassian cloud service where they use who knows what db engine. Either way though I need the data off the olddrive and running in a new PostgreSQL server to move forward. Anyone have any ideas?

4

Install the latest minor release of the same PostgreSQL major version - e.g. if you were using 9.3.1 before, install 9.3.9. Make sure it's the same architecture - i.e. if you were using a 32-bit PostgreSQL before you must install 32-bit PostgreSQL now.

  • Stop the service using the services control panel (services.msc).
  • Delete %PROGRAMFILES%\PostgreSQL\9.3\data.
  • Copy the old data directory to that location.
  • Get properties on the data directory and in the security tab recursively grant full control to NETWORKSERVICE.
  • Start the service using services.msc
  • immediately set up backups.
  • While not the first to respond (Thanks Joishi!) this seems to be the most clear description for people with similar issues so I am marking this post as the solution. I actually did the migration before seeing your post and it appears to have worked (as far as I can see dbs) without the access right change but I will go back and make that change just in case. Thanks for the post. – Fyndor Jun 23 '15 at 20:12
1

It's been a while since I've done anything like this, but last I recall - If you have ALL of the corresponding files, you should be able to copy them and start up the server .. it will go into recovery mode to validate that it's got everything that it needs. Of course, if it finds that it's missing something, it will likely either not start or complain in the log files that it's missing something (depending on the severity of what it's missing).

The most likely files that you MAY NOT HAVE would be the WAL files (by default stored in the pg_xlog directory .. but could be different if it was configured that way).

If the configuration was different, you will need to make sure that the new environment is suited to match the configuration that postgres is expecting (for example - if the WAL files were located in a different place than default).

The postgres configuration files may also not be where you would expect them ... they COULD be in the base directory of the database, or they could be in the postgres config directory (which I do not recall the exact location of it - you can look in the /etc/init.d/postgres-9.3.sh file, iirc)

  • The one thing I can tell you is that I have a standard Windows install of PostgreSQL 9.3. I almost certainly took the default options for everything. Theoretically just the CPU went down and the harddrive should be fine so all the files should be there and in the standard locations. I see 3 files in that pg_xlog dir under the data dir which appear to be the WAL files. So you are suggesting that I install 9.3 on my Win7 box and replace the data dir in that new install with the data dir from the old install then the dbs will be rebuilt from the WAL files? – Fyndor Jun 22 '15 at 23:11
  • You will need to STOP the postgres service before doing that .. but yes. Default directory on windows is C:\Program Files\PostgreSQL\9.3\data .. replace this whole directory, and I believe it should start up with everything that was in the old database. – Joishi Bodio Jun 23 '15 at 15:02
  • It looks like Craig Ringer has a more complete answer above (basically the same as mine, but with more specific details). Good luck. :) – Joishi Bodio Jun 23 '15 at 15:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.