Re: [GENERAL] Replication slot and pg_rewind

[Michael Paquier]

On Tue, Jun 6, 2017 at 1:52 PM, Bhattacharyya, Subhro
 wrote:
> Our expectation is that slave will be able to sync with the new master with 
> the help of whatever WALs are present in the new master due to replication 
> slots.
> Can pg_rewind still work without WAL archiving in this scenario.

I see. Yes, the slot on the old primary would keep retaining WAL, and
the promoted standby would stop sending feedback once it has switched
to a new timeline so that should work. Don't forget to drop the drop
on the old primary after pg_rewind has been run, you don't want to
bloat its pg_xlog with useless data.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication slot and pg_rewind

[Bhattacharyya, Subhro]

Our cluster works as follows:

We do not promote the slave while the primary is up.

During an update scenario, when the master goes down the slave is promoted to 
master only if there is no replication lag.

As a result, we do not have any data difference till now.

Transactions now continue on the newly promoted master thus creating a 
difference in data on the two nodes.

When the original master, post update comes back as slave, instead of taking a 
pg_basebackup, we use pg_rewind.

Our expectation is that slave will be able to sync with the new master with the 
help of whatever WALs are present in the new master due to replication slots.

Can pg_rewind still work without WAL archiving in this scenario.

Thanks, Subhro

-Original Message-
From: Michael Paquier [mailto:michael.paqu...@gmail.com] 
Sent: Tuesday, June 6, 2017 8:50 AM
To: Bhattacharyya, Subhro <s.bhattachar...@sap.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Replication slot and pg_rewind

On Tue, Jun 6, 2017 at 12:03 PM, Bhattacharyya, Subhro
<s.bhattachar...@sap.com> wrote:
> We are using the replication slot and pg_rewind feature of postgresql 9.6
> Our cluster consists of 1 master and 1 slave node.
>
> The replication slot feature allows the master to keep as much WAL as is
> required by the slave.
>
> The pg_rewind command uses WALs to bring the slave in sync with the master.
> By using replication slots there are always enough WAL in the pg_xlog.
>
> In this case is it safe to use pg_rewind without WAL archiving?
> Can there be a situation where pg_rewind fails?

When pg_rewind runs it looks at the WAL from the last checkpoint
before WAL diverged on the *target* node, not the source. So retaining
the WAL data on the primary after the standby has been promoted makes
little sense from this point of view. Even worse, once the promoted
standby decides to recycle the past WAL segments you won't be able to
do a rewind of the previous primary because there is no way to know
what are the blocks modified on the standby since the point of
divergence.
-- 
Michael

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication slot and pg_rewind

[Michael Paquier]

On Tue, Jun 6, 2017 at 12:03 PM, Bhattacharyya, Subhro
 wrote:
> We are using the replication slot and pg_rewind feature of postgresql 9.6
> Our cluster consists of 1 master and 1 slave node.
>
> The replication slot feature allows the master to keep as much WAL as is
> required by the slave.
>
> The pg_rewind command uses WALs to bring the slave in sync with the master.
> By using replication slots there are always enough WAL in the pg_xlog.
>
> In this case is it safe to use pg_rewind without WAL archiving?
> Can there be a situation where pg_rewind fails?

When pg_rewind runs it looks at the WAL from the last checkpoint
before WAL diverged on the *target* node, not the source. So retaining
the WAL data on the primary after the standby has been promoted makes
little sense from this point of view. Even worse, once the promoted
standby decides to recycle the past WAL segments you won't be able to
do a rewind of the previous primary because there is no way to know
what are the blocks modified on the standby since the point of
divergence.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] replication slot to be used in the future

[Tom DalPozzo]

2017-01-04 18:24 GMT+01:00 Adrian Klaver :

> On 01/04/2017 08:44 AM, Tom DalPozzo wrote:
>
>> Hi,
>>
>
> Postgres version?
>
> Because in 9.6:
>
> https://www.postgresql.org/docs/9.6/static/functions-admin.h
> tml#FUNCTIONS-REPLICATION
>
> Table 9-82. Replication SQL Functions
>
> pg_create_physical_replication_slot(slot_name name [, immediately_reserve
> boolean ])
>
> Creates a new physical replication slot named slot_name. The optional
> second parameter, when true, specifies that the LSN for this replication
> slot be reserved immediately; otherwise the LSN is reserved on first
> connection from a streaming replication client. Streaming changes from a
> physical slot is only possible with the streaming-replication protocol —
> see Section 51.3. This function corresponds to the replication protocol
> command CREATE_REPLICATION_SLOT ... PHYSICAL.
>
>
>>
Hi, it is 9.5.5!
Thank you very much!

Pupillo

Re: [GENERAL] replication slot to be used in the future

[Adrian Klaver]

On 01/04/2017 08:44 AM, Tom DalPozzo wrote:

Hi,


Postgres version?

Because in 9.6:

https://www.postgresql.org/docs/9.6/static/functions-admin.html#FUNCTIONS-REPLICATION

Table 9-82. Replication SQL Functions

pg_create_physical_replication_slot(slot_name name [, 
immediately_reserve boolean ])


Creates a new physical replication slot named slot_name. The optional 
second parameter, when true, specifies that the LSN for this replication 
slot be reserved immediately; otherwise the LSN is reserved on first 
connection from a streaming replication client. Streaming changes from a 
physical slot is only possible with the streaming-replication protocol — 
see Section 51.3. This function corresponds to the replication protocol 
command CREATE_REPLICATION_SLOT ... PHYSICAL.



I've got my primary and I make a pg_basebackup -x in order to create a
standby.
I can connect my standby only later, in some hours, so I'd like the
master to keep new WALs but I don't like to use archiving nor
keep-segments option. I thought to do it through a physical replication
slot (my standby will have its replication slot name).
So I create a physical replication slot but I see that the master, which
has never seen my standby connected to him, doesn't keep WALs.

Any idea?

Regards
Pupillo







--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] replication setup: advice needed

[Dmitry Karasik]

> You need to look at a replication solution like Slony, which is a trigger
> based replication solution. If you are using PostgreSQL version 9.4 or
> higher, then, you can explore "pglogical" which is WAL based and uses
> logical decoding capability.

I'm using 9.4, and I'm looking at pglogical as well -- thank you!. I'll
try to experiment how much it fits my needs.


-- 
Sincerely,
Dmitry Karasik



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] replication setup: advice needed

[Venkata B Nagothi]

On Thu, Nov 3, 2016 at 8:17 PM, Dmitry Karasik 
wrote:

> Dear all,
>
> I'd like to ask for help or advice with choosing the best replication
> setup for
> my task.
>
> I need to listen to continuous inserts/deletes/updates over a set of
> tables,
> and serve them over http, so I would like to off-load this procedure to a
> separate slave machine.  I thought that logical master-slave replication
> could
> be the best match here, but I couldn't find enough details in the
> documentation
> which implementation would match my needs best.
>

Which version of PostgreSQL are you using ?


>
> Basically, I need to:
>
> a) replicate selected tables to a hot standby slave
> b) on the slave, listen for the insert/update/delete events (either
> through triggers or logical decoder plugin)
>
> While I see that a) should be feasible, I can't see if it's possible to do
> b) at all.
> Also, with so many replication solutions, I don't want to test them all
> one by one, but
> rather would like to ask for help choosing the one goes best here -- and
> if there's none,
> an alternative setup then.
>

You need to look at a replication solution like Slony, which is a trigger
based replication solution. If you are using PostgreSQL version 9.4 or
higher, then, you can explore "pglogical" which is WAL based and uses
logical decoding capability.

If you are just looking at replicating specific tables, then either of the
above solutions would work fine.

Regards,

Venkata B N
Database Consultant

Fujitsu Australia

Re: [GENERAL] Replication (BDR) problem: won't catch up after connection timeout

[Craig Ringer]

See also https://github.com/2ndQuadrant/bdr/issues/233


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication (BDR) problem: won't catch up after connection timeout

[Craig Ringer]

Increase wal_sender_timeout to resolve the issue.

I've been investigating just this issue recently. See
https://www.postgresql.org/message-id/camsr+ye2dsfhvr7iev1gspzihitwx-pmkd9qalegctya+sd...@mail.gmail.com
.

It would be very useful to me to know more about the transaction that
caused this problem.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication rolling back to normal.

[Michael Paquier]

On Mon, Oct 24, 2016 at 2:20 PM, Dasitha Karunajeewa
 wrote:
> Hi Michael,
>
> Thanks a lot for the information. I am totally new to Postgres clustering. I
> have follow up the below-attached article.
> https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps
>
> Can u guide me on how to do it as u mentioned :)

Documentation is your best friend:
https://www.postgresql.org/docs/9.6/static/high-availability.html
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication rolling back to normal.

[Michael Paquier]

On Fri, Oct 21, 2016 at 10:10 PM, Dasitha Karunajeewa
 wrote:
> What I want to know is how to switch them back to the normal status. That
> means pgmaster need to be the Master server which acept the writed and
> pgslave that accepts only reads along with the replication.

If your promoted standby got ahead of the former master, you could use
the promoted standby as a new master, and replug the former master as
a standby. The latter step can be done by either running pg_rewind on
the former masfer or taking a new base backup from the promoted
standby and use that to set up a new standby. pg_rewind will be
successful to run only if the master has kept WAL segments from the
last checkpoint record where WAL forked when standby has been
promoted.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication slot on master failure

[Michael Paquier]

On Mon, Sep 26, 2016 at 7:49 PM, hariprasath nallasamy
 wrote:
>We are using replication slot for capturing some change sets to
> update dependent tables.
>Will there be inconsistency if the master fails and the standby takes
> the role of master.?

Replication slot creation is not replicated to standbys if that's what
you are looking for. So if you use a slot on master and consume its
data up to a given point, and then promote a standby, you may see a
gap of data after creating a slot, or if you created a slot previously
you may consume twice the same WAL records. Note that replication
slots created on standbys are initialized from the last checkpoint
redo record, so you could take advantage of this property before
promoting a standby.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication Recommendation

[Vick Khera]

On Mon, Sep 12, 2016 at 3:46 PM, Lee Hachadoorian
 wrote:
> * Because database is updated infrequently, workforce can come
> together for LAN-based replication as needed
> * Entire database is on the order of a few GB

Just update one copy, then send pg_dump's to the others for stomping
over the old one.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication Recommendation

[Adrian Klaver]

On 09/12/2016 02:35 PM, Lee Hachadoorian wrote:

On Mon, Sep 12, 2016 at 5:12 PM, Adrian Klaver
 wrote:

On 09/12/2016 12:46 PM, Lee Hachadoorian wrote:


There are a wide variety of Postgres replication solutions, and I
would like advice on which one would be appropriate to my use case.

* Small (~half dozen) distributed workforce using a file sharing
service, but without access to direct network connection over the
internet
* Database is updated infrequently, when new government agency data
releases replace old data
* Because database is updated infrequently, workforce can come
together for LAN-based replication as needed
* Entire database is on the order of a few GB

Given this, I am considering the super lowtech "replication" solution
of updating "master" and doing a full database drop and restore on the
"slaves". But I would like to know which of the other (real)
replication solutions might work for this use case.



If I follow correctly the layout is?:

Main database <--- Govt. data
|
|
   \ /

   File share
|
|
   \ /

DB   DBDB   DB   DBDB

User 1   User 2User 3   User 4   User 5User 6



For your simple scenario you might want to look at:

https://www.postgresql.org/docs/9.5/static/app-pgbasebackup.html



That diagram is what I am proposing.

pg_basebackup looks interesting. My initial impression is that the
main gain would be for a multiple database cluster. Are there other
advantages to using this in preference to a full DB dump and restore
if all of our data will be in a single database?


Not sure.

pg_basebackup can:

"There is no guarantee that all WAL files required for the backup are 
archived at the end of backup. If you are planning to use the backup for 
an archive recovery and want to ensure that all required files are 
available at that moment, you need to include them into the backup by 
using -x option."


At that point you have a complete $DATADIR. So on your user machines it 
then becomes a matter of stopping the server clearing out the old 
$DATADIR and dropping the new one in place and starting the server. 
Whether that is faster then having pg_restore connect to a database and 
then process the dump file is something you will have to test.




Best,
--Lee




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication Recommendation

[Lee Hachadoorian]

On Mon, Sep 12, 2016 at 5:12 PM, Adrian Klaver
 wrote:
> On 09/12/2016 12:46 PM, Lee Hachadoorian wrote:
>>
>> There are a wide variety of Postgres replication solutions, and I
>> would like advice on which one would be appropriate to my use case.
>>
>> * Small (~half dozen) distributed workforce using a file sharing
>> service, but without access to direct network connection over the
>> internet
>> * Database is updated infrequently, when new government agency data
>> releases replace old data
>> * Because database is updated infrequently, workforce can come
>> together for LAN-based replication as needed
>> * Entire database is on the order of a few GB
>>
>> Given this, I am considering the super lowtech "replication" solution
>> of updating "master" and doing a full database drop and restore on the
>> "slaves". But I would like to know which of the other (real)
>> replication solutions might work for this use case.
>
>
> If I follow correctly the layout is?:
>
> Main database <--- Govt. data
> |
> |
>\ /
>
>File share
> |
> |
>\ /
>
> DB   DBDB   DB   DBDB
>
> User 1   User 2User 3   User 4   User 5User 6
>
>
>
> For your simple scenario you might want to look at:
>
> https://www.postgresql.org/docs/9.5/static/app-pgbasebackup.html
>

That diagram is what I am proposing.

pg_basebackup looks interesting. My initial impression is that the
main gain would be for a multiple database cluster. Are there other
advantages to using this in preference to a full DB dump and restore
if all of our data will be in a single database?

Best,
--Lee


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication Recommendation

[Adrian Klaver]

On 09/12/2016 12:46 PM, Lee Hachadoorian wrote:

There are a wide variety of Postgres replication solutions, and I
would like advice on which one would be appropriate to my use case.

* Small (~half dozen) distributed workforce using a file sharing
service, but without access to direct network connection over the
internet
* Database is updated infrequently, when new government agency data
releases replace old data
* Because database is updated infrequently, workforce can come
together for LAN-based replication as needed
* Entire database is on the order of a few GB

Given this, I am considering the super lowtech "replication" solution
of updating "master" and doing a full database drop and restore on the
"slaves". But I would like to know which of the other (real)
replication solutions might work for this use case.


If I follow correctly the layout is?:

Main database <--- Govt. data
|
|
   \ /

   File share
|
|
   \ /

DB   DBDB   DB   DBDB

User 1   User 2User 3   User 4   User 5User 6



For your simple scenario you might want to look at:

https://www.postgresql.org/docs/9.5/static/app-pgbasebackup.html




Regards,
--Lee




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication with non-read-only standby.

