Hi,
I have two virtual machines with two different versions of Postgresql. One machine contains Postgres 7.4.19 and another has Postgres 8.4.3. I also have other instances of these two virtual machines. I need to transfer the database from one machine to other. Both machines contain a database with the same name, for example: testdb, but with different data/values, but the structure is exactly same.
I want to do the following:
· Take file system level backup from from first machine for Postgres database ‘testdb’ and restore it in another instance that has SAME version of Postgres. i.e. backup the database ‘testdb’ from Postgres 7.4.19 and restore it on another virtual machine with same Postgres version 7.4.19.
· Take file system level backup from from first machine for Postgres database ‘testdb’ and restore it in another instance that has DIFFERENT version of Postgres. i.e. backup the database testdb from Postgres 7.4.19 and restore it to another virtual machine with different Postgres version 8.4.3.
I can achieve it with pg_dump and pg_restore, but not with file level backups. The data directory is /var/lib/pgsql/data that contains base directory amongst others, that contains directories for each database referred to by their OIDs. If I replace the complete data directory of one machine from the instance of another machine (with same or different Postgres version), It is failing to identify OIDs for that database.
It should be possible in Postgres to swap two data directories in two different virtual machines without requiring pg_dump and pg_restore. With me, it doesn’t work in both the cases mentioned above. In first case, it gives an error of missing OID for the database. In second case, it is giving version incompatibility issue.
Is there a way in Postgres to do file system level backup? The objective is to push /var/lib/pgsql/data directory across different virtual machines, without the need to backup and restore Postgres database with ‘pg_dump’ and ‘pg_restore’ commands. Any help will be highly appreciated.
Kind Regards,
Manoj Agarwal