Changes between Version 6 and Version 7 of Ticket #84, comment 3


Ignore:
Timestamp:
May 28, 2018 9:04:37 AM (7 years ago)
Author:
cedric
Comment:

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #84, comment 3

    v6 v7  
    8989 
    9090------------------ 
    91 = EDA2.0 = 
    92 {{{#!sh 
    93 F: 
    94 cd F:/base 
    95 set PGPORT=5432 
    96 set PGHOST=w3 
    97 set PGUSER=postgres 
    98 set PGPASSWORD=? 
    99 set THEDBSOURCE=eda2 
    100 set PGINSTALL=C:\Program Files\PostgreSQL\9.6 
    101 set PATH=%PATH%;"%PGINSTALL%\bin" 
    102  
    103 set SCHEMA=anglian 
    104 set BACKUPFILE=anglian.backup 
    105 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    106  
    107 set SCHEMA=bd_map 
    108 set BACKUPFILE=bd_map.backup 
    109 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    110  
    111 set SCHEMA=belge 
    112 set BACKUPFILE=belge.backup 
    113 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    114  
    115 REM OK 
    116 set SCHEMA=ccm21 
    117 set BACKUPFILE=ccm21.backup 
    118 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    119  
    120 set SCHEMA=clc 
    121 set BACKUPFILE=clc.backup 
    122 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    123 REM TODO 
    124 set SCHEMA=dbeel 
    125 set BACKUPFILE=dbeel.backup 
    126 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    127 REM OK 
    128 set SCHEMA=dbeel_nomenclature 
    129 set BACKUPFILE=dbeel_nomenclature.backup 
    130 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    131 REM OK 
    132 set SCHEMA=dmeer2003 
    133 set BACKUPFILE=dmeer2003.backup 
    134 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    135 REM OK 
    136 set SCHEMA=ecoregion2003 
    137 set BACKUPFILE=ecoregion2003.backup 
    138 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    139 REM OK 
    140 set SCHEMA= europe 
    141 set BACKUPFILE= europe.backup 
    142 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    143 REM OK 
    144 set SCHEMA= european_wise2008 
    145 set BACKUPFILE= european_wise2008.backup 
    146 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    147 REM OK 
    148 set SCHEMA= france 
    149 set BACKUPFILE= france.backup 
    150 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    151 REM OK 
    152 set SCHEMA= geobs2010 
    153 set BACKUPFILE= geobs2010.backup 
    154 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    155  
    156 set SCHEMA= ia 
    157 set BACKUPFILE= ia.backup 
    158 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    159  
    160 set SCHEMA= ireland 
    161 set BACKUPFILE= ireland.backup 
    162 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    163 REM TODO 
    164 set SCHEMA= onema 
    165 set BACKUPFILE= onema.backup 
    166 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    167  
    168 set SCHEMA= oria 
    169 set BACKUPFILE= oria.backup 
    170 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    171 REM TODO 
    172 set SCHEMA= temperature 
    173 set BACKUPFILE= temperature.backup 
    174 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    175  
    176 set SCHEMA= tidal 
    177 set BACKUPFILE= tidal.backup 
    178 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    179  
    180 set SCHEMA= uk 
    181 set BACKUPFILE= uk.backup 
    182 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    183  
    184 set SCHEMA= vilaine 
    185 set BACKUPFILE= vilaine.backup 
    186 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    187 REM TODO 
    188 set SCHEMA= wrbd 
    189 set BACKUPFILE= wrbd.backup 
    190 pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
    191  
    192 }}} 
    193  
    194 The first trials didn't work. I'll have to split my databases in several components. 
    195 Size of databases 
    196  
    197 {{{ 
    198 SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner, 
    199     CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') 
    200         THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) 
    201         ELSE 'No Access' 
    202     END AS SIZE 
    203 FROM pg_catalog.pg_database d 
    204     ORDER BY 
    205     CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') 
    206         THEN pg_catalog.pg_database_size(d.datname) 
    207         ELSE NULL 
    208     END DESC -- nulls first 
    209     LIMIT 20 
    210 }}} 
    211  
    212 | table_name | size | 
    213 | eda2|10 GB| 
    214 |eda2.1|3305 MB| 
    215  
    21691 
    21792---------------------- 
    … …  
    277152 uk                  
    278153 vilaine             
    279  wrbd      
     154 wrbd     
     155= EDA2.0 = 
     156{{{#!sh 
     157F: 
     158cd F:/base 
     159set PGPORT=5432 
     160set PGHOST=w3 
     161set PGUSER=postgres 
     162set PGPASSWORD=? 
     163set THEDBSOURCE=eda2 
     164set PGINSTALL=C:\Program Files\PostgreSQL\9.6 
     165set PATH=%PATH%;"%PGINSTALL%\bin" 
     166 
     167set SCHEMA=anglian 
     168set BACKUPFILE=anglian.backup 
     169pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     170 
     171set SCHEMA=bd_map 
     172set BACKUPFILE=bd_map.backup 
     173pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     174 
     175set SCHEMA=belge 
     176set BACKUPFILE=belge.backup 
     177pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     178 
     179REM OK 
     180set SCHEMA=ccm21 
     181set BACKUPFILE=ccm21.backup 
     182pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     183 
     184set SCHEMA=clc 
     185set BACKUPFILE=clc.backup 
     186pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     187REM TODO 
     188set SCHEMA=dbeel 
     189set BACKUPFILE=dbeel.backup 
     190pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     191REM OK 
     192set SCHEMA=dbeel_nomenclature 
     193set BACKUPFILE=dbeel_nomenclature.backup 
     194pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     195REM OK 
     196set SCHEMA=dmeer2003 
     197set BACKUPFILE=dmeer2003.backup 
     198pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     199REM OK 
     200set SCHEMA=ecoregion2003 
     201set BACKUPFILE=ecoregion2003.backup 
     202pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     203REM OK 
     204set SCHEMA= europe 
     205set BACKUPFILE= europe.backup 
     206pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     207REM OK 
     208set SCHEMA= european_wise2008 
     209set BACKUPFILE= european_wise2008.backup 
     210pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     211REM OK 
     212set SCHEMA= france 
     213set BACKUPFILE= france.backup 
     214pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     215REM OK 
     216set SCHEMA= geobs2010 
     217set BACKUPFILE= geobs2010.backup 
     218pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     219 
     220set SCHEMA= ia 
     221set BACKUPFILE= ia.backup 
     222pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     223 
     224set SCHEMA= ireland 
     225set BACKUPFILE= ireland.backup 
     226pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     227REM TODO 
     228set SCHEMA= onema 
     229set BACKUPFILE= onema.backup 
     230pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     231 
     232set SCHEMA= oria 
     233set BACKUPFILE= oria.backup 
     234pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     235REM TODO 
     236set SCHEMA= temperature 
     237set BACKUPFILE= temperature.backup 
     238pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     239 
     240set SCHEMA= tidal 
     241set BACKUPFILE= tidal.backup 
     242pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     243 
     244set SCHEMA= uk 
     245set BACKUPFILE= uk.backup 
     246pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     247 
     248set SCHEMA= vilaine 
     249set BACKUPFILE= vilaine.backup 
     250pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     251REM TODO 
     252set SCHEMA= wrbd 
     253set BACKUPFILE= wrbd.backup 
     254pg_dump --schema %SCHEMA% --file %BACKUPFILE% --verbose -Fc -b %THEDBSOURCE% 
     255 
     256}}} 
     257 
     258The first trials didn't work. I'll have to split my databases in several components. 
     259Size of databases 
     260 
     261{{{ 
     262SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner, 
     263    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') 
     264        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) 
     265        ELSE 'No Access' 
     266    END AS SIZE 
     267FROM pg_catalog.pg_database d 
     268    ORDER BY 
     269    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') 
     270        THEN pg_catalog.pg_database_size(d.datname) 
     271        ELSE NULL 
     272    END DESC -- nulls first 
     273    LIMIT 20 
     274}}} 
     275 
     276| table_name | size | 
     277| eda2|10 GB| 
     278|eda2.1|3305 MB| 
     279 
     280