Need a script to drop larger indexes and recreate those indexes once the environment is up with the backup in postgresql

Question

I am looking for the help to minimise the time taken by the pg_basebackup utility.

We are taking the backup of the database using pg_basbackup utility using below command.

$PGHOME/bin/pg_basebackup -p 5433 -U postgres -P -v -x --format=tar --gzip --compress=6 --pgdata=- -D /opt/backup_db

pg_basebackup is not depend on any of the postgresql configuration parameters. If I go for gzip format we need to compromise on time.

We are planning to take backup by following below steps to minimise the database backup time. Please correct me if I am wrong.

1)Identify the larger indexes(whose size is above 256MB) and drop those indexes. Due to this size of the database will reduce. 2)Take the backup of the database.By using this backup create a new environment. 3)Recreate the indexes on the environment where we created the environment which we created using the backup.

Note:-We will create a new environment using backup. On that environment we apply few ddls and dml and make that as live database due to our product migration.

I am new to postgres database. Could you help me to construct the query to drop and create the indexes, please?

Answer 1

The necessary informations are in view pg_indexes:

I wrote some scripts, that can be used for index maintenance. Small indexes can be usually directly reindexed. There is a write lock, but it doesn't need too much time. If lock is not possible, then this script cannot be used:

psql -At -c "select 'REINDEX INDEX' || c.relname from pg_class c where relkind = 'i' and pg_table_size(c.oid) < 1024*1024*1024;" mydb | psql -S mydb

The query:

SELECT c.relname
   FROM pg_class
  WHERE relkind = 'i'
    AND pg_table_size(c.oid) < 1024*1024*1024;

selects indexes less than 1GB. In script I create a REINDEX INDEX commands for these indexes.

With bigger index is usually much more work. REINDEX INDEX is not possible due too long lock times. CREATE INDEX CONCURRENTLY command is solution. But there is necessary much more magic for constraint indexes. It is possible do atomically:

alter table T1
  drop constraint T1_pkey,
  add constraint T1_pkey PRIMARY KEY USING INDEX T1_xx_pk2; 

Some years ago I wrote script, that does concurrent reindexation on selected table:

create table commands(cmd text);

do $$
declare 
  r record;
  newname text;
begin
  for r in 
    SELECT c2.relname as indexname, i.indisprimary,
           pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as indexdef,
           pg_catalog.pg_get_constraintdef(con.oid, true),
           c.oid::regclass as tablename
      FROM pg_catalog.pg_class c,
           pg_catalog.pg_class c2, pg_catalog.pg_index i
           LEFT JOIN pg_catalog.pg_constraint con 
                     ON (conrelid = i.indrelid 
                       AND conindid = i.indexrelid
                       AND contype IN ('p','u','x'))
     WHERE c.oid = 'mytable'::regclass
       AND c.oid = i.indrelid AND i.indexrelid = c2.oid
  loop
    newname = 'fresh_' || md5(r.indexname || r.tablename);
    -- inject CONCURRENTLY keyword
    insert into commands
      values(replace(replace(r.indexdef, 'INDEX', 'INDEX CONCURRENTLY'), r.indexname, newname) || ';');
    if r.indisprimary then
      insert into commands
        values(format('ALTER TABLE %I DROP CONSTRAINT %I, ADD CONSTRAINT %I PRIMARY KEY USING INDEX %I;',
            r.tablename, r.indexname, r.indexname, newname));
    else
      insert into commands
        values(format('BEGIN;DROP INDEX %I;', r.indexname) || format('ALTER INDEX %I RENAME TO %I; COMMIT;', newname, r.indexname));
    end if;
    raise notice 'refreshed index: %', r.indexname;
  end loop;
end;
$$ language plpgsql;

The all necessary commands are stored in table commands

just few notes:

1. usually you should be pretty careful about dropping indexes - some applications can stop work

2. when the indexes of 256MB are problem (very small indexes), then probably you can use pg_dump for backup. pg_dump does backup only of index definition. It doesn't backup index content - maybe it is better solution for you instead pg_basebackup.

3. postgresql-9.2. is unsupported version of PostgreSQL. You should to do upgrade.