Обсуждение: Migrate postgres to newer hardware



On Tue, 2010-03-30 at 12:36 +0100, Renato Oliveira wrote:
> 
> Szymon,
>
>
>
> We had Slony running previously, but it lagged behind so badly that
> never managed to catch up.
>
>
>
> Hardware
>
> AMD 1.8GHz 32 Bits
>
> 8GB RAM DDR1
>
> 300GB Disk single volume
>
>
>
> Database:
>
> Postgres 8.2.24
>
> 160GB in size
>
> There are thousands of tables, apparently for each new device a new
> table is created.

I *think* I remember reading a while back case on the Slony list that
some of the internal queries where not very efficient for databases with
large numbers of tables.
>
> The DB  grows around 1GB every 2 days.

That's not material.  We use slony replicate much larger databases with
a higher growth rate than that.
>
> Do you still think slony would work?

It might, it might not.  I'd ask for assistance on the Slony list.

Also, Londiste is another replication engine that can be used for DB
upgrades.

>
> Renato Oliveira
> Systems Administrator
> e-mail: renato.oliveira@grant.co.uk
>
> Tel: +44 (0)1763 260811
> Fax: +44 (0)1763 262410
> 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
>
>
>
> From: Szymon Guz [mailto:mabewlun@gmail.com]
> Sent: 30 March 2010 12:29
> To: Renato Oliveira
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Migrate postgres to newer hardware
>
>
>
>
>
>
> 2010/3/30 Renato Oliveira <renato.oliveira@grant.co.uk>
>
> Dear All,
>
> What would be the easiest and fastest way to migrate Postgres 8.2.24
> 32 BIT to a new server 64 Bit.
>
> The existing server runs on 32 bit architecture and has a database as
> big as 160GB.
>
> We initially thought of using PITR, but as one of the PITR
> requirements is both machines need to be similar.
>
> This similarity needs to be even in architecture? I think I read
> something which says “Yes”.
>
> If we cannot use PITR what would be the best approach, we can’t have
> down time I am afraid.
>
> Any ideas or suggestions would be very welcome.
>
>
>
>
>
>
>
> I'd use Slony as a replication tool so the data would be copied to the
> new serwer while the old still works. After initial copy Slony will
> copy changes made during making the copy. Later (when the replication
> lag is small) it should be enough to stop application, reconfigure it
> for the new database, get rid of replication so the new slave database
> will restore all triggers and then start the application for using the
> new database.
>
>
> Slony uses pure SQL for copying the data so there is no problem with
> the differences in the hardware.
>
>
>
>
>
> regards
>
>
> Szymon Guz
>
>
>
>
>
>
>
> 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 website
--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

Hi Brad,

Thank you for your reply, really appreciated.

Do you know how much load slony gives to your servers?

Thank you again


Renato




Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.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: Brad Nicholson [mailto:bnichols@ca.afilias.info]
Sent: 30 March 2010 13:20
To: Renato Oliveira
Cc: Szymon Guz; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Migrate postgres to newer hardware

On Tue, 2010-03-30 at 12:36 +0100, Renato Oliveira wrote:
> ?
> Szymon,
>
>
>
> We had Slony running previously, but it lagged behind so badly that
> never managed to catch up.
>
>
>
> Hardware
>
> AMD 1.8GHz 32 Bits
>
> 8GB RAM DDR1
>
> 300GB Disk single volume
>
>
>
> Database:
>
> Postgres 8.2.24
>
> 160GB in size
>
> There are thousands of tables, apparently for each new device a new
> table is created.

I *think* I remember reading a while back case on the Slony list that
some of the internal queries where not very efficient for databases with
large numbers of tables.
>
> The DB  grows around 1GB every 2 days.

That's not material.  We use slony replicate much larger databases with
a higher growth rate than that.
>
> Do you still think slony would work?

It might, it might not.  I'd ask for assistance on the Slony list.

Also, Londiste is another replication engine that can be used for DB
upgrades.

>
> Renato Oliveira
> Systems Administrator
> e-mail: renato.oliveira@grant.co.uk
>
> Tel: +44 (0)1763 260811
> Fax: +44 (0)1763 262410
> 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
>
>
>
> From: Szymon Guz [mailto:mabewlun@gmail.com]
> Sent: 30 March 2010 12:29
> To: Renato Oliveira
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Migrate postgres to newer hardware
>
>
>
>
>
>
> 2010/3/30 Renato Oliveira <renato.oliveira@grant.co.uk>
>
> Dear All,
>
> What would be the easiest and fastest way to migrate Postgres 8.2.24
> 32 BIT to a new server 64 Bit.
>
> The existing server runs on 32 bit architecture and has a database as
> big as 160GB.
>
> We initially thought of using PITR, but as one of the PITR
> requirements is both machines need to be similar.
>
> This similarity needs to be even in architecture? I think I read
> something which says “Yes”.
>
> If we cannot use PITR what would be the best approach, we can’t have
> down time I am afraid.
>
> Any ideas or suggestions would be very welcome.
>
>
>
>
>
>
>
> I'd use Slony as a replication tool so the data would be copied to the
> new serwer while the old still works. After initial copy Slony will
> copy changes made during making the copy. Later (when the replication
> lag is small) it should be enough to stop application, reconfigure it
> for the new database, get rid of replication so the new slave database
> will restore all triggers and then start the application for using the
> new database.
>
>
> Slony uses pure SQL for copying the data so there is no problem with
> the differences in the hardware.
>
>
>
>
>
> regards
>
>
> Szymon Guz
>
>
>
>
>
>
>
> 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 website
--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.





-----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
toanother person or take copies. 

VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system.
WhilstGrant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept
liabilityfor any damage which you sustain as a result of software viruses. You should therefore carry out your own
viruschecks 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 Renato.

I would follow the ancient method: perform a pg_dump / pg_restore

Yes, you will have to take offline database for a long period.

And yes, it would be a great moment to perform a 8.4 upgrade. Performance is far superior, restore is far faster...
... and yes, it could give you many problems if you don't perform many test in order to address all queries without explicit type conversions before real migration, but I think it's the best moment to deal with a very convenient upgrade. 

