[GENERAL] postgres standby won't start

[Ramalingam, Sankarakumar]

We have a standby set up between two sites in two different locations. The 
replication was going on well and suddenly it stopped due to error
2015-09-08 16:07:51 EDT LOG:  streaming replication successfully connected to 
primary
2015-09-08 16:07:51 EDT FATAL:  could not receive data from WAL stream: FATAL:  
requested WAL segment 000C035E00F0 has already been removed

I am unable to start the DB as well.
Should I restore a fresh copy from production on to this standby to make things 
in order? If yes, how to go about it. I am quite new to Postgres. Any 
help/suggestions will be greatly appreciated.


Thanks
Kumar Ramalingam
Global Database Administration
Elavon, Atlanta , GA

678 731 5288

The information contained in this e-mail and in any attachments is intended 
only for the person or entity to which it is addressed and may contain 
confidential and/or privileged material. Any review, retransmission, 
dissemination or other use of, or taking of any action in reliance upon, this 
information by persons or entities other than the intended recipient is 
prohibited. This message has been scanned for known computer viruses.

Re: [GENERAL] postgres standby won't start

[Andreas Kretschmer]

Ramalingam, Sankarakumar  wrote:

> We have a standby set up between two sites in two different locations. The
> replication was going on well and suddenly it stopped due to error
> 
> 2015-09-08 16:07:51 EDT LOG:  streaming replication successfully connected to
> primary
> 
> 2015-09-08 16:07:51 EDT FATAL:  could not receive data from WAL stream: 
> FATAL: 
> requested WAL segment 000C035E00F0 has already been removed
> 
>  
> 
> I am unable to start the DB as well.
> 
> Should I restore a fresh copy from production on to this standby to make 
> things
> in order? If yes, how to go about it. I am quite new to Postgres. Any help/
> suggestions will be greatly appreciated.

Yeah, the master doesn't have/contains the requested WAL-File, you have
to rebuild the replication with a fresh copy from the master. You can
use pg_basebackup, it's well documented.

For the future: increase wal_keep_segments and/or use physical
replications slots, if you are using 9.4 or higher.

Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
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] postgres standby won't start

[Adrian Klaver]

On 10/05/2015 10:53 AM, Ramalingam, Sankarakumar wrote:

We have a standby set up between two sites in two different locations.
The replication was going on well and suddenly it stopped due to error

2015-09-08 16:07:51 EDT LOG:  streaming replication successfully
connected to primary

2015-09-08 16:07:51 EDT FATAL:  could not receive data from WAL stream:
FATAL:  requested WAL segment 000C035E00F0 has already been
removed


Best guess is you have wal_keep_segments set to low:

http://www.postgresql.org/docs/9.4/interactive/runtime-config-replication.html

wal_keep_segments (integer)

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


This sets only the minimum number of segments retained in pg_xlog; 
the system might need to retain more segments for WAL archival or to 
recover from a checkpoint. If wal_keep_segments is zero (the default), 
the system doesn't keep any extra segments for standby purposes, so the 
number of old WAL segments available to standby servers is a function of 
the location of the previous checkpoint and status of WAL archiving. 
This parameter can only be set in the postgresql.conf file or on the 
server command line.




I am unable to start the DB as well.


Which one the primary, the standby or both?



Should I restore a fresh copy from production on to this standby to make
things in order? If yes, how to go about it. I am quite new to Postgres.
Any help/suggestions will be greatly appreciated.


Depends did you have WAL archiving set up, where you could pull the 
missing WAL file(s) from?


If not you will need to rebuild. Take a look at pg_basebackup:

http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html



Thanks

Kumar Ramalingam

Global Database Administration

Elavon, Atlanta , GA

678 731 5288

The information contained in this e-mail and in any attachments is
intended only for the person or entity to which it is addressed and may
contain confidential and/or privileged material. Any review,
retransmission, dissemination or other use of, or taking of any action
in reliance upon, this information by persons or entities other than the
intended recipient is prohibited. This message has been scanned for
known computer viruses.




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


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

Re: [GENERAL] Selecting pairs of numbers

[Adrian Klaver]

On 10/05/2015 11:39 AM, Raymond O'Donnell wrote:

Hello all,

I have an SQL problem which ought to be simple, but I can't get my head
around it.

I have pairs of integers - let's call them (x, y). In effect, x is a
category, while y is an item within that category. For every x, there is
always the same number of integers y; and both x and y are always
numbered sequentially starting from 1.

My problem is that I need to select a list of these pairs, ordered first
on x and then on y, from a given starting point to a given finishing
point and including all pairs in between.

For example, I might have:

x | y
-
1 | 1
1 | 2
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
2 | 4
(etc)

I then might want to extract a list from, say, (1, 3) to (3, 2), giving:

x | y
-
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
2 | 4
3 | 1
3 | 2

For the life of me, I can't figure out how to do this. Any help will be
appreciated, or even just a pointer in the right direction. There's
probably something simple that I'm just not seeing


aklaver@test=> create table pr_test(x int, y int);

aklaver@test=> select * from pr_test  where (x, y) between (1, 3) and 
(3,2) order by x,y;

 x | y
---+---
 1 | 3
 1 | 4
 2 | 1
 2 | 2
 2 | 3
 2 | 4
 3 | 1
 3 | 2



If anyone's interested, these numbers represent channels and pistons on
the combination system of a largish pipe organ... it's for a hobby project.

Many thanks in advance!

Ray.





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


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

Re: [GENERAL] Selecting pairs of numbers

[Raymond O'Donnell]

On 05/10/2015 20:03, John McKown wrote:
> When in doubt, cheat! Why not something like:
> 
> SELECT x, y FROM organ_pipes WHERE point(x,y) <@
> box(point(?x1,?y1),point(?x2,?y2)) ;
> 
> This is definitely a different approach from the others that I've seen.
> 
> 
> Basically, think of your channel / piston as a point in a Cartesian
> plane. And your boundaries as a box in that graph. So you see if the
> "point" is inside the "box"

Ohhh - that's nice! Reminds of one of my lecturers from college days,
who said there was often a geometrical solution to an algebraic problem.

Thanks!

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


-- 
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] Selecting pairs of numbers

[Chris Mair]

I then might want to extract a list from, say, (1, 3) to (3, 2), giving:

x | y
-
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
2 | 4
3 | 1
3 | 2

For the life of me, I can't figure out how to do this.


Hi,

starting from this:


chris=# select * from t order by x,y;
 x | y
---+---
 1 | 1
 1 | 2
 1 | 3
 1 | 4
 2 | 1
 2 | 2
 2 | 3
 2 | 4
 3 | 1
 3 | 2
 3 | 3
 3 | 4
(12 rows)

one trick that might help is this:

chris=# select * from t where x*1000+y >= 1003 and x*1000+y <= 3002 order by 
x,y;
 x | y
---+---
 1 | 3
 1 | 4
 2 | 1
 2 | 2
 2 | 3
 2 | 4
 3 | 1
 3 | 2
(8 rows)

watch out, if you have y values bigger than 1000, though...


Bye,
chris.






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

[GENERAL] Selecting pairs of numbers

[Raymond O'Donnell]

Hello all,

I have an SQL problem which ought to be simple, but I can't get my head
around it.

I have pairs of integers - let's call them (x, y). In effect, x is a
category, while y is an item within that category. For every x, there is
always the same number of integers y; and both x and y are always
numbered sequentially starting from 1.

My problem is that I need to select a list of these pairs, ordered first
on x and then on y, from a given starting point to a given finishing
point and including all pairs in between.

For example, I might have:

x | y
-
1 | 1
1 | 2
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
2 | 4
(etc)

I then might want to extract a list from, say, (1, 3) to (3, 2), giving:

x | y
-
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
2 | 4
3 | 1
3 | 2

For the life of me, I can't figure out how to do this. Any help will be
appreciated, or even just a pointer in the right direction. There's
probably something simple that I'm just not seeing

If anyone's interested, these numbers represent channels and pistons on
the combination system of a largish pipe organ... it's for a hobby project.

Many thanks in advance!

Ray.


-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


-- 
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] Selecting pairs of numbers

[Adrian Klaver]

On 10/05/2015 12:00 PM, Raymond O'Donnell wrote:

On 05/10/2015 19:53, Adrian Klaver wrote:

On 10/05/2015 11:39 AM, Raymond O'Donnell wrote:

Hello all,

I have an SQL problem which ought to be simple, but I can't get my head
around it.

I have pairs of integers - let's call them (x, y). In effect, x is a
category, while y is an item within that category. For every x, there is
always the same number of integers y; and both x and y are always
numbered sequentially starting from 1.

My problem is that I need to select a list of these pairs, ordered first
on x and then on y, from a given starting point to a given finishing
point and including all pairs in between.

For example, I might have:

x | y
-
1 | 1
1 | 2
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
2 | 4
(etc)

I then might want to extract a list from, say, (1, 3) to (3, 2), giving:

x | y
-
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
2 | 4
3 | 1
3 | 2

For the life of me, I can't figure out how to do this. Any help will be
appreciated, or even just a pointer in the right direction. There's
probably something simple that I'm just not seeing


aklaver@test=> create table pr_test(x int, y int);

aklaver@test=> select * from pr_test  where (x, y) between (1, 3) and
(3,2) order by x,y;
  x | y
---+---
  1 | 3
  1 | 4
  2 | 1
  2 | 2
  2 | 3
  2 | 4
  3 | 1
  3 | 2


As simple as that? Thank you! I knew there had to be an easy way.


Yea, surprised me to.



Thanks also to the others who replied with a slightly different
approach, which involved multiplying x by 10.

Ray.




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


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

Re: [GENERAL] md5(large_object_id)

[Karsten Hilbert]

On Mon, Oct 05, 2015 at 03:27:26PM +, Kevin Grittner wrote:

> Karsten Hilbert  wrote:
> 
> > I am dealing with radiology studies aka DICOM data) one would
> > want an md5 function which streams in parts of a large object
> > piece by piece using md5_update and m5_finalize or some such.
> It would certainly be possible to write a lo_md5(oid) function to do
> this, but as far as I'm aware nobody has yet done so.  How are your
> C skills?

Next to none, unfortunately :-/

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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] Selecting pairs of numbers

[John McKown]

When in doubt, cheat! Why not something like:

SELECT x, y FROM organ_pipes WHERE point(x,y) <@
box(point(?x1,?y1),point(?x2,?y2)) ;

This is definitely a different approach from the others that I've seen.


Basically, think of your channel / piston as a point in a Cartesian plane.
And your boundaries as a box in that graph. So you see if the "point" is
inside the "box"

On Mon, Oct 5, 2015 at 1:39 PM, Raymond O'Donnell  wrote:

> Hello all,
>
> I have an SQL problem which ought to be simple, but I can't get my head
> around it.
>
> I have pairs of integers - let's call them (x, y). In effect, x is a
> category, while y is an item within that category. For every x, there is
> always the same number of integers y; and both x and y are always
> numbered sequentially starting from 1.
>
> My problem is that I need to select a list of these pairs, ordered first
> on x and then on y, from a given starting point to a given finishing
> point and including all pairs in between.
>
> For example, I might have:
>
> x | y
> -
> 1 | 1
> 1 | 2
> 1 | 3
> 1 | 4
> 2 | 1
> 2 | 2
> 2 | 3
> 2 | 4
> (etc)
>
> I then might want to extract a list from, say, (1, 3) to (3, 2), giving:
>
> x | y
> -
> 1 | 3
> 1 | 4
> 2 | 1
> 2 | 2
> 2 | 3
> 2 | 4
> 3 | 1
> 3 | 2
>
> For the life of me, I can't figure out how to do this. Any help will be
> appreciated, or even just a pointer in the right direction. There's
> probably something simple that I'm just not seeing
>
> If anyone's interested, these numbers represent channels and pistons on
> the combination system of a largish pipe organ... it's for a hobby project.
>
> Many thanks in advance!
>
> Ray.
>
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> r...@iol.ie
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

Re: [GENERAL] Selecting pairs of numbers

[Raymond O'Donnell]

On 05/10/2015 19:53, Adrian Klaver wrote:
> On 10/05/2015 11:39 AM, Raymond O'Donnell wrote:
>> Hello all,
>>
>> I have an SQL problem which ought to be simple, but I can't get my head
>> around it.
>>
>> I have pairs of integers - let's call them (x, y). In effect, x is a
>> category, while y is an item within that category. For every x, there is
>> always the same number of integers y; and both x and y are always
>> numbered sequentially starting from 1.
>>
>> My problem is that I need to select a list of these pairs, ordered first
>> on x and then on y, from a given starting point to a given finishing
>> point and including all pairs in between.
>>
>> For example, I might have:
>>
>> x | y
>> -
>> 1 | 1
>> 1 | 2
>> 1 | 3
>> 1 | 4
>> 2 | 1
>> 2 | 2
>> 2 | 3
>> 2 | 4
>> (etc)
>>
>> I then might want to extract a list from, say, (1, 3) to (3, 2), giving:
>>
>> x | y
>> -
>> 1 | 3
>> 1 | 4
>> 2 | 1
>> 2 | 2
>> 2 | 3
>> 2 | 4
>> 3 | 1
>> 3 | 2
>>
>> For the life of me, I can't figure out how to do this. Any help will be
>> appreciated, or even just a pointer in the right direction. There's
>> probably something simple that I'm just not seeing
> 
> aklaver@test=> create table pr_test(x int, y int);
> 
> aklaver@test=> select * from pr_test  where (x, y) between (1, 3) and
> (3,2) order by x,y;
>  x | y
> ---+---
>  1 | 3
>  1 | 4
>  2 | 1
>  2 | 2
>  2 | 3
>  2 | 4
>  3 | 1
>  3 | 2

As simple as that? Thank you! I knew there had to be an easy way.

Thanks also to the others who replied with a slightly different
approach, which involved multiplying x by 10.

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


-- 
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] Selecting pairs of numbers

[Igor Neyman]

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Raymond O'Donnell
Sent: Monday, October 05, 2015 2:40 PM
To: 'PostgreSQL' 
Subject: [GENERAL] Selecting pairs of numbers

Hello all,

I have an SQL problem which ought to be simple, but I can't get my head around 
it.

I have pairs of integers - let's call them (x, y). In effect, x is a category, 
while y is an item within that category. For every x, there is always the same 
number of integers y; and both x and y are always numbered sequentially 
starting from 1.

My problem is that I need to select a list of these pairs, ordered first on x 
and then on y, from a given starting point to a given finishing point and 
including all pairs in between.

For example, I might have:

x | y
-
1 | 1
1 | 2
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
2 | 4
(etc)

I then might want to extract a list from, say, (1, 3) to (3, 2), giving:

x | y
-
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
2 | 4
3 | 1
3 | 2

For the life of me, I can't figure out how to do this. Any help will be 
appreciated, or even just a pointer in the right direction. There's probably 
something simple that I'm just not seeing

If anyone's interested, these numbers represent channels and pistons on the 
combination system of a largish pipe organ... it's for a hobby project.

Many thanks in advance!

Ray.




SELECT  x, y FROM my_table 
WHERE (x*10 + y) >= (1*10 + 3) AND (x*10 + y) <= (3*10 + 2)
ORDER BY x, y;

Regards,
Igor Neyman


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

[GENERAL] How to reduce pg_dump backup time

[Sachin Srivastava]

Dear Team,



I am using PostgreSQL 9.1 on Redhat Linux on my Production environment. My
database size is 680 GB and it take 7 hour for completion the pg_dump
backup.



I want that my pg_dump backup should be fast and take less time.



In PostgresQL 9.3 there is “ -j *njobs” *option is available for fast
pg_dump backup.



There is any way, that I will use *“ **-j njobs”** option *in “PostgreSQL
9.1 “ so that my backup is completed in less time or any other way in
”PostgreSQL 9.1” to take the backup fast. Kindly suggest?





*[root]# lsb_release –a*



LSB Version:
:base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch

Distributor ID: RedHatEnterpriseServer

Description:Red Hat Enterprise Linux Server release 6.7 (Santiago)

Release:6.7

Codename:   Santiago



*[root@CPPMOMA_DB01 even]# psql*

Password:

psql.bin (9.1.2)

Type "help" for help.



postgres=# select version();

version

---

 PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-46), 64-bit

(1 row)



postgres=#









*Regards,*
Sachin Srivastava

Re: [GENERAL] BDR Rejoin of failed node, hangs.

[Craig Ringer]

On 5 October 2015 at 20:58, Steve Pribyl  wrote:

> Clean up node 1.
>select bdr.bdr_part_by_node_names('{node2}');
>delete from bdr.bdr_nodes where node_status='k';

You need to delete the bdr.bdr_connections entry too.

0.9.3 will fix that, so orphan connections entries and those
associated with terminated nodes are ignored.

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


-- 
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] Selecting pairs of numbers

[Charles Clavadetscher]

> aklaver@test=> create table pr_test(x int, y int);
> 
> aklaver@test=> select * from pr_test  where (x, y) between (1, 3) and
> (3,2) order by x,y;
>   x | y
> ---+---
>   1 | 3
>   1 | 4
>   2 | 1
>   2 | 2
>   2 | 3
>   2 | 4
>   3 | 1
>   3 | 2

+1, nice.



-- 
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] Selecting pairs of numbers

[Raymond O'Donnell]

On 05/10/2015 20:08, Adrian Klaver wrote:
> On 10/05/2015 12:00 PM, Raymond O'Donnell wrote:
>> On 05/10/2015 19:53, Adrian Klaver wrote:
>>> On 10/05/2015 11:39 AM, Raymond O'Donnell wrote:
 Hello all,

 I have an SQL problem which ought to be simple, but I can't get my head
 around it.

 I have pairs of integers - let's call them (x, y). In effect, x is a
 category, while y is an item within that category. For every x,
 there is
 always the same number of integers y; and both x and y are always
 numbered sequentially starting from 1.

 My problem is that I need to select a list of these pairs, ordered
 first
 on x and then on y, from a given starting point to a given finishing
 point and including all pairs in between.

 For example, I might have:

 x | y
 -
 1 | 1
 1 | 2
 1 | 3
 1 | 4
 2 | 1
 2 | 2
 2 | 3
 2 | 4
 (etc)

 I then might want to extract a list from, say, (1, 3) to (3, 2),
 giving:

 x | y
 -
 1 | 3
 1 | 4
 2 | 1
 2 | 2
 2 | 3
 2 | 4
 3 | 1
 3 | 2

 For the life of me, I can't figure out how to do this. Any help will be
 appreciated, or even just a pointer in the right direction. There's
 probably something simple that I'm just not seeing
>>>
>>> aklaver@test=> create table pr_test(x int, y int);
>>>
>>> aklaver@test=> select * from pr_test  where (x, y) between (1, 3) and
>>> (3,2) order by x,y;
>>>   x | y
>>> ---+---
>>>   1 | 3
>>>   1 | 4
>>>   2 | 1
>>>   2 | 2
>>>   2 | 3
>>>   2 | 4
>>>   3 | 1
>>>   3 | 2
>>
>> As simple as that? Thank you! I knew there had to be an easy way.
> 
> Yea, surprised me to.

Just to complete my understanding of what's going on here, how does
Postgres see the construct (x, y)? Is it some sort of anonymous or
temporary composite type?

Thanks,

Ray.
-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


-- 
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] postgres standby won't start

[Ramalingam, Sankarakumar]

Thanks Adrian.

My primary is doing  fine. Only the standby. I am noticing it after a while..my 
Bad!!
Primary setting
#--
# REPLICATION
#--

max_wal_senders = 5
wal_sender_delay = 1s
wal_keep_segments = 512
vacuum_defer_cleanup_age = 20
hot_standby = on

Since I am unable to bring standby up, would the pg_basebackup help me out here 
for a complete sync? Thanks again.



Thanks
Kumar Ramalingam
X6015288


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Monday, October 05, 2015 2:18 PM
To: Ramalingam, Sankarakumar; pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgres standby won't start

On 10/05/2015 10:53 AM, Ramalingam, Sankarakumar wrote:
> We have a standby set up between two sites in two different locations.
> The replication was going on well and suddenly it stopped due to error
>
> 2015-09-08 16:07:51 EDT LOG:  streaming replication successfully 
> connected to primary
>
> 2015-09-08 16:07:51 EDT FATAL:  could not receive data from WAL stream:
> FATAL:  requested WAL segment 000C035E00F0 has already 
> been removed

Best guess is you have wal_keep_segments set to low:

http://www.postgresql.org/docs/9.4/interactive/runtime-config-replication.html

wal_keep_segments (integer)

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

 This sets only the minimum number of segments retained in pg_xlog; the 
system might need to retain more segments for WAL archival or to recover from a 
checkpoint. If wal_keep_segments is zero (the default), the system doesn't keep 
any extra segments for standby purposes, so the number of old WAL segments 
available to standby servers is a function of the location of the previous 
checkpoint and status of WAL archiving. 
This parameter can only be set in the postgresql.conf file or on the server 
command line.

>
> I am unable to start the DB as well.

Which one the primary, the standby or both?

>
> Should I restore a fresh copy from production on to this standby to 
> make things in order? If yes, how to go about it. I am quite new to Postgres.
> Any help/suggestions will be greatly appreciated.

Depends did you have WAL archiving set up, where you could pull the missing WAL 
file(s) from?

If not you will need to rebuild. Take a look at pg_basebackup:

http://www.postgresql.org/docs/9.4/interactive/app-pgbasebackup.html

>
> Thanks
>
> Kumar Ramalingam
>
> Global Database Administration
>
> Elavon, Atlanta , GA
>
> 678 731 5288
>
> The information contained in this e-mail and in any attachments is 
> intended only for the person or entity to which it is addressed and 
> may contain confidential and/or privileged material. Any review, 
> retransmission, dissemination or other use of, or taking of any action 
> in reliance upon, this information by persons or entities other than 
> the intended recipient is prohibited. This message has been scanned 
> for known computer viruses.
>


--
Adrian Klaver
adrian.kla...@aklaver.com
The information contained in this e-mail and in any attachments is intended 
only for the person or entity to which it is addressed and may contain 
confidential and/or privileged material. Any review, retransmission, 
dissemination or other use of, or taking of any action in reliance upon, this 
information by persons or entities other than the intended recipient is 
prohibited. This message has been scanned for known computer viruses.



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

[GENERAL] Database size on disk

[Quiroga, Damian]

Hi,

I'm looking for some advice on how to determine the DB size on disk to trigger 
some cleanup tasks (deleting old data) if some threshold is reached.

Let's say that for simplicity I don't want to ask for the size on disk to the 
OS, but rather to PostgreSQL itself. And I only have a single database other 
than the defaults ('template', etc).

In that scenario is using pg_database_size() reasonable? Does that function 
return the actual size on disk or is it a calculation? Is there a better 
alternative?

Thanks,

Damian

Re: [GENERAL] Database size on disk

[dinesh kumar]

On Mon, Oct 5, 2015 at 12:47 PM, Quiroga, Damian 
wrote:

> Hi,
>
>
>
> I’m looking for some advice on how to determine the DB size on disk to
> trigger some cleanup tasks (deleting old data) if some threshold is reached.
>
>
>
> Let’s say that for simplicity I don’t want to ask for the size on disk to
> the OS, but rather to PostgreSQL itself. And I only have a single database
> other than the defaults (‘template’, etc).
>
>
>
> In that scenario is using pg_database_size() reasonable? Does that
> function return the actual size on disk or is it a calculation? Is there a
> better alternative?
>
>
>

Yes, pg_database_size() gives the size from disk level. IIRC, it do "du -h
" kind of method.




> Thanks,
>
>
>
> Damian
>



-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [GENERAL] Getting a leading zero on negative intervals with to_char?

[Bruce Momjian]

On Wed, May 13, 2015 at 01:40:41PM -0400, Bruce Momjian wrote:
> > I can't find any way to produce the output '-09:00' . There's no apparent 
> > way
> > to add an additional width-specifier. HH24 is clearly not constrained to be 
> > 2
> > digits wide, since "-11" and "101" and "-101" are all output by "HH24". It
> > seems like "-9" should be "-09" with the HH24 specifier, and "-9" with the
> > "FMHH24" specifier.
> > 
> > Opinions?
> > 
> > Unless I'm doing something woefully wrong, Oracle compatibility doesn't 
> > seem to
> > be an issue because we format intervals wildly differently to Oracle anyway:
> > 
> > http://sqlfiddle.com/#!4/d41d8/2751
> > 
> > and it looks like Oracle handling of intervals isn't much like Pg anyway:
> > 
> > http://stackoverflow.com/questions/970249/format-interval-with-to-char
> > 
> > 
> > Arose from trying to find a non-ugly solution to this SO post:
> > 
> > 
> > http://stackoverflow.com/questions/12335438/server-timezone-offset-value/
> > 12338490#12338490
> 
> [This is for 9.6.]
> 
> I looked over this report from 2012, and the behavior still exists.  I
> think we have not seen more reports about this because negative
> hours/years is not something people regularly use, but you found a need
> for it.
> 
> I think the big question is whether  (4) or HH24 (2) represents
> characters. or digits for zero-padding.  printf() assumes it is
> characters, e.g. %02d outputs "-2" not "-02", but I think our API
> suggests it is digits, meaning the minus sign is not part of the
> specific length, i.e. a minus sign is not a digit.
> 
> I have developed the attached unified-diff patch which changes the
> behavior to not consider the negative sign as a digit in all the places
> I thought it was reasonable.

Applied.

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

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


-- 
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] to_number, to_char inconsistency.

[Bruce Momjian]

On Thu, May 14, 2015 at 01:02:01PM -0400, Bruce Momjian wrote:
> On Sun, Feb 10, 2013 at 06:27:02PM -0500, Tom Lane wrote:
> > Jeremy Lowery  writes:
> > > I load and dump text files with currency values in it. The decimal in 
> > > these
> > > input and output formats in implied. The V format character works great 
> > > for
> > > outputing numeric data:
> > 
> > > # select to_char(123.45, '999V99');
> > >  to_char
> > > -
> > >   12345
> > > (1 row)
> > 
> > > However, when importing data, the V doesn't do the same thing:
> > 
> > > # select to_number('12345', '999V99');
> > 
> > A look at the source code shows that to_number doesn't do anything at
> > all with the V format code, so this isn't terribly surprising.  It
> > wouldn't be very hard to make it do the right thing, probably, but
> > nobody's had that particular itch yet.  Feel free to scratch it and
> > send a patch ...
> 
> (This is for 9.6.)
> 
> I have developed the attached patch to support 'V' with to_number(). 
> Oracle doesn't support that, so we are on our own in defining the API.
> 
> The patch doesn't handle non-whole-number strings very well as there is
> no way for the user to specify decimal precision because we have
> overridden the decimal digit meaning, but that seems fine to me as most
> users will be using whole numbers.

Patch applied.

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

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


-- 
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] Serialization errors despite KEY SHARE/NO KEY UPDATE

[Kevin Grittner]

[Sorry for the delay in responding -- was on vacation.]


Jim Nasby  wrote:
> On 10/2/15 11:44 AM, Olivier Dony wrote:
>> On 10/02/2015 12:28 AM, Jim Nasby wrote:
>>> On 9/29/15 9:47 AM, Olivier Dony wrote:

 -- Setup tables
 CREATE TABLE users ( id serial PRIMARY KEY,
  name varchar,
  date timestamp );
 CREATE TABLE orders ( id serial PRIMARY KEY,
   name varchar,
   user_id int REFERENCES users (id) );
 INSERT INTO users (id, name) VALUES (1, 'foo');
 INSERT INTO orders (id, name) VALUES (1, 'order 1');


 -- Run 2 concurrent transactions: T1 and T2
  T1   T2
 |-|--|
BEGIN ISOLATION LEVEL
   REPEATABLE READ;

   UPDATE orders
SET name = 'order of foo',
user_id = 1
WHERE id = 1;

 BEGIN ISOLATION LEVEL
   REPEATABLE READ;

 UPDATE users
 SET date = now()
 WHERE id = 1;

 COMMIT;

   UPDATE orders
   SET name = 'order of foo (2)',
   user_id = 1
   WHERE id = 1;

 T1 fails with:
 ERROR:  could not serialize access due to concurrent update
 CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."users" x WHERE "id" 
 OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
>>>
>>> This isn't a locking failure, it's a serialization failure. I'm not sure
>>> why it's happening though... is there an index on date?
>>
>> I don't think so. I can reproduce the problem with the queries quoted
>> above, and the only index that seems to be present is the PK

> I'm not sure. Perhaps Kevin Grittner (author of serializable patch) can
> shed some light.

This has nothing to do with the serializable patch[1], but it is
related to the new multixact code in 9.3.  I thought I remembered
that this was a regression from 9.2 behavior which was fixed in a
patch after being reported, and sure enough tests of the tips of
branches showed that prior to 9.3 T2 blocked until T1 committed,
rather than generating a serialization failure, while on 9.5 and
master there was no blocking or error.

Using `git bisect` to see where this was fixed shows:

commit 05315498012530d44cd89a209242a243374e274d
Author: Alvaro Herrera 
Date:   Tue Jul 29 15:41:06 2014 -0400

Avoid uselessly looking up old LOCK_ONLY multixacts

Commit 0ac5ad5134f2 removed an optimization in multixact.c that skipped
fetching members of MultiXactId that were older than our
OldestVisibleMXactId value.  The reason this was removed is that it is
possible for multixacts that contain updates to be older than that
value.  However, if the caller is certain that the multi does not
contain an update (because the infomask bits say so), it can pass this
info down to GetMultiXactIdMembers, enabling it to use the old
optimization.

Pointed out by Andres Freund in 20131121200517.gm7...@alap2.anarazel.de

I don't know whether, after being in 9.5 for over a year it could
be considered for a back-patch to 9.3 and 9.4; I leave that to
Álvaro and others who have been repairing the multixact problems to
determine.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[1]  The serializable patch has no effect if there are not
serializable transactions in use, and it doesn't generate errors
"due to concurrent update" in any new situations.


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

[GENERAL] Try to understand VACUUM and its settings

[Michael Chau]

Hi,

Last Friday, I ran :

postgres=# select max(age(datfrozenxid)) from pg_database;

max
42579490

and then I ran :

SELECT relname, age(relfrozenxid) as xid_age,
pg_size_pretty(pg_table_size(oid)) as table_size
FROM pg_class
WHERE relkind = 'r' and pg_table_size(oid) > 1073741824
ORDER BY age(relfrozenxid) DESC LIMIT 20;


"table_1";42574012;"10111 MB"
"table_2";42567398;"5961 MB"
"table_3";20320509;"12 GB"

Today, the max is

43068744

and

"table_1";43063214;"10134 MB"
"table_2";43056600;"5984 MB"
"table_3";20809711;"12 GB"



It looks like the  age(relfrozenxid) of the tables go up in tandem with the
max. autovacuum_freeze_max_age and vacuum_freeze_table_age are commented
out in postgresql.conf

#autovacuum_freeze_max_age = 2
#vacuum_freeze_table_age = 15000

So, do I need to run vacuum freeze on those tables? Also, if
autovacuum_freeze_max_age is commented, does it still mean that the default
is 200M?

Thanks,
Michael

Re: [GENERAL] Selecting pairs of numbers

[Adrian Klaver]

On 10/05/2015 12:33 PM, Raymond O'Donnell wrote:

On 05/10/2015 20:08, Adrian Klaver wrote:

On 10/05/2015 12:00 PM, Raymond O'Donnell wrote:

On 05/10/2015 19:53, Adrian Klaver wrote:

On 10/05/2015 11:39 AM, Raymond O'Donnell wrote:

Hello all,

I have an SQL problem which ought to be simple, but I can't get my head
around it.

I have pairs of integers - let's call them (x, y). In effect, x is a
category, while y is an item within that category. For every x,
there is
always the same number of integers y; and both x and y are always
numbered sequentially starting from 1.

My problem is that I need to select a list of these pairs, ordered
first
on x and then on y, from a given starting point to a given finishing
point and including all pairs in between.

For example, I might have:

x | y
-
1 | 1
1 | 2
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
2 | 4
(etc)

I then might want to extract a list from, say, (1, 3) to (3, 2),
giving:

x | y
-
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
2 | 4
3 | 1
3 | 2

For the life of me, I can't figure out how to do this. Any help will be
appreciated, or even just a pointer in the right direction. There's
probably something simple that I'm just not seeing


aklaver@test=> create table pr_test(x int, y int);

aklaver@test=> select * from pr_test  where (x, y) between (1, 3) and
(3,2) order by x,y;
   x | y
---+---
   1 | 3
   1 | 4
   2 | 1
   2 | 2
   2 | 3
   2 | 4
   3 | 1
   3 | 2


As simple as that? Thank you! I knew there had to be an easy way.


Yea, surprised me to.


Just to complete my understanding of what's going on here, how does
Postgres see the construct (x, y)? Is it some sort of anonymous or
temporary composite type?


This is getting a bit out of my depth, but I figured the behavior is 
explained here:


http://www.postgresql.org/docs/9.4/static/sql-expressions.html

4.2.13. Row Constructors

I took the shortcut described here:

"The key word ROW is optional when there is more than one expression in 
the list."


The more complete form of the query above is:

test=> select * from pr_test  where ROW(x, y) between (1, 3) and
(3,2) order by x,y;;
 x | y
---+---
 1 | 3
 1 | 4
 2 | 1
 2 | 2
 2 | 3
 2 | 4
 3 | 1
 3 | 2
(8 rows)


The comparison procedure is described here:

http://www.postgresql.org/docs/9.4/static/functions-comparisons.html

9.23.5. Row Constructor Comparison



Thanks,

Ray.




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


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

Re: [GENERAL] Replication with 9.4

[Thomas Munro]

On Sun, Oct 4, 2015 at 11:47 PM, Michael Paquier
 wrote:
> (Seems like you forgot to push the Reply-all button)
>
> On Sun, Oct 4, 2015 at 7:01 PM, Madovsky wrote:
>> On 10/3/2015 3:30 PM, Michael Paquier wrote:
>>>  and no reason is given to justify *why* this would be needed in your case
>> reason for a choice can be often an issue for other :D
>>
>> I thought that postgresql 9.4  user could change on the fly with
>> synchronous_commit from local to on for ex
>> which hotstandby would become in sync and which in async to avoid a big
>> latency in case of let's say 100 hot standby.
>> it was an idea, a concept to let the master write and update the nodes, like
>> a queen bee ;)
>> but I'm afraid it's not possible, so maybe future version of pg will do it,
>> for now  read from the master is my only solution.
>
> Well, Thomas Munro (adding him in CC) has sent for integration with
> 9.6 a patch that would cover your need, by adding to
> synchronous_commit a mode called 'apply', in which case a master would
> wait for the transaction to be applied on standby before committing
> locally:
> http://www.postgresql.org/message-id/CAEepm=1fqkivl4v-otphwsgw4af9hcogimrcw-ybtjipx9g...@mail.gmail.com
> Perhaps you could help with the review of the patch, this has stalled
> a bit lately.

That patch (or something more sophisticated long those lines) is a
small piece of a bigger puzzle, though it might be enough if you only
have one standby, are prepared to block until manual intervention if
that standby fails, and don't mind potentially lumpy apply
performance.  See also the work being done to separate wal writing
from wal applying for smoother performance[1], and handle multiple
synchronous standbys[2].  But there is another piece of the puzzle
IMHO: how to know reliably that the standby that you are talking to
guarantees causal consistency, while also allowing standbys to
fail/drop out gracefully, and I'm currently working on an idea for
that.

Of course you can make your own causal consistency today if you are
prepared to have your clients explicitly wait for WAL to be applied.
You can call pg_current_xlog_location() on the master after
committing, and then wait until pg_last_xlog_replay_location() reports
that that LSN has been applied on any standby you talk to at the start
of any transaction that wants causal reads.  You could wrap the
waiting up in a user defined function
wait_for_xlog_replay_location(lsn, timeout) which could do a naive
poll/sleep loop (or do something more efficient with latches in core
code).  For example, imagine a client that inserts some new accounts
and then causes a large number of workers to regenerate some reports
that must include the new accounts against a pool of standbys.  It
just needs to give them the LSN they should wait for first.  And if
you don't want to pass LSNs around but don't mind introducing some
extra conservative lag, those workers could call
pg_current_xlog_location() on the master themselves to get some
arbitrary recent LSN and then wait for that to be applied before they
start their work on the standbys.

The explicit wait-for-LSN approach pushes the waiting over to readers
who want causal reads, instead of writer (the master), which still
might be interesting for some cases even if we do finish up with a
good optional master-waits system.  But a master-waits system will
allow naive clients to see up to date data no matter where they run
their queries (or be kicked off by standbys that can't guarantee that)
without having to think about LSNs and replication machinery, and I
think that would be a very useful feature.

[1] 
http://www.postgresql.org/message-id/flat/CA+U5nMJifauXvVbx=v3UbYbHO3Jw2rdT4haL6CCooEDM5=4...@mail.gmail.com
[2] 
http://www.postgresql.org/message-id/flat/caog9aphycpmtypaawfd3_v7svokbnecfivmrc1axhb40zbs...@mail.gmail.com

-- 
Thomas Munro
http://www.enterprisedb.com


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

Re: [GENERAL] postgres standby won't start

[Adrian Klaver]

On 10/05/2015 12:35 PM, Ramalingam, Sankarakumar wrote:

Thanks Adrian.

My primary is doing  fine. Only the standby. I am noticing it after a while..my 
Bad!!
Primary setting
#--
# REPLICATION
#--

max_wal_senders = 5
wal_sender_delay = 1s
wal_keep_segments = 512
vacuum_defer_cleanup_age = 20
hot_standby = on

Since I am unable to bring standby up, would the pg_basebackup help me out here 
for a complete sync? Thanks again.


The problem is that the WAL files got recycled on the primary and are no 
longer available there, which is why the standby is failing. If you 
where archiving the WAL files somewhere, you could fetch the missing WAL 
files from there. If that is not the case then you will need to rebuild 
the standby to the current state(more or less) of the primary. The 
simplest way is to use pg_backup, but that means starting with either no 
data directory or an empty one on the standby. The docs page steps you 
through the procedure.






Thanks
Kumar Ramalingam
X6015288






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


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

Re: [GENERAL] Unexpected query result

[Adrian Klaver]

On 10/05/2015 05:02 AM, Begin Daniel wrote:

In order to process a large amount of data I need to run a procedure
using parallel batch processes.
The query I run in each process is expected to ...

1- select a bunch of id (500) in a table (wait4processing) containing
the list of all records to process
2- remove selected records from wait4processing table in order to
eliminate duplicate processing
3- run the procedure (build_contributions_clusters) over the range of
selected ids

--The query I use:
With ids as( delete from wait4processing where id in( select id from
wait4processing limit 500)  returning id)
select build_contributions_clusters(min(id),max(id)) from ids;

The query runs properly if I send it sequentially (wait for the
completion of the query before sening it again) but it does'nt work
when sending multiple occurrences in parallel. Seems from the results I
got that the first query received by the server runs properly but the
following ones try to process the same first 500 records even if deleted
- the build_contributions_clusters procedure receive NULL values instead
of the following 500 records.

Since I am almost certain it is the expected behavior, I would like to
like to understand why, and I would also appreciate to
see alternative queries to do the job.


See here:

http://www.postgresql.org/docs/9.4/interactive/transaction-iso.html

"13.2.1. Read Committed Isolation Level

Read Committed is the default isolation level in PostgreSQL. When a 
transaction uses this isolation level, a SELECT query (without a FOR 
UPDATE/SHARE clause) sees only data committed before the query began; it 
never sees either uncommitted data or changes committed during query 
execution by concurrent transactions. In effect, a SELECT query sees a 
snapshot of the database as of the instant the query begins to run. 
However, SELECT does see the effects of previous updates executed within 
its own transaction, even though they are not yet committed. Also note 
that two successive SELECT commands can see different data, even though 
they are within a single transaction, if other transactions commit 
changes after the first SELECT starts and before the second SELECT starts."




Thanks :-)



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


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

