Re: FW: Setting up streaming replication problems

[Andreas Kretschmer]

Hi,


Am 29.01.2018 um 06:03 schrieb Thiemo Kellner:

Thanks for your patience.


you are welcome. From the other mail (in german) i think i know the 
issue now: you have placed the recovery.conf NOT in the data_directoy 
but in /etc/...



PostgreSQL is looking for the recovery.conf in the data_directory.



Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: Information on savepoint requirement within transctions

[Robert Zenz]

On 26.01.2018 17:11, David G. Johnston wrote:
> ​The convention for these lists is to inline or bottom-post.  Top-posting
> is discouraged.

Okay, I'll keep it in mind.

> Here's my take, the docs support this but maybe take some interpretation...
> 
> A SAVEPOINT ​begins what is effectively a sub-transaction without ending
> the main transaction.  If that sub-transaction fails you can throw it away
> (ROLLBACK TO) and pretend that it didn't happen: you are dropped back to
> the point where the savepoint was issued and the main transaction
> re-engaged.
> 
> Its kinda like a try/catch block:
> 
> BEGIN:
> 
> do_stuff
> 
> SAVEPOINT try { lets_fail; this_works; } catch { ROLLBACK TO }
> 
> do_more_stuff
> 
> ​COMMIT;​
> 
> ​As ​long as both do_stuff and do_more_stuff succeed when you commit the
> things that they did will persist.
> 
> The stuff in lets_fail AND this_works, however, will be discarded because
> of the lets_fail failing and this_works belonging to the same
> sub-transaction.
> 
> ​If do_more_stuff depends on lets_fail or this_works succeeding then
> do_more_stuff will ​fail and will cause do_stuff to rollback as well.

Thanks for the detailed explanation, that helps a lot. However, I'm still
looking for "official" documentation on their *requirement* in combination with
failing statements. Documentation, bug report, mailing list discussions,
something like that. In particular I'm interested in the questions:

 * Why are they required in combination with failing statements (when every
other database does an "automatic savepoint and rollback" for a failed 
statement)?
 * When was that behavior chosen? Was it always like that? Was it adopted later?
 * What is the design decision behind it?