We have performed this upgrade last week with a gforge (with only 25GB database) and having also to upgrade to new tsearch2 and everything is running smooth.

-----Original Message-----
From: Renato Oliveira <renato.oliveira@grant.co.uk>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: [ADMIN] Migrate postgres to newer hardware
Date: Tue, 30 Mar 2010 12:18:36 +0100

Dear All,

 

What would be the easiest and fastest way to migrate Postgres 8.2.24 32 BIT to a new server 64 Bit.

 

The existing server runs on 32 bit architecture and has a database as big as 160GB.

 

We initially thought of using PITR, but as one of the PITR requirements is both machines need to be similar.

This similarity needs to be even in architecture? I think I read something which says “Yes”.

 

If we cannot use PITR what would be the best approach, we can’t have down time I am afraid.

 

Any ideas or suggestions would be very welcome.

 

Thank you very much

 

Best regards

 

Renato

 

 


  Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk   Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
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  
 
 
   

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 website

On Tue, 2010-03-30 at 16:29 +0200, Iñigo Martinez Lasala wrote:
> Hi Renato.
>
> I would follow the ancient method: perform a pg_dump / pg_restore

This is the easiest approach.  The problem is that a lot of people have
contractual SLA's that do not allow them to take their systems down long
enough to do a dump and restore upgrade.

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

That is very true and well pointed out.





Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.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: Brad Nicholson [mailto:bnichols@ca.afilias.info]
Sent: 30 March 2010 15:38
To: Iñigo Martinez Lasala
Cc: Renato Oliveira; pgsql-admin
Subject: Re: [ADMIN] Migrate postgres to newer hardware

On Tue, 2010-03-30 at 16:29 +0200, Iñigo Martinez Lasala wrote:
> Hi Renato.
>
> I would follow the ancient method: perform a pg_dump / pg_restore

This is the easiest approach.  The problem is that a lot of people have
contractual SLA's that do not allow them to take their systems down long
enough to do a dump and restore upgrade.

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.





-----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
toanother person or take copies. 

VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system.
WhilstGrant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept
liabilityfor any damage which you sustain as a result of software viruses. You should therefore carry out your own
viruschecks 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 Renato,

dump/restore is the way to go. I suppose, you don't want to compile
Postgres for 32 bit on the new machine which might work and might allow
you to do the PITR migration.

And "downtime is not an option" is always a sign of insufficient
planning beforehand. There is no system which doesn't need an upgrade or
reboot or whatever, so there will be downtime and it needs to be
considered during system analysis.

In my experience, it is often just a matter of communicating: "Because
of hardware upgrades, the system XYZ will not be available on ..."

After all the switch won't be without interruption - you need to switch
to the new server anyway.

Tino, having migrated a 300+ GB database.

On Tue, Mar 30, 2010 at 04:29:29PM +0200, Iñigo Martinez Lasala wrote:

> I would follow the ancient method: perform a pg_dump / pg_restore
>
> Yes, you will have to take offline database for a long period.
>
> And yes, it would be a great moment to perform a 8.4 upgrade.
> Performance is far superior, restore is far faster...
> ... and yes, it could give you many problems if you don't perform many
> test in order to address all queries without explicit type conversions
> before real migration, but I think it's the best moment to deal with a
> very convenient upgrade.
>
> We have performed this upgrade last week with a gforge (with only 25GB
> database) and having also to upgrade to new tsearch2 and everything is
> running smooth.
>
> -----Original Message-----
> From: Renato Oliveira <renato.oliveira@grant.co.uk>
> To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
> Subject: [ADMIN] Migrate postgres to newer hardware
> Date: Tue, 30 Mar 2010 12:18:36 +0100
>
> Dear All,
>
>
>
> What would be the easiest and fastest way to migrate Postgres 8.2.24 32
> BIT to a new server 64 Bit.
>
>
>
> The existing server runs on 32 bit architecture and has a database as
> big as 160GB.
>
>
>
> We initially thought of using PITR, but as one of the PITR requirements
> is both machines need to be similar.
>
> This similarity needs to be even in architecture? I think I read
> something which says “Yes”.
>
>
>
> If we cannot use PITR what would be the best approach, we can’t have
> down time I am afraid.
>
>
>
> Any ideas or suggestions would be very welcome.
>
>
>
> Thank you very much
>
>
>
> Best regards
>
>
>
> Renato
>
>
>
>
>
>
>
> Renato Oliveira
> Systems Administrator
> e-mail: renato.oliveira@grant.co.uk
>
> Tel: +44 (0)1763 260811
> Fax: +44 (0)1763 262410
> 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
>
>
>
>
>
>
>
>
>
> 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 website
>

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.lichtkreis-chemnitz.de
www.tisc.de

If I use postgres 32 bit will it benefit from the extra memory on the system?

I don't think it will.

Thank you very much

Renato



Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.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@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tino Schwarze
Sent: 30 March 2010 15:39
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Migrate postgres to newer hardware

Hi Renato,

dump/restore is the way to go. I suppose, you don't want to compile
Postgres for 32 bit on the new machine which might work and might allow
you to do the PITR migration.

And "downtime is not an option" is always a sign of insufficient
planning beforehand. There is no system which doesn't need an upgrade or
reboot or whatever, so there will be downtime and it needs to be
considered during system analysis.

In my experience, it is often just a matter of communicating: "Because
of hardware upgrades, the system XYZ will not be available on ..."

After all the switch won't be without interruption - you need to switch
to the new server anyway.

Tino, having migrated a 300+ GB database.

On Tue, Mar 30, 2010 at 04:29:29PM +0200, Iñigo Martinez Lasala wrote:

