Grant to a group defined in Windows AD

[Hans Schou]

Hi

Can I have a advise on how to handle groups?

In my Windows AD (Active Directory) I have two groups named:
  readers
  writers

In Postgresql I have these databases:
  d1
  d2

The "writers" should have NOCREATEDB etc but ALL PRIVILEGES to d1 and d2.

The "readers" should have SELECT to all tables in d1 and no access at all
to d2.

It seems like I can either use a group ROLE or a SCHEME to accomplish my
goal. Which one will be the most best/simple to administer for me and my
colleagues? Or is there another approach we can use?

Here is my draft for a daily-run Powershell script which will get all the
users in the group "readers" in the Windows AD and create them in
Postgresql:

$LdapCon = "LDAP://CN=readers,OU=specimen,DC=example,DC=org"
Write-Host "-- Get group members from: $($LdapCon)"
$Group = [ADSI]$LdapCon
$Group.Member | ForEach-Object {
$Searcher = [adsisearcher]"(distinguishedname=$_)"
$u = $($searcher.FindOne().Properties.samaccountname).ToLower()
Write-Host "CREATE ROLE `"$u`";"
Write-Host " ALTER ROLE `"$u`" WITH LOGIN;"
Write-Host " GRANT SELECT ... readers ...;"
}

And then I pipe the output to psql.exe.
The output looks like:
  CREATE ROLE "joe";
   ALTER ROLE "joe" WITH LOGIN;
   GRANT SELECT ... readers ...;

PS: To get a list of your own groups use Powershell:

([ADSISEARCHER]"samaccountname=$($env:USERNAME)").Findone().Properties.memberof

Using enum instead of join tables

[hmidi slim]

Hi,
Is it a good thing to use enum type such a column in a table instead of
making a foreign key which references to another table?
I found these links talking about enum and when I will use them:
http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/
https://www.percona.com/blog/2008/01/24/enum-fields-vs-varchar-vs-int-joined-table-what-is-faster/

Is there any advices or new updates to use enum type in order to decrease
the number of join between the table and optimize the performance and query
runtime?

Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Pavan Deolasee]

On Fri, Apr 6, 2018 at 8:55 AM, Pavan Deolasee 
wrote:

>
>
> On Fri, Apr 6, 2018 at 2:34 AM, Tom Lane  wrote:
>
>> a...@novozymes.com (Adam =?utf-8?Q?Sj=C3=B8gren?=) writes:
>> >> [... still waiting for the result, I will return with what it said
>> >> when the server does ...]
>>
>> > It did eventually finish, with the same result:
>>
>> Huh.  So what we have here, apparently, is that regular MVCC snapshots
>> think there is exactly one copy of the 1698936148/0 row, but TOAST fetches
>> think there is more than one.  This is darn odd, not least because we
>> never do UPDATEs in toast tables, only inserts and deletes, so there
>> certainly shouldn't be update chains there.
>>
>> It seems like you've got some corner case wherein SnapshotToast sees a row
>> that isn't visible according to MVCC --- probably a row left over from
>> some previous cycle of life.  That is, I'm imagining the OID counter
>> wrapped around and we've reused a toast OID, but for some reason there's
>> still a row in the table with that OID.  I'm not sure offhand how we could
>> get into such a state.  Alvaro, does this ring any bells (remembering that
>> this is 9.3)?
>>
>
> FWIW one of our support customers reported a very similar TOAST table
> corruption issue last week which nearly caused an outage. After a lot of
> analysis, I think I've now fully understood the reasons behind the
> corruption, the underlying bug(s) and possible remedy. I am currently
> working on writing a reproducible test case to demonstrate the problem and
> writing the fix. More details on that soon.
>
>
I've posted a reproducer and a proposed fix to -hackers [1]

In the particular case that I investigated, a database crash/recovery was
involved. But I think we should be able to create a scenario where OID
wrap-around or a standby promotion triggers the problem.  I don't know if
any of that was involved in the cases reported on this thread, but I've a
strong suspicion that the underlying bug is probably the same.

Thanks,
Pavan

[1] https://www.postgresql.org/message-id/CABOikdOgWT2hHkYG3Wwo2cyZJq2zf
s1FH0FgX-%3Dh4OLosXHf9w%40mail.gmail.com

-- 
 Pavan Deolasee   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: ERROR: found multixact from before relminmxid

[Alexandre Arruda]

2018-04-09 23:51 GMT-03:00 Peter Geoghegan :
> On Mon, Apr 9, 2018 at 6:56 PM, Alexandre Arruda  wrote:
>> (... and all other indexes returns null too)
>>
>> I tried with bt_index_check too. Same results.
>
> That's interesting, because it tells me that you have a table that
> appears to not be corrupt, despite the CLUSTER error. Also, the error
> itself comes from sanity checking added to MultiXact freezing fairly
> recently, in commit 699bf7d0.
>
> You didn't say anything about regular VACUUM being broken. Do you find
> that it works without any apparent issue?
>
> I have a suspicion that this could be a subtle bug in
> CLUSTER/freezing. The only heap_freeze_tuple() caller is code used by
> CLUSTER, so it's not that hard to imagine a MultiXact freezing bug
> that is peculiar to CLUSTER. Though I haven't thought about it in much
> detail.
>
> --
> Peter Geoghegan

Hi Peter,

Actualy, I first notice the problem in logs by autovacuum:

2018-04-10 08:22:15.385 -03 [55477] CONTEXT:  automatic vacuum of
table "production.public.fn06t"
2018-04-10 08:22:16.815 -03 [55477] ERROR:  found multixact 68834765
from before relminmxid 73262006

production=# vacuum analyze verbose fn06t;
INFO:  vacuuming "public.fn06t"
ERROR:  found multixact 76440919 from before relminmxid 122128619

Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Adam Sjøgren]

Pavan writes:

>> FWIW one of our support customers reported a very similar TOAST table
>> corruption issue last week which nearly caused an outage. After a lot of
>> analysis, I think I've now fully understood the reasons behind the
>> corruption, the underlying bug(s) and possible remedy. I am currently
>> working on writing a reproducible test case to demonstrate the problem and
>> writing the fix. More details on that soon.
>
> I've posted a reproducer and a proposed fix to -hackers [1]
>
> In the particular case that I investigated, a database crash/recovery was
> involved. But I think we should be able to create a scenario where OID
> wrap-around or a standby promotion triggers the problem.  I don't know if
> any of that was involved in the cases reported on this thread, but I've a
> strong suspicion that the underlying bug is probably the same.

In our case there was no crash+recovery, but we do have a high write
load (and the problem occurring quite seldom), so it sounds like it
fits.

> [1] 
> https://www.postgresql.org/message-id/CABOikdOgWT2hHkYG3Wwo2cyZJq2zfs1FH0FgX-%3Dh4OLosXHf9w%40mail.gmail.com

Impressive investigation!