There is a lot of information on what savepoints are and how they work (and also
thanks to you I'm now fairly certain I have good grasp on them), but I fail to
locate documentation on these questions.

Re: Information on savepoint requirement within transctions

[Adam Tauno Williams]

>  * Why are they required in combination with failing statements (when
> every other database does an "automatic savepoint and rollback" for a
> failed statement)?

It this statement true?  I very much feel *not*.  This depends on how
you have set AUTO_COMMIT - and it certainly is not true for
transactions of multiple statements.

Informix does not do "automatic savepoint and rollback" - you will
rollback the entire transaction.

Fwd: Re: Information on savepoint requirement within transctions

[Robert Zenz]

On 29.01.2018 12:37, Adam Tauno Williams wrote:
> It this statement true?  I very much feel *not*.  This depends on how
> you have set AUTO_COMMIT - and it certainly is not true for
> transactions of multiple statements.

Maybe I should clarify at that point that AUTO_COMMIT is OFF, and yes, that is
how it does behave for me.

> Informix does not do "automatic savepoint and rollback" - you will
> rollback the entire transaction.

Okay, not *all* of them, but *most* as it seems.

Re: Information on savepoint requirement within transctions

[David G. Johnston]

On Mon, Jan 29, 2018 at 1:37 AM, Robert Zenz 
wrote:

> Documentation, bug report, mailing list discussions,
> something like that. In particular I'm interested in the questions:
>
>  * Why are they required in combination with failing statements (when every
> other database does an "automatic savepoint and rollback" for a failed
> statement)?
>  * When was that behavior chosen? Was it always like that? Was it adopted
> later?
>  * What is the design decision behind it?
>
> There is a lot of information on what savepoints are and how they work
> (and also
> thanks to you I'm now fairly certain I have good grasp on them), but I
> fail to
> locate documentation on these questions.


​Those questions would not be answered in user-facing documentation.  You
can explore the git history and search the far-back mailing list archives
if you wish to satisfy your curiosity.  For me this is how it works - the
only question for me is whether I should argue that the behavior should be
changed.  I do vaguely recall this topic coming up in the recent (couple of
years) past...but changing transaction behavior like this is problematic no
matter how desirable the new state might be to have (and that's debatable).

It may be worth updating the docs here but you have received your official
answer - I'm nearly positive I'm right and even if I was mistaken most
likely I would have been corrected by now.  I am writing this on a mailing
list...

David J.

Re: Information on savepoint requirement within transctions

[Robert Zenz]

On 29.01.2018 14:36, David G. Johnston wrote:
> ​Those questions would not be answered in user-facing documentation.  You
> can explore the git history and search the far-back mailing list archives if
> you wish to satisfy your curiosity. For me this is how it works - the only 
> question for me is whether I should argue that the behavior should be 
> changed. I do vaguely recall this topic coming up in the recent (couple of 
> years) past...but changing transaction behavior like this is problematic no 
> matter how desirable the new state might be to have (and that's debatable).

From my point of view, no, it shouldn't be changed. It has always been this way
and I find nothing wrong with the approach, it is only something that you need
to be aware of, that's all.

> It may be worth updating the docs here...

I'd vote for that. I would have expected to see this mentioned in the
documentation a little bit more prominent than just a single sentence at the end
of the transaction tutorial. A short section about how the transaction behaves
in an error cases (and what to do) would be nice.

> ...but you have received your official answer - I'm nearly positive I'm right
> and even if I was mistaken most likely I would have been corrected by now. I
> am writing this on a mailing list...
> 
> David J.
> 

Thank you for your time and explanations.

Re: Information on savepoint requirement within transctions

[Alban Hertroys]

On 29 January 2018 at 14:59, Robert Zenz  wrote:
> On 29.01.2018 14:36, David G. Johnston wrote:
...
> From my point of view, no, it shouldn't be changed. It has always been this 
> way
> and I find nothing wrong with the approach, it is only something that you need
> to be aware of, that's all.
>
>> It may be worth updating the docs here...
>
> I'd vote for that. I would have expected to see this mentioned in the
> documentation a little bit more prominent than just a single sentence at the 
> end
> of the transaction tutorial. A short section about how the transaction behaves
> in an error cases (and what to do) would be nice.

IMHO, the burden of explaining that is with those RDBMSes that don't
behave properly:

If you start a transaction and something goes wrong in the process,
the logical behaviour is to fail - the user will want to rollback to a
sane state, doing any more work is rather pointless because of that.
Allowing a commit at the end is dubious at best.

That does not exclude PG from documenting this behaviour, but I'd have
a look at the docs for those other vendors whether they perhaps
documented their irregular transactional behaviour ;)

You didn't mention which RDBMSes behave like what you expected
(probably from experience), but I seem to recall Oracle does odd stuff
like that, as well as issuing a commit to all open transactions when
any DDL happens or treating NULLs and empty literals as the same
thing. Just to say that the "big names" aren't without flaws - they're
kind of hard to fix when users probably depend on their behaviour
though.

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

PG Sharding

[Matej]

Hi Everyone.

We are looking at a rather large fin-tech installation. But as scalability
requirements are high we look at sharding of-course.

I have looked at many sources for Postgresql sharding, but we are a little
confused as to shared with schema or databases or both.


So far our understanding:

*SCHEMA.*

PROS:
- seems native to PG
- backup seems easier
- connection pooling seems easier, as you can use same connection between
shard.

CONS:
- schema changes seems litlle more complicated
- heard of backup and maintenance problems
- also some caching  problems.

*DATABASE:*

PROS:
- schema changes litlle easier
- backup and administration seems more robust

CONS:
- heard of vacuum problems
- connection pooling is hard, as 100 shards would mean 100 pools


So what is actually the right approach? If anyone could  shed some light on
my issue.

*Thanks*

Re: PG Sharding

[Melvin Davidson]

On Mon, Jan 29, 2018 at 9:34 AM, Matej  wrote:

> Hi Everyone.
>
> We are looking at a rather large fin-tech installation. But as scalability
> requirements are high we look at sharding of-course.
>
> I have looked at many sources for Postgresql sharding, but we are a little
> confused as to shared with schema or databases or both.
>
>
> So far our understanding:
>
> *SCHEMA.*
>
> PROS:
> - seems native to PG
> - backup seems easier
> - connection pooling seems easier, as you can use same connection between
> shard.
>
> CONS:
> - schema changes seems litlle more complicated
> - heard of backup and maintenance problems
> - also some caching  problems.
>
> *DATABASE:*
>
> PROS:
> - schema changes litlle easier
> - backup and administration seems more robust
>
> CONS:
> - heard of vacuum problems
> - connection pooling is hard, as 100 shards would mean 100 pools
>
>
> So what is actually the right approach? If anyone could  shed some light
> on my issue.
>
> *Thanks*
>
>
>

*You might also want to consider GridSQL. IIRC it was originally developed
by EnterpriseDB. I saw a demo of it a few years ago and it was quite
impressive, *
*but I've had no interaction with it since, so you will have to judge for
yourself.*


*https://sourceforge.net/projects/gridsql/?source=navbar
*

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

Re: Information on savepoint requirement within transctions

[Robert Zenz]

On 29.01.2018 15:11, Alban Hertroys wrote:
> IMHO, the burden of explaining that is with those RDBMSes that don't
> behave properly:
> 
> If you start a transaction and something goes wrong in the process,
> the logical behaviour is to fail - the user will want to rollback to a
> sane state, doing any more work is rather pointless because of that.
> Allowing a commit at the end is dubious at best.

One could argue that automatically "undoing all broken things" (read: reverting
back to the state before the failing statement was executed) would be a feature
worth having. As far as I recall, that has also been brought up on the mailing
list. Though, I don't care particularly about it. I was just interested in the
documentation.

> That does not exclude PG from documenting this behaviour, but I'd have
> a look at the docs for those other vendors whether they perhaps
> documented their irregular transactional behaviour ;)

Heh, good luck. :)

> You didn't mention which RDBMSes behave like what you expected
> (probably from experience), but I seem to recall Oracle does odd stuff
> like that, as well as issuing a commit to all open transactions when
> any DDL happens or treating NULLs and empty literals as the same
> thing. Just to say that the "big names" aren't without flaws - they're
> kind of hard to fix when users probably depend on their behaviour
> though.

To reiterate my example (to get rid of any misconceptions):

begin transaction
insert row #1
insert row #2 (this fails)
insert row #3
commit

I've tested MySQL/MariaDB, Oracle, H2 and SQLite, all allow to continue a
transaction after a failed statement without user interaction (rows #1 and #3
are in the database after committing). PostgresSQL requires the manual rollback
to savepoint after a failed statement (obviously stores #1 and #3 in the
database if each insert is "wrapped" with a savepoint). MSSQL on the other hand
loses the complete state up to the failed statement and allows the user to
continue to use the transaction like nothing happened (only #3 is inserted when
committing). So, I think we can all agree who's the actually broken one here. ;)

Re: PG Sharding

[Thomas Boussekey]

Hello,

Facing the same situation, I'm considering 3 solutions:
- Sharding with postgres_xl (waiting for a Pg10 release)
- Sharding with citusdata (Release 7.2, compatible with Pg10 and
pg_partman, seems interesting)
- Partitioning with PG 10 native partitioning or pg_partman

With colleagues, we have tested the 3 scenarios.
Sharding looks interesting, but you have to apprehend its behaviour in case
of node loss, or cross-node queries.

Thomas

2018-01-29 15:44 GMT+01:00 Melvin Davidson :

>
>
> On Mon, Jan 29, 2018 at 9:34 AM, Matej  wrote:
>
>> Hi Everyone.
>>
>> We are looking at a rather large fin-tech installation. But as
>> scalability requirements are high we look at sharding of-course.
>>
>> I have looked at many sources for Postgresql sharding, but we are a
>> little confused as to shared with schema or databases or both.
>>
>>
>> So far our understanding:
>>
>> *SCHEMA.*
>>
>> PROS:
>> - seems native to PG
>> - backup seems easier
>> - connection pooling seems easier, as you can use same connection between
>> shard.
>>
>> CONS:
>> - schema changes seems litlle more complicated
>> - heard of backup and maintenance problems
>> - also some caching  problems.
>>
>> *DATABASE:*
>>
>> PROS:
>> - schema changes litlle easier
>> - backup and administration seems more robust
>>
>> CONS:
>> - heard of vacuum problems
>> - connection pooling is hard, as 100 shards would mean 100 pools
>>
>>
>> So what is actually the right approach? If anyone could  shed some light
>> on my issue.
>>
>> *Thanks*
>>
>>
>>
>
> *You might also want to consider GridSQL. IIRC it was originally developed
> by EnterpriseDB. I saw a demo of it a few years ago and it was quite
> impressive, *
> *but I've had no interaction with it since, so you will have to judge for
> yourself.*
>
>
> *https://sourceforge.net/projects/gridsql/?source=navbar
> *
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Re: Information on savepoint requirement within transctions

[Tom Lane]

Robert Zenz  writes:
> On 29.01.2018 15:11, Alban Hertroys wrote:
>> If you start a transaction and something goes wrong in the process,
>> the logical behaviour is to fail - the user will want to rollback to a
>> sane state, doing any more work is rather pointless because of that.
>> Allowing a commit at the end is dubious at best.

> One could argue that automatically "undoing all broken things" (read: 
> reverting
> back to the state before the failing statement was executed) would be a 
> feature
> worth having.

That behavior does exist, and so does documentation for it; you're just
looking in the wrong place.

Years ago (7.3 era, around 2002) we experimented with a server-side
GUC variable "AUTOCOMMIT", which switched from the implicit-commit-
if-you-don't-say-BEGIN behavior to implicitly-issue-BEGIN-so-you-
have-to-say-COMMIT-explicitly.  That was an unmitigated disaster:
flipping the setting broke just about all client applications.  After
spending many months trying to fix just the apps we ship with Postgres,
and getting pushback from users whose code broke with the alternate
setting, we gave up and removed the feature.  Instead we set project
policy that if you want to modify transactional behavior you have to
do it on the client side, where it doesn't risk breaking other apps.
Institutional memory around here is very long, so any time proposals to
change the server or wire-protocol behavior in this area come up, they
get batted down.

What we do have though is client-side support for appropriate behaviors.
In psql, see the AUTOCOMMIT and ON_ERROR_ROLLBACK control variables.
Other interfaces such as JDBC have their own ideas about how this ought
to work.

regards, tom lane

Which specific flags to use for bash client applications for DaVinci Resolve?

[Seth Goldin]

 Hello all,

I apologize if this is off-topic for this specific mailing list--if it is,
let me know, and I can post it to the right spot instead.

I'm a complete newbie with PostgreSQL, or any kind of database language for
that matter, but I'm trying to figure out how I might automate some
workflows.

I'm using a video post-production application called Blackmagic DaVinci
Resolve. It relies on PostgreSQL 9.5, but because its GUI is rather
limited, I created a bash script and launchd agent for ./pg_dump
, so that I could
backup databases automatically.

The bash script was based on a blog post from someone who had intimate
knowledge of PostgreSQL.

Using ./pg_dump for backups is great, and it exactly corresponds to
pressing DaVinci Resolve's "Backup" button in its GUI.

So, I'd love to automate a few more functions.

Whereas pressing "Backup" in the DaVinci Resolve GUI seems to correspond to
pg_dump , it
seems like:

   - Creating a database in the DaVinci Resolve GUI corresponds to createdb
   ; and
   - Pressing "Optimize" in the DaVinci Resolve GUI seems to correspond to
   reindexdb 
   and vacuumdb
   .

My issue is that I don't know what flags to use. Can any PostgreSQL gurus
inform me what flags I should include and omit?

I've already tried monkeying around in `postgresql.conf`, increasing the
logging level, hitting some GUI buttons and staring at `ps -ef` to try to
get a sense of how I might be able to run bash client applications, but I
was just left scratching my head.

The ultimate goal here is to come up with one simple little program to
create a proper database and immediately load "Optimize" and "Backup" bash
scripts right into launchd in one fell swoop.

Can anyone help me out by figuring out exactly what the right bash commands
would be for createdb, reindexdb, and vacuumdb? Maybe someone could examine
`ps -ef` while pressing the GUI buttons and inform what the equivalent bash
commands would be?

Thanks in advance!
-- Seth
-- 
S E T H   G O L D I N

Director of Production Technology and Workflow | Freethink

phone: +1.757.739.5424
web: freethinkmedia.com
twitter: @sethgoldin

Re: Information on savepoint requirement within transctions

[David G. Johnston]

On Mon, Jan 29, 2018 at 8:33 AM, Tom Lane  wrote:

> What we do have though is client-side support for appropriate behaviors.
> In psql, see the AUTOCOMMIT and ON_ERROR_ROLLBACK control variables.
> Other interfaces such as JDBC have their own ideas about how this ought
> to work.
>

​Not quite the same.  I think what people probably want is for psql to
recognize it is in a transaction and before sending a command to the server
for processing to precede it by sending "SAVEPOINT random()".  Then, before
returning the result of the command to the user issue either "RELEASE
SAVEPOINT" or "ROLLBACK TO SAVEPOINT" depending on whether the command
succeeded or failed.  Then report the result to the user.

Having a GUC to instruct the server to do that instead sounds appealing as
a user, or middle-ware writer, though I couldn't see doing it given today's
GUC mechanics for the same reason the AUTOCOMMIT GUC was removed.

David J.

Re: PG Sharding

[Matej]

Hi Thomas.

Thanks.

Also looked at those solutions:
- PGXL Am a ltille afraid we would be the test dummies. Did not hear of
many production installs.
- Citus seems heavily limited scalability vise, because of the master node
design.

Regarding  partitioning we are considering ourselves pg_pathman. Was hoping
on PG10 partitioning but currently not really many changes performance
vise.

Overall we are still considering manual APP/sharding as this seems to be
the most scalable approach which least added latency. The builtin solutions
seems to introduce extra lag and I am afraid of what to do when something
goes wrong. then it's not a black box anymore and you have to study the
details.

For node loss we plan a master -slave setup, and there will not be so many
cross shard queries.

BR
Matej


2018-01-29 16:15 GMT+01:00 Thomas Boussekey :

> Hello,
>
> Facing the same situation, I'm considering 3 solutions:
> - Sharding with postgres_xl (waiting for a Pg10 release)
> - Sharding with citusdata (Release 7.2, compatible with Pg10 and
> pg_partman, seems interesting)
> - Partitioning with PG 10 native partitioning or pg_partman
>
> With colleagues, we have tested the 3 scenarios.
> Sharding looks interesting, but you have to apprehend its behaviour in
> case of node loss, or cross-node queries.
>
> Thomas
>
> 2018-01-29 15:44 GMT+01:00 Melvin Davidson :
>
>>
>>
>> On Mon, Jan 29, 2018 at 9:34 AM, Matej  wrote:
>>
>>> Hi Everyone.
>>>
>>> We are looking at a rather large fin-tech installation. But as
>>> scalability requirements are high we look at sharding of-course.
>>>
>>> I have looked at many sources for Postgresql sharding, but we are a
>>> little confused as to shared with schema or databases or both.
>>>
>>>
>>> So far our understanding:
>>>
>>> *SCHEMA.*
>>>
>>> PROS:
>>> - seems native to PG
>>> - backup seems easier
>>> - connection pooling seems easier, as you can use same connection
>>> between shard.
>>>
>>> CONS:
>>> - schema changes seems litlle more complicated
>>> - heard of backup and maintenance problems
>>> - also some caching  problems.
>>>
>>> *DATABASE:*
>>>
>>> PROS:
>>> - schema changes litlle easier
>>> - backup and administration seems more robust
>>>
>>> CONS:
>>> - heard of vacuum problems
>>> - connection pooling is hard, as 100 shards would mean 100 pools
>>>
>>>
>>> So what is actually the right approach? If anyone could  shed some
>>> light on my issue.
>>>
>>> *Thanks*
>>>
>>>
>>>
>>
>> *You might also want to consider GridSQL. IIRC it was originally
>> developed by EnterpriseDB. I saw a demo of it a few years ago and it was
>> quite impressive, *
>> *but I've had no interaction with it since, so you will have to judge for
>> yourself.*
>>
>>
>> *https://sourceforge.net/projects/gridsql/?source=navbar
>> *
>>
>> --
>> *Melvin Davidson*
>> I reserve the right to fantasize.  Whether or not you
>> wish to share my fantasy is entirely up to you.
>>
>
>

Re: Information on savepoint requirement within transctions

[Robert Zenz]

On 29.01.2018 16:33, Tom Lane wrote:
> That behavior does exist, and so does documentation for it; you're just
> looking in the wrong place.
> 
> Years ago (7.3 era, around 2002) we experimented with a server-side
> GUC variable "AUTOCOMMIT", which switched from the implicit-commit-
> if-you-don't-say-BEGIN behavior to implicitly-issue-BEGIN-so-you-
> have-to-say-COMMIT-explicitly.  That was an unmitigated disaster:
> flipping the setting broke just about all client applications.  After
> spending many months trying to fix just the apps we ship with Postgres,
> and getting pushback from users whose code broke with the alternate
> setting, we gave up and removed the feature.  Instead we set project
> policy that if you want to modify transactional behavior you have to
> do it on the client side, where it doesn't risk breaking other apps.
> Institutional memory around here is very long, so any time proposals to
> change the server or wire-protocol behavior in this area come up, they
> get batted down.
> 
> What we do have though is client-side support for appropriate behaviors.
> In psql, see the AUTOCOMMIT and ON_ERROR_ROLLBACK control variables.
> Other interfaces such as JDBC have their own ideas about how this ought
> to work.

Very interesting. However, I'm talking explicitly about the behavior that occurs
when AUTOCOMMIT is switched off and a statement fails.

Most curiously, you already did such a feature (of what I was talking about)
request in 2007:
https://www.postgresql.org/message-id/flat/11539.1177352713%40sss.pgh.pa.us#11539.1177352...@sss.pgh.pa.us

Re: Information on savepoint requirement within transctions

[Tom Lane]

"David G. Johnston"  writes:
> On Mon, Jan 29, 2018 at 8:33 AM, Tom Lane  wrote:
>> What we do have though is client-side support for appropriate behaviors.
>> In psql, see the AUTOCOMMIT and ON_ERROR_ROLLBACK control variables.

> Not quite the same.  I think what people probably want is for psql to
> recognize it is in a transaction and before sending a command to the server
> for processing to precede it by sending "SAVEPOINT random()".  Then, before
> returning the result of the command to the user issue either "RELEASE
> SAVEPOINT" or "ROLLBACK TO SAVEPOINT" depending on whether the command
> succeeded or failed.  Then report the result to the user.

Which part of that isn't implemented by ON_ERROR_ROLLBACK?

regards, tom lane

Re: PG Sharding

[Thomas Boussekey]

Hello Matej,

I found some interesting implementation of postgres_XL at TenCent(WeChat)
and Javelin. You can find video capture of conferences of IT people from
these companies. Moreover, I attended to PgConf.eu at Warsaw in October,
and The ESA (European Space Agency) made a lightning talk on their Gaïa
project using a 8-datanode cluster.

I agree with you about the SPOF for the master on the citusdata
architecture. Yet, implementation is very easy, and it's an extension in
Pg10! But I had to fire many foreign into my data model to be able to
install my tables into citusdata.

2 years ago, I was looking for a partitioning extension, pg_partman was
mature, whereas pg_pathman was in version 0.4 and many issues in their
github were written in Cyrillic, and I'm French ;-)... So I had a closer
look at pg_partman.
I'm using pg_partman in production now.

2018-01-29 16:49 GMT+01:00 Matej :

> Hi Thomas.
>
> Thanks.
>
> Also looked at those solutions:
> - PGXL Am a ltille afraid we would be the test dummies. Did not hear of
> many production installs.
> - Citus seems heavily limited scalability vise, because of the master node
> design.
>
> Regarding  partitioning we are considering ourselves pg_pathman. Was
> hoping on PG10 partitioning but currently not really many changes
> performance vise.
>
> Overall we are still considering manual APP/sharding as this seems to be
> the most scalable approach which least added latency. The builtin solutions
> seems to introduce extra lag and I am afraid of what to do when something
> goes wrong. then it's not a black box anymore and you have to study the
> details.
>
> For node loss we plan a master -slave setup, and there will not be so many
> cross shard queries.
>
> BR
> Matej
>
>
> 2018-01-29 16:15 GMT+01:00 Thomas Boussekey :
>
>> Hello,
>>
>> Facing the same situation, I'm considering 3 solutions:
>> - Sharding with postgres_xl (waiting for a Pg10 release)
>> - Sharding with citusdata (Release 7.2, compatible with Pg10 and
>> pg_partman, seems interesting)
>> - Partitioning with PG 10 native partitioning or pg_partman
>>
>> With colleagues, we have tested the 3 scenarios.
>> Sharding looks interesting, but you have to apprehend its behaviour in
>> case of node loss, or cross-node queries.
>>
>> Thomas
>>
>> 2018-01-29 15:44 GMT+01:00 Melvin Davidson :
>>
>>>
>>>
>>> On Mon, Jan 29, 2018 at 9:34 AM, Matej  wrote:
>>>
 Hi Everyone.

 We are looking at a rather large fin-tech installation. But as
 scalability requirements are high we look at sharding of-course.

 I have looked at many sources for Postgresql sharding, but we are a
 little confused as to shared with schema or databases or both.


 So far our understanding:

 *SCHEMA.*

 PROS:
 - seems native to PG
 - backup seems easier
 - connection pooling seems easier, as you can use same connection
 between shard.

 CONS:
 - schema changes seems litlle more complicated
 - heard of backup and maintenance problems
 - also some caching  problems.

 *DATABASE:*

 PROS:
 - schema changes litlle easier
 - backup and administration seems more robust

 CONS:
 - heard of vacuum problems
 - connection pooling is hard, as 100 shards would mean 100 pools


 So what is actually the right approach? If anyone could  shed some
 light on my issue.

 *Thanks*



>>>
>>> *You might also want to consider GridSQL. IIRC it was originally
>>> developed by EnterpriseDB. I saw a demo of it a few years ago and it was
>>> quite impressive, *
>>> *but I've had no interaction with it since, so you will have to judge
>>> for yourself.*
>>>
>>>
>>> *https://sourceforge.net/projects/gridsql/?source=navbar
>>> *
>>>
>>> --
>>> *Melvin Davidson*
>>> I reserve the right to fantasize.  Whether or not you
>>> wish to share my fantasy is entirely up to you.
>>>
>>
>>
>

Re: Information on savepoint requirement within transctions

[David G. Johnston]

On Mon, Jan 29, 2018 at 9:00 AM, Tom Lane  wrote:

> "David G. Johnston"  writes:
> > On Mon, Jan 29, 2018 at 8:33 AM, Tom Lane  wrote:
> >> What we do have though is client-side support for appropriate behaviors.
> >> In psql, see the AUTOCOMMIT and ON_ERROR_ROLLBACK control variables.
>
> > Not quite the same.  I think what people probably want is for psql to
> > recognize it is in a transaction and before sending a command to the
> server
> > for processing to precede it by sending "SAVEPOINT random()".  Then,
> before
> > returning the result of the command to the user issue either "RELEASE
> > SAVEPOINT" or "ROLLBACK TO SAVEPOINT" depending on whether the command
> > succeeded or failed.  Then report the result to the user.
>
> Which part of that isn't implemented by ON_ERROR_ROLLBACK?
>

​My turn to fail to re-read the docs :(

David J.
​

Re: Which specific flags to use for bash client applications for DaVinci Resolve?

[Steve Crawford]

Interesting. I was unaware that Resolve used PostgreSQL. I looked at
Resolve a year or two ago but the Linux version was still pricey while the
basic Mac/Windows versions could be downloaded for free. Looks like there
may be a free version for Linux, now. I'll have to check it out.

In any case, you may find the answers in online documentation.

pgdump: https://www.postgresql.org/docs/9.5/static/backup-dump.html
vacuumdb: https://www.postgresql.org/docs/9.5/static/app-vacuumdb.html
reindexdb: https://www.postgresql.org/docs/9.5/static/app-reindexdb.html

Note that a properly configured PostgreSQL database may require little or
no manual vacuum as the autovacuum process will handle things well most of
the time.

>From poking around on the forums it appears that newer versions of Davinci
Resolve don't install the database by default. I'm not sure if it is an
option necessary for some features or if they have changed from using
PostgreSQL for internal data.

If the manuals don't get you far enough and you have more questions please
be sure to include the versions of Resolve and your OS in your question.

Cheers,
Steve




On Mon, Jan 29, 2018 at 7:46 AM Seth Goldin  wrote:

> Hello all,
>
> I apologize if this is off-topic for this specific mailing list--if it is,
> let me know, and I can post it to the right spot instead.
>
> I'm a complete newbie with PostgreSQL, or any kind of database language
> for that matter, but I'm trying to figure out how I might automate some
> workflows.
>
> I'm using a video post-production application called Blackmagic DaVinci
> Resolve. It relies on PostgreSQL 9.5, but because its GUI is rather
> limited, I created a bash script and launchd agent for ./pg_dump
> , so that I
> could backup databases automatically.
>
> The bash script was based on a blog post from someone who had intimate
> knowledge of PostgreSQL.
>
> Using ./pg_dump for backups is great, and it exactly corresponds to
> pressing DaVinci Resolve's "Backup" button in its GUI.
>
> So, I'd love to automate a few more functions.
>
> Whereas pressing "Backup" in the DaVinci Resolve GUI seems to correspond
> to pg_dump ,
> it seems like:
>
>- Creating a database in the DaVinci Resolve GUI corresponds to
>createdb ;
>and
>- Pressing "Optimize" in the DaVinci Resolve GUI seems to correspond
>to reindexdb
> and
>vacuumdb 
>.
>
> My issue is that I don't know what flags to use. Can any PostgreSQL gurus
> inform me what flags I should include and omit?
>
> I've already tried monkeying around in `postgresql.conf`, increasing the
> logging level, hitting some GUI buttons and staring at `ps -ef` to try to
> get a sense of how I might be able to run bash client applications, but I
> was just left scratching my head.
>
> The ultimate goal here is to come up with one simple little program to
> create a proper database and immediately load "Optimize" and "Backup" bash
> scripts right into launchd in one fell swoop.
>
> Can anyone help me out by figuring out exactly what the right bash
> commands would be for createdb, reindexdb, and vacuumdb? Maybe someone
> could examine `ps -ef` while pressing the GUI buttons and inform what the
> equivalent bash commands would be?
>
> Thanks in advance!
> -- Seth
> --
> S E T H   G O L D I N
>
> Director of Production Technology and Workflow | Freethink
>
> phone: +1.757.739.5424 <(757)%20739-5424>
> web: freethinkmedia.com
> twitter: @sethgoldin
>

Re: [GENERAL] Matching statement and duration log lines

[Bruce Momjian]

On Mon, Oct 23, 2017 at 01:06:07PM +, Popov Aleksey wrote:
> Hello!
> 
> I am sending PG logs to Elasticsearch and want to merge a line with statement
> and a line with duration into one document.
> Having a statement line and a duration line, can I assume that if a session 
> ids
> (%c) of these lines match,
> and numbers of log lines (%l) are consecutive, then the duration line belongs
> to statement line?

Very late reply here, but setting log_min_duration_statement to zero
will give you those on one line, but the statement will only be output
when it completes, rather than when it starts, which is what
log_statement does.

And, yes, using %c to match up lines will work too.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +

Re: PG Sharding

[Konstantin Gredeskoul]

When I worked at Wanelo, we built a sharded data store for a giant join table 
with 4B records and growing. We too could not find any generic sharding 
solution at the level of postgresql, and after some research decided to 
implement it in the application.

As it was written in ruby, here are some resources to point out:

https://github.com/taskrabbit/makara
https://github.com/wanelo/sequel-schema-sharding

The service used Sequel gem (not active record from Rails) and has been working 
very stable for us.  I'm not sure if your project is in ruby or not, but wanted 
to give it a shout.

Another good resource is this ActivityFeed library, which relies on pluggable 
backends to support very high write to maintain precomputed activity feeds for 
each user using Redis. I'm a bit fan of moving things out of postgresql that 
don't have to be there :)

https://github.com/kigster/simple-feed

Best,
Konstantin

__
Konstantin Gredeskoul
https://kig.re/
https://reinvent.one/
(415) 265-1054

From: Matej 
Sent: Monday, January 29, 2018 7:49:19 AM
To: Thomas Boussekey
Cc: Melvin Davidson; pgsql-general@lists.postgresql.org
Subject: Re: PG Sharding

Hi Thomas.

Thanks.

Also looked at those solutions:
- PGXL Am a ltille afraid we would be the test dummies. Did not hear of many 
production installs.
- Citus seems heavily limited scalability vise, because of the master node 
design.

Regarding  partitioning we are considering ourselves pg_pathman. Was hoping on 
PG10 partitioning but currently not really many changes performance vise.

Overall we are still considering manual APP/sharding as this seems to be the 
most scalable approach which least added latency. The builtin solutions seems 
to introduce extra lag and I am afraid of what to do when something goes wrong. 
then it's not a black box anymore and you have to study the details.

For node loss we plan a master -slave setup, and there will not be so many 
cross shard queries.

BR
Matej


2018-01-29 16:15 GMT+01:00 Thomas Boussekey 
mailto:thomas.bousse...@gmail.com>>:
Hello,

Facing the same situation, I'm considering 3 solutions:
- Sharding with postgres_xl (waiting for a Pg10 release)
- Sharding with citusdata (Release 7.2, compatible with Pg10 and pg_partman, 
seems interesting)
- Partitioning with PG 10 native partitioning or pg_partman

With colleagues, we have tested the 3 scenarios.
Sharding looks interesting, but you have to apprehend its behaviour in case of 
node loss, or cross-node queries.

Thomas

2018-01-29 15:44 GMT+01:00 Melvin Davidson 
mailto:melvin6...@gmail.com>>:


On Mon, Jan 29, 2018 at 9:34 AM, Matej 
mailto:gma...@gmail.com>> wrote:
Hi Everyone.

We are looking at a rather large fin-tech installation. But as scalability 
requirements are high we look at sharding of-course.

I have looked at many sources for Postgresql sharding, but we are a little 
confused as to shared with schema or databases or both.


So far our understanding:

SCHEMA.

PROS:
- seems native to PG
- backup seems easier
- connection pooling seems easier, as you can use same connection between shard.

CONS:
- schema changes seems litlle more complicated
- heard of backup and maintenance problems
- also some caching  problems.

DATABASE:

PROS:
- schema changes litlle easier
- backup and administration seems more robust

CONS:
- heard of vacuum problems
- connection pooling is hard, as 100 shards would mean 100 pools


So what is actually the right approach? If anyone could  shed some light on my 
issue.

Thanks



You might also want to consider GridSQL. IIRC it was originally developed by 
EnterpriseDB. I saw a demo of it a few years ago and it was quite impressive,
but I've had no interaction with it since, so you will have to judge for 
yourself.

https://sourceforge.net/projects/gridsql/?source=navbar

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you. 
[http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif]

pgpool Connections Distributions Among Nodes

[Kumar, Virendra]

Hi Gurus,

We have 4-node cluster (1 master and 3 hot standby).  We are using pgpool as 
load balancer. We have an observation where if application requests for 3 
connections, pgpool connects to all 4 servers and I see 3 connections on each 
of them. I was expecting it have a total of 3 connections from either of 4 
servers but I can easily see 12 connections in all.

Can somebody shed some light on it.

Please let me know if you need more information.

Regards,
Virendra




This message is intended only for the use of the addressee and may contain
information that is PRIVILEGED AND CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified that any
dissemination of this communication is strictly prohibited. If you have
received this communication in error, please erase all copies of the message
and its attachments and notify the sender immediately. Thank you.

Using AWS ephemeral SSD storage for production database workload?

[Pritam Barhate]

Hi everyone,

As you may know, EBS volumes though durable are very costly when you
need provisioned IOPS. As opposed to this AWS instance attached ephemeral SSD
is very fast but isn't durable.

I have come across some ideas on the Internet where people hinted at
running production PostgreSQL workloads on AWS ephemeral SSD storage.
Generally, this involves shipping WAL logs continuously to S3 and keeping
an async read replica in another AWS availability zone. Worst case scenario
in such deployment is data loss of a few seconds. But beyond this the
details are sketchy.

Have you come across such a deployment? What are some best practices that
need to be followed to pull this through without significant data loss?
Even though WAL logs are being shipped to S3, in case of loss of both the
instances, the restore time is going be quite a bit for databases of a few
hundred GBs.

Just to be clear, I am not planning anything like this, anytime soon :-)
But I am curious about trade-offs of such a deployment. Any concrete
information in this aspect is well appreciated.

Regards,

Pritam.

Re: PG Sharding

[Rakesh Kumar]

> On Jan 29, 2018, at 09:34 , Matej  wrote:
> 
> Hi Everyone.
> 
> We are looking at a rather large fin-tech installation. But as scalability 
> requirements are high we look at sharding of-course. 
> 
> I have looked at many sources for Postgresql sharding, but we are a little 
> confused as to shared with schema or databases or both. 
> 
> 
> So far our understanding:
> 
> SCHEMA.
> 
> PROS:
> - seems native to PG
> - backup seems easier
> - connection pooling seems easier, as you can use same connection between 
> shard.
> 
> CONS:
> - schema changes seems litlle more complicated
> - heard of backup and maintenance problems
> - also some caching  problems.
> 
> DATABASE:
> 
> PROS:
> - schema changes litlle easier
> - backup and administration seems more robust
> 
> CONS:
> - heard of vacuum problems
> - connection pooling is hard, as 100 shards would mean 100 pools
> 
> 
> So what is actually the right approach? If anyone could  shed some light on 
> my issue.

From your description it seems your requirement is more of multi tenancy in a 
non distributed env, rather than distributed Sharding env.

Re: pgpool Connections Distributions Among Nodes

[Adrian Klaver]

On 01/29/2018 08:19 AM, Kumar, Virendra wrote:

Hi Gurus,

We have 4-node cluster (1 master and 3 hot standby).  We are using 
pgpool as load balancer. We have an observation where if application 
requests for 3 connections, pgpool connects to all 4 servers and I see 3 
connections on each of them. I was expecting it have a total of 3 
connections from either of 4 servers but I can easily see 12 connections 
in all.


Can somebody shed some light on it.

Please let me know if you need more information.


I would say the contents of your config file at the least.



Regards,

Virendra






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

Re: pgpool Connections Distributions Among Nodes

[Glauco Torres]

>
>
>
>
> We have 4-node cluster (1 master and 3 hot standby).  We are using pgpool
> as load balancer. We have an observation where if application requests for
> 3 connections, pgpool connects to all 4 servers and I see 3 connections on
> each of them. I was expecting it have a total of 3 connections from either
> of 4 servers but I can easily see 12 connections in all.
>
>
>
> Can somebody shed some light on it.
>
>
>
> Please let me know if you need more information.
>
>
>
Hello,

Which version pgpool you are using?

How did you balance the reading by pgpool? Should all readings go to the
replicas? or reading goes to all nodes?

Regards,

Glauco Torres

Re: Which specific flags to use for bash client applications for DaVinci Resolve?

[Seth Goldin]

Thanks, Steve.

Yes, Blackmagic Design changed the pricing last year, and now ship a free
Linux version.

On Windows and Mac, it uses a "disk" database by default, which is a
special configuration of files and folders on the boot drive.

However, on Windows, Mac, and Linux, a PostgreSQL database can be
configured on a local network, for more advanced features, like having
different users on different workstations collaborating in the same
project, or sending different render jobs to different workstations.

The Resolve manual doesn't have any mention of automating the PostgreSQL
functions--the ./pg_dump script I mentioned earlier was just from someone
who was familiar with PostgreSQL administration.

I don't know what special flags to use in the bash commands, which is why I
came here.

I'm using DaVinci Resolve Studio 14.2.1.007 on macOS Sierra 10.12.6 with
PostgreSQL 9.5.9.

Thanks,
-- Seth.

On Mon, Jan 29, 2018 at 11:12 AM Steve Crawford <
scrawf...@pinpointresearch.com> wrote:

> Interesting. I was unaware that Resolve used PostgreSQL. I looked at
> Resolve a year or two ago but the Linux version was still pricey while the
> basic Mac/Windows versions could be downloaded for free. Looks like there
> may be a free version for Linux, now. I'll have to check it out.
>
> In any case, you may find the answers in online documentation.
>
> pgdump: https://www.postgresql.org/docs/9.5/static/backup-dump.html
> vacuumdb: https://www.postgresql.org/docs/9.5/static/app-vacuumdb.html
> reindexdb: https://www.postgresql.org/docs/9.5/static/app-reindexdb.html
>
> Note that a properly configured PostgreSQL database may require little or
> no manual vacuum as the autovacuum process will handle things well most of
> the time.
>
> From poking around on the forums it appears that newer versions of Davinci
> Resolve don't install the database by default. I'm not sure if it is an
> option necessary for some features or if they have changed from using
> PostgreSQL for internal data.
>
> If the manuals don't get you far enough and you have more questions please
> be sure to include the versions of Resolve and your OS in your question.
>
> Cheers,
> Steve
>
>
>
>
> On Mon, Jan 29, 2018 at 7:46 AM Seth Goldin  wrote:
>
>> Hello all,
>>
>> I apologize if this is off-topic for this specific mailing list--if it
>> is, let me know, and I can post it to the right spot instead.
>>
>> I'm a complete newbie with PostgreSQL, or any kind of database language
>> for that matter, but I'm trying to figure out how I might automate some
>> workflows.
>>
>> I'm using a video post-production application called Blackmagic DaVinci
>> Resolve. It relies on PostgreSQL 9.5, but because its GUI is rather
>> limited, I created a bash script and launchd agent for ./pg_dump
>> , so that I
>> could backup databases automatically.
>>
>> The bash script was based on a blog post from someone who had intimate
>> knowledge of PostgreSQL.
>>
>> Using ./pg_dump for backups is great, and it exactly corresponds to
>> pressing DaVinci Resolve's "Backup" button in its GUI.
>>
>> So, I'd love to automate a few more functions.
>>
>> Whereas pressing "Backup" in the DaVinci Resolve GUI seems to correspond
>> to pg_dump ,
>> it seems like:
>>
>>- Creating a database in the DaVinci Resolve GUI corresponds to
>>createdb
>>; and
>>- Pressing "Optimize" in the DaVinci Resolve GUI seems to correspond
>>to reindexdb
>> and
>>vacuumdb
>>.
>>
>> My issue is that I don't know what flags to use. Can any PostgreSQL gurus
>> inform me what flags I should include and omit?
>>
>> I've already tried monkeying around in `postgresql.conf`, increasing the
>> logging level, hitting some GUI buttons and staring at `ps -ef` to try to
>> get a sense of how I might be able to run bash client applications, but I
>> was just left scratching my head.
>>
>> The ultimate goal here is to come up with one simple little program to
>> create a proper database and immediately load "Optimize" and "Backup" bash
>> scripts right into launchd in one fell swoop.
>>
>> Can anyone help me out by figuring out exactly what the right bash
>> commands would be for createdb, reindexdb, and vacuumdb? Maybe someone
>> could examine `ps -ef` while pressing the GUI buttons and inform what the
>> equivalent bash commands would be?
>>
>> Thanks in advance!
>> -- Seth
>> --
>> S E T H   G O L D I N
>>
>> Director of Production Technology and Workflow | Freethink
>>
>> phone: +1.757.739.5424 <(757)%20739-5424>
>> web: freethinkmedia.com
>> twitter: @sethgoldin
>>
> --
S E T H   G O L D I N
Director of Production Technology and Workflow | Freethink

web: freethink.com 

RE: pgpool Connections Distributions Among Nodes

[Kumar, Virendra]

Attached is config file.


[pgpool@usdf23v0550 ~]$ pgpool -v
pgpool-II version 3.7.1 (amefuriboshi)

Reading should go to all nodes.

Regards,
Virendra
From: Glauco Torres [mailto:torres.gla...@gmail.com]
Sent: Monday, January 29, 2018 11:53 AM
To: Kumar, Virendra
Cc: pgsql-gene...@postgresql.org
Subject: Re: pgpool Connections Distributions Among Nodes



We have 4-node cluster (1 master and 3 hot standby).  We are using pgpool as 
load balancer. We have an observation where if application requests for 3 
connections, pgpool connects to all 4 servers and I see 3 connections on each 
of them. I was expecting it have a total of 3 connections from either of 4 
servers but I can easily see 12 connections in all.

Can somebody shed some light on it.

Please let me know if you need more information.


Hello,
Which version pgpool you are using?
How did you balance the reading by pgpool? Should all readings go to the 
replicas? or reading goes to all nodes?
Regards,
Glauco Torres



This message is intended only for the use of the addressee and may contain
information that is PRIVILEGED AND CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified that any
dissemination of this communication is strictly prohibited. If you have
received this communication in error, please erase all copies of the message
and its attachments and notify the sender immediately. Thank you.


pgpool.conf
Description: pgpool.conf

Re: Using AWS ephemeral SSD storage for production database workload?

[Tomas Vondra]

On 01/29/2018 05:41 PM, Pritam Barhate wrote:
> Hi everyone, 
> 
> As you may know, EBS volumes though durable are very costly when you 
> need provisioned IOPS. As opposed to this AWS instance attached
> ephemeral SSD is very fast but isn't durable.
> 
> I have come across some ideas on the Internet where people hinted at 
> running production PostgreSQL workloads on AWS ephemeral SSD
> storage. Generally, this involves shipping WAL logs continuously to
> S3 and keeping an async read replica in another AWS availability
> zone. Worst case scenario in such deployment is data loss of a few
> seconds. But beyond this the details are sketchy.
> 

Both log shipping and async replication are ancient features, and should
be well understood. What exactly is unclear?

> Have you come across such a deployment? What are some best practices 
> that need to be followed to pull this through without significant
> data loss? Even though WAL logs are being shipped to S3, in case of
> loss of both the instances, the restore time is going be quite a bit
> for databases of a few hundred GBs.
> 

Pretty much everyone who is serious about HA is running such cluster. If
they can't afford any data loss, they use synchronous replicas instead.
That's a basic latency-durability trade-off.

> Just to be clear, I am not planning anything like this, anytime soon
> :-) But I am curious about trade-offs of such a deployment. Any
> concrete information in this aspect is well appreciated.
> 

