Upgrade Old PostgreSQL Database Via Logical Replication With Bucardo

Photo by Wesley Tingey on Unsplash

Bucardo is an open-source replication tool that can be used to upgrade from PostgreSQL 9.6 to PostgreSQL 15 while minimizing downtime. In this how-to article, we will walk through the steps required to perform this upgrade using Bucardo backup. We will assume that you are installing Bucardo from Git.

Note: Before proceeding, it is important to thoroughly test the upgrade process in a non-production environment to ensure that it works correctly and to minimize the risk of data loss.

Step 1: Install Git If you have not already done so, install Git on your server using your operating system’s package manager or by downloading it from the official website.

Step 2: Clone the Bucardo repository Once Git is installed, clone the Bucardo repository to your server using the following command:

$ git clone git://github.com/bucardo/bucardo.git

This will create a new directory called “bucardo” in your current directory.

Step 3: Install Bucardo After cloning the Bucardo repository, install Bucardo by running the following commands:

$ cd bucardo
$ perl Makefile.PL
$ make
$ make test
$ sudo make install

These commands will configure, compile, and install Bucardo on your server.

Step 4: Create a Bucardo database After installing Bucardo, create a new database to be used by Bucardo to store its configuration information. You can create this database on either the PostgreSQL 9.6 or PostgreSQL 15 server. For example, using the psql command-line tool:

$ psql -c "CREATE DATABASE bucardo;"

Step 5: Create a Bucardo sync Once the Bucardo database is set up, create a new sync to replicate data from the PostgreSQL 9.6 server to the PostgreSQL 15 server. In this example, we assume that the database to be upgraded is named “mydatabase”.

$ bucardo add sync mydatabase
$ bucardo add table mydatabase.mytable
$ bucardo add db source db=mydatabase host=9.6.server user=myuser pass=mypassword
$ bucardo add db target db=mydatabase host=15.server user=myuser pass=mypassword
$ bucardo add all tables to sync mydatabase

This creates a new sync called “mydatabase” and adds the “mytable” table to the sync. It also specifies the source and target databases and adds all tables to the sync.

Step 6: Initial sync After creating the sync, use Bucardo to perform an initial sync from the PostgreSQL 9.6 server to the PostgreSQL 15 server. This will ensure that the two databases are in sync before the upgrade process begins.

$ bucardo sync mydatabase onetimecopy=2

This command performs a one-time copy of the data from the source to the target database. The “onetimecopy=2” option ensures that Bucardo does not make any changes to the source database during the initial sync.

Step 7: Upgrade PostgreSQL After the initial sync is complete, upgrade the PostgreSQL server on the PostgreSQL 15 server to version 15. This can be done using your operating system’s package manager or by compiling from source.

Step 8: Final sync Once the PostgreSQL 15 server has been upgraded, perform a final sync to copy any changes made to the PostgreSQL 9.6 server during the upgrade process to the PostgreSQL 15 server.

$ bucardo sync mydatabase

This command performs a regular sync of the data from the source to the target database

I Love Coffee! https://ko-fi.com/canutethegreat