Re: Users, Roles and Connection Pooling

[Laurenz Albe]

On Wed, 2019-10-02 at 08:46 +1000, Matt Andrews wrote:
> Here’s a question I’ve been asking for a while and just can’t find an
> answer to, so I thought I’d ask it here. The answer could be
> subjective, but here goes...
> 
> When a web app connects to Postgres via a connection pooler, what is
> the best way to manage privileges for the connecting user? Especially
> when their is a complex hierarchy of privileges?
> 
> Should each signed up user have their own role which inherits from
> whichever roles they are members of? This means that the connection
> pool role should then be granted in every user?
> 
> Does creating a Postgres role for every app user scale? Roles can
> only have names, is an email address a good name for individual app
> users?
> 
> Are their any resources out there that anyone knows of that addresses
> these questions?
> 
> There’s plenty of stuff out there on roles and permissions, I’ve read
> a lot of it, but not much on dealing with individual app users
> through a connection pool. 
> 
> I’m thinking that any queries should start with SET ROLE ‘username’
> and end with RESET ROLE. Is this how it could work?
> 
> Any help would be greatly appreciated. 

A couple of pointers:

- The role that the application server connects with should be a
  member of each database role it wants to become through SET ROLE.

- Yes, you will have to start with SET ROLE.
  RESET ROLE should be executed by the pool when it gets a connection
  back.

- This is a good setup if you don't have too many users.  Metadata
  queries will start getting slow if you get into the tens of thousands
  of users, maybe earlier.

  The advantages are that you can make use of PostgreSQL's rich
  permission concept and row level security.

- The name of the user might as well be an e-mail address, as long
  as it does not exceed 63 bytes.

- Make use of user groups and grant privileges on that level rather
  than to the individual users.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Users, Roles and Connection Pooling

[Stephen Frost]

Greetings,

* Laurenz Albe (laurenz.a...@cybertec.at) wrote:
> A couple of pointers:

I generally agree with these comments.

> - This is a good setup if you don't have too many users.  Metadata
>   queries will start getting slow if you get into the tens of thousands
>   of users, maybe earlier.

While this seems plausible- I'd love to hear about exactly what you've
seen start to be a problem when getting up to that many users.  Are you
just referring to things like \du?  Or..?

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Users, Roles and Connection Pooling

[Matt Andrews]

I have little experience in this area, but it seems like having a Postgres
role for every application user is the right way to do things. It’s just
that it also seems really inconvenient.

For example how to map an application’s users/people table to Postgres
roles? The pg_role name field is limited to 64 bytes, you can’t create a
foreign key to pg_role. What’s the answer? Use UUIDs as usernames or
something?

There’s very little out there on this topic, but surely this has been done
before.

On Wed, 2 Oct 2019 at 17:43, Stephen Frost  wrote:

> Greetings,
>
> * Laurenz Albe (laurenz.a...@cybertec.at) wrote:
> > A couple of pointers:
>
> I generally agree with these comments.
>
> > - This is a good setup if you don't have too many users.  Metadata
> >   queries will start getting slow if you get into the tens of thousands
> >   of users, maybe earlier.
>
> While this seems plausible- I'd love to hear about exactly what you've
> seen start to be a problem when getting up to that many users.  Are you
> just referring to things like \du?  Or..?
>
> Thanks,
>
> Stephen
>
-- 
Matt Andrews

0400 990 131

Re: Users, Roles and Connection Pooling

[Rob Sargent]

> On Oct 2, 2019, at 3:41 AM, Matt Andrews  wrote:
> 
> I have little experience in this area, but it seems like having a Postgres 
> role for every application user is the right way to do things. It’s just that 
> it also seems really inconvenient.
> 
> For example how to map an application’s users/people table to Postgres roles? 
> The pg_role name field is limited to 64 bytes, you can’t create a foreign key 
> to pg_role. What’s the answer? Use UUIDs as usernames or something?
> 
> There’s very little out there on this topic, but surely this has been done 
> before. 
> 
>> On Wed, 2 Oct 2019 at 17:43, Stephen Frost  wrote:
>> Greetings,
>> 
>> * Laurenz Albe (laurenz.a...@cybertec.at) wrote:
>> > A couple of pointers:
>> 
>> I generally agree with these comments.
>> 
>> > - This is a good setup if you don't have too many users.  Metadata
>> >   queries will start getting slow if you get into the tens of thousands
>> >   of users, maybe earlier.
>> 
>> While this seems plausible- I'd love to hear about exactly what you've
>> seen start to be a problem when getting up to that many users.  Are you
>> just referring to things like \du?  Or..?
>> 
>> Thanks,
>> 
>> Stephen
The terminology gets a little wonky here since “user” equals “role” in postgres 
terms but I’ll apply user to the person using your app. 
What are your expected numbers of total distinct users? 
Ratio of users to roles (as permissions set) or is every user unique in access 
needs?
Do any users need to be in more than one role/group? 
When/how will you assign role to user?
I feel these issues will affect your choice of design. 

Re: Users, Roles and Connection Pooling

[Matt Andrews]

Yes, I’ll be more clear with the terminology. When I say user, I mean an
individual application user, which most likely is a person.

I’m also asking about this in a general sense, being concerned more with
implementation details.

The Postgres role system is really powerful and versatile, why should it be
a problem to create privilege hierarchies and provide individuals with
privileges from any branch of the hierarchy?

Obviously, designing privileges should be done carefully, but granting
roles to users should be easy. I can easily imagine an organisation that
would require only a few privileges for many people, but many different
privileges for a few people.

Does it come down to performance issues when there are many roles to users?

On Wed, 2 Oct 2019 at 21:03, Rob Sargent  wrote:

>
>
> On Oct 2, 2019, at 3:41 AM, Matt Andrews 
> wrote:
>
> I have little experience in this area, but it seems like having a Postgres
> role for every application user is the right way to do things. It’s just
> that it also seems really inconvenient.
>
> For example how to map an application’s users/people table to Postgres
> roles? The pg_role name field is limited to 64 bytes, you can’t create a
> foreign key to pg_role. What’s the answer? Use UUIDs as usernames or
> something?
>
> There’s very little out there on this topic, but surely this has been done
> before.
>
> On Wed, 2 Oct 2019 at 17:43, Stephen Frost  wrote:
>
>> Greetings,
>>
>> * Laurenz Albe (laurenz.a...@cybertec.at) wrote:
>> > A couple of pointers:
>>
>> I generally agree with these comments.
>>
>> > - This is a good setup if you don't have too many users.  Metadata
>> >   queries will start getting slow if you get into the tens of thousands
>> >   of users, maybe earlier.
>>
>> While this seems plausible- I'd love to hear about exactly what you've
>> seen start to be a problem when getting up to that many users.  Are you
>> just referring to things like \du?  Or..?
>>
>> Thanks,
>>
>> Stephen
>>
> The terminology gets a little wonky here since “user” equals “role” in
> postgres terms but I’ll apply user to the person using your app.
> What are your expected numbers of total distinct users?
> Ratio of users to roles (as permissions set) or is every user unique in
> access needs?
> Do any users need to be in more than one role/group?
> When/how will you assign role to user?
> I feel these issues will affect your choice of design.
>
-- 
Matt Andrews

0400 990 131

A post describing PostgreSQL 12 Generated Columns

[Pankaj Jangid]

Found a very nice article about PostgreSQL 12 Generated Columns. I
thought this might be useful for everyone. Hence sharing.

https://pgdash.io/blog/postgres-12-generated-columns.html

-- 
Pankaj Jangid

Questions about Partitioned Tables and Indexes

[Evelyn Dibben]

I apologize for the lengthy post.  I'm trying to get in all the details.

We recently upgraded our Postgres AWS RDS from 9.5 to 11.1.