Pretty much everyone is using such architecture (primary + streaming
replicas) nowadays, so it's a reasonably well understood scenario. But
it's really unclear what kind of information you expect to get, or how
much time have you spent reading about this.

There is quite a bit of information in the official docs, although maybe
a bit too low level - it certainly gives you the building blocks instead
of a complete solution. There are also books like [1] for example.

And finally there are tools that help with managing such clusters, like
for example [2]. Not only it's rather bad idea to implement this on your
own (bugs, unnecessary effort) but the tools also show how to do stuff.

[1]
https://www.packtpub.com/big-data-and-business-intelligence/postgresql-replication-second-edition

[2] https://repmgr.org/

regards

-- 
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Using AWS ephemeral SSD storage for production database workload?

[Pritam Barhate]

>> Both log shipping and async replication are ancient features, and should
>> be well understood. What exactly is unclear?

I know about these and I know how to operate them also. The only part I am
concerned about is the ephemeral storage. The risk appetite around it and
the steps people take in order to ensure no "serious" data is lost when
both the primary and the standby are lost (very unlikely when both are in
different AZ but still possible.).  I was just wondering if there is any
secret sauce (like some wisdom that comes only from operating a real-world
deployment) to it. Even Heroku seems to be using PIOS (
https://devcenter.heroku.com/articles/heroku-postgres-production-tier-technical-characterization)
and these guys created WAL-E. Anyways I did learn some new things from
Manuel's response.

In short, I am just trying to learn from other people's experience.

Thanks for all the information.

Pritam.


On Mon, Jan 29, 2018 at 11:02 PM, Tomas Vondra  wrote:

>
>
> On 01/29/2018 05:41 PM, Pritam Barhate wrote:
> > Hi everyone,
> >
> > As you may know, EBS volumes though durable are very costly when you
> > need provisioned IOPS. As opposed to this AWS instance attached
> > ephemeral SSD is very fast but isn't durable.
> >
> > I have come across some ideas on the Internet where people hinted at
> > running production PostgreSQL workloads on AWS ephemeral SSD
> > storage. Generally, this involves shipping WAL logs continuously to
> > S3 and keeping an async read replica in another AWS availability
> > zone. Worst case scenario in such deployment is data loss of a few
> > seconds. But beyond this the details are sketchy.
> >
>
> Both log shipping and async replication are ancient features, and should
> be well understood. What exactly is unclear?
>
> > Have you come across such a deployment? What are some best practices
> > that need to be followed to pull this through without significant
> > data loss? Even though WAL logs are being shipped to S3, in case of
> > loss of both the instances, the restore time is going be quite a bit
> > for databases of a few hundred GBs.
> >
>
> Pretty much everyone who is serious about HA is running such cluster. If
> they can't afford any data loss, they use synchronous replicas instead.
> That's a basic latency-durability trade-off.
>
> > Just to be clear, I am not planning anything like this, anytime soon
> > :-) But I am curious about trade-offs of such a deployment. Any
> > concrete information in this aspect is well appreciated.
> >
>
> Pretty much everyone is using such architecture (primary + streaming
> replicas) nowadays, so it's a reasonably well understood scenario. But
> it's really unclear what kind of information you expect to get, or how
> much time have you spent reading about this.
>
> There is quite a bit of information in the official docs, although maybe
> a bit too low level - it certainly gives you the building blocks instead
> of a complete solution. There are also books like [1] for example.
>
> And finally there are tools that help with managing such clusters, like
> for example [2]. Not only it's rather bad idea to implement this on your
> own (bugs, unnecessary effort) but the tools also show how to do stuff.
>
> [1]
> https://www.packtpub.com/big-data-and-business-intelligence/postgresql-
> replication-second-edition
>
> [2] https://repmgr.org/
>
> regards
>
> --
> Tomas Vondra  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

