current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 1 down vote favorite
1

I'm in a bit of a pickle here. I managed to copy the whole postgresql installation directory to a backup drive but how would I retrieve it from Linux with the same architecture and version. Is this possible? I wasn't able to do a backup.

share|improve this question
1  
No, this is not possible. You need to use pg_dump and pg_restore – a_horse_with_no_name Oct 29 '15 at 9:05
    
Would this be possible if I do it in another windows pc? And use pg_dump and pg_restore from there? – jtothebee Oct 29 '15 at 9:07
2  
You need to run pg_dump wherever your current Postgres installation is running and pg_restore wherever your new Postgres installation is running. Both are client programs, so you can actually run them on any computer that can connect to the the running Postgres instances – a_horse_with_no_name Oct 29 '15 at 9:12
    
Yes I understand that sir. But since you told me it is not possible from windows to Linux. I don't have access to my old one anymore. All I have is a hard drive of it. The OS has problems that require reinstallation. I have read that you can copy the data folder to the new instance of postgres. – jtothebee Oct 29 '15 at 9:16
2  
It's not possible to copy the raw database files from one operating system to another. pg_dump and pg_restore do work across different operating systems. This is all explained in the manual. – a_horse_with_no_name Oct 29 '15 at 9:19
up vote 1 down vote accepted

There's no way to dump your Windows PostgreSQL database directory using Linux directly1.

You need to start up a virtual machine running Windows, then copy the database directory to that virtual machine. You can then pg_dump -Fc it, and pg_restore the dump to your new blank PostgreSQL install on the Linux machine.

You must make sure that if your PostgreSQL data directory was from 64-bit PostgreSQL you install a 64-bit Windows version and install 64-bit PostgreSQL on it.

If your PostgreSQL data dir is 32-bit it doesn't matter whether you install 32-bit or 64-bit Windows, but you must install 32-bit PostgreSQL.

Licensing restrictions mean that you can't just download a virtual machine image for Vagrant or whatever to run Windows. You will need a Windows install DVD or ISO image. It's possible to download these legally from Microsoft's digital distribution system if you don't already have a DVD or ISO. You can install them without a license key, but it'll disable its self after a few days, and it's not legal to use it without possessing a license.

For that reason, unless the database is really huge one of the best options is to sign up to Amazon EC2 (if you haven't already) and launch a Windows Server virtual machine on EC2. You can install PostgreSQL then copy your database to that virtual machine, run pg_dump, and copy the dump back to your computer. Then just shut down the VM. You can use EC2 for free if you use a micro instance.

1 It's possible you might be able to run PostgreSQL using WINE, but I suspect that'd be even more complicated, if it works at all, and probably not particularly trustworthy. So just use Windows.

share|improve this answer
    
I was considering the same thing. And it looks like the most probable way of retrieving my data. Much appreciated. I'll do that. The database contains records for the system we made for a client for a school project. It took us hours to encode all of them. So I really need this. Just wait till I solve this. I'll give you the rep you deserve, sir – jtothebee Oct 29 '15 at 9:30
    
Everything worked fine as you suggested, sir! – jtothebee Nov 1 '15 at 16:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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