Re: Migration of PGDATA

Hi,

is it safe to move the PGDATA directory from one system to another when migrating from one operating system to another?
For example: migrating from Debian to RHEL, or from RHEL4 to RHEL5?
The database is of course shutdown properly, and the PG major versions match.

Or is a dump/restore necessary in these cases?

Thanks,
Martin

--
GMX DSL: Internet, Telefon und Entertainment für nur 19,99 EUR/mtl.!
http://portal.gmx.net/de/go/dsl02

Le 29/03/2010 11:31, "Martin Münstermann" a écrit :
> [...]
> is it safe to move the PGDATA directory from one system to another when migrating from one operating system to another?
> For example: migrating from Debian to RHEL, or from RHEL4 to RHEL5?
> The database is of course shutdown properly, and the PG major versions match.
>
> Or is a dump/restore necessary in these cases?
>

As long as you have the same hardware platform (for example no
32bit/64bit changes) and no difference in compiling options (for example
--disable-integer-datetimes), it should work great.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

Hi,

> is it safe to move the PGDATA directory from one system to another when migrating from one operating system to another?
> For example: migrating from Debian to RHEL, or from RHEL4 to RHEL5?
> The database is of course shutdown properly, and the PG major versions match.
> Or is a dump/restore necessary in these cases?

Besides the other reply, take into account little- versus bigendian
changes if you switch hardware.

But just out of curiosity: why propose against a dump/restore ? I mean:
when I upgrade between major releases I dump as well, as it is
recommended. I never ran into trouble, you know.

Regards,
Robert

Hi,

> > is it safe to move the PGDATA directory from one system to another when
> migrating from one operating system to another?
> > For example: migrating from Debian to RHEL, or from RHEL4 to RHEL5?
> > The database is of course shutdown properly, and the PG major versions
> match.
> > Or is a dump/restore necessary in these cases?
>
> Besides the other reply, take into account little- versus bigendian
> changes if you switch hardware.

Good point. But as we stick to Linux on Intel, we expect no problems there.

> But just out of curiosity: why propose against a dump/restore ? I mean:
> when I upgrade between major releases I dump as well, as it is
> recommended. I never ran into trouble, you know.

It's a question of migration duration aka downtime.
Our PGDATA resides on a SAN, so moving/migrating the directory takes a couple of seconds. A dump/restore cycle would take much longer.

Thanks,
Martin

--
GMX.at - Österreichs FreeMail-Dienst mit über 2 Mio Mitgliedern
E-Mail, SMS & mehr! Kostenlos: http://portal.gmx.net/de/go/atfreemail

Only out of curiosity, is your current server 32 bit or 64?
Are you migrating 32 to 32 or 64 to 64?

Thank you

Renato

Renato Oliveira
Systems Administrator
e-mail: renato(dot)oliveira(at)grant(dot)co(dot)uk

Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
http://www.grant.co.uk/

Grant Instruments (Cambridge) Ltd

Company registered in England, registration number 658133

Registered office address:
29 Station Road,
Shepreth,
CAMBS SG8 6GB
UK

-----Original Message-----