> I would follow the ancient method: perform a pg_dump / pg_restore
>
> Yes, you will have to take offline database for a long period.
>
> And yes, it would be a great moment to perform a 8.4 upgrade.
> Performance is far superior, restore is far faster...
> ... and yes, it could give you many problems if you don't perform many
> test in order to address all queries without explicit type conversions
> before real migration, but I think it's the best moment to deal with a
> very convenient upgrade.
>
> We have performed this upgrade last week with a gforge (with only 25GB
> database) and having also to upgrade to new tsearch2 and everything is
> running smooth.
>
> -----Original Message-----
> From: Renato Oliveira <renato.oliveira@grant.co.uk>
> To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
> Subject: [ADMIN] Migrate postgres to newer hardware
> Date: Tue, 30 Mar 2010 12:18:36 +0100
>
> Dear All,
>
>
>
> What would be the easiest and fastest way to migrate Postgres 8.2.24 32
> BIT to a new server 64 Bit.
>
>
>
> The existing server runs on 32 bit architecture and has a database as
> big as 160GB.
>
>
>
> We initially thought of using PITR, but as one of the PITR requirements
> is both machines need to be similar.
>
> This similarity needs to be even in architecture? I think I read
> something which says “Yes”.
>
>
>
> If we cannot use PITR what would be the best approach, we can’t have
> down time I am afraid.
>
>
>
> Any ideas or suggestions would be very welcome.
>
>
>
> Thank you very much
>
>
>
> Best regards
>
>
>
> Renato
>
>
>
>
>
>
>
> Renato Oliveira
> Systems Administrator
> e-mail: renato.oliveira@grant.co.uk
>
> Tel: +44 (0)1763 260811
> Fax: +44 (0)1763 262410
> 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
>
>
>
>
>
>
>
>
>
> 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 website
>

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.lichtkreis-chemnitz.de
www.tisc.de

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.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
toanother person or take copies. 

VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system.
WhilstGrant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept
liabilityfor any damage which you sustain as a result of software viruses. You should therefore carry out your own
viruschecks 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

Are there any commercial solutions out there for migrating large DBs?

Renato



Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.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@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tino Schwarze
Sent: 30 March 2010 15:39
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Migrate postgres to newer hardware

Hi Renato,

dump/restore is the way to go. I suppose, you don't want to compile
Postgres for 32 bit on the new machine which might work and might allow
you to do the PITR migration.

And "downtime is not an option" is always a sign of insufficient
planning beforehand. There is no system which doesn't need an upgrade or
reboot or whatever, so there will be downtime and it needs to be
considered during system analysis.

In my experience, it is often just a matter of communicating: "Because
of hardware upgrades, the system XYZ will not be available on ..."

After all the switch won't be without interruption - you need to switch
to the new server anyway.

Tino, having migrated a 300+ GB database.

On Tue, Mar 30, 2010 at 04:29:29PM +0200, Iñigo Martinez Lasala wrote:

> I would follow the ancient method: perform a pg_dump / pg_restore
>
> Yes, you will have to take offline database for a long period.
>
> And yes, it would be a great moment to perform a 8.4 upgrade.
> Performance is far superior, restore is far faster...
> ... and yes, it could give you many problems if you don't perform many
> test in order to address all queries without explicit type conversions
> before real migration, but I think it's the best moment to deal with a
> very convenient upgrade.
>
> We have performed this upgrade last week with a gforge (with only 25GB
> database) and having also to upgrade to new tsearch2 and everything is
> running smooth.
>
> -----Original Message-----
> From: Renato Oliveira <renato.oliveira@grant.co.uk>
> To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
> Subject: [ADMIN] Migrate postgres to newer hardware
> Date: Tue, 30 Mar 2010 12:18:36 +0100
>
> Dear All,
>
>
>
> What would be the easiest and fastest way to migrate Postgres 8.2.24 32
> BIT to a new server 64 Bit.
>
>
>
> The existing server runs on 32 bit architecture and has a database as
> big as 160GB.
>
>
>
> We initially thought of using PITR, but as one of the PITR requirements
> is both machines need to be similar.
>
> This similarity needs to be even in architecture? I think I read
> something which says “Yes”.
>
>
>
> If we cannot use PITR what would be the best approach, we can’t have
> down time I am afraid.
>
>
>
> Any ideas or suggestions would be very welcome.
>
>
>
> Thank you very much
>
>
>
> Best regards
>
>
>
> Renato
>
>
>
>
>
>
>
> Renato Oliveira
> Systems Administrator
> e-mail: renato.oliveira@grant.co.uk
>
> Tel: +44 (0)1763 260811
> Fax: +44 (0)1763 262410
> 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
>
>
>
>
>
>
>
>
>
> 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 website
>

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.lichtkreis-chemnitz.de
www.tisc.de

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.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
toanother person or take copies. 

VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system.
WhilstGrant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept
liabilityfor any damage which you sustain as a result of software viruses. You should therefore carry out your own
viruschecks 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

We had a similar situation last week, 32-bit to 64-bit OS. We decided to keep our PostgreSQL 32-bit (our
backup/replicationservers are still running 32-bit). We are running it on a 64-bit xen image. We used the package
managerto install the 32-bit binaries. Building cross-platform is tricky, so we avoided it. 

We then did the easy rsync method. With the old DB running, new DB stopped:
1) rsync the /data/ directory
1b) rsync it again (for large DBs, this will get any changes since the long initial rsync)
2) shutdown the old DB
3) final rsync (took us 20 minutes on 5GB)
4) starup new DB

Matt

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tino Schwarze
Sent: Tuesday, March 30, 2010 9:39 AM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Migrate postgres to newer hardware

Hi Renato,

dump/restore is the way to go. I suppose, you don't want to compile
Postgres for 32 bit on the new machine which might work and might allow
you to do the PITR migration.

And "downtime is not an option" is always a sign of insufficient
planning beforehand. There is no system which doesn't need an upgrade or
reboot or whatever, so there will be downtime and it needs to be
considered during system analysis.

In my experience, it is often just a matter of communicating: "Because
of hardware upgrades, the system XYZ will not be available on ..."

After all the switch won't be without interruption - you need to switch
to the new server anyway.

Tino, having migrated a 300+ GB database.

On Tue, Mar 30, 2010 at 04:29:29PM +0200, Iñigo Martinez Lasala wrote:

