date:20170227


On 2017-02-28 06:14, Adrian Klaver wrote:

On 02/27/2017 05:52 PM, Sasa Vilic wrote:

Because standby is running in syncronous replication, whereby wal
archiver is asynchronous. Therefore there is a small window where slave
has received the data but master has not pushed it yet to wal archive.


Exactly. The standby already has the latest information, it would gain
nothing from fetching it from the archive and anything it wrote to the
archive would only have the information it got from the master at the
point of failure. That is before you promoted it, after it would be on
its own path independent of the master.


Hi Adrian,

I am afraid that you don't understand me.

Standby is not fetching WAL from archive, it fetches it directly from 
master and is done synchronously, which means that master will only 
confirm transaction to client when WAL is streamed and applied at 
standby. On the other hand, master does not have to wait for WAL 
archiver. If master crashes before WAL archiver is able to send WAL, we 
would still have it on standby.


Let us for the sake of demonstration consider that we have same very low 
busy but very critical system:


1. Your client connects to primary server (master) and performs changes 
on data
2. It just happen that this is a moment where PostgreSQL opens new WAL 
segment. It writes few kilobytes in this new WAL segment but it has 
almost 16MB to write before segment is complete. So the wal archiver has 
to wait before it can push wal segment in wal archive
3. Secondary server (standby) is doing same, it is writing changes to 
newly created WAL segment

4. Your client issues COMMIT
- primary waits until changes are applied at secondary
- primary flushes changes to WAL
- secondary confirms transaction to primary
- primary confirms transaction to client
- WAL is still not processed by wal archiver because it is only 
i.e. 1 MB big and we are still left 15MB to go

5. Primary server crashes, i.e. due to catastrophic disk failure
- everything stops and can't be recovered
- wal archiver is dead, but even if it were alive it wouldn't send 
WAL to archive anyway because 16MB of wal segment was not filled up

6. We promote our secondary server to master
- In secondary server's WAL we already got changes from primary
- Secondary continues appending new changes to wal segment
7. Eventually WAL segment on secondary fills up and then pushes it to 
wal archive.
8. Although primary is dead, we didn't loose anything because lost WAL 
data was pushed by secondary.


Regards,
Sasa


--
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] pg_xlog keeps growing

On Monday, February 27, 2017, dhanuj hippie  wrote:

> So does it work like - it can go upto 16MB*1024 times which is 16GB, and
> then this will be cleaned up automatically ?
>
> On Tue, Feb 28, 2017 at 9:47 AM, dhanuj hippie  > wrote:
>
>> psql (9.3.5)
>> wal_keep_segments = 1024
>>
>>
https://www.postgresql.org/docs/9.3/static/wal-configuration.html

David J.

Re: [GENERAL] pg_xlog keeps growing

Based on my config, what is the max size this directory is expected to grow
? And how can I check whether this recycle/removal is happening fine ?

On Tue, Feb 28, 2017 at 10:08 AM, Michael Paquier  wrote:

> On Tue, Feb 28, 2017 at 1:20 PM, dhanuj hippie 
> wrote:
> > So does it work like - it can go upto 16MB*1024 times which is 16GB, and
> > then this will be cleaned up automatically ?
>
> On a standby each time a restart point is created the oldest segments
> are either recycled or removed, wal_keep_segments retains more of
> that. You can look at KeepLogSeg() in
> src/backend/access/transam/xlog.c if you want..
> --
> Michael
>

Re: [GENERAL] Shared WAL archive between master and standby: WALs not always identical

On 02/27/2017 05:52 PM, Sasa Vilic wrote:

Because standby is running in syncronous replication, whereby wal
archiver is asynchronous. Therefore there is a small window where slave
has received the data but master has not pushed it yet to wal archive.

Exactly. The standby already has the latest information, it would gain 
nothing from fetching it from the archive and anything it wrote to the 
archive would only have the information it got from the master at the 
point of failure. That is before you promoted it, after it would be on 
its own path independent of the master.

See here for more info:

https://www.postgresql.org/docs/9.6/static/warm-standby-failover.html

Regards,
Sasa

Am 28.02.2017 02:48 schrieb "Adrian Klaver" >:

On 02/27/2017 05:29 PM, Sasa Vilic wrote:

Master is streaming directly to standby. Both master and standby are
pushing WALs to archive.

My point is that in case that master crashed completely (and we
failover
to standby) and wal archiver on master didn't push everything to wal
archive, we would still have a wal pushed from slave. Therefore
there is
no interruption in WAL stream.

Still failing to see how the standby can have more information then
what the master had sent to it at the time of the crash.

Regards,
Sasa

On 28 February 2017 at 01:57, Adrian Klaver

>> wrote:

On 02/27/2017 04:40 PM, Sasa Vilic wrote:

Hallo,

I am trying to setup shared WAL archive between master
and standby.
Standby is synchronously streaming from master and both
servers
run with
archive_mode = always. The ideas is that when promoting
standby to
master we would not missed WALs.

