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.

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

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.

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.

System migrations are extremely complicated. This section only touches on a small number of concepts. Please refer to these PostgreSQL documentation links for a deeper exploration of the material we covered: