2

I am in process of dumping a huge database, and pg_dump is taking a lot of time. I have to transfer my database to a new server. i have searched a lot online, but everyone suggests the same method of dumping the database, then transferring the file and restoring it on the new server. Is there another way to do this fast?

I have to transfer data which is in v 8.4.20 to v 9.3.5. can i just transfer the folder /var/lib/pgsql/data/base??

I also looked at this: Copying PostgreSQL database to another server

Is this way faster than the usual way?

My db has 756,000 tables and is 200 GB in size.

Community
  • 1
  • 1
user3455531
  • 775
  • 1
  • 14
  • 28

2 Answers2

3

The main complication is moving to a new server. It's going to take a while to move 200 GB. It might be faster to dump to disk, then carry or send the disk to the new server. That would avoid sending 200 GB of data over the wire.

You can probably install 9.3 on the old server, then use pg_upgrade. Running multiple versions of PostgreSQL is commonplace; give each one a different port number. Having done that--having gotten the same version of PostgreSQL running on both the old server and the new server--one of the alternative methods of backup and restore becomes possible.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
2

According to the documentation it is possible to do a file system backup. I would not expect it to work, if you change versions and I would not expect the files to be much smaller than the output of pg_dump. The answer you have linked to, is using pg_dump without writing to a file, but streaming to the target server directly. If that's an option depends on your network. You might also copy the files to a new server, run both versions an parallel and dump directly to the new version on the target machine. Which options would be the best depends heavily on your hardware, network connection, ...

Achim
  • 15,415
  • 15
  • 80
  • 144