I seem to be missing the point of duplicating your effort.

You are doing this, correct?:

Master WAL --> WAL archive <--
  |
Master stream --> Standby --> |

I can't see how the Standby contributes anything to the
archive that
it does not already have from the Master?

My problem is that sometimes WAL uploaded from master
and from
slave are
not 100% identical. In most cases they are but
occasionally they are
not. I have written small script that ensures that
upload is free of
race condition and I log md5 sum of each WAL. Aren't
WALs from
master
and standby supposed to be identical? After all, standby
is just
consuming WAL that it is receiving from master ...

Or do you have any better suggestion on how to achieve
continuous
incremental backup?

Thanks in advance

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

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

--
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] [ADMIN] cpu hight sy% usage

2017-02-27 Thread Tom Lane

"downey.d...@postgresdata.com"  writes:
> i have PostgreSQL 9.5.3 server running on redhalt 6.6
> when i run one query  with pgbench the cpu is 80% and sy% is 60%

If you were to provide a self-contained test case, people might take
some interest in this ... but nobody is going to spend time guessing
at all the details you didn't supply.

https://www.postgresql.org/docs/current/static/bug-reporting.html
https://wiki.postgresql.org/wiki/Slow_Query_Questions

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] pg_xlog keeps growing

2017-02-27 Thread Michael Paquier

On Tue, Feb 28, 2017 at 1:20 PM, dhanuj hippie  wrote:
> So does it work like - it can go upto 16MB*1024 times which is 16GB, and
> then this will be cleaned up automatically ?

On a standby each time a restart point is created the oldest segments
are either recycled or removed, wal_keep_segments retains more of
that. You can look at KeepLogSeg() in
src/backend/access/transam/xlog.c if you want..
-- 
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] pg_xlog keeps growing

So does it work like - it can go upto 16MB*1024 times which is 16GB, and
then this will be cleaned up automatically ?

On Tue, Feb 28, 2017 at 9:47 AM, dhanuj hippie 
wrote:

> psql (9.3.5)
> wal_keep_segments = 1024
>
> On Tue, Feb 28, 2017 at 9:16 AM, Rob Sargent 
> wrote:
>
>>
>> > On Feb 27, 2017, at 8:33 PM, dhanuj hippie 
>> wrote:
>> >
>> > Hi,
>> >
>> > I have a postgres cluster running in hot_standby. I see the pg_xlog is
>> growing over time (may files of size 16 MB each). The replication lag is
>> very less ~2kB, and never goes into a bad state.
>> > I'm manually resetting this once a while using pg_resetxlog command.
>> > Is there a way to understand why my system keeps running into this
>> problem ?
>> >
>> > Thanks
>>
>> wal_keep_segments is set to ???
>>
>> (and version = ???, etc)
>
>
>

Re: [GENERAL] pg_xlog keeps growing

psql (9.3.5)
wal_keep_segments = 1024

On Tue, Feb 28, 2017 at 9:16 AM, Rob Sargent  wrote:

>
> > On Feb 27, 2017, at 8:33 PM, dhanuj hippie 
> wrote:
> >
> > Hi,
> >
> > I have a postgres cluster running in hot_standby. I see the pg_xlog is
> growing over time (may files of size 16 MB each). The replication lag is
> very less ~2kB, and never goes into a bad state.
> > I'm manually resetting this once a while using pg_resetxlog command.
> > Is there a way to understand why my system keeps running into this
> problem ?
> >
> > Thanks
>
> wal_keep_segments is set to ???
>
> (and version = ???, etc)

Re: [GENERAL] pg_xlog keeps growing

2017-02-27 Thread Rob Sargent


> On Feb 27, 2017, at 8:33 PM, dhanuj hippie  wrote:
> 
> Hi,
> 
> I have a postgres cluster running in hot_standby. I see the pg_xlog is 
> growing over time (may files of size 16 MB each). The replication lag is very 
> less ~2kB, and never goes into a bad state.
> I'm manually resetting this once a while using pg_resetxlog command.
> Is there a way to understand why my system keeps running into this problem ?
> 
> Thanks

wal_keep_segments is set to ???

(and version = ???, etc)

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

[GENERAL] pg_xlog keeps growing

Hi,

I have a postgres cluster running in hot_standby. I see the pg_xlog is
growing over time (may files of size 16 MB each). The replication lag is
very less ~2kB, and never goes into a bad state.
I'm manually resetting this once a while using pg_resetxlog command.
Is there a way to understand why my system keeps running into this problem ?

Thanks

Re: [GENERAL] Shared WAL archive between master and standby: WALs not always identical

On Mon, Feb 27, 2017 at 7:32 PM, Sasa Vilic  wrote:

>
> My general idea is to have synchronous hot standby and asynchronous shared
> wal archive. If that were possible I could actually switch back and forth
> between master and slave without interrupting wal stream and with very
> short downtime. This also makes PostgreSQL upgrade very easy. Alternative
> to this is to have separate backup for master and slave, but this has other
> disadvantages:
>
> * I need double disk space for backup if I am going to archive WALs from
> standby and master at the same time, or
> * I could only archive WALs from current master, but that would require
> performing base backup immediately after failover. Otherwise archived WALs
> are useless.
>
>
I became a bit skeptical when I read:

"To get a complete series of WAL files in the archive, you must ensure that
all WAL is archived, before it reaches the standby. This is inherently true
with file-based log shipping, as the standby can only restore files that
are found in the archive, but not if streaming replication is enabled."

given the lack of any hint as to how one would accomplish "a complete
series of WAL files in the archive" in streaming replication mode...

Maybe I'm just missing something here - but it does seem like you don't get
to have your cake and eat it...

Dave

Re: [GENERAL] Shared WAL archive between master and standby: WALs not always identical

Am 28.02.2017 02:50 schrieb "David G. Johnston" :

It is customary to inline or bottom-posts on these lists.  Please follow
the example of those responding to your emails.

On Mon, Feb 27, 2017 at 6:45 PM, Sasa Vilic  wrote:

> And also this:
>
> """
> If archive_mode is set to on, the archiver is not enabled during recovery
> or standby mode. If the standby server is promoted, it will start archiving
> after the promotion, but will not archive any WAL it did not generate
> itself. To get a complete series of WAL files in the archive, you must
> ensure that all WAL is archived, before it reaches the standby
> """
>
> If I understand it correctly, WAL started on master but finished on
> standby (after failover) will still not be (old master is presumably dead
> and new master must wait for next WAL segment). Of course, the next WAL
> segment will be sent by new master, but we are going to miss exactly this
> one WAL segment during which failover occurred and thus introduce
> interruption in our WAL stream. Am I right?
>

Requires knowledge and familiarity I present lack.  Sorry.  I think I see
where you are going with all of this but it would probably help to
explicitly state the overall concern or plan and not just ask how specific
mechanics work in isolation.

David J.

Hi David,

sorry about email formating. I didn't realize that such convetions exists
as I am first time here + I am currently using gmail mobile app. I hope it
looks good this time. :)

My general idea is to have synchronous hot standby and asynchronous shared
wal archive. If that were possible I could actually switch back and forth
between master and slave without interrupting wal stream and with very
short downtime. This also makes PostgreSQL upgrade very easy. Alternative
to this is to have separate backup for master and slave, but this has other
disadvantages:

* I need double disk space for backup if I am going to archive WALs from
standby and master at the same time, or
* I could only archive WALs from current master, but that would require
performing base backup immediately after failover. Otherwise archived WALs
are useless.

Both of these solutions are good solution but not perfect. I thought that
shared wal archive is possible, based on PostgreSQL documention. I also
assume that requirement "not to overwrite existing WAL with different
content" was only there to prevent from accidental mis-configuration.

I wasn't aware that standby server would produce different WAL. My current
test setup looks just like that. And it works, except for 2-3 WALs per day
that are not identical. Everything else is same. I can even restore backup
without issues, but those non-identical WALs still throuble me because I
fear that I am missing something big.

I wrote a small python script to perform WAL decoding and I hope get more
information tommorow morning. Since I was playing today with repmgr (which
internaly uses pg_rewind for switchover) I got suspicios that this could be
it.