[Nick Babadzhanian]

Thanks.

I ended up using pglogical, since I don't really need Bi-directional 
replication and docs for UDR suggest using pglogical instead.
Although I ran into a problem there, but pglogical seems to be the answer.

Regards,
Nick.

- Original Message -
From: "Sylvain Marechal" <marechal.sylva...@gmail.com>
To: "Nick Babadzhanian" <n...@cobra.ru>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Wednesday, July 6, 2016 11:00:05 PM
Subject: Re: [GENERAL] Replication with non-read-only standby.

2016-06-30 15:15 GMT+02:00 Nick Babadzhanian <n...@cobra.ru>:

> Setup:
> 2 PostgreSQL servers are geographically spread. The first one is used for
> an application that gathers data. It is connected to the second database
> that is used to process the said data. Connection is not very stable nor is
> it fast, so using Bidirectional replication is not an option. It is OK if
> data is shipped in batches rather than streamed.
>
> Question:
> Is there a way to make the standby server non-read-only, so that it can
> keep getting updates (mostly inserts) from the 'master', but users are able
> to edit the data stored on 'slave'? Is there some alternative solution to
> this?
>
> Regards,
> Nick.
>
> Hi Nick,

sorry for this silly question, but I am not sure to understand why BDR is
not an option.
As far as I know, it was designed to handle such cases.

My 2 cents,
Sylvain


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication with non-read-only standby.

[Moreno Andreo]

  
  
Il 01/07/2016 05:21, Venkata Balaji N
  ha scritto:


  

  On Thu, Jun 30, 2016 at 11:15 PM,
Nick Babadzhanian  wrote:
Setup:
  2 PostgreSQL servers are geographically spread. The first
  one is used for an application that gathers data. It is
  connected to the second database that is used to process
  the said data. Connection is not very stable nor is it
  fast, so using Bidirectional replication is not an option.
  It is OK if data is shipped in batches rather than
  streamed.
  
  Question:
  Is there a way to make the standby server non-read-only,
  so that it can keep getting updates (mostly inserts) from
  the 'master', but users are able to edit the data stored
  on 'slave'? Is there some alternative solution to this?



You can consider Ruby replication for such a
  requirement. I think, there is no much development
  happening around Ruby Replication since long time i
  believe. This can be used for production environment.


http://www.rubyrep.org/





Regards,
Venkata B N


Fujitsu Australia
  

  


I'm using rubyrep actively in the last 5 years, and that's what in
my experience

The replicator is very good and stable, easy as 1-2-3 to configure
(if you don't need special features), but the project is almost dead
(I've seen no updates since 2009 and no responses in forums since
2011). 
I've tried many times to email the author because of PG 9.1 changes
in bytea management that caused BLOB corruption while replicating,
but never had response, so ended hiring a Ruby developer to fix
things.

One more thing: rubyrep is OK if you want to replicate ONE database,
I've never seen it working on more than 1 database or a whole
cluster. Of course you can run more than one instance, but will be
harder to manage.
If replicating on *nix I'd prefer Bucardo or Slony

Remember, when using async replication with unreliable network, that
your replication can fall far behind "actual" data and this can lead
to conflicts, that must be resolved.
Not to mention the huge memory consumption when working with large
data types and when replication queues get quite big (>300k
rows). In this cases, if JVM memory cap is not large enough(I
reached 2 GB), rubyrep is likely to stop for OutOfMemoryException

My 50 cents
Cheers,
Moreno.
  

Re: [GENERAL] Replication with non-read-only standby.

[Scott Marlowe]

On Thu, Jun 30, 2016 at 7:15 AM, Nick Babadzhanian  wrote:
> Setup:
> 2 PostgreSQL servers are geographically spread. The first one is used for an 
> application that gathers data. It is connected to the second database that is 
> used to process the said data. Connection is not very stable nor is it fast, 
> so using Bidirectional replication is not an option. It is OK if data is 
> shipped in batches rather than streamed.
>
> Question:
> Is there a way to make the standby server non-read-only, so that it can keep 
> getting updates (mostly inserts) from the 'master', but users are able to 
> edit the data stored on 'slave'? Is there some alternative solution to this?

I'd probably solve this with slony.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication

[Bertrand Paquet]

Hi,

Thx you for answering.

Regards,

Bertrand

2016-06-06 10:22 GMT+02:00 Vik Fearing :

> On 06/06/16 09:54, Masahiko Sawada wrote:
> > On Sat, Jun 4, 2016 at 10:58 PM, Vik Fearing  wrote:
> >> On 02/06/16 15:32, Bertrand Paquet wrote:
> >>> Hi,
> >>>
> >>> On an hot standby streaming server, is there any way to know, in SQL,
> to
> >>> know the ip of current master ?
> >>
> >> No.
> >>
> >>> The solution I have is to read the recovery.conf file to find
> >>> primary_conninfo,
> >>
> >> That is currently the only solution.  There are plans to allow SQL
> >> access to the parameters in recovery.conf (or to merge them into
> >> postgresql.conf) but that's not currently possible.
> >
> > It might not be a right way but how about using pg_read_file()?
> > postgres(1)=# select regexp_replace(pg_read_file('recovery.conf'),
> > '.*primary_conninfo = (.*)', '\1');
> >   regexp_replace
> > ---
> >  'host=localhost port=5550 application_name=node1'+
> >
> > (1 row)
> >
> > You can get the master server information via SQL from standby server.
>
> This is a good idea, but suffers the same problem that Bertrand has with
> looking at the file a different way: if the file was changed but the
> standby server has not been restarted, it's (potentially) not going to
> be the correct information.
> --
> Vik Fearing  +33 6 46 75 15 36
> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
>

Re: [GENERAL] Replication

[Vik Fearing]

On 06/06/16 09:54, Masahiko Sawada wrote:
> On Sat, Jun 4, 2016 at 10:58 PM, Vik Fearing  wrote:
>> On 02/06/16 15:32, Bertrand Paquet wrote:
>>> Hi,
>>>
>>> On an hot standby streaming server, is there any way to know, in SQL, to
>>> know the ip of current master ?
>>
>> No.
>>
>>> The solution I have is to read the recovery.conf file to find
>>> primary_conninfo,
>>
>> That is currently the only solution.  There are plans to allow SQL
>> access to the parameters in recovery.conf (or to merge them into
>> postgresql.conf) but that's not currently possible.
> 
> It might not be a right way but how about using pg_read_file()?
> postgres(1)=# select regexp_replace(pg_read_file('recovery.conf'),
> '.*primary_conninfo = (.*)', '\1');
>   regexp_replace
> ---
>  'host=localhost port=5550 application_name=node1'+
> 
> (1 row)
> 
> You can get the master server information via SQL from standby server.

This is a good idea, but suffers the same problem that Bertrand has with
looking at the file a different way: if the file was changed but the
standby server has not been restarted, it's (potentially) not going to
be the correct information.
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication

[Masahiko Sawada]

On Sat, Jun 4, 2016 at 10:58 PM, Vik Fearing  wrote:
> On 02/06/16 15:32, Bertrand Paquet wrote:
>> Hi,
>>
>> On an hot standby streaming server, is there any way to know, in SQL, to
>> know the ip of current master ?
>
> No.
>
>> The solution I have is to read the recovery.conf file to find
>> primary_conninfo,
>
> That is currently the only solution.  There are plans to allow SQL
> access to the parameters in recovery.conf (or to merge them into
> postgresql.conf) but that's not currently possible.
>

It might not be a right way but how about using pg_read_file()?
postgres(1)=# select regexp_replace(pg_read_file('recovery.conf'),
'.*primary_conninfo = (.*)', '\1');
  regexp_replace
---
 'host=localhost port=5550 application_name=node1'+

(1 row)

You can get the master server information via SQL from standby server.

Regards,

--
Masahiko Sawada


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication

[Vik Fearing]

On 02/06/16 18:39, John R Pierce wrote:
> On 6/2/2016 6:32 AM, Bertrand Paquet wrote:
>> On an hot standby streaming server, is there any way to know, in SQL,
>> to know the ip of current master ?
>> The solution I have is to read the recovery.conf file to find
>> primary_conninfo, but, it can be false.
> 
> "The IP" assumes there is only one...   hosts can be multihomed,
> postgres can be listening on numerous interfaces, there is no 'the IP'

That's nice, but a standby is only connecting to one.
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication

[Vik Fearing]

On 02/06/16 15:32, Bertrand Paquet wrote:
> Hi,
> 
> On an hot standby streaming server, is there any way to know, in SQL, to
> know the ip of current master ?

No.

> The solution I have is to read the recovery.conf file to find
> primary_conninfo,

That is currently the only solution.  There are plans to allow SQL
access to the parameters in recovery.conf (or to merge them into
postgresql.conf) but that's not currently possible.

> but, it can be false.

It would only be wrong if recovery.conf has been edited since the stanby
was last restarted.

There are hooks for connections and disconnections of the walreceiver,
so it should be possible and fairly simple to write an extension that
remembers and exposes the primary_conninfo in effect.
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication

[John R Pierce]

On 6/2/2016 6:32 AM, Bertrand Paquet wrote:
On an hot standby streaming server, is there any way to know, in SQL, 
to know the ip of current master ?
The solution I have is to read the recovery.conf file to find 
primary_conninfo, but, it can be false.


"The IP" assumes there is only one...   hosts can be multihomed, 
postgres can be listening on numerous interfaces, there is no 'the IP'


--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication

[Sameer Kumar]

On Thu, 2 Jun 2016, 10:34 p.m. Scott Mead,  wrote:

> On Thu, Jun 2, 2016 at 10:16 AM, Melvin Davidson 
> wrote:
>
>> It's been a few years since I worked with slony, and you did not state
>> which version of slony or PostgreSQL you are working with, nor did you
>> indicate the O/S.
>>
>
> I think OP had pointed to using streaming
>
>
>> That being said, you should be able to formulate a query with a join
>> between sl_path & sl_node that gives you the information you need.
>>
>> On Thu, Jun 2, 2016 at 9:32 AM, Bertrand Paquet <
>> bertrand.paq...@doctolib.fr> wrote:
>>
>>> Hi,
>>>
>>> On an hot standby streaming server, is there any way to know, in SQL, to
>>> know the ip of current master ?
>>> The solution I have is to read the recovery.conf file to find
>>> primary_conninfo, but, it can be false.
>>>
>>>
> I've run into this as well.  Only way is recovery.conf.
>

9.6 onward you will have a new view which will facilitate you to query the
replication details on standby.

I have not tried but probably you can check the pid of wal receiver and
find out what host it is connected to (should be possible from network
stats).


> --Scott
>
>
>
>> Regards,
>>>
>>> Bertrand
>>>
>>
>>
>>
>> --
>> *Melvin Davidson*
>> I reserve the right to fantasize.  Whether or not you
>> wish to share my fantasy is entirely up to you.
>>
>
>
>
> --
> --
> Scott Mead
> Sr. Architect
> *OpenSCG *
> http://openscg.com
>
-- 
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

Re: [GENERAL] Replication

[Scott Mead]

On Thu, Jun 2, 2016 at 10:16 AM, Melvin Davidson 
wrote:

> It's been a few years since I worked with slony, and you did not state
> which version of slony or PostgreSQL you are working with, nor did you
> indicate the O/S.
>

I think OP had pointed to using streaming


> That being said, you should be able to formulate a query with a join
> between sl_path & sl_node that gives you the information you need.
>
> On Thu, Jun 2, 2016 at 9:32 AM, Bertrand Paquet <
> bertrand.paq...@doctolib.fr> wrote:
>
>> Hi,
>>
>> On an hot standby streaming server, is there any way to know, in SQL, to
>> know the ip of current master ?
>> The solution I have is to read the recovery.conf file to find
>> primary_conninfo, but, it can be false.
>>
>>
I've run into this as well.  Only way is recovery.conf.

--Scott



> Regards,
>>
>> Bertrand
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>



-- 
--
Scott Mead
Sr. Architect
*OpenSCG *
http://openscg.com

Re: [GENERAL] Replication

[Melvin Davidson]

It's been a few years since I worked with slony, and you did not state
which version of slony or PostgreSQL you are working with, nor did you
indicate the O/S.
That being said, you should be able to formulate a query with a join
between sl_path & sl_node that gives you the information you need.

On Thu, Jun 2, 2016 at 9:32 AM, Bertrand Paquet  wrote:

> Hi,
>
> On an hot standby streaming server, is there any way to know, in SQL, to
> know the ip of current master ?
> The solution I have is to read the recovery.conf file to find
> primary_conninfo, but, it can be false.
>
> Regards,
>
> Bertrand
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Replication Question

[Bala Venkat]

Thanks for the help. We need an upgrade on the DB for the solution. I
checked your suggestion and it works on versions from 9.1 and above

Regards

On Thu, Jan 28, 2016 at 12:04 PM, Andreas Kretschmer <
akretsch...@spamfence.net> wrote:

> Bala Venkat  wrote:
>
> > Hi there -
> >
> >We have a set up where there is One master streaming to 3
> Slaves .
> > 2 slaves are in our DR environment. One is the prod environment.
> >
> >   Wanted to make the DR as primary. I know we can make the one
> of the
> > slave in DR to primary. If I want to keep the other slave as slave
> connecting
> > to the new Master DR , is it possible ? or I have to set the whole
> streaming
> > process again in DR ?  Can you please share your experience ?
>
> should be possible, but you have to change the recovery.conf to point
> out to the new master, and, of course, the new master should stream the
> wals (wal_sender, proper pg_hba.conf and so on).
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect.  (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly."   (unknown)
> Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Replication Question

[Andreas Kretschmer]

Bala Venkat  wrote:

> Hi there -
> 
>    We have a set up where there is One master streaming to 3 Slaves . 
> 2 slaves are in our DR environment. One is the prod environment. 
> 
>   Wanted to make the DR as primary. I know we can make the one of the
> slave in DR to primary. If I want to keep the other slave as slave connecting
> to the new Master DR , is it possible ? or I have to set the whole streaming
> process again in DR ?  Can you please share your experience ?

should be possible, but you have to change the recovery.conf to point
out to the new master, and, of course, the new master should stream the
wals (wal_sender, proper pg_hba.conf and so on).


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication with 9.4

[Thomas Munro]

On Tue, Oct 6, 2015 at 12:27 PM, Thomas Munro  wrote:

> On Sun, Oct 4, 2015 at 11:47 PM, Michael Paquier
>  wrote:
> > (Seems like you forgot to push the Reply-all button)
> >
> > On Sun, Oct 4, 2015 at 7:01 PM, Madovsky wrote:
> >> On 10/3/2015 3:30 PM, Michael Paquier wrote:
> >>>  and no reason is given to justify *why* this would be needed in your
> case
> >> reason for a choice can be often an issue for other :D
> >>
> >> I thought that postgresql 9.4  user could change on the fly with
> >> synchronous_commit from local to on for ex
> >> which hotstandby would become in sync and which in async to avoid a big
> >> latency in case of let's say 100 hot standby.
> >> it was an idea, a concept to let the master write and update the nodes,
> like
> >> a queen bee ;)
> >> but I'm afraid it's not possible, so maybe future version of pg will do
> it,
> >> for now  read from the master is my only solution.
> >
> > Well, Thomas Munro (adding him in CC) has sent for integration with
> > 9.6 a patch that would cover your need, by adding to
> > synchronous_commit a mode called 'apply', in which case a master would
> > wait for the transaction to be applied on standby before committing
> > locally:
> >
> http://www.postgresql.org/message-id/CAEepm=1fqkivl4v-otphwsgw4af9hcogimrcw-ybtjipx9g...@mail.gmail.com
> > Perhaps you could help with the review of the patch, this has stalled
> > a bit lately.
>
> That patch (or something more sophisticated long those lines) is a
> small piece of a bigger puzzle, though it might be enough if you only
> have one standby, are prepared to block until manual intervention if
> that standby fails, and don't mind potentially lumpy apply
> performance.  See also the work being done to separate wal writing
> from wal applying for smoother performance[1], and handle multiple
> synchronous standbys[2].  But there is another piece of the puzzle
> IMHO: how to know reliably that the standby that you are talking to
> guarantees causal consistency, while also allowing standbys to
> fail/drop out gracefully, and I'm currently working on an idea for
> that.
>