> I would follow the ancient method: perform a pg_dump / pg_restore
>
> Yes, you will have to take offline database for a long period.
>
> And yes, it would be a great moment to perform a 8.4 upgrade.
> Performance is far superior, restore is far faster...
> ... and yes, it could give you many problems if you don't perform many
> test in order to address all queries without explicit type conversions
> before real migration, but I think it's the best moment to deal with a
> very convenient upgrade.
>
> We have performed this upgrade last week with a gforge (with only 25GB
> database) and having also to upgrade to new tsearch2 and everything is
> running smooth.
>
> -----Original Message-----
> From: Renato Oliveira <renato.oliveira@grant.co.uk>
> To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
> Subject: [ADMIN] Migrate postgres to newer hardware
> Date: Tue, 30 Mar 2010 12:18:36 +0100
>
> Dear All,
>
>
>
> What would be the easiest and fastest way to migrate Postgres 8.2.24 32
> BIT to a new server 64 Bit.
>
>
>
> The existing server runs on 32 bit architecture and has a database as
> big as 160GB.
>
>
>
> We initially thought of using PITR, but as one of the PITR requirements
> is both machines need to be similar.
>
> This similarity needs to be even in architecture? I think I read
> something which says “Yes”.
>
>
>
> If we cannot use PITR what would be the best approach, we can’t have
> down time I am afraid.
>
>
>
> Any ideas or suggestions would be very welcome.
>
>
>
> Thank you very much
>
>
>
> Best regards
>
>
>
> Renato
>
>
>
>
>
>
>
> Renato Oliveira
> Systems Administrator
> e-mail: renato.oliveira@grant.co.uk
>
> Tel: +44 (0)1763 260811
> Fax: +44 (0)1763 262410
> 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
>
>
>
>
>
>
>
>
>
> 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 website
>

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.lichtkreis-chemnitz.de
www.tisc.de

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

2010/3/30 Renato Oliveira <renato.oliveira@grant.co.uk>:
> If I use postgres 32 bit will it benefit from the extra memory on the system?
>
> I don't think it will.
>
> Thank you very much
>
> Renato
>

Depending on the system, with a 32Bit OS you can only address 2 or 3
GB of continuous memory. 64Bit OS's have not this limitation.

Renato Oliveira <renato.oliveira@grant.co.uk> writes:
> If I use postgres 32 bit will it benefit from the extra memory on the system?

Yes, although perhaps not as much as you'd get with a 64-bit build.
You still get the ability to have more than 4GB worth of kernel-managed
disk cache, and in many situations that's all the value of a 64-bit
machine anyway.  You can find lots and lots about that in the
pgsql-performance archives.

            regards, tom lane

On Tue, Mar 30, 2010 at 8:51 AM, Renato Oliveira
<renato.oliveira@grant.co.uk> wrote:
> If I use postgres 32 bit will it benefit from the extra memory on the system?

Indirectly, yes.  No individual PG process will be able to address
more than 4 gbytes of memory.  Assuming you have a 64-bit OS living
underneath, however, that may not matter much.  You'll potentially be
somewhat constrained in the sane values you can use for shared_buffers
(which, on a 16 gbyte box for example, I'd probably start in the 4
gbyte range and tune from there -- not an option in a 32-bit install).

But leaving aside effective_cache_size (and, as mentioned, potentially
shared_buffers), none of your config values are likely to approach the
4 gbyte boundary -- and in the case of effective_cache_size, that
isn't actually directly addressed by postgres, anyway.  It's just used
by the planner to calculate the likelihood of a given page it needs
being in the OS buffer cache, instead of on disk.

I've had production systems with a 32-bit postgres running quite
happily on a 64-bit OS.

rls

--
:wq

Yes, you only have that two possibilities, I think.

PITR is not an option. I tested the same, from 7.4 32bit to 7.4 64bit and didn't work. Later, when I asked here, I was told why not.

The problem with slony is that you have to manually create tables in destination database and all database model (procedures, triggers, sequences, views, etc). If your application creates new tables, you will have to deal with this prior starting migration, or at least disable the creation of new tables.

Slony is asynchronous, so you will have to ensure that all changes have been committed to new database before changing your applications or exchanging IP addresses.
Slony also add many triggers and special tables to both databases (master and slave). So, after migration, you will have to delete them. It's not difficult but don't forget to do it.

By the way, are you sure your database is 160GB? Including indexes? There are strategies in order to perform a faster pg_restore...
For example, if you migrate your database schema but don't create indexes, then migrate data and finally create pending indexes restore will be faster. With pg 8.4 restore is very fast, so it will take less time that export.

Anyway, if you cannot leave database down for a day, I think slony will be your best bet, although it's not exempt of problems. :)

-----Original Message-----
From: Renato Oliveira <renato.oliveira@grant.co.uk>
To: Iñigo Martinez Lasala <imartinez@vectorsf.com>
Subject: RE: [ADMIN] Migrate postgres to newer hardware
Date: Tue, 30 Mar 2010 15:47:27 +0100

 Hey Iñigo,

 

Thank you very much for your reply.

 

I would love to do just that, but unfortunately I can’t it is not as simple as that.

 

I would love if the application had been built in with this in mind…

 

To give you an idea; the pg_dump takes 15 hours and I attempted a restore yesterday and it took 14 hours and 21 min.

It would not be viable for us and specially I cannot have the system down more than maximum 30 min without the risk of losing data and customers not having alerts.

 

I don’t think I will be able to use PITR to migrate to new servers specially if it is 64 bit and to migrate to another 32 bit is no gain, as we need more memory.

 

As far as can gather there are only two ways:

a)   Slony type

b)   Pg_dump

 

Is that correct ? Do you guys have any other ways?

 

Renato

 

 


  Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk   Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
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  
  From: Iñigo Martinez Lasala [mailto:imartinez@vectorsf.com]
Sent: 30 March 2010 15:29
To: Renato Oliveira
Cc: pgsql-admin
Subject: Re: [ADMIN] Migrate postgres to newer hardware


 

Hi Renato.

I would follow the ancient method: perform a pg_dump / pg_restore

Yes, you will have to take offline database for a long period.

And yes, it would be a great moment to perform a 8.4 upgrade. Performance is far superior, restore is far faster...
... and yes, it could give you many problems if you don't perform many test in order to address all queries without explicit type conversions before real migration, but I think it's the best moment to deal with a very convenient upgrade. 