Anyway, I hopped that someone had similar setup and experience with it. :(

Sorry for such lengthly email.

Regards,
Sasa

Re: [GENERAL] Shared WAL archive between master and standby: WALs not always identical

On Mon, Feb 27, 2017 at 6:10 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> But IIUC the system seems designed around master->slave replication and
> doesn't support slave daisy-chains.
>
>
I thought that sounded wrong when I wrote it...

https://www.postgresql.org/docs/9.5/static/warm-standby.html

David J.

Re: [GENERAL] Shared WAL archive between master and standby: WALs not always identical

Because standby is running in syncronous replication, whereby wal archiver
is asynchronous. Therefore there is a small window where slave has received
the data but master has not pushed it yet to wal archive.

Regards,
Sasa

Am 28.02.2017 02:48 schrieb "Adrian Klaver" :

> On 02/27/2017 05:29 PM, Sasa Vilic wrote:
>
>> Master is streaming directly to standby. Both master and standby are
>> pushing WALs to archive.
>>
>> My point is that in case that master crashed completely (and we failover
>> to standby) and wal archiver on master didn't push everything to wal
>> archive, we would still have a wal pushed from slave. Therefore there is
>> no interruption in WAL stream.
>>
>
> Still failing to see how the standby can have more information then what
> the master had sent to it at the time of the crash.
>
>
>> Regards,
>> Sasa
>>
>> On 28 February 2017 at 01:57, Adrian Klaver > > wrote:
>>
>> On 02/27/2017 04:40 PM, Sasa Vilic wrote:
>>
>> Hallo,
>>
>> I am trying to setup shared WAL archive between master and
>> standby.
>> Standby is synchronously streaming from master and both servers
>> run with
>> archive_mode = always. The ideas is that when promoting standby to
>> master we would not missed WALs.
>>
>>
>> I seem to be missing the point of duplicating your effort.
>>
>> You are doing this, correct?:
>>
>> Master WAL --> WAL archive <--
>>   |
>> Master stream --> Standby --> |
>>
>> I can't see how the Standby contributes anything to the archive that
>> it does not already have from the Master?
>>
>>
>>
>> My problem is that sometimes WAL uploaded from master and from
>> slave are
>> not 100% identical. In most cases they are but occasionally they
>> are
>> not. I have written small script that ensures that upload is free
>> of
>> race condition and I log md5 sum of each WAL. Aren't WALs from
>> master
>> and standby supposed to be identical? After all, standby is just
>> consuming WAL that it is receiving from master ...
>>
>> Or do you have any better suggestion on how to achieve continuous
>> incremental backup?
>>
>> Thanks in advance
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Shared WAL archive between master and standby: WALs not always identical

It is customary to inline or bottom-posts on these lists.  Please follow
the example of those responding to your emails.

On Mon, Feb 27, 2017 at 6:45 PM, Sasa Vilic  wrote:

> And also this:
>
> """
> If archive_mode is set to on, the archiver is not enabled during recovery
> or standby mode. If the standby server is promoted, it will start archiving
> after the promotion, but will not archive any WAL it did not generate
> itself. To get a complete series of WAL files in the archive, you must
> ensure that all WAL is archived, before it reaches the standby
> """
>
> If I understand it correctly, WAL started on master but finished on
> standby (after failover) will still not be (old master is presumably dead
> and new master must wait for next WAL segment). Of course, the next WAL
> segment will be sent by new master, but we are going to miss exactly this
> one WAL segment during which failover occurred and thus introduce
> interruption in our WAL stream. Am I right?
>

Requires knowledge and familiarity I present lack.  Sorry.  I think I see
where you are going with all of this but it would probably help to
explicitly state the overall concern or plan and not just ask how specific
mechanics work in isolation.

David J.

Re: [GENERAL] Shared WAL archive between master and standby: WALs not always identical


On 02/27/2017 05:29 PM, Sasa Vilic wrote:

Master is streaming directly to standby. Both master and standby are
pushing WALs to archive.

My point is that in case that master crashed completely (and we failover
to standby) and wal archiver on master didn't push everything to wal
archive, we would still have a wal pushed from slave. Therefore there is
no interruption in WAL stream.


Still failing to see how the standby can have more information then what 
the master had sent to it at the time of the crash.




Regards,
Sasa

On 28 February 2017 at 01:57, Adrian Klaver > wrote:

On 02/27/2017 04:40 PM, Sasa Vilic wrote:

Hallo,

I am trying to setup shared WAL archive between master and standby.
Standby is synchronously streaming from master and both servers
run with
archive_mode = always. The ideas is that when promoting standby to
master we would not missed WALs.


I seem to be missing the point of duplicating your effort.

You are doing this, correct?:

Master WAL --> WAL archive <--
  |
Master stream --> Standby --> |

I can't see how the Standby contributes anything to the archive that
it does not already have from the Master?



My problem is that sometimes WAL uploaded from master and from
slave are
not 100% identical. In most cases they are but occasionally they are
not. I have written small script that ensures that upload is free of
race condition and I log md5 sum of each WAL. Aren't WALs from
master
and standby supposed to be identical? After all, standby is just
consuming WAL that it is receiving from master ...

Or do you have any better suggestion on how to achieve continuous
incremental backup?

Thanks in advance



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





--
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] Shared WAL archive between master and standby: WALs not always identical

And also this:

"""
If archive_mode is set to on, the archiver is not enabled during recovery
or standby mode. If the standby server is promoted, it will start archiving
after the promotion, but will not archive any WAL it did not generate
itself. To get a complete series of WAL files in the archive, you must
ensure that all WAL is archived, before it reaches the standby
"""

If I understand it correctly, WAL started on master but finished on standby
(after failover) will still not be (old master is presumably dead and new
master must wait for next WAL segment). Of course, the next WAL segment
will be sent by new master, but we are going to miss exactly this one WAL
segment during which failover occurred and thus introduce interruption in
our WAL stream. Am I right?

Regards,
Sasa

On 28 February 2017 at 02:33, Sasa Vilic  wrote:

> Hi David,
>
> thanks for the answer. I read this in documentation but here there is a
> corner case that I am not sure how to handle:
> """
> This requires more care in the archive_command, as it must be careful to
> not overwrite an existing file with different contents, *but return
> success if the exactly same file is archived twice.*
> """
> But what I am supposed to do when content differs? Still return success
> and ignore or return error? If I return error, wouldn't that prevent wal
> archiver slave from pushing further WALs?
>
> Regards,
> Sasa
>
>
> On 28 February 2017 at 02:10, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Mon, Feb 27, 2017 at 5:40 PM, Sasa Vilic  wrote:
>>
>>> Aren't WALs from master and standby supposed to be identical?
>>>
>>
>> This would seem unwise to assume on its face and at least one piece of
>> documentation directly mentions that it is false:
>>
>> https://www.postgresql.org/docs/9.6/static/warm-standby.html
>> #CONTINUOUS-ARCHIVING-IN-STANDBY
>>
>> """
>> When continuous WAL archiving is used in a standby, there are two
>> different scenarios: the WAL archive can be shared between the primary and
>> the standby, or the standby can have its own WAL archive. When the standby
>> has its own WAL archive, set archive_mode to always, and the standby will
>> call the archive command for every WAL segment it receives, whether it's by
>> restoring from the archive or by streaming replication. *The shared
>> archive can be handled similarly, but the archive_command must test if the
>> file being archived exists already, and if the existing file has identical
>> contents*. This requires more care in the archive_command, as it must be
>> careful to not overwrite an existing file with different contents, but
>> return success if the exactly same file is archived twice. And all that
>> must be done free of race conditions, if two servers attempt to archive the
>> same file at the same time.
>> """
>>
>> The contents of both must match with respect to the data files but there
>> are likely things that go into the master WAL stream solely for the purpose
>> of communicating with a standby - and possibly some standby concepts that
>> would be unique to the standby's WAL - that would cause them to differ.
>> Not familiar enough to quickly list examples of what those might be.  But
>> IIUC the system seems designed around master->slave replication and doesn't
>> support slave daisy-chains.
>>
>> David J.
>>
>>
>

Re: [GENERAL] Shared WAL archive between master and standby: WALs not always identical

On Mon, Feb 27, 2017 at 6:33 PM, Sasa Vilic  wrote:

> Hi David,
>
> thanks for the answer. I read this in documentation but here there is a
> corner case that I am not sure how to handle:
> """
> This requires more care in the archive_command, as it must be careful to
> not overwrite an existing file with different contents, *but return
> success if the exactly same file is archived twice.*
> """
> But what I am supposed to do when content differs? Still return success
> and ignore or return error? If I return error, wouldn't that prevent wal
> archiver slave from pushing further WALs?
>
>
As Adrian said - I'm not seeing the point to even dealing with a shared
archive.  My solution would be to avoid the problem completely by pointing
the standby WAL elsewhere.

That said, if I was a guessing man, I would say that, yes, you indicate
failure.  The file in question will exist within the archive and will
contain the contents from the master.  The standby's view of the file would
be discarded.

"as it must be careful to not overwrite an existing file with different
contents, but return success if the exactly same file is archived twice." -
the unspoken flip side is not returning true if the "not overwrite"
provision took precedence.

David J.

Re: [GENERAL] Shared WAL archive between master and standby: WALs not always identical

Hi David,

thanks for the answer. I read this in documentation but here there is a
corner case that I am not sure how to handle:
"""
This requires more care in the archive_command, as it must be careful to
not overwrite an existing file with different contents, *but return success
if the exactly same file is archived twice.*
"""
But what I am supposed to do when content differs? Still return success and
ignore or return error? If I return error, wouldn't that prevent wal
archiver slave from pushing further WALs?

Regards,
Sasa


On 28 February 2017 at 02:10, David G. Johnston 
wrote:

> On Mon, Feb 27, 2017 at 5:40 PM, Sasa Vilic  wrote:
>
>> Aren't WALs from master and standby supposed to be identical?
>>
>
> This would seem unwise to assume on its face and at least one piece of
> documentation directly mentions that it is false:
>
> https://www.postgresql.org/docs/9.6/static/warm-standby.
> html#CONTINUOUS-ARCHIVING-IN-STANDBY
>
> """
> When continuous WAL archiving is used in a standby, there are two
> different scenarios: the WAL archive can be shared between the primary and
> the standby, or the standby can have its own WAL archive. When the standby
> has its own WAL archive, set archive_mode to always, and the standby will
> call the archive command for every WAL segment it receives, whether it's by
> restoring from the archive or by streaming replication. *The shared
> archive can be handled similarly, but the archive_command must test if the
> file being archived exists already, and if the existing file has identical
> contents*. This requires more care in the archive_command, as it must be
> careful to not overwrite an existing file with different contents, but
> return success if the exactly same file is archived twice. And all that
> must be done free of race conditions, if two servers attempt to archive the
> same file at the same time.
> """
>
> The contents of both must match with respect to the data files but there
> are likely things that go into the master WAL stream solely for the purpose
> of communicating with a standby - and possibly some standby concepts that
> would be unique to the standby's WAL - that would cause them to differ.
> Not familiar enough to quickly list examples of what those might be.  But
> IIUC the system seems designed around master->slave replication and doesn't
> support slave daisy-chains.
>
> David J.
>
>

Re: [GENERAL] Shared WAL archive between master and standby: WALs not always identical

Master is streaming directly to standby. Both master and standby are
pushing WALs to archive.

