Thread: streaming replication + wal shipping
Hi All,
I would like to setup a 2 servers with streaming replication, one master and another hot standby.
I want to use the standby for read-only queries. So I want the replication lag to be as small as possible.
So I choose streaming replication over WAL shipping.
When the master fails, I want the standby to take over as master. So I would like minimal data loss, if there is a streaming replication delay.
Is it possible to setup such a way that under normal conditions the standby by replicating using streaming replication and on failover, it uses the WAL archive for syncing up with the transactions. Of course the WAL will be available on a shared storage volume. If this is possible, what exactly do I need in my configuration files - postgresql.conf, recovery.conf ?
Thanks for your help
Anupama.
Anupama Ramaswamy wrote: > I would like to setup a 2 servers with streaming replication, one master and another hot standby. > I want to use the standby for read-only queries. So I want the replication lag to be as small as > possible. > So I choose streaming replication over WAL shipping. > > When the master fails, I want the standby to take over as master. So I would like minimal data loss, > if there is a streaming replication delay. > > Is it possible to setup such a way that under normal conditions the standby by replicating using > streaming replication and on failover, it uses the WAL archive for syncing up with the transactions. > Of course the WAL will be available on a shared storage volume. If this is possible, what exactly do I > need in my configuration files - postgresql.conf, recovery.conf ? Most of this will happen automatically - WAL archives are used if recovery falls behind. Where you will need additional software is automatic failover; you need some OS cluster software that can detect failure and automatically promote the standby. Yours, Laurenz Albe
Thanks for your response.
So are you saying that if I setup the following in my recovery.conf
restore_command =.....
It will it be used only when the streaming replication falls behind more than ( wal_keep_segments ) or replication stream is not available (master goes down) ?
Thanks for your help.
On Monday, April 14, 2014 1:35 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
Anupama Ramaswamy wrote:
> I would like to setup a 2 servers with streaming replication, one master and another hot standby.
> I want to use the standby for read-only queries. So I want the replication lag to be as small as
> possible.
> So I choose streaming replication over WAL shipping.
>
> When the master fails, I want the standby to take over as master. So I would like minimal data loss,
> if there is a streaming replication delay.
>
> Is it possible to setup such a way that under normal conditions the standby by replicating using
> streaming replication and on failover, it uses the WAL archive for syncing up with the transactions.
> Of course the WAL will be available on a shared storage volume. If this is possible, what exactly do I
> need in my configuration files - postgresql.conf, recovery.conf ?
Most of this will happen automatically - WAL archives are used if recovery
falls behind.
Where you will need additional software is automatic failover; you need some
OS cluster software that can detect failure and automatically promote the standby.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> I would like to setup a 2 servers with streaming replication, one master and another hot standby.
> I want to use the standby for read-only queries. So I want the replication lag to be as small as
> possible.
> So I choose streaming replication over WAL shipping.
>
> When the master fails, I want the standby to take over as master. So I would like minimal data loss,
> if there is a streaming replication delay.
>
> Is it possible to setup such a way that under normal conditions the standby by replicating using
> streaming replication and on failover, it uses the WAL archive for syncing up with the transactions.
> Of course the WAL will be available on a shared storage volume. If this is possible, what exactly do I
> need in my configuration files - postgresql.conf, recovery.conf ?
Most of this will happen automatically - WAL archives are used if recovery
falls behind.
Where you will need additional software is automatic failover; you need some
OS cluster software that can detect failure and automatically promote the standby.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> Anupama Ramaswamy wrote: >>> I would like to setup a 2 servers with streaming replication, one master and another hot standby. >>> I want to use the standby for read-only queries. So I want the replication lag to be as small as >>> possible. >>> So I choose streaming replication over WAL shipping. >>> >>> When the master fails, I want the standby to take over as master. So I would like minimal data loss, >>> if there is a streaming replication delay. >>> >>> Is it possible to setup such a way that under normal conditions the standby by replicating using >>> streaming replication and on failover, it uses the WAL archive for syncing up with the transactions. >>> Of course the WAL will be available on a shared storage volume. If this is possible, what exactly do >>> I need in my configuration files - postgresql.conf, recovery.conf ? >> >> Most of this will happen automatically - WAL archives are used if recovery >> falls behind. > So are you saying that if I setup the following in my recovery.conf > restore_command =..... > > It will it be used only when the streaming replication falls behind more than ( wal_keep_segments ) or > replication stream is not available (master goes down) ? Yes. Yours, Laurenz Albe
*postgresql.conf:* wal_level = hot_standby hot_standby = on archive_mode = on archive_command = 'cp %p ../archive/%f' ## "archive" parallel to data directory max_wal_senders = 10 ## number of slaves *recovery.conf(in slave)* restore_command = 'cp ../archive/%f "%p"' standby_mode = 'on' primary_conninfo = 'host=MASTERIP port=5432 user=REPLUSER password=REPLPASS' trigger_file = '/tmp/pg_failover_trigger' recovery_target_timeline='latest' *pg_hba.conf* host replication REPLUSER 0.0.0.0/0 md5 The above entries to be added for replication to be configured. -Senthil -- View this message in context: http://postgresql.1045698.n5.nabble.com/streaming-replication-wal-shipping-tp5799821p5800081.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.