FYI I posted the resulting proposal and patch over on the -hackers list.
Feedback, ideas, flames welcome as always.

http://www.postgresql.org/message-id/flat/CAEepm=0n_OxB2_pNntXND6aD85v5PvADeUY8eZjv9CBLk=z...@mail.gmail.com

-- 
Thomas Munro
http://www.enterprisedb.com

Re: [GENERAL] Replication with 9.4

[Thomas Munro]

On Sun, Oct 4, 2015 at 11:47 PM, Michael Paquier
 wrote:
> (Seems like you forgot to push the Reply-all button)
>
> On Sun, Oct 4, 2015 at 7:01 PM, Madovsky wrote:
>> On 10/3/2015 3:30 PM, Michael Paquier wrote:
>>>  and no reason is given to justify *why* this would be needed in your case
>> reason for a choice can be often an issue for other :D
>>
>> I thought that postgresql 9.4  user could change on the fly with
>> synchronous_commit from local to on for ex
>> which hotstandby would become in sync and which in async to avoid a big
>> latency in case of let's say 100 hot standby.
>> it was an idea, a concept to let the master write and update the nodes, like
>> a queen bee ;)
>> but I'm afraid it's not possible, so maybe future version of pg will do it,
>> for now  read from the master is my only solution.
>
> Well, Thomas Munro (adding him in CC) has sent for integration with
> 9.6 a patch that would cover your need, by adding to
> synchronous_commit a mode called 'apply', in which case a master would
> wait for the transaction to be applied on standby before committing
> locally:
> http://www.postgresql.org/message-id/CAEepm=1fqkivl4v-otphwsgw4af9hcogimrcw-ybtjipx9g...@mail.gmail.com
> Perhaps you could help with the review of the patch, this has stalled
> a bit lately.

That patch (or something more sophisticated long those lines) is a
small piece of a bigger puzzle, though it might be enough if you only
have one standby, are prepared to block until manual intervention if
that standby fails, and don't mind potentially lumpy apply
performance.  See also the work being done to separate wal writing
from wal applying for smoother performance[1], and handle multiple
synchronous standbys[2].  But there is another piece of the puzzle
IMHO: how to know reliably that the standby that you are talking to
guarantees causal consistency, while also allowing standbys to
fail/drop out gracefully, and I'm currently working on an idea for
that.

Of course you can make your own causal consistency today if you are
prepared to have your clients explicitly wait for WAL to be applied.
You can call pg_current_xlog_location() on the master after
committing, and then wait until pg_last_xlog_replay_location() reports
that that LSN has been applied on any standby you talk to at the start
of any transaction that wants causal reads.  You could wrap the
waiting up in a user defined function
wait_for_xlog_replay_location(lsn, timeout) which could do a naive
poll/sleep loop (or do something more efficient with latches in core
code).  For example, imagine a client that inserts some new accounts
and then causes a large number of workers to regenerate some reports
that must include the new accounts against a pool of standbys.  It
just needs to give them the LSN they should wait for first.  And if
you don't want to pass LSNs around but don't mind introducing some
extra conservative lag, those workers could call
pg_current_xlog_location() on the master themselves to get some
arbitrary recent LSN and then wait for that to be applied before they
start their work on the standbys.

The explicit wait-for-LSN approach pushes the waiting over to readers
who want causal reads, instead of writer (the master), which still
might be interesting for some cases even if we do finish up with a
good optional master-waits system.  But a master-waits system will
allow naive clients to see up to date data no matter where they run
their queries (or be kicked off by standbys that can't guarantee that)
without having to think about LSNs and replication machinery, and I
think that would be a very useful feature.

[1] 
http://www.postgresql.org/message-id/flat/CA+U5nMJifauXvVbx=v3UbYbHO3Jw2rdT4haL6CCooEDM5=4...@mail.gmail.com
[2] 
http://www.postgresql.org/message-id/flat/caog9aphycpmtypaawfd3_v7svokbnecfivmrc1axhb40zbs...@mail.gmail.com

-- 
Thomas Munro
http://www.enterprisedb.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication with 9.4

[Michael Paquier]

(Seems like you forgot to push the Reply-all button)

On Sun, Oct 4, 2015 at 7:01 PM, Madovsky wrote:
> On 10/3/2015 3:30 PM, Michael Paquier wrote:
>>  and no reason is given to justify *why* this would be needed in your case
> reason for a choice can be often an issue for other :D
>
> I thought that postgresql 9.4  user could change on the fly with
> synchronous_commit from local to on for ex
> which hotstandby would become in sync and which in async to avoid a big
> latency in case of let's say 100 hot standby.
> it was an idea, a concept to let the master write and update the nodes, like
> a queen bee ;)
> but I'm afraid it's not possible, so maybe future version of pg will do it,
> for now  read from the master is my only solution.

Well, Thomas Munro (adding him in CC) has sent for integration with
9.6 a patch that would cover your need, by adding to
synchronous_commit a mode called 'apply', in which case a master would
wait for the transaction to be applied on standby before committing
locally:
http://www.postgresql.org/message-id/CAEepm=1fqkivl4v-otphwsgw4af9hcogimrcw-ybtjipx9g...@mail.gmail.com
Perhaps you could help with the review of the patch, this has stalled
a bit lately.
Regards,
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication with 9.4

[Madovsky]

On 10/4/2015 3:47 AM, Michael Paquier wrote:

(Seems like you forgot to push the Reply-all button)

On Sun, Oct 4, 2015 at 7:01 PM, Madovsky wrote:

On 10/3/2015 3:30 PM, Michael Paquier wrote:

  and no reason is given to justify *why* this would be needed in your case

reason for a choice can be often an issue for other :D

I thought that postgresql 9.4  user could change on the fly with
synchronous_commit from local to on for ex
which hotstandby would become in sync and which in async to avoid a big
latency in case of let's say 100 hot standby.
it was an idea, a concept to let the master write and update the nodes, like
a queen bee ;)
but I'm afraid it's not possible, so maybe future version of pg will do it,
for now  read from the master is my only solution.

Well, Thomas Munro (adding him in CC) has sent for integration with
9.6 a patch that would cover your need, by adding to
synchronous_commit a mode called 'apply', in which case a master would
wait for the transaction to be applied on standby before committing
locally:
http://www.postgresql.org/message-id/CAEepm=1fqkivl4v-otphwsgw4af9hcogimrcw-ybtjipx9g...@mail.gmail.com
Perhaps you could help with the review of the patch, this has stalled
a bit lately.
Regards,

Brilliant, however I'm not to caught everything in this thread.
I would love to contribute to this patch, but I have absolutely no
C/C++ experience (webdev languages only).
to tell simple, when SET LOCAL synchronous_commit TO ON is used on the fly
for the current transaction it would be great to have an other option to 
choose
if this setting is for all standby or a specific one, leaving the other 
async... gotcha? :)







--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication with 9.4

[Michael Paquier]

On Sat, Oct 3, 2015 at 10:20 PM, Madovsky  wrote:
>
>
> On 10/3/2015 4:48 AM, Michael Paquier wrote:
>>
>> On Sat, Oct 3, 2015 at 8:09 PM, Madovsky  wrote:
>>>
>>> I would like to fix a issue I'm facing of with the version 9.4 streaming
>>> replication.
>>> is it possible to set on the fly the synchronous commit on the master (or
>>> standby?)
>>> which only sync commit the hot standby node used by the client who has a
>>> read only sql session on?
>>
>> By referring to the docs:
>>
>> http://www.postgresql.org/docs/devel/static/warm-standby.html#SYNCHRONOUS-REPLICATION
>> Synchronous replication gives the insurance that a transaction has
>> been flushed to the disk of the standby which is in sync, aka the one
>> with the lowest priority depending on the nodes currently connected.
>> This does not ensure that the transaction has been *replayed* on the
>> standby. You are sure that the transaction data is available. Hence if
>> you wish to know that a transaction in a standby is running a
>> transaction with enough data replayed, you should make the WAL
>> position of the master necessary for the transaction of the standby
>> something that your application is aware of.
>
>
> I really well understood Michael thanks,
> the docs doesn't cover if the sync priorities can be changed
> so one node can be considered fully sync and the other only async
> thus to minimize sync request overhead...

The amount of overhead of a node is something that needs to be
evaluated externally of the Postgres backend, then you could always
adjust synchronous_standby_names to change the priorities as you wish.
You can for example do so with libpq or psql using ALTER SYSTEM
combined with "SELECT pg_reload_conf();". The configuration will be be
reloaded at the next query loop in a backup once it catches the
changes of the parameter via SIGHUP.

> usually a client connect to a node would like to see the results
> on the node where  he has a session on.
> I just wanted to avoid a SELECT request to the master and
> stay on the HOT STANDBY for all read requests.
> my script open 2 session, on on the master and one on the hot standby
> in case of block transactions.

Requesting the master would be necessary, still I don't really get why
you don't want to query the master for read queries... You could for
example plug on top of the master pgbouncer if you have many
connections, but well at this stage I have no idea of what is your use
case.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication with 9.4

[Michael Paquier]

On Sat, Oct 3, 2015 at 8:09 PM, Madovsky  wrote:
> I would like to fix a issue I'm facing of with the version 9.4 streaming
> replication.
> is it possible to set on the fly the synchronous commit on the master (or
> standby?)
> which only sync commit the hot standby node used by the client who has a
> read only sql session on?

By referring to the docs:
http://www.postgresql.org/docs/devel/static/warm-standby.html#SYNCHRONOUS-REPLICATION
Synchronous replication gives the insurance that a transaction has
been flushed to the disk of the standby which is in sync, aka the one
with the lowest priority depending on the nodes currently connected.
This does not ensure that the transaction has been *replayed* on the
standby. You are sure that the transaction data is available. Hence if
you wish to know that a transaction in a standby is running a
transaction with enough data replayed, you should make the WAL
position of the master necessary for the transaction of the standby
something that your application is aware of.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication with 9.4

[Edson Richter]

 Madovsky escreveu 

> Hi,
> 
> I would like to fix a issue I'm facing of with the version 9.4 streaming 
> replication.
> is it possible to set on the fly the synchronous commit on the master 
> (or standby?)
> which only sync commit the hot standby node used by the client who has a 
> read only sql session on?
> example:
> node1 node2 node3 are hot standby with replication slots (physical) on 
> master 1
> node1 has the priority 1 since it's first on the names list
> now a client open a ready only session on node3 and start a transaction 
> update 

Sorry for my ignorance, but can you start a "transaction update" over a read 
only connection?

Edson


but
> within this transaction a sql select is done on the hot standby from the 
> updated table
> 
> thanks
> 
> Franck
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication with 9.4

[Madovsky]

Hi

On 10/3/2015 5:46 AM, Edson Richter wrote:


 Madovsky escreveu 

> Hi,
>
> I would like to fix a issue I'm facing of with the version 9.4 
streaming

> replication.
> is it possible to set on the fly the synchronous commit on the master
> (or standby?)
> which only sync commit the hot standby node used by the client who 
has a

> read only sql session on?
> example:
> node1 node2 node3 are hot standby with replication slots (physical) on
> master 1
> node1 has the priority 1 since it's first on the names list
> now a client open a ready only session on node3 and start a transaction
> update

Sorry for my ignorance, but can you start a "transaction update" over 
a *read only connection?*


Edson

but
> within this transaction a sql select is done on the hot standby from 
the

> updated table
>
> thanks
>
> Franck
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org 
)

> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


Obviously not, my script open 2 sessions

Re: [GENERAL] Replication with 9.4

[Madovsky]

On 10/3/2015 4:48 AM, Michael Paquier wrote:

On Sat, Oct 3, 2015 at 8:09 PM, Madovsky  wrote:

I would like to fix a issue I'm facing of with the version 9.4 streaming
replication.
is it possible to set on the fly the synchronous commit on the master (or
standby?)
which only sync commit the hot standby node used by the client who has a
read only sql session on?

By referring to the docs:
http://www.postgresql.org/docs/devel/static/warm-standby.html#SYNCHRONOUS-REPLICATION
Synchronous replication gives the insurance that a transaction has
been flushed to the disk of the standby which is in sync, aka the one
with the lowest priority depending on the nodes currently connected.
This does not ensure that the transaction has been *replayed* on the
standby. You are sure that the transaction data is available. Hence if
you wish to know that a transaction in a standby is running a
transaction with enough data replayed, you should make the WAL
position of the master necessary for the transaction of the standby
something that your application is aware of.


I really well understood Michael thanks,
the docs doesn't cover if the sync priorities can be changed
so one node can be considered fully sync and the other only async
thus to minimize sync request overhead...
usually a client connect to a node would like to see the results
on the node where  he has a session on.
I just wanted to avoid a SELECT request to the master and
stay on the HOT STANDBY for all read requests.
my script open 2 session, on on the master and one on the hot standby
in case of block transactions.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication with 9.4

[Michael Paquier]

On Sun, Oct 4, 2015 at 6:38 AM, Madovsky wrote:
> On 10/3/2015 6:55 AM, Michael Paquier wrote:
>> On Sat, Oct 3, 2015 at 10:20 PM, Madovsky wrote:
>> Requesting the master would be necessary, still I don't really get why
>> you don't want to query the master for read queries... You could for
>> example plug on top of the master pgbouncer if you have many
>> connections, but well at this stage I have no idea of what is your use
>> case.
>
>
> Your idea is interesting, but unfortunately not dynamic and not for a per
> user basis.
> like we can change synchronous_commit on the fly and per block transactions
> so
> why not the same for standby priority?
> I'm trying to use the master for write only.