Just a couple of data points: your script to reproduce the problem does
so for all three versions of PostgreSQL we have in use: 9.3.22, 9.6.8
and 10.3.

And I specifically tested on our production machine which exhibits the
problem, running 9.3.22, and your script reproduced the problem there as
well.

I.e. ending with:

  ERROR:  unexpected chunk number 0 (expected 1) for toast value 16396 in 
pg_toast_16384
  REINDEX
  ERROR:  unexpected chunk number 0 (expected 1) for toast value 16396 in 
pg_toast_16384
  VACUUM
  ERROR:  unexpected chunk number 0 (expected 1) for toast value 16396 in 
pg_toast_16384


  Best regards,

Adam

-- 
 "No more than that, but very powerful all theAdam Sjøgren
  same; simple things are good."a...@novozymes.com

Postgresql Split Brain: Which one is latest

[Vikas Sharma]

Hi,

We have postgresql 9.5 with streaming replication(Master-slave) and
automatic failover. Due to network glitch we are in master-master situation
for quite some time. Please, could you advise best way to confirm which
node is latest in terms of updates to the postgres databases.

Regards
Vikas Sharma

Re: best way to write large data-streams quickly?

[Mark Moellering]

On Mon, Apr 9, 2018 at 12:01 PM, Steve Atkins  wrote:

>
> > On Apr 9, 2018, at 8:49 AM, Mark Moellering  com> wrote:
> >
> > Everyone,
> >
> > We are trying to architect a new system, which will have to take several
> large datastreams (total of ~200,000 parsed files per second) and place
> them in a database.  I am trying to figure out the best way to import that
> sort of data into Postgres.
> >
> > I keep thinking i can't be the first to have this problem and there are
> common solutions but I can't find any.  Does anyone know of some sort
> method, third party program, etc, that can accept data from a number of
> different sources, and push it into Postgres as fast as possible?
>
> Take a look at http://ossc-db.github.io/pg_bulkload/index.html. Check the
> benchmarks for different situations compared to COPY.
>
> Depending on what you're doing using custom code to parse your data and
> then do multiple binary COPYs in parallel may be better.
>
> Cheers,
>   Steve
>
>
>
(fighting google slightly to keep from top-posting...)

Thanks!

How long can you run COPY?  I have been looking at it more closely.  In
some ways, it would be simple just to take data from stdin and send it to
postgres but can I do that literally 24/7?  I am monitoring data feeds that
will never stop and I don't know if that is how Copy is meant to be used or
if I have to let it finish and start another one at some point?

Thanks for everyones' help and input!

Mark Moellering

Re: Postgresql Split Brain: Which one is latest

[Achilleas Mantzios]

On 10/04/2018 16:50, Vikas Sharma wrote:

Hi,

We have postgresql 9.5 with streaming replication(Master-slave) and automatic failover. Due to network glitch we are in master-master situation for quite some time. Please, could you advise best way 
to confirm which node is latest in terms of updates to the postgres databases.

The one with the latest timeline.


Regards
Vikas Sharma



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Postgresql Split Brain: Which one is latest

[Adrian Klaver]

On 04/10/2018 06:50 AM, Vikas Sharma wrote:

Hi,

We have postgresql 9.5 with streaming replication(Master-slave) and 
automatic failover. Due to network glitch we are in master-master 
situation for quite some time. Please, could you advise best way to 
confirm which node is latest in terms of updates to the postgres databases.


It might help to know how the two masters received data when they where 
operating independently.




Regards
Vikas Sharma



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

Re: Postgresql Split Brain: Which one is latest

[Vikas Sharma]

Hi Adrian,

This can be a good example: Application server e.g. tomcat having two
entries to connect to databases, one for master and 2nd for Slave (ideally
used when slave becomes master). If application is not able to connect to
first, it will try to connect to 2nd.

Regards
Vikas

On 10 April 2018 at 15:26, Adrian Klaver  wrote:

> On 04/10/2018 06:50 AM, Vikas Sharma wrote:
>
>> Hi,
>>
>> We have postgresql 9.5 with streaming replication(Master-slave) and
>> automatic failover. Due to network glitch we are in master-master situation
>> for quite some time. Please, could you advise best way to confirm which
>> node is latest in terms of updates to the postgres databases.
>>
>
> It might help to know how the two masters received data when they where
> operating independently.
>
>
>> Regards
>> Vikas Sharma
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Postgresql Split Brain: Which one is latest

[Melvin Davidson]

On Tue, Apr 10, 2018 at 11:04 AM, Vikas Sharma  wrote:

> Hi Adrian,
>
> This can be a good example: Application server e.g. tomcat having two
> entries to connect to databases, one for master and 2nd for Slave (ideally
> used when slave becomes master). If application is not able to connect to
> first, it will try to connect to 2nd.
>
> Regards
> Vikas
>
> On 10 April 2018 at 15:26, Adrian Klaver 
> wrote:
>
>> On 04/10/2018 06:50 AM, Vikas Sharma wrote:
>>
>>> Hi,
>>>
>>> We have postgresql 9.5 with streaming replication(Master-slave) and
>>> automatic failover. Due to network glitch we are in master-master situation
>>> for quite some time. Please, could you advise best way to confirm which
>>> node is latest in terms of updates to the postgres databases.
>>>
>>
>> It might help to know how the two masters received data when they where
>> operating independently.
>>
>>
>>> Regards
>>> Vikas Sharma
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>


*Vikas,*

*Presuming the the real "master" will have additional records/rows inserted
in the tables,*

*if you run ANALYZE on the database(s) in both "masters", then execute the
following query *






*on both, whichever returns the highest count would be the real
"master". SELECT sum(c.reltuples::bigint)FROM pg_stat_all_tables s
JOIN pg_class c ON c.oid = s.relid WHERE s.relname NOT LIKE 'pg_%'   AND
s.relname NOT LIKE 'sql_%';*


-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: Postgresql Split Brain: Which one is latest

[Edson Carlos Ericksson Richter]

Em 10/04/2018 12:28, Melvin Davidson escreveu:



On Tue, Apr 10, 2018 at 11:04 AM, Vikas Sharma > wrote:


Hi Adrian,

This can be a good example: Application server e.g. tomcat having
two entries to connect to databases, one for master and 2nd for
Slave (ideally used when slave becomes master). If application is
not able to connect to first, it will try to connect to 2nd.

Regards
Vikas

On 10 April 2018 at 15:26, Adrian Klaver
mailto:adrian.kla...@aklaver.com>> wrote:

On 04/10/2018 06:50 AM, Vikas Sharma wrote:

Hi,

We have postgresql 9.5 with streaming
replication(Master-slave) and automatic failover. Due to
network glitch we are in master-master situation for quite
some time. Please, could you advise best way to confirm
which node is latest in terms of updates to the postgres
databases.


It might help to know how the two masters received data when
they where operating independently.


Regards
Vikas Sharma



-- 
Adrian Klaver