My point is that in case that master crashed completely (and we failover to
standby) and wal archiver on master didn't push everything to wal archive,
we would still have a wal pushed from slave. Therefore there is no
interruption in WAL stream.

Regards,
Sasa

On 28 February 2017 at 01:57, Adrian Klaver 
wrote:

> On 02/27/2017 04:40 PM, Sasa Vilic wrote:
>
>> Hallo,
>>
>> I am trying to setup shared WAL archive between master and standby.
>> Standby is synchronously streaming from master and both servers run with
>> archive_mode = always. The ideas is that when promoting standby to
>> master we would not missed WALs.
>>
>
> I seem to be missing the point of duplicating your effort.
>
> You are doing this, correct?:
>
> Master WAL --> WAL archive <--
>   |
> Master stream --> Standby --> |
>
> I can't see how the Standby contributes anything to the archive that it
> does not already have from the Master?
>
>
>
>> My problem is that sometimes WAL uploaded from master and from slave are
>> not 100% identical. In most cases they are but occasionally they are
>> not. I have written small script that ensures that upload is free of
>> race condition and I log md5 sum of each WAL. Aren't WALs from master
>> and standby supposed to be identical? After all, standby is just
>> consuming WAL that it is receiving from master ...
>>
>> Or do you have any better suggestion on how to achieve continuous
>> incremental backup?
>>
>> Thanks in advance
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Shared WAL archive between master and standby: WALs not always identical

On Mon, Feb 27, 2017 at 5:40 PM, Sasa Vilic wrote:

> Aren't WALs from master and standby supposed to be identical?
>

This would seem unwise to assume on its face and at least one piece of
documentation directly mentions that it is false:

https://www.postgresql.org/docs/9.6/static/warm-standby.html#CONTINUOUS-ARCHIVING-IN-STANDBY

"""
When continuous WAL archiving is used in a standby, there are two different
scenarios: the WAL archive can be shared between the primary and the
standby, or the standby can have its own WAL archive. When the standby has
its own WAL archive, set archive_mode to always, and the standby will call
the archive command for every WAL segment it receives, whether it's by
restoring from the archive or by streaming replication. *The shared archive
can be handled similarly, but the archive_command must test if the file
being archived exists already, and if the existing file has identical
contents*. This requires more care in the archive_command, as it must be
careful to not overwrite an existing file with different contents, but
return success if the exactly same file is archived twice. And all that
must be done free of race conditions, if two servers attempt to archive the
same file at the same time.
"""

The contents of both must match with respect to the data files but there
are likely things that go into the master WAL stream solely for the purpose
of communicating with a standby - and possibly some standby concepts that
would be unique to the standby's WAL - that would cause them to differ.
Not familiar enough to quickly list examples of what those might be. But
IIUC the system seems designed around master->slave replication and doesn't
support slave daisy-chains.

David J.

Re: [GENERAL] Shared WAL archive between master and standby: WALs not always identical


On 02/27/2017 04:40 PM, Sasa Vilic wrote:

Hallo,

I am trying to setup shared WAL archive between master and standby.
Standby is synchronously streaming from master and both servers run with
archive_mode = always. The ideas is that when promoting standby to
master we would not missed WALs.


I seem to be missing the point of duplicating your effort.

You are doing this, correct?:

Master WAL --> WAL archive <--
  |
Master stream --> Standby --> |

I can't see how the Standby contributes anything to the archive that it 
does not already have from the Master?




My problem is that sometimes WAL uploaded from master and from slave are
not 100% identical. In most cases they are but occasionally they are
not. I have written small script that ensures that upload is free of
race condition and I log md5 sum of each WAL. Aren't WALs from master
and standby supposed to be identical? After all, standby is just
consuming WAL that it is receiving from master ...

Or do you have any better suggestion on how to achieve continuous
incremental backup?

Thanks in advance



--
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

[GENERAL] Shared WAL archive between master and standby: WALs not always identical

Hallo,

I am trying to setup shared WAL archive between master and standby. Standby
is synchronously streaming from master and both servers run with
archive_mode = always. The ideas is that when promoting standby to master
we would not missed WALs.

My problem is that sometimes WAL uploaded from master and from slave are
not 100% identical. In most cases they are but occasionally they are not. I
have written small script that ensures that upload is free of race
condition and I log md5 sum of each WAL. Aren't WALs from master and
standby supposed to be identical? After all, standby is just consuming WAL
that it is receiving from master ...

Or do you have any better suggestion on how to achieve continuous
incremental backup?

Thanks in advance

Re: [GENERAL] Conferences for a DBA?

2017-02-27 Thread Nathan Stocks

Thank you for mentioning location, Josh.

I should have noted that I am in the western United States.

From: Joshua D. Drake 
Sent: Monday, February 27, 2017 4:39:08 PM
To: Nathan Stocks; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Conferences for a DBA?

On 02/27/2017 03:25 PM, Nathan Stocks wrote:
> What worthwhile conferences should a PostgreSQL DBA consider going to?
>
>
> There have been some good sessions at OSCON in the past, but I was
> wondering about more DBA-specific events.