There can be only one sync standby at a time, and please note again
the difference between WAL flush and WAL replay. Synchonous
replication ensures that the former has been done, not the latter. As
far as this thread goes, it seems to me that you are taking a more
bug-prone approach on your application-side which could be solved by
just querying the master... Another idea would be to add some
meta-data in the schema to check the validity of the things replayed,
like a state-of-data-per-user or a data-version-number-per-user in a
dedicated table, this would avoid having to make the application
WAL-aware, still you would need to make the application aware of this
meta-data in some way, which would surely require to query the master
or update some cache or a file on client side. Still I don't see why
you would need to use this approach, and no reason is given to justify
*why* this would be needed in your case. But well I guess I cannot
stop you to do so if you wish to do it :)
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication with 9.4

[Madovsky]

On 10/3/2015 6:55 AM, Michael Paquier wrote:

On Sat, Oct 3, 2015 at 10:20 PM, Madovsky  wrote:


On 10/3/2015 4:48 AM, Michael Paquier wrote:

On Sat, Oct 3, 2015 at 8:09 PM, Madovsky  wrote:

I would like to fix a issue I'm facing of with the version 9.4 streaming
replication.
is it possible to set on the fly the synchronous commit on the master (or
standby?)
which only sync commit the hot standby node used by the client who has a
read only sql session on?

By referring to the docs:

http://www.postgresql.org/docs/devel/static/warm-standby.html#SYNCHRONOUS-REPLICATION
Synchronous replication gives the insurance that a transaction has
been flushed to the disk of the standby which is in sync, aka the one
with the lowest priority depending on the nodes currently connected.
This does not ensure that the transaction has been *replayed* on the
standby. You are sure that the transaction data is available. Hence if
you wish to know that a transaction in a standby is running a
transaction with enough data replayed, you should make the WAL
position of the master necessary for the transaction of the standby
something that your application is aware of.


I really well understood Michael thanks,
the docs doesn't cover if the sync priorities can be changed
so one node can be considered fully sync and the other only async
thus to minimize sync request overhead...

The amount of overhead of a node is something that needs to be
evaluated externally of the Postgres backend, then you could always
adjust synchronous_standby_names to change the priorities as you wish.
You can for example do so with libpq or psql using ALTER SYSTEM
combined with "SELECT pg_reload_conf();". The configuration will be be
reloaded at the next query loop in a backup once it catches the
changes of the parameter via SIGHUP.


usually a client connect to a node would like to see the results
on the node where  he has a session on.
I just wanted to avoid a SELECT request to the master and
stay on the HOT STANDBY for all read requests.
my script open 2 session, on on the master and one on the hot standby
in case of block transactions.

Requesting the master would be necessary, still I don't really get why
you don't want to query the master for read queries... You could for
example plug on top of the master pgbouncer if you have many
connections, but well at this stage I have no idea of what is your use
case.


Your idea is interesting, but unfortunately not dynamic and not for a 
per user basis.
like we can change synchronous_commit on the fly and per block 
transactions so

why not the same for standby priority?
I'm trying to use the master for write only.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication fell out of sync

[Joshua D. Drake]

On 03/02/2015 03:25 PM, David Kerr wrote:


Howdy,

I had an instance where a replica fell out of sync with the master.

Now it's in in a state where it's unable to catch up because the master has 
already removed the WAL segment.

(logs)
Mar  2 23:10:13 db13 postgres[11099]: [3-1] user=,db=,host= LOG:  streaming 
replication successfully connected to primary
Mar  2 23:10:13 db13 postgres[11099]: [4-1] user=,db=,host= FATAL:  could not 
receive data from WAL stream: FATAL:  requested WAL segment 
0006047C001F has already been removed


I was under the impression that when you setup streaming replication if you 
specify a restore command like : restore_command = 'cp /arch/%f %p'

Then even if the slave falls out of sync, and the master removes the WAL 
segment, as long as you can still retrieve the WAL files, then it can bring 
itself back into sync.


If the archive command is also set so that the restore command has a 
file to retrieve, then yes it will work that way.



--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, @cmdpromptinc

Now I get it: your service is designed for a customer
base that grew up with Facebook, watches Japanese seizure
robot anime, and has the attention span of a gnat.
I'm not that user., Tyler Riddle



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication fell out of sync

[David Kerr]

On Mon, Mar 02, 2015 at 04:06:02PM PDT, Adrian Klaver wrote:
 On 03/02/2015 03:25 PM, David Kerr wrote:
 Howdy,
 
 I had an instance where a replica fell out of sync with the master.
 
 Now it's in in a state where it's unable to catch up because the master has 
 already removed the WAL segment.
 
 (logs)
 Mar  2 23:10:13 db13 postgres[11099]: [3-1] user=,db=,host= LOG:  streaming 
 replication successfully connected to primary
 Mar  2 23:10:13 db13 postgres[11099]: [4-1] user=,db=,host= FATAL:  could 
 not receive data from WAL stream: FATAL:  requested WAL segment 
 0006047C001F has already been removed
 
 
 I was under the impression that when you setup streaming replication if you 
 specify a restore command like : restore_command = 'cp /arch/%f %p'
 
 Then even if the slave falls out of sync, and the master removes the WAL 
 segment, as long as you can still retrieve the WAL files, then it can bring 
 itself back into sync.
 
 
 But that doesn't seem to be happening.
 
 The restore_command is working
 # Slave's $PGDATA/pg_xlog/
 -rw--- 1 postgres postgres 16777216 Mar  2 21:29 0006047C001F
 -rwx-- 1 postgres postgres 16777216 Mar  2 23:13 RECOVERYXLOG
 
 Trying to figure out why the error occurred at Mar  2 23:10:13 and
 the file shows a time stamp of Mar  2 21:29, especially since you
 say the WAL segments flew past?
 
 Are there any other WAL files in the slave ~/pg_xlog?

Turns out just that file had gotten corrupt on copy. When i re-pulled it from 
source life was good.

(phew!)

Thanks all for looking.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication fell out of sync

[Adrian Klaver]

On 03/02/2015 03:25 PM, David Kerr wrote:

Howdy,

I had an instance where a replica fell out of sync with the master.

Now it's in in a state where it's unable to catch up because the master has 
already removed the WAL segment.

(logs)
Mar  2 23:10:13 db13 postgres[11099]: [3-1] user=,db=,host= LOG:  streaming 
replication successfully connected to primary
Mar  2 23:10:13 db13 postgres[11099]: [4-1] user=,db=,host= FATAL:  could not 
receive data from WAL stream: FATAL:  requested WAL segment 
0006047C001F has already been removed


I was under the impression that when you setup streaming replication if you 
specify a restore command like : restore_command = 'cp /arch/%f %p'

Then even if the slave falls out of sync, and the master removes the WAL 
segment, as long as you can still retrieve the WAL files, then it can bring 
itself back into sync.


But that doesn't seem to be happening.

The restore_command is working
# Slave's $PGDATA/pg_xlog/
-rw--- 1 postgres postgres 16777216 Mar  2 21:29 0006047C001F
-rwx-- 1 postgres postgres 16777216 Mar  2 23:13 RECOVERYXLOG


Trying to figure out why the error occurred at Mar  2 23:10:13 and the 
file shows a time stamp of Mar  2 21:29, especially since you say the 
WAL segments flew past?


Are there any other WAL files in the slave ~/pg_xlog?



I'm on PG 9.2.7, which i know is old, but I'm upgrading shortly.

recovery.conf:
standby_mode  = 'on'
primary_conninfo  = 'host=pgmaster port=5432'
restore_command   = 'cp /arch/%f %p'

relevant info from postgresql.conf:
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
archive_mode = on
hot_standby = on
hot_standby_feedback = true


I know that to avoid this entirely I need to set wal_keep_segments higher, 
although in this particular case it wouldn't have mattered because a rogue 
program slammed the database and basically 32/64/128 WAL segments went by in a 
short span of time.

However, I really thought that as long as PG could get the archived logs i'd be 
able to recover.

Was I wrong with that assertion or did i just run into a bug?

Thanks





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication fell out of sync

[David Kerr]

On Mon, Mar 02, 2015 at 03:33:22PM PDT, Joshua D. Drake wrote:
 
 On 03/02/2015 03:25 PM, David Kerr wrote:
 
 Howdy,
 
 I had an instance where a replica fell out of sync with the master.
 
 Now it's in in a state where it's unable to catch up because the master has 
 already removed the WAL segment.
 
 (logs)
 Mar  2 23:10:13 db13 postgres[11099]: [3-1] user=,db=,host= LOG:  streaming 
 replication successfully connected to primary
 Mar  2 23:10:13 db13 postgres[11099]: [4-1] user=,db=,host= FATAL:  could 
 not receive data from WAL stream: FATAL:  requested WAL segment 
 0006047C001F has already been removed
 
 
 I was under the impression that when you setup streaming replication if you 
 specify a restore command like : restore_command = 'cp /arch/%f %p'
 
 Then even if the slave falls out of sync, and the master removes the WAL 
 segment, as long as you can still retrieve the WAL files, then it can bring 
 itself back into sync.
 
 If the archive command is also set so that the restore command has a
 file to retrieve, then yes it will work that way.

Yeah it is, it's actually pulling the file down. 

Glad that's how it's supposed to work. I'd rather be unlucky then crazy.  =)



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication: How to query current segments allocation relative to Wal keep segments?

[Edson Carlos Ericksson Richter]

On 05-01-2015 02:08, Michael Paquier wrote:

On Sun, Jan 4, 2015 at 1:48 AM, Edson Carlos Ericksson Richter
edsonrich...@hotmail.com wrote:

How to  query current segments allocation relative to Wal keep segments in
each master server?

What is your server version? You can have a look at
pg_stat_replication on the master which contains information about the
WAL segments written, flushed and replayed on each slave:
http://www.postgresql.org/docs/devel/static/monitoring-stats.html#PG-STAT-REPLICATION-VIEW


In production servers, 9.3.5.
In development servers, 9.4.0.

I had a quick look at this view before:

Example (on 9.3.5):
postgres=# select * from pg_stat_replication;
 pid  | usesysid | usename  | application_name | client_addr | 
client_hostname | client_port |backend_start | state   | 
sent_location | write_location

 | flush_location | replay_location | sync_priority | sync_state
--+--+--+--+-+-+-+--+---+---+---
-++-+---+
 2808 |   10 | postgres | walreceiver  | 10.68.73.1 
| |   36075 | 2015-01-03 20:17:48.53706-02 | 
streaming | 22/F94D1A90   | 22/F94D1A90

 | 22/F94D1A90| 22/F94D1A90 | 0 | async
(1 registro)

but in either case (9.3.5, 9.4.0), I get lots of info, but not the count 
of wal_segments consumed.

Would this kind of count being recorded somewhere else?
How does the server knows that the wal_segments have been exhausted?

Thanks,

Edson




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication: How to query current segments allocation relative to Wal keep segments?

[Michael Paquier]

On Mon, Jan 5, 2015 at 6:51 PM, Edson Carlos Ericksson Richter
edsonrich...@hotmail.com wrote:
 Would this kind of count being recorded somewhere else?
 How does the server knows that the wal_segments have been exhausted?
You should evaluate the amount of wal_keep_segments necessary using
the replication lag in terms of WAL position differences (LSN) between
the master and its slaves. pg_stat_replication gives you the WAL
position (LSN) up to where each slave has received WAL information.
Combine it with pg_current_xlog_location() to determine what is the
current location master is writing WAL and you can evaluate the number
of WAL files that need to be retained on master. Knowing that each WAL
file is normally 16MB, simply use pg_xlog_location_diff to calculate
the WAL lag as a difference of bytes (for 9.4 a simple difference
operation is possible with the data type pg_lsn), and then guess from
it the number of WAL files that are actually necessary.

If you care that much about WAL retention btw, consider using
replication slots with 9.4, just be careful to monitor the partition
where pg_xlog sits in.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication: How to query current segments allocation relative to Wal keep segments?

[Edson Richter]

On 05-01-2015 10:02, Michael Paquier wrote:

On Mon, Jan 5, 2015 at 6:51 PM, Edson Carlos Ericksson Richter
edsonrich...@hotmail.com wrote:

Would this kind of count being recorded somewhere else?
How does the server knows that the wal_segments have been exhausted?

You should evaluate the amount of wal_keep_segments necessary using
the replication lag in terms of WAL position differences (LSN) between
the master and its slaves. pg_stat_replication gives you the WAL
position (LSN) up to where each slave has received WAL information.
Combine it with pg_current_xlog_location() to determine what is the
current location master is writing WAL and you can evaluate the number
of WAL files that need to be retained on master. Knowing that each WAL
file is normally 16MB, simply use pg_xlog_location_diff to calculate
the WAL lag as a difference of bytes (for 9.4 a simple difference
operation is possible with the data type pg_lsn), and then guess from
it the number of WAL files that are actually necessary.

If you care that much about WAL retention btw, consider using
replication slots with 9.4, just be careful to monitor the partition
where pg_xlog sits in.
Despite being a completely valid statement, I've two contrary thoughts 
about it:


1) I cannot migrate production servers at my free will. It requires long 
planning, and probably will happen only in one or two years from now 
(year end 2015 or 2016)
2) I do prefer to monitor how much wal segments I'm really using (and in 
need), and then fix them up to 25% above this limit, than giving a 
chance to blow my disk space (I mean, is preferable to stop replication 
than put in risk whole database because of disk space)


Thanks,

Edson



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication: How to query current segments allocation relative to Wal keep segments?

[Michael Paquier]

On Sun, Jan 4, 2015 at 1:48 AM, Edson Carlos Ericksson Richter
edsonrich...@hotmail.com wrote:
 How to  query current segments allocation relative to Wal keep segments in
 each master server?
What is your server version? You can have a look at
pg_stat_replication on the master which contains information about the
WAL segments written, flushed and replayed on each slave:
http://www.postgresql.org/docs/devel/static/monitoring-stats.html#PG-STAT-REPLICATION-VIEW
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] replication timeout in pg_basebackup

[Aggarwal, Ajay]

Thats exactly what I was thinking after all other experiments. Couple of 
questions:
1) why did you say that 300 seconds is the upper limit? Is this enforced by 
Postgres? What if I want to set it to 10 minutes?
2) whats the downside of bigger replication timeout?

Thanks.

Ajay

From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] 
on behalf of John R Pierce [pie...@hogranch.com]
Sent: Monday, March 10, 2014 9:58 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] replication timeout in pg_basebackup