adrian.kla...@aklaver.com 



*Vikas,

*
*Presuming the the real "master" will have additional records/rows 
inserted in the tables,

*
*if you run ANALYZE on the database(s) in both "masters", then execute 
the following query

*
*on both, whichever returns the highest count would be the real "master".

 SELECT sum(c.reltuples::bigint)
    FROM pg_stat_all_tables s
      JOIN pg_class c ON c.oid = s.relid
 WHERE s.relname NOT LIKE 'pg_%'
   AND s.relname NOT LIKE 'sql_%';*


--
*Melvin Davidson**
Maj. Database & Exploration Specialist**
Universe Exploration Command – UXC*
Employment by invitation only!


I'm just trying to understand the scenario...

Correct me if I'm wrong, if you had two servers acting as master for a 
while, then you have inserted/updated records on both servers, and you 
will need some kind of "merge" of records into one of the databases, 
that will become the new updated master...


If you have "sequences" (or "serial" fields), then you will get a bit 
trouble in your hands.


Regards,

Edson

Multiple records returned by a JOIN

[Alexander Farber]

Good evening,

in PostgreSQL 10.3 I have written the following custom function (trying to
fetch 10 latest games played by a user):

CREATE OR REPLACE FUNCTION words_stat_games(
in_social integer,
in_sidtext
) RETURNS TABLE (
out_gidinteger,
out_reason text,
out_state1 text,
out_score1 integer,
out_score2 integer
) AS
$func$
SELECT
g.gid,
g.reason,
CASE WHEN g.player1 = s.uid THEN g.state1 ELSE g.state2 END,
CASE WHEN g.player1 = s.uid THEN g.score1 ELSE g.score2 END,
CASE WHEN g.player1 = s.uid THEN g.score2 ELSE g.score1 END
FROMwords_games g
JOINwords_social s ON (g.player1 = s.uid OR g.player2 = s.uid)
WHERE   g.finished IS NOT NULL
ORDER BY g.finished DESC
LIMIT   10;

$func$ LANGUAGE sql STABLE;

Unfortunately, it returns multiple records and with wrong values too:

 # select * from words_stat_games(1, '10999844041575271');
 out_gid | out_reason | out_state1 | out_score1 | out_score2
-++++
1978 | resigned   | lost   |  0 |  0
1978 | resigned   | won|  0 |  0
1847 | resigned   | lost   |234 |441
1847 | resigned   | won|441 |234
1847 | resigned   | won|441 |234
1800 | expired| won| 41 |  0
1798 | expired| lost   |  8 | 28
1798 | expired| won| 28 |  8
1800 | expired| lost   |  0 | 41
1926 | expired| won| 35 | 13
(10 rows)

Why does it return the game 1978 twice and also the out_state1 changes
between 'lost' and 'won' values?

I hoped to handle that with my "CASE WHEN g.player1 = s.uid THEN g.state1
ELSE g.state2 END", but it obviously fails

Below are my 2 table definitions, thank you for any hints.

CREATE TABLE words_social (
sid text NOT NULL,
social  integer  NOT NULL CHECK (0 < social AND social <= 64),
uid integer  NOT NULL REFERENCES words_users ON DELETE CASCADE,
PRIMARY KEY(sid, social)
)

CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,

created timestamptz NOT NULL,
finished timestamptz,

player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT
NULL CHECK (player1 <> player2),
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,

reason  text, -- regular, resigned, expired, banned
state1  text, -- tie, winning, losing, draw, won, lost
state2  text, -- tie, winning, losing, draw, won, lost

score1  integer NOT NULL CHECK (score1 >= 0),
score2  integer NOT NULL CHECK (score2 >= 0)
);
CREATE INDEX words_games_state1_index ON words_games(state1);
CREATE INDEX words_games_state2_index ON words_games(state2);
CREATE INDEX words_games_reason_index ON words_games(reason);

Re: Multiple records returned by a JOIN

[David G. Johnston]

On Tue, Apr 10, 2018 at 8:44 AM, Alexander Farber <
alexander.far...@gmail.com> wrote:

> Unfortunately, it returns multiple records and with wrong values too:
>
>  # select * from words_stat_games(1, '10999844041575271');
>  out_gid | out_reason | out_state1 | out_score1 | out_score2
> -++++
> 1978 | resigned   | lost   |  0 |  0
> 1978 | resigned   | won|  0 |  0
> 1847 | resigned   | lost   |234 |441
> 1847 | resigned   | won|441 |234
> 1847 | resigned   | won|441 |234
> 1800 | expired| won| 41 |  0
> 1798 | expired| lost   |  8 | 28
> 1798 | expired| won| 28 |  8
> 1800 | expired| lost   |  0 | 41
> 1926 | expired| won| 35 | 13
> (10 rows)
>
> Why does it return the game 1978 twice and also the out_state1 changes
> between 'lost' and 'won' values?
>
>
JOINwords_social s ON (g.player1 = s.uid OR g.player2 = s.uid)

I'm more surprised by the single and triple than the doubles.  Your join
against social, which has a record for each user, and games which has two
users, should result in an output with two rows, one for each of the users
in the games row.  One of those users wins, and one of them loses.  How you
have 2 winners in 1847 I cannot tell without seeing data.  Why there is no
loser for 1926 is likewise a mystery.

David J.

Re: Multiple records returned by a JOIN

[Alexander Farber]

Ok thanks, I guess I should switch to a SELECT UNION (first on uid =
player1 and the uid = player2) and that will fix the CASE ... END for me.

On Tue, Apr 10, 2018 at 6:01 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Tue, Apr 10, 2018 at 8:44 AM, Alexander Farber <
> alexander.far...@gmail.com> wrote:
>
>> Unfortunately, it returns multiple records and with wrong values too:
>>
>>  # select * from words_stat_games(1, '10999844041575271');
>>  out_gid | out_reason | out_state1 | out_score1 | out_score2
>> -++++
>> 1978 | resigned   | lost   |  0 |  0
>> 1978 | resigned   | won|  0 |  0
>> 1847 | resigned   | lost   |234 |441
>> 1847 | resigned   | won|441 |234
>> 1847 | resigned   | won|441 |234
>> 1800 | expired| won| 41 |  0
>> 1798 | expired| lost   |  8 | 28
>> 1798 | expired| won| 28 |  8
>> 1800 | expired| lost   |  0 | 41
>> 1926 | expired| won| 35 | 13
>> (10 rows)
>>
>> Why does it return the game 1978 twice and also the out_state1 changes
>> between 'lost' and 'won' values?
>>
>>
> JOINwords_social s ON (g.player1 = s.uid OR g.player2 = s.uid)
>
> I'm more surprised by the single and triple than the doubles.  Your join
> against social, which has a record for each user, and games which has two
> users, should result in an output with two rows, one for each of the users
> in the games row.  One of those users wins, and one of them loses.  How you
> have 2 winners in 1847 I cannot tell without seeing data.  Why there is no
> loser for 1926 is likewise a mystery.
>
>
I don't know why this triple is there, but it really is :-)

Regards
Alex

Re: Multiple records returned by a JOIN

[Alexander Farber]

Or actually I can not use SELECT UNION here, because then I only get 10
records of the condition uid = player1 and then nothing would be left for
the other condition uid = player2

Re: Postgresql Split Brain: Which one is latest

[Adrian Klaver]

On 04/10/2018 08:04 AM, Vikas Sharma wrote:

Hi Adrian,

This can be a good example: Application server e.g. tomcat having two 
entries to connect to databases, one for master and 2nd for Slave 
(ideally used when slave becomes master). If application is not able to 
connect to first, it will try to connect to 2nd.


So the application server had a way of seeing the new master(old slave), 
in spite of the network glitch, that the original master database did not?


If so and it was distributing data between the two masters on an unknown 
schedule, then as Edison pointed out in another post, you really have a 
split brain issue. Each master would have it's own view of the data and 
latest update would really only be relevant for that master.




Regards
Vikas

On 10 April 2018 at 15:26, Adrian Klaver > wrote:


On 04/10/2018 06:50 AM, Vikas Sharma wrote:

Hi,

We have postgresql 9.5 with streaming replication(Master-slave)
and automatic failover. Due to network glitch we are in
master-master situation for quite some time. Please, could you
advise best way to confirm which node is latest in terms of
updates to the postgres databases.


It might help to know how the two masters received data when they
where operating independently.


Regards
Vikas Sharma



-- 
Adrian Klaver

adrian.kla...@aklaver.com 





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

Re: Postgresql Split Brain: Which one is latest

[Vikas Sharma]

Thanks Adrian and Edison, I also think so. At the moment I have 2 masters,
as soon as slave is promoted to master it starts its own timeline and
application might have added data to either of them or both, only way to
find out correct master now is the instance with max count of data in
tables which could incur data loss as well. Correct me if wrong please?

Thanks and Regards
Vikas

On Tue, Apr 10, 2018, 17:29 Adrian Klaver  wrote:

> On 04/10/2018 08:04 AM, Vikas Sharma wrote:
> > Hi Adrian,
> >
> > This can be a good example: Application server e.g. tomcat having two
> > entries to connect to databases, one for master and 2nd for Slave
> > (ideally used when slave becomes master). If application is not able to
> > connect to first, it will try to connect to 2nd.
>
> So the application server had a way of seeing the new master(old slave),
> in spite of the network glitch, that the original master database did not?
>
> If so and it was distributing data between the two masters on an unknown
> schedule, then as Edison pointed out in another post, you really have a
> split brain issue. Each master would have it's own view of the data and
> latest update would really only be relevant for that master.
>
> >
> > Regards
> > Vikas
> >
> > On 10 April 2018 at 15:26, Adrian Klaver  > > wrote:
> >
> > On 04/10/2018 06:50 AM, Vikas Sharma wrote:
> >
> > Hi,
> >
> > We have postgresql 9.5 with streaming replication(Master-slave)
> > and automatic failover. Due to network glitch we are in
> > master-master situation for quite some time. Please, could you
> > advise best way to confirm which node is latest in terms of
> > updates to the postgres databases.
> >
> >
> > It might help to know how the two masters received data when they
> > where operating independently.
> >
> >
> > Regards
> > Vikas Sharma
> >
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Postgresql Split Brain: Which one is latest

[Ron]

You need to find out when the split happened, and whether each new master 
have records since then.


On 04/10/2018 11:47 AM, Vikas Sharma wrote:
Thanks Adrian and Edison, I also think so. At the moment I have 2 masters, 
as soon as slave is promoted to master it starts its own timeline and 
application might have added data to either of them or both, only way to 
find out correct master now is the instance with max count of data in 
tables which could incur data loss as well. Correct me if wrong please?


Thanks and Regards
Vikas

On Tue, Apr 10, 2018, 17:29 Adrian Klaver > wrote:


On 04/10/2018 08:04 AM, Vikas Sharma wrote:
> Hi Adrian,
>
> This can be a good example: Application server e.g. tomcat having two
> entries to connect to databases, one for master and 2nd for Slave
> (ideally used when slave becomes master). If application is not able to
> connect to first, it will try to connect to 2nd.

So the application server had a way of seeing the new master(old slave),
in spite of the network glitch, that the original master database did not?

If so and it was distributing data between the two masters on an unknown
schedule, then as Edison pointed out in another post, you really have a
split brain issue. Each master would have it's own view of the data and
latest update would really only be relevant for that master.

>
> Regards
> Vikas
>
> On 10 April 2018 at 15:26, Adrian Klaver mailto:adrian.kla...@aklaver.com>
> >> wrote:
>
>     On 04/10/2018 06:50 AM, Vikas Sharma wrote:
>
>         Hi,
>
>         We have postgresql 9.5 with streaming replication(Master-slave)
>         and automatic failover. Due to network glitch we are in
>         master-master situation for quite some time. Please, could you
>         advise best way to confirm which node is latest in terms of
>         updates to the postgres databases.
>
>
>     It might help to know how the two masters received data when they
>     where operating independently.
>
>
>         Regards
>         Vikas Sharma
>
>
>
>     --
>     Adrian Klaver
> adrian.kla...@aklaver.com 
>
>
>


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



--
Angular momentum makes the world go 'round.

Re: Postgresql Split Brain: Which one is latest

[Adrian Klaver]

On 04/10/2018 09:47 AM, Vikas Sharma wrote:
Thanks Adrian and Edison, I also think so. At the moment I have 2 
masters, as soon as slave is promoted to master it starts its own 
timeline and application might have added data to either of them or 
both, only way to find out correct master now is the instance with max 
count of data in tables which could incur data loss as well. Correct me 
if wrong please?


Not sure max count is necessarily a valid indicator:

1) What if there was a legitimate large delete process?

2) The application/end users where looking at two different views of the 
data at different points in time. Just because the count is higher does 
not mean the data is actually valid.




Thanks and Regards
Vikas

On Tue, Apr 10, 2018, 17:29 Adrian Klaver > wrote:


On 04/10/2018 08:04 AM, Vikas Sharma wrote:
 > Hi Adrian,
 >
 > This can be a good example: Application server e.g. tomcat having two
 > entries to connect to databases, one for master and 2nd for Slave
 > (ideally used when slave becomes master). If application is not
able to
 > connect to first, it will try to connect to 2nd.

So the application server had a way of seeing the new master(old slave),
in spite of the network glitch, that the original master database
did not?

If so and it was distributing data between the two masters on an unknown
schedule, then as Edison pointed out in another post, you really have a
split brain issue. Each master would have it's own view of the data and
latest update would really only be relevant for that master.

 >
 > Regards
 > Vikas
 >
 > On 10 April 2018 at 15:26, Adrian Klaver