If you are in North America, this is the largest and it is taking place
in a Month.

http://pgconf.us/

There are others such as postgresopen and next week there is a two
track, two day set of sessions at SCALE.

Sincerely,

JD

--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.

Re: [GENERAL] Conferences for a DBA?

2017-02-27 Thread Joshua D. Drake


On 02/27/2017 03:25 PM, Nathan Stocks wrote:

What worthwhile conferences should a PostgreSQL DBA consider going to?


There have been some good sessions at OSCON in the past, but I was
wondering about more DBA-specific events.


If you are in North America, this is the largest and it is taking place 
in a Month.


http://pgconf.us/

There are others such as postgresopen and next week there is a two 
track, two day set of sessions at SCALE.


Sincerely,

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


--
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] Conferences for a DBA?

2017-02-27 Thread Joe Conway

On 02/27/2017 03:25 PM, Nathan Stocks wrote:
> What worthwhile conferences should a PostgreSQL DBA consider going to?
> 
> There have been some good sessions at OSCON in the past, but I was
> wondering about more DBA-specific events.

In North America the bigger ones are:
--
PostgreSQL@SCaLE: 24 talks, *this* week Thursday/Friday in Pasadena
PGConf.US: last week of March in Jersey City
PGCon: end of May in Ottawa, Canada
Postgres Open SV: first week of September in San Francisco

See the wiki for a list of past events with talk titles and many talk
slide-decks:

https://wiki.postgresql.org/wiki/PostgreSQL_Related_Slides_and_Presentations

HTH,

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Conferences for a DBA?

2017-02-27 Thread cen

How about PGCon? I've never been but the online videos are always 
interesting.



Nathan Stocks je 28. 02. 2017 ob 00:25 napisal:


What worthwhile conferences should a PostgreSQL DBA consider going to?


There have been some good sessions at OSCON in the past, but I was 
wondering about more DBA-specific events.



~ Nathan

[GENERAL] Conferences for a DBA?

2017-02-27 Thread Nathan Stocks

What worthwhile conferences should a PostgreSQL DBA consider going to?


There have been some good sessions at OSCON in the past, but I was wondering 
about more DBA-specific events.


~ Nathan

[GENERAL] Question about TOAST table - PostgreSQL 9.2

2017-02-27 Thread Patrick B

Hi all.

I have a database which is 4TB big. We currently store binary data in a
bytea data type column (seg_data BYTEA). The column is behind binary_schema
and the files types stored are: pdf, jpg, png.


*Getting the schema binary_schema size:*

SELECT pg_size_pretty(pg_database_size('live_database')) As fullprod,

pg_size_pretty(CAST(pg_database_size('live_database') - (SELECT
SUM(pg_total_relation_size(table_schema || '.' || table_name)  )

FROM information_schema.tables WHERE table_schema = 'binary_schema') As
bigint)) As  tobebackedup_size,

pg_size_pretty(CAST((SELECT SUM(pg_total_relation_size(table_schema || '.'
|| table_name) )

FROM information_schema.tables

WHERE table_schema = 'binary_schema') As bigint) )  As junk_size;


fullprod tobebackedup_size junk_size

 - -

4302 GB  489 GB2813 GB




On my database, using pgadmin, I can see a lot of *pg_tast_temp_** and
*pg_temp_** table.

I understand the TOAST code is triggered when a row is wider than the
TOAST_TUPLE_THRESHOLD [1]. I also understand the only way to shrink toast
table is by using a vacuum full or even pg_dump.

*Questions:*

1 - If I take out 500GB of bytea data ( by updating the column seg_data and
setting it to null ), will I get those 500GB of free disk space? or do I
need to run vacuum full or either pg_dump?

2 - If I choose going ahead with VACUUM FULL, I have 3 streaming
replication slaves, Will I need to run the vacuum full on them too?

3 - [2] vacuum full needs some free disk space as same size as the target
table. It locks the table (cannot be used while running vacuum full) and a
REINDEX might be needed after. AM I right?

Thanks in advanced for your help.
Patrick

[1] https://www.postgresql.org/docs/9.2/static/storage-toast.html
[2] https://wiki.postgresql.org/wiki/VACUUM_FULL

Re: [GENERAL] via psycopg2 or pg2pg? Move rows from one database to other

2017-02-27 Thread Francisco Olarte

Thomas:

On Mon, Feb 27, 2017 at 12:47 PM, Thomas Güttler
 wrote:
> Thank you for explaining the steps of your algorithm.

My pleasure. But check it anyway, I may have forgotten something ( I
normally implement this things after writing a big flow diagram on a
piece of paper and checking it for a while, or something similar, I
find easier to spot the missing spots graphically )

> Just one question: How to do the actual transfer of data?

It does not matter too much, but ..

> I see two solutions:
> 1, Read the data into a script (via psycopg2 (we love python))
> and dump it into a second connection.
> 2, connect postgres to postgres and transfer the data without a database
> adapter like psycopg2.