On 3/9/2014 6:52 PM, Aggarwal, Ajay wrote:
Our replication timeout is default 60 seconds. If we increase the replication 
time to say 180 seconds, we see better results but backups still fail 
occasionally.

so increase it to 300 seconds, or whatever.   thats an upper limit, it needs to 
be big enough that you DONT get into problems when doing stuff like basebackups.






--
john r pierce  37N 122W
somewhere on the middle of the left coast

Re: [GENERAL] replication timeout in pg_basebackup

[John R Pierce]

On 3/11/2014 5:50 AM, Aggarwal, Ajay wrote:
Thats exactly what I was thinking after all other experiments. Couple 
of questions:
1) why did you say that 300 seconds is the upper limit? Is this 
enforced by Postgres? What if I want to set it to 10 minutes?

2) whats the downside of bigger replication timeout?



I said, set it to 300 or whatever.   An unfortunate extra comma confused 
my meaning.  What I meant was, whatever you set it to, thats a upper limit.


As I understand it, that timeout is how long the replication can lag the 
server before the server decides to stop replication.   with it at 300, 
under heavy load, the replication could run as much as 5 minutes (300 
seconds) behind before it errors.




--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] replication timeout in pg_basebackup

[Aggarwal, Ajay]

Thanks Hari Babu.

I think what is happening is that my dirty cache builds up quickly for the 
volume where I am backing up. This would trigger flush of these dirty pages to 
the disk. While this flush is going on pg_basebackup tries to do fsync() on a 
received WAL file and gets blocked.

While in this state, i.e. when dirty page count is high, following are the 
results of pg_test_fsync


# /usr/pgsql-9.2/bin/pg_test_fsync -f /backup/fsync_test
2 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
open_datasync  16.854 ops/sec
fdatasync  15.242 ops/sec
fsync   0.187 ops/sec
fsync_writethroughn/a
open_sync  14.747 ops/sec

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
open_datasync   6.137 ops/sec
fdatasync  14.899 ops/sec
fsync   0.007 ops/sec
fsync_writethroughn/a
open_sync   1.450 ops/sec

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB
in different write open_sync sizes.)
 1 * 16kB open_sync write  13.486 ops/sec
 2 *  8kB open_sync writes  6.006 ops/sec
 4 *  4kB open_sync writes  3.446 ops/sec
 8 *  2kB open_sync writes  1.400 ops/sec
16 *  1kB open_sync writes  0.859 ops/sec

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
write, fsync, close 0.009 ops/sec
write, close, fsync 0.008 ops/sec

Non-Sync'ed 8kB writes:
write   99415.368 ops/sec


However when backups are not going on and dirty pages count is low, below are 
the results of this test

# /usr/pgsql-9.2/bin/pg_test_fsync -f /backup/fsync_test
2 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
open_datasync1974.243 ops/sec
fdatasync1410.804 ops/sec
fsync 181.129 ops/sec
fsync_writethroughn/a
open_sync 547.389 ops/sec

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
open_datasync 290.109 ops/sec
fdatasync 962.378 ops/sec
fsync 158.987 ops/sec
fsync_writethroughn/a
open_sync 642.309 ops/sec

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB
in different write open_sync sizes.)
 1 * 16kB open_sync write1014.456 ops/sec
 2 *  8kB open_sync writes627.964 ops/sec
 4 *  4kB open_sync writes340.313 ops/sec
 8 *  2kB open_sync writes173.581 ops/sec
16 *  1kB open_sync writes103.236 ops/sec

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written
on a different descriptor.)
write, fsync, close   244.670 ops/sec
write, close, fsync   207.248 ops/sec

Non-Sync'ed 8kB writes:
write   202216.900 ops/sec



From: Haribabu Kommi [kommi.harib...@gmail.com]
Sent: Monday, March 10, 2014 1:42 AM
To: Aggarwal, Ajay
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] replication timeout in pg_basebackup

On Mon, Mar 10, 2014 at 12:52 PM, Aggarwal, Ajay 
aaggar...@verizon.commailto:aaggar...@verizon.com wrote:
Our environment: Postgres version 9.2.2 running on CentOS 6.4

Our backups using pg_basebackup are frequently failing with following error

pg_basebackup: could not send feedback packet: server closed the connection 
unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

We are invoking pg_basebackup with these arguments : pg_basebackup -D 
backup_dir -X stream -l backup_dir

In postgres logs we see this log message terminating walsender process due to 
replication timeout.

Our replication timeout is default 60 seconds. If we increase the replication 
time to say 180 seconds, we see better results

Re: [GENERAL] replication timeout in pg_basebackup

[Haribabu Kommi]

On Tue, Mar 11, 2014 at 7:07 AM, Aggarwal, Ajay aaggar...@verizon.com wrote:
 Thanks Hari Babu.

 I think what is happening is that my dirty cache builds up quickly for the
 volume where I am backing up. This would trigger flush of these dirty pages
 to the disk. While this flush is going on pg_basebackup tries to do fsync()
 on a received WAL file and gets blocked.

But the sync is executed for every WAL file finish. Does your database
is big in size?
Does your setup is write-heavy operations?

In Linux when it tries to write a bunch of buffers at once, the fysnc
call might block for some time.
In the following link there are some Tuning Recommendations for
write-heavy operations which might be useful to you.

http://www.westnet.com/~gsmith/content/linux-pdflush.htm

Any other ideas to handle these kind of problems?

Regards,
Hari Babu
Fujitsu Australia


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] replication timeout in pg_basebackup

[Aggarwal, Ajay]

I have already tried experimenting with linux dirty_ratio etc. You can only 
fine tune up to a limit. The backup process still fills up the buffer cache 
very quickly. Yes, my database is about 5-6 GB in size and will grow bigger 
over time.

If wish there was a way to slow down pg_basebackup or force it to use direct 
I/O.

From: Haribabu Kommi [kommi.harib...@gmail.com]
Sent: Monday, March 10, 2014 8:31 PM
To: Aggarwal, Ajay
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] replication timeout in pg_basebackup

On Tue, Mar 11, 2014 at 7:07 AM, Aggarwal, Ajay aaggar...@verizon.com wrote:
 Thanks Hari Babu.

 I think what is happening is that my dirty cache builds up quickly for the
 volume where I am backing up. This would trigger flush of these dirty pages
 to the disk. While this flush is going on pg_basebackup tries to do fsync()
 on a received WAL file and gets blocked.

But the sync is executed for every WAL file finish. Does your database
is big in size?
Does your setup is write-heavy operations?

In Linux when it tries to write a bunch of buffers at once, the fysnc
call might block for some time.
In the following link there are some Tuning Recommendations for
write-heavy operations which might be useful to you.

http://www.westnet.com/~gsmith/content/linux-pdflush.htm

Any other ideas to handle these kind of problems?

Regards,
Hari Babu
Fujitsu Australia


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] replication timeout in pg_basebackup

[John R Pierce]

On 3/9/2014 6:52 PM, Aggarwal, Ajay wrote:
Our replication timeout is default 60 seconds. If we increase the 
replication time to say 180 seconds, we see better results but backups 
still fail occasionally.


so increase it to 300 seconds, or whatever.   thats an upper limit, it 
needs to be big enough that you DONT get into problems when doing stuff 
like basebackups.






--
john r pierce  37N 122W
somewhere on the middle of the left coast

Re: [GENERAL] replication timeout in pg_basebackup

[Haribabu Kommi]

On Mon, Mar 10, 2014 at 12:52 PM, Aggarwal, Ajay aaggar...@verizon.comwrote:

  Our environment: Postgres version 9.2.2 running on CentOS 6.4

 Our backups using pg_basebackup are frequently failing with following error

 pg_basebackup: could not send feedback packet: server closed the connection 
 unexpectedly
 This probably means the server terminated abnormally
 before or while processing the request.

 We are invoking pg_basebackup with these arguments : pg_basebackup -D 
 backup_dir -X stream -l backup_dir

 In postgres logs we see this log message terminating walsender process
 due to replication timeout.

 Our replication timeout is default 60 seconds. If we increase the
 replication time to say 180 seconds, we see better results but backups
 still fail occasionally.

 Running strace on pg_basebackup process, we see that the fsync() call
 takes significant time and could be responsible for causing this timeout in
 postgres.


Use the pg_test_fsync utility which is available in postgresql contrib
module to test your system sync methods performance.


 Has anybody else run into the same issue? Is there a way to run
 pg_basebackup without fsync() ?


As of now there is no such options available, I feel it is better to find
why the sync is taking time?

Regards,
Hari Babu
Fujitsu Australia

Re: [GENERAL] Replication failed after stalling

[Albe Laurenz]

Sergey Konoplev wrote:
 On Mon, Dec 30, 2013 at 12:27 AM, Albe Laurenz laurenz.a...@wien.gv.at 
 wrote:
 Joe Van Dyk wrote:
 If I run COPY (select * from complicate_view) to stdout on the standby, 
 I've noticed that sometimes
 halts replication updates to the slave.

 For example, that's happening right now and now() - 
 pg_last_xact_replay_timestamp() is 22 minutes.
 There's many transactions per second being committed on the master. Once 
 that query is canceled, the
 slave catches up immediately.

 You have hot_standby_feedback = on, right?

 In that case that is expected behaviour.
 Some change on the master conflicted with the query on the standby,
 perhaps with a tuple cleaned up after a HOT update.  Replication will
 stall until the query is done.
 
 IIRC, the applying process is paused but the receiving one is going on
 in this case, isn't it?

Frankly, I don't know.

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

Re: [GENERAL] Replication failed after stalling

[Albe Laurenz]

Joe Van Dyk wrote:
 If I run COPY (select * from complicate_view) to stdout on the standby, 
 I've noticed that sometimes
 halts replication updates to the slave.
 
 For example, that's happening right now and now() - 
 pg_last_xact_replay_timestamp() is 22 minutes.
 There's many transactions per second being committed on the master. Once that 
 query is canceled, the
 slave catches up immediately.

You have hot_standby_feedback = on, right?

In that case that is expected behaviour.
Some change on the master conflicted with the query on the standby,
perhaps with a tuple cleaned up after a HOT update.  Replication will
stall until the query is done.

If that is unacceptable to you and you would rather have queries
canceled on the standby if they take too long, set hot_standby_feedback
back to off and increase vacuum_defer_cleanup_age.

But it is not possible to have a predictable maximum replication lag
and arbitrarily long running queries on the standby at the same time.

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

Re: [GENERAL] Replication failed after stalling

[Sergey Konoplev]

On Mon, Dec 30, 2013 at 12:27 AM, Albe Laurenz laurenz.a...@wien.gv.at wrote:
 Joe Van Dyk wrote:
 If I run COPY (select * from complicate_view) to stdout on the standby, 
 I've noticed that sometimes
 halts replication updates to the slave.

 For example, that's happening right now and now() - 
 pg_last_xact_replay_timestamp() is 22 minutes.
 There's many transactions per second being committed on the master. Once 
 that query is canceled, the
 slave catches up immediately.

 You have hot_standby_feedback = on, right?

 In that case that is expected behaviour.
 Some change on the master conflicted with the query on the standby,
 perhaps with a tuple cleaned up after a HOT update.  Replication will
 stall until the query is done.

IIRC, the applying process is paused but the receiving one is going on
in this case, isn't it?

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray...@gmail.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication: GZIP compression in WAL sender/receiver processes communication?

[Sergey Konoplev]

On Mon, Dec 9, 2013 at 3:13 PM, Dmitry Koterov dmi...@koterov.ru wrote:
 Is there a way to compress the traffic between master and slave during the
 replication?.. The streaming gzip would be quite efficient for that.

Take a look at the ssh_tunnel.sh [1] tool. This is a wrapper around
SSH tunnel with compression, a watchdog and lock management. Very
useful for cross data center streaming.

[1] 
https://github.com/grayhemp/pgcookbook/blob/master/ssh_tunnel_with_compression_setup.md

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray...@gmail.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication failed after stalling

[Joe Van Dyk]

On Mon, Dec 30, 2013 at 10:27 AM, Sergey Konoplev gray...@gmail.com wrote:

 On Mon, Dec 30, 2013 at 12:02 AM, Joe Van Dyk j...@tanga.com wrote:
  On Sun, Dec 29, 2013 at 10:52 PM, Sergey Konoplev gray...@gmail.com
 wrote:
  On Sun, Dec 29, 2013 at 9:56 PM, Joe Van Dyk j...@tanga.com wrote:
   On Wed, Dec 18, 2013 at 3:39 PM, Sergey Konoplev gray...@gmail.com
   wrote:
   If I run COPY (select * from complicate_view) to stdout on the
   standby,
   I've noticed that sometimes halts replication updates to the slave.
  
   For example, that's happening right now and now() -
   pg_last_xact_replay_timestamp() is 22 minutes. There's many
   transactions
   per second being committed on the master. Once that query is canceled,
   the
   slave catches up immediately.
 
  And what
 
  \x
  select * from pg_stat_repication;
 
  shows?
 
  on the master, right?

 Yes.

 And it would be very useful to take a look at your checkpoints and
 replication configuration parameters on both master and replica.


master and replica have same settings.

checkpoint_completion_target: 0.9
checkpoint_segments: 16
checkpoint_timeout: 5m
checkpoint_warning: 30s
hot_standby: on
hot_standby_feedback: on

pid  | 10736
usesysid | 10
usename  | postgres
application_name | walreceiver
client_addr  | the ip
client_hostname  |
client_port  | 47124
backend_start| 2013-12-30 12:08:42.967868-08
state| streaming
sent_location| 410/BC152000
write_location   | 410/BC152000
flush_location   | 410/BC152000
replay_location  | 410/A758B7D0
sync_priority| 0
sync_state   | async

Re: [GENERAL] Replication failed after stalling

[Sergey Konoplev]

On Mon, Dec 30, 2013 at 8:56 PM, Joe Van Dyk j...@tanga.com wrote:
 On Mon, Dec 30, 2013 at 10:27 AM, Sergey Konoplev gray...@gmail.com wrote:
 On Mon, Dec 30, 2013 at 12:02 AM, Joe Van Dyk j...@tanga.com wrote:
  On Sun, Dec 29, 2013 at 10:52 PM, Sergey Konoplev gray...@gmail.com
  wrote:
  On Sun, Dec 29, 2013 at 9:56 PM, Joe Van Dyk j...@tanga.com wrote:
   On Wed, Dec 18, 2013 at 3:39 PM, Sergey Konoplev gray...@gmail.com
   wrote:
   If I run COPY (select * from complicate_view) to stdout on the
   standby,
   I've noticed that sometimes halts replication updates to the slave.
 
  \x
  select * from pg_stat_repication;

 And it would be very useful to take a look at your checkpoints and
 replication configuration parameters on both master and replica.

 master and replica have same settings.

 checkpoint_completion_target: 0.9
 checkpoint_segments: 16
 checkpoint_timeout: 5m
 checkpoint_warning: 30s
 hot_standby: on
 hot_standby_feedback: on