From: pgsql-admin-owner(at)postgresql(dot)org [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of "Martin Münstermann"
Sent: 29 March 2010 11:50
To: Robert Joosten; pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Migration of PGDATA

Hi,

> > is it safe to move the PGDATA directory from one system to another when
> migrating from one operating system to another?
> > For example: migrating from Debian to RHEL, or from RHEL4 to RHEL5?
> > The database is of course shutdown properly, and the PG major versions
> match.
> > Or is a dump/restore necessary in these cases?
>
> Besides the other reply, take into account little- versus bigendian
> changes if you switch hardware.

Good point. But as we stick to Linux on Intel, we expect no problems there.

> But just out of curiosity: why propose against a dump/restore ? I mean:
> when I upgrade between major releases I dump as well, as it is
> recommended. I never ran into trouble, you know.

It's a question of migration duration aka downtime.
Our PGDATA resides on a SAN, so moving/migrating the directory takes a couple of seconds. A dump/restore cycle would take much longer.

Thanks,
Martin

--
GMX.at - Österreichs FreeMail-Dienst mit über 2 Mio Mitgliedern
E-Mail, SMS & mehr! Kostenlos: http://portal.gmx.net/de/go/atfreemail

--
Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

-----Original Message-----

P Please consider the environment before printing this email
CONFIDENTIALITY: The information in this e-mail and any attachments is confidential. It is intended only for the named recipients(s). If you are not the named recipient please notify the sender immediately and do not disclose the contents to another person or take copies.

VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system. Whilst Grant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept liability for any damage which you sustain as a result of software viruses. You should therefore carry out your own virus checks before opening the attachment(s).

OpenXML: For information about the OpenXML file format in use within Grant Instruments please visit our http://www.grant.co.uk/Support/openxml.html

Hi,

> > is it safe to move the PGDATA directory from one system to another when
> migrating from one operating system to another?
> > For example: migrating from Debian to RHEL, or from RHEL4 to RHEL5?
> > The database is of course shutdown properly, and the PG major versions
> match.
> >
> > Or is a dump/restore necessary in these cases?
> >
>
> As long as you have the same hardware platform (for example no
> 32bit/64bit changes) and no difference in compiling options (for example
> --disable-integer-datetimes), it should work great.

There is no difference in the platform or the compiler options.

In case it does not work great, what kind of problems would we experience?
Would PG not startup at all? Or would we experience SQL errors later on or even casual crashes?

Thanks,
Martin

--
Sicherer, schneller und einfacher. Die aktuellen Internet-Browser -
jetzt kostenlos herunterladen! http://portal.gmx.net/de/go/chbrowser

=?iso-8859-1?Q?=22Martin_M=FCnstermann=22?= <mmuenst(at)gmx(dot)de> writes:
> In case it does not work great, what kind of problems would we experience?

In theory pg_control contains enough information to detect such
compatibility problems, so that you'd get a refusal to start.
In practice, maybe not, and the consequences could be arbitrarily
unpleasant. Better keep a pg_dump backup just in case.

One thing I'd particularly caution you to check is that the locale
settings on the new platform are equivalent to those on the old.
We have seen cases where the same locale name produces different
sort orderings on different platforms. That will result in corrupt
(malfunctioning) indexes on text columns.

regards, tom lane

Hi,

Postgres server crashed on windows vista.

From the log:
2010-04-07 07:00:35.694 LOG: could not receive data from client: Unknown winsock error 10061
2010-04-07 07:00:35.694 LOG: could not receive data from client: Unknown winsock error 10061
2010-04-07 07:00:35.694 LOG: unexpected EOF on client connection
2010-04-07 07:00:35.694 LOG: unexpected EOF on client connection
2010-04-07 08:27:53.934 LOG: received fast shutdown request
2010-04-07 08:27:53.934 LOG: aborting any active transactions

Why did the postgres server crashed?
Is it operating system problem or postgres?
Is there any work around ?

Thanks,
Yuval

"Sofer, Yuval" <Yuval_Sofer(at)bmc(dot)com> wrote:

> Postgres server crashed on windows vista.

What causes you to say that the server crashed?

> 2010-04-07 07:00:35.694 LOG: could not receive data from client:
> Unknown winsock error 10061
> 2010-04-07 07:00:35.694 LOG: could not receive data from client:
> Unknown winsock error 10061
> 2010-04-07 07:00:35.694 LOG: unexpected EOF on client connection
> 2010-04-07 07:00:35.694 LOG: unexpected EOF on client connection

This looks like a network disruption or a client machine being
turned off. Sort of. The odd thing is the error code of 10061,
which is supposed to be for "connection refused" -- are you using
anything which might be trying to connect out from the PostgreSQL
server (e.g., dblink)?

> 2010-04-07 08:27:53.934 LOG: received fast shutdown request
> 2010-04-07 08:27:53.934 LOG: aborting any active transactions

This looks like the server was shut down an hour and half after the
broken client connections. Did you shut it down at 8:27?

> Why did the postgres server crashed?

What did you see that makes you think it did?

> Is it operating system problem or postgres?

I don't see any indication of a PostgreSQL problem. By the way,
what version of PostgreSQL is this?

> Is there any work around ?

Without knowing what happened, it's hard to say. Any idea what
might have happened with the network or client machine(s) at 7:00?

If you review this page and follow the suggestions there, you'll
give us more to work with when trying to help:

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

-Kevin