EXPLAIN BUFFERS: dirtied

[Vitaliy Garnashevich]

Hi,

In EXPLAIN (ANALYZE, BUFFERS) for a SELECT query, I see the following 
statistics under an Index Scan node:


Buffers: shared hit=8357288 read=6165444 dirtied=44820 written=5590

As far as I understand, that's the statistics for accesses to shared 
buffers during the query:

- hit = required page was already in shared buffers
- read = required page was not in shared buffers, and was loaded from 
disk (from filesystem cache)
- written = while loading the required page, there was no free space for 
it in shared buffers, so some other dirty page was evicted from shared 
buffers and was written to disk (to filesystem cache), to free some 
space and to load the required page


But what is "dirtied" statistics? When a SELECT query could make pages 
dirty?


Regards,
Vitaliy

Re: EXPLAIN BUFFERS: dirtied

[Tom Lane]

Vitaliy Garnashevich  writes:
> But what is "dirtied" statistics? When a SELECT query could make pages 
> dirty?

Setting hint bits on recently-committed rows.

regards, tom lane

Re: pg 10.1 missing libpq in postgresql-devel

[support-tiger]

On 01/27/2018 06:43 PM, Adrian Klaver wrote:


On 01/27/2018 04:34 PM, support-tiger wrote:

sorry for delay but ran some tests on older version pg gem - still fails