mailto:adrian.kla...@aklaver.com>
 > >> wrote:
 >
 >     On 04/10/2018 06:50 AM, Vikas Sharma wrote:
 >
 >         Hi,
 >
 >         We have postgresql 9.5 with streaming
replication(Master-slave)
 >         and automatic failover. Due to network glitch we are in
 >         master-master situation for quite some time. Please,
could you
 >         advise best way to confirm which node is latest in terms of
 >         updates to the postgres databases.
 >
 >
 >     It might help to know how the two masters received data when they
 >     where operating independently.
 >
 >
 >         Regards
 >         Vikas Sharma
 >
 >
 >
 >     --
 >     Adrian Klaver
 > adrian.kla...@aklaver.com 
>
 >
 >


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




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

Re: Postgresql Split Brain: Which one is latest

[Vikas Sharma]

Max count is one way (vague I agree), before confirming I will ask the
application owner to have a look on data in tables as well.

Regards


On Tue, Apr 10, 2018, 17:55 Adrian Klaver  wrote:

> On 04/10/2018 09:47 AM, Vikas Sharma wrote:
> > Thanks Adrian and Edison, I also think so. At the moment I have 2
> > masters, as soon as slave is promoted to master it starts its own
> > timeline and application might have added data to either of them or
> > both, only way to find out correct master now is the instance with max
> > count of data in tables which could incur data loss as well. Correct me
> > if wrong please?
>
> Not sure max count is necessarily a valid indicator:
>
> 1) What if there was a legitimate large delete process?
>
> 2) The application/end users where looking at two different views of the
> data at different points in time. Just because the count is higher does
> not mean the data is actually valid.
>
> >
> > Thanks and Regards
> > Vikas
> >
> > On Tue, Apr 10, 2018, 17:29 Adrian Klaver  > > wrote:
> >
> > On 04/10/2018 08:04 AM, Vikas Sharma wrote:
> >  > Hi Adrian,
> >  >
> >  > This can be a good example: Application server e.g. tomcat having
> two
> >  > entries to connect to databases, one for master and 2nd for Slave
> >  > (ideally used when slave becomes master). If application is not
> > able to
> >  > connect to first, it will try to connect to 2nd.
> >
> > So the application server had a way of seeing the new master(old
> slave),
> > in spite of the network glitch, that the original master database
> > did not?
> >
> > If so and it was distributing data between the two masters on an
> unknown
> > schedule, then as Edison pointed out in another post, you really
> have a
> > split brain issue. Each master would have it's own view of the data
> and
> > latest update would really only be relevant for that master.
> >
> >  >
> >  > Regards
> >  > Vikas
> >  >
> >  > On 10 April 2018 at 15:26, Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>
> >  >  > >> wrote:
> >  >
> >  > On 04/10/2018 06:50 AM, Vikas Sharma wrote:
> >  >
> >  > Hi,
> >  >
> >  > We have postgresql 9.5 with streaming
> > replication(Master-slave)
> >  > and automatic failover. Due to network glitch we are in
> >  > master-master situation for quite some time. Please,
> > could you
> >  > advise best way to confirm which node is latest in terms
> of
> >  > updates to the postgres databases.
> >  >
> >  >
> >  > It might help to know how the two masters received data when
> they
> >  > where operating independently.
> >  >
> >  >
> >  > Regards
> >  > Vikas Sharma
> >  >
> >  >
> >  >
> >  > --
> >  > Adrian Klaver
> >  > adrian.kla...@aklaver.com 
> >  >>
> >  >
> >  >
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Postgresql Split Brain: Which one is latest

[Adrian Klaver]

On 04/10/2018 10:02 AM, Vikas Sharma wrote:
Max count is one way (vague I agree), before confirming I will ask the 
application owner to have a look on data in tables as well.


Along that line and dependent on the size of the database and individual 
tables you might try:


1) Do a plain text dump of the data from the same table from each master.

2) Diff the data dumps.



Regards






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

Suggest the best values for the postgres configuration parameters

[Raghavendra Rao J S V]

We are using postgres *9.2*  version on *Centos *operating system.

Total ram available is *80GB *. At present we don't have any connection
pool mechanisiam.  Max number of  connections are allowed is 1000.

Could you please suggest the best values for the below configuration
parameters?


   - shared_buffers
   - effective_cache_size
   - work_mem
   - maintenance_work_mem
   - checkpoint_segments
   - wal_keep_segments
   - checkpoint_completion_target
   - Max_prepared_transactions =0

-- 
Regards,
Raghavendra Rao J S V
​​

difference between checkpoint_segments and wal_keep_segments in postgres configuration file

[Raghavendra Rao J S V]

I am not clear the difference between  checkpoint_segments and
wal_keep_segments .

I would like to now below things. Please explain.Thanks in advance.


   - Difference  between *checkpoint_segments *and *wal_keep_segments *
   value
   - Role  of  *checkpoint_segments *and *wal_keep_segments *
   - Which one should has higher value.

-- 
Regards,
Raghavendra Rao J S V

Planning to change autovacuum_vacuum_scale_factor value to zero. Please suggest me if any negative impact.

[Raghavendra Rao J S V]

 We are using postgres *9.2*  version on *Centos *operating system.  We
have around 1300+ tables.
We have following auto vacuum settings are enables. Still few of the tables
which are always busy are not vacuumed. Due to that tables are bloating and
observed few areas has performance degradation.

autovacuum_max_workers = 6
autovacuum_naptime = 15s
autovacuum_vacuum_threshold = 25
autovacuum_analyze_threshold = 10
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000

To avoid the above problem, I am planning to make '
autovacuum_vacuum_scale_factor'
value to zero and  autovacuum_vacuum_threshold  value to 150. Please
suggest me does it have any negative impact.


-- 
Regards,
Raghavendra Rao J S V

Re: best way to write large data-streams quickly?

[Jerry Sievers]

Mark Moellering  writes:



>
> How long can you run COPY?  I have been looking at it more closely. 
> In some ways, it would be simple just to take data from stdin and
> send it to postgres but can I do that literally 24/7?  I am
> monitoring data feeds that will never stop and I don't know if that
> is how Copy is meant to be used or if I have to let it finish and
> start another one at some point? 

Launch a single copy and pipe data into it for an extended period an/or
bulk is fine but nothing will be visible until the statement is finished
and, if it were run in a transaction block, the block committed.

HTH

>
> Thanks for everyones' help and input!
>
> Mark Moellering
>
>
>
>

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

Re: Postgresql Split Brain: Which one is latest

[Jehan-Guillaume (ioguix) de Rorthais]

On Tue, 10 Apr 2018 17:02:39 +
Vikas Sharma  wrote:

> Max count is one way (vague I agree), before confirming I will ask the
> application owner to have a look on data in tables as well.

Maybe you could compare your tables on both sides using a tool like
pg_comparator? See:

  https://cri.ensmp.fr/people/coelho/pg_comparator/pg_comparator.html

