Managing system migrations_PostgreSQL 9 High Availability Cookbook-QQ阅读中文历史网

上QQ阅读APP看书，第一时间看更新

Managing system migrations

As DBAs, it is likely that we will eventually preside over a server replacement. Whether this is to avoid failed hardware or due to system upgrades, our job is to move PostgreSQL from one system to the next.

It is not simple to perform a server migration while simultaneously maintaining maximum availability. One of the easiest methods is limited to users of shared storage such as a SAN. Such storage can be reassigned to another server easily. Without a SAN or other means of shared storage, we need to utilize another method.

Luckily, PostgreSQL added streaming database replication in Version 9.1. With this, we can make a copy on the new server and switch to it when we're ready.

Getting ready

For this demonstration, we will need another server or virtual machine to receive a copy of our database. Have one ready to follow along. We will also be using a PostgreSQL tool named pg_basebackup. Check the PostgreSQL documentation regarding this utility for more information.

If the donor server is configured as described in the Configuration – getting it right the first time recipe, modify its pg_hba.conf file and add the following line:

host    replication    rep_user    0/0    md5

Then, create a user to control replication with this SQL query issued as a superuser:

CREATE USER rep_user WITH PASSWORD 'rep_test' REPLICATION;

Then, reload the server to activate the configuration line. If you are attempting this in a real production system, use a better password and replace 0/0 with the actual IP address of the new server.

How to do it...

Assuming 192.168.1.10 is our donor server, follow these steps to create a copy:

Connect to the new server as the postgres user.

Issue the following command to copy data from the donor system:

pg_basebackup -U rep_user -h 192.168.1.10 -D /path/to/database

Create a file named recovery.conf in /path/to/database with the following contents:

standby_mode = 'on'
primary_conninfo = 'host=192.168.1.10 port=5432 user=rep_user'

Create a file named .pgpass in the home directory of the postgres user with the following line:
```
*:5432:replication:rep_user:rep_test
```
Set the correct permissions for the .pgpass file with this command:
```
chmod 0600 ~postgres/.pgpass
```
Start the new server using the following command:
```
pg_ctl -D /path/to/database start
```
Inform application owners to stop their applications or bring available services up with a maintenance message.
Issue the following command on the donor server to write any pending data to the database:
```
CHECKPOINT;
```
Connect to PostgreSQL on the donor server and issue the following query to check replication status:
```
SELECT sent_location, replay_location
  FROM pg_stat_replication
 WHERE usename = 'rep_user';
```
Periodically, repeat the preceding query until sent_location and replay_location match.
Issue a command on the primary server to stop the database. This command should work on most systems:
```
pg_ctl -D /path/to/database stop -m fast
```
Issue this command on the new server:
```
pg_ctl -D /path/to/database promote
```
Inform application owners to start their applications or bring available services up normally configured to use the new database server address.

How it works...

We start the somewhat long journey on the new server by invoking the pg_basebackup command. When PostgreSQL introduced streaming replication, they also made it possible for a regular utility to obtain copies of database files through the client protocol. To create a copy of every file in the donor system, we specify its address with the -h parameter. Using the -U parameter, we can tell pg_basebackup to use the rep_user user we created specifically to manage database replication.

When PostgreSQL detects the presence of a recovery.conf file, it begins to recover as if it crashed. The value we used for the primary_conninfo setting will cause the replica to connect to the primary server. Once established, the replica will consume changes from the primary database server until it is synchronized. After starting the database, any activity that occurs in the primary system will also eventually be replayed in the copy.

As we created the replication user with a password, we need an automatic method to convey the password from the replica to the primary. PostgreSQL clients often seek .pgpass files to obtain credentials automatically; used in this context, the new server acts as a client.

Once we start the new server, everything should be ready, so we need all sources of new data in the database to stop temporarily. Once this has happened, we issue CHECKPOINT to flush the activity to disk. Afterward, we monitor the status of the replication stream until it is fully synchronized with the donor.

After the synchronization is verified with our replication lag query, we stop the source PostgreSQL database; its job is complete. All that remains is to promote the new database to full production status and tell various departments and application owners that the database is available at the new location. Before replication, this was a much more involved process.

There's more...

We can use what we learned in the Exploring the magic of virtual IPs recipe to make this even simpler for end users. Until near the end, the process is the same. However, if applications and users were using the virtual address instead of the actual server IP for the old database, they can continue to use the virtual location after the migration.

Simply detach the virtual IP from the old database server, and attach it on the new one before informing the users that the migration is complete. As an added benefit, we can use the virtual IP address as a form of security. Until we create it, users will be unable to locate the database. We can take advantage of this and perform database checks before going fully online.

Once we have created the virtual IP address, any applications that were using the database before we started the migration will need to reconnect. Yet, even this necessity can be removed; we will discuss this in a future chapter.