I meant all the replication settings, see [1]. And pg_stat_statements
when there is a problem, preferable the error, because when everything
is okay it is not very useful actually.

[1] http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray...@gmail.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication failed after stalling

[Scott Marlowe]

On Wed, Dec 18, 2013 at 1:51 PM, Adrian Klaver adrian.kla...@gmail.com wrote:
 On 12/18/2013 12:15 PM, Joe Van Dyk wrote:

 A possibly related question:

 I've set wal_keep_segments to 10,000 and also have archive_command
 running wal-e. I'm seeing my wal files disappear from pg_xlog after 30
 minutes. Is that expected? Is there a way around that?


 Well a WAL segment is 16MB in size so that should give you a basis for
 determining whether the above is appropriate, my guess it is not. I do not
 know enough about Wal-e, but my guess is it is siphoning off WAL segments
 before you want it to.

Don't some operations like forced checkpoints etc skip to the next WAL
resulting in them not necessarily being full?


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication failed after stalling

[Joe Van Dyk]

On Mon, Dec 30, 2013 at 9:11 PM, Sergey Konoplev gray...@gmail.com wrote:

 On Mon, Dec 30, 2013 at 8:56 PM, Joe Van Dyk j...@tanga.com wrote:
  On Mon, Dec 30, 2013 at 10:27 AM, Sergey Konoplev gray...@gmail.com
 wrote:
  On Mon, Dec 30, 2013 at 12:02 AM, Joe Van Dyk j...@tanga.com wrote:
   On Sun, Dec 29, 2013 at 10:52 PM, Sergey Konoplev gray...@gmail.com
   wrote:
   On Sun, Dec 29, 2013 at 9:56 PM, Joe Van Dyk j...@tanga.com wrote:
On Wed, Dec 18, 2013 at 3:39 PM, Sergey Konoplev 
 gray...@gmail.com
wrote:
If I run COPY (select * from complicate_view) to stdout on the
standby,
I've noticed that sometimes halts replication updates to the slave.
  
   \x
   select * from pg_stat_repication;
 
  And it would be very useful to take a look at your checkpoints and
  replication configuration parameters on both master and replica.
 
  master and replica have same settings.
 
  checkpoint_completion_target: 0.9
  checkpoint_segments: 16
  checkpoint_timeout: 5m
  checkpoint_warning: 30s
  hot_standby: on
  hot_standby_feedback: on

 I meant all the replication settings, see [1]. And pg_stat_statements
 when there is a problem, preferable the error, because when everything
 is okay it is not very useful actually.


I don't understand, how is pg_stat_statements helpful here, and what error?


 [1]
 http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html


max_wal_senders: 5
wal_keep_segments: 1
wal_sender_timeout: 1m
synchronous_standby_names: n/a
vacuum_defer_cleanup_age: 0
max_standby_archive_delay: 30s
max_standby_streaming_delay: -1
wal_receiver_status_interval: 10s
hot_standby_feedback: on
wal_receiver_timeout: 1m

Re: [GENERAL] Replication failed after stalling

[Sergey Konoplev]

On Mon, Dec 30, 2013 at 10:05 PM, Joe Van Dyk j...@tanga.com wrote:
 I meant all the replication settings, see [1]. And pg_stat_statements
 when there is a problem, preferable the error, because when everything
 is okay it is not very useful actually.

 I don't understand, how is pg_stat_statements helpful here, and what error?

The error you showed in the initial email.

My guess is that the master might stop sending WAL records to the
replica, that is why I wanted to check the stat_replication query. Oh,
yes, and I forget to put current_xlog_location in the query. So, the
correct one is below.

\x
select pg_current_xlog_location(), * from pg_stat_replication;

 checkpoint_completion_target: 0.9
 checkpoint_segments: 16
 checkpoint_timeout: 5m
 checkpoint_warning: 30s
[...]
 max_wal_senders: 5
 wal_keep_segments: 1
 vacuum_defer_cleanup_age: 0
 max_standby_archive_delay: 30s
 max_standby_streaming_delay: -1
 wal_receiver_status_interval: 10s
 hot_standby_feedback: on
[...]

That 1 looks weird and I would increase checkpoint_segments and
checkpoint_timeout, but first let us check how often checkpoints and
checkpoint warnings happen on master. You can see it in logs. Turn
log_checkpoints on if it is off.

And also how many WAL your system generates and for what period.

ls -lt /path/to/pg_xlog/ | wc -l
ls -lt /path/to/pg_xlog/ | head
ls -lt /path/to/pg_xlog/ | tail

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray...@gmail.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication failed after stalling

[Joe Van Dyk]

On Wed, Dec 18, 2013 at 3:39 PM, Sergey Konoplev gray...@gmail.com wrote:

 On Wed, Dec 18, 2013 at 11:26 AM, Joe Van Dyk j...@tanga.com wrote:
  I'm running Postgresql 9.3. I have a streaming replication server.
 Someone
  was running a long COPY query (8 hours) on the standby which halted
  replication. The replication stopped at 3:30 am. I canceled the
 long-running
  query at 9:30 am and replication data started catching up.

 What do you mean by COPY on the standby halted replication?


If I run COPY (select * from complicate_view) to stdout on the standby,
I've noticed that sometimes halts replication updates to the slave.

For example, that's happening right now and now() -
pg_last_xact_replay_timestamp() is 22 minutes. There's many transactions
per second being committed on the master. Once that query is canceled, the
slave catches up immediately.

Joe

Re: [GENERAL] Replication failed after stalling

[Sergey Konoplev]

On Sun, Dec 29, 2013 at 9:56 PM, Joe Van Dyk j...@tanga.com wrote:
 On Wed, Dec 18, 2013 at 3:39 PM, Sergey Konoplev gray...@gmail.com wrote:

 On Wed, Dec 18, 2013 at 11:26 AM, Joe Van Dyk j...@tanga.com wrote:
  I'm running Postgresql 9.3. I have a streaming replication server.
  Someone
  was running a long COPY query (8 hours) on the standby which halted
  replication. The replication stopped at 3:30 am. I canceled the
  long-running
  query at 9:30 am and replication data started catching up.

 What do you mean by COPY on the standby halted replication?

 If I run COPY (select * from complicate_view) to stdout on the standby,
 I've noticed that sometimes halts replication updates to the slave.

 For example, that's happening right now and now() -
 pg_last_xact_replay_timestamp() is 22 minutes. There's many transactions
 per second being committed on the master. Once that query is canceled, the
 slave catches up immediately.

And what

\x
select * from pg_stat_repication;

shows?

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray...@gmail.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication: GZIP compression in WAL sender/receiver processes communication?

[Rahila Syed]

Hello,

Yes gzip compression can be used for compressing WAL traffic during
streaming replication  Following tools can be used in this regard.
SSL compression-SSL support is built in PostgreSQL. You need to ensure you
have OpenSSL library support in your PostgreSQL installation.
Also, you can compress WAL traffic by setting up SSH tunneling between
master and standby and turn on compression while setting up SSH tunnel.
Following link can be followed for the same.
http://www.postgresql.org/docs/9.3/static/ssh-tunnels.html
Ofcourse, these are the solutions outside PostgreSQL.


On Tue, Dec 10, 2013 at 4:43 AM, Dmitry Koterov dmi...@koterov.ru wrote:

 Hello.

 Is there a way to compress the traffic between master and slave during the
 replication?.. The streaming gzip would be quite efficient for that.

 (WAL archiving is not too good for this purpose because of high lag. I
 just need to minimize the cross-datacenter traffic keeping the replication
 lag low.)

Re: [GENERAL] Replication failed after stalling

[Joe Van Dyk]

A possibly related question:

I've set wal_keep_segments to 10,000 and also have archive_command running
wal-e. I'm seeing my wal files disappear from pg_xlog after 30 minutes. Is
that expected? Is there a way around that?

(I want to use streaming replication and wal-e for PITR restores)


On Wed, Dec 18, 2013 at 11:26 AM, Joe Van Dyk j...@tanga.com wrote:

 I'm running Postgresql 9.3. I have a streaming replication server. Someone
 was running a long COPY query (8 hours) on the standby which halted
 replication. The replication stopped at 3:30 am. I canceled the
 long-running query at 9:30 am and replication data started catching up.

 The data up until 10 am got restored fine (took until 10:30 am to restore
 that much). Then I started getting errors like FATAL:  could not receive
 data from WAL stream: ERROR:  requested WAL segment
 000103C30086 has already been removed.

 I'm confused about how pg could restore data from 3:30 am to 10 am, then
 start complaining about missing WAL files.

 What's the best way to avoid this problem? Increase wal_keep_segments?

 Joe

Re: [GENERAL] Replication failed after stalling

[Jerry Sievers]

Joe Van Dyk j...@tanga.com writes:

 I'm running Postgresql 9.3. I have a streaming replication server. Someone 
 was running a long COPY query (8 hours) on the standby which halted 
 replication. The
 replication stopped at 3:30 am. I canceled the long-running query at 9:30 am 
 and replication data started catching up.

 The data up until 10 am got restored fine (took until 10:30 am to restore 
 that much). Then I started getting errors like FATAL:  could not receive 
 data from WAL
 stream: ERROR:  requested WAL segment 000103C30086 has already 
 been removed.

 I'm confused about how pg could restore data from 3:30 am to 10 am, then 
 start complaining about missing WAL files.

 What's the best way to avoid this problem? Increase wal_keep_segments?

Yes and/or implement as a hybrid of streaming and WAL shipping.

Quite simply, your wal_keep segments was almost enough to  get you
through that backlog period but as your standby was catching up, it hit
a point  where  there was a gap.

Depending on how much traffic your master sees at various times of the
day, it's unsurprising that during peak loads, your grace-period is a
lot lower than during off-peak times due to variations in how quickly
WAL segments are filled and cycled over. 

HTH


 Joe


-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication failed after stalling

[Adrian Klaver]

On 12/18/2013 12:15 PM, Joe Van Dyk wrote:

A possibly related question:

I've set wal_keep_segments to 10,000 and also have archive_command
running wal-e. I'm seeing my wal files disappear from pg_xlog after 30
minutes. Is that expected? Is there a way around that?


Well a WAL segment is 16MB in size so that should give you a basis for 
determining whether the above is appropriate, my guess it is not. I do 
not know enough about Wal-e, but my guess is it is siphoning off WAL 
segments before you want it to.





--
Adrian Klaver
adrian.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication failed after stalling

[Sergey Konoplev]

On Wed, Dec 18, 2013 at 11:26 AM, Joe Van Dyk j...@tanga.com wrote:
 I'm running Postgresql 9.3. I have a streaming replication server. Someone
 was running a long COPY query (8 hours) on the standby which halted
 replication. The replication stopped at 3:30 am. I canceled the long-running
 query at 9:30 am and replication data started catching up.

What do you mean by COPY on the standby halted replication?

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray...@gmail.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication: GZIP compression in WAL sender/receiver processes communication?

[Rahila Syed]

Hello,

Yes, gzip compression can be used for compressing WAL traffic during
streaming replication  Following tools can be used in this regard.
SSL compression-SSL support is built in PostgreSQL. You need to ensure
you have OpenSSL library support in your PostgreSQL installation.
Also, you can compress WAL traffic by setting up SSH tunneling between
master and standby and turn on compression while setting up SSH
tunnel. Following link can be followed for the same.
http://www.postgresql.org/docs/9.3/static/ssh-tunnels.html
Ofcourse, these are the solutions outside PostgreSQL.


On Tue, Dec 10, 2013 at 4:43 AM, Dmitry Koterov dmi...@koterov.ru wrote:

 Hello.

 Is there a way to compress the traffic between master and slave during the 
 replication?.. The streaming gzip would be quite efficient for that.

 (WAL archiving is not too good for this purpose because of high lag. I just 
 need to minimize the cross-datacenter traffic keeping the replication lag 
 low.)


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication: GZIP compression in WAL sender/receiver processes communication?

[Michael Paquier]

On Tue, Dec 10, 2013 at 8:13 AM, Dmitry Koterov dmi...@koterov.ru wrote:
 Hello.

 Is there a way to compress the traffic between master and slave during the
 replication?.. The streaming gzip would be quite efficient for that.

 (WAL archiving is not too good for this purpose because of high lag. I just
 need to minimize the cross-datacenter traffic keeping the replication lag
 low.)
Not directly AFAIK, but this would be a good argument for the
implementation of hooks in walsender and walreceiver that could allow
to use such customization of the stream sent and received.

Note that there is also a patch pending for 9.4 that would make
possible the compression of full page writes, reducing globally the
amount of WAL produced by server in exchange of some CPU work to
compress and decompress the data.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication: GZIP compression in WAL sender/receiver processes communication?

[John R Pierce]

On 12/9/2013 3:13 PM, Dmitry Koterov wrote:
Is there a way to compress the traffic between master and slave during 
the replication?.. The streaming gzip would be quite efficient for that.


(WAL archiving is not too good for this purpose because of high lag. I 
just need to minimize the cross-datacenter traffic keeping the 
replication lag low.)


run it through a ssh tunnel that has compression turned on ?



--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication and fsync

[Kevin Grittner]

Tomas Vondra t...@fuzzy.cz wrote:

 I think it's safe as long as you don't try to reuse the cluster
 after a crash (be it due to OS error, power outage, ...). If the
 primary crashes for any reasons, you have to start from scratch,
 otherwise there might be silent corruption as you've described.

I agree.  It seems to me that as long as you never try to start the
old master after a crash of the OS (directly or because of hardware
or VM failure), the standby should be usable without risk of
corruption.  As soon as you start the old master after such a crash
though, you could be replicating corrupted blocks; you would need
to be very hard-line about never bringing the old master back up.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication and fsync

[Tomas Vondra]

Hi,

On 24.10.2013 23:18, Alban Hertroys wrote:
 On Oct 24, 2013, at 18:10, maillis...@gmail.com wrote:
 
 Thank you for the answers. I'm still confused. If fsync is not
 replicated to the slave, then how is replication affected by a
 corrupt master? If the master dies and there's a commit recorded in
 the wal log that didn't actually happen, wouldn't the slave still
 be expected to be in a sane state, with the wal logs accurately
 reflecting what's on disk?
 
 Maybe I just don't understand streaming replication enough. The
 docs seem to say that synchronous commits mean that the slave also
 has to verify a write before a transaction is considered complete.
 How does fsync affect the way/order in which statements are sent to
 the slave for replication?
 
 What you're missing is that the master will be replicating corrupt
 data. That is, _if_ it gets corrupted of course. But, data corruption
 in a database has a tendency to go unnoticed for a while.