Re: [GENERAL] Best way to sync table DML between databases

[Bill Moran]

On Mon, 5 Oct 2015 06:20:28 -0700 (MST)
jimbosworth  wrote:

> Hi All,
> 
> I have two servers each running pg9.4.4 database instances.  
> I need to determine the best way to keep a large 20gb table on server A
> synchronised onto server B...
> 
> At the moment, I use pg_dump to periodically dump the table on server A,
> then psql to reload into server B.  This is fine, but means I have to pull
> 100% of the table each time rather than just the changes.  This option does
> not offer real time accuracy on server B.
> 
> I have considered using a table trigger on row (update, insert or delete)
> and then using db_link or postgres_fdw to sync the changes, but am concerned
> that a table trigger is synchronous... so a db_link or fdw could incur a
> lengthy delay.
> 
> I have also considered using table OIDs to track changes, then just
> periodically sync the difference.
> 
> I have considered using postgre_fdw and then 'refresh concurrently
> materialized view' on server B.
> 
> I have considered using logical decoding to read the wal files, then extract
> the changes.
> 
> Can anyone explain the best way to synchronise JUST the changes on a table
> between servers please?

Sounds like a problem custom-made to be solved by Slony:
http://slony.info/

-- 
Bill Moran


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

[GENERAL] Unexpected query result

[Begin Daniel]

In order to process a large amount of data I need to run a procedure using 
parallel batch processes.  The query I run in each process is expected to ...
1- select a bunch of id (500) in a table (wait4processing) containing the list 
of all records to process2- remove selected records from wait4processing table 
in order to eliminate duplicate processing3- run the procedure 
(build_contributions_clusters) over the range of selected ids 
--The query I use:With ids as( delete from wait4processing where id in( select 
id from wait4processing limit 500)  returning id) select 
build_contributions_clusters(min(id),max(id)) from ids;
The query runs properly if I send it sequentially (wait for the completion of 
the query before sening it again) but it does'nt work when sending multiple 
occurrences in parallel.  Seems from the results I got that the first query 
received by the server runs properly but the following ones try to process the 
same first 500 records even if deleted - the build_contributions_clusters 
procedure receive NULL values instead of the following 500 records.
Since I am almost certain it is the expected behavior, I would like to like to 
understand why, and I would also appreciate to see alternative queries to do 
the job.
Thanks :-)

[GENERAL] Best way to sync table DML between databases

[jimbosworth]

Hi All,

I have two servers each running pg9.4.4 database instances.  
I need to determine the best way to keep a large 20gb table on server A
synchronised onto server B...

At the moment, I use pg_dump to periodically dump the table on server A,
then psql to reload into server B.  This is fine, but means I have to pull
100% of the table each time rather than just the changes.  This option does
not offer real time accuracy on server B.

I have considered using a table trigger on row (update, insert or delete)
and then using db_link or postgres_fdw to sync the changes, but am concerned
that a table trigger is synchronous... so a db_link or fdw could incur a
lengthy delay.

I have also considered using table OIDs to track changes, then just
periodically sync the difference.

I have considered using postgre_fdw and then 'refresh concurrently
materialized view' on server B.

I have considered using logical decoding to read the wal files, then extract
the changes.

Can anyone explain the best way to synchronise JUST the changes on a table
between servers please?

thanks
Jim




--
View this message in context: 
http://postgresql.nabble.com/Best-way-to-sync-table-DML-between-databases-tp5868715.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] Best way to sync table DML between databases

[jimbosworth]

Thanks Bill, but is there no way to just track row changes on a postgres
table without using 3rd party replication solutions or a patched version of
postgres?

Im not in a position to change the database setup on server A.  Server B is
mine to maintain, hence why postgres_fdw and some means of tracking just the
DML would work ok.



--
View this message in context: 
http://postgresql.nabble.com/Best-way-to-sync-table-DML-between-databases-tp5868715p5868720.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] Unexpected query result

[Begin Daniel]

Thank Adrian, it makes sense. I'll adapt the calling procedures Daniel

> Subject: Re: [GENERAL] Unexpected query result
> To: jfd...@hotmail.com; pgsql-general@postgresql.org
> From: adrian.kla...@aklaver.com
> Date: Mon, 5 Oct 2015 06:17:33 -0700
> 
> On 10/05/2015 05:02 AM, Begin Daniel wrote:
> > In order to process a large amount of data I need to run a procedure
> > using parallel batch processes.
> > The query I run in each process is expected to ...
> >
> > 1- select a bunch of id (500) in a table (wait4processing) containing
> > the list of all records to process
> > 2- remove selected records from wait4processing table in order to
> > eliminate duplicate processing
> > 3- run the procedure (build_contributions_clusters) over the range of
> > selected ids
> >
> > --The query I use:
> > With ids as( delete from wait4processing where id in( select id from
> > wait4processing limit 500)  returning id)
> > select build_contributions_clusters(min(id),max(id)) from ids;
> >
> > The query runs properly if I send it sequentially (wait for the
> > completion of the query before sening it again) but it does'nt work
> > when sending multiple occurrences in parallel. Seems from the results I
> > got that the first query received by the server runs properly but the
> > following ones try to process the same first 500 records even if deleted
> > - the build_contributions_clusters procedure receive NULL values instead
> > of the following 500 records.
> >
> > Since I am almost certain it is the expected behavior, I would like to
> > like to understand why, and I would also appreciate to
> > see alternative queries to do the job.
> 
> See here:
> 
> http://www.postgresql.org/docs/9.4/interactive/transaction-iso.html
> 
> "13.2.1. Read Committed Isolation Level
> 
> Read Committed is the default isolation level in PostgreSQL. When a 
> transaction uses this isolation level, a SELECT query (without a FOR 
> UPDATE/SHARE clause) sees only data committed before the query began; it 
> never sees either uncommitted data or changes committed during query 
> execution by concurrent transactions. In effect, a SELECT query sees a 
> snapshot of the database as of the instant the query begins to run. 
> However, SELECT does see the effects of previous updates executed within 
> its own transaction, even though they are not yet committed. Also note 
> that two successive SELECT commands can see different data, even though 
> they are within a single transaction, if other transactions commit 
> changes after the first SELECT starts and before the second SELECT starts."
> 
> >
> > Thanks :-)
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
  

Re: [GENERAL] Unexpected query result

[dinesh kumar]

Hi,

On Mon, Oct 5, 2015 at 5:02 AM, Begin Daniel  wrote:

> In order to process a large amount of data I need to run a procedure using
> parallel batch processes.
> The query I run in each process is expected to ...
>
>
It seems, you are trying to achieve the same, what we did. Find my blog
entry
,
which may help you in this scenarios.

1- select a bunch of id (500) in a table (wait4processing
>
) containing the list of all records to process
> 2- remove selected records from wait4processing table in order to
> eliminate duplicate processing
> 3- run the procedure (build_contributions_clusters) over the range of
> selected ids
>
> --The query I use:
> With ids as( delete from wait4processing where id in( select id from
> wait4processing limit 500)  returning id)
> select build_contributions_clusters(min(id),max(id)) from ids;
>
> The query runs properly if I send it sequentially (wait for the completion
> of the query before sening it again) but it does'nt work when sending
> multiple occurrences in parallel.  Seems from the results I got that the
> first query received by the server runs properly but the following ones try
> to process the same first 500 records even if deleted - the
> build_contributions_clusters procedure receive NULL values instead of the
> following 500 records.
>
> Since I am almost certain it is the expected behavior, I would like to like
> to understand why, and I would also appreciate to see alternative queries
> to do the job.
>
> Thanks :-)
>



-- 

Regards,
Dinesh
manojadinesh.blogspot.com

Re: [GENERAL] md5(large_object_id)

[Kevin Grittner]

Karsten Hilbert  wrote:

> I am dealing with radiology studies aka DICOM data) one would
> want an md5 function which streams in parts of a large object
> piece by piece using md5_update and m5_finalize or some such.
It would certainly be possible to write a lo_md5(oid) function to do
this, but as far as I'm aware nobody has yet done so.  How are your
C skills?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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 Rejoin of failed node, hangs.

[Steve Pribyl]

Good Morning,

Has anyone had a moment to look at this?
It is a bit of a show stopper.

Thanks Steve


From: pgsql-general-ow...@postgresql.org  
on behalf of Steve Pribyl 
Sent: Thursday, October 1, 2015 12:27 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] BDR Rejoin of failed node, hangs.

Good Afternoon,

I am in the process of testing out BDR and am having problems rejoining after a 
simulated loss of one node.   The join hangs while waiting to complete and 
there interesting errors in the logs.


Simulation process
After creating the database on both nodes.

Shutdown postgres and reset db on node 2
   rm -rf /var/lib/postgresql/9.4/main/*
   /usr/lib/postgresql/9.4/bin/initdb -D /var/lib/postgresql/9.4/main -A trust 
-U postgre

Clean up node 1.
   select bdr.bdr_part_by_node_names('{node2}');
   delete from bdr.bdr_nodes where node_status='k';

When I try to re-add node2
Using the NODE 2 Create sql again, it hangs
SELECT bdr.bdr_node_join_wait_for_ready(); hangs.

The log on node2  has errors
http://pastebin.com/8ZsTe5cG
55000: System identification mismatch between connection and slot
0: worker process: bdr db: bdrdemo (PID 12042) exited with exit code 1

The log on node1 does not have any errors.
http://pastebin.com/njVJ9WX7

Both nodes show up in select * from bdr.bdr_nodes; on node1 and node2;

Database Creation:
NODE 1
create database bdrdemo;
\connect bdrdemo
CREATE EXTENSION btree_gist;
CREATE EXTENSION bdr;
select bdr.bdr_group_create(local_node_name := 'node1', node_external_dsn := 
'host=192.168.101.41 port=5432 dbname=bdrdemo');
SELECT bdr.bdr_node_join_wait_for_ready();

NODE 2
create database bdrdemo;
\connect bdrdemo
CREATE EXTENSION btree_gist;
CREATE EXTENSION bdr;
select bdr.bdr_group_join(local_node_name := 'node2', node_external_dsn := 
'host=192.168.101.42 port=5432 dbname=bdrdemo', join_using_dsn := 
'host=192.168.101.41 port=5432 dbname=bdrdemo');
SELECT bdr.bdr_node_join_wait_for_ready();


Ubuntu 14.04 packages
ii  postgresql-bdr-9.4  9.4.4-1trusty amd64 
   object-relational SQL database, version 9.4 server
ii  postgresql-bdr-9.4-bdr-plugin   0.9.2-1trusty amd64 
   BDR Plugin for PostgreSQL-BDR 9.4
ii  postgresql-bdr-client-9.4   9.4.4-1trusty amd64 
   front-end programs for PostgreSQL-BDR 9.4
ii  postgresql-bdr-contrib-9.4  9.4.4-1trusty amd64 
   additional facilities for PostgreSQL
ii  postgresql-client-common154   all   
   manager for multiple PostgreSQL client versions
ii  postgresql-common   154   all   
   PostgreSQL database-cluster manager

TIA,
Steve

 [http://www.akunacapital.com/images/akuna.png]
Steve Pribyl | Senior Systems Engineer
Akuna Capital LLC
36 S Wabash, Suite 310 Chicago IL 60603 USA | www.akunacapital.com 

p: +1 312 994 4646 | m:  | f: +1 312 750 1667 | spri...@akunacapital.com

Please consider the environment, before printing this email.

This electronic message contains information from Akuna Capital LLC that may be 
confidential, legally privileged or otherwise protected from disclosure. This 
information is intended for the use of the addressee only and is not offered as 
investment advice to be relied upon for personal or professional use. 
Additionally, all electronic messages are recorded and stored in compliance 
pursuant to applicable SEC rules. If you are not the intended recipient, you 
are hereby notified that any disclosure, copying, distribution, printing or any 
other use of, or any action in reliance on, the contents of this electronic 
message is strictly prohibited. If you have received this communication in 
error, please notify us by telephone at (312)994-4640 and destroy the original 
message.


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

 [http://www.akunacapital.com/images/akuna.png]
Steve Pribyl | Senior Systems Engineer
Akuna Capital LLC
36 S Wabash, Suite 310 Chicago IL 60603 USA | www.akunacapital.com 

p: +1 312 994 4646 | m:  | f: +1 312 750 1667 | spri...@akunacapital.com

Please consider the environment, before printing this email.

This electronic message contains information from Akuna Capital LLC that may be 
confidential, legally privileged or otherwise protected from disclosure. This 
information is intended for the use of the addressee only and is not offered as 
investment advice to be relied upon for personal or professional use. 
Additionally, all electronic messages are recorded and stored in compliance 
pursuant to applicable SEC rules. If you are not the intended