Thread: PG migration policy

PG migration policy

From
Allan Kamau
Date:
Hi,
This is definitely off topic, I apologize.
We are planning to move our PostgreSQL installation from a shared
server to a dedicated server. I have been given the responsibility of
writing a migration policy document for this operation. This would be
my first such document to put together,
I am looking for pointers, samples and so on on which to build this
document for our scenario.

Allan.

Re: PG migration policy

From
Sim Zacks
Date:
You are moving to a dedicated server for a reason, most probably
performance.
The intro section of the document should discuss the reasons you are
moving and what you hope to accomplish.

Hardware - The discuss the new platform that you are moving to, in terms
of how it will perform better. Important aspects are memory, disk speed,
RAID set up, cpu (not so much).

Server Configuration
Include:
* Kernel compile options
* params such as shmmax
* file system chosen

Postgresql Configuration:
List each option you are going to change and values you are planing on using

Backup strategy
How often backups will be taken, where they go, if there is an offsite
plan, how often restores are tested.

HA strategy
What are you plans if the server dies.

You should also have a test server where you can test changes without
affecting production. It should have the same configuration as the primary.



On 01/29/2012 08:30 AM, Allan Kamau wrote:
> Hi,
> This is definitely off topic, I apologize.
> We are planning to move our PostgreSQL installation from a shared
> server to a dedicated server. I have been given the responsibility of
> writing a migration policy document for this operation. This would be
> my first such document to put together,
> I am looking for pointers, samples and so on on which to build this
> document for our scenario.
>
> Allan.
>


Re: PG migration policy

From
Bill Moran
Date:
In addition to all this, you will need to outline an actual plan for
the migration itself:

1) Steps to migrate
2) Testing strategy to ensure the migration actually worked
3) Estimated time
3a) How much of that time is actual downtime
4) Fallback plan in case the migration fails

For such documents, we also include a checklist for everything that needs
to be done so that it can be signed off at each step of the process.

I didn't see "monitoring" on Sim's list, but in addition to the stuff you
monitor for any server, you'll need to monitor DB-specific stuff like
table bloat, slow queries, etc.

On Sun, 29 Jan 2012 10:14:21 +0200 Sim Zacks <sim@compulab.co.il> wrote:

> You are moving to a dedicated server for a reason, most probably
> performance.
> The intro section of the document should discuss the reasons you are
> moving and what you hope to accomplish.
>
> Hardware - The discuss the new platform that you are moving to, in terms
> of how it will perform better. Important aspects are memory, disk speed,
> RAID set up, cpu (not so much).
>
> Server Configuration
> Include:
> * Kernel compile options
> * params such as shmmax
> * file system chosen
>
> Postgresql Configuration:
> List each option you are going to change and values you are planing on using
>
> Backup strategy
> How often backups will be taken, where they go, if there is an offsite
> plan, how often restores are tested.
>
> HA strategy
> What are you plans if the server dies.
>
> You should also have a test server where you can test changes without
> affecting production. It should have the same configuration as the primary.
>
>
>
> On 01/29/2012 08:30 AM, Allan Kamau wrote:
> > Hi,
> > This is definitely off topic, I apologize.
> > We are planning to move our PostgreSQL installation from a shared
> > server to a dedicated server. I have been given the responsibility of
> > writing a migration policy document for this operation. This would be
> > my first such document to put together,
> > I am looking for pointers, samples and so on on which to build this
> > document for our scenario.
> >
> > Allan.
> >
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


--
Bill Moran <wmoran@potentialtech.com>

Re: PG migration policy

From
Andy Colson
Date:
On 01/29/2012 12:30 AM, Allan Kamau wrote:
> Hi,
> This is definitely off topic, I apologize.
> We are planning to move our PostgreSQL installation from a shared
> server to a dedicated server. I have been given the responsibility of
> writing a migration policy document for this operation. This would be
> my first such document to put together,
> I am looking for pointers, samples and so on on which to build this
> document for our scenario.
>
> Allan.
>

When our office moved location, and thus ISP's, we layed out a simple plan to keep our websites up at all time.  Our
plan,100% uptime while loosing no client updates. 

We layed out a time line, and on it what would happen and when.  We tried to come up with problems and solutions, etc.

Having a fallback wasn't really an option, because we were moving, its not like we could just give up and stay.  But we
hadsafety nets and backup options (including run a copy of the webserver VM from my house). 