I think it's safe as long as you don't try to reuse the cluster after a
crash (be it due to OS error, power outage, ...). If the primary crashes
for any reasons, you have to start from scratch, otherwise there might
be silent corruption as you've described.

regards
Tomas


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication and fsync

[Albe Laurenz]

DDT wrote:
 According to manual, when you set synchronous_commit to on, the transaction 
 commits will wait until
 master and slave flush the commit record of transaction to the physical 
 storage, so I think even if
 turn off the fsync on master is safe for data consistency and data will not 
 be lost if slave physical
 storage is not damaged.

I don't think that this is safe.

What if the master crashes and becomes corrupted as a consequence?

It will start sending corrupted data to the slave, which will
replay it, thus becoming corrupted itself.

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

Re: [GENERAL] Replication and fsync

[Fujii Masao]

On Thu, Oct 24, 2013 at 10:39 AM,  maillis...@gmail.com wrote:
 Newb question.

 I'm running 9.1 with a slave using streaming replication. A coworker wants
 to turn off fsync on the master and insists that the slave will still be in
 a usable state if there is a failure on the master. We all know that turning
 off fsync is a bad idea, but I was under the impression that the fsync
 setting would be replicated to the slave, making it useless as a backup in
 this scenario.

No. The setting of fsync in the master is not replicated to the standby.

 Am I wrong? If I'm wrong, is there still danger to the slave
 in this kind of setup?

No, I think.

Regards,

-- 
Fujii Masao


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication and fsync

[Alban Hertroys]

On 24 October 2013 15:04, Fujii Masao masao.fu...@gmail.com wrote:
 On Thu, Oct 24, 2013 at 10:39 AM,  maillis...@gmail.com wrote:
 Am I wrong? If I'm wrong, is there still danger to the slave
 in this kind of setup?

 No, I think.

Corruption due to fsync being off on the master will be replicated to
the slave, or - if corruption is bad enough - replication will fail to
replicate affected records entirely. Of course, turning fsync off is
no guarantee for corruption - it's the other way around: having it on
guarantees that you don't get corruption (provided that... etc).

You could disable replication while fsync is off. I'd verify the data
on the master (by creating a dump, for example) before re-enabling it
again, though.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication and fsync

[maillists0]

Thank you for the answers. I'm still confused. If fsync is not replicated
to the slave, then how is replication affected by a corrupt master? If the
master dies and there's a commit recorded in the wal log that didn't
actually happen, wouldn't the slave still be expected to be in a sane
state, with the wal logs accurately reflecting what's on disk?

Maybe I just don't understand streaming replication enough. The docs seem
to say that synchronous commits mean that the slave also has to verify a
write before a transaction is considered complete. How does fsync affect
the way/order in which statements are sent to the slave for replication?


On Thu, Oct 24, 2013 at 9:42 AM, Alban Hertroys haram...@gmail.com wrote:

 On 24 October 2013 15:04, Fujii Masao masao.fu...@gmail.com wrote:
  On Thu, Oct 24, 2013 at 10:39 AM,  maillis...@gmail.com wrote:
  Am I wrong? If I'm wrong, is there still danger to the slave
  in this kind of setup?
 
  No, I think.

 Corruption due to fsync being off on the master will be replicated to
 the slave, or - if corruption is bad enough - replication will fail to
 replicate affected records entirely. Of course, turning fsync off is
 no guarantee for corruption - it's the other way around: having it on
 guarantees that you don't get corruption (provided that... etc).

 You could disable replication while fsync is off. I'd verify the data
 on the master (by creating a dump, for example) before re-enabling it
 again, though.

 --
 If you can't see the forest for the trees,
 Cut the trees and you'll see there is no forest.

Re: [GENERAL] Replication and fsync

[Jeff Janes]

On Wed, Oct 23, 2013 at 6:39 PM, maillis...@gmail.com wrote:

 Newb question.

 I'm running 9.1 with a slave using streaming replication. A coworker wants
 to turn off fsync on the master and insists that the slave will still be in
 a usable state if there is a failure on the master.


This would only be safe if you made sure to shoot the master in the head
after a crash.  If the master ever comes back up again, through automatic
recovery or through start-up scripts, it could start feeding corrupt WAL
records to the slave, corrupting it as well.

Cheers,

Jeff

Re: [GENERAL] Replication and fsync

[Alban Hertroys]

On Oct 24, 2013, at 18:10, maillis...@gmail.com wrote:

 Thank you for the answers. I'm still confused. If fsync is not replicated to 
 the slave, then how is replication affected by a corrupt master? If the 
 master dies and there's a commit recorded in the wal log that didn't actually 
 happen, wouldn't the slave still be expected to be in a sane state, with the 
 wal logs accurately reflecting what's on disk? 
 
 Maybe I just don't understand streaming replication enough. The docs seem to 
 say that synchronous commits mean that the slave also has to verify a write 
 before a transaction is considered complete. How does fsync affect the 
 way/order in which statements are sent to the slave for replication?

What you're missing is that the master will be replicating corrupt data. That 
is, _if_ it gets corrupted of course.
But, data corruption in a database has a tendency to go unnoticed for a while.

A corrupted master doesn't necessarily break down immediately - in fact, it can 
remain running for quite a while as long as the corruption doesn't break stuff 
in the wrong places or as long as the corrupted records don't get fetched.
Until that time, corruption is just blocks of data on disk, which quite 
possibly end up being replicated to the slave.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication and fsync

[DDT]

Dear

According to manual, when you set synchronous_commit to on, the transaction 
commits will wait until master and slave flush the commit record of transaction 
to the physical storage, so I think even if turn off the fsync on master is 
safe for data consistency and data will not be lost if slave physical storage 
is not damaged.

If synchronous_commit is set to remote_write, the transaction commits will 
wait until slave to write the data to os. Data will lose if master crashed and 
slave os crashed. But acording to WAL documents it will not crash the data 
consistency on slave if slave fsync not off.

Otherwise fsync off on master may will result in losing data  when master 
crashes, but still keep consistency on the slave if the slave is not crashed or 
slave's fsync is on .

See follow:
http://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-FSYNC 
fsync and synchronous_commit
http://www.postgresql.org/docs/current/static/wal-intro.html





-- Original --
From:  maillists0;maillis...@gmail.com;
Date:  Thu, Oct 24, 2013 09:39 AM
To:  pgsql-generalpgsql-general@postgresql.org; 

Subject:  [GENERAL] Replication and fsync



Newb question. 


I'm running 9.1 with a slave using streaming replication. A coworker wants to 
turn off fsync on the master and insists that the slave will still be in a 
usable state if there is a failure on the master. We all know that turning off 
fsync is a bad idea, but I was under the impression that the fsync setting 
would be replicated to the slave, making it useless as a backup in this 
scenario. Am I wrong? If I'm wrong, is there still danger to the slave in this 
kind of setup? Can I count on it remaining unharmed if the master suffers 
unrecoverable corruption?

Re: [GENERAL] Replication Postgre Oracle

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


 ...replication between PostgreSQL 9.2.4 and Oracle Database
...
 We also thank to develop a solution based on trigger and/or WAL

Before you reinvent that first wheel, check out Bucardo, which is a 
trigger-based solution that can go from Postgres to Oracle.

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201308262157
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAlIcB6AACgkQvJuQZxSWSshF+gCgkx7McL06gXAws96rzMSscsli
F9kAoIaTEJ1aZYxk8izDomL4ZtSjtMuO
=NavN
-END PGP SIGNATURE-




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication delay

[ascot.m...@gmail.com]

Thanks so much.

On 11 Aug 2013, at 9:50 PM, Michael Paquier wrote:

 On Sun, Aug 11, 2013 at 5:51 AM, ascot.m...@gmail.com
 ascot.m...@gmail.com wrote:
 Hi,
 
 I have a pair of PG servers, a master and a replica, all read-write queries 
 are handled by the master, read-only ones are by the replica.
 
 From time to time the replica itself is too busy, all read-only queries will 
 get  inconsistent results because of replication lag,  sometimes it can be 
 longer than 6 minutes.  I am thinking to add multiple replicas to off-load 
 read-only queries, can you please suggest a way to monitor and failover the 
 read-only query when the replication lag in a replica is more than 5 minutes?
 I assume that you could use pgpool for that. It has some monitoring
 features for replication delay and it can do read-only load balancing
 among several servers. You also shouldn't need to change your
 application.
 Have a look at its documentation to make an opinion:
 http://pgpool.projects.pgfoundry.org/pgpool-II/doc/pgpool-en.html
 
 Regards,
 -- 
 Michael



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] replication server: LOG: invalid magic number 0000 in log file 169, segment 77, offset 4325376

[Jov]

This means the slave meet the end of the WAL when it replay the WAL
files/records.The message level is LOG,so it can be safely ignored.

Jov
blog: http:amutu.com/blog http://amutu.com/blog


2013/8/11 ascot.m...@gmail.com ascot.m...@gmail.com

 Hi,

 I found 'LOG:  invalid magic number  in log file 169, segment 77,
 offset 4325376 from the replica's log:

 LOG:  entering standby mode
 LOG:  redo starts at A8/BE81B200
 LOG:  consistent recovery state reached at A9/4CF8
 LOG:  database system is ready to accept read only connections
 LOG:  invalid magic number  in log file 169, segment 77, offset 4325376
 LOG:  streaming replication successfully connected to primary

 Can you please advise what it means? can it be ignored?

 regards



 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication delay

[Michael Paquier]

On Sun, Aug 11, 2013 at 5:51 AM, ascot.m...@gmail.com
ascot.m...@gmail.com wrote:
 Hi,

 I have a pair of PG servers, a master and a replica, all read-write queries 
 are handled by the master, read-only ones are by the replica.

 From time to time the replica itself is too busy, all read-only queries will 
 get  inconsistent results because of replication lag,  sometimes it can be 
 longer than 6 minutes.  I am thinking to add multiple replicas to off-load 
 read-only queries, can you please suggest a way to monitor and failover the 
 read-only query when the replication lag in a replica is more than 5 minutes?
I assume that you could use pgpool for that. It has some monitoring
features for replication delay and it can do read-only load balancing
among several servers. You also shouldn't need to change your
application.
Have a look at its documentation to make an opinion:
http://pgpool.projects.pgfoundry.org/pgpool-II/doc/pgpool-en.html

Regards,
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] replication server: LOG: invalid magic number 0000 in log file 169, segment 77, offset 4325376

[ascot.m...@gmail.com]

Thanks so much.

On 11 Aug 2013, at 7:36 PM, Jov wrote:

 This means the slave meet the end of the WAL when it replay the WAL 
 files/records.The message level is LOG,so it can be safely ignored.
 
 Jov
 blog: http:amutu.com/blog
 
 
 2013/8/11 ascot.m...@gmail.com ascot.m...@gmail.com
 Hi,
 
 I found 'LOG:  invalid magic number  in log file 169, segment 77, offset 
 4325376 from the replica's log:
 
 LOG:  entering standby mode
 LOG:  redo starts at A8/BE81B200
 LOG:  consistent recovery state reached at A9/4CF8
 LOG:  database system is ready to accept read only connections
 LOG:  invalid magic number  in log file 169, segment 77, offset 4325376
 LOG:  streaming replication successfully connected to primary
 
 Can you please advise what it means? can it be ignored?
 
 regards
 
 
 
 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general
 
 

Re: [GENERAL] Replication delay

[Tatsuo Ishii]

 On Sun, Aug 11, 2013 at 5:51 AM, ascot.m...@gmail.com
 ascot.m...@gmail.com wrote:
 Hi,

 I have a pair of PG servers, a master and a replica, all read-write queries 
 are handled by the master, read-only ones are by the replica.

 From time to time the replica itself is too busy, all read-only queries will 
 get  inconsistent results because of replication lag,  sometimes it can be 
 longer than 6 minutes.  I am thinking to add multiple replicas to off-load 
 read-only queries, can you please suggest a way to monitor and failover the 
 read-only query when the replication lag in a replica is more than 5 minutes?
 I assume that you could use pgpool for that. It has some monitoring
 features for replication delay and it can do read-only load balancing
 among several servers. You also shouldn't need to change your
 application.
 Have a look at its documentation to make an opinion:
 http://pgpool.projects.pgfoundry.org/pgpool-II/doc/pgpool-en.html

The url above is obsoleted. Please visit:

http://www.pgpool.net/docs/latest/pgpool-en.html
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication Postgre Oracle

[Jayadevan M]

Hi,
From Oracle to PostgreSQL, you could have a look at Goldengate. It does not
support PostgreSQL as  the source database.
Regards,
Jayadevan


On Wed, Aug 7, 2013 at 1:54 PM, BOUVARD Aurélien 
aurelien.bouv...@supinfo.com wrote:

  Hi all,

 ** **

 My compagny would like to configure replication between PostgreSQL 9.2.4
 and Oracle Database (11g and maybe 12c soon). We saw that* Postgres Plus
 Database Replication *could be a good solution for us.* *

 * *

 We also thank to develop a solution based on trigger and/or WAL , but we
 didn’t know if it’s possible in our case…we have a huge amount of data (100
 terabits) and it will increase soon (Datawarehouse context)

 ** **

 So it will be very interesting for us to have some feedback about PostGre
 Plus or other concepts/solutions.

 ** **

 Regards,

 ** **

 ** **

 ** **

 ** **

Re: [GENERAL] Replication Postgre Oracle

[Gavin Flower]

On 07/08/13 20:24, BOUVARD Aurélien wrote:


Hi all,

My compagny would like to configure replication between PostgreSQL 
9.2.4 and Oracle Database (11g and maybe 12c soon). We saw 
that/Postgres Plus Database Replication /could be a good solution for 
us.//


//

We also thank to develop a solution based on trigger and/or WAL , but 
we didn't know if it's possible in our case...we have a huge amount of 
data (100 terabits) and it will increase soon (Datawarehouse context)


So it will be very interesting for us to have some feedback about 
PostGre Plus or other concepts/solutions.


Regards,


100 terabytes?

Either way, not an insignificant amount of data!

Re: [GENERAL] Replication Postgre Oracle

[James Sewell]

Postgres Plus Advanced Server (PPAS) is a licensed version of PostgreSQL
owned by EnterpriseDB. You need to buy a support contract to use this
software (although there is a 45 day trial period).

The component you are talking about is xDB Replication Server, which is
part of the PPAS product or can be licensed separately for use with
community versions of PostgreSQL.

xDB Replication server is trigger based (on both the Oracle and the
PostgreSQL side). It can handle asynchronous master/slave or multi-master
replication.

You can ask more questions about xDB at the EDB forums over at
http://forums.enterprisedb.com/forums/list.page or contact EDB directly for
pre-sales advice.

Cheers,

