Обсуждение: PG migration policy
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.
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. >
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>
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
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.
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.
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 >