Re: PG migration policy - Mailing list pgsql-general

From Allan Kamau
Subject Re: PG migration policy
Date
Msg-id CAF3N6oR9dEm0J5zq6HWthN=EzHZLTr81TGom86u9m2C_z2EUYw@mail.gmail.com
Whole thread Raw
In response to Re: PG migration policy  (Tomas Vondra <tv@fuzzy.cz>)
List pgsql-general
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
>

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Question about (probably wrong) index scan cost for conditional indexes
Next
From: Jasen Betts
Date:
Subject: Re: Best way to create unique primary keys across schemas?