By the way, what are you using for your auto-failover? What went wrong to
end-up with a split brain situation?

Regards,

> On Tue, Apr 10, 2018, 17:55 Adrian Klaver  wrote:
> 
> > On 04/10/2018 09:47 AM, Vikas Sharma wrote:  
> > > Thanks Adrian and Edison, I also think so. At the moment I have 2
> > > masters, as soon as slave is promoted to master it starts its own
> > > timeline and application might have added data to either of them or
> > > both, only way to find out correct master now is the instance with max
> > > count of data in tables which could incur data loss as well. Correct me
> > > if wrong please?  
> >
> > Not sure max count is necessarily a valid indicator:
> >
> > 1) What if there was a legitimate large delete process?
> >
> > 2) The application/end users where looking at two different views of the
> > data at different points in time. Just because the count is higher does
> > not mean the data is actually valid.
> >  
> > >
> > > Thanks and Regards
> > > Vikas
> > >
> > > On Tue, Apr 10, 2018, 17:29 Adrian Klaver  > > > wrote:
> > >
> > > On 04/10/2018 08:04 AM, Vikas Sharma wrote:  
> > >  > Hi Adrian,
> > >  >
> > >  > This can be a good example: Application server e.g. tomcat having  
> > two  
> > >  > entries to connect to databases, one for master and 2nd for Slave
> > >  > (ideally used when slave becomes master). If application is not  
> > > able to  
> > >  > connect to first, it will try to connect to 2nd.  
> > >
> > > So the application server had a way of seeing the new master(old  
> > slave),  
> > > in spite of the network glitch, that the original master database
> > > did not?
> > >
> > > If so and it was distributing data between the two masters on an  
> > unknown  
> > > schedule, then as Edison pointed out in another post, you really  
> > have a  
> > > split brain issue. Each master would have it's own view of the data  
> > and  
> > > latest update would really only be relevant for that master.
> > >  
> > >  >
> > >  > Regards
> > >  > Vikas
> > >  >
> > >  > On 10 April 2018 at 15:26, Adrian Klaver  
> > > mailto:adrian.kla...@aklaver.com>  
> > >  >  > > >> wrote:  
> > >  >
> > >  > On 04/10/2018 06:50 AM, Vikas Sharma wrote:
> > >  >
> > >  > Hi,
> > >  >
> > >  > We have postgresql 9.5 with streaming  
> > > replication(Master-slave)  
> > >  > and automatic failover. Due to network glitch we are in
> > >  > master-master situation for quite some time. Please,  
> > > could you  
> > >  > advise best way to confirm which node is latest in terms  
> > of  
> > >  > updates to the postgres databases.
> > >  >
> > >  >
> > >  > It might help to know how the two masters received data when  
> > they  
> > >  > where operating independently.
> > >  >
> > >  >
> > >  > Regards
> > >  > Vikas Sharma
> > >  >
> > >  >
> > >  >
> > >  > --
> > >  > Adrian Klaver
> > >  > adrian.kla...@aklaver.com   
> > >  > >>
> > >  >
> > >  >  
> > >
> > >
> > > --
> > > Adrian Klaver
> > > adrian.kla...@aklaver.com 
> > >  
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com
> >  



-- 
Jehan-Guillaume de Rorthais
Dalibo

Re: ERROR: found multixact from before relminmxid

[Peter Geoghegan]

On Tue, Apr 10, 2018 at 4:31 AM, Alexandre Arruda  wrote:
> Actualy, I first notice the problem in logs by autovacuum:
>
> 2018-04-10 08:22:15.385 -03 [55477] CONTEXT:  automatic vacuum of
> table "production.public.fn06t"
> 2018-04-10 08:22:16.815 -03 [55477] ERROR:  found multixact 68834765
> from before relminmxid 73262006
>
> production=# vacuum analyze verbose fn06t;
> INFO:  vacuuming "public.fn06t"
> ERROR:  found multixact 76440919 from before relminmxid 122128619

Do you think that CLUSTER was run before regular VACUUM/autovacuum
showed this error, though?

Have you noticed any data loss? Things look okay when you do your dump
+ restore, right? The problem, as far as you know, is strictly that
CLUSTER + VACUUM refuse to finish/raise these multixactid errors?

-- 
Peter Geoghegan

Re: Using enum instead of join tables

[PT]

On Tue, 10 Apr 2018 11:24:49 +0100
hmidi slim  wrote:

> Hi,
> Is it a good thing to use enum type such a column in a table instead of
> making a foreign key which references to another table?
> I found these links talking about enum and when I will use them:
> http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/
> https://www.percona.com/blog/2008/01/24/enum-fields-vs-varchar-vs-int-joined-table-what-is-faster/
> 
> Is there any advices or new updates to use enum type in order to decrease
> the number of join between the table and optimize the performance and query
> runtime?

Performance isn't always the only issue.

Enums work well if you're 100% sure that the values will never change.
While it's not impossible to change them, it's essentially a schema change,
which can be tricky to do on an active databse, due to locking.

A foreign table has the advantage of being easy to change, but with the
extra join required to get the text representation.

A foreign table with a text field doesn't require the join, but takes up
more space and requires a cascading change if you need to change an enum
value. Adding new values is pretty easy, though.

Another option is a text field with a check constraint to ensure the data
in it stays valid. This is somewhere in between as changing the check constraint
is easier than with an enum, but harder than with a foriegn table. It doesn't
require a join to get the text representation of the value, but takes up more
space (depending on the lenght of the text for each value).

So you have to balance the requirements of your use case to decide what method
is best.

-- 
Bill Moran

Re: ERROR: found multixact from before relminmxid

[Andres Freund]

On 2018-04-10 08:31:20 -0300, Alexandre Arruda wrote:
> 2018-04-09 23:51 GMT-03:00 Peter Geoghegan :
> > On Mon, Apr 9, 2018 at 6:56 PM, Alexandre Arruda  wrote:
> >> (... and all other indexes returns null too)
> >>
> >> I tried with bt_index_check too. Same results.
> >
> > That's interesting, because it tells me that you have a table that
> > appears to not be corrupt, despite the CLUSTER error. Also, the error
> > itself comes from sanity checking added to MultiXact freezing fairly
> > recently, in commit 699bf7d0.
> >
> > You didn't say anything about regular VACUUM being broken. Do you find
> > that it works without any apparent issue?
> >
> > I have a suspicion that this could be a subtle bug in
> > CLUSTER/freezing. The only heap_freeze_tuple() caller is code used by
> > CLUSTER, so it's not that hard to imagine a MultiXact freezing bug
> > that is peculiar to CLUSTER. Though I haven't thought about it in much
> > detail.
> >
> > --
> > Peter Geoghegan
> 
> Hi Peter,
> 
> Actualy, I first notice the problem in logs by autovacuum:
> 
> 2018-04-10 08:22:15.385 -03 [55477] CONTEXT:  automatic vacuum of
> table "production.public.fn06t"
> 2018-04-10 08:22:16.815 -03 [55477] ERROR:  found multixact 68834765
> from before relminmxid 73262006
> 
> production=# vacuum analyze verbose fn06t;
> INFO:  vacuuming "public.fn06t"
> ERROR:  found multixact 76440919 from before relminmxid 122128619

