This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
Step 1: Dumping the database
Go to the master machine that stores the database. Use the pg_dump command to dump the database to a file. For more information, refer to the pg_dump command in the reference section. This command dumps our booktown database into a file called booktown.db. You can give the extension name any valid set of characters. Make sure that the name will help you distinguish the file as a dumped database file.
pg_dump booktown > booktown.db
Step 2: Copy the dump to the slave machine
Use scp to copy the dump from the master machine to the slave machine. The command I used was:
scp booktown.db pma@pma:
You need to move the dump into the path /usr/local/pgsql/ on the slave machine. It may require you to log in as the root user. This command can be used in the directory that holds the dumped file:
mv booktown.db /usr/local/pgsql/
Step 3: Create the appropriate users
The dump command does not automatically create any users when it is undumped. Therefore, we need to create these users before undumping the database.
If you haven't done so, start the PostgreSQL service. Switch to the root user and start the database using this command:
service postgresql start
![]() | PostgreSQL: unrecognized service |
---|---|
You may receive this error message if you did not set up the sysV start up script for PostgreSQL. You can instead use the command below to start PostgreSQL in the background: nohup /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data </dev/null >>server.log 2>&1 >/dev/null & If you want to configure your system to use the command: service PostgreSQL start , then refer to Step 9 in the PostgreSQL Installation section. |
Once the PostgreSQL database has started, you can now create the database users. You should be logged in as the postgres superuser or another superuser that was set up during installation. The command to create the booktown users are:
createuser mark createuser jennifer createuser jessica createuser william createuser jonathan
When asked if the users are allowed to create databases and new users, you should specify no for both options. The only exception is user Mark. He is the manager of the entire booktown system and should be able to create new users and databases.
Step 4: Create the database
Now you can create the database that will store the users and the tables from the dump. You should still be logged in as the postgres user. Use the command below:
createdb booktown
Step 5: Import the database
psql booktown < booktown.db
![]() | \connect:Fatal 1: user "pma" does not exist |
---|---|
This message appears if you forgot to create a user that owns an object which existed in the dumped database. The object could be a table, sequence, function, ... etc. To fix this problem, you can follow the steps below:
|
You should receive a list of messages which says that you are now connected as the superusers.
Step 6: Edit the pg_hba.conf file
You need to make sure the slave machine will allow connections from the master machine. Editing the pg_hba.conf file allows these connections to occur. This file is located in the $PGDATA directory at /usr/local/pgsql/data/pg_hba.conf. You also need to add the IP address of the master machine into this file. All this requires is a line which tells the slave machine the type of authentication to use. For more information, refer to the Password Authentication sect1.
We can specify the slave machine to accept packets from the master machine with the IP address 192.168.1.65 after the encrypted password has been verified. The connection will only have access to the booktown database. The record added to the pg_hba.conf file looks like this:
host booktown 192.168.1.65 255.255.255.255 crypt
Step 7: Initialize the database
Use the MasterInit file to initialize the database that will act as the master database. You can log onto the master machine and perform the following command:
./MasterInit booktown
Step 8: Using MasterAddTable
The commands executed for this step should be performed on the master machine. The MasterAddTable tool allows you to select the table you want to replicate from a database on the master machine.
Choose a column that you wish to be the trigger on the table for replication. Then use the syntax below to specify the column you want to replicate:
./MasterAddTable dbname table column
For instance, this replicates the lastname column in the customer table:
./MasterAddTable booktown customer lastname
![]() | Replication only occurs when the column specified by the above command is modified. |
You can replicate multiple columns in a table by running MasterAddTable multiple times.
![]() | ERROR: ProcedureCreate: procedure _rserv_log_ already exists with same arguments |
---|---|
You may receive this error when executing the MasterAddTable command several times on the same column in the same table and database name. You can log into the database and drop the following items:
You can now re-execute your MasterInit command. You also need to execute the MasterAddTable command again. |
You should receive the message below if the MasterInit command was successful:
NOTICE: CREATE TABLE/UNIQUE will create implicit index '_rserv_servers__server_key' for table '_rserv_servers_'
Step 9: Initializing the Slave Database
You will need to log onto the slave machine and run the following command to initialize the booktown database:
./SlaveInit booktown
Step 10: Selecting the table to Replicate
Use the SlaveAddTable to select the table you want to replicate on the master machine. The syntax is the same as MasterAddTable. We will replicate the firstname column in the customer table:
./SlaveAddTable booktown customer lastname
Step 11: Testing the configuration
After the first 10 steps are completed, you can check if you configured the system correctly by performing the following tests:
Log into the master database and update the column you referenced using MasterAddTable. We changed Dr.John's last name from Nathan to Nathaniel:
UPDATE customer SET lastname = 'Nathaniel' WHERE cust_id = 10;
From the command line, use the Replicate command. The syntax for the Replicate command is:
./Replicate masterdb slavedb
The command below was used to replicate the booktown database:
./Replicate --user=pma --host=local --masterhost=localhost --slavehost=pma booktown booktown
You will either receive an error message, or another message which showed that it worked.
Step 12: To verify that it works, log into the slave database and perform a query that searches for your modification.
Step 13: Why the replication does not run automatically
 
Continue to: