Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jTKjB-0003AS-Mn for pgsql-admin@arkaria.postgresql.org; Tue, 28 Apr 2020 07:32:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jTKjA-0000BW-7I for pgsql-admin@arkaria.postgresql.org; Tue, 28 Apr 2020 07:32:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jTKj9-0000BI-Sw for pgsql-admin@lists.postgresql.org; Tue, 28 Apr 2020 07:32:23 +0000 Received: from impout004aa.msg.chrl.nc.charter.net ([47.43.20.28] helo=impout004.msg.chrl.nc.charter.net) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jTKj7-0006Zm-RT for pgsql-admin@postgresql.org; Tue, 28 Apr 2020 07:32:23 +0000 Received: from cerberus.crazybean.net ([24.151.69.73]) by cmsmtp with ESMTP id TKj6jKYY2wzF8TKj6joPeP; Tue, 28 Apr 2020 07:32:20 +0000 Authentication-Results: crazybean.net; none X-Authority-Analysis: v=2.3 cv=SdVDicZu c=1 sm=1 tr=0 a=YXTuGtgWN9YX+aIhe8tYuA==:117 a=YXTuGtgWN9YX+aIhe8tYuA==:17 a=xqWC_Br6kY4A:10 a=cl8xLZFz6L8A:10 a=ZlyK2CvnAAAA:8 a=NLRqbuQO-ZYeR54SJU4A:9 a=DuIv_xLPf08v-RlY:21 a=sN1TdgQ4jVPkOxsW:21 a=xV_AHS-d4cQMnME7:21 a=QEXdDO2ut3YA:10 a=hSEdbQNT-5Jyh5DpmnYA:9 a=KTy9RX2U7lNpYiXj:21 a=qJPHXi7T50GmrFfQ:21 a=SIEILR9aqN_9keLl:21 a=_W_S_7VecoQA:10 a=65LznRTJNR0fhtMdHxbG:22 Received: from [IPv6:2001:470:e117:4:60b1:c785:603b:e373] ( [2001:470:e117:4:60b1:c785:603b:e373]) by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id 3b391170 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Tue, 28 Apr 2020 03:32:20 -0400 (EDT) From: Rui DeSousa Message-Id: <33697C7D-04B1-4F93-AA25-D10648DED591@crazybean.net> Content-Type: multipart/alternative; boundary="Apple-Mail=_73656B5D-2C7B-4478-AC91-B158C7378189" Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.80.23.2.2\)) Subject: Re: PostgreSQL DB checkpoint error! Date: Tue, 28 Apr 2020 03:32:19 -0400 In-Reply-To: Cc: "David G. Johnston" , "pgsql-admin@postgresql.org" , "pgsql-admin@lists.postgresql.org" To: Ashok kumar Mani References: <61537141-A530-4AF4-A11D-7722740D4101@crazybean.net> X-Mailer: Apple Mail (2.3608.80.23.2.2) X-CMAE-Envelope: MS4wfBS9pOQ76bCE4hkliY8X7sNsaH5snITZIY5PuJNbURVYUGdp58+pJNhIRk/7CJx1Q+8d7lBXnjE9EKd4PvoHARpy8vfsOG1ij9zngNxcaN0xl/cZGRS9 qHfwnRX79hCSIHdyq4y83XC03hBVN76NIJEOfWX9nJaspjG8tyfI+BfsoTR07iQaeBHob+KCoY/N5F7xCjP9FF9023+++imOYTwhgH15sgvWzg3+vgF7y4bN N78im90QoxTP+hAHNrU2rvez7reLztK20cBhfRMRWumzume5/joi540BsQxj0qEtmqTyvhTr5SDkxgEINpqqZA== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_73656B5D-2C7B-4478-AC91-B158C7378189 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Apr 28, 2020, at 3:06 AM, Ashok kumar Mani = wrote: >=20 > Classification: External > =20 > Dear Rui DeSousa, > =20 > I would like to share the row counts of the big tables in zabbix as = below. > What would you suggest if vacuum full and reindex is not possible = then, is there any way to avoid blocking issue? > =20 I would not use =E2=80=98vacuum full=E2=80=99 for regular maintenance; = only to address a specific issue. Use just vacuum and auto-vacuum.=20 reindex =E2=80=94 that I do concurrently. If you are running PostgreSQL = 12 ; great news! it adds =E2=80=98reindex currently=E2=80=99. If you = are running an early version; then you have to build a new index = currently and drop the old.=20 I only reindex tables that need to be reindexed; not just for the sake = of it. I use the following function that I wrote to create a script = that will reindex the table. The script creates a new index = concurrently and then drops the old. It also does the same for the = primary key by dropping the foreign key constraints as well and rebuilds = them. It does this to minimize blocking contention for applications = that normally run 24/7. =20 To use just execute the function with the table that you would like = reindexed and the result we be a script that you can feed to psql to = actually do the reindex. =20 i.e. select reindex_script(=E2=80=98public=E2=80=99, =E2=80=98mytable=E2=80= =99); I would test it with your tables and application. I haven=E2=80=99t use = this with a partitioned table and I don=E2=80=99t it would work for = those tables.=20 p.s. looking forward to retiring this script when upgrading to version = 12.=20 = /*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D Function: reindex_script() Description: Returns a script to rebuild indexes concurrently = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D*/ create or replace function reindex_script(_schema name, _table name, = _tablespace name default null, _rename boolean default true)=20 returns table (sql text) as $body$ declare _obj_name text; _old_iname text; _new_iname text; _is_pkey boolean; _is_ukey boolean; _sql text; _cnt int; _idx_oid oid; _spath text; begin create temp table rebuild_indexes (pos serial not null primary key, = sql text not null)=20 on commit drop; -- sanitize schema input select current_setting('search_path') , nspname into _spath, _schema from pg_namespace=20 where nspname =3D _schema ; if _schema is null then=20 raise exception 'Unable to identify schema'; end if ;=20 perform set_config('search_path', _schema, true); insert into rebuild_indexes(sql) values ('\set ON_ERROR_STOP on') , (concat('set search_path=3D', quote_ident(_schema), ';')) , ('\timing on') , ('') , (concat('\echo Vacuuming table ', quote_ident(_table), '.')) , (concat('vacuum verbose ', quote_ident(_table), ';')) ; for _idx_oid, _obj_name, _old_iname, _new_iname, _is_pkey, _is_ukey, = _sql in select i.indexrelid=20 , quote_ident(ns.nspname)=20 || '.' || quote_ident(c.relname) as objname , quote_ident((i.indexrelid::regclass)::text) as old_iname , quote_ident( c.relname=20 || case=20 when substring((i.indexrelid::regclass)::text from = '(_idx|_pkey|_ukey)\d{0,3}$') in ('_idx', '_pkey', '_ukey') then=20 case=20 when i.indisprimary then '_pk' when con.conindid is not null then '_uk' else '_ix' end else=20 case=20 when i.indisprimary then '_pkey' when con.conindid is not null then '_ukey' else '_idx' end end || case i.indisprimary=20 when true then '' else (row_number() over (partition by con.conindid is = not null order by substring((i.indexrelid::regclass)::text from = '(\d+)$')))::text end=20 ) as new_iname , i.indisprimary as is_pkey , con.conindid is not null as is_ukey=20= , regexp_replace( lower(pg_get_indexdef(i.indexrelid)) , '( where |$)' , coalesce(' tablespace ' || _tablespace, ' tablespace ' || = its.spcname, '') || case when i.indpred is null then '' else ' where ' end ) as csql from pg_namespace ns join pg_class c on c.relnamespace =3D ns.oid and c.relkind =3D 'r' and c.relname =3D _table join pg_index i on i.indrelid =3D c.oid join pg_class ic on ic.oid =3D i.indexrelid left join pg_tablespace its on its.oid =3D ic.reltablespace left join pg_constraint con on con.conindid =3D i.indexrelid and con.conrelid =3D c.oid and con.contype =3D 'u' where ns.nspname =3D _schema order by i.indisprimary , substring((i.indexrelid::regclass)::text from '(\d+)$') loop insert into rebuild_indexes(sql) values ('') , ('\echo Creating ' || _new_iname || ' on ' || _obj_name || = '.' ) , (regexp_replace(_sql, 'index (.+) on (.+) using', 'index = concurrently ' || _new_iname || ' on ' || _obj_name || ' using') || ';') ;=20 if _is_pkey then insert into rebuild_indexes(sql) values ('')=20 , ('\echo Swapping primary key ' || _old_iname || ' with '|| = _new_iname || ' on ' || _obj_name || '.' ) , ('begin;') , ('--Drop foreign keys to allow primary key swap') ; insert into rebuild_indexes(sql) select concat('alter table ', = quote_ident((fk.conrelid::regclass)::text), ' drop constraint ', = quote_ident(fk.conname), ';') from pg_constraint fk where fk.conindid =3D _idx_oid and fk.contype =3D 'f' ; insert into rebuild_indexes(sql) values ('') , ('alter table ' || _obj_name || ' drop constraint ' || = _old_iname || ';') , ('alter table ' || _obj_name || ' add primary key using = index ' || _new_iname || ';') , ('') , ('--Rresintate foreign keys with "not valid" option; = contraint is enforced just not validated') ; insert into rebuild_indexes(sql) select concat('alter table ', = quote_ident((fk.conrelid::regclass)::text), ' add constraint ', = quote_ident(fk.conname), ' ', lower(pg_get_constraintdef(fk.oid)), ' not = valid;') from pg_constraint fk where fk.conindid =3D _idx_oid and fk.contype =3D 'f' ; insert into rebuild_indexes(sql) values ('commit;') , ('') , ('\echo Revalidating foreign key constraints with existing = records') ; insert into rebuild_indexes(sql) select concat('alter table ', = quote_ident((fk.conrelid::regclass)::text), ' validate constraint ', = quote_ident(fk.conname), ';') from pg_constraint fk where fk.conindid =3D _idx_oid and fk.contype =3D 'f' ; elsif _is_ukey then insert into rebuild_indexes(sql) values ('') , ('\echo Swapping constraint ' || _old_iname || ' with '|| = _new_iname || ' on ' || _obj_name || '.' ) , ('begin;') , ('alter table ' || _obj_name || ' drop constraint ' || = _old_iname || ';') , ('alter table ' || _obj_name || ' add unique using index ' = || _new_iname || ';') , ('commit;') ; else=20 insert into rebuild_indexes(sql) values ('')=20 , ('\echo Dropping index ' || _old_iname || '.' ) , ('drop index concurrently ' || _old_iname || ';') ; =20 end if; if _rename then insert into rebuild_indexes(sql) values ('')=20 , ('\echo Renaming index ' || _new_iname || ' to ' || = _old_iname || '.' ) , ('alter index ' || _new_iname || ' rename to ' || _old_iname = || ';') ; end if ; end loop ; -- Reset search path incase of extended tranacation=20 perform set_config('search_path', _spath, true); return query select x.sql from rebuild_indexes x order by x.pos ; end; $body$ language plpgsql ; --Apple-Mail=_73656B5D-2C7B-4478-AC91-B158C7378189 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On Apr 28, 2020, at 3:06 AM, Ashok kumar Mani <amani@accelaero.com>= wrote:

