Log shipping or streaming replication in PostgreSQL 9.x is near real-time, so it takes less than a second for a change made in the master database to propagate to the replicated database. Here are the steps necessary to configure streaming replication on Linux:
1. Set up two identical database server machines. Make sure to disable the firewall on both machines. For the purposes of this tutorial, one of the machines is named PG9master and the other PG9slave. Their names should indicate which one is which.
2. Install the proper PostgreSQL packages for your given distribution on both servers. RedHat, Fedora, and CentOS require postgresql-libs, postgresql-server, and postgresql-contrib. Ubuntu and Debian require postgresql-9.x and postgresql-contrib-9.x, where the x can be 0, 1, 2, or 3 (9.3 is the latest version of PostgreSQL at the time of this writing).
3. Set up some form of file transfer from PG9master to PG9slave. The example in this tutorial assumes that scp will be used. You will need to generate public and private keys that allow scp to transfer files without the need for password exchanges. As the postgres user on PG9master, run the following:
ssh-keygen -t rsa
Don't enter a passphrase. As it asks for it twice, be sure to just hit return twice. Your key files will be saved in the .ssh directory. You will need to copy id_rsa.pub to the PG9slave server using the following command from PG9master:
scp ~/.ssh/id_rsa.pub PG9slave:.ssh/authorized_keys
You will be asked to enter a password for the postgres user on PG9slave. Once the scp is finished, you can test not needing the password by using ssh to log into PG9slave from PG9master using the following:
ssh PG9slave
You should not need to enter the password.
4. You will need to create a directory where log files can be copied. On PG9slave, create a directory called:
~/master_logs
5. You are now ready to edit the postgresql.conf file on PG9master. If you are running one of the RedHat flavors of Linux, it will be located underneath the postgres user's home directory. If you are running Ubuntu, it will be located under the /etc/postgresql directory. Adjust the following parameters:
wal_level = hot_standby
archive_mode = on
archive_command = 'scp %p PG9slave:master_logs/%f'
listen_addresses = '*'
archive_timeout = '120 #2 minute window if streaming fails
max_wal_senders = 1 #assume a single slave
6. Now edit the pg_hba.conf file on PG9master. It will be found in the same directory as the postgresql.conf file. Add the following being sure to replace <IP of PG9slave> with the actual IP address of PG9slave:
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host replication pgrep <IP of PG9slave>/32 md5
7. If you are running the RedHat flavors of Linux, start the PostgreSQL server on PG9master using the following command as the root user:
/etc/init.d/postgresql-9.x start
Make sure to replace the .x with the correct dot release of PostgreSQL that you are using. If you are using the Ubuntu flavor of Linux, you will need to reload PostgreSQL's postgresql.conf file by running the following command as the root user:
service postgresql restart
8. Create a database user on PG9master named pgrep by going into psql and running the following command:
CREATE USER pgrep WITH superuser login PASSWORD 'pgrep_pass';
9. You are now ready to make a filesystem backup of the master database and copy it to the slave. On PG9master, run psql and issue the following command:
SELECT pg_start_backup('backup');
Exit psql using \q and make a copy of the data directory using the following:
cd 9.x
tar -cvf db_backup.tar data
Be sure to replace the .x with the dot version of PostgreSQL you are using. When the backup completes, go back into psql and issue the following command:
SELECT pg_stop_backup();
Copy the tar file to the slave machine using the following scp command:
scp db_backup.tar PG9slave:
10. Restore the master database on the slave. Log into PG9slave as the postgres user and issue the following commands:
cd 9.x
mv ../db_backup.tar .
tar -xvf db_backup.tar
cd data
rm postmaster.pid
rm -rf pg_xlog/*
Being sure to replace .x with the dot version of PostgreSQL you are using.
11. Edit the postgresql.conf file on PG9slave and set the following parameter:
hot_standby = on
If you are running the RedHat flavor of Linux, you will also need to comment out the following parameters that you brought over when you moved the postgresql.conf file from PG9master:
archive_mode
archive_command
archive_timeout
12. Create the recovery.conf file on PG9slave. If you are running the RedHat flavor of Linux, it will be in the same directory as the postgresql.conf file. If you are running Ubuntu, it will need to go in the data directory. It is a new file that should contain the following information and assumes you are running the RedHat flavor of Linux:
standby_mode = 'on'
primary_conninfo = 'host=PG9master port=5432 user=pgrep password = 'pgrep_pass'
restore_command = 'cp -i /var/lib/pgsql/master_logs/%f %p'
archive_cleanup_command = 'pg_archivecleanup /var/lib/pgsql/master_logs %r'
trigger_file = '/tmp/stop_pg_streaming.tmp'
If you are running Ubuntu Linux, you will need to replace the paths in the restore and archive cleanup commands with those used by Ubuntu.
Be sure to make sure that the program pg_archivecleanup is in the postgres user's path. If not, you can symbolically link it. On RedHat you would use:
ln -s /usr/pgsql-9.x/bin/pg_archivecleanup /usr/bin/pg_archivecleanup
On Ubuntu, you would use:
ln -s /usr/lib/postgresql/9.x/bin/pg_archivecleanup /usr/bin/pg_archivecleanup
Again, be sure to replace the .x with the appropriate dot release of Postgresql that you are using.
13. Start the database on PG9slave as root using the following commands. On RedHat use:
/etc/init.d/postgresql-9.x start
Be sure to replace the .x with the point release of PostgreSQL you are using. On Ubuntu, use:
service postgresql start
14. Test everything out. The psql program will work on both systems. You shold be able to create a table on PG9master and have it immediately show up on PG9slave. If there is a delay of 2 minutes, then something is amiss with streaming replication.
Troubleshooting
Always check the log files. As usual RedHat flavors of Linux will store them in different places than Ubuntu. If there is a problem though, you should get some hint of what has gone wrong from the log file.Try logging into the PG9master database from PG9slave using:
psql -h PG9master -U gprep postgres
If you get an error, then streaming replication is not working either.
Make sure that the "wal sender process" is runnning on PG9master by doing the following:
ps aux | grep post
Also make sure there is a "wal receiver process" running on PG9slave.
No comments:
Post a Comment