Londiste provides a very capable control mechanism in londiste3. Unlike Bucardo, we don't need to create a herd or sync, nor do we have to launch the process that handles data for a particular herd. With Londiste, it's all about the tables.
In this recipe, we will utilize londiste3 to register all of the tables we want to copy and verify that the data is the same on each PostgreSQL server.
We will be continuing where we left off in the Setting up Londiste recipe. Please make sure that you have completed that recipe before continuing. Once again, we will use the pgbench utility to create an initial set of tables. Execute this command on the primary PostgreSQL server as the postgres user if you haven't already done so:
pgbench -i postgres
Execute all commands in this recipe as the postgres system user. Follow these steps to copy the sample pgbench tables:
- Extract the table creation statements from the primary node with the following command:
pg_dump -s -t 'pgbench*' postgres > /tmp/tables.sql - Create the empty tables on the subscriber node by executing this command on the primary node:
psql -U rep_user -h 192.168.56.30 -f /tmp/tables.sql postgres - Make sure that you are in the
/etc/skytoolsdirectory for the following steps. - Register all of the
pgbenchtables with the primary PostgreSQL server with these commands:londiste3 primary.ini add-table pgbench_accounts londiste3 primary.ini add-table pgbench_branches londiste3 primary.ini add-table pgbench_tellers
- Register all of the
pgbenchtables with the subscriber PostgreSQL server with these commands:londiste3 subscriber.ini add-table pgbench_accounts londiste3 subscriber.ini add-table pgbench_branches londiste3 subscriber.ini add-table pgbench_tellers
- Compare data on both nodes with this command:
londiste3 subscriber.ini compare
Once again, we need to begin by duplicating table structures to the subscriber. Londiste only copies data and assumes that the source and target tables have the exact same columns. Therefore, we use pg_dump to obtain a schema-only (-s) extract of any table that begins with pgbench (-t 'pgbench*'). Using the -h parameter, we can execute the resulting SQL on the subscriber database and create all of the pgbench tables as empty shells.
Next, we need to be in the /etc/skytools directory. This isn't strictly required, but as the configuration file is always the first parameter to londiste3, we would need to type the full path to each file every time.
To register each table with the primary server, we specify its configuration file, the add-table parameter, and the table we want to register. As with Slony and Bucardo, we need to add the three pgbench tables with primary keys. We repeat this process for the subscriber, using its configuration file instead.
Once we have done this, Londiste will begin by checking the table contents on each server and copying any data that is missing on the subscriber. All future modifications will also be copied to the subscriber.
An interesting function that londiste3 provides is the ability to confirm that data is synchronized by performing checksum comparisons. If we wait a moment for the data to synchronize and execute londiste3 with the compare parameter, we should see these lines for each table:
- The
londiste3utility is very versatile. We highly recommend that you use the http://skytools.projects.pgfoundry.org/skytools-3.0/doc/londiste3.html URL to learn more about its capabilities.