psql \copy hanging

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 wi

Re: PG11 Parallel Thanks!!

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

Re: Users, Roles and Connection Pooling

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 t

Re: PG11 Parallel Thanks!!

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 ta

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

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 a

Re: performance of pg_upgrade "Copying user relation files"

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

Re: Users, Roles and Connection Pooling

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 n

Re: Users, Roles and Connection Pooling

> 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,

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

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- y

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

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 >

Re: performance of pg_upgrade "Copying user relation files"

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 us

Re: performance of pg_upgrade "Copying user relation files"

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_upgra

Re: Questions about Partitioned Tables and Indexes

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

performance of pg_upgrade "Copying user relation files"

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

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

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-

Re: Performance on JSONB select

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

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

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

Re: Performance on JSONB select

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 inclu

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

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, co

Re: partitions vs indexes

"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_

Re: partitions vs indexes

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

partitions vs indexes

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: CR

Re: Query Tuning

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/ne

Re: Drop a primary

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=#

Drop a primary

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: Users, Roles and Connection Pooling

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 als

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

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

Urgent :: Postgresql streaming replication issue - sync mode

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 h

Re: Performance on JSONB select

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 ju

PG11 Parallel Thanks!!

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 happen

Performance on JSONB select

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'='naoatribui

Questions about Partitioned Tables and Indexes

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 par

A post describing PostgreSQL 12 Generated Columns

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

Re: Users, Roles and Connection Pooling

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 shou

Re: Users, Roles and Connection Pooling

> 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 us

Re: Users, Roles and Connection Pooling

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 limite

Re: Users, Roles and Connection Pooling

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.

Re: Users, Roles and Connection Pooling

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 > th