For 2 you need and adapter, the foreign data wrapper, anyway. I
personally would go for 1, especially if you can collocate the program
near main db ( same machine or network, so you can have enough speed )
. Normally problems are much easier to diagnose this way, as you only
deal with psycopg2, not with psyco AND fdw, and you will need a
program to do the transfers anyway. Also, IIRC, you had a lot of
machines, so you will need a main program to do all the retrying and
accounting. And you can optimize some things ( like copying from
several satellites and then inserting them at once ).

YMMV anyway, just use whichever is easier for you, but avoid false lazyness ;-)

Francisco Olarte.

-- 
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] hight cpu %sy usage

2017-02-27 Thread Jeff Janes

On Mon, Feb 27, 2017 at 6:13 AM, dby...@163.com  wrote:

> hello everyone,
>
> i have PostgreSQL 9.5.3 server running on redhalt 6.6
> when i run one query  with pgbench the cpu is 80% and sy% is 60%
>
>
Why is this a problem?  If you run the query as fast as you can, all of the
time spent running the query has to go somewhere.  Is there something
inherently wrong with much of it going to sy rather than something else?

Can you show us the pgbench command you used?  -c, -j, etc.,


> 1.the query runing is 2.7ms
>
>
Where is that from?  It contradicts the information from the "explain
analyze".


>
> i drop the btree index  table_name_1_user_id_idx
> create index table_name_1_user_id_idx  on talbe_name_1 using
> hash(user_id);
> vacuum analyze table_name_1
>
> then the cpu is normal 
>
>

What happened to the TPS?  It is easy to shift load from sy to us if you do
it by making things slower overall by bloating the time spent in user
space.  I suspect that that is what happened.

Cheers,

Jeff

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE


On 02/27/2017 09:08 AM, Sven R. Kunze wrote:

On 27.02.2017 16:37, Adrian Klaver wrote:

On 02/27/2017 07:03 AM, Sven R. Kunze wrote:

Why is this relevant for dates? I cannot see that dates are
timezone-influenced.


Per Tom's post, see points 2 & 3:


Maybe, I am on a completely wrong track here, but to me dates still
don't look timezone dependent. They are just dates and not times, aren't
they?


Yes, but is not about timezone dependency, it is about the other 
dependencies listed in the second and third points. Namely the datestyle 
setting and magic strings e.g. 'now'





"* some of them depend on the current timezone (but I don't believe
date_in does);

* all of them depend on the current datestyle setting, eg to resolve
'02/03/2017';

* all of them accept strings with time-varying values, such as 'now'
or 'today'.

You could get around the second and third points with to_timestamp(),
but since the only variant of that is one that yields timestamptz and
hence is affected by the timezone setting, it's still not immutable.
"


Reading this through again, I got an idea:

Wouldn't it be possible to provide an immutable variant of to_timestamp
and to_date with a third parameter to specify the otherwise
setting-dependent timezone?


I still feel that a function is overkill for a simple text to date
conversion. Couldn't there be an IMMUTABLE modifier for an expression to
mark it as immutable?


Any thoughts on this?



"SELECT '2007-02-02'::date;" just works. It would be great if one could
define an index with the same ease. I already can see how our
application developers need constant reminders that "in case of dates,
use 'magic_function' first". If they don't, the application will suffer
from bad performance.


Best regards,
Sven






--
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] ERROR: functions in index expression must be marked IMMUTABLE

2017-02-27 Thread Sven R. Kunze


On 27.02.2017 16:37, Adrian Klaver wrote:

On 02/27/2017 07:03 AM, Sven R. Kunze wrote:

Why is this relevant for dates? I cannot see that dates are
timezone-influenced.


Per Tom's post, see points 2 & 3:


Maybe, I am on a completely wrong track here, but to me dates still 
don't look timezone dependent. They are just dates and not times, aren't 
they?


"* some of them depend on the current timezone (but I don't believe 
date_in does);


* all of them depend on the current datestyle setting, eg to resolve
'02/03/2017';

* all of them accept strings with time-varying values, such as 'now'
or 'today'.

You could get around the second and third points with to_timestamp(),
but since the only variant of that is one that yields timestamptz and
hence is affected by the timezone setting, it's still not immutable.
"


Reading this through again, I got an idea:

Wouldn't it be possible to provide an immutable variant of to_timestamp 
and to_date with a third parameter to specify the otherwise 
setting-dependent timezone?



I still feel that a function is overkill for a simple text to date
conversion. Couldn't there be an IMMUTABLE modifier for an expression to
mark it as immutable?


Any thoughts on this?



"SELECT '2007-02-02'::date;" just works. It would be great if one could
define an index with the same ease. I already can see how our
application developers need constant reminders that "in case of dates,
use 'magic_function' first". If they don't, the application will suffer
from bad performance.


Best regards,
Sven



--
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] ERROR: functions in index expression must be marked IMMUTABLE