What does the function in
https://www.postgresql.org/message-id/20180319181723.ugaf7hfkluqyo...@alap3.anarazel.de
say about your table?

Could you post pg_controldata output and
SELECT * FROM pg_class WHERE oid = 'public.fn06t'::regclass;
?

Greetings,

Andres Freund

Re: ERROR: found multixact from before relminmxid

[Alexandre Arruda]

2018-04-10 19:53 GMT-03:00 Andres Freund :
> On 2018-04-10 08:31:20 -0300, Alexandre Arruda wrote:
>> 2018-04-09 23:51 GMT-03:00 Peter Geoghegan :
>> > On Mon, Apr 9, 2018 at 6:56 PM, Alexandre Arruda  
>> > wrote:
>> >> (... and all other indexes returns null too)
>> >>
>> >> I tried with bt_index_check too. Same results.
>> >
>> > That's interesting, because it tells me that you have a table that
>> > appears to not be corrupt, despite the CLUSTER error. Also, the error
>> > itself comes from sanity checking added to MultiXact freezing fairly
>> > recently, in commit 699bf7d0.
>> >
>> > You didn't say anything about regular VACUUM being broken. Do you find
>> > that it works without any apparent issue?
>> >
>> > I have a suspicion that this could be a subtle bug in
>> > CLUSTER/freezing. The only heap_freeze_tuple() caller is code used by
>> > CLUSTER, so it's not that hard to imagine a MultiXact freezing bug
>> > that is peculiar to CLUSTER. Though I haven't thought about it in much
>> > detail.
>> >
>> > --
>> > Peter Geoghegan
>>
>> Hi Peter,
>>
>> Actualy, I first notice the problem in logs by autovacuum:
>>
>> 2018-04-10 08:22:15.385 -03 [55477] CONTEXT:  automatic vacuum of
>> table "production.public.fn06t"
>> 2018-04-10 08:22:16.815 -03 [55477] ERROR:  found multixact 68834765
>> from before relminmxid 73262006
>>
>> production=# vacuum analyze verbose fn06t;
>> INFO:  vacuuming "public.fn06t"
>> ERROR:  found multixact 76440919 from before relminmxid 122128619
>
> What does the function in
> https://www.postgresql.org/message-id/20180319181723.ugaf7hfkluqyo...@alap3.anarazel.de
> say about your table?
>
> Could you post pg_controldata output and
> SELECT * FROM pg_class WHERE oid = 'public.fn06t'::regclass;
> ?
>
> Greetings,
>
> Andres Freund

pg_control version number:1002
Catalog version number:   201707211
Database system identifier:   6513284223567708119
Database cluster state:   in production
pg_control last modified: Ter 10 Abr 2018 23:51:56 BRT
Latest checkpoint location:   183/E4849618
Prior checkpoint location:183/E19F8A80
Latest checkpoint's REDO location:183/E202C430
Latest checkpoint's REDO WAL file:0001018300E2
Latest checkpoint's TimeLineID:   1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:  0:494263452
Latest checkpoint's NextOID:  1816390854
Latest checkpoint's NextMultiXactId:  256298856
Latest checkpoint's NextMultiOffset:  529468697
Latest checkpoint's oldestXID:10338
Latest checkpoint's oldestXID's DB:   866864162
Latest checkpoint's oldestActiveXID:  494263450
Latest checkpoint's oldestMultiXid:   73262006
Latest checkpoint's oldestMulti's DB: 866864162
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:Ter 10 Abr 2018 23:50:14 BRT
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline:   0
Backup start location:0/0
Backup end location:  0/0
End-of-backup record required:no
wal_level setting:replica
wal_log_hints setting:off
max_connections setting:  600
max_worker_processes setting: 8
max_prepared_xacts setting:   0
max_locks_per_xact setting:   64
track_commit_timestamp setting:   off
Maximum data alignment:   8
Database block size:  8192
Blocks per segment of large relation: 131072
WAL block size:   8192
Bytes per WAL segment:16777216
Maximum length of identifiers:64
Maximum columns in an index:  32
Maximum size of a TOAST chunk:1996
Size of a large-object chunk: 2048
Date/time type storage:   64-bit integers
Float4 argument passing:  by value
Float8 argument passing:  by value
Data page checksum version:   0
Mock authentication nonce:
a8eb07ea85c4bbeaf202020d036277b276bda47ef55c1456723ec2b3c40386b1


SELECT * FROM pg_class WHERE oid = 'public.fn06t'::regclass;
-[ RECORD 1 ]---+--
relname | fn06t
relnamespace| 2200
reltype | 866874136
reloftype   | 0
relowner| 10
relam   | 0
relfilenode | 1092835324
reltablespace   | 0
relpages| 218797
reltuples   | 2.60181e+06
relallvisible   | 192741
reltoastrelid   | 866874160
relhasindex | t
relisshared | f
relpersistence  | p
relkind | r
relnatts| 154
relchecks   | 0
relhasoids  | t
relhaspkey  | t
relhasrules | f
relhastriggers  | t
relhassubclass  | f
relrowsecurity  | f
relforcerowsecurity | f
relispopulated  | t
r

Re: ERROR: found multixact from before relminmxid

[Peter Geoghegan]

On Tue, Apr 10, 2018 at 7:54 PM, Alexandre Arruda  wrote:
> pg_control version number:1002

Andres was also asking about his check_rel() function, from
https://www.postgresql.org/message-id/20180319181723.ugaf7hfkluqyo...@alap3.anarazel.de.
Can you check that out as well, please? You'll need to be able to
install the pageinspect contrib module.

-- 
Peter Geoghegan

Re: ERROR: found multixact from before relminmxid

[Alexandre Arruda]

2018-04-10 19:09 GMT-03:00 Peter Geoghegan :
> On Tue, Apr 10, 2018 at 4:31 AM, Alexandre Arruda  wrote:
>> Actualy, I first notice the problem in logs by autovacuum:
>>
>> 2018-04-10 08:22:15.385 -03 [55477] CONTEXT:  automatic vacuum of
>> table "production.public.fn06t"
>> 2018-04-10 08:22:16.815 -03 [55477] ERROR:  found multixact 68834765
>> from before relminmxid 73262006
>>
>> production=# vacuum analyze verbose fn06t;
>> INFO:  vacuuming "public.fn06t"
>> ERROR:  found multixact 76440919 from before relminmxid 122128619
>
> Do you think that CLUSTER was run before regular VACUUM/autovacuum
> showed this error, though?