We have performed this upgrade last week with a gforge (with only 25GB database) and having also to upgrade to new tsearch2 and everything is running smooth.

-----Original Message-----
From: Renato Oliveira <renato.oliveira@grant.co.uk>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: [ADMIN] Migrate postgres to newer hardware
Date: Tue, 30 Mar 2010 12:18:36 +0100

Dear All,

 

What would be the easiest and fastest way to migrate Postgres 8.2.24 32 BIT to a new server 64 Bit.

 

The existing server runs on 32 bit architecture and has a database as big as 160GB.

 

We initially thought of using PITR, but as one of the PITR requirements is both machines need to be similar.

This similarity needs to be even in architecture? I think I read something which says “Yes”.

 

If we cannot use PITR what would be the best approach, we can’t have down time I am afraid.

 

Any ideas or suggestions would be very welcome.

 

Thank you very much

 

Best regards

 

Renato

 

 


  Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk   Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
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  
 
 
   

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 website


 
   

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 website

> -----Original Message-----
> From: Renato Oliveira <renato.oliveira@grant.co.uk>
> To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
> Subject: [ADMIN] Migrate postgres to newer hardware
> Date: Tue, 30 Mar 2010 12:18:36 +0100
>
> Dear All,
>
>
>
> What would be the easiest and fastest way to migrate Postgres 8.2.24 32 BIT
> to a new server 64 Bit.
>
>
>
> The existing server runs on 32 bit architecture and has a database as big as
> 160GB.
>
>
>
> We initially thought of using PITR, but as one of the PITR requirements is
> both machines need to be similar.
>
> This similarity needs to be even in architecture? I think I read something
> which says “Yes”.
>
>
>
> If we cannot use PITR what would be the best approach, we can’t have down
> time I am afraid.
>
>
>
> Any ideas or suggestions would be very welcome.
>
>
>
> Thank you very much
>
>
>
> Best regards
>
>
>
> Renato
>
>
>
>
>
>
>   Renato Oliveira
> Systems Administrator
> e-mail: renato.oliveira@grant.co.uk   Tel: +44 (0)1763 260811
> Fax: +44 (0)1763 262410
> 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
>
>
>
>
> 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 website
>
>
>
>
>
> 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 website
>

You can use the pg_dump method and use pgpool to replicate the new
data, then when sinchronized, switch off the old node.

On Tue, 2010-03-30 at 16:38 +0200, Tino Schwarze wrote:
> Hi Renato,
>
> dump/restore is the way to go. I suppose, you don't want to compile
> Postgres for 32 bit on the new machine which might work and might allow
> you to do the PITR migration.
>
> And "downtime is not an option" is always a sign of insufficient
> planning beforehand.

First, there is a big difference between saying "downtime is not an
option" and "several hours of downtime is not an option".

Lack of planning is launching a system without having the procedures and
tooling in place to cope with the technical issues while functioning
within your contractual constraints.

Many of people have tight contracts about system availability with end
customers, often with financial penalties associated for failure to
comply.  Dump and restore of a large system is something that can take a
very long time, and can't always fit inside those maintenance windows.

> There is no system which doesn't need an upgrade or
> reboot or whatever, so there will be downtime and it needs to be
> considered during system analysis.

Correct.  One of the reasons we (Afilias) wrote Slony was so that we
could quickly move the services from one database to another with a
short application outage.  That gives us the freedom to do many
maintenances without having the application down for extended periods.
PG upgrades fit nicely into this.  The only outage is a brief one to
move master and re-point the target.  You can measure the time in
minutes (or even seconds if your procedures are automated enough).

> In my experience, it is often just a matter of communicating: "Because
> of hardware upgrades, the system XYZ will not be available on ..."

Again, this will depend on business drivers.

> After all the switch won't be without interruption - you need to switch
> to the new server anyway.

There is a very big difference between saying the system will be down
for a short duration during a Slony switchover and the system will be
down for several hours while doing a dump and restore.

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

Renato Oliveira wrote:
> Are there any commercial solutions out there for migrating large DBs?
>

I'm not aware of any.  The main way to address this problem by throwing
money at it is to hire someone extremely familiar with PostgreSQL
replication technology and figure out how to customize one of the
available approaches (Slony, Londiste, PITR, dump/restore) to match your
application.  For example, in some cases it's possible to record
database changes on the application side, replicate the database via one
of the fast online approaches like PITR, and then synchronize just the
changes made in the master while that was happening for a fast
switch-over to a new version.  It's not unheard for that to require
small application changes to support, to provide an easier way to log
the difference between the two.

If you can't take the additional load of Slony and have minimal
tolerance for downtime, you really need to come up with a long-term
approach to coping with that from an application architecture
perspective.  Unfortunately you're not going to find any quick fix for
that combination of requirements.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> What would be the easiest and fastest way to
> migrate Postgres 8.2.24 32 BIT to a new server 64 Bit.
...
> If we cannot use PITR what would be the best approach,
> we can't have down time I am afraid.
>
> Any ideas or suggestions would be very welcome.

You could also consider Bucardo. Its advantage is that it
does not TRUNCATE and COPY over the entire data on startup
(unless you ask it to), so you can use "pre-warmed" slaves
and avoid the load and time of a full copy. So the procedure
would be something like:

1. Install Bucardo and add all tables you want replicated.
2. Perform your usual pg_dump and restore to the new box.
   (or use a snapshot, bring up a PITR slave, etc.)
3. Drop the bucardo schema on the new box.
   Run ANALYZE on the new box.
4. Tell Bucardo about the new box, then start it up.
   This will copy over all changes made since the pg_dump.
5. Stop your app, or put the DB in readonly mode.
6. Do a final Bucardo sync of any changes since step 4.
7. Copy over any other tables that do not have primary keys.
8. Point your app at the new box.

For speed, you can do things like turn off fsync and some
other settings on the new box until just before step 8.

And of course no matter which solution you choose, you'll
want to test this out first using a complete copy of the db
going from a 32-bit test box to a 64-bit test box. Heck, it
might even be a good time to jump to version 8.4 :)

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201003301345
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkuyOYIACgkQvJuQZxSWSsgPqwCg3DGv8O0klxkCdukecwuNyxir
TvAAni+5rbVbmwbAz3zsgahMuBHfuAUM
=ybeB
-----END PGP SIGNATURE-----