We have several large partitioned tables implemented using inheritance that
we are considering converting to declarative partitioning.
(I'm talking about 5TB of partitioned data).   I want to be sure of my
methodology before I push forward.

For example here is how we would have created a partitioned table with
inheritance.  The table has a primary key and an index.  The inherited
partition has a check constraint and an index.  (Not shown is the trigger
on the primary table that would put the new rows in the correct partition.)

CREATE TABLE test
(
date_key numeric(15,0) NOT NULL,
metric numeric(15,0) NOT NULL,
value numeric(28,5) NOT NULL,
CONSTRAINT test_pkey PRIMARY KEY (date_key,metric)
)
TABLESPACE pg_default;

CREATE INDEX test_idx1
ON test USING btree
(metric)
TABLESPACE pg_default;

CREATE TABLE test_201908
(
CONSTRAINT const_test_chk CHECK (date_key >= 20190801::numeric AND
date_key <= 20190831::numeric)
)
INHERITS (test)
TABLESPACE pg_default;

CREATE INDEX test_idx1_201908
ON test_201908 USING btree
(metric)
TABLESPACE pg_default;

AMZGQ3DW=> \d+ edibben.test
   Table "edibben.test"
  Column  | Type  | Collation | Nullable | Default | Storage |
Stats target | Description
--+---+---+--+-+-+--+-
 date_key | numeric(15,0) |   | not null | | main|
 |
 metric   | numeric(15,0) |   | not null | | main|
 |
 value| numeric(28,5) |   | not null | | main|
 |
Indexes:
"test_pkey" PRIMARY KEY, btree (date_key, metric)
"test_idx1" btree (metric)
Child tables: edibben.test_201908


AMZGQ3DW=> \d+ edibben.test_201908
   Table "edibben.test_201908"
  Column  | Type  | Collation | Nullable | Default | Storage |
Stats target | Description
--+---+---+--+-+-+--+-
 date_key | numeric(15,0) |   | not null | | main|
 |
 metric   | numeric(15,0) |   | not null | | main|
 |
 value| numeric(28,5) |   | not null | | main|
 |
Indexes:
"test_idx1_201908" btree (metric)
Check constraints:
"const_test_chk" CHECK (date_key >= 20190801::numeric AND date_key <=
20190831::numeric)
Inherits: edibben.test

I know that I can convert this table into a declarative partitioned table
by doing the following:

Create a new partitioned table:

CREATE TABLE test_part
(
date_key numeric(15,0) NOT NULL,
metric numeric(15,0) NOT NULL,
value numeric(28,5) NOT NULL,
CONSTRAINT test_part_pkey PRIMARY KEY (date_key,metric)
) PARTITION BY RANGE (date_key)
TABLESPACE pg_default;

CREATE INDEX test_part_idx1
ON test_part USING btree
(metric)
TABLESPACE pg_default;

Drop the inheritance on the test_201908 table:

alter table test_201908 no inherit test;

And then add this table to the partitioned table.  The doco says to keep
the check constraint in place until after the data is loaded.

alter table test_part
attach partition test_201908
for VALUES FROM (20190801) TO (20190831);

The partition shows up attached to the table:

 \d+ edibben.test_part
Table "edibben.test_part"
  Column  | Type  | Collation | Nullable | Default | Storage |
Stats target | Description
--+---+---+--+-+-+--+-
 date_key | numeric(15,0) |   | not null | | main|
 |
 metric   | numeric(15,0) |   | not null | | main|
 |
 value| numeric(28,5) |   | not null | | main|
 |
Partition key: RANGE (date_key)
Indexes:
"test_part_pkey" PRIMARY KEY, btree (date_key, metric)
"test_part_idx1" btree (metric)
Partitions: edibben.test_201908 FOR VALUES FROM ('20190801') TO ('20190831')

My question is about what happens to the indexes.  When you examine the
partition you see the primary key inherited from the partition table
and the original index (test_idx1_201908).

AMZGQ3DW-> \d+ edibben.test_201908
   Table "edibben.test_201908"
  Column  | Type  | Collation | Nullable | Default | Storage |
Stats target | Description
--+---+---+--+-+-+--+-
 date_key | numeric(15,0) |   | not null | | main|
 |
 metric   | numeric(15,0) |   | not null | | main|
 |
 value| numeric(28,5) |   | not null | | main|
 |
Partition of: edibben.test_part FOR VALUES FROM ('20190801')

Performance on JSONB select

[PegoraroF10]

select * from MyTable where
  ((JsonBField->>'status'='descartada' and
To_Date(JsonBField->'descartada'->>'data','-mm-dd') > Current_Date) or
   (JsonBField->>'status'='contrato' and
To_Date(JsonBField->'contrato'->>'data','-mm-dd') > Current_Date-7) or
   (JsonBField->>'status'='naoatribuido'));

Considering this table has a million records, I would like to use a proper
index, but how can I use an index when using operator >. 




--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

PG11 Parallel Thanks!!

[Jason Ralph]

I wanted to drop a quick note thanking the developers who have contributed to 
Postgres.  I have recently upgraded our production PG instances from pg9.3 to 
pg11.

We do a lot of table syncs, and we have one process at the end of the month 
that syncs 3 very large tables (400GB).  This sync happens from a shell script 
using pg_dump and pg_restore, we have it set to use -j3 but it's called 
sequentially so it never really takes advantage of parallel.

Since pg11 on both the target and source, the run time has decreased a lot, I 
chalk it up to the parallel index creations in pg11 which was a very time 
consuming process on pg9.3.
The process has finished almost 10 hours earlier than pg93.  So thank you for 
your hard work and dedication to this awesome piece of software.



Jason Ralph


This message contains confidential information and is intended only for the 
individual named. If you are not the named addressee you should not 
disseminate, distribute or copy this e-mail. Please notify the sender 
immediately by e-mail if you have received this e-mail by mistake and delete 
this e-mail from your system. E-mail transmission cannot be guaranteed to be 
secure or error-free as information could be intercepted, corrupted, lost, 
destroyed, arrive late or incomplete, or contain viruses. The sender therefore 
does not accept liability for any errors or omissions in the contents of this 
message, which arise as a result of e-mail transmission. If verification is 
required please request a hard-copy version.

Re: Performance on JSONB select

[Michael Lewis]

Much of indexing strategy depends on knowing the data like how many
distinct values and what the distribution is like. Is JsonBField->>'status'
always set? Are those three values mentioned in this query common or rare?
Can you re-write this query to avoid using an OR in the where clause? Are
you just wanting to add a GIN index for the jsonb paths? Or do you want
indexed like below that are a bit stylized to this query?

CREATE INDEX idx_MyTable_status USING btree( JsonBField->>'status' );
CREATE INDEX idx_MyTable_descartada_date USING btree(
To_Date(JsonBField->'descartada'->>'data','-mm-dd') );
CREATE INDEX idx_MyTable_contrato_date USING btree(
To_Date(JsonBField->'contrato'->>'data','-mm-dd') );

Urgent :: Postgresql streaming replication issue - sync mode

[Shital A]

Hello,

We are seeing a strange issue with postgresql streaming application in sync
mode.

We are using postgresql 9.6. Old version because of some specific
requirements.  We have setup cluster with master-standby using pacemaker.

When we kill master using killall -9 postgres. The failed primary has few
records extra than standby node. We have done setup with synchronous_commit
= remote_apply and have set synchronous_standby_names=server_name.

As the failed primary is having more data, How is it possible that primary
is committing transaction before they were applied on standby with
synchronous_commit=remote_apply?


Please share if you have any thoughts. Are we missing any config ?

Thanks !

Re: Urgent :: Postgresql streaming replication issue - sync mode

[Ravi Krishna]

> 
> As the failed primary is having more data, How is it possible that primary is 
> committing transaction before they were applied on standby with 
> synchronous_commit=remote_apply?

If I am not mistaken remote_apply is only from ver 11.

Re: Users, Roles and Connection Pooling

[Stephen Frost]

Greetings,

(we don't top-post on these lists, fyi, please reply in-line and trim)

* Matt Andrews (mattandr...@massey.com.au) wrote:
> I have little experience in this area, but it seems like having a Postgres
> role for every application user is the right way to do things. It’s just
> that it also seems really inconvenient.

I agree that there are some drawbacks to it.

> For example how to map an application’s users/people table to Postgres
> roles? The pg_role name field is limited to 64 bytes, you can’t create a
> foreign key to pg_role. What’s the answer? Use UUIDs as usernames or
> something?

Yeah, it would be nice to have an answer to the FK issue when it comes
to roles (and possibly other things..).  The limit on length is annoying
but I'm not sure that it's show-stopper.  I don't think using UUIDs is a
good idea, at all...

> There’s very little out there on this topic, but surely this has been done
> before.

Oh, absolutely, but with compromises, particularly around FKs and such.

Thanks,

Stephen


signature.asc
Description: PGP signature

Drop a primary

[Martin Mueller]

I created a primary key with the following commands

Add id serial
Add primary key (id)

I cannot figure out from the documentation how to drop that column.

Re: Drop a primary

[Ron]

On 10/2/19 1:48 PM, Martin Mueller wrote:


I created a primary key with the following commands

Add id serial

Add primary key (id)

I cannot figure out from the documentation how to drop that column.



Drop it just like you added it:

test=# alter table foobar add id serial;
ALTER TABLE
test=# alter table foobar add primary key(id);
ALTER TABLE
test=# alter table foobar drop id;
ALTER TABLE


--
Angular momentum makes the world go 'round.

Re: Query Tuning

[Michael Lewis]

Both of the below visualizers can help, but require some knowledge about
comparing estimated vs actual row estimates, disk sorts vs in memory,
etc. Drawing implications about whether your schema needs to change or just
the query will take time to master as well.

http://tatiyants.com/pev/#/plans/new --data only stored locally on your
computer, nice for security but not great for sharing with others and
getting help, since you need to share full json output (and query ideally)

https://explain.depesz.com/ --URL is unique and sharable for help from
others


Often you need to provide much more information to get good advice. What
version of Postgres are you on? What are stats like for the involved
tables? What other indexes are available or might you add? What is the use
of this system-- single user or thousands? Heavy writes continually, or
load & analysis? Can you share the full query text and output of EXPLAIN
ANALYZE on it?

partitions vs indexes

[Enrico Thierbach]

Hello list,

I run into some trouble with partitions:

I would like to convert a table with a primary key into a partitioned 
setup by a column which is not part of the primary key. Also, a column 
might hold a referenece to a parent row. So this is my current table 
setup, slimmed down:



CREATE TYPE statuses AS ENUM ('ready', ‘processing’, 
‘done’);


CREATE TABLE mytable (
  id  BIGSERIAL PRIMARY KEY NOT NULL,
  parent_id   BIGINT REFERENCES mytable(id) ON DELETE CASCADE,
  status statuses DEFAULT 'ready'
);

Since entries in the table are often frequented when status is not 
‘done’ I would like to partition by state. However, if I want to do 
that:


CREATE TABLE mytable (
  id  BIGSERIAL NOT NULL,
  parent_id   BIGINT REFERENCES mytable(id) ON DELETE CASCADE,
  status statuses DEFAULT 'ready'

  -- UNIQUE(id, status)-- doesn’t work: can’t reference 
parent

  -- UNIQUE(id)-- doesn’t work: can’t partition
) PARTITION BY LIST(status);

I need to add status to the primary key or another unique constraint. In 
that case, however, I can no longer have the foreign key constraint on 
parent_id.


Weirdly enough partitioning works fine if there is no unique constraint 
on that table:


CREATE TABLE mytable (
  id  BIGSERIAL NOT NULL,
  status statuses DEFAULT 'ready'
) PARTITION BY LIST(status);


So partitioning seems to require the column being in a unique constraint 
if and only if a unique constraint exist on the table. Also I cannot 
create multiple unique constraints on the table.


Here comes my question:

- Do I miss something?
- ThI don’t understand the requirement the partition value to be part 
of a unique constraint if such a constraint exists, since partitioning 
seems to work fine if the table has no unique constraints at all. Can 
someone shed some light on that? Is that maybe an artificial limitation 
that will go away on the future?

- Any suggestions how I could proceed?

Thank you for any suggestion!

Best,
Eno

--
me on github: http://github.com/radiospiel

Re: partitions vs indexes

[Enrico Thierbach]

On 2 Oct 2019, at 22:09, Enrico Thierbach wrote:


Hello list,

I run into some trouble with partitions:

I would like to convert a table with a primary key into a partitioned 
setup by a column which is not part of the primary key. Also, a column 
might hold a referenece to a parent row. So this is my current table 
setup, slimmed down:



CREATE TYPE statuses AS ENUM ('ready', ‘processing’, 
‘done’);


CREATE TABLE mytable (
  id  BIGSERIAL PRIMARY KEY NOT NULL,
  parent_id   BIGINT REFERENCES mytable(id) ON DELETE CASCADE,
  status statuses DEFAULT 'ready'
);

Since entries in the table are often frequented when status is not 
‘done’ I would like to partition by state. However, if I want to 
do that:


CREATE TABLE mytable (
  id  BIGSERIAL NOT NULL,
  parent_id   BIGINT REFERENCES mytable(id) ON DELETE CASCADE,
  status statuses DEFAULT 'ready'

  -- UNIQUE(id, status)-- doesn’t work: can’t 
reference parent
  -- UNIQUE(id)-- doesn’t work: can’t 
partition

) PARTITION BY LIST(status);

I need to add status to the primary key or another unique constraint. 
In that case, however, I can no longer have the foreign key constraint 
on parent_id.


Weirdly enough partitioning works fine if there is no unique 
constraint on that table:


CREATE TABLE mytable (
  id  BIGSERIAL NOT NULL,
  status statuses DEFAULT 'ready'
) PARTITION BY LIST(status);


So partitioning seems to require the column being in a unique 
constraint if and only if a unique constraint exist on the table. Also 
I cannot create multiple unique constraints on the table.


Here comes my question:

- Do I miss something?
- ThI don’t understand the requirement the partition value to be 
part of a unique constraint if such a constraint exists, since 
partitioning seems to work fine if the table has no unique constraints 
at all. Can someone shed some light on that? Is that maybe an 
artificial limitation that will go away on the future?

- Any suggestions how I could proceed?

Thank you for any suggestion!

Best,
Eno

--
me on github: http://github.com/radiospiel


and, errm, forgot to mention thatI am on postgresql 11.3. Sorry for that 
omission.


Best,
eno

--
me on github: http://github.com/radiospiel

Re: partitions vs indexes

[Michael Lewis]

"I would like to convert a table with a primary key into a partitioned
setup by a column which is not part of the primary key"

That isn't possible. The partition key must be contained by the primary
key. That is, the primary key could be site_id, id and you can create hash
partition on id or site_id but not created_on.

You could drop primary key and foreign keys and implement them via trigger
functions as described in this blog series, but it seems questionable-
https://www.depesz.com/2018/10/31/foreign-key-to-partitioned-table-part-2/

I do not assume the restriction would be dropped in future releases. I
don't know that scanning all the partitions to figure out whether the
primary key is violated would be advisable. Which is what the trigger
functions described in the blog post has to do, right?

It might be noteworthy that partitioning with more than 10-100 partitions
is MUCH faster in PG12 than PG11 (up to 4-8 thousand partitions) from
testing shared by those working on that code.

>

Wall shiping replica failed to recover database with error: invalid contrecord length 1956 at FED/38FFE208

[Aleš Zelený]

Hello,

we run out of disk space on our production primary database on file system
dedicated for WALs (one of our logical replica died and thus WALs were
accumulated).
As expeced, primary instance shuts down:


Primary instance:
PostgreSQL 11.3 (Ubuntu 11.3-1.pgdg18.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04) 7.4.0, 64-bit

2019-09-29 19:24:28 UTC 2241 5d36488f.8c1 10[53100]:PANIC:  could
not write to file "pg_wal/xlogtemp.2241": No space left on device
2019-09-29 19:24:30 UTC 1706 5d364871.6aa 33870[0]:LOG:  WAL
writer process (PID 2241) was terminated by signal 6: Aborted
2019-09-29 19:24:30 UTC 1706 5d364871.6aa 33880[0]:LOG:
 terminating any other active server processes

2019-09-29 19:24:30 UTC 1706 5d364871.6aa 33900[0]:LOG:  all
server processes terminated; reinitializing
2019-09-29 19:24:30 UTC 21499 5d9104ee.53fb 10[0]:LOG:
 database system was interrupted; last known up at 2019-09-29 19:23:47 UTC

2019-09-29 19:24:33 UTC 21499 5d9104ee.53fb 20[0]:LOG:
 recovered replication state of node 1 to CF3/442D0758
2019-09-29 19:24:33 UTC 21499 5d9104ee.53fb 30[0]:LOG:
 database system was not properly shut down; automatic recovery in progress
2019-09-29 19:24:33 UTC 21499 5d9104ee.53fb 40[0]:LOG:  redo
starts at FE9/A49830D8

2019-09-29 19:25:55 UTC 21499 5d9104ee.53fb 50[0]:LOG:  redo
done at FED/38FFC540
2019-09-29 19:25:55 UTC 21499 5d9104ee.53fb 60[0]:LOG:  last
completed transaction was at log time 2019-09-29 19:22:06.597333+00
2019-09-29 19:25:55 UTC 21499 5d9104ee.53fb 70[0]:LOG:
 checkpoint starting: end-of-recovery immediate

2019-09-29 19:26:59 UTC 21499 5d9104ee.53fb 80[0]:LOG:
 checkpoint complete: wrote 1046940 buffers (99.8%); 0 WAL file(s) added, 0
removed, 0 recycled; write=64.166 s, sync=0.008 s, total=64.212 s; sync
files=184, longest=0.001 s, average=0.000 s; distance=15014380 kB,
estimate=15014380 kB
2019-09-29 19:26:59 UTC 21499 5d9104ee.53fb 90[53100]:FATAL:  could
not write to file "pg_wal/xlogtemp.21499": No space left on device
2019-09-29 19:26:59 UTC 1706 5d364871.6aa 33910[0]:LOG:
 startup process (PID 21499) exited with exit code 1
2019-09-29 19:26:59 UTC 1706 5d364871.6aa 33920[0]:LOG:
 aborting startup due to startup process failure
2019-09-29 19:26:59 UTC 1706 5d364871.6aa 33930[0]:LOG:
 database system is shut down

Expceted bahavior, free space on file system for WAL was 14MB, so not
enough for next WAL file to be saved.

Once falied logical replica was back up & running, we have extened
filesystem for WALs and started primary instance:

2019-09-30 08:21:44 UTC 13635 5d91bb18.3543 10[0]:LOG:  Auto
detecting pg_stat_kcache.linux_hz parameter...
2019-09-30 08:21:44 UTC 13635 5d91bb18.3543 20[0]:LOG:
 pg_stat_kcache.linux_hz is set to 100
2019-09-30 08:21:44 UTC 13635 5d91bb18.3543 30[0]:LOG:
 listening on IPv4 address "0.0.0.0", port 5432
2019-09-30 08:21:44 UTC 13635 5d91bb18.3543 40[0]:LOG:
 listening on IPv6 address "::", port 5432
2019-09-30 08:21:44 UTC 13635 5d91bb18.3543 50[0]:LOG:
 listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-09-30 08:21:44 UTC 13637 5d91bb18.3545 10[0]:LOG:
 database system was shut down at 2019-09-29 19:26:59 UTC
2019-09-30 08:21:44 UTC 13637 5d91bb18.3545 20[0]:LOG:
 recovered replication state of node 1 to CF3/442D0758
2019-09-30 08:21:44 UTC 13646 5d91bb18.354e 10[0]:LOG:
 pg_partman master background worker master process initialized with role
postgres
2019-09-30 08:21:44 UTC 13635 5d91bb18.3543 60[0]:LOG:
 database system is ready to accept connections
2019-09-30 08:21:44 UTC 13645 5d91bb18.354d 10 2/0   [0]:LOG:
 pg_cron scheduler started
2019-09-30 08:21:44 UTC 13647 5d91bb18.354f 10 3/0   [0]:LOG:  POWA
connected to database powa

So primary instance stated, all logical replications streams connected and
as soon as logical replication for failed subscriber process WALs,
unnecessery WALs were deleted as expected. Until this point, all works like
a charm  from primary instance point of view.


Form this primary instance we are running wall shipping replica (pgBackRest
is used) and this is where issues started.

WAL recovery replica:
PostgreSQL 11.3 (Ubuntu 11.3-1.pgdg18.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04) 7.4.0, 64-bit

WAL files were restored from backup till last one backed up before primary
instance shut down.

2019-09-29 19:24:31.390 P00   INFO: found 00010FED0037 in the
archive
2019-09-29 19:24:31.391 P00   INFO: archive-get command end: completed
successfully (43ms)
2019-09-29 19:24:31 UTC 3062 5d76145f.bf6 1810120 1/0   [0]:LOG:
 restored log file "00010FED0037" from archive
2019-09-29 19:24:31.919 P00   IN

Re: Performance on JSONB select

[PegoraroF10]

ok, my select performed better but I had to create 8 indices to speed up my
query.

I would love to create just one index using GIN(JsonBField jsonb_ops) but
using version 11 I cannot use operators like > and  <. 

I see on docs that version 12 has jsonpath Filter Expression Elements and
they include > and <. So, the question is, will be possible to have just one
index and use it with "jsonpath Filter Expression Elements ?



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Urgent :: Postgresql streaming replication issue - sync mode

[Shital A]

On Thu, 3 Oct 2019, 00:08 Ravi Krishna,  wrote:

> >
> > As the failed primary is having more data, How is it possible that
> primary is committing transaction before they were applied on standby with
> synchronous_commit=remote_apply?
>
> If I am not mistaken remote_apply is only from ver 11.
>

Hi Ravi,

Thanks for your reply.

This property/feature is available in 9.6.
https://www.postgresql.org/docs/9.6/runtime-config-wal.html


Thanks!

>

Re: Performance on JSONB select

[Stephen Frost]

Greetings,

* Michael Lewis (mle...@entrata.com) wrote:
> Much of indexing strategy depends on knowing the data like how many
> distinct values and what the distribution is like. Is JsonBField->>'status'
> always set? Are those three values mentioned in this query common or rare?
> Can you re-write this query to avoid using an OR in the where clause? Are
> you just wanting to add a GIN index for the jsonb paths? Or do you want
> indexed like below that are a bit stylized to this query?

If you know a field is going to always be there, you're better off, by
far, by just having a regular column for that value and a straight up
btree for it.  This saves a significant amount of space and makes it
much easier to index and work with.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Wall shiping replica failed to recover database with error: invalid contrecord length 1956 at FED/38FFE208

[Stephen Frost]

Greetings,

* Aleš Zelený (zeleny.a...@gmail.com) wrote:
> But recovery on replica failed to proceed WAL file
> 00010FED0039  with log message: " invalid contrecord length
> 1956 at FED/38FFE208".

Err- you've drawn the wrong conclusion from that message (and you're
certainly not alone- it's a terrible message and we should really have a
HINT there or something).  That's an INFO-level message, not an error,
and basically just means "oh, look, there's an invalid WAL record, guess
we got to the end of the WAL available from this source."  If you had
had primary_conninfo configured in your recovery.conf, PG would likely
have connected to the primary and started replication.  One other point
is that if you actually did a promotion in this process somewhere, then
you might want to set recovery_target_timeline=latest, to make sure the
replica follows along on the timeline switch that happens when a
promotion happens.

Thanks,

Stephen


signature.asc
Description: PGP signature

performance of pg_upgrade "Copying user relation files"

[Glenn Pierce]

I have been trying to upgrade postgres 9.5 to 9.6 with pg_upgrade

Everything seems to start fine but when pg_upgrade gets to "Copying
user relation files"
It takes ages to complete copying.

ie
Each file like

/var/lib/pgsql/9.5/data/base/24602/25140
is 1G and taking ~5-10 minutes to copyand that directory is 223G of
about 990 files

After a couple of hours running pg_upgrade I had to give up.

looking a iotop I was seeing

99% IO load caused by [md0_raid1]

and only seeing pg_upgrade go to 99% every 3-4 seconds

I had no other processes using io or high cpu.

Has anyone got any pointers of what could be the issue ?

Ps running cp on /var/lib/pgsql/9.5/data/base/24602/25140 to my home
directory is instantaneous

System is Centos 7

Thanks

Re: Questions about Partitioned Tables and Indexes

[legrand legrand]

Hi,

what you proposed seems correct to me.
I don't know how to list indexes from a partitionned index.

You can check if your indexes are valid using:

   select i.relname as indexname,i.relkind, t.relname as tablename,
t.relkind, idx.indisvalid
from pg_class i
join pg_index idx on idx.indexrelid = i.oid
join pg_class t on t.oid = idx.indrelid
where t.relname like 'test%'


to be really sure, try EXPLAIN with statements that should use those indexes
like

postgres=# explain select * from test_part where date_key >0;
  QUERY PLAN
---
 Append  (cost=6.53..43.80 rows=614 width=60)
   ->  Bitmap Heap Scan on test_201908  (cost=6.53..20.37 rows=307 width=60)
 Recheck Cond: (date_key > '0'::numeric)
 ->  Bitmap Index Scan on test_201908_pkey  (cost=0.00..6.45
rows=307 width=0)
   Index Cond: (date_key > '0'::numeric)
   ->  Bitmap Heap Scan on test_201909  (cost=6.53..20.37 rows=307 width=60)
 Recheck Cond: (date_key > '0'::numeric)
 ->  Bitmap Index Scan on test_201909_pkey  (cost=0.00..6.45
rows=307 width=0)
   Index Cond: (date_key > '0'::numeric)
(9 rows)

or

postgres=# explain select * from test_part where metric >0;
 QUERY PLAN
-
 Append  (cost=6.53..43.80 rows=614 width=60)
   ->  Bitmap Heap Scan on test_201908  (cost=6.53..20.37 rows=307 width=60)
 Recheck Cond: (metric > '0'::numeric)
 ->  Bitmap Index Scan on test_idx1_201908  (cost=0.00..6.45
rows=307 width=0)
   Index Cond: (metric > '0'::numeric)
   ->  Bitmap Heap Scan on test_201909  (cost=6.53..20.37 rows=307 width=60)
 Recheck Cond: (metric > '0'::numeric)
 ->  Bitmap Index Scan on test_201909_metric_idx  (cost=0.00..6.45
rows=307 width=0)
   Index Cond: (metric > '0'::numeric)
(9 rows)

Regards
PAscal



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: performance of pg_upgrade "Copying user relation files"

[Adrian Klaver]

On 10/2/19 3:30 PM, Glenn Pierce wrote:

I have been trying to upgrade postgres 9.5 to 9.6 with pg_upgrade

Everything seems to start fine but when pg_upgrade gets to "Copying
user relation files"
It takes ages to complete copying.


This is going to need more information:

1) What is the pg_upgrade command you are using?

