how_to:postgresql_replication
Differences
This shows you the differences between two versions of the page.
| Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
| how_to:postgresql_replication [2013/06/28 13:09] – paul | how_to:postgresql_replication [2019/02/06 17:10] (current) – [PostgreSQL Replication] paul | ||
|---|---|---|---|
| Line 9: | Line 9: | ||
| Both the Master and Slave PCs should have VPOP3 installed into the same location (eg c:\vpop3 or d:\vpop3, etc), or settings relating to paths will not work if you have to fail over to the Slave PC. Alternatively, | Both the Master and Slave PCs should have VPOP3 installed into the same location (eg c:\vpop3 or d:\vpop3, etc), or settings relating to paths will not work if you have to fail over to the Slave PC. Alternatively, | ||
| + | (We can help set this up database replication for you if you wish, but this will be chargeable - the current rate is £150 + VAT for a simple setup (ie replicating over a LAN with file sharing available between the two PCs). If you have a more complicated setup (eg replicating over the Internet) this can be more complex as it can involve configuring VPNs etc, so the cost may be greater) | ||
| =====On the Master PC===== | =====On the Master PC===== | ||
| * In the VPOP3\pgsql\data folder, find the file **postgresql.conf** and edit it using Notepad or another text editor. At the bottom of the file add the lines: | * In the VPOP3\pgsql\data folder, find the file **postgresql.conf** and edit it using Notepad or another text editor. At the bottom of the file add the lines: | ||
| Line 14: | Line 15: | ||
| wal_level = hot_standby | wal_level = hot_standby | ||
| archive_mode = on | archive_mode = on | ||
| - | archive_command = 'copy %p \\\\192.168.1.10\pgsql\\db_replication' | + | archive_command = 'copy %p \\\\192.168.1.10\\pgsql\\db_replication\\%f' |
| max_wal_senders = 5 | max_wal_senders = 5 | ||
| wal_keep_segments = 128 | wal_keep_segments = 128 | ||
| Line 31: | Line 32: | ||
| * You should see files starting to appear in the share directory as the master starts copying its transaction log files there | * You should see files starting to appear in the share directory as the master starts copying its transaction log files there | ||
| - | * Make sure that port 5433 is allowed through any firewall on the master PC | + | * Make sure that TCP port 5433 is allowed through any firewall on the master PC |
| =====On the Slave PC===== | =====On the Slave PC===== | ||
| + | Once you have made the changes on the Master server, follow these steps on the Slave PC: | ||
| + | |||
| * Install the same version of VPOP3 as on the master PC | * Install the same version of VPOP3 as on the master PC | ||
| * Stop the VPOP3 service, and Disable it | * Stop the VPOP3 service, and Disable it | ||
| Line 39: | Line 42: | ||
| * Rename the c: | * Rename the c: | ||
| - | Go to a command prompt in the VPOP3\pgsql\bin folder and run | + | * Go to a command prompt in the VPOP3\pgsql\bin folder and run |
| pg_basebackup -D c:/ | pg_basebackup -D c:/ | ||
| - | This will make a copy of the database folder on the slave PC | + | This will make a copy of the database folder on the slave PC. This does a full copy of the database folder, so includes all the settings changes made to the Master PC configuration above. |
| + | |||
| + | * Using Notepad or another text editor, make a file called **Recovery.conf** in the VPOP3\pgsql\data folder. The presence of this file tells this installation of PostgreSQL that it is a slave, and how it should contact the master server to replicate the data. Make this file contain the following contents: | ||
| + | |||
| + | standby_mode = ' | ||
| + | primary_conninfo = ' | ||
| + | trigger_file = ' | ||
| + | restore_command = 'copy \\\\192.168.1.10\\pgsql\\db_replication\\%f %p' | ||
| + | archive_cleanup_command = ' | ||
| + | |||
| + | * Start the VPOP3DB service on the Standby PC | ||
| + | |||
| + | Now, if you need to fail over, you need to: | ||
| + | - ensure that the VPOP3 and VPOP3DB services are stopped on the Master PC | ||
| + | - copy any file (contents don't matter) to a file called '' | ||
| + | - Enable and start the VPOP3 service on the Slave PC | ||
| + | - Either change the IP address on the Slave PC to match that which was used on the Master PC (ensuring there is no conflict), or change your local DNS server to point email clients at the new IP address, or change all the email account settings in your email clients to point to the Slave PC's IP address. | ||
| + | |||
| + | To start replicating back to the ex-master, you need to do the above steps (most will already have been carried out) - ie do a new 'base backup' | ||
| + | |||
| + | =====Monitoring Replication===== | ||
| + | On the Master PC, run ' | ||
| + | |||
| + | select pg_current_xlog_location(); | ||
| + | |||
| + | That will give some numbers (in hexadecimal). This shows the current ' | ||
| + | |||
| + | On the Slave PC, run ' | ||
| + | |||
| + | select pg_last_xlog_replay_location(); | ||
| + | |||
| + | That will give some numbers in hexadecimal as well. This shows how far the Slave PC has got with ' | ||
| + | |||
| + | The difference between the two numbers shows how far behind the Slave is from the Master PC. Usually, as long as the Slave PC's counter is increasing, and is not too far behind the Master PC's counter, then everything is OK. | ||
how_to/postgresql_replication.1372421370.txt.gz · Last modified: 2018/11/14 10:44 (external edit)
Page Tools