With what error message?

What Ruby gem?



it fails on building the config file with simply missing libpq-fe.h, 
cannot find libpq-fe.h


Well it is there:

rpm -qlp postgresql10-devel-10.1-1PGDG.f27.x86_64.rpm | grep libpq-fe.h
warning: postgresql10-devel-10.1-1PGDG.f27.x86_64.rpm: Header V4 
DSA/SHA1 Signature, key ID 442df0f8: NOKEY

/usr/pgsql-10/include/libpq-fe.h



I suspect it is a problem with using the pgdg repository and that 
postgresql get "10" added to them ie postgresql10-devel and maybe the 
gem code does not recognize that.   Since vers 10 is not breaking it 
does not make sense (i guess fedora)  to rename vers 10 with the "10" 
- maybe they just love the python 2 / 3 fiasco and want to extend it 
to postgresql.


Previous versions used version numbering also:

https://yum.postgresql.org/9.6/fedora/fedora-26-x86_64/

postgresql96-devel-9.6.6-1PGDG.f26.x86_64.rpm

My guess it that the config script for the gem is not taking into 
account that Postgres changed from a three part versioning system 
X.Y.z to a two part system X.y(where lower case is minor version) from 
9.6.0 to 10.1.


could be, cannot find the config file in the repository - specifies to 
use a pg-config file to build the gem but no trace of such a config file