Classification: = External
 
Dear = Rui DeSousa,
 
I would like to share the = row counts of the big tables in zabbix as below.
What would you suggest if vacuum = full and reindex is not possible then, is there any way to avoid = blocking issue?
 

I would not use =E2=80=98vacuum full=E2=80=99 = for regular maintenance; only to address a specific issue.  Use = just vacuum and auto-vacuum. 

reindex =E2=80=94 that I do = concurrently.  If you are running PostgreSQL 12 ; great news! it = adds =E2=80=98reindex currently=E2=80=99.   If you are running an = early version; then you have to build a new index currently and drop the = old. 

I = only reindex tables that need to be reindexed; not just for the sake of = it.  I use the following function that I wrote to create a script = that will reindex the table.  The script creates a new index = concurrently and then drops the old.  It also does the same for the = primary key by dropping the foreign key constraints as well and rebuilds = them.  It does this to minimize blocking contention for = applications that normally run 24/7.  

To use just execute the function with = the table that you would like reindexed and the result we be a script = that you can feed to psql to actually do the reindex.  

i.e. =  select reindex_script(=E2=80=98public=E2=80=99, =E2=80=98mytable=E2= =80=99);

I would test it with your tables and application.  I = haven=E2=80=99t use this with a partitioned table and I don=E2=80=99t it = would work for those tables. 

p.s. looking forward to retiring this = script when upgrading to version 12. 


/*=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D
   Function: = reindex_script()
   Description: Returns = a script to rebuild indexes concurrently
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D*/
create or replace function = reindex_script(_schema name, _table name, _tablespace name default null, = _rename boolean default true) 
  returns = table (sql text)
as $body$
declare
  _obj_name text;
  _old_iname text;
  = _new_iname text;
  _is_pkey boolean;
  _is_ukey boolean;
  _sql = text;
  _cnt int;
  = _idx_oid oid;
  _spath text;
begin
  create temp table = rebuild_indexes (pos serial not null primary key, sql text not = null) 
      on commit = drop;

  = -- sanitize schema input
  select = current_setting('search_path')
    , = nspname
    into _spath, = _schema
  from pg_namespace 
  where nspname =3D _schema
  = ;

  if = _schema is null then 
    raise = exception 'Unable to identify schema';
  end = if
  ; 

  perform = set_config('search_path', _schema, true);

  insert into = rebuild_indexes(sql)
    values ('\set = ON_ERROR_STOP on')
      , = (concat('set search_path=3D', quote_ident(_schema), ';'))
      , ('\timing on')
      , ('')
  =     , (concat('\echo Vacuuming table ', quote_ident(_table), = '.'))
      , (concat('vacuum = verbose ', quote_ident(_table), ';'))
  = ;

  for = _idx_oid, _obj_name, _old_iname, _new_iname, _is_pkey, _is_ukey, _sql = in
    select = i.indexrelid 
      , = quote_ident(ns.nspname) 
      =   || '.' || quote_ident(c.relname)         =       as objname
    =   , quote_ident((i.indexrelid::regclass)::text)    as = old_iname
      , = quote_ident(
          =   c.relname 
        =     || case 
      =            when = substring((i.indexrelid::regclass)::text from = '(_idx|_pkey|_ukey)\d{0,3}$') in ('_idx', '_pkey', '_ukey')
                =    then 
        =              case 
                =        when i.indisprimary then '_pk'
                =        when con.conindid is not null then = '_uk'
            =            else '_ix'
                =      end
        =          else 
  =                 =  case 
          =            when i.indisprimary then = '_pkey'
            =          when con.conindid is not null then = '_ukey'
            =          else '_idx'
  =                 =  end
            =    end
          =   || case i.indisprimary 
    =              when true then = ''
              =    else (row_number() over (partition by con.conindid is not = null order by substring((i.indexrelid::regclass)::text from = '(\d+)$')))::text
        =        end 
    =     )                 =                     =               as new_iname
      , i.indisprimary       =                     =            as is_pkey
      , con.conindid is not null     =                     =    as is_ukey 
      = , regexp_replace(
        =   lower(pg_get_indexdef(i.indexrelid))
  =         , '( where |$)'
  =         , coalesce(' tablespace ' || _tablespace, ' = tablespace ' || its.spcname, '')
    =          || case
  =                 when i.indpred = is null then ''
          =         else ' where '
  =               end
        )         =                     =                     =   as csql
    from pg_namespace = ns
    join pg_class c on c.relnamespace = =3D ns.oid
      and c.relkind =3D = 'r'
      and c.relname =3D = _table
    join pg_index i on i.indrelid = =3D c.oid
    join pg_class ic on ic.oid = =3D i.indexrelid
    left join = pg_tablespace its on its.oid =3D ic.reltablespace
    left join pg_constraint con on con.conindid =3D = i.indexrelid
      and con.conrelid = =3D c.oid
      and con.contype =3D = 'u'
    where ns.nspname =3D = _schema
    order by = i.indisprimary
      , = substring((i.indexrelid::regclass)::text from '(\d+)$')
  loop
    insert into = rebuild_indexes(sql)
      values = ('')
        , ('\echo Creating = '  || _new_iname || ' on ' || _obj_name || '.' )
        , (regexp_replace(_sql, 'index = (.+) on (.+) using', 'index concurrently ' || _new_iname || ' on ' || = _obj_name || ' using') || ';')
    = ; 

  =   if _is_pkey then
      insert = into rebuild_indexes(sql)
      =   values ('') 
      =     , ('\echo Swapping primary key '  || _old_iname || ' = with '|| _new_iname || ' on ' || _obj_name || '.' )
          , ('begin;')
          , ('--Drop foreign keys to = allow primary key swap')
      = ;

  =     insert into rebuild_indexes(sql)
 =     select concat('alter table ', = quote_ident((fk.conrelid::regclass)::text), ' drop constraint ', = quote_ident(fk.conname), ';')
      = from pg_constraint fk
      where = fk.conindid =3D _idx_oid
      =   and fk.contype =3D 'f'
      = ;

  =     insert into rebuild_indexes(sql)
 =       values ('')
    =       , ('alter table ' || _obj_name || ' drop constraint = ' || _old_iname || ';')
        =   , ('alter table ' || _obj_name || ' add primary key using index ' = || _new_iname || ';')
        =   , ('')
          , = ('--Rresintate foreign keys with "not valid" option; contraint is = enforced just not validated')
      = ;

  =     insert into rebuild_indexes(sql)
 =     select concat('alter table ', = quote_ident((fk.conrelid::regclass)::text), ' add constraint ', =  quote_ident(fk.conname), ' ', lower(pg_get_constraintdef(fk.oid)), = ' not valid;')
      from = pg_constraint fk
      where = fk.conindid =3D _idx_oid
      =   and fk.contype =3D 'f'
      = ;

  =     insert into rebuild_indexes(sql)
 =       values ('commit;')
  =         , ('')
    =       , ('\echo Revalidating foreign key constraints with = existing records')
      ;

      = insert into rebuild_indexes(sql)
    =   select concat('alter table ', = quote_ident((fk.conrelid::regclass)::text), ' validate constraint ', = quote_ident(fk.conname), ';')
      = from pg_constraint fk
      where = fk.conindid =3D _idx_oid
      =   and fk.contype =3D 'f'
      = ;
    elsif _is_ukey then
      insert into = rebuild_indexes(sql)
        = values ('')
          , = ('\echo Swapping constraint '  || _old_iname || ' with '|| = _new_iname || ' on ' || _obj_name || '.' )
  =         , ('begin;')
  =         , ('alter table ' || _obj_name || ' drop = constraint ' || _old_iname || ';')
    =       , ('alter table ' || _obj_name || ' add unique = using index ' || _new_iname || ';')
    =       , ('commit;')
    =   ;
    else 
      insert into = rebuild_indexes(sql)
        = values ('') 
        =   , ('\echo Dropping index ' || _old_iname  || '.' )
          , ('drop index = concurrently ' || _old_iname || ';')
    =   ;      
    end = if;

  =   if _rename then
      insert = into rebuild_indexes(sql)
      =   values ('') 
      =     , ('\echo Renaming index '  || _new_iname || ' to ' = || _old_iname  || '.' )
      =     , ('alter index ' || _new_iname || ' rename to ' || = _old_iname || ';')
      ;
    end if
    = ;

  end = loop
  ;

  -- Reset search path incase of = extended tranacation 
  perform = set_config('search_path', _spath, true);

  return query
    select x.sql
    = from rebuild_indexes x
    order by = x.pos
  ;

end;
$body$ = language plpgsql
;

= --Apple-Mail=_73656B5D-2C7B-4478-AC91-B158C7378189--