2) Where are you upgrading from/to?

3) What OS, version?




ie
Each file like

/var/lib/pgsql/9.5/data/base/24602/25140
is 1G and taking ~5-10 minutes to copyand that directory is 223G of
about 990 files

After a couple of hours running pg_upgrade I had to give up.

looking a iotop I was seeing

99% IO load caused by [md0_raid1]

and only seeing pg_upgrade go to 99% every 3-4 seconds

I had no other processes using io or high cpu.

Has anyone got any pointers of what could be the issue ?

Ps running cp on /var/lib/pgsql/9.5/data/base/24602/25140 to my home
directory is instantaneous

System is Centos 7

Thanks





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

Re: performance of pg_upgrade "Copying user relation files"

[Adrian Klaver]

On 10/2/19 3:30 PM, Glenn Pierce wrote:

I have been trying to upgrade postgres 9.5 to 9.6 with pg_upgrade

Everything seems to start fine but when pg_upgrade gets to "Copying
user relation files"
It takes ages to complete copying.


Adding to previous list:

4) Which pg_upgrade version did you use, 9.5 or 9.6?



ie
Each file like

/var/lib/pgsql/9.5/data/base/24602/25140
is 1G and taking ~5-10 minutes to copyand that directory is 223G of
about 990 files

After a couple of hours running pg_upgrade I had to give up.

looking a iotop I was seeing

99% IO load caused by [md0_raid1]

and only seeing pg_upgrade go to 99% every 3-4 seconds

I had no other processes using io or high cpu.

Has anyone got any pointers of what could be the issue ?

Ps running cp on /var/lib/pgsql/9.5/data/base/24602/25140 to my home
directory is instantaneous

System is Centos 7

Thanks





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

Re: Wall shiping replica failed to recover database with error: invalid contrecord length 1956 at FED/38FFE208

[Tom Lane]

Stephen Frost  writes:
> * Aleš Zelený (zeleny.a...@gmail.com) wrote:
>> But recovery on replica failed to proceed WAL file
>> 00010FED0039  with log message: " invalid contrecord length
>> 1956 at FED/38FFE208".

> Err- you've drawn the wrong conclusion from that message (and you're
> certainly not alone- it's a terrible message and we should really have a
> HINT there or something).

Yeah, those messages are all pretty ancient, from when WAL was new and not
to be trusted much.  Perhaps the thing to do is move the existing info
into DETAIL and make the primary message be something like "reached
apparent end of WAL stream".

regards, tom lane

Re: Wall shiping replica failed to recover database with error: invalid contrecord length 1956 at FED/38FFE208

[Stephen Frost]

Greetings,

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Stephen Frost  writes:
> > * Aleš Zelený (zeleny.a...@gmail.com) wrote:
> >> But recovery on replica failed to proceed WAL file
> >> 00010FED0039  with log message: " invalid contrecord length
> >> 1956 at FED/38FFE208".
> 
> > Err- you've drawn the wrong conclusion from that message (and you're
> > certainly not alone- it's a terrible message and we should really have a
> > HINT there or something).
> 
> Yeah, those messages are all pretty ancient, from when WAL was new and not
> to be trusted much.  Perhaps the thing to do is move the existing info
> into DETAIL and make the primary message be something like "reached
> apparent end of WAL stream".