thks anyway for your help.  had emailed the pg gem leads but no response 
and github issues are blocked (duh)  - can't depend on this -will just 
make a faster transition to Crystal - the pg shard in Crystal loaded 
okay on Fedora with the 10.1












On 01/25/2018 03:59 PM, Adrian Klaver wrote:

On 01/25/2018 12:20 PM, support-tiger wrote:
running postgresql 10.1 on Fedora 27. Cannot install ruby pg gem 
due to missing libpq-fe.h file. This is usually found in 
postgresql-devel but 


What was the error message returned when you tried to install 
originally?


it is not in fedora 27 postgresql10-devel. If install 
postgresql-devel (9.6) the gem installs without problem but afraid 
this will interfere with 10.1  How can we install the necessary 
libpq-fe.h  ? thks













--
Support Dept
Tiger Nassau, Inc.
www.tigernassau.com
406-624-9310

Re: EXPLAIN BUFFERS: dirtied

[Vitaliy Garnashevich]

I've read this article: https://wiki.postgresql.org/wiki/Hint_Bits

It says:

A plain SELECT, count(*), or VACUUM on the entire table will check 
every tuple for visibility and set its hint bits. 


Suppose, a new page was created using many INSERTs, and then was written 
to disk during a checkpoint. There were no SELECTs or VACUUM on the page 
or table yet. Will the following SELECT of one tuple from the page 
update hint bits for ALL tuples on the page? Is that correct?


When a page is initially created and then is being written to disk 
during a checkpoint, does checkpoint writer update the hint bits before 
writing the page, or the following SELECT/VACUUM will have to do that 
(possibly loading/updating/writing the page again)?


Regards,
Vitaliy

On 2018-01-29 20:38, Tom Lane wrote:

Vitaliy Garnashevich  writes:

But what is "dirtied" statistics? When a SELECT query could make pages
dirty?

Setting hint bits on recently-committed rows.

regards, tom lane

Re: Using AWS ephemeral SSD storage for production database workload?

[Steven Lembark]

On Mon, 29 Jan 2018 23:27:32 +0530
Pritam Barhate  wrote:

> In short, I am just trying to learn from other people's experience.

This is identical to solutions that use tmpfs on linux for 
database storage or dealing with a fully failed storage 
system. Think about what you'd do if a RAID controller 
fried and botchd your entire array at once. You'll feel
just the same way if a box using ephemeral storage goes
down.

Your application needs to handle restarting transactions
and either a reverse proxy/load-balancer or client-side
switchover.

Depending on your tolerance for data loss you might need
three servers up, on as a secondary failover if the primary
fails so that you (pretty much) always have two servers up
to maintain the data. The last server only has to last long
enough for a restart and recovery so it might not have to
be very heavy duty, it's main purpose is to keep the database
alive long enough to recover the "real" server.

Q: Why not just use RDS?

It'll be simpler.

-- 
Steven Lembark   1505 National Ave
Workhorse Compuing Rockford, IL 61103
lemb...@wrkhors.com+1 888 359 3508

Re: Using AWS ephemeral SSD storage for production database workload?

[Paul A Jungwirth]

> I have come across some ideas on the Internet
> where people hinted at running production PostgreSQL workloads
> on AWS ephemeral SSD storage.

I think people were more serious about that before AWS introduced
PIOPS. I wouldn't do this unless I had streaming replication to a
standby, plus regular backups (e.g. with WAL-E). Actually that's what
I use even with regular EBS volumes, and it's not hard to set up. The
standby gives you fast failover/recovery, and WAL-E gives you an extra
layer of insurance.

Another worry about ephemeral storage is that you can't add more, and
the amount you get depends on the instance type. Also it seems like
modern instances don't come with as much ephemeral storage as they
used to, although maybe that impression is mistaken.

I agree that PIOPS is still expensive though. Some people get around
that by running a RAID0 array of gp2 EBS volumes. The conversation at
https://news.ycombinator.com/item?id=13842044 has some details. I've
set it up for one client, and it wasn't too bad. It's been running
fine for 6 months or so.

Paul

Re: pg 10.1 missing libpq in postgresql-devel

[Adrian Klaver]

On 01/29/2018 11:18 AM, support-tiger wrote:

On 01/27/2018 06:43 PM, Adrian Klaver wrote:


On 01/27/2018 04:34 PM, support-tiger wrote:

sorry for delay but ran some tests on older version pg gem - still fails


With what error message?

What Ruby gem?



it fails on building the config file with simply missing libpq-fe.h, 
cannot find libpq-fe.h


Well it is there:

rpm -qlp postgresql10-devel-10.1-1PGDG.f27.x86_64.rpm | grep libpq-fe.h
warning: postgresql10-devel-10.1-1PGDG.f27.x86_64.rpm: Header V4 
DSA/SHA1 Signature, key ID 442df0f8: NOKEY

/usr/pgsql-10/include/libpq-fe.h



I suspect it is a problem with using the pgdg repository and that 
postgresql get "10" added to them ie postgresql10-devel and maybe the 
gem code does not recognize that.   Since vers 10 is not breaking it 
does not make sense (i guess fedora)  to rename vers 10 with the "10" 
- maybe they just love the python 2 / 3 fiasco and want to extend it 
to postgresql.


Previous versions used version numbering also:

https://yum.postgresql.org/9.6/fedora/fedora-26-x86_64/

postgresql96-devel-9.6.6-1PGDG.f26.x86_64.rpm

My guess it that the config script for the gem is not taking into 
account that Postgres changed from a three part versioning system 
X.Y.z to a two part system X.y(where lower case is minor version) from 
9.6.0 to 10.1.


could be, cannot find the config file in the repository - specifies to 
use a pg-config file to build the gem but no trace of such a config file


It is not looking for a file named pg-config, it is looking for a 
program named pg_config.


See:

https://deveiate.org/code/pg/

gem install pg -- --with-pg-config=


