'1. IT Story/Scripts'에 해당되는 글 75건

1.PostgreSQL Export

 1-1. META ONLY

   - pg_dump -U TEST -E UTF8 -s -v -f /pg_backup/TESTDB_schema_`date "+%Y%m%d%H%M%S"`.dmp  TESTDB 2> /pg_backup/TESTDB_schema_`date "+%Y%m%d%H%M%S"`.log

 1-2. DATA ONLY

   - pg_dump -U TEST -E UTF8 -a -v -f /pg_backup/TESTDB_dataonly_`date "+%Y%m%d%H%M%S"`.dmp  TESTDB 2> /pg_backup/TESTDB_dataonly_`date "+%Y%m%d%H%M%S"`.log

 1-3. 주요사용 옵션(대소문자 주의) 

1. -U username

2. -d dbname

3. -W password / -w no password

4. -a data only

5. -E encoding

6. -f file

7. -s schema only

8. -t table : include table

9. -T talbe : exclude table

10. --help

2. DB 및 계정 RENAME

- TEST_201903.dmp 파일 안에 TEST 단어를 TESTDB로 치환

- find . -name "TESTDB_dataonly_201903.dmp" -exec perl -pi -e 's/TEST/TESTDB/g' {} \;

- find . -name "TESTDB_schema_201903.dmp" -exec perl -pi -e 's/TEST/TESTDB/g' {} \;

3. PostgreSQL Import

psql -d TESTDB -U TESTDB -f /pg_backup/TESTDB_schema_201903.dmp

psql -d TESTDB -U TESTDB -f /pg_backup/TESTDB_dataonly_201903.dmp

상세 옵션: https://www.postgresql.org/docs/10/app-pgdump.html

SELECT n.nspname as "Schema",

  c.relname as "Name",

  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' END as "Type",

  pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",

  pg_catalog.array_to_string(ARRAY(

    SELECT attname || E':\n  ' || pg_catalog.array_to_string(attacl, E'\n  ')

    FROM pg_catalog.pg_attribute a

    WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL

  ), E'\n') AS "Column privileges",

  pg_catalog.array_to_string(ARRAY(

    SELECT polname

    || CASE WHEN polcmd != '*' THEN

           E' (' || polcmd || E'):'

       ELSE E':' 

       END

    || CASE WHEN polqual IS NOT NULL THEN

           E'\n  (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)

       ELSE E''

       END

    || CASE WHEN polwithcheck IS NOT NULL THEN

           E'\n  (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)

       ELSE E''

       END    || CASE WHEN polroles <> '{0}' THEN

           E'\n  to: ' || pg_catalog.array_to_string(

               ARRAY(

                   SELECT rolname

                   FROM pg_catalog.pg_roles

                   WHERE oid = ANY (polroles)

                   ORDER BY 1

               ), E', ')

       ELSE E''

       END

    FROM pg_catalog.pg_policy pol

    WHERE polrelid = c.oid), E'\n')

    AS "Policies"

FROM pg_catalog.pg_class c

     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')

  AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)

ORDER BY 1, 2;

- Vacuum threshold(autovacuum 임계값) = vacuum base threshold + vacuum scale factor * number of tuples

WITH avt AS (

    SELECT 

            setting AS autovacuum_vacuum_threshold 

    FROM    pg_settings 

    WHERE   name = 'autovacuum_vacuum_threshold'

)

, vsf AS (

    SELECT  setting AS autovacuum_vacuum_scale_factor 

    FROM    pg_settings 

    WHERE   name = 'autovacuum_vacuum_scale_factor'

)

, fma AS (

    SELECT  setting AS autovacuum_freeze_max_age 

    FROM    pg_settings 

    WHERE   name = 'autovacuum_freeze_max_age'

)

, sto AS (

    select  opt_oid

          , split_part(setting, '=', 1) as param

          , split_part(setting, '=', 2) as value 

    from    (

                select 

                        oid opt_oid

                      , unnest(reloptions) setting 

                from    pg_class

            ) opt

)

SELECT  '"'||ns.nspname||'"."'||c.relname||'"' as relation

      , pg_size_pretty(pg_table_size(c.oid)) as table_size

      , age(relfrozenxid) as xid_age

      , coalesce(cfma.value::float, autovacuum_freeze_max_age::float) as autovacuum_freeze_max_age

      , (coalesce(cavt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples) as autovacuum_vacuum_tuples

      , n_dead_tup as dead_tuples

FROM    pg_class c 

        join pg_namespace ns 

        on  ns.oid = c.relnamespace

        join pg_stat_all_tables stat 

        on  stat.relid = c.oid

        join avt 

        on (1=1) join vsf 

        on (1=1) join fma 

        on (1=1)

        left join sto cavt 

        on cavt.param = 'autovacuum_vacuum_threshold' 

        and c.oid = cavt.opt_oid

        left join sto cvsf 

        on cvsf.param = 'autovacuum_vacuum_scale_factor' 

        and c.oid = cvsf.opt_oid

        left join sto cfma 

        on cfma.param = 'autovacuum_freeze_max_age' 

        and c.oid = cfma.opt_oid

WHERE   c.relkind = 'r' and nspname <> 'pg_catalog'

and     (

            age(relfrozenxid) >= coalesce(cfma.value::float, autovacuum_freeze_max_age::float)

            or

            coalesce(cavt.value::float, autovacuum_vacuum_threshold::float) + coalesce(cvsf.value::float,autovacuum_vacuum_scale_factor::float) * c.reltuples <= n_dead_tup

        )

ORDER BY age(relfrozenxid) DESC;

1. Temp tablespace 대체하기

*Temp tablespace가 100% 일시 테이블 스페이스를 재성성 하는 방법

*주의사항 Temp tablespace를 생성할 저장공간이 없다면 불가능한 방법임

예시)

1) 생성

CREATE TEMPORARY TABLESPACE "TEMP_1" TEMPFILE

'/ora_data/temp01_1.dbf' SIZE 1024M AUTOEXTEND OFF,

'/ora_data/temp02_1.dbf' SIZE 1024M AUTOEXTEND OFF,

'/ora_data/temp03_1.dbf' SIZE 4096M AUTOEXTEND OFF

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

2) 변경

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_1

3) 삭제

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

2. UNDO tablespace 대체하기

1) 생성

CREATE UNDO TABLESPACE "UNDOTBS1_1" DATAFILE

'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ASITSNT\UNDOTBS01_1.DBF' SIZE 20480M AUTOEXTEND OFF

EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

2) 변경

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS1_1;

3) 삭제

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

select distinct sh.username || '(' || sh.sid || ')' "Lock holder",

     ao.object_name "Object",

     l.type,

     decode(lmode, 

     1, 'NULL', 

     2, 'ROW SHARE', 

     3, 'ROW EXCLUSIVE', 

     4, 'SHARE', 

     5, 'SHARE ROW EXCLUSIVE', 

     6, 'EXCLUSIVE', '?') "Lock mode",

     sr.username || '(' || sr.sid || ')' "Object requester"

     from v$session sh, all_objects ao, v$lock l, v$session sr

     where l.id1 = ao.object_id

     and sh.sid = l.sid

     and sh.lockwait is null

     and sr.lockwait is not null

     and l.type = 'TM'

     order by 1, 2, 3

1. PK(Unique) 인덱스를 가지고 있지 않은 테이블 확인

SELECT table_catalog, table_schema, table_name

FROM information_schema.tables

WHERE table_schema NOT IN ('information_schema', 'pg_catalog')

EXCEPT SELECT table_catalog, table_schema, table_name

FROM information_schema.table_constraints

WHERE constraint_type IN ('PRIMARY KEY', 'UNIQUE') 

AND table_schema NOT IN ('information_schema', 'pg_catalog');

2. 인덱스 중복 생성 확인

WITH index_info AS

(

SELECT 

pg_get_indexdef(indexrelid) AS index_def, 

indexrelid::regclass index_name, 

indrelid::regclass table_name, 

array_agg(attname) AS index_att

FROM pg_index i 

JOIN pg_attribute a 

ON i.indexrelid = a.attrelid

GROUP BY pg_get_indexdef(indexrelid), indrelid,  indexrelid

)

SELECT DISTINCT

  CASE WHEN a.index_name > b.index_name THEN a.index_def ELSE b.index_def END AS index_def,

  CASE WHEN a.index_name > b.index_name THEN a.index_name ELSE b.index_name END AS index_name,

  CASE WHEN a.index_name > b.index_name THEN b.index_def ELSE a.index_def END AS overlap_index_def,

  CASE WHEN a.index_name > b.index_name THEN b.index_def ELSE a.index_def END AS overlap_index_name,

  a.table_name

FROM index_info a 

INNER JOIN index_info b 

ON (

a.index_name != b.index_name 

AND a.table_name = b.table_name 

AND a.index_att && b.index_att 

);

SELECT col.table_schema AS schema_name, col.table_name, col.column_name, 

col.character_maximum_length, col.is_nullable, col.numeric_precision, 

col.numeric_scale, col.datptime_precision, col.ordinal_position, b.atttypmod, b.attndims, col.data_type AS col_type,

pt.typelem, pt.typlen, pt.typtype, nbt.nspname AS elem_schema, bt.typname AS elem_name, b.atttypid, col.udt_schema, col.udt_name, 

col.domain_catalog, col.domain_schema, col.domain_name, col_description(c.oid, col.ordinal_position) AS comment,

col.column_default AS col_default, b.attacl, colnsp.nspname AS collation_schema_name, 

coll.collname, c.relkind, b.attfdwoptions AS foreign_options 

FROM information_schema.columns AS col 

LEFT JOIN pg_namespace ns ON ns.nspname = col.table_schema 

LEFT JOIN pg_class c ON col.table_name = c.relname 

AND c.relnamespace = ns.oid 

LEFT JOIN pg_attrdef a 

ON c.oid = a.adrelid 

AND col.ordinal_position = a.adnum 

LEFT JOIN pg_attribute b 

ON b.attrelid = c.oid 

AND b.attname = col.column_name 

LEFT JOIN pg_type pt 

ON pt.oid = b.atttypid 

LEFT JOIN pg_collation coll 

ON coll.oid = b.attcollation 

LEFT JOIN pg_namespace colnsp 

ON coll.collnamespace = colnsp.oid 

LEFT JOIN pg_type bt 

ON pt.typelem = bt.oid 

LEFT JOIN pg_namespace nbt 

ON bt.typnamespace = nbt.oid 

 WHERE col.table_schema = 'cbsadm' 

 ORDER BY col.table_schema, col.table_name, col.ordinal_position