Yes, +1 on that.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Users, Roles and Connection Pooling

[raf]

> On Wed, 2019-10-02 at 08:46 +1000, Matt Andrews wrote:
> > Here’s a question I’ve been asking for a while and just can’t find an
> > answer to, so I thought I’d ask it here. The answer could be
> > subjective, but here goes...
> > 
> > When a web app connects to Postgres via a connection pooler, what is
> > the best way to manage privileges for the connecting user? Especially
> > when their is a complex hierarchy of privileges?
> > 
> > Should each signed up user have their own role which inherits from
> > whichever roles they are members of? This means that the connection
> > pool role should then be granted in every user?
> > 
> > Does creating a Postgres role for every app user scale? Roles can
> > only have names, is an email address a good name for individual app
> > users?
> > 
> > Are their any resources out there that anyone knows of that addresses
> > these questions?
> > 
> > There’s plenty of stuff out there on roles and permissions, I’ve read
> > a lot of it, but not much on dealing with individual app users
> > through a connection pool. 
> > 
> > I’m thinking that any queries should start with SET ROLE ‘username’
> > and end with RESET ROLE. Is this how it could work?
> > 
> > Any help would be greatly appreciated. 

I can't help with questions about scale but I like to give roles/users
almost no permissions at all. i.e. They can't select, insert, update
or delete anything. All they have permission to do is to execute stored
functions that were installed by a role with the necessary permissions
and they are security defining functions so the permissions of the role
that created them apply when the functions are called. This means that
there will never be any successful SQL injection, even if the application
code is buggy, so it's more important for web applications, but I apply
this method to internal systems as well. This approach might help with
scaling because fewer users might be needed but I'm not sure.

