PostgreSQL Replication

Refer to  http://dbversity.com/postgresql-installation/ for PostgreSQL Installation procedure on your both Master & Slave servers.

Replication

Now we are testing something that called hot standby replication.

Configure the Master Server

We will begin by configuring our master server. All of these commands should be executed with the postgres user.

First, we will create a user called “rep” that can be used solely for replication:

psql -c “CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD ‘yourpassword‘;”

 

Change the password to whatever you’d like to use.

Next, we will move to the postgres configuration directory:

 cd /etc/postgresql/9.1/main

 

We will modify the access file with the user we just created:

nano pg_hba.conf

 

At any place not at the bottom of the file, add a line to let the new user get access to this server:

host    replication     rep     IP_address_of_slave/32   md5

 

Save and close the file.

Next, we will open the main postgres configuration file:

nano postgresql.conf

 

Find these parameters. Uncomment them if they are commented, and modify the values according to what we have listed below:

listen_addresses = ‘localhost,IP_address_of_THIS_host‘wal_level = ‘hot_standby’archive_mode = onarchive_command = ‘cd .’max_wal_senders = 1hot_standby = on

 

Save and close the file.

 

Restart the master server to implement your changes:

service postgresql restart

 

Configure the Slave Server

 

Begin on the slave server by shutting down the postgres database software:

service postgresql stop

We will be making some similar configuration changes to postgres files, so change to the configuration directory:

cd /etc/postgresql/9.1/main

Adjust the access file to allow the other server to connect to this. This is in case we need to turn the slave into the master later on down the road.

nano pg_hba.conf

Again, add this line somewhere not at the end of the file:

host    replication     rep     IP_address_of_master/32  md5

Save and close the file.

Next, open the postgres configuration file:

nano postgresql.conf

You can use the same configuration options you set for the master server, modifying only the IP address to reflect the slave server’s address:

listen_addresses = ‘localhost,IP_address_of_THIS_host‘wal_level = ‘hot_standby’archive_mode = onarchive_command = ‘cd .’max_wal_senders = 1hot_standby = on

Save and close the file.

 

Replicating the Initial database:

 

Before the slave can replicate the master, we need to give it the initial database to build off of. This is because it reads the logs off of the master server and applies the changes to its own database. We need that database to match the master database.

On the master server, we can use an internal postgres backup start command to create a backup label command. We then will transfer the database data to our slave and then issue an internal backup stop command to clean up:

psql -c “select pg_start_backup(‘initial_backup’);”rsync -cva –inplace –exclude=*pg_xlog* /var/lib/postgresql/9.1/main/ slave_IP_address:/var/lib/postgresql/9.1/main/psql -c “select pg_stop_backup();”

The rsync command may have had an error on modifying the certificate files, but this is okay for our use. The master’s data should now be on the slave.

We now have to configure a recovery file on our slave. On the slave navigate to the data directory:

cd /var/lib/postgresql/9.1/main

Here, we need to create a recovery file called recovery.conf:

nano recovery.conf

Fill in the following information. Make sure to change the IP address of your master server and the password for the rep user you created:

 

standby_mode = ‘on’primary_conninfo = ‘host=master_IP_address port=5432 user=rep password=yourpassword‘trigger_file = ‘/tmp/postgresql.trigger.5432’

The last line in the file, trigger_file, is one of the most interesting parts of the entire configuration. If you create a file at that location on your slave machine, your slave will reconfigure itself to act as a master.

This will break your current replication, especially if the master server is still running, but is what you would need to do if your master server goes down. This will allow the slave to begin accepting writes. You can then fix the master server and turn that into the slave.

You should now have the pieces in place to start your slave server. Type:

service postgresql start

You’ll want to check the logs to see if there are any problems. They are located on both machines here:

less /var/log/postgresql/postgresql-9.1-main.log

You should see that it is successfully connecting to the master server.

 

Test the Replication

We will see first-hand if our servers are replicating correctly by making some changes on the master server and then querying the slave.

On the master server, as the postgres user, log into the postgres system by typing:

psql

Your prompt will change to indicate that you are now communicating with the database software.

We will create a test table to create some changes:

CREATE TABLE rep_test (test varchar(40));

Now, we can insert some values into the table with the following commands:

INSERT INTO rep_test VALUES (‘data one’);INSERT INTO rep_test VALUES (‘some more words’);INSERT INTO rep_test VALUES (‘lalala’);INSERT INTO rep_test VALUES (‘hello there’);INSERT INTO rep_test VALUES (‘blahblah’);

You can now exit out of this interface by typing:

\q

 

 

Now, on the slave, enter the database interface in the same way:

psql

Now, we can see if the data we entered in the master database has been replicated on the slave:

SELECT * FROM rep_test;      test       —————– data one some more words lalala hello there blahblah(5 rows)

 

Excellent! Our data has been written to both the master and slave servers.

Let’s see if we can insert more data into the table on our slave:

INSERT INTO rep_test VALUES (‘oops’);ERROR:  cannot execute INSERT in a read-only transaction

As you can see, we are unable to insert data into the slave. This is because the data is only being transferred in one direction. In order to keep the databases consistent, postgres must make the slave read-only.

 

 Conclusion

You should now have a master and slave PostgreSQL server configured to communicate effectively. If you have an application that will be writing to and querying the databases, you could set up a load balancing scheme to always write to the master, but split the reads between the master and slave.

This could increase the performance of your database interactions.

 

Ask Question