Re: Change in db size

On Wed, Jul 17, 2019 at 7:33 PM Sonam Sharma wrote: > > I took the backup using pg_dump with gzip option and restored it with psql. It does not change the way you backed up, chances are when you restored it the database cropped table and index bloating as already mentioned. The other, remote chan

pg_stat_progress_vacuum comes up empty ...?

Hello, We have a database cluster which recently got very close to XID Wraparound. To get it back under control I've been running a lot of aggressive manual vacuums. However, I have noticed a few anomolies. When I try to check the status of vacuum commands: qtodb_pmxtr=# select * from pg_stat

Re: Corrupt index stopping autovacuum system wide

Hi, On 2019-07-17 13:27:23 -0400, Tom Lane wrote: > My concern here is that if we have blinders on to the extent of only > processing that one table or DB, we're unnecessarily allowing bloat to > occur in other tables, and causing that missed vacuuming work to pile > up so that there's more of it

Re: Corrupt index stopping autovacuum system wide

On Wed, Jul 17, 2019 at 10:27 AM Peter Geoghegan wrote: > > It's possible that amcheck would have given you an accurate diagnosis > > of the problem -- especially if you used bt_index_parent_check(): > > > > https://www.postgresql.org/docs/current/amcheck.html > > BTW, be sure to use the 'heapalli

Re: Corrupt index stopping autovacuum system wide

On Wed, Jul 17, 2019 at 11:43 AM Alvaro Herrera wrote: > This might make things worse operationally, though. If searches aren't > failing but vacuum is, we'd break a production system that currently > works. If searches aren't failing and VACUUM works, then that's probably down to dumb luck. The

Resolved: Looking for Postgres upgrade Metrix

Ok, thanks for the clarification. On Wed, Jul 17, 2019 at 11:46 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Jul 17, 2019 at 11:39 AM Perumal Raj wrote: > >> Thanks Adrian, David, >> >> Basically , i want to upgrade few 9.X/8.X version DBs to some stable >> version ( 10.X

Re: Looking for Postgres upgrade Metrix

On Wed, Jul 17, 2019 at 11:39 AM Perumal Raj wrote: > Thanks Adrian, David, > > Basically , i want to upgrade few 9.X/8.X version DBs to some stable > version ( 10.X / 11.X ), At the same time with less down time. > So want to understand whether direct upgrade possible or not between major > rel

Re: Corrupt index stopping autovacuum system wide

On 2019-Jul-17, Tom Lane wrote: > Alvaro Herrera writes: > > On 2019-Jul-17, Peter Geoghegan wrote: > >> Maybe nbtree VACUUM should do something more aggressive than give up > >> when there is a "failed to re-find parent key" or similar condition. > >> Perhaps it would make more sense to make the

Re: Looking for Postgres upgrade Metrix

Thanks Adrian, David, Basically , i want to upgrade few 9.X/8.X version DBs to some stable version ( 10.X / 11.X ), At the same time with less down time. So want to understand whether direct upgrade possible or not between major releases . Thanks, On Wed, Jul 17, 2019 at 11:24 AM Adrian Klaver

Re: Corrupt index stopping autovacuum system wide

Alvaro Herrera writes: > On 2019-Jul-17, Peter Geoghegan wrote: >> Maybe nbtree VACUUM should do something more aggressive than give up >> when there is a "failed to re-find parent key" or similar condition. >> Perhaps it would make more sense to make the index inactive (for some >> value of "inac

Re: Looking for Postgres upgrade Metrix

On 7/17/19 11:15 AM, Perumal Raj wrote: Hi Team, Do we have any reference link which explain various  upgrade path ( Direct / indirect) by using pg_upgrade or latest utility. https://www.postgresql.org/docs/11/backup.html What exactly are you trying to do? I hope pg_dump can be used from

Re: Looking for Postgres upgrade Metrix

On Wed, Jul 17, 2019 at 11:16 AM Perumal Raj wrote: > Hi Team, > > Do we have any reference link which explain various upgrade path ( Direct > / indirect) by using pg_upgrade or latest utility. > > I hope pg_dump can be used from any lower version to Higher version. > Please correct me if I am w

Re: Corrupt index stopping autovacuum system wide

On 2019-Jul-17, Peter Geoghegan wrote: > Maybe nbtree VACUUM should do something more aggressive than give up > when there is a "failed to re-find parent key" or similar condition. > Perhaps it would make more sense to make the index inactive (for some > value of "inactive") instead of just compla

Looking for Postgres upgrade Metrix

Hi Team, Do we have any reference link which explain various upgrade path ( Direct / indirect) by using pg_upgrade or latest utility. I hope pg_dump can be used from any lower version to Higher version. Please correct me if I am wrong. Thanks, Raj

Re: Corrupt index stopping autovacuum system wide

On Wed, Jul 17, 2019 at 10:27 AM Tom Lane wrote: > Right, you're eventually going to get to a forced shutdown if vacuum never > succeeds on one table; no question that that's bad. It occurs to me that we use operator class/insertion scankey comparisons within page deletion, to relocate a leaf pag

Re: Why no CREATE TEMP MATERIALIZED VIEW ?

On 2019-Jul-16, Ivan Voras wrote: > Hello, > > Out of curiosity, since there's CREATE TEMP VIEW, any particular reason > there's no CREATE TEMP MATERIALIZED VIEW? Because it hasn't been implemented. There is a patch, but it's not done. See this thread: https://postgr.es/m/CAKLmikNoQR4ZNg_wt=-h

Re: disable and enable trigger all when a foreign keys

On 2019-Jul-12, Tom Lane wrote: > The OP already found the best method to get out of this, which is to > drop and re-add the FK constraint. Adding the constraint will force > a full verification scan, which is what you need here since there's > no information available about which checks were mis

Re: Change in db size

I took the backup using pg_dump with gzip option and restored it with psql. On Wed, Jul 17, 2019, 10:39 PM David G. Johnston wrote: > On Wed, Jul 17, 2019 at 10:03 AM Sonam Sharma > wrote: > >> I have restored database and the db size of source was around 55gb and >> after restore the db size o

Re: Corrupt index stopping autovacuum system wide

Peter Geoghegan writes: > On Wed, Jul 17, 2019 at 9:57 AM Tom Lane wrote: >> It looks that way, but how would a broken non-shared index have held up >> autovacuuming in other databases? Maybe, as this one's xmin horizon >> got further and further behind, the launcher eventually stopped >> consid

Re: Corrupt index stopping autovacuum system wide

On Wed, Jul 17, 2019 at 9:21 AM Peter Geoghegan wrote: > It's possible that amcheck would have given you an accurate diagnosis > of the problem -- especially if you used bt_index_parent_check(): > > https://www.postgresql.org/docs/current/amcheck.html BTW, be sure to use the 'heapallindexed' opti

Re: Corrupt index stopping autovacuum system wide

On Wed, Jul 17, 2019 at 9:57 AM Tom Lane wrote: > It looks that way, but how would a broken non-shared index have held up > autovacuuming in other databases? Maybe, as this one's xmin horizon > got further and further behind, the launcher eventually stopped > considering launching workers into an

Re: [External] Change in db size

I guess the restore cleared the bloat from the table. \dt+ \di+ If you run the above commands from the terminal, you would see diff in sizes of the. objects. Also querying pg_stat_all_tables you might see “dead” tuples in old tables which would have cleaned up now. Also if there were any large tem

Re: Change in db size

On Wed, Jul 17, 2019 at 10:03 AM Sonam Sharma wrote: > I have restored database and the db size of source was around 55gb and > after restore the db size of Target is 47gb. > You haven't described how you restored the database but measuring size doesn't work because typically restoring a table c

Change in db size

I have restored database and the db size of source was around 55gb and after restore the db size of Target is 47gb. How to confirm if restore was successful or not ? The table count is also same.

Re: Corrupt index stopping autovacuum system wide

Peter Geoghegan writes: > On Wed, Jul 17, 2019 at 8:54 AM Aaron Pelz wrote: >> To me it looks like a *single* corrupt index held up autovacuums across our >> entire server, even other in other databases on the same server. Am I >> interpreting this correctly? > Yes -- that is correct. It look

Re: Corrupt index stopping autovacuum system wide

> What PostgreSQL version are you on? Was this an INCLUDE index on PostgreSQL 11? On 11, and no it was just a normal btree. > It's possible that amcheck would have given you an accurate diagnosis > of the problem -- especially if you used bt_index_parent_check(): I'll look into this, seems helpf

Re: Corrupt index stopping autovacuum system wide

On Wed, Jul 17, 2019 at 8:54 AM Aaron Pelz wrote: > To me it looks like a *single* corrupt index held up autovacuums across our > entire server, even other in other databases on the same server. Am I > interpreting this correctly? Yes -- that is correct. What PostgreSQL version are you on? Was

Corrupt index stopping autovacuum system wide

Hello, Over the weekend we noticed that our max transaction IDs have been continuously increasing - got an alert it passed 1B - and we believe that no autovacuums were running for a period of about a month by looking at pg_stat_user_tables. We had not updated any autovac tuning parameters over tha

Re: Removing a key from jsonb is sloooow

Volkan Unsal writes: > I'm trying to remove a key from a jsonb column in a table with 10K rows, > and the performance is abysmal. When the key is missing, it takes 5 > minutes. When the key is present, it takes even longer. How wide are the jsonb values? It seems likely that most of this is TOAS

Re: Removing a key from jsonb is sloooow

On 7/17/19 7:59 AM, Volkan Unsal wrote: Aha, it's due to the trigger, isn't it? Yes. On Wed, Jul 17, 2019 at 10:58 AM Volkan Unsal > wrote: @Adrian More information about my setup: Postgres version: PostgreSQL 10.9 (Debian 10.9-1.pgdg90+1) o

Re: Removing a key from jsonb is sloooow

More information about my setup: Postgres version: PostgreSQL 10.9 (Debian 10.9-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit Table schema: CREATE TABLE public.projects ( misc jsonb DEFAULT '{}'::jsonb NOT NULL ); Explain analyze: explain

Re: Removing a key from jsonb is sloooow

On 7/17/19 7:58 AM, Volkan Unsal wrote: Please post to list also. Ccing list @Adrian More information about my setup: Postgres version: PostgreSQL 10.9 (Debian 10.9-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit Table schema: CREATE TABLE

Re: Removing a key from jsonb is sloooow

Hi, Your current query actually process your 10K rows, it is a bit of an overkill if only a few row contains the key you want to delete. Depending on how big your json data is, this could be problematic. Have you considered adding a where clause to your query ? Also, maybe you could create an in

Re: Removing a key from jsonb is sloooow

On 7/17/19 7:30 AM, Volkan Unsal wrote: I'm trying to remove a key from a jsonb column in a table with 10K rows, and the performance is abysmal. When the key is missing, it takes 5 minutes. When the key is present, it takes even longer. Test with non-existent key: >> update projects set misc

Removing a key from jsonb is sloooow

I'm trying to remove a key from a jsonb column in a table with 10K rows, and the performance is abysmal. When the key is missing, it takes 5 minutes. When the key is present, it takes even longer. Test with non-existent key: >> update projects set misc = misc - 'foo'; Time: 324711.960 ms (05:24.7

Re: How to run a task continuously in the background

On 17/07/19, Luca Ferrari (fluca1...@gmail.com) wrote: > On Wed, Jul 17, 2019 at 9:38 AM Dirk Mika wrote: > > That is basically still my main question. How do I start a > > background job (e.g. a function) which waits by polling or LISTEN / > > NOTIFY for records in a table to be processed. > > Y

Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?

Hi Luca, Yes, that's the answer,It really works! Thanks again Luca, you actually saved my day! James. -- Original -- From: "Luca Ferrari"; Date: Wed, Jul 17, 2019 06:49 PM To: "James(王旭)"; Cc: "pgsql-general"; Subject: Re: Issue related with patitioned tab

Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?

On Wed, Jul 17, 2019 at 11:41 AM James(王旭) wrote: > From these results I can tell the route to a table is not even related with > the mod function, right? > So It's hard for me to do any kind of guesses... Because it is the wrong function. According to \d+ on a child table and partbounds.c the f

Re: How to run a task continuously in the background

We used a python process running continually on a linux client for the LISTEN piece. Reading the documentation, it seems that a background worker ( https://www.postgresql.org/docs/11/bgworker.html) might be a solution to your requirements. I don’t have personal experience with them. -- Gerard W

Re: Matview size - space increased on concurrently refresh

Il giorno dom 14 lug 2019 alle ore 22:23 Tom Lane ha scritto: > [ please do not top-post in your replies, it makes the conversation hard > to follow ] > > Nicola Contu writes: > > Il dom 14 lug 2019, 21:34 Kaixi Luo ha scritto: > >> This is normal and something to be expected. When refreshing

Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?

Here's my PG version: PostgreSQL 11.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit Hope this helps. -- Original -- From: "James(王旭)"; Date: Wed, Jul 17, 2019 05:36 PM To: "Luca Ferrari"; Cc: "pgsql-general"; Subj

Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?

Thanks you Luca. Yes my intention was " SELECT (hashint2(6365::SMALLINT)% 3) ", that's my mistake, thank you for pointing out. Actually I just did a simple query in my single table: SELECT distinct(symbol_id) FROM _0 and I got these results: "symbol_id" 6521 1478 1964 5642 7470 115

Re: Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?

On Wed, Jul 17, 2019 at 9:19 AM 王旭 wrote: > I tried something like this: e.g., for symbol_id 6365, > SELECT (hashint2(6365::SMALLINT)% 10) shouldn't this be modulus 3 instead of 10? The problem is that record 6365 is not where you expected to be? As far as I know, there is no easy user-level wa

Re: How to run a task continuously in the background

On Wed, Jul 17, 2019 at 9:38 AM Dirk Mika wrote: > That is basically still my main question. How do I start a background job > (e.g. a function) which waits by polling or LISTEN / NOTIFY for records in a > table to be processed. You will have a trigger that, once new tuples are created (or old

Re: How to run a task continuously in the background

> On Jul 17, 2019, at 1:26 AM, Dirk Mika wrote: > > > We used a trigger that called pg_notify > (https://www.postgresql.org/docs/9.5/sql-notify.html > ​) and then had another > > process that LISTENed for notifications. > > > > What

Issue related with patitioned table:How can I quickly determine which child table my record is in,given a specific primary key value?

Hello! My table is described as below: CREATE TABLE IF NOT EXISTS mytable ( uuid varchar(45) NOT NULL, symbol_idsmallint NOT NULL, ... ... PRIMARY KEY (symbol_id,uuid) ) partition by hash(symbol_id) create table mytable_0 partition of 0 FOR VALUES W