Postgresql – Migrate Postgres from VM to VM

migrationpostgresql

I have 2 VMs, both are identical in structural terms (came from the same snapshot, nothing installed, upgraded or removed).

One VM is "clean", never used.

The other VMs was heavily used and it has some data stored in the Postgres DB.

Is there a way to migrate the Postgres database from the heavily used VM to the clean VM just by copying the DB files from a specific folder?

For instance, let's say that Postgres (I'm assuming this part) writes all its files in the directory XYZ. If I copy all files from that XYZ directory to the clean VM, will Postgres work?

I'm not worried if that is the correct way to do that or not. This is just a PoC and I need to do it with the least effort possible and copying files around seems to be the best fit here.

Best Answer

Absolutely no! Do not try this. The normal way of doing this is a proper dump and restore and copy the dumped file from one VM to another VM.

pg_dump -d db -f db.sql

You might want to compress the file with xz or some other compressor before transferring it.

transfer db.sql to new VM after creating the database with createdb db (adding any other option you require)

then

psql -d db -f db.sql

or use pg_restore

Related Question