cheers,
raf

Re: Users, Roles and Connection Pooling

[Rob Sargent]

On 10/2/19 5:27 PM, raf wrote:



I can't help with questions about scale but I like to give roles/users
almost no permissions at all. i.e. They can't select, insert, update
or delete anything. All they have permission to do is to execute stored
functions that were installed by a role with the necessary permissions
and they are security defining functions so the permissions of the role
that created them apply when the functions are called. This means that
there will never be any successful SQL injection, even if the application
code is buggy, so it's more important for web applications, but I apply
this method to internal systems as well. This approach might help with
scaling because fewer users might be needed but I'm not sure.

cheers,
raf


How easy is it to introduce an new function call all the way up to the 
app user?  Does this approach preclude making use of any query 
generation techniques available?

Re: performance of pg_upgrade "Copying user relation files"

[Adrian Klaver]

On 10/2/19 4:58 PM, Glenn Pierce wrote:
Please reply to list also.
Ccing list.



On Thu, 3 Oct 2019, 00:11 Adrian Klaver, > wrote:


On 10/2/19 3:30 PM, Glenn Pierce wrote:
 > I have been trying to upgrade postgres 9.5 to 9.6 with pg_upgrade
 >
 > Everything seems to start fine but when pg_upgrade gets to "Copying
 > user relation files"
 > It takes ages to complete copying.

This is going to need more information:

1) What is the pg_upgrade command you are using?


I was using pg_upgrade from the installed postgres 9.6

/usr/pgsql-9.6/bin/pg_upgrade --old-bindir=/usr/pgsql-9.5/bin/ 
--new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/var/lib/pgsql/9.5/data/ 
--new-datadir=/var/lib/pgsql/9.6/data/



I was following the article from

https://medium.com › postgresql-upg...
Web results
PostgreSQL upgrade on CentOS - Dzmitry Plashchynski - Medium



2) Where are you upgrading from/to?


Trying to upgrade from 9.5 to 9.6
Same machine with both versions installed.