Hi Everybody,

      I'm not a dba. I'm a sysadmin by training. Is there some way to mirror the disks at the OS level? And then move
itto the new machine. Just a though, I don't know the exact steps. But if you are interested, I can see what I can
find.

-Tino

________________________________________
From: pgsql-admin-owner@postgresql.org [pgsql-admin-owner@postgresql.org] On Behalf Of Greg Smith
[greg@2ndquadrant.com]
Sent: Tuesday, March 30, 2010 1:05 PM
To: Dai, Tino; Renato Oliveira
Cc: pgsql-admin@postgresql.org; Tino Schwarze
Subject: Re: [ADMIN] Migrate postgres to newer hardware

Renato Oliveira wrote:
> Are there any commercial solutions out there for migrating large DBs?
>

I'm not aware of any.  The main way to address this problem by throwing
money at it is to hire someone extremely familiar with PostgreSQL
replication technology and figure out how to customize one of the
available approaches (Slony, Londiste, PITR, dump/restore) to match your
application.  For example, in some cases it's possible to record
database changes on the application side, replicate the database via one
of the fast online approaches like PITR, and then synchronize just the
changes made in the master while that was happening for a fast
switch-over to a new version.  It's not unheard for that to require
small application changes to support, to provide an easier way to log
the difference between the two.

If you can't take the additional load of Slony and have minimal
tolerance for downtime, you really need to come up with a long-term
approach to coping with that from an application architecture
perspective.  Unfortunately you're not going to find any quick fix for
that combination of requirements.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us


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

Tino,

I did try mirroring the disk using 'dd' command and it took me a long time.
At the end postgres did not start up.

I am sure I could probably get postgres working, but the db integrity not sure about that.

Thank you very much for the input and offer

Renato



Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.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: Dai, Tino [mailto:tdai@loc.gov]
Sent: 31 March 2010 03:08
To: Greg Smith; Renato Oliveira
Cc: pgsql-admin@postgresql.org; Tino Schwarze
Subject: RE: [ADMIN] Migrate postgres to newer hardware

Hi Everybody,

      I'm not a dba. I'm a sysadmin by training. Is there some way to mirror the disks at the OS level? And then move
itto the new machine. Just a though, I don't know the exact steps. But if you are interested, I can see what I can
find.

-Tino

________________________________________
From: pgsql-admin-owner@postgresql.org [pgsql-admin-owner@postgresql.org] On Behalf Of Greg Smith
[greg@2ndquadrant.com]
Sent: Tuesday, March 30, 2010 1:05 PM
To: Dai, Tino; Renato Oliveira
Cc: pgsql-admin@postgresql.org; Tino Schwarze
Subject: Re: [ADMIN] Migrate postgres to newer hardware

Renato Oliveira wrote:
> Are there any commercial solutions out there for migrating large DBs?
>

I'm not aware of any.  The main way to address this problem by throwing
money at it is to hire someone extremely familiar with PostgreSQL
replication technology and figure out how to customize one of the
available approaches (Slony, Londiste, PITR, dump/restore) to match your
application.  For example, in some cases it's possible to record
database changes on the application side, replicate the database via one
of the fast online approaches like PITR, and then synchronize just the
changes made in the master while that was happening for a fast
switch-over to a new version.  It's not unheard for that to require
small application changes to support, to provide an easier way to log
the difference between the two.

If you can't take the additional load of Slony and have minimal
tolerance for downtime, you really need to come up with a long-term
approach to coping with that from an application architecture
perspective.  Unfortunately you're not going to find any quick fix for
that combination of requirements.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.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
toanother person or take copies. 

VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system.
WhilstGrant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept
liabilityfor any damage which you sustain as a result of software viruses. You should therefore carry out your own
viruschecks 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

Greg,

Thank you very much for your input.
I agree with you and I do understand where you are coming from.

I do agree that in order to transition without a noticeable downtime the application would need to be built for that.

Which one works best: bucardo, slony or Londiste?

I have researched Slony and Bucardo but have not heard of  Londiste before.

How many people are using all three of them and their review  have you heard anything about that?

Thank you again

Really Appreciate it

Renato



Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.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: Greg Smith [mailto:greg@2ndquadrant.com]
Sent: 30 March 2010 18:06
To: Renato Oliveira
Cc: Tino Schwarze; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Migrate postgres to newer hardware

Renato Oliveira wrote:
> Are there any commercial solutions out there for migrating large DBs?
>

I'm not aware of any.  The main way to address this problem by throwing
money at it is to hire someone extremely familiar with PostgreSQL
replication technology and figure out how to customize one of the
available approaches (Slony, Londiste, PITR, dump/restore) to match your
application.  For example, in some cases it's possible to record
database changes on the application side, replicate the database via one
of the fast online approaches like PITR, and then synchronize just the
changes made in the master while that was happening for a fast
switch-over to a new version.  It's not unheard for that to require
small application changes to support, to provide an easier way to log
the difference between the two.

If you can't take the additional load of Slony and have minimal
tolerance for downtime, you really need to come up with a long-term
approach to coping with that from an application architecture
perspective.  Unfortunately you're not going to find any quick fix for
that combination of requirements.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us




-----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
toanother person or take copies. 

VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system.
WhilstGrant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept
liabilityfor any damage which you sustain as a result of software viruses. You should therefore carry out your own
viruschecks 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

On Wed, Mar 31, 2010 at 1:43 AM, Renato Oliveira
<renato.oliveira@grant.co.uk> wrote:
> Greg,
>
> Thank you very much for your input.
> I agree with you and I do understand where you are coming from.
>
> I do agree that in order to transition without a noticeable downtime the application would need to be built for that.
>
> Which one works best: bucardo, slony or Londiste?
>
> I have researched Slony and Bucardo but have not heard of  Londiste before.
>
> How many people are using all three of them and their review  have you heard anything about that?

