Re: Dealing with latency to replication slave; what to do?

[Jeff Janes]

Please don't top-post, it is not the custom on this list.

On Tue, Jul 24, 2018 at 4:08 PM, Rory Falloon  wrote:

> On Tue, Jul 24, 2018 at 4:02 PM Andres Freund  wrote:
>
Hi,
>>
>> On 2018-07-24 15:39:32 -0400, Rory Falloon wrote:
>> > Looking for any tips here on how to best maintain a replication slave
>> which
>> > is operating under some latency between networks - around 230ms. On a
>> good
>> > day/week, replication will keep up for a number of days, but however,
>> when
>> > the link is under higher than average usage, keeping replication active
>> can
>> > last merely minutes before falling behind again.
>> >
>> > 2018-07-24 18:46:14 GMTLOG:  database system is ready to accept read
>> only
>> > connections
>> > 2018-07-24 18:46:15 GMTLOG:  started streaming WAL from primary at
>> > 2B/9300 on timeline 1
>> > 2018-07-24 18:59:28 GMTLOG:  incomplete startup packet
>> > 2018-07-24 19:15:36 GMTLOG:  incomplete startup packet
>> > 2018-07-24 19:15:36 GMTLOG:  incomplete startup packet
>> > 2018-07-24 19:15:37 GMTLOG:  incomplete startup packet
>> >
>> > As you can see above, it lasted about half an hour before falling out of
>> > sync.
>>
>> How can we see that from the above? The "incomplete startup messages"
>> are independent of streaming rep? I think you need to show us more logs.
>>
>>
>>
> regarding your first reply, I was inferring that from the fact I saw those
> messages at the same time the replication stream fell behind. What other
> logs would be more pertinent to this situation?
>

This is circular.  You think it lost sync because you saw some message you
didn't recognize, and then you think the error message was related to it
losing sync because they occured at the same time.  What evidence do you
have that it has lost sync at all? From the log file you posted, it seems
the server is running fine and is just getting probed by a port scanner, or
perhaps by a monitoring tool.

If it had lost sync, you would be getting log messages about "requested WAL
segment has already been removed"

Cheers,

Jeff

On Tue, Jul 24, 2018 at 4:08 PM, Rory Falloon  wrote:

> Hi Andres,
>
> regarding your first reply, I was inferring that from the fact I saw those
> messages at the same time the replication stream fell behind. What other
> logs would be more pertinent to this situation?
>
>
>
> On Tue, Jul 24, 2018 at 4:02 PM Andres Freund  wrote:
>
>> Hi,
>>
>> On 2018-07-24 15:39:32 -0400, Rory Falloon wrote:
>> > Looking for any tips here on how to best maintain a replication slave
>> which
>> > is operating under some latency between networks - around 230ms. On a
>> good
>> > day/week, replication will keep up for a number of days, but however,
>> when
>> > the link is under higher than average usage, keeping replication active
>> can
>> > last merely minutes before falling behind again.
>> >
>> > 2018-07-24 18:46:14 GMTLOG:  database system is ready to accept read
>> only
>> > connections
>> > 2018-07-24 18:46:15 GMTLOG:  started streaming WAL from primary at
>> > 2B/9300 on timeline 1
>> > 2018-07-24 18:59:28 GMTLOG:  incomplete startup packet
>> > 2018-07-24 19:15:36 GMTLOG:  incomplete startup packet
>> > 2018-07-24 19:15:36 GMTLOG:  incomplete startup packet
>> > 2018-07-24 19:15:37 GMTLOG:  incomplete startup packet
>> >
>> > As you can see above, it lasted about half an hour before falling out of
>> > sync.
>>
>> How can we see that from the above? The "incomplete startup messages"
>> are independent of streaming rep? I think you need to show us more logs.
>>
>>
>> > On the master, I have wal_keep_segments=128. What is happening when I
>> see
>> > "incomplete startup packet" - is it simply the slave has fallen behind,
>> > and  cannot 'catch up' using the wal segments quick enough? I assume the
>> > slave is using the wal segments to replay changes and assuming there are
>> > enough wal segments to cover the period it cannot stream properly, it
>> will
>> > eventually recover?
>>
>> You might want to look into replication slots to ensure the primary
>> keeps the necessary segments, but not more, around.  You might also want
>> to look at wal_compression, to reduce the bandwidth usage.
>>
>> Greetings,
>>
>> Andres Freund
>>
>

Re: width_bucket issue

[Christophe Pettus]

> On Jul 24, 2018, at 13:02, Raphaël Berbain  wrote:
> I'd expect b1 = b2 = 2. What am I missing?

The problem appears to be due to rounding during the intermediate calculations. 
 In compute_bucket() in numeric.c:

div_var(_var, _var, result_var,
select_div_scale(_var, _var), 
true);

... produces 0. for that particular value, instead of 1, 
and the subsequent +1 and FLOOR() result in 1 instead of 2.
--
-- Christophe Pettus
   x...@thebuild.com

Re: Dealing with latency to replication slave; what to do?

[Rory Falloon]

Hi Andres,

regarding your first reply, I was inferring that from the fact I saw those
messages at the same time the replication stream fell behind. What other
logs would be more pertinent to this situation?



On Tue, Jul 24, 2018 at 4:02 PM Andres Freund  wrote:

> Hi,
>
> On 2018-07-24 15:39:32 -0400, Rory Falloon wrote:
> > Looking for any tips here on how to best maintain a replication slave
> which
> > is operating under some latency between networks - around 230ms. On a
> good
> > day/week, replication will keep up for a number of days, but however,
> when
> > the link is under higher than average usage, keeping replication active
> can
> > last merely minutes before falling behind again.
> >
> > 2018-07-24 18:46:14 GMTLOG:  database system is ready to accept read only
> > connections
> > 2018-07-24 18:46:15 GMTLOG:  started streaming WAL from primary at
> > 2B/9300 on timeline 1
> > 2018-07-24 18:59:28 GMTLOG:  incomplete startup packet
> > 2018-07-24 19:15:36 GMTLOG:  incomplete startup packet
> > 2018-07-24 19:15:36 GMTLOG:  incomplete startup packet
> > 2018-07-24 19:15:37 GMTLOG:  incomplete startup packet
> >
> > As you can see above, it lasted about half an hour before falling out of
> > sync.
>
> How can we see that from the above? The "incomplete startup messages"
> are independent of streaming rep? I think you need to show us more logs.
>
>
> > On the master, I have wal_keep_segments=128. What is happening when I see
> > "incomplete startup packet" - is it simply the slave has fallen behind,
> > and  cannot 'catch up' using the wal segments quick enough? I assume the
> > slave is using the wal segments to replay changes and assuming there are
> > enough wal segments to cover the period it cannot stream properly, it
> will
> > eventually recover?
>
> You might want to look into replication slots to ensure the primary
> keeps the necessary segments, but not more, around.  You might also want
> to look at wal_compression, to reduce the bandwidth usage.
>
> Greetings,
>
> Andres Freund
>

width_bucket issue

[Raphaël Berbain]

Hi,

The width_bucket function doesn't seem to work the way I'd expect:
postgres=# SELECT width_bucket(4, 0, 12, 3) b1, width_bucket(4 :: NUMERIC,
0, 12, 3) b2;
 b1 | b2
+
  2 |  1
(1 row)

I'd expect b1 = b2 = 2. What am I missing?

This is with 10.4 running as a docker container, if it matters:
postgres=# SELECT version();
 version

---
 PostgreSQL 10.4 (Debian 10.4-2.pgdg90+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debi
(1 row)

TIA,

Re: Dealing with latency to replication slave; what to do?

[Andres Freund]

Hi,

On 2018-07-24 15:39:32 -0400, Rory Falloon wrote:
> Looking for any tips here on how to best maintain a replication slave which
> is operating under some latency between networks - around 230ms. On a good
> day/week, replication will keep up for a number of days, but however, when
> the link is under higher than average usage, keeping replication active can
> last merely minutes before falling behind again.
> 
> 2018-07-24 18:46:14 GMTLOG:  database system is ready to accept read only
> connections
> 2018-07-24 18:46:15 GMTLOG:  started streaming WAL from primary at
> 2B/9300 on timeline 1
> 2018-07-24 18:59:28 GMTLOG:  incomplete startup packet
> 2018-07-24 19:15:36 GMTLOG:  incomplete startup packet
> 2018-07-24 19:15:36 GMTLOG:  incomplete startup packet
> 2018-07-24 19:15:37 GMTLOG:  incomplete startup packet
> 
> As you can see above, it lasted about half an hour before falling out of
> sync.

How can we see that from the above? The "incomplete startup messages"
are independent of streaming rep? I think you need to show us more logs.


> On the master, I have wal_keep_segments=128. What is happening when I see
> "incomplete startup packet" - is it simply the slave has fallen behind,
> and  cannot 'catch up' using the wal segments quick enough? I assume the
> slave is using the wal segments to replay changes and assuming there are
> enough wal segments to cover the period it cannot stream properly, it will
> eventually recover?

You might want to look into replication slots to ensure the primary
keeps the necessary segments, but not more, around.  You might also want
to look at wal_compression, to reduce the bandwidth usage.

Greetings,

Andres Freund

RES: How can i install contrib modules in pg11 via source

[Márcio Antônio Sepp]

root@pg11:/pg11/postgresql-11beta2/contrib/hstore # make
make: "/pg11/postgresql-11beta2/contrib/hstore/Makefile" line 16: Need an
operator
make: "/pg11/postgresql-11beta2/contrib/hstore/Makefile" line 19: Could not
find
make: "/pg11/postgresql-11beta2/contrib/hstore/Makefile" line 20: Need an
operator

 

Márcio, remember that it is FreeBSD, not Linux.

Use gmake (GNU make) instead of make (BSD make). They are not compatible...

 

Thank you so much. Now it’s ok.

 

Commands:  gmake;  gmake install; create extension hstore;

 

Thanks so much.

Re: How can i install contrib modules in pg11 via source

[Olivier Gautherot]

On Tue, Jul 24, 2018 at 3:41 PM, Márcio Antônio Sepp <
mar...@zyontecnologia.com.br> wrote:

> >
> > If so I can tell you how I do it in Linux and you can make the
> > appropriate translations to BSD.
> >
> > 1) cd to contrib/hstore/
> >
> > 2) make
> >
> > 3) sudo make install
> >
> > 4) In psql CREATE EXTENSION hstore;
>
> Well, I allways install via pkg and I didn't find the contrib folder  (:
>
> Now, the follow error message occur:
>
> root@pg11:/pg11/postgresql-11beta2/contrib/hstore # make
> make: "/pg11/postgresql-11beta2/contrib/hstore/Makefile" line 16: Need an
> operator
> make: "/pg11/postgresql-11beta2/contrib/hstore/Makefile" line 19: Could
> not
> find
> make: "/pg11/postgresql-11beta2/contrib/hstore/Makefile" line 20: Need an
> operator
>
>
Márcio, remember that it is FreeBSD, not Linux.
Use gmake (GNU make) instead of make (BSD make). They are not compatible...

Re: How can i install contrib modules in pg11 via source

[Joshua D. Drake]

On 07/24/2018 12:08 PM, Adrian Klaver wrote:

On 07/24/2018 12:00 PM, Márcio Antônio Sepp wrote:

Hi all,

How can i install contrib modules in pg11. I’m using FreeBSD 11.2.

In specific i need to install hstore for test purpose.


To confirm you are building from source correct?

If so I can tell you how I do it in Linux and you can make the 
appropriate translations to BSD.




0) ./configure;



1) cd to contrib/hstore/

2) make

3) sudo make install

4) In psql CREATE EXTENSION hstore;



Thanks in advance.

--

Att

Márcio A. Sepp






--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

RES: How can i install contrib modules in pg11 via source

[Márcio Antônio Sepp]

> 
> If so I can tell you how I do it in Linux and you can make the
> appropriate translations to BSD.
> 
> 1) cd to contrib/hstore/
> 
> 2) make
> 
> 3) sudo make install
> 
> 4) In psql CREATE EXTENSION hstore;

Well, I allways install via pkg and I didn't find the contrib folder  (: 

Now, the follow error message occur:

root@pg11:/pg11/postgresql-11beta2/contrib/hstore # make
make: "/pg11/postgresql-11beta2/contrib/hstore/Makefile" line 16: Need an
operator
make: "/pg11/postgresql-11beta2/contrib/hstore/Makefile" line 19: Could not
find
make: "/pg11/postgresql-11beta2/contrib/hstore/Makefile" line 20: Need an
operator
make: "../../src/../../src/Makefile.global" line 44: Need an operator
make: "../../src/../../src/Makefile.global" line 49: Need an operator
make: "../../src/../../src/Makefile.global" line 51: Need an operator
make: "../../src/../../src/Makefile.global" line 52: Missing dependency
operator
make: "../../src/../../src/Makefile.global" line 55: Need an operator
make: "../../src/../../src/Makefile.global" line 58: Need an operator
make: "../../src/../../src/Makefile.global" line 59: Need an operator
make: "../../src/../../src/Makefile.global" line 60: Need an operator
make: "../../src/../../src/Makefile.global" line 65: Missing dependency
operator
make: "../../src/../../src/Makefile.global" line 68: Need an operator
make: "../../src/../../src/Makefile.global" line 72: Need an operator
make: "../../src/../../src/Makefile.global" line 73: Need an operator
make: "../../src/../../src/Makefile.global" line 93: Need an operator
make: "../../src/../../src/Makefile.global" line 105: Need an operator
make: "../../src/../../src/Makefile.global" line 106: Need an operator
make: "../../src/../../src/Makefile.global" line 108: Need an operator
make: "../../src/../../src/Makefile.global" line 109: Need an operator
make: "../../src/../../src/Makefile.global" line 112: Need an operator
make: "../../src/../../src/Makefile.global" line 113: Need an operator
make: "../../src/../../src/Makefile.global" line 115: Need an operator
make: "../../src/../../src/Makefile.global" line 116: Need an operator
make: "../../src/../../src/Makefile.global" line 121: Need an operator
make: "../../src/../../src/Makefile.global" line 122: Need an operator
make: "../../src/../../src/Makefile.global" line 124: Need an operator
make: "../../src/../../src/Makefile.global" line 125: Need an operator
make: "../../src/../../src/Makefile.global" line 130: Need an operator
make: "../../src/../../src/Makefile.global" line 131: Need an operator
make: "../../src/../../src/Makefile.global" line 133: Need an operator
make: "../../src/../../src/Makefile.global" line 134: Need an operator
make: "../../src/../../src/Makefile.global" line 139: Need an operator
make: "../../src/../../src/Makefile.global" line 140: Need an operator
make: "../../src/../../src/Makefile.global" line 142: Need an operator
make: "../../src/../../src/Makefile.global" line 143: Need an operator
make: "../../src/../../src/Makefile.global" line 149: Need an operator
make: "../../src/../../src/Makefile.global" line 152: Need an operator
...

Dealing with latency to replication slave; what to do?

[Rory Falloon]

Hi,

Looking for any tips here on how to best maintain a replication slave which
is operating under some latency between networks - around 230ms. On a good
day/week, replication will keep up for a number of days, but however, when
the link is under higher than average usage, keeping replication active can
last merely minutes before falling behind again.

2018-07-24 18:46:14 GMTLOG:  database system is ready to accept read only
connections
2018-07-24 18:46:15 GMTLOG:  started streaming WAL from primary at
2B/9300 on timeline 1
2018-07-24 18:59:28 GMTLOG:  incomplete startup packet
2018-07-24 19:15:36 GMTLOG:  incomplete startup packet
2018-07-24 19:15:36 GMTLOG:  incomplete startup packet
2018-07-24 19:15:37 GMTLOG:  incomplete startup packet

As you can see above, it lasted about half an hour before falling out of
sync.

On the master, I have wal_keep_segments=128. What is happening when I see
"incomplete startup packet" - is it simply the slave has fallen behind,
and  cannot 'catch up' using the wal segments quick enough? I assume the
slave is using the wal segments to replay changes and assuming there are
enough wal segments to cover the period it cannot stream properly, it will
eventually recover?

Re: How can i install contrib modules in pg11 via source

[Adrian Klaver]

On 07/24/2018 12:00 PM, Márcio Antônio Sepp wrote:

Hi all,

How can i install contrib modules in pg11. I’m using FreeBSD 11.2.

In specific i need to install hstore for test purpose.


To confirm you are building from source correct?

If so I can tell you how I do it in Linux and you can make the 
appropriate translations to BSD.


1) cd to contrib/hstore/

2) make

3) sudo make install

4) In psql CREATE EXTENSION hstore;



Thanks in advance.

--

Att

Márcio A. Sepp




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

Re: logical replication snapshots

[Dimitri Maziuk]

On 07/24/2018 01:43 PM, Andres Freund wrote:
> On 2018-07-24 12:22:24 -0500, Dimitri Maziuk wrote:
>> On 07/24/2018 12:21 PM, Alvaro Herrera wrote:
>>>
>>> Are you serious about us trying to diagnose a bug with this description?
>>
>> What do you want to know, exactly?
> 
> A recipe that we can follow and reproduce the issue.

The nutshell version is as I said: I pretty much followed the manual to
create a test publication/subscription setup and let it run for a few
weeks. I had to increase a few limits but otherwise everything's at the
default settings as shipped in the rpms.

I can send you postgres config files from both nodes and the entire
database dump -- it's all public domain. I can `zfs send` you the
snapshot of the entire /var/lib/pgsql/10 as is, too.

If you are asking for something other than those two extremes, please
let me know what it is and I'll be happy to try and provide it.

-- 
Dimitri Maziuk
Programmer/sysadmin
BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu



signature.asc
Description: OpenPGP digital signature

How can i install contrib modules in pg11 via source

[Márcio Antônio Sepp]

 

Hi all,

 

 

How can i install contrib modules in pg11. I’m using FreeBSD 11.2.

In specific i need to install hstore for test purpose.

 

Thanks in advance.

 

 

--

Att

Márcio A. Sepp

 

Re: logical replication snapshots

[Andres Freund]

On 2018-07-24 12:22:24 -0500, Dimitri Maziuk wrote:
> On 07/24/2018 12:21 PM, Alvaro Herrera wrote:
> > 
> > Are you serious about us trying to diagnose a bug with this description?
> 
> What do you want to know, exactly?

A recipe that we can follow and reproduce the issue.

Greetings,

Andres Freund

Re: Speccing a remote backup server

[Adrian Klaver]

On 07/24/2018 09:10 AM, Ron wrote:

Hi,

v9.6 backing up v8.4

Where does the gzip run (where the database lives, or the remote server 
where the pg_dump runs from)?  I ask this because I need to know how 
beefy to make the backup server.  (It'll just store backups for a 
version upgrade.)




pg_dump is a client to the server, so wherever pg_dump runs.

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

Re: Connections on cluster not being logged

[Adrian Klaver]

On 07/24/2018 09:47 AM, Peter J. Holzer wrote:

On 2018-07-24 06:46:18 -0700, Adrian Klaver wrote:

On 07/24/2018 06:25 AM, Sandy Becker wrote:

There is only one set of logs since it's a hardware cluster.  The two
nodes share the underlying database storage.  Not sure why, but when the


The community Postgres can't do that, have two instances share the same data
storage, at least AFAIK. So are you using some fork of Postgres or are there
actually two data directories?


Maybe Postgresql only runs on the active node? I.e. a classic failover
cluster.


Yeah, the OP emailed me off-list saying just that. My suspicion, they 
where looking at log for inactive node.





(I'm still not sure what a "hardware cluster" is. Probably some kind of
appliance which packages two nodes, some storage and the HA software.)

 hp




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

Re: logical replication snapshots

[Dimitri Maziuk]

On 07/24/2018 12:21 PM, Alvaro Herrera wrote:
> 
> Are you serious about us trying to diagnose a bug with this description?

What do you want to know, exactly?

-- 
Dimitri Maziuk
Programmer/sysadmin
BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu



signature.asc
Description: OpenPGP digital signature

Re: logical replication snapshots

[Alvaro Herrera]

On 2018-Jul-24, Dimitri Maziuk wrote:

> On 7/23/2018 6:51 PM, Andres Freund wrote:

> > Could you describe what exactly you did into that situation?
> 
> Created a database, few publications, as per TFM, and a few subscriptions on
> another host. Went on vacation for 3 weeks. The problem host is centos
> 7.current w/ postgres rpms from PGDG repo. Postgres is on port 5434 because
> there is a stock one on 5432.

Are you serious about us trying to diagnose a bug with this description?

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Connections on cluster not being logged

[Peter J. Holzer]

On 2018-07-24 06:46:18 -0700, Adrian Klaver wrote:
> On 07/24/2018 06:25 AM, Sandy Becker wrote:
> > There is only one set of logs since it's a hardware cluster.  The two
> > nodes share the underlying database storage.  Not sure why, but when the
> 
> The community Postgres can't do that, have two instances share the same data
> storage, at least AFAIK. So are you using some fork of Postgres or are there
> actually two data directories?

Maybe Postgresql only runs on the active node? I.e. a classic failover
cluster.

(I'm still not sure what a "hardware cluster" is. Probably some kind of
appliance which packages two nodes, some storage and the HA software.)

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Speccing a remote backup server

[Ron]

Hi,

v9.6 backing up v8.4

Where does the gzip run (where the database lives, or the remote server 
where the pg_dump runs from)?  I ask this because I need to know how beefy 
to make the backup server.  (It'll just store backups for a version upgrade.)


--
Angular momentum makes the world go 'round.

Re: logical replication snapshots

[Dimitri Maziuk]

On 7/23/2018 6:51 PM, Andres Freund wrote:


What precedes that "loop"?


systemctl start postgresql-10


Could you describe what exactly you did into that situation?


Created a database, few publications, as per TFM, and a few 
subscriptions on another host. Went on vacation for 3 weeks. The problem 
host is centos 7.current w/ postgres rpms from PGDG repo. Postgres is on 
port 5434 because there is a stock one on 5432.



ls probably works in general, it's just the sorting that makes it
hang. Should work with -f or such.


I don't think this means what you think this means.

There are *13 million* files in 
/var/lib/pgsql/10/data/pg_logical/snapshots and postgres hangs forever 
on start with

FATAL:  the database system is starting up
FATAL:  the database system is starting up
FATAL:  the database system is starting up
...
I think the two are related. ICBW and all that. What do I do no not get 
it into that state again?


Thx

Order of execution for permissive RLS policies

[Simon Brent]

I've been using postgres for a while now, and have just started looking in to 
row level security. I have found something that I think is a bit strange, and 
wanted to know if anyone knows how/why it is the case.

I have a table with multiple policies, each with a USING statement. When I run 
EXPLAIN ANALYSE SELECT * FROM [table], I see that the policies are OR'd 
together in reverse alphabetical name order. It doesn't matter which order I 
create the policies in - the order they are checked is always (for example) zz 
OR yy OR xx OR ww.

I dug into the code in the postgres github repo a bit, but my knowledge of C is 
pretty limited, so I wasn't able to work out why this is happening. I did, 
however, note the comment about sorting policies here - 
https://github.com/postgres/postgres/blob/REL_10_4/src/backend/rewrite/rowsecurity.c#L509
 -

"sort_policies_by_name

This is only used for restrictive policies, ensuring that any
WithCheckOptions they generate are applied in a well-defined order.
This is not necessary for permissive policies, since they are all combined
together using OR into a single WithCheckOption check."

I would argue that the claim "This is not necessary for permissive policies" is 
false. In the case of multiple policies OR'd together, executing the policies 
from least to most expensive can have a dramatic effect on query speed, since 
there is the possibility that the more expensive policies will not be executed 
(when a cheaper policy returns true).

I guess my questions are:
1) Why is order considered unimportant for permissive policies?
2) How come permissive policies are always executed in reverse alphabetical 
order? (This is mostly for my curiosity)
3) Could the code be changed so that permissive policies are also run through 
sort_policies_by_name, to make the observed behaviour more sensible (and 
intentional), without any negative effects?

(Note: I've also observed the same behaviour - reverse alphabetical order of 
policies - in Postgres 9.6)

Thanks



-- 
 The Wellcome Sanger Institute is operated by Genome Research 
 Limited, a charity registered in England with number 1021457 and a 
 company registered in England with number 2742969, whose registered 
 office is 215 Euston Road, London, NW1 2BE. 

Re: Connections on cluster not being logged

[Adrian Klaver]

On 07/24/2018 06:25 AM, Sandy Becker wrote:
There is only one set of logs since it's a hardware cluster.  The two 
nodes share the underlying database storage.  Not sure why, but when the 


The community Postgres can't do that, have two instances share the same 
data storage, at least AFAIK. So are you using some fork of Postgres or 
are there actually two data directories?


log rolled over this morning, connections started getting logged.  All 
is good now.  Thanks for  your help.








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

Re: DB Backup from WAL Slave

[Andreas Kretschmer]

On 24 July 2018 14:44:45 CEST, basti  wrote:
>Hello,
>
>we have a db master and a slave.

-
>
>How can I do an backup with pg_dumpall from slave?

Set hot_standby_feedback to on.


Regards, Andreas


-- 
2ndQuadrant - The PostgreSQL Support Company

Re: Connections on cluster not being logged

[Sandy Becker]

There is only one set of logs since it's a hardware cluster.  The two nodes
share the underlying database storage.  Not sure why, but when the log
rolled over this morning, connections started getting logged.  All is good
now.  Thanks for  your help.



On Mon, Jul 23, 2018 at 1:58 PM, Adrian Klaver 
wrote:

> On 07/23/2018 12:50 PM, Sandy Becker wrote:
> Please reply to list also.
> Ccing list.
>
> Two servers set up in a hardware cluster for automatic failover.  That's
>> all I know about it.
>>
>
> Alright, so which server's logs are you looking at?
>
> Long term it would be a good thing to know how the cluster/failover is
> setup, as that is something folks on this list are going to ask when
> attempting to answer a question. Just trying to get ahead of the
> inevitable:)
>
>
>> On Mon, Jul 23, 2018 at 1:04 PM, Adrian Klaver > > wrote:
>>
>> On 07/23/2018 08:14 AM, Sandy Becker wrote:
>>
>> I have postgresql 9.4 on a cluster, hardware based.  I need to
>> be able to see which users are connecting to which database and
>> when to be in compliance with our security policies.
>>
>> I have set the following in the postgresql.conf and did a pg_ctl
>> reload:
>>
>>log_connections = on
>>log_line_prefix = '%t [%p]:[%u]:[%h]-[%d] [%1-1]'
>>
>> Unfortunately, it doesn't seem to be logging the connections.
>>  This is what I have used on our non-clustered instances and it's
>> working as expected.  Have I missed something relating to
>> logging on a cluster?  The second node is strictly for
>> automatice failover, so nothing is actually running there at the
>> moment.
>>
>>
>> Can you define what you mean by a cluster?
>>
>>
>>
>>
>> Thank you in advance for any suggestions.
>>
>> Sandy
>>
>>
>>
>> -- Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

DB Backup from WAL Slave

[basti]

Hello,

we have a db master and a slave.

master conf:

wal_level = replica
max_wal_senders = 5
wal_keep_segments = 100
archive_mode    = on
archive_command = 'rsync -a %p -e "ssh -i
/var/lib/postgresql/.ssh/id_rsa"
postgres@slave:/var/lib/postgresql/9.6/wals/master/%f https://www.postgresql.org/message-id/CADp-Sm6if-z0NbsxrJwPBQDQNt6AQJpbbagHbWyGrN7zyYZdCA%40mail.gmail.com
max_standby_streaming_delay = 30


The slave is not used as read-only
But as docu say
(https://www.postgresql.org/docs/9.6/static/runtime-config-wal.html)
wal_level = archive is mapped to replica

How can I do an backup with pg_dumpall from slave?

Best Regards,