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?

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