3) What OS, version?

Centos 7.4


Thanks


 >
 > ie
 > Each file like
 >
 > /var/lib/pgsql/9.5/data/base/24602/25140
 > is 1G and taking ~5-10 minutes to copyand that directory is 223G of
 > about 990 files
 >
 > After a couple of hours running pg_upgrade I had to give up.
 >
 > looking a iotop I was seeing
 >
 > 99% IO load caused by [md0_raid1]
 >
 > and only seeing pg_upgrade go to 99% every 3-4 seconds
 >
 > I had no other processes using io or high cpu.
 >
 > Has anyone got any pointers of what could be the issue ?
 >
 > Ps running cp on /var/lib/pgsql/9.5/data/base/24602/25140 to my home
 > directory is instantaneous
 >
 > System is Centos 7
 >
 > Thanks
 >
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: Urgent :: Postgresql streaming replication issue - sync mode

[Shital A]

On Thu, 3 Oct 2019, 03:10 Jason Wang,  wrote:

> I think when you use kill -9 it wouldn't give any chance for postgres to
> do what it normally does. So in your case, the db was killed with no chance
> to apply to remote then it would be up to the recovery to decide how to
> handle the extra data at the master. I'm not sure what would happen but
> killall in general is a dangerous command.
>
> On Thu, 3 Oct 2019, 7:00 am Shital A,  wrote:
>
>>
>>
>> On Thu, 3 Oct 2019, 00:08 Ravi Krishna,  wrote:
>>
>>> >
>>> > As the failed primary is having more data, How is it possible that
>>> primary is committing transaction before they were applied on standby with
>>> synchronous_commit=remote_apply?
>>>
>>> If I am not mistaken remote_apply is only from ver 11.
>>>
>>
>> Hi Ravi,
>>
>> Thanks for your reply.
>>
>> This property/feature is available in 9.6.
>> https://www.postgresql.org/docs/9.6/runtime-config-wal.html
>>
>>
>> Thanks!
>>
>
Thanks Jason.

Using killall -9 we are trying to simulate the situation where primary is
stopped unexpectedly/crashed.

So in this case there is data loss because when the broken primary later
comes in sync with new primary it copies data from new primary and the data
records that were extra in old primary are lost. Can this data loss be
prevented in anyway in postgres 9.6 ? Please suggest.


Thanks!

>

Re: PG11 Parallel Thanks!!

[Pankaj Jangid]

Jason Ralph  writes:

> I wanted to drop a quick note thanking the developers who have
> contributed to Postgres.  I have recently upgraded our production PG
> instances from pg9.3 to pg11.

>
> We do a lot of table syncs, and we have one process at the end of the
> month that syncs 3 very large tables (400GB).  This sync happens from
> a shell script using pg_dump and pg_restore, we have it set to use -j3
> but it's called sequentially so it never really takes advantage of
> parallel.
>
> Since pg11 on both the target and source, the run time has decreased a
> lot, I chalk it up to the parallel index creations in pg11 which was a
> very time consuming process on pg9.3.
> The process has finished almost 10 hours earlier than pg93.  So thank
> you for your hard work and dedication to this awesome piece of
> software.

This is a great user story. Thanks for sharing your experience, Jason.

-- 
Pankaj Jangid

Re: Users, Roles and Connection Pooling

[raf]

Rob Sargent wrote:

> On 10/2/19 5:27 PM, raf wrote:
> > > 
> > I can't help with questions about scale but I like to give roles/users
> > almost no permissions at all. i.e. They can't select, insert, update
> > or delete anything. All they have permission to do is to execute stored
> > functions that were installed by a role with the necessary permissions
> > and they are security defining functions so the permissions of the role
> > that created them apply when the functions are called. This means that
> > there will never be any successful SQL injection, even if the application
> > code is buggy, so it's more important for web applications, but I apply
> > this method to internal systems as well. This approach might help with
> > scaling because fewer users might be needed but I'm not sure.
> > 
> > cheers,
> > raf
> > 
> How easy is it to introduce an new function call all the way up to the app
> user?

It's easy when you have the right tools to make it
easy: i.e. you write the stored function, then run a
tool to generate the python class for the result set
and the python function that the client applications
can then call to execute the stored function and return
its results.

> Does this approach preclude making use of any query generation
> techniques available?

Yes, it does. I'm happy to write my own plpgsql and
sql. I find that usually results in faster results
(mainly by reducing the temptation to process data
outside the database) as well as being more secure.

I once worked in a job were I couldn't go home until
some program had finished and it was sucking data out
of the database just to summarise it and insert the
summaries. It tooks hours. I replaced it with a stored
procedure that took two minutes and I started going
home much earlier. Rightly or wrongly, that made me
prefer prcessing data inside the database.

cheers,
raf

Re: PG11 Parallel Thanks!!

[Peter Geoghegan]

On Wed, Oct 2, 2019 at 8:41 AM Jason Ralph  wrote:
> Since pg11 on both the target and source, the run time has decreased a lot, I 
> chalk it up to the parallel index creations in pg11 which was a very time 
> consuming process on pg9.3.
> The process has finished almost 10 hours earlier than pg93.  So thank you for 
> your hard work and dedication to this awesome piece of software.

How long did it take on 9.3?

I am the author of the parallel CREATE INDEX feature. It's good to get
feedback like this.

-- 
Peter Geoghegan

psql \copy hanging

[Arnaud L.]

Hi list,

Le 28/08/2019 à 09:43, Luca Ferrari a écrit :

I don't want to be pedantic, but I would have tried with a single change at a 
time.
And my bet is: the local file would do the trick (i.e., it is a weird share 
problem).


Well, this problem is still bugging me, and this time I've tried with a 
local file. Unfortunately, it did not help.
To further rule out filesystem problems, I first took care to delete the 
target files before copying to it, but it did not help either.


So now I'm quite confident that the problem is either psql or even 
postgresql itself.


Does anyone know of anything I could try to try to fix or debug this ?

Thanks a lot for your help!

Regards
--
Arnaud