Try something like below to verify pg_config is installed(I'm sure it is):

aklaver@tito:~> pg_config
BINDIR = /usr/local/pgsql96/bin
DOCDIR = /usr/local/pgsql96/share/doc
HTMLDIR = /usr/local/pgsql96/share/doc

...

LIBS = -lpgcommon -lpgport -lxml2 -lssl -lcrypto -lz -lreadline -lrt 
-lcrypt -ldl -lm

VERSION = PostgreSQL 9.6.6

*IMPORTANT* If you have more then one instance of Postgres installed you 
will have more then one pg_config binary, so it is important that you 
point the gem install at the correct one.




thks anyway for your help.  had emailed the pg gem leads but no response 
and github issues are blocked (duh)  - can't depend on this -will just 
make a faster transition to Crystal - the pg shard in Crystal loaded 
okay on Fedora with the 10.1


Are you talking about this?:

https://github.com/ged/ruby-pg

The above is a Git mirror of the 'real' repo, which is on BitBucket:

https://bitbucket.org/ged/ruby-pg/wiki/Home

You will find an issues page there.












On 01/25/2018 03:59 PM, Adrian Klaver wrote:

On 01/25/2018 12:20 PM, support-tiger wrote:
running postgresql 10.1 on Fedora 27. Cannot install ruby pg gem 
due to missing libpq-fe.h file. This is usually found in 
postgresql-devel but 


What was the error message returned when you tried to install 
originally?


it is not in fedora 27 postgresql10-devel. If install 
postgresql-devel (9.6) the gem installs without problem but afraid 
this will interfere with 10.1  How can we install the necessary 
libpq-fe.h  ? thks
















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

Re: EXPLAIN BUFFERS: dirtied

[Tomas Vondra]

On 01/29/2018 08:21 PM, Vitaliy Garnashevich wrote:
> I've read this article: https://wiki.postgresql.org/wiki/Hint_Bits
> 
> It says:
> 
>> A plain SELECT, count(*), or VACUUM on the entire table will check
>> every tuple for visibility and set its hint bits. 
> 
> Suppose, a new page was created using many INSERTs, and then was written
> to disk during a checkpoint. There were no SELECTs or VACUUM on the page
> or table yet. Will the following SELECT of one tuple from the page
> update hint bits for ALL tuples on the page? Is that correct?
> 

Possibly, if there are no old transactions running.

> When a page is initially created and then is being written to disk
> during a checkpoint, does checkpoint writer update the hint bits before
> writing the page, or the following SELECT/VACUUM will have to do that
> (possibly loading/updating/writing the page again)?
> 

Checkpoint only deals with 8kB chunks of data. Hint bits are not set on
a page, but on individual items (rows), so it's not up to the checkpoint
process to tweak that - that's up to queries accessing the data.

regards

-- 
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Using AWS ephemeral SSD storage for production database workload?

[Pritam Barhate]

Thank you, Paul and Steven, for the information.

@Paul: Thanks for the link. Planning to read through most of fdr's comments
on Hacker News.

@Steven:

> Q: Why not just use RDS?

> It'll be simpler.

Already using it for multiple deployments. I am primarily a programmer. But
now want to get out of the RDS convenience zone.

Regards,

Pritam.

pgcrypto Documentation/Function Mismatch

[Gary Chambers]

Hello,

I wanted to pass along a pgcrypto documentation/function definition
mismatch in (at least) v9.3 and v9.6.

The mismatch occurs in the hmac function definition:

Documentation:

hmac(data bytea, key text, type text) returns bytea

As defined in 9.3.17 and 9.6.6:

List of functions
Schema | Name | Result data type | Argument data types |  Type 
---+--+--+-+

tools  | hmac | bytea| bytea, bytea, text  | normal

--
G.

best way to storing logs

[Ibrahim Edib Kokdemir]

Hi,
In our environment, we are logging "all" statements because of the security
considerations (future auditing if necessary). But the system is very big
and produces 100GB logs for an hour and we expect that this will be much
more. We are having trouble to find the disk for this amount of data.

Now, we are considering one of the following paths:
- deduped file system for all logs.
- parsing useful lines with syslog server or pgaudit. And are there any
drawbacks for using remote syslog for the logs?

What should be the right path for that?

Regards,
Ibrahim.

Re: pgpool Connections Distributions Among Nodes

[Tatsuo Ishii]

> We have 4-node cluster (1 master and 3 hot standby).  We are using pgpool as 
> load balancer. We have an observation where if application requests for 3 
> connections, pgpool connects to all 4 servers and I see 3 connections on each 
> of them. I was expecting it have a total of 3 connections from either of 4 
> servers but I can easily see 12 connections in all.

Yes, that's an expected behavior.

Why?

When client A connects to pgpool, it connects to all PostgreSQL (a, b,
c, d). But actually pgpool sends query to one of them (in case load
balance node is primary) or two of them (in case load balance node is
not primary). Suppose it sends to a and b. Client A logs off, but
connections to a and b remain because of connection pooling. Client B
comes in. B uses c and d. The connections to c and d remain.

So eventually pgpool needs to connect to all backend anyway.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

Meaning of query age in pg_stat_activity

[Jan De Moerloose]

Hi,

I'm using the following to detect long running queries in a webapp that is
high on cpu:

SELECT pid, age(clock_timestamp(),query_start) as age, usename, query,
state from pg_stat_activity order by age;

When the cpu is 100% and the app slowing down, i can see that some queries
have a long age.
Running the same queries in psql is very fast, however. The db itself is
low on cpu.

Is the age value as i calculate it representing the time spent by the
database to execute the query or does it also include the time to read the
result ? In other words, if the client is starving on cpu, will i see
higher values of age ?

Regards,
Jan

Re: Meaning of query age in pg_stat_activity

[Nikolay Samokhvalov]

On Mon, Jan 29, 2018 at 3:06 PM, Jan De Moerloose  wrote:
...

> SELECT pid, age(clock_timestamp(),query_start) as age, usename, query,
> state from pg_stat_activity order by age;
>
> When the cpu is 100% and the app slowing down, i can see that some queries
> have a long age.
>

What is the value of "state" column for those queries?

Re: Meaning of query age in pg_stat_activity

[Adrian Klaver]

On 01/29/2018 03:06 PM, Jan De Moerloose wrote:

Hi,

I'm using the following to detect long running queries in a webapp that 
is high on cpu:


SELECT pid, age(clock_timestamp(),query_start) as age, usename, query, 
state from pg_stat_activity order by age;


I would add WHERE state = 'active'



When the cpu is 100% and the app slowing down, i can see that some 
queries have a long age.
Running the same queries in psql is very fast, however. The db itself is 
low on cpu.


Is the age value as i calculate it representing the time spent by the 
database to execute the query or does it also include the time to read 
the result ? In other words, if the client is starving on cpu, will i 
see higher values of age ?


Regards,
Jan



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

Re: Meaning of query age in pg_stat_activity

[Jan De Moerloose]

The state is 'idle in transaction'.

On Tue, Jan 30, 2018 at 12:10 AM, Nikolay Samokhvalov  wrote:

> On Mon, Jan 29, 2018 at 3:06 PM, Jan De Moerloose 
> wrote:
> ...
>
>> SELECT pid, age(clock_timestamp(),query_start) as age, usename, query,
>> state from pg_stat_activity order by age;
>>
>> When the cpu is 100% and the app slowing down, i can see that some
>> queries have a long age.
>>
>
> What is the value of "state" column for those queries?
>
>

Re: Meaning of query age in pg_stat_activity

[Nikolay Samokhvalov]

On Mon, Jan 29, 2018 at 3:19 PM, Jan De Moerloose  wrote:

> The state is 'idle in transaction'.
>

So you have long-running *transactions*, not queries. This is not good for
an OLTP system, because some transaction can wait of others, which are
"idle in transaction" but do nothing at the moment. Think how you can make
them shorter, commit faster.

Also, if your server version is 9.6+ consider setting
idle_in_transaction_session_timeout to some low value:
https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT

Re: Meaning of query age in pg_stat_activity

[Jan De Moerloose]

So the query is just the latest query and the time is the transaction time
since this query, i suppose ?
Thanks for your answer, i will try to make the transaction shorter as you
suggest.

On Tue, Jan 30, 2018 at 12:29 AM, Nikolay Samokhvalov  wrote:

> On Mon, Jan 29, 2018 at 3:19 PM, Jan De Moerloose 
> wrote:
>
>> The state is 'idle in transaction'.
>>
>
> So you have long-running *transactions*, not queries. This is not good for
> an OLTP system, because some transaction can wait of others, which are
> "idle in transaction" but do nothing at the moment. Think how you can make
> them shorter, commit faster.
>
> Also, if your server version is 9.6+ consider setting
> idle_in_transaction_session_timeout to some low value:
> https://www.postgresql.org/docs/current/static/runtime-
> config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT
>
>

Re: best way to storing logs

[PT]

On Tue, 30 Jan 2018 00:38:02 +0300
Ibrahim Edib Kokdemir  wrote:

> Hi,
> In our environment, we are logging "all" statements because of the security
> considerations (future auditing if necessary). But the system is very big
> and produces 100GB logs for an hour and we expect that this will be much
> more. We are having trouble to find the disk for this amount of data.
> 
> Now, we are considering one of the following paths:
> - deduped file system for all logs.
> - parsing useful lines with syslog server or pgaudit. And are there any
> drawbacks for using remote syslog for the logs?
> 
> What should be the right path for that?

I did something like this quite recently. Here's what I learned:

rsyslog works fine. It can handle quite a bit of traffic with no problem.
There is theoretical data loss if you use UDP, so if you need true
auditability, be sure ot use TCP. We had a single dedicated logging
server receiving TONS of log data from multiple Postgres servers and
never had a problem with performance.

Rotate the logs often. Many smaller logs is easier to manage than fewer
large ones. We were rotating once an hour, and the typical size
was tens of G to about 100G per hour.

Compress older logs. Everyone rants about these newer compression algorithms,
like bzip2, but remember that they save a little more space at the cost of
a lot more processing time. gzip is still the way to go, if you ask me, but
YMMV.

After logs got a week old we shipped them off to AWS Glacier storage.
It was a pretty cost effective way to offload the problem of ever-increasing
storage requirements. YMMV on that as well.

We didn't bother to trim out any data from the logs, so I can't say how
much that would or wouldn't help.

-- 
Bill Moran

Re: Information on savepoint requirement within transctions

[David G. Johnston]

On Mon, Jan 29, 2018 at 6:59 AM, Robert Zenz 
wrote:

> > It may be worth updating the docs here...
>
> I'd vote for that. I would have expected to see this mentioned in the
> documentation a little bit more prominent than just a single sentence at
> the end
> of the transaction tutorial. A short section about how the transaction
> behaves
> in an error cases (and what to do) would be nice.
>

​So, my first pass at this.  I'm probably going a bit outside what would
normally be covered in the SQL Command section but it does feel right at
first blush.

Also attached; not compiled.

As a bug fix I've updated the description of "COMMIT" here since it can
cause a ROLLBACK to be issued and that isn't documented.

"(pseudo) sub-transaction" seemed like a reasonable choice of terminology
to introduce rather than just "mark".  Having it mentioned in context in
the BEGIN docs, instead of just a "see also", should aid in understanding
how the whole transaction system fits together.  The advanced features of
the tutorial cover this to some degree (I didn't re-read it prior to
writing this up) but I'm inclined to believe people wanting to understand
transactions, lacking a top-level chapter on the topic, will know of BEGIN
and start their discovery there.

David J.

​diff --git a/doc/src/sgml/ref/begin.sgml b/doc/src/sgml/ref/begin.sgml
index c23bbfb4e7..c1b3ef9306 100644
--- a/doc/src/sgml/ref/begin.sgml
+++ b/doc/src/sgml/ref/begin.sgml
@@ -49,6 +49,16 @@ BEGIN [ WORK | TRANSACTION ] [ transaction_mode

   
+   Pseudo sub-transactions are created using .
+   These are of particular use for client software to use when executing
+   user-supplied SQL statements and want to provide try/catch behavior
+   where failures are ignored. The server cannot be configured to do this
+   automatically: all (sub-)transaction blocks either commit or rollback
in their
+   entirety. A commit issued while the transaction has an active failure
+   is automatically converted into a .
+  
+
+  
Statements are executed more quickly in a transaction block, because
transaction start/commit requires significant CPU and disk
activity. Execution of multiple statements inside a transaction is
diff --git a/doc/src/sgml/ref/commit.sgml b/doc/src/sgml/ref/commit.sgml
index b2e8d5d180..8bb368b771 100644
--- a/doc/src/sgml/ref/commit.sgml
+++ b/doc/src/sgml/ref/commit.sgml
@@ -29,9 +29,11 @@ COMMIT [ WORK | TRANSACTION ]
   Description

   
-   COMMIT commits the current transaction. All
+   COMMIT ends the current transaction. All
changes made by the transaction become visible to others
-   and are guaranteed to be durable if a crash occurs.
+   and are guaranteed to be durable if a crash occurs. However,
+   if the transaction has failed a 
+   will be processed instead.
   
  

diff --git a/doc/src/sgml/ref/savepoint.sgml
b/doc/src/sgml/ref/savepoint.sgml
index 87243b1d20..66cee63966 100644
--- a/doc/src/sgml/ref/savepoint.sgml
+++ b/doc/src/sgml/ref/savepoint.sgml
@@ -41,7 +41,8 @@ SAVEPOINT savepoint_name
   
A savepoint is a special mark inside a transaction that allows all
commands
that are executed after it was established to be rolled back, restoring
-   the transaction state to what it was at the time of the savepoint.
+   the transaction state to what it was at the time of the savepoint. It
can be
+   thought of as a kind of a pseudo sub-transaction.
   
  

@@ -74,6 +75,11 @@ SAVEPOINT savepoint_name
Savepoints can only be established when inside a transaction block.
There can be multiple savepoints defined within a transaction.
   
+
+  
+psql makes use of savepoints to implment its
+ON_ERROR_ROLLBACK behavior.
+  
  

  
diff --git a/doc/src/sgml/ref/begin.sgml b/doc/src/sgml/ref/begin.sgml
index c23bbfb4e7..c1b3ef9306 100644
--- a/doc/src/sgml/ref/begin.sgml
+++ b/doc/src/sgml/ref/begin.sgml
@@ -49,6 +49,16 @@ BEGIN [ WORK | TRANSACTION ] [ transaction_mode

   
+   Pseudo sub-transactions are created using .
+   These are of particular use for client software to use when executing
+   user-supplied SQL statements and want to provide try/catch behavior
+   where failures are ignored. The server cannot be configured to do this
+   automatically: all (sub-)transaction blocks either commit or rollback in 
their
+   entirety. A commit issued while the transaction has an active failure
+   is automatically converted into a .
+  
+
+  
Statements are executed more quickly in a transaction block, because
transaction start/commit requires significant CPU and disk
activity. Execution of multiple statements inside a transaction is
diff --git a/doc/src/sgml/ref/commit.sgml b/doc/src/sgml/ref/commit.sgml
index b2e8d5d180..8bb368b771 100644
--- a/doc/src/sgml/ref/commit.sgml
+++ b/doc/src/sgml/ref/commit.sgml
@@ -29,9 +29,11 @@ COMMIT [ WORK | TRANSACTION ]
   Description

   
-   COMMIT commits the current transaction. All
+   COMMIT ends the current transaction. All
changes made by the tran

Re: Using AWS ephemeral SSD storage for production database workload?

[Ben Madin]

G'day all,

We have been doing this in production for about five years, the client is
aware of the trade off between speed, cost and availability. (By this I
mean, if it goes down for a few minutes, no big concern to them). We had
around 2 million users, but very small payloads.

We take full database backups every six hours, log-ship to s3, and run
multiple hot streaming servers for a high level of user query activity.

Obviously, the risk was always there of an un-planned shutdown, but we had
very good performance on a very cheap setup. m3.medium / ubuntu anyone!

Disclaimer - we don't do this any more for our bigger production systems
... for a number of reasons. We ran out of space on the SSD, our write
queries are generally small and not complex, so we went to EBS backed
without PIOPS, and haven't really noticed any major problems with
performance. I felt that the risk / humbug associated with needing to
upgrade the operating system etc made it easier to just use the EBS. This
does also mean we can vertically scale our DB server easily and securely.
We still have hot-streaming replications, back ups and log shipping.

hth

cheers

Ben





On 30 January 2018 at 04:36, Pritam Barhate  wrote:

> Thank you, Paul and Steven, for the information.
>
> @Paul: Thanks for the link. Planning to read through most of fdr's
> comments on Hacker News.
>
> @Steven:
>
> > Q: Why not just use RDS?
>
> > It'll be simpler.
>
> Already using it for multiple deployments. I am primarily a programmer.
> But now want to get out of the RDS convenience zone.
>
> Regards,
>
> Pritam.
>
>


-- 

Dr Ben Madin
Managing Director



m : +61 448 887 220

e : b...@ausvet.com.au

5 Shuffrey Street, Fremantle
Western Australia

on the web: www.ausvet.com.au


This transmission is for the intended addressee only and is confidential
information. If you have received this transmission in error, please delete
it and notify the sender. The contents of this email are the opinion of the
writer only and are not endorsed by Ausvet unless expressly stated
otherwise. Although Ausvet uses virus scanning software we do not accept
liability for viruses or similar in any attachments.

Re: Using AWS ephemeral SSD storage for production database workload?

[Sam Gendler]

Why not use EBS storage, but don’t use provisioned iops SSDs (io1) for the
ebs volume. Just use the default storage type (gp2) and live with the 3000
IOPS peak for 30 minutes that that allows. You’d be amazed at just how much
I/o can be handled within the default IOPS allowance, though bear in mind
that you accrue iops credits at a rate that is proportional to storage
amount once you’ve started to eat into your quota, so the performance of
someone using general-purpose SSDs (gp2) with 2 terabytes of storage will
be different than someone using 100GB of storage. But I recently moved
several databases to gp2 storage and saved a ton of money doing so (we were
paying for 5000 IOPS and using 5 AT PEAK other than brief bursts to a
couple hundred when backing up and restoring). I’ve done numerous backups
and restores on those hosts since then and have had no trouble keeping up
and have never come close to the 3k theoretical max, even briefly.
Replication doesn’t appear to be bothered, either.

Going to ephemeral storage seems unnecessarily problem prone when instances
die, and I’m not even sure it is an option in RDS or recent EC2 instance
types, which require EBS volumes even for the boot volume. But EBS with
general purpose storage isn’t much more expensive than ephemeral.

On Mon, Jan 29, 2018 at 08:42 Pritam Barhate 
wrote:

> Hi everyone,
>
> As you may know, EBS volumes though durable are very costly when you
> need provisioned IOPS. As opposed to this AWS instance attached ephemeral SSD
> is very fast but isn't durable.
>
> I have come across some ideas on the Internet where people hinted at
> running production PostgreSQL workloads on AWS ephemeral SSD storage.
> Generally, this involves shipping WAL logs continuously to S3 and keeping
> an async read replica in another AWS availability zone. Worst case scenario
> in such deployment is data loss of a few seconds. But beyond this the
> details are sketchy.
>
> Have you come across such a deployment? What are some best practices that
> need to be followed to pull this through without significant data loss?
> Even though WAL logs are being shipped to S3, in case of loss of both the
> instances, the restore time is going be quite a bit for databases of a few
> hundred GBs.
>
> Just to be clear, I am not planning anything like this, anytime soon :-)
> But I am curious about trade-offs of such a deployment. Any concrete
> information in this aspect is well appreciated.
>
> Regards,
>
> Pritam.
>

Re: Using AWS ephemeral SSD storage for production database workload?

[Ben Chobot]

> On Jan 29, 2018, at 8:05 PM, Sam Gendler  wrote:
> 
> Why not use EBS storage, but don’t use provisioned iops SSDs (io1) for the 
> ebs volume. Just use the default storage type (gp2) and live with the 3000 
> IOPS peak for 30 minutes that that allows. You’d be amazed at just how much 
> I/o can be handled within the default IOPS allowance, though bear in mind 
> that you accrue iops credits at a rate that is proportional to storage amount 
> once you’ve started to eat into your quota, so the performance of someone 
> using general-purpose SSDs (gp2) with 2 terabytes of storage will be 
> different than someone using 100GB of storage. But I recently moved several 
> databases to gp2 storage and saved a ton of money doing so (we were paying 
> for 5000 IOPS and using 5 AT PEAK other than brief bursts to a couple hundred 
> when backing up and restoring). I’ve done numerous backups and restores on 
> those hosts since then and have had no trouble keeping up and have never come 
> close to the 3k theoretical max, even briefly. Replication doesn’t appear to 
> be bothered, either.  

One reason would be that gp2 volumes cap out at 160MB/s. We have a bunch of 
databases on gp2 (it works great) but that throughput cap can bite you if 
you’re not expecting it.

Re: Using AWS ephemeral SSD storage for production database workload?

[Pritam Barhate]

Thanks Ben and Sam for sharing your experience.

On Jan 30, 2018 8:52 AM, "Ben Chobot"  wrote:

>
> > On Jan 29, 2018, at 8:05 PM, Sam Gendler 
> wrote:
> >
> > Why not use EBS storage, but don’t use provisioned iops SSDs (io1) for
> the ebs volume. Just use the default storage type (gp2) and live with the
> 3000 IOPS peak for 30 minutes that that allows. You’d be amazed at just how
> much I/o can be handled within the default IOPS allowance, though bear in
> mind that you accrue iops credits at a rate that is proportional to storage
> amount once you’ve started to eat into your quota, so the performance of
> someone using general-purpose SSDs (gp2) with 2 terabytes of storage will
> be different than someone using 100GB of storage. But I recently moved
> several databases to gp2 storage and saved a ton of money doing so (we were
> paying for 5000 IOPS and using 5 AT PEAK other than brief bursts to a
> couple hundred when backing up and restoring). I’ve done numerous backups
> and restores on those hosts since then and have had no trouble keeping up
> and have never come close to the 3k theoretical max, even briefly.
> Replication doesn’t appear to be bothered, either.
>
> One reason would be that gp2 volumes cap out at 160MB/s. We have a bunch
> of databases on gp2 (it works great) but that throughput cap can bite you
> if you’re not expecting it.
>
>

Re: Meaning of query age in pg_stat_activity

[Nikolay Samokhvalov]

On Mon, Jan 29, 2018 at 3:45 PM, Jan De Moerloose  wrote:

> So the query is just the latest query and the time is the transaction time
> since this query, i suppose ?
> Thanks for your answer, i will try to make the transaction shorter as you
> suggest.
>

Yep. This is a very common confusion, however it usually happens regarding
records with "state" = 'idle'
– notice that they also have something in "query" column while they are
actually doing nothing.

Re: Meaning of query age in pg_stat_activity

[Nikolay Samokhvalov]

On Mon, Jan 29, 2018 at 9:52 PM, Nikolay Samokhvalov 
wrote:

>
>
> On Mon, Jan 29, 2018 at 3:45 PM, Jan De Moerloose 
> wrote:
>
>> So the query is just the latest query and the time is the transaction
>> time since this query, i suppose ?
>> Thanks for your answer, i will try to make the transaction shorter as you
>> suggest.
>>
>
> Yep. This is a very common confusion, however it usually happens regarding
> records with "state" = 'idle'
> – notice that they also have something in "query" column while they are
> actually doing nothing.
>

One correction:

"the time is the transaction time since this query" is not an accurate
statement.

You mentioned query_start – it reflects the last query's starting time, not
transaction's.
There are other timestamps:
  backend_start, xact_start, and state_change.

All of them are useful in different contexts.

session_replication_role meaning?

[Luca Ferrari]

Hi all,
now this should be trivial, but I cannot udnerstand what is the
purpose of session_replication_role

or better, when I should use it in a way different from 'origin'.
I've a logical replication setup and both master and client
connections default to 'origin', so it has to be specified manually.
What is its meaning?

Thanks,
Luca

Re: EXPLAIN BUFFERS: dirtied

[Vitaliy Garnashevich]

Thanks!