Re: File system level backup

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?