Thread: File system level backup
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
On Thu, Jul 26, 2012 at 3:39 AM, Manoj Agarwal <ma@ockham.be> wrote: > 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. You definitely cannot take a filesystem level backup from one version and throw it into a different version (perhaps minor versions, but not 7.x vs. 8.x). This is basically what pg_upgrade was created to solve (however with a different purpose). But pg_upgrade definitely doesn't support 7.x, and I'm not even sure that it supports 8.x. In fact, I don't even know that 7.x is a supported version of postgresql in any context any longer. As for the issue of moving a filesystem level backup between identical versions, I believe that should work (although I have no clue if there were bugs preventing this in a version as old as 7.x). Can you provide exact details & commands of what you're trying to do, and the exact errors you're seeing?
Manoj Agarwal wrote on 26.07.2012 12:39: > 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. Unrelated: why are you still using 7.3 It was end-of-live 2 years ago. In general: you can *not* just copy files between different versions or servers. The only way this works if both systems are running the exact same version, the exact same operating system and are using the same architecture. (So essentially a cold backup can be used to restore the server where the backup was taken). > It should be possible in Postgres to swap two data directories in two > different virtual machines without requiring pg_dump and pg_restore. No, this is not possible especially not with such an outdated version (I actually consider 8.4 as somewhat "outdated" as well). In your case pg_dump is the only way to go.
"Manoj Agarwal" <ma@ockham.be> wrote: > 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. > 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. You cannot use file system level copy for individual databases in a cluster. -Kevin
Hi, I have two identical Centos 4.6 Virtual machines with postgresql database from different customers. Can't I swap the databases between these two machines using file level copy, without requiring pg_dump and pg_restore? I don't wish to use file system level copy for individual database in a cluster, but as a whole, is it possible? Kind Regards, Manoj Agarwal -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] Sent: Thursday, August 02, 2012 2:18 AM To: Manoj Agarwal; pgsql-general@postgresql.org Subject: Re: [GENERAL] File system level backup "Manoj Agarwal" <ma@ockham.be> wrote: > 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. > 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. You cannot use file system level copy for individual databases in a cluster. -Kevin
"Manoj Agarwal" <ma@ockham.be> wrote: > From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] >> "Manoj Agarwal" <ma@ockham.be> wrote: >>> 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. >> >>> 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. That's old, and long out of support. http://www.postgresql.org/support/versioning/ >> You cannot use file system level copy for individual databases in >> a cluster. > > I have two identical Centos 4.6 Virtual machines with postgresql > database from different customers. Can't I swap the databases > between these two machines using file level copy, without > requiring pg_dump and pg_restore? I don't wish to use file system > level copy for individual database in a cluster, but as a whole, > is it possible? Yes, if you copy the whole database *cluster* (everything under the the directory you get when you run `show data_directory;` -- you can't pick individual databases). Be sure to read this page carefully: http://www.postgresql.org/docs/7.4/interactive/backup-file.html -Kevin