I run slony.  On our regular db there are about 2000 relations, about
1200 of those are indexes, so slony has to worry about 800 or so
relations.  It has no problem with that.  On another machine that has
some 45k relations in addition to the 2000 base relations.  That slony
instance takes 3.5 hours to run the same create set that takes 2
minutes on the machine with just 2000 relations.

Slony should be able to work for you.  See if you can schedule it so
you start your subscription right when you're entering your lowest
throughput window.  Your real bottleneck here is that source database
with a single hard drive.  That's going to limit your speed of
subscription by quite a bit.



Greg,

I am going to gather the figures about our database and I will email to the list, if I am allowed to.
Number of tables, number of transactions per day etc.

Nothing on our db servers are optimized, hardware wise, the db is on the same volume as logs as the os.
I know we have an IO problem because I have been checking it and it has been growing steadily.
We must migrate to newer and better optimized hardware.

Thank you again

Much appreciated.

Renato



Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.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: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: 31 March 2010 09:11
To: Renato Oliveira
Cc: Greg Smith; Tino Schwarze; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Migrate postgres to newer hardware

On Wed, Mar 31, 2010 at 1:43 AM, Renato Oliveira
<renato.oliveira@grant.co.uk> wrote:
> Greg,
>
> Thank you very much for your input.
> I agree with you and I do understand where you are coming from.
>
> I do agree that in order to transition without a noticeable downtime the application would need to be built for that.
>
> Which one works best: bucardo, slony or Londiste?
>
> I have researched Slony and Bucardo but have not heard of  Londiste before.
>
> How many people are using all three of them and their review  have you heard anything about that?

I run slony.  On our regular db there are about 2000 relations, about
1200 of those are indexes, so slony has to worry about 800 or so
relations.  It has no problem with that.  On another machine that has
some 45k relations in addition to the 2000 base relations.  That slony
instance takes 3.5 hours to run the same create set that takes 2
minutes on the machine with just 2000 relations.

Slony should be able to work for you.  See if you can schedule it so
you start your subscription right when you're entering your lowest
throughput window.  Your real bottleneck here is that source database
with a single hard drive.  That's going to limit your speed of
subscription by quite a bit.



-----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
toanother person or take copies. 

VIRUSES: The contents of this e-mail or attachment(s) may contain viruses which could damage your own computer system.
WhilstGrant Instruments (Cambridge) Ltd has taken every reasonable precaution to minimise this risk, we cannot accept
liabilityfor any damage which you sustain as a result of software viruses. You should therefore carry out your own
viruschecks 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

On Tue, Mar 30, 2010 at 10:07:54PM -0400, Dai, Tino wrote:

>       I'm not a dba. I'm a sysadmin by training. Is there some way to
>       mirror the disks at the OS level? And then move it to the new
>       machine. Just a though, I don't know the exact steps. But if you
>       are interested, I can see what I can find.

It would work if both architectures were the same. But Renato is
migrating from 32 bit to 64 bit, so the on-disk format changes.

Tino, too. :-)

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.lichtkreis-chemnitz.de
www.tisc.de

On Wed, 2010-03-31 at 09:22 +0100, Renato Oliveira wrote:
> Greg,
>
> I am going to gather the figures about our database and I will email to the list, if I am allowed to.
> Number of tables, number of transactions per day etc.

Absolutely allowed, and encouraged.

Also, do you have long running transactions against the database?  Are
you committing large numbers of writes in a single transaction?

> Nothing on our db servers are optimized, hardware wise, the db is on the same volume as logs as the os.
> I know we have an IO problem because I have been checking it and it has been growing steadily.
> We must migrate to newer and better optimized hardware.

That will be your sticking point with Slony.  If your IO system is
already taxed, Slony will just add to the burden.

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.

Renato Oliveira wrote:
> I am going to gather the figures about our database and I will email to the list, if I am allowed to.
> Number of tables, number of transactions per day etc.
>

A quick snapshot from "vmstat 1" during a busy time is also very helpful.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us

I would never sing a SLA for HA if I don't have system, at least, fully redundant in order to deal with these issues.
Single server, single point of failure.

-----Original Message-----
From: Brad Nicholson <bnichols@ca.afilias.info>
To: Iñigo Martinez Lasala <imartinez@vectorsf.com>
Cc: Renato Oliveira <renato.oliveira@grant.co.uk>, pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] Migrate postgres to newer hardware
Date: Tue, 30 Mar 2010 10:38:02 -0400

On Tue, 2010-03-30 at 16:29 +0200, Iñigo Martinez Lasala wrote:
> Hi Renato.
> 
> I would follow the ancient method: perform a pg_dump / pg_restore

This is the easiest approach.  The problem is that a lot of people have
contractual SLA's that do not allow them to take their systems down long
enough to do a dump and restore upgrade.

I've never worked with Bucardo and Londiste, so I cannot give you any hint about it.
With slony it's not necessary to restore all database prior staring sync process. You only have to restore your schema but not data. Slony will keep them synced with source server.

Anyway, if you plan to migrate to 8.4 there are some issues with slony. For 8.3 and higher, you will go with slony 2.X. For 8.2 with slony 1.X
I'm not sure if Slony 1.x is compatible with Postgres >= 8.3, but I'm sure slony 2.x is not backwards compatible.

So, prior thinking about migrating to 8.4 you will have to check this. And more important, it's possible you have to slightly change your database schema to work in postgres 8.4, specially if you use tsearch2.

If using slony, I wouldn't upgrade to 8.4 before a deep analysis of changes in your existing database.

-----Original Message-----
From: Renato Oliveira <renato.oliveira@grant.co.uk>
To: Iñigo Martinez Lasala <imartinez@vectorsf.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: RE: [ADMIN] Migrate postgres to newer hardware
Date: Wed, 31 Mar 2010 09:11:37 +0100

 Hi Iñigo,

 

Thank you for your input, really appreciated.

 

I just had a thought; if I backup ‘pg_dump’ full database, then restore to my new machine new postgres 8.4, which one of these programs would work best to do the migration,

Slony, Bucardo or Londiste?

 

I would like to say that we did have slony and I was not impressed, it fell behind and could not catch up and caused a very high load on the system.

Also they way its philosophy works, it is very high maintenance, and the idea of creating tables, and triggers on both dbs...

