Re: [GENERAL] BDR, wal segment has been removed, is it possible move forward?

[Craig Ringer]

On 7 September 2017 at 21:16, milist ujang  wrote:

> Hi Craig,
>
> On Wed, Sep 6, 2017 at 4:07 PM, Craig Ringer 
> wrote:
>
>>
>> You could drop and re-create the replication slot, I guess. But your
>> nodes would be hopelessly out of sync and need manual resync (with data
>> replication disabled) of one node vs another.
>>
>
> Thanks for pointing to replication slot.
> I Simulate the similar situation in dev env by remove the wal segment on
> node1, when  node2 keep inserting into a table, now it perfectly can move
> forward to latest wal segment, but the difference situation is at
> node_status.
>
> In production node_status is  i  in node1
>

there's a known bug in bdr1 where sometimes the node status doesn't update
from 'i' after joining.


> can I safely update the node_status directy on bdr.bdr_nodes?
>

Usually not. In this one specific case where a node is known to be fully
joined and online, but its status is stuck at 'i', yes.


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

[GENERAL] B-tree index on a VARCHAR(4000) column

[Ron Johnson]

Hi,

v 9.2.7

Based on LENGTH(offending_column), none of the values are more than 144 
bytes in this 44.2M row table.  Even though VARCHAR is, by definition, 
variable length, are there any internal design issues which would make 
things more efficient if it were dropped to, for example, VARCHAR(256)?


(I don't have access to the source code or to development boxes, so can't 
just test this on my own.)


Thanks,

--
World Peace Through Nuclear Pacification



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

Re: [GENERAL] pg_rewind issue

[Michael Paquier]

On Thu, Sep 7, 2017 at 9:06 PM, James Sewell  wrote:
> A client has have been having problems with pg_rewind.
>
> They have two PostgreSQL (Oracle Enterprise Linux 7, 9.6.4) nodes in
> streaming replication and  follow these steps:
>
> 1. Stop the master
> 2. Promote the standby
> 3. After successful failover wait some time (a lot of data is written)
> 4. Issue a checkpoint on the new master
> 5. Issue a pg_rewind on the old master
> 6. Start up the old master with a recovery.conf pointing at the new master.

How are you stopping the primary at step 1)? If you stopped it
cleanly, then it had the occasion to send to the standby the WAL
record corresponding to the shutdown checkpoint, so at step 2) the
standby would use a WAL history that has not forked from the primary
when it is promoted, so the ex-primary could be reused as-is as a
standby. Hence step 5 would not be necessary.
-- 
Michael


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

Re: [GENERAL] Performance with high correlation in group by on PK

[Jeff Janes]

On Tue, Aug 29, 2017 at 1:20 AM, Alban Hertroys  wrote:

> On 28 August 2017 at 21:32, Jeff Janes  wrote:
> > On Mon, Aug 28, 2017 at 5:22 AM, Alban Hertroys 
> wrote:
> >>
> >> Hi all,
> >>
> >> It's been a while since I actually got to use PG for anything serious,
> >> but we're finally doing some experimentation @work now to see if it is
> >> suitable for our datawarehouse. So far it's been doing well, but there
> >> is a particular type of query I run into that I expect we will
> >> frequently use and that's choosing a sequential scan - and I can't
> >> fathom why.
> >>
> >> This is on:
> >>
> >>
> >> The query in question is:
> >> select "VBAK_MANDT", max("VBAK_VBELN")
> >>   from staging.etl1_vbak
> >>  group by "VBAK_MANDT";
> >>
> >> This is the header-table for another detail table, and in this case
> >> we're already seeing a seqscan. The thing is, there are 15M rows in
> >> the table (disk usage is 15GB), while the PK is on ("VBAK_MANDT",
> >> "VBAK_VBELN") with very few distinct values for "VBAK_MANDT" (in fact,
> >> we only have 1 at the moment!).
> >
> >
> > You need an "index skip-scan" or "loose index scan".  PostgreSQL doesn't
> > currently detect and implement them automatically, but you can use a
> > recursive CTE to get it to work.  There are some examples at
> > https://wiki.postgresql.org/wiki/Loose_indexscan
>
> Thanks Jeff, that's an interesting approach. It looks very similar to
> correlated subqueries.
>
> Unfortunately, it doesn't seem to help with my issue. The CTE is
> indeed fast, but when querying the results from the 2nd level ov the
> PK with the CTE results, I'm back at a seqscan on pdw2_vbak again.
>

Something like this works:

create table foo as select trunc(random()*5) as col1, random() as col2 from
generate_series(1,1);
create index on foo (col1, col2);
vacuum analyze foo;


with recursive t as (
   select * from (select col1, col2 from foo order by col1 desc, col2 desc
limit 1) asdfsaf
union all
  select
 (select col1 from foo where foo.col1 < t.col1 order by col1 desc, col2
desc limit 1) as col1,
 (select col2 from foo where foo.col1 < t.col1 order by col1 desc, col2
desc limit 1) as col2
   from t where t.col1 is not null
)
select * from t where t is not null;

It is pretty ugly that you need one subquery in the select list for each
column to be returned.  Maybe someone can find a way to avoid that part.  I
tried using lateral joins to get around it, but couldn't make that work.

Cheers,

Jeff

Re: [GENERAL] WAL & ready files retained after turning off log shipping

[Ron Johnson]

On 09/07/2017 05:07 PM, Michael Paquier wrote:

On Thu, Sep 7, 2017 at 11:08 PM, Tom Lane  wrote:

Manual cleanup shouldn't be very hard, fortunately.  Run pg_controldata
to see where the last checkpoint is, and delete WAL files whose names
indicate they are before that (but not the one including the checkpoint!).
If you don't intend to do archiving any more, you can just flush all the
.ready files (and .done if any) without much thought.

It would be less risky to do that as a two-time move:
- First change archive_command to /sbin/true and let all archives be
switched to .done.
- And then disable archive_mode.


Interesting.  Thanks.

--
World Peace Through Nuclear Pacification



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

Re: [GENERAL] WAL & ready files retained after turning off log shipping

[Michael Paquier]

On Thu, Sep 7, 2017 at 11:08 PM, Tom Lane  wrote:
> Manual cleanup shouldn't be very hard, fortunately.  Run pg_controldata
> to see where the last checkpoint is, and delete WAL files whose names
> indicate they are before that (but not the one including the checkpoint!).
> If you don't intend to do archiving any more, you can just flush all the
> .ready files (and .done if any) without much thought.

It would be less risky to do that as a two-time move:
- First change archive_command to /sbin/true and let all archives be
switched to .done.
- And then disable archive_mode.
-- 
Michael


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

Re: [GENERAL] column names query

[John R Pierce]

On 9/7/2017 12:18 AM, haman...@t-online.de wrote:

is there a simple way to retrieve column names from a query - basically the way 
psql adds
column headings when I do a select?


if you're using libpq to do your queries, PQfname(*result, 
column_number) returns the name of that column number.


there are equivalent functions in most other APIs.


--
john r pierce, recycling bits in santa cruz



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

Re: [GENERAL] Confused about max_standby_streaming_delay

[Jeff Janes]

On Thu, Sep 7, 2017 at 1:16 AM, Robert Inder 
wrote:

>
>
> On 6 September 2017 at 20:47, Jeff Janes  wrote:
>
>>
>>> Have I misunderstood something?  Or is Postgres not actually configured
>>> the way I think it is?
>>>
>>
>> The standby will wait for ten minutes to obtain the lock it wishes to
>> obtain.  In 9.4, if something other than dump of database b was already
>> blocking it for 8 minutes before the dump starts, then the dump of database
>> b will only have 2 minutes, not 10, before it gets cancelled.
>>
>
> H...
> You're saying that the time for dumping database b may be spent 8 minutes
> waiting on a lock then 2 minutes actually dumping.
>

No, I'm saying that maybe the replay process was already waiting for
something else for 8 minutes before the pg_dump of database b even
attempted to start.  So it would be cancelled after 2 minutes.

Are these database a, database b, etc. different databases in the same
postgres instance (CREATE DATABASE A) or are they entirely different
postgres instances (initdb -D /opt/database_a)?  Your original description
of different unix users with different installations made me think the 2nd
case is the one, but just want to make sure.

Cheers,

Jeff

Re: [GENERAL] column names query

[Francisco Olarte]

Hi Hamman:

On Thu, Sep 7, 2017 at 3:17 PM,   wrote:
> I would like to do something like
> \copy (select heading(select  my query here)) to /tmp/heading_testfile1

It's already been pointed out, you can do something using CSV copy
with headers ( Why headers are not allowed in other formats remains a
mistery to me), and LIMIT 0 ( I would try adding AND FALSE to the
where clause better, it may lead to faster response, although I doubt
it) .

Francisco Olarte.


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

Re: [GENERAL] WAL & ready files retained after turning off log shipping

[Tom Lane]

Ron Johnson  writes:
> On 09/07/2017 09:08 AM, Tom Lane wrote:
>> Manual cleanup shouldn't be very hard, fortunately.  Run pg_controldata
>> to see where the last checkpoint is, and delete WAL files whose names
>> indicate they are before that (but not the one including the checkpoint!).

> All WAL files after log shipping was stopped will keep accumulating "forever"?

Hmm ... on second thought, I think if you just remove the .ready/.done
files, the next checkpoint should clean up the old WAL files.  That'd
certainly be safer than doing it manually.

regards, tom lane


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

Re: [GENERAL] WAL & ready files retained after turning off log shipping

[Ron Johnson]

On 09/07/2017 09:08 AM, Tom Lane wrote:

Ron Johnson  writes:

After disabling log shipping via setting "archive_mode = off", and then
running, "pg_ctl reload", old WAL files and their associated .ready files
aren't being deleted.

Hmm.  I might be misremembering, but I think that it's the archiver
process that is in charge of deleting those files, so that this behavior
doesn't seem surprising.

I don't think anybody's thought very hard about how to clean up if
archiving had been running and then you turn it off with not everything
having been archived.


Presumably, restarting postgres will fix that?


Manual cleanup shouldn't be very hard, fortunately.  Run pg_controldata
to see where the last checkpoint is, and delete WAL files whose names
indicate they are before that (but not the one including the checkpoint!).


All WAL files after log shipping was stopped will keep accumulating "forever"?


If you don't intend to do archiving any more, you can just flush all the
.ready files (and .done if any) without much thought.


Many thanks.

--
World Peace Through Nuclear Pacification



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

Re: [GENERAL] WAL & ready files retained after turning off log shipping

[Tom Lane]

Ron Johnson  writes:
> After disabling log shipping via setting "archive_mode = off", and then 
> running, "pg_ctl reload", old WAL files and their associated .ready files 
> aren't being deleted.

Hmm.  I might be misremembering, but I think that it's the archiver
process that is in charge of deleting those files, so that this behavior
doesn't seem surprising.

I don't think anybody's thought very hard about how to clean up if
archiving had been running and then you turn it off with not everything
having been archived.

Manual cleanup shouldn't be very hard, fortunately.  Run pg_controldata
to see where the last checkpoint is, and delete WAL files whose names
indicate they are before that (but not the one including the checkpoint!).
If you don't intend to do archiving any more, you can just flush all the
.ready files (and .done if any) without much thought.

regards, tom lane


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

[GENERAL] WAL & ready files retained after turning off log shipping

[Ron Johnson]

Hi,

v8.4 (and there's nothing I can do about it).

After disabling log shipping via setting "archive_mode = off", and then 
running, "pg_ctl reload", old WAL files and their associated .ready files 
aren't being deleted.


Is there any document you can point me to as to why this is happening, and 
what I can do to stop it?


Thanks

--
World Peace Through Nuclear Pacification



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

Re: [GENERAL] column names query

[Manuel Gómez]

On Thu, Sep 7, 2017 at 3:28 PM  wrote:

> Example query
> select a.col1, case when a.col2 > 0 then a.col3 else b.xcol1 end as mycol3
> from a left join b on 
> Expected response
> col1 mycol3
>

This may be overkill, but works:

postgres=# \copy (select 1 as foo, 2 as bar limit 0) to stdout with (format
csv, header, delimiter ' ')
foo bar

Re: [GENERAL] column names query

[hamann . w]

>> SELECT table_name, column_name
>> FROM information_schema.columns
>> WHERE table_name = 'your_name';
>>  
>> - Original Message -From: hamann w To: 
>> pgsql-general@postgresql.orgSent: Thu, 07 Sep 2017 07:18:12 - 
>> (UTC)Subject: [GENERAL] column names query
>> 
>> 
>> 
>> Hi,
>> 
>> is there a simple way to retrieve column names from a query - basically the 
>> way psql addscolumn headings when I do a select?
>> 
>> Best regardsWolfgang Hamann
>> 
>> 
>> 
>> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)To make 
>> changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
>> 

Hi,

I am already using something like this (but using pg_attribute table) to 
retrieve current table layout
for static tables. At the moment I am looking for a solution for ad hoc queries

Example query
select a.col1, case when a.col2 > 0 then a.col3 else b.xcol1 end as mycol3 from 
a left join b on 
Expected response
col1 mycol3

Obviously, I could save the result into a temp table and retrieve the table's 
heading.

Best regards
Wolfgang Hamann 





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

Re: [GENERAL] column names query

[hamann . w]

>> On Thu, Sep 7, 2017 at 9:18 AM,   wrote:
>> > is there a simple way to retrieve column names from a query - basically 
>> > the way psql adds
>> > column headings when I do a select?
>> 
>> How do you do the query? I mean, JDBC, PERL? After all psql is just a
>> C program doing a query using libpq and can do it, we may provide some
>> useful info if you show yours first.
>> 
>> Francisco Olarte.

Hi,

I imagine this situation:
I start working on a query... when I am happy with the result, I see a table 
(with headers) in psql.
Now I can do
\copy (select  my query here) to /tmp/testfile1
I would like to do something like
\copy (select heading(select  my query here)) to /tmp/heading_testfile1

Best regards
Wolfgang Hamann




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

Re: [GENERAL] BDR, wal segment has been removed, is it possible move forward?

[milist ujang]

Hi Craig,

On Wed, Sep 6, 2017 at 4:07 PM, Craig Ringer  wrote:

>
> You could drop and re-create the replication slot, I guess. But your nodes
> would be hopelessly out of sync and need manual resync (with data
> replication disabled) of one node vs another.
>

Thanks for pointing to replication slot.
I Simulate the similar situation in dev env by remove the wal segment on
node1, when  node2 keep inserting into a table, now it perfectly can move
forward to latest wal segment, but the difference situation is at
node_status.

In production node_status is  i  in node1 but  r  in node2, where on my dev
both nodes keep  r  , even I waited to let it may change quite long.

can I safely update the node_status directy on bdr.bdr_nodes?


-- 
regards

ujang jaenudin | DBA Consultant (Freelancer)
http://ora62.wordpress.com
http://id.linkedin.com/pub/ujang-jaenudin/12/64/bab

Re: [GENERAL] column names query

[stimits]

SELECT table_name, column_name
FROM information_schema.columns
WHERE table_name = 'your_name';
 
- Original Message -From: hamann w To: 
pgsql-general@postgresql.orgSent: Thu, 07 Sep 2017 07:18:12 - (UTC)Subject: 
[GENERAL] column names query



Hi,

is there a simple way to retrieve column names from a query - basically the way 
psql addscolumn headings when I do a select?

Best regardsWolfgang Hamann



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

[GENERAL] Plans to lift no write limitation on parallelism?

[Alexander Stoddard]

The documentation for 9.6 suggests the future possibility of lifting the
limitation on parallel query where it cannot be used in a query that writes
data.  Is lifting this limitation on the roadmap?

https://www.postgresql.org/docs/current/static/when-can-parallel-query-be-used.html

Thank you,
Alex Stoddard

[GENERAL] pg_rewind issue

[James Sewell]

Hi,

A client has have been having problems with pg_rewind.

They have two PostgreSQL (Oracle Enterprise Linux 7, 9.6.4) nodes in
streaming replication and  follow these steps:

1. Stop the master
2. Promote the standby
3. After successful failover wait some time (a lot of data is written)
4. Issue a checkpoint on the new master
5. Issue a pg_rewind on the old master
6. Start up the old master with a recovery.conf pointing at the new master.

The recovery.conf is identical in both cases (it points at a VIP).

They are seeing the old master come up as a standby after replaying some
logs from the archive, then connect to the master as a streaming replica
but fail when it asks for a log on the new timeline which has never
existed. It isn't in the archive, or in the master pg_xlog. The numbering
does exist in the old timeline (not sure if this is relevant or a
coincidence).

This has been hit  three times now with identical results. The old master
needs to be destroyed at this stage.

In the most recent example the old master is asking for
00150C7300A9 from the streaming session. Looking at the
15.history file (timeline 20) I can see:

20  C74/4500no recovery target specified+

So this makes no sense, as the file it's asking for is before
00150C740044
so never existed on either machine (the old master has never been in
timeline 15). You can see that this file is in the archive on the new
master.

They are using pg_backrest for archive management, which works fine the
rest of the time.

Can anyone think of a way this could be happening? Are we missing a step
above?

Cheers,
James Sewell,

-- 

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

Re: [GENERAL] Confused about max_standby_streaming_delay

[Robert Inder]

On 6 September 2017 at 20:47, Jeff Janes  wrote:

>
>> Have I misunderstood something?  Or is Postgres not actually configured
>> the way I think it is?
>>
>
> The standby will wait for ten minutes to obtain the lock it wishes to
> obtain.  In 9.4, if something other than dump of database b was already
> blocking it for 8 minutes before the dump starts, then the dump of database
> b will only have 2 minutes, not 10, before it gets cancelled.
>

H...
You're saying that the time for dumping database b may be spent 8 minutes
waiting on a lock then 2 minutes actually dumping.

But would it not still be at least 10 minutes elapsed between the finish
time of the previous dump and the following dump starting (let alone
finishing)?  And that's what I'm not seeing...

When one dump fails, there is no 10-minute gap in the mod. times of the
other successful dump files

​

> So, are there any long running jobs in database b other than the pg_dump?
>
​There shouldn't be.


The standby server is (well, should be!) essentially idle:
 we're using it to do the dumps, so that they don't
 get under the feet of live queries, but ​(I think -- I'll have to check!)
that is all


>
> Cheers,
>
> Jeff
>

​Robert.​

-- 
Robert Inder,0131 229 1052 / 07808 492
213
Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
   Interactions speak louder than
words

Re: [GENERAL] column names query

[Francisco Olarte]

On Thu, Sep 7, 2017 at 9:18 AM,   wrote:
> is there a simple way to retrieve column names from a query - basically the 
> way psql adds
> column headings when I do a select?

How do you do the query? I mean, JDBC, PERL? After all psql is just a
C program doing a query using libpq and can do it, we may provide some
useful info if you show yours first.

Francisco Olarte.


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

[GENERAL] column names query

[hamann . w]

Hi,

is there a simple way to retrieve column names from a query - basically the way 
psql adds
column headings when I do a select?

Best regards
Wolfgang Hamann



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