Yes, because the table is clustered in the old database and
reclustered when it was reloaded in the version 10.
But the table was not clustered again.

> Have you noticed any data loss? Things look okay when you do your dump
> + restore, right? The problem, as far as you know, is strictly that
> CLUSTER + VACUUM refuse to finish/raise these multixactid errors?

I did not see, apparently, any data loss. A dump/reload in fresh db
shows the same data os production. I verify this by md5sum in
genereted files created by a select statment (ordered by pkey) in both
databases (prod and test).
The problem is with regular vaccum that not complete. Cluster or
vacuum FULL is a try to correct the problem.

But I'm affraid if any data can be corrupted along time.

Re: difference between checkpoint_segments and wal_keep_segments in postgres configuration file

[Michael Paquier]

On Tue, Apr 10, 2018 at 11:06:54PM +0530, Raghavendra Rao J S V wrote:
> I am not clear the difference between checkpoint_segments and
> wal_keep_segments .
> 
> I would like to now below things. Please explain.Thanks in advance.
> 
>- Difference  between *checkpoint_segments *and *wal_keep_segments *
>value
>- Role  of  *checkpoint_segments *and *wal_keep_segments *
>- Which one should has higher value.

Documentation is king here.  For checkpoint_segments:
https://www.postgresql.org/docs/9.4/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS

  Maximum number of log file segments between automatic WAL
  checkpoints (each segment is normally 16 megabytes). The default is
  three segments. Increasing this parameter can increase the amount of
  time needed for crash recovery. This parameter can only be set in
  the postgresql.conf file or on the server command line.

For wal_keep_segments:
https://www.postgresql.org/docs/9.4/static/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-SENDER

  Specifies the minimum number of past log file segments kept in the
  pg_xlog directory, in case a standby server needs to fetch them for
  streaming replication. Each segment is normally 16 megabytes. If a
  standby server connected to the sending server falls behind by more than
  wal_keep_segments segments, the sending server might remove a WAL
  segment still needed by the standby, in which case the replication
  connection will be terminated. Downstream connections will also
  eventually fail as a result. (However, the standby server can recover by
  fetching the segment from archive, if WAL archiving is in use.)

Mentioning checkpoint_segments implies that you are using PostgreSQL 9.4
or older versions as this has been removed and replaced by max_wal_size
in 9.5.  You should consider upgrading to a newer version.

Hence the first is used in the context of normal operations to decide
the frequency of checkpoints when those are triggered by volume.  The
second can be used with streaming replication to give a standby a higher
catchup window.  Giving value to one or the other depends on the
context, and both are usable in completely different circumstances.
--
Michael


signature.asc
Description: PGP signature

dblink: give search_path

[Thiemo Kellner]

Hi all

I try to execute a function not in the Schema I connect to with  
dblink. Is there way to tell dblink to set search_path in a specific  
way? I have not found a solution in the documentation. I tried with  
the set search_path definition in the function declarations to no avail.


Function Schema: logger
Database: act
User: act
User Default Schema: act

Kind regards

Thiemo

--
Öffentlicher PGP-Schlüssel:  
http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF



This message was sent using IMP, the Internet Messaging Program.

psql variable to plpgsql?

[Thiemo Kellner]

Hi all

Is there a way to pass the value of a psql variable into function  
code? I create a schema with help of psql variable


   \set SCHEMA_NAME LOGGER
   create
 schema :SCHEMA_NAME;

I would like to create a function that has the Schema hard coded like
   declare
  V_SCHEMA_NAME text := :SCHEMA_NAME;
   begin
but as the plpgsql code is within quotes, it Fails.

Kind regards

Thiemo

--
Öffentlicher PGP-Schlüssel:  
http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF



This message was sent using IMP, the Internet Messaging Program.

Re: ERROR: found multixact from before relminmxid

[Pavan Deolasee]

On Wed, Apr 11, 2018 at 8:31 AM, Alexandre Arruda 
wrote:

> 2018-04-10 19:09 GMT-03:00 Peter Geoghegan :
> > On Tue, Apr 10, 2018 at 4:31 AM, Alexandre Arruda 
> wrote:
> >> Actualy, I first notice the problem in logs by autovacuum:
> >>
> >> 2018-04-10 08:22:15.385 -03 [55477] CONTEXT:  automatic vacuum of
> >> table "production.public.fn06t"
> >> 2018-04-10 08:22:16.815 -03 [55477] ERROR:  found multixact 68834765
> >> from before relminmxid 73262006
> >>
> >> production=# vacuum analyze verbose fn06t;
> >> INFO:  vacuuming "public.fn06t"
> >> ERROR:  found multixact 76440919 from before relminmxid 122128619
> >
> > Do you think that CLUSTER was run before regular VACUUM/autovacuum
> > showed this error, though?
>
> Yes, because the table is clustered in the old database and
> reclustered when it was reloaded in the version 10.
> But the table was not clustered again.
>
>
I wonder if we're staring at some race condition in visibility map where a
previous vacuum inadvertently skips a page because the visibility map bit
is set, thus leaving behind an unfrozen multixid. The page then again
becomes !all_visible and the next vacuum then complains about the unfrozen
multixid.

Thanks,
Pavan

-- 
 Pavan Deolasee   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: ERROR: found multixact from before relminmxid

[Andres Freund]

On April 10, 2018 11:51:40 PM PDT, Pavan Deolasee  
wrote:
>On Wed, Apr 11, 2018 at 8:31 AM, Alexandre Arruda 
>wrote:
>
>> 2018-04-10 19:09 GMT-03:00 Peter Geoghegan :
>> > On Tue, Apr 10, 2018 at 4:31 AM, Alexandre Arruda
>
>> wrote:
>> >> Actualy, I first notice the problem in logs by autovacuum:
>> >>
>> >> 2018-04-10 08:22:15.385 -03 [55477] CONTEXT:  automatic vacuum of
>> >> table "production.public.fn06t"
>> >> 2018-04-10 08:22:16.815 -03 [55477] ERROR:  found multixact
>68834765
>> >> from before relminmxid 73262006
>> >>
>> >> production=# vacuum analyze verbose fn06t;
>> >> INFO:  vacuuming "public.fn06t"
>> >> ERROR:  found multixact 76440919 from before relminmxid 122128619
>> >
>> > Do you think that CLUSTER was run before regular VACUUM/autovacuum
>> > showed this error, though?
>>
>> Yes, because the table is clustered in the old database and
>> reclustered when it was reloaded in the version 10.
>> But the table was not clustered again.
>>
>>
>I wonder if we're staring at some race condition in visibility map
>where a
>previous vacuum inadvertently skips a page because the visibility map
>bit
>is set, thus leaving behind an unfrozen multixid. The page then again
>becomes !all_visible and the next vacuum then complains about the
>unfrozen
>multixid.

Should be possible to write a query using page inspect and freespacemap to make 
sure they at least currently match.

Andres

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.