Thread: Master - slave replication?
I have a database that I would like to replicate in case of hardware failure on this server. So I gather I just need a streamingreplication (warm standby?) and I found a howto describing how to set it up, that looks like this: Binary Replication in 7 Steps This 6-step guide, and all of the examples in this tutorial, assume that you have a master server at 192.168.0.1 and a standbyserver at 192.168.0.2 and that your database and its configuration files are installed at /var/lib/postgresql/data.Replace those with whatever your actual server addresses and directories are. 1. Edit postgresql.conf on the master to turn on streaming replication. Change these settings: listen_addresses = '*' wal_level = hot_standby max_wal_senders = 3 2. Edit pg_hba.conf on the master in order to let the standby connect. host replication all 192.168.0.2/32 trust 3. Edit postgresql.conf on the standby to set up hot standby. Change this line: hot_standby = on 4. Create or edit recovery.conf on the standby to set up replication and standby mode. Save a file in the standby's datadirectory, called recovery.conf, with the following lines: standby_mode = 'on' primary_conninfo = 'host=192.168.0.1' 5. Shut down both the master and standby, and copy the files. You want to copy most but not all files between the two servers,excluding the configuration files and the pg_xlog directory. An example rsync script would be: rsync -av --exclude pg_xlog --exclude postgresql.conf data/* 192.168.0.2:/var/lib/postgresql/data/ 6. Start the standby first, so that they can't get out of sync. (Messages will be logged about not being able to connectto the primary server, that's OK.) 7. Start the master. Is this all that is needed to get a working master - slave replication? (It just looks too easy... :) ) Do I need to enable archive mode for this to work or? Never done this before, so just trying to be sure I understand this right... :) Regards, BTJ -- ----------------------------------------------------------------------------------------------- Bjørn T Johansen btj@havleik.no ----------------------------------------------------------------------------------------------- Someone wrote: "I understand that if you play a Windows CD backwards you hear strange Satanic messages" To which someone replied: "It's even worse than that; play it forwards and it installs Windows" -----------------------------------------------------------------------------------------------
Bjørn T Johansen schrieb am 26.10.2016 um 13:17: > I have a database that I would like to replicate in case of hardware failure on this server. So I gather I just need astreaming replication (warm > standby?) and I found a howto describing how to set it up, that looks like this: > > 1. Edit postgresql.conf on the master to turn on streaming replication. Change these settings: > listen_addresses = '*' > wal_level = hot_standby > max_wal_senders = 3 > 2. Edit pg_hba.conf on the master in order to let the standby connect. > host replication all 192.168.0.2/32 trust > 3. Edit postgresql.conf on the standby to set up hot standby. Change this line: > hot_standby = on > 4. Create or edit recovery.conf on the standby to set up replication and standby mode. Save a file in the standby's datadirectory, called recovery.conf, with the following lines: > standby_mode = 'on' > primary_conninfo = 'host=192.168.0.1' > 5. Shut down both the master and standby, and copy the files. You want to copy most but not all files between the two servers,excluding the configuration files and the pg_xlog directory. An example rsync script would be: > rsync -av --exclude pg_xlog --exclude postgresql.conf data/* 192.168.0.2:/var/lib/postgresql/data/ > 6. Start the standby first, so that they can't get out of sync. (Messages will be logged about not being able to connectto the primary server, that's OK.) > 7. Start the master. > > Is this all that is needed to get a working master - slave replication? (It just looks too easy... :) ) Yes, that's all. In fact 5. could be done withoug shutting down the master using pg_basebackup > Do I need to enable archive mode for this to work or? No. "wal_level = hot_standby" includes that.
On Wed, 26 Oct 2016 13:59:02 +0200 Thomas Kellerer <spam_eater@gmx.net> wrote: > Bjørn T Johansen schrieb am 26.10.2016 um 13:17: > > I have a database that I would like to replicate in case of hardware failure on this server. So I gather I just needa streaming replication (warm > > standby?) and I found a howto describing how to set it up, that looks like this: > > > > 1. Edit postgresql.conf on the master to turn on streaming replication. Change these settings: > > listen_addresses = '*' > > wal_level = hot_standby > > max_wal_senders = 3 > > 2. Edit pg_hba.conf on the master in order to let the standby connect. > > host replication all 192.168.0.2/32 trust > > 3. Edit postgresql.conf on the standby to set up hot standby. Change this line: > > hot_standby = on > > 4. Create or edit recovery.conf on the standby to set up replication and standby mode. Save a file in the standby's datadirectory, called recovery.conf, with the following lines: > > standby_mode = 'on' > > primary_conninfo = 'host=192.168.0.1' > > 5. Shut down both the master and standby, and copy the files. You want to copy most but not all files between the twoservers, excluding the configuration files and the pg_xlog directory. An example rsync script would be: > > rsync -av --exclude pg_xlog --exclude postgresql.conf data/* 192.168.0.2:/var/lib/postgresql/data/ > > 6. Start the standby first, so that they can't get out of sync. (Messages will be logged about not being able to connectto the primary server, that's OK.) > > 7. Start the master. > > > > Is this all that is needed to get a working master - slave replication? (It just looks too easy... :) ) > > Yes, that's all. > > In fact 5. could be done withoug shutting down the master using pg_basebackup > > > Do I need to enable archive mode for this to work or? > > No. > > "wal_level = hot_standby" includes that. > > > Ok, thx.. :) BTJ