James Sewell
PostgreSQL Team Lead / Solutions Architect
_

[image:
http://www.lisasoft.com/sites/lisasoft/files/u1/2013hieghtslogan_0.png]

Level 2, 50 Queen St,
Melbourne, VIC, 3000

P: 03 8370 8000   F: 03 8370 8099  W: www.lisasoft.com



On Wed, Aug 7, 2013 at 6:24 PM, BOUVARD Aurélien 
aurelien.bouv...@supinfo.com wrote:

  Hi all,

 ** **

 My compagny would like to configure replication between PostgreSQL 9.2.4
 and Oracle Database (11g and maybe 12c soon). We saw that* Postgres Plus
 Database Replication *could be a good solution for us.* *

 * *

 We also thank to develop a solution based on trigger and/or WAL , but we
 didn’t know if it’s possible in our case…we have a huge amount of data (100
 terabits) and it will increase soon (Datawarehouse context)

 ** **

 So it will be very interesting for us to have some feedback about PostGre
 Plus or other concepts/solutions.

 ** **

 Regards,

 ** **

 ** **

 ** **

 ** **


-- 


--
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.
image001.png

Re: [GENERAL] REPLICATION Stopped abruptly

[Merlin Moncure]

On Fri, Jul 26, 2013 at 9:35 AM, akp geek akpg...@gmail.com wrote:
 Hi All -

 We have been running postgres 9.0.2 since October. Streaming also in
 place. Working flawless. yesterday suddenly the replication stopped.

Did you write that correctly?   If so, Postgres 9.0 is on patch
release 9.0.13.   Priority #1 is to install bugfix release and see if
you can restart replication.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] replication stops working

[John DeSoi]

On Jul 8, 2013, at 5:41 PM, Daniel Serodio (lists) daniel.li...@mandic.com.br 
wrote:

 If there are no errors in the log, how did you conclude that replication has 
 stopped working? Since you're using a hot standby, you've also setup 
 streaming replication in addition to the WAL archiving, correct?

I have an external process that calls pg_last_xact_replay_timestamp and sends 
an alert if the standby is more than 20 minutes out of sync. 

I'm not using streaming replication, just WAL archiving at 5 minute intervals. 

I just tried to restart the primary to fix it and it would not shut down. There 
should not have been any active connections. I finally had to power off the VM. 

I think what might be happening is that rsync is hanging when trying to send a 
WAL file. That might explain no error in the log and difficulty stopping the 
server. I added a timeout to the archive command; hopefully this will fix it.

John DeSoi, Ph.D.



2013-07-08 21:06:02 EDT [27170]: [1-1] 
user=main,db=main8,remote=127.0.0.1(62194) FATAL:  the database system is 
shutting down
2013-07-08 21:07:29 EDT [27189]: [1-1] 
user=postgres,db=postgres,remote=127.0.0.1(62195) FATAL:  the database system 
is shutting down
2013-07-08 21:07:51 EDT [27190]: [1-1] 
user=postgres,db=postgres,remote=127.0.0.1(62196) FATAL:  the database system 
is shutting down
2013-07-08 21:09:42 EDT [27275]: [1-1] user=postgres,db=postgres,remote=[local] 
FATAL:  the database system is shutting down
2013-07-08 21:11:03 EDT [27363]: [1-1] 
user=[unknown],db=[unknown],remote=127.0.0.1(62199) LOG:  incomplete startup 
packet
2013-07-08 21:11:03 EDT [27364]: [1-1] 
user=main,db=main8,remote=127.0.0.1(62200) FATAL:  the database system is 
shutting down
Killed by signal 15.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] replication stops working

[Daniel Serodio (lists)]

John DeSoi wrote:

I have a 9.2 hot standby setup with replication via rsync. For the second time, 
it has stopped working with no apparent error on the primary or standby. Last 
time this happened I fixed it by restarting the primary. Yesterday I started a 
new base backup around noon and it replicated without any problems for about 12 
hours. Then it just stopped and I don't see any errors in the Postgres log 
(primary or standby). I looked at other system logs and still don't see any 
problems.

I'm running Postgres 9.2.4 on CentOS 6.4. Thanks for any ideas or debug 
suggestions.

John DeSoi, Ph.D.

=

wal_level = hot_standby
wal_keep_segments = 48
max_wal_senders = 2

archive_mode = on
archive_command = 'rsync --whole-file --ignore-existing --delete-after -a %p 
bak-postgres:/pgbackup/%f'
archive_timeout = 300

If there are no errors in the log, how did you conclude that replication 
has stopped working? Since you're using a hot standby, you've also setup 
streaming replication in addition to the WAL archiving, correct?


Regards,
Daniel Serodio



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication with Drop: could not open relation with OID

[salah jubeh]

Hello,

I have just found this post 
http://grokbase.com/t/postgresql/pgsql-hackers/10ces3w9kz/alter-table-replace-with/nested/page/2#responses_tab_top
and it helped a lot 


Thanks 




 From: salah jubeh s_ju...@yahoo.com
To: pgsql pgsql-general@postgresql.org 
Sent: Thursday, June 20, 2013 5:58 PM
Subject: [GENERAL] Replication with Drop: could not open relation with OID 
 


Hello, 


I have a database server which do a complex  views calculation,  the result of 
those views are shipped to another database servers via a simple  replication 
tool which have a high  client loads. 


The tool  is creating a  table, and indexes based on predefined conf.,   then 
drop the table that needs to be synched then rename the temporary tables.  i.e.


BEGIN;
DROP TABLE IF EXISTS y;  -- the table I want to replace it 
ALTER TABLE x RENAME TO y;  -- x contains the data which synched from server  
(already created)
ALTER INDEX . RENAME TO .; -- rename indexes 
COMMIT;



In version 8.3 , 8.4, and 9.1, I get errors could not open relation with OID; 
However with version 9.2 every thing works fine, I tried to lock the table in 
access exclusive mode  before dropping it   i.e 
 
BEGIN;
LOCK TABLE y IN ACCESS EXCLUSIVE MODE;
DROP TABLE IF EXISTS y;  -- the table I want to replace 
ALTER TABLE x RENAME TO y;  -- x is
 the temporay table 
ALTER INDEX x_x_name_idx RENAME TO y_x_name_idx; -- rename indexes 
COMMIT;

But I still get the same errors.  

I have seen this post  

http://dba.stackexchange.com/questions/16909/rotate-a-table-in-postgresql and I 
used the same strategy for testing. In version 9.2 I was not able at all to 
generate the error. In 8.3, 8.4, 9.1 I was able to generate the errors. 


Since the tables, I am creating are quite big (several millions of record) , I 
am using drop and rename  to speed the creation. For small table sizes,  this 
problem does not appear often, but in my case it pops up often because of the 
table size. 


Is there any way to solve this for the mensioned versions 


Regards

Re: [GENERAL] Replication with Drop: could not open relation with OID

[Eduardo Morras]

On Thu, 20 Jun 2013 08:58:35 -0700 (PDT)
salah jubeh s_ju...@yahoo.com wrote:

 Hello, 
 
 
 I have a database server which do a complex  views calculation,  the result 
 of those views are shipped to another database servers via a simple  
 replication tool which have a high  client loads. 
 
 
 The tool  is creating a  table, and indexes based on predefined conf.,   then 
 drop the table that needs to be synched then rename the temporary tables.  
 i.e.
 
 
 BEGIN;
 DROP TABLE IF EXISTS y;  -- the table I want to replace it 
 ALTER TABLE x RENAME TO y;  -- x contains the data which synched from server  
 (already created)
 ALTER INDEX . RENAME TO .; -- rename indexes 
 COMMIT;
 
 
 
 In version 8.3 , 8.4, and 9.1, I get errors could not open relation with OID; 
 However with version 9.2 every thing works fine, I tried to lock the table in 
 access exclusive mode  before dropping it   i.e 
  
 BEGIN;
 LOCK TABLE y IN ACCESS EXCLUSIVE MODE;
 DROP TABLE IF EXISTS y;  -- the table I want to replace 
 ALTER TABLE x RENAME TO y;  -- x is the temporay table 
 ALTER INDEX x_x_name_idx RENAME TO y_x_name_idx; -- rename indexes 
 COMMIT;
 
 But I still get the same errors.  
 
 I have seen this post  
 
 http://dba.stackexchange.com/questions/16909/rotate-a-table-in-postgresql and 
 I used the same strategy for testing. In version 9.2 I was not able at all to 
 generate the error. In 8.3, 8.4, 9.1 I was able to generate the errors. 
 
 
 Since the tables, I am creating are quite big (several millions of record) , 
 I am using drop and rename  to speed the creation. For small table sizes,  
 this problem does not appear often, but in my case it pops up often because 
 of the table size. 
 
 
 Is there any way to solve this for the mensioned versions 

In sqlite, which don't have the wonderful features Postgres has, you can do 
that using a column with the data version. Create a View to the table that 
enforces current data version. Add new data using a new data version number. 
When you want to switch, update the view and delete old version data. You must 
add version column as the first index entry on the indexes you create.

HTH

 
 Regards


---   ---
Eduardo Morras emorr...@yahoo.es


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] replication breaks with CentOS 6.4 upgrade

[Tom Lane]

John DeSoi de...@pgedit.com writes:
 Foiled again by SELinux permissions:

 type=AVC msg=audit(1367932037.676:10325): avc:  denied  { search } for  
 pid=2567 comm=rsync name=pgsql dev=dm-0 ino=664822 
 scontext=unconfined_u:system_r:rsync_t:s0 
 tcontext=system_u:object_r:postgresql_db_t:s0 tclass=dir
 type=SYSCALL msg=audit(1367932037.676:10325): arch=c03e syscall=2 
 success=no exit=-13 a0=1ebd330 a1=0 a2=e a3=4 items=0 ppid=2433 pid=2567 
 auid=0 uid=26 gid=26 euid=26 suid=26 fsuid=26 egid=26 sgid=26 fsgid=26 
 tty=(none) ses=57 comm=rsync exe=/usr/bin/rsync 
 subj=unconfined_u:system_r:rsync_t:s0 key=(null)
 type=AVC msg=audit(1367932037.677:10326): avc:  denied  { execute } for  
 pid=2568 comm=rsync name=ssh dev=dm-0 ino=266187 
 scontext=unconfined_u:system_r:rsync_t:s0 
 tcontext=system_u:object_r:ssh_exec_t:s0 tclass=file
 type=SYSCALL msg=audit(1367932037.677:10326): arch=c03e syscall=59 
 success=no exit=-13 a0=7fff1686fa27 a1=7fff1686fb60 a2=7fff16872d38 
 a3=7fff1686f860 items=0 ppid=2567 pid=2568 auid=0 uid=26 gid=26 euid=26 
 suid=26 fsuid=26 egid=26 sgid=26 fsgid=26 tty=(none) ses=57 comm=rsync 
 exe=/usr/bin/rsync subj=unconfined_u:system_r:rsync_t:s0 key=(null)

 I found there is a boolean for postgres and rsync and tried
 setsebool -P postgresql_can_rsync 1
 but replication still failed to work. There must be more required related to 
 ssh and/or rsync. Anyone solved this (without just disabling SELinux)?

Short term: use audit2allow to generate custom policy tweaks that
allow these specific operations.

Longer term: file a bug in Red Hat's bugzilla against
selinux-policy-targeted.  That boolean should allow this, one would
think, or else there should be another one that does.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Replication terminated due to PANIC

[Lonni J Friedman]

If its really index corruption, then you should be able to fix it by
reindexing.  However, that doesn't explain what caused the corruption.
 Perhaps your hardware is bad in some way?

On Wed, Apr 24, 2013 at 10:46 PM, Adarsh Sharma eddy.ada...@gmail.com wrote:
 Thanks Sergey for such a quick response, but i dont think this is some patch
 problem because we have other DB servers also running fine on same version
 and message is also different :

 host= PANIC: _bt_restore_page: cannot add item to page

 And the whole day replication is working fine but at midnight when log
 rotates it shows belows msg :

 2013-04-24 00:00:00 UTC [26989]: [4945032-1] user= db= host= LOG:
 checkpoint starting: time
 2013-04-24 00:00:00 UTC [26989]: [4945033-1] user= db= host= ERROR:  could
 not open file global/14078: No such file or directory

 2013-04-24 00:00:00 UTC [26989]: [4945034-1] user= db= host= CONTEXT:
 writing block 0 of relation global/14078
 2013-04-24 00:00:00 UTC [26989]: [4945035-1] user= db= host= WARNING:  could
 not write block 0 of global/14078

 2013-04-24 00:00:00 UTC [26989]: [4945036-1] user= db= host= DETAIL:
 Multiple failures --- write error might be permanent.

 Looks like some index corruption.


 Thanks






 On Thu, Apr 25, 2013 at 8:14 AM, Sergey Konoplev gray...@gmail.com wrote:

 On Wed, Apr 24, 2013 at 5:05 PM, Adarsh Sharma eddy.ada...@gmail.com
 wrote:
  I have a Postgresql 9.2 instance running on a CentOS6.3 box.Yesterday i
  setup a hot standby by using pgbasebackup. Today i got the below  alert
  from
  standby box :
 
  [1] (from line 412,723)
  2013-04-24 23:07:18 UTC [13445]: [6-1] user= db= host= PANIC:
  _bt_restore_page: cannot add item to page
 
  When i check, the replication is terminated due to slave DB shutdown.
  From
  the logs i can see below messages :-

 I am not sure that it is your situation but take a look at this thread:


 http://www.postgresql.org/message-id/CAL_0b1t=WuM6roO8dki=w8dhh8p8whhohbpjreymmqurocn...@mail.gmail.com

 There is a patch by Andres Freund in the end of the discussion. Three
 weeks have passed after I installed the patched version and it looks
 like the patch fixed my issue.

 
  2013-04-24 23:17:16 UTC [26989]: [5360083-1] user= db= host= ERROR:
  could
  not open file global/14078: No such file or directory
  2013-04-24 23:17:16 UTC [26989]: [5360084-1] user= db= host= CONTEXT:
  writing block 0 of relation global/14078
  2013-04-24 23:17:16 UTC [26989]: [5360085-1] user= db= host= WARNING:
  could
  not write block 0 of global/14078
  2013-04-24 23:17:16 UTC [26989]: [5360086-1] user= db= host= DETAIL:
  Multiple failures --- write error might be permanent.
 
  I checked in global directory of master, the directory 14078 doesn't
  exist.
 
  Anyone has faced above issue ?
 
  Thanks



 --
 Kind regards,
 Sergey Konoplev
 Database and Software Consultant

 Profile: http://www.linkedin.com/in/grayhemp
 Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
 Skype: gray-hemp
 Jabber: gray...@gmail.com





-- 
~
L. Friedmannetll...@gmail.com
LlamaLand   https://netllama.linux-sxs.org


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general