Migrating PostgreSQL files to new server

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?

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%PostgreSQL9.3data.
• 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.

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)