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

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

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

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

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

Re: [GENERAL] [ADMIN] cpu hight sy% usage

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

Re: [GENERAL] pg_xlog keeps growing

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 r

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

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

Re: [GENERAL] pg_xlog keeps growing

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

[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

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.

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

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

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

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 t

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

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 archiv

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 f

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 sa

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

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.

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 se

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

Re: [GENERAL] Conferences for a DBA?

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

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 pl

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. In North America the bigger ones are: --

Re: [GENERAL] Conferences for a DBA?

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-

[GENERAL] Conferences for a DBA?

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

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_databa

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

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 w

Re: [GENERAL] hight cpu %sy usage

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

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

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

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 d

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

On 02/27/2017 07:03 AM, Sven R. Kunze wrote: On 27.02.2017 12:10, Geoff Winkless wrote: On 27 February 2017 at 10:52, Sven R. Kunze mailto:srku...@mail.de>>wrote: So, what can I do to parse texts to date(times) in a safe manner? You know best the format of your data; if you know that you

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

On 27.02.2017 12:10, Geoff Winkless wrote: On 27 February 2017 at 10:52, Sven R. Kunze >wrote: So, what can I do to parse texts to date(times) in a safe manner? You know best the format of your data; if you know that your date field is always in a particular style

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

Thank you for explaining the steps of your algorithm. Just one question: How to do the actual transfer of data? 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 withou

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

On 27 February 2017 at 10:52, Sven R. Kunze wrote: > > So, what can I do to parse texts to date(times) in a safe manner? > > You know best the format of your data; if you know that your date field is always in a particular style and timezone, you can write a function that can be considered safe t

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

Hi Geoff, Adrian and Tom, thanks for your responses so far. Excuse my late response. I will respond to Tom's mail as it covers most points: On 26.02.2017 17:50, Tom Lane wrote: There are multiple reasons why the text-to-datetime conversion functions are not immutable: * some of them depend o

Re: [GENERAL] Foreign key references a unique index instead of a primary key

On Feb 23, 2017 12:42 PM, "Ivan Voras" wrote: Hello, I've inherited a situation where: - a table has both a primary key and a unique index on the same field. - at some time, a foreign key was added which references this table (actually, I'm not sure about the sequence of events), which

Re: [GENERAL] Full Text Search combined with Fuzzy

On Sun, Feb 26, 2017 at 3:52 PM, Nicolas Paris wrote: > Hello, > > AFAIK there is no built-in way to combine full text search and fuzzy > matching > (https://www.postgresql.org/docs/current/static/fuzzystrmatch.html). > By example, phrase searching with tipos in it. > > First I don't know if post