DNS update was our biggest stumbling block, so we had to run two web servers concurrently, so that some people could
hitthe new and some could hit the old.  And updates would find their way to the right spots. 

-Andy

Re: PG migration policy

From
Scott Marlowe
Date:
On Sat, Jan 28, 2012 at 11:30 PM, Allan Kamau <kamauallan@gmail.com> wrote:
> Hi,
> This is definitely off topic, I apologize.
> We are planning to move our PostgreSQL installation from a shared
> server to a dedicated server. I have been given the responsibility of
> writing a migration policy document for this operation. This would be
> my first such document to put together,
> I am looking for pointers, samples and so on on which to build this
> document for our scenario.

Assuming you've tested ahead of time, the real question is how much
down time can you afford?

If you can afford hours or more of downtime, a dump and restore if the
easiest way.

If you need to keep your down time to a minimum, then look at using
some form of replication and switching from master to slave once the
slave is caught up.

Re: PG migration policy

From
Tomas Vondra
Date:
On 29.1.2012 07:30, Allan Kamau wrote:
> Hi,
> This is definitely off topic, I apologize.
> We are planning to move our PostgreSQL installation from a shared
> server to a dedicated server. I have been given the responsibility of
> writing a migration policy document for this operation. This would be
> my first such document to put together,
> I am looking for pointers, samples and so on on which to build this
> document for our scenario.

Hi,

what exactly do you expect such document to cover? Should it discuss
ways to migrate the database, or have you already decided how to perform
the migration and it should describe each step?

What do you mean by shared and dedicated server? Does that mean the
server is shared by multiple applications, one of them being PostgreSQL
cluster and you want to move the whole cluster to a dedicated database
server? Or does that mean that you want to move just one of the
databases (not the whole cluster)?

Tomas

PS: I don't think this to be off topic. General list is exactly the
right place where this should be discussed.

Re: PG migration policy

From
Allan Kamau
Date:
Thank you Sim, Bill, Andy, Scott and Tomas for all your suggestions.
Indeed I have now learnt formulate such a document.

The database we plan to move is used for research and it resides along
side several other applications on the same hardware. The idea is to
move the entire database cluster to another computer and move the
other applications on the same computer to virtual machines in other
computers. We shall then de-commission the current server and provide
it for other projects.

We can allow overnight downtimes or more if needed. All our users are
in-house scientists.


In my document I will now include (taken from these e-mails)

1)Introduction detailing the migration objectives.
1b)List of client applications using the current PostgreSQL setup.
2)Hardware of the new server,
3)Operating system of the new hardware.
4)Server resources parameter such as shmmax
5)Disk allocation and layout for PostgreSQL and including disk type
and file system type.
6)PostgreSQL configuration details, (postgresql.conf and pg_hba.conf)
7)Backup strategy
8)Sequence of steps (including commands) for the postgreSQL cluster migration.
8b)Post restore steps such as tablespace creation.
9)Timelines and projected downtime.
10)Procedures and commands to test the success of the PostgreSQL migration.
11)Changes to IP and server name and DNS entries.
12)Steps to switch connection configuration details on the client
applications to point to the new installation.
13)De-comissioning procedures.


As suggest by Scott, a "dump_all" to dump the cluster to file followed
by a "psql" to restore the cluster along with redirecting the STDOUT
and STDERR to a common file during the restore is the way to go. I
could then grep this file for errors as a first line test for success
of the cluster migration.


Regards,
Allan.

On 1/29/12, Tomas Vondra <tv@fuzzy.cz> wrote:
> On 29.1.2012 07:30, Allan Kamau wrote:
>> Hi,
>> This is definitely off topic, I apologize.
>> We are planning to move our PostgreSQL installation from a shared
>> server to a dedicated server. I have been given the responsibility of
>> writing a migration policy document for this operation. This would be
>> my first such document to put together,
>> I am looking for pointers, samples and so on on which to build this
>> document for our scenario.
>
> Hi,
>
> what exactly do you expect such document to cover? Should it discuss
> ways to migrate the database, or have you already decided how to perform
> the migration and it should describe each step?
>
> What do you mean by shared and dedicated server? Does that mean the
> server is shared by multiple applications, one of them being PostgreSQL
> cluster and you want to move the whole cluster to a dedicated database
> server? Or does that mean that you want to move just one of the
> databases (not the whole cluster)?
>
> Tomas
>
> PS: I don't think this to be off topic. General list is exactly the
> right place where this should be discussed.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>