It might be, it was not setup properly or well, we removed it as the db was screaming for some fresh air.

 

Ps I would like to point out that I am systems administrator and not a dba, so you can understand sometimes my questions...

 

I think Bucardo seems best for the task, for what I have read so far,  but I do not know.

 

Thank you very much and I am sorry for this

 

Renato

 

 

 


  Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk   Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
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  
  From: Iñigo Martinez Lasala [mailto:imartinez@vectorsf.com]
Sent: 30 March 2010 16:27
To: Renato Oliveira
Cc: pgsql-admin
Subject: RE: [ADMIN] Migrate postgres to newer hardware


 

Yes, you only have that two possibilities, I think.

PITR is not an option. I tested the same, from 7.4 32bit to 7.4 64bit and didn't work. Later, when I asked here, I was told why not.

The problem with slony is that you have to manually create tables in destination database and all database model (procedures, triggers, sequences, views, etc). If your application creates new tables, you will have to deal with this prior starting migration, or at least disable the creation of new tables.

Slony is asynchronous, so you will have to ensure that all changes have been committed to new database before changing your applications or exchanging IP addresses.
Slony also add many triggers and special tables to both databases (master and slave). So, after migration, you will have to delete them. It's not difficult but don't forget to do it.

By the way, are you sure your database is 160GB? Including indexes? There are strategies in order to perform a faster pg_restore...
For example, if you migrate your database schema but don't create indexes, then migrate data and finally create pending indexes restore will be faster. With pg 8.4 restore is very fast, so it will take less time that export.

Anyway, if you cannot leave database down for a day, I think slony will be your best bet, although it's not exempt of problems. :)

-----Original Message-----
From: Renato Oliveira <renato.oliveira@grant.co.uk>
To: Iñigo Martinez Lasala <imartinez@vectorsf.com>
Subject: RE: [ADMIN] Migrate postgres to newer hardware
Date: Tue, 30 Mar 2010 15:47:27 +0100

 Hey Iñigo,

 

Thank you very much for your reply.

 

I would love to do just that, but unfortunately I can’t it is not as simple as that.

 

I would love if the application had been built in with this in mind…

 

To give you an idea; the pg_dump takes 15 hours and I attempted a restore yesterday and it took 14 hours and 21 min.

It would not be viable for us and specially I cannot have the system down more than maximum 30 min without the risk of losing data and customers not having alerts.

 

I don’t think I will be able to use PITR to migrate to new servers specially if it is 64 bit and to migrate to another 32 bit is no gain, as we need more memory.

 

As far as can gather there are only two ways:

a)   Slony type

b)   Pg_dump

 

Is that correct ? Do you guys have any other ways?

 

Renato

 

 


  Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk   Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
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  
  From: Iñigo Martinez Lasala [mailto:imartinez@vectorsf.com]
Sent:30 March 2010 15:29
To:Renato Oliveira
Cc:pgsql-admin
Subject: Re: [ADMIN] Migrate postgres to newer hardware


 

Hi Renato.

I would follow the ancient method: perform a pg_dump / pg_restore

Yes, you will have to take offline database for a long period.

And yes, it would be a great moment to perform a 8.4 upgrade. Performance is far superior, restore is far faster...
... and yes, it could give you many problems if you don't perform many test in order to address all queries without explicit type conversions before real migration, but I think it's the best moment to deal with a very convenient upgrade. 

We have performed this upgrade last week with a gforge (with only 25GB database) and having also to upgrade to new tsearch2 and everything is running smooth.

-----Original Message-----
From: Renato Oliveira <renato.oliveira@grant.co.uk>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: [ADMIN] Migrate postgres to newer hardware
Date: Tue, 30 Mar 2010 12:18:36 +0100

Dear All,

 

What would be the easiest and fastest way to migrate Postgres 8.2.24 32 BIT to a new server 64 Bit.

 

The existing server runs on 32 bit architecture and has a database as big as 160GB.

 

We initially thought of using PITR, but as one of the PITR requirements is both machines need to be similar.

This similarity needs to be even in architecture? I think I read something which says “Yes”.

 

If we cannot use PITR what would be the best approach, we can’t have down time I am afraid.

 

Any ideas or suggestions would be very welcome.

 

Thank you very much

 

Best regards

 

Renato

 

 


  Renato Oliveira
Systems Administrator
e-mail: renato.oliveira@grant.co.uk   Tel: +44 (0)1763 260811
Fax: +44 (0)1763 262410
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  
 
 
   

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 website


 
   

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 website


 
   

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 website

On Fri, Apr 2, 2010 at 1:58 AM, Iñigo Martinez Lasala
<imartinez@vectorsf.com> wrote:
> I've never worked with Bucardo and Londiste, so I cannot give you any hint
> about it.
> With slony it's not necessary to restore all database prior staring sync
> process. You only have to restore your schema but not data. Slony will keep
> them synced with source server.
>
> Anyway, if you plan to migrate to 8.4 there are some issues with slony. For
> 8.3 and higher, you will go with slony 2.X. For 8.2 with slony 1.X
> I'm not sure if Slony 1.x is compatible with Postgres >= 8.3, but I'm sure
> slony 2.x is not backwards compatible.

slony 1.2.20 is definitely compatible with pg 8.4 and 8.5/9.0
according to the release notes.  Not sure if 2.x is considered
production ready.  It certainly wasn't last fall.

On Fri, 2010-04-02 at 09:58 +0200, Iñigo Martinez Lasala wrote:
> I've never worked with Bucardo and Londiste, so I cannot give you any
> hint about it.
> With slony it's not necessary to restore all database prior staring
> sync process. You only have to restore your schema but not data. Slony
> will keep them synced with source server.
>
> Anyway, if you plan to migrate to 8.4 there are some issues with
> slony. For 8.3 and higher, you will go with slony 2.X. For 8.2 with
> slony 1.X
> I'm not sure if Slony 1.x is compatible with Postgres >= 8.3, but I'm
> sure slony 2.x is not backwards compatible.

Slony 1.2.17 and higher is compatible with PG 8.4.  I'd go with the
latest stable release though.

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.