Thread: db size and tables size difference

db size and tables size difference

From
Isabella Ghiurea
Date:
Hi Pg Admin list.
I'm trying to understand why  there are  GB's difference  when checking
for db size using pg_size_pretty()  and  querying for  tables + indexes
size. .
The  sum  of tables +index sizes is  showing as aprox 6.5GB and
pg_size_pretty(dbname) is coming as 12GB, this  are the results after a
full vacuum and reindexdb,  also the sum of OS db files size is ~ 6.5GB.
Any tips what I'm missing : are some  "unallocated" db pages or anything
else ?

select pg_size_pretty(pg_database_size('db1'));
 pg_size_pretty
----------------
 12 GB
(1 row)
  *** Check for tables size :
SELECT schemaname, tablename,
pg_size_pretty(size) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *,
pg_relation_size(schemaname||'.'||tablename) AS
size,pg_total_relation_size(schemaname||'.'||tablename) AS
total_size FROM pg_tables) AS TABLES where schemaname='caom'
ORDER BY total_size DESC;
schemaname |          tablename          | size_pretty | total_size_pretty
------------+-----------------------------+-------------+-------------------
 caom       | spatialentity               | 3053 MB     | 3216 MB
 caom       | artifact                    | 1979 MB     | 2150 MB
 caom       | plane                       | 413 MB      | 677 MB
 caom       | positionsample              | 110 MB      | 219 MB
 caom       | simpleobservation           | 165 MB      | 202 MB
 caom       | temporalentity              | 77 MB       | 86 MB
 caom       | spectralentity              | 68 MB       | 73 MB
 caom       | metric                      | 33 MB       | 70 MB
 caom       | polarizationentity          | 29 MB       | 33 MB
 caom       | harvestskip                 | 1576 kB     | 2056 kB
 caom       | harveststate                | 840 kB      | 856 kB
 caom       | positionhole                | 48 kB       | 584 kB




 *** OR  : check for the biggest tables+index size:
SELECT ' Top 20  biggest tables and indexes'
;
SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(nspname || '.' || relname)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND nspname !~ '^pg_toast'
    AND pg_relation_size(nspname || '.' || relname)>0
  ORDER BY pg_relation_size(nspname || '.' || relname) DESC
  LIMIT 20;
(1 row)

         relation          |      size
---------------------------+---------
 caom.spatialentity        |  3053 MB
 caom.artifact             |   1979 MB
 caom.plane                |    413 MB
 caom.artifact_i1          |  171 MB
 caom.simpleobservation    | 165 MB
 caom.spatialentity_i1     | 162 MB
 caom.positionsample       | 110 MB
 caom.plane_psi2           | 86 MB
 caom.temporalentity       | 77 MB
 caom.spectralentity       | 68 MB
 caom.plane_energy_i1      | 67 MB
 caom.plane_time_i1        | 58 MB
 caom.plane_position_i2    | 48 MB
 caom.metric               | 33 MB
 caom.polarizationentity   | 29 MB
 caom.simpleobservation_i2 | 25 MB
 caom.plane_psi1           | 23 MB
 caom.metric_i2            | 18 MB

Thank you
Isabella

--
-----------------------------------------------------------
Isabella A. Ghiurea

Isabella.Ghiurea@nrc-cnrc.gc.ca
Canadian Astronomy Data Centre | http://www1.cadc-ccda.hia-iha.nrc-cnrc.gc.ca/
National Research Council of Canada, Herzberg Institute of Astrophysics
5071 West Saanich Road, Victoria BC V9E 2E7, Canada
Phone: 250 363-3446 fax: 250 363-0045


Re: db size and tables size difference

From
Tom Lane
Date:
Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca> writes:
> I'm trying to understand why  there are  GB's difference  when checking
> for db size using pg_size_pretty()  and  querying for  tables + indexes
> size. .

You are not counting everything --- the total DB size is clearly 12GB,
so the question is where are the other 5.5GB?  Your first query shows
that schema caom accounts for 6+GB, but the second one does not prove
that schema caom contains all the big hogs.  My guesses are:

1. Toast tables for tables that aren't in caom --- you used
pg_relation_size not pg_total_relation_size, and excluded toast
tables, so you are missing those.

2. pg_largeobject ... got any large objects?

3. Bloat in other system catalogs.  5GB of catalog bloat would be
pretty awful, but maybe that's what it is.

Try that last query without the namespace restrictions.

            regards, tom lane

Re: db size and tables size difference

From
Tom Lane
Date:
Naomi Walker <nwalker@eldocomp.com> writes:
> How would one check for catalog bloat?

Like I said:

>> Try that last query without the namespace restrictions.

Isabella excluded pg_catalog, so if that's where the problem is,
that's why she didn't see it.  But let's see the data before
discussing how to fix it...

            regards, tom lane

Re: db size and tables size difference

From
Naomi Walker
Date:
How would one check for catalog bloat?  And, if bloated, would unloading
and reloading the database
fix it?

Naomi
>
> 3. Bloat in other system catalogs.  5GB of catalog bloat would be
> pretty awful, but maybe that's what it is.
>
> Try that last query without the namespace restrictions.
>
>             regards, tom lane
>
>


--
------------------------------------------------------------------------
Naomi Walker                          Chief Information Officer
Eldorado Computing, Inc               nwalker@eldocomp.com
  ---An EDS Company                   602-604-3100
------------------------------------------------------------------------
Hard things are put in our way, not to stop us, but to call out our
courage and strength. -- Anonymous
------------------------------------------------------------------------


Re: db size and tables size difference

From
Isabella Ghiurea
Date:
Hi All,

Please,  see more info my env:  PG 8.3.6 on RHE5-64bits.

 1. there are more than one schemas, but the size of the tables is close
to 30-40kB, see some samples
schemaname |  tablename  | size_pretty | total_size_pretty
------------+-------------+-------------+-------------------
 tap_schema | tables      | 8192 bytes  | 32 kB
 tap_schema | columns     | 8192 bytes  | 32 kB
 tap_schema | schemas     | 8192 bytes  | 32 kB
 tap_schema | keys        | 8192 bytes  | 24 kB
 tap_schema | key_columns | 8192 bytes  | 8192 bytes

2.   There are no BLOB's  data type in db at this time :
cvodb=# select * from pg_largeobject;
 loid | pageno | data

------+--------+------
(0 rows)


3. As  Tom suggested ,  I  excluded the table space restriction  and
changed  to pg_total_relation_size  my  original SQL :


SELECT 'the table  size without table space restrictions';
SELECT nspname || '.' || relname AS
"relation",pg_size_pretty(pg_total_relation_size(nspname || '.' ||
relname)) AS "s
ize"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  ORDER BY pg_relation_size(nspname || '.' || relname) DESC
  LIMIT 20;
the table  size without table space restrictions
(1 row)

         relation          |  size
---------------------------+---------
 caom.spatialentity        | 3216 MB
 caom.artifact             | 2150 MB
 caom.plane                | 677 MB
 caom.artifact_i1          | 171 MB
 caom.simpleobservation    | 202 MB
 caom.spatialentity_i1     | 162 MB
 caom.positionsample       | 219 MB
 caom.plane_psi2           | 86 MB
 caom.temporalentity       | 86 MB
 caom.spectralentity       | 73 MB
 caom.plane_energy_i1      | 67 MB
 caom.plane_time_i1        | 58 MB
 caom.plane_position_i2    | 48 MB
 caom.metric               | 70 MB
 caom.polarizationentity   | 33 MB
 caom.simpleobservation_i2 | 25 MB
 caom.plane_psi1           | 23 MB
 caom.metric_i2            | 18 MB
 caom.metric_i1            | 18 MB
 caom.plane_position_i3    | 15 MB
(20 rows)


4. Where are the  rest of 5,5 GB been used ? How can I get the system
catalog   correct size ?



Thank you,
Isabella

Tom Lane wrote:
>
> Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca> writes:
> > I'm trying to understand why  there are  GB's difference  when checking
> > for db size using pg_size_pretty()  and  querying for  tables + indexes
> > size. .
>
> You are not counting everything --- the total DB size is clearly 12GB,
> so the question is where are the other 5.5GB?  Your first query shows
> that schema caom accounts for 6+GB, but the second one does not prove
> that schema caom contains all the big hogs.  My guesses are:
>
> 1. Toast tables for tables that aren't in caom --- you used
> pg_relation_size not pg_total_relation_size, and excluded toast
> tables, so you are missing those.
>
> 2. pg_largeobject ... got any large objects?
>
> 3. Bloat in other system catalogs.  5GB of catalog bloat would be
> pretty awful, but maybe that's what it is.
>
> Try that last query without the namespace restrictions.
>


>
>                         regards, tom lane
>


--
-----------------------------------------------------------
Isabella A. Ghiurea

Isabella.Ghiurea@nrc-cnrc.gc.ca
Canadian Astronomy Data Centre | http://www1.cadc-ccda.hia-iha.nrc-cnrc.gc.ca/
National Research Council of Canada, Herzberg Institute of Astrophysics
5071 West Saanich Road, Victoria BC V9E 2E7, Canada
Phone: 250 363-3446 fax: 250 363-0045


Re: db size and tables size difference

From
Tom Lane
Date:
Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca> writes:
> SELECT nspname || '.' || relname AS
> "relation",pg_size_pretty(pg_total_relation_size(nspname || '.' ||
> relname)) AS "s
> ize"
>   FROM pg_class C
>   LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
>   ORDER BY pg_relation_size(nspname || '.' || relname) DESC
>   LIMIT 20;

I think maybe you'd better ORDER BY pg_total_relation_size instead.
Also, maybe look further than 20 rows ... maybe the issue is many
thousands of little tables?

            regards, tom lane

Re: db size and tables size difference

From
Isabella Ghiurea
Date:
 Hi All,
 Tom, please see bellow are the results for  the  modified query with ORDER
BY

select pg_size_pretty(pg_database_size('db1')); ---->
pg_size_pretty
----------------
12 GB

SELECT 'the table  size without table space restrictions';
SELECT nspname || '.' || relname AS
"relation",pg_size_pretty(pg_total_relation_size(nspname || '.' || relname))
AS "s
ize"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  ORDER BY pg_total_relation_size(nspname || '.' || relname) DESC
  LIMIT 1000;

------------------------------------------------------+------------
 caom.spatialentity                                   | 3216 MB
 caom.artifact                                        | 2150 MB
 caom.plane                                           | 677 MB
 caom.positionsample                                  | 219 MB
 caom.simpleobservation                               | 202 MB
 caom.artifact_i1                                     | 171 MB
 caom.spatialentity_i1                                | 162 MB
 caom.temporalentity                                  | 86 MB
 caom.plane_psi2                                      | 86 MB
 caom.spectralentity                                  | 73 MB
 caom.metric                                          | 70 MB
 caom.plane_energy_i1                                 | 67 MB
 caom.plane_time_i1                                   | 58 MB
 caom.plane_position_i2                               | 48 MB
 caom.polarizationentity                              | 33 MB
 caom.simpleobservation_i2                            | 25 MB
 caom.plane_psi1                                      | 23 MB
 caom.metric_i2                                       | 18 MB
 caom.metric_i1                                       | 18 MB
 caom.plane_i1                                        | 15 MB
 caom.plane_position_i3                               | 15 MB
 caom.plane_polar_i1                                  | 15 MB
 caom.plane_time_i2                                   | 15 MB
 caom.plane_energy_i2                                 | 15 MB
 caom.plane_i2                                        | 15 MB
 caom.simpleobservation_i1                            | 12 MB
 caom.temporalentity_i1                               | 9496 kB
 caom.spectralentity_i1                               | 4384 kB
 caom.polarizationentity_i1                           | 4368 kB
 caom.harvestskip                                     | 2056 kB
 pg_catalog.pg_depend                                 | 1008 kB
 pg_catalog.pg_proc                                   | 880 kB
 caom.harveststate                                    | 856 kB
 pg_catalog.pg_attribute                              | 648 kB
 caom.positionhole                                    | 584 kB
 pg_catalog.pg_statistic                              | 576 kB
 caom.plane_phi2                                      | 496 kB
 caom.harvestskip_i1                                  | 480 kB
 pg_catalog.pg_proc_proname_args_nsp_index            | 328 kB
 pg_catalog.pg_operator                               | 296 kB
 pg_catalog.pg_description                            | 280 kB
 pg_catalog.pg_depend_depender_index                  | 264 kB
 pg_catalog.pg_depend_reference_index                 | 264 kB
 pg_catalog.pg_rewrite                                | 256 kB
 pg_catalog.pg_attribute_relid_attnam_index           | 240 kB
 pg_toast.pg_toast_2618                               | 160 kB
 pg_catalog.pg_type                                   | 144 kB
 pg_catalog.pg_class                                  | 136 kB
 pg_catalog.pg_amop                                   | 128 kB
 pg_catalog.pg_operator_oprname_l_r_n_index           | 112 kB
 pg_catalog.pg_description_o_c_o_index                | 96 kB
 pg_toast.pg_toast_2619                               | 96 kB
 pg_catalog.pg_constraint                             | 80 kB
 pg_catalog.pg_conversion                             | 80 kB
 pg_catalog.pg_amproc                                 | 72 kB
 pg_catalog.pg_attribute_relid_attnum_index           | 72 kB
 pg_catalog.pg_proc_oid_index                         | 72 kB
 pg_catalog.pg_opclass                                | 72 kB
 pg_catalog.pg_trigger                                | 56 kB
 pg_catalog.pg_type_typname_nsp_index                 | 56 kB
 pg_catalog.pg_index                                  | 56 kB
 information_schema.sql_features                      | 48 kB
 pg_catalog.pg_cast                                   | 48 kB
 pg_catalog.pg_class_relname_nsp_index                | 48 kB
 pg_catalog.pg_database                               | 48 kB
 pg_catalog.pg_authid                                 | 48 kB
 pg_catalog.pg_ts_config_map                          | 48 kB
 pg_catalog.pg_opfamily                               | 48 kB
 pg_catalog.pg_language                               | 40 kB
 pg_catalog.pg_shdepend                               | 40 kB
 pg_catalog.pg_ts_parser                              | 40 kB
 pg_catalog.pg_ts_template                            | 40 kB
 pg_catalog.pg_auth_members                           | 40 kB
 pg_catalog.pg_ts_config                              | 40 kB
 pg_catalog.pg_ts_dict                                | 40 kB
 pg_catalog.pg_operator_oid_index                     | 40 kB
 pg_catalog.pg_tablespace                             | 40 kB
 pg_catalog.pg_namespace                              | 40 kB
 pg_catalog.pg_am                                     | 40 kB
 pg_toast.pg_toast_45054                              | 32 kB
 pg_catalog.pg_conversion_name_nsp_index              | 32 kB
 pg_catalog.pg_shdescription                          | 32 kB
 tap_schema.tables                                    | 32 kB
 pg_catalog.pg_statistic_relid_att_index              | 32 kB
 pg_catalog.pg_amop_fam_strat_index                   | 32 kB
 tap_schema.schemas                                   | 32 kB
 pg_catalog.pg_amproc_fam_proc_index                  | 32 kB
 pg_catalog.pg_ts_config_map_index                    | 32 kB
 tap_schema.columns                                   | 32 kB
 caom.plane_phi1                                      | 32 kB
 pg_catalog.pg_amop_oid_index                         | 32 kB
 pg_catalog.pg_amop_opr_fam_index                     | 32 kB
 pg_catalog.pg_opclass_am_name_nsp_index              | 32 kB
 caom.deletedplane                                    | 24 kB
 pg_catalog.pg_pltemplate                             | 24 kB
 pg_catalog.pg_attrdef                                | 24 kB
 tap_schema.keys                                      | 24 kB
 caom.deletedspatialentity                            | 24 kB
 caom.deletedpolarizationentity                       | 24 kB
 caom.compositeobservation                            | 24 kB
 caom.deletedtemporalentity                           | 24 kB
 caom.deletedartifact                                 | 24 kB
 caom.deletedspectralentity                           | 24 kB
 pg_catalog.pg_aggregate                              | 24 kB
 information_schema.sql_implementation_info           | 16 kB
 information_schema.sql_languages                     | 16 kB
 pg_catalog.pg_enum                                   | 16 kB
 pg_catalog.pg_ts_config_oid_index                    | 16 kB
 pg_catalog.pg_trigger_oid_index                      | 16 kB
 tap_schema.schemas_pkey                              | 16 kB
 pg_catalog.pg_constraint_conrelid_index              | 16 kB
 pg_catalog.pg_conversion_default_index               | 16 kB
 pg_catalog.pg_namespace_oid_index                    | 16 kB
 pg_catalog.pg_namespace_nspname_index                | 16 kB
 pg_catalog.pg_cast_source_target_index               | 16 kB
 pg_catalog.pg_constraint_contypid_index              | 16 kB
 information_schema.sql_packages                      | 16 kB
 pg_catalog.pg_aggregate_fnoid_index                  | 16 kB
 pg_catalog.pg_rewrite_oid_index                      | 16 kB
 pg_toast.pg_toast_2618_index                         | 16 kB
 pg_catalog.pg_trigger_tgconstrname_index             | 16 kB
 pg_catalog.pg_trigger_tgrelid_tgname_index           | 16 kB
 information_schema.sql_parts                         | 16 kB
 pg_catalog.pg_authid_rolname_index                   | 16 kB
 pg_catalog.pg_authid_oid_index                       | 16 kB
 pg_catalog.pg_auth_members_role_member_index         | 16 kB
 pg_catalog.pg_auth_members_member_role_index         | 16 kB
 information_schema.sql_sizing                        | 16 kB
 tap_schema.keys_pkey                                 | 16 kB
 tap_schema.columns_pkey                              | 16 kB
 caom.deletedplane_i1                                 | 16 kB
 pg_catalog.pg_database_datname_index                 | 16 kB
 pg_catalog.pg_database_oid_index                     | 16 kB
 caom.deletedpolarizationentity_i1                    | 16 kB
 pg_catalog.pg_shdescription_o_c_index                | 16 kB
 pg_catalog.pg_language_name_index                    | 16 kB
 pg_catalog.pg_language_oid_index                     | 16 kB
 pg_catalog.pg_index_indrelid_index                   | 16 kB
 caom.timesample                                      | 16 kB
 pg_catalog.pg_am_name_index                          | 16 kB
 pg_toast.pg_toast_2619_index                         | 16 kB
 pg_catalog.pg_constraint_conname_nsp_index           | 16 kB
 pg_catalog.pg_index_indexrelid_index                 | 16 kB
 pg_catalog.pg_am_oid_index                           | 16 kB
 pg_catalog.pg_pltemplate_name_index                  | 16 kB
 pg_catalog.pg_shdepend_depender_index                | 16 kB
 pg_catalog.pg_shdepend_reference_index               | 16 kB
 pg_catalog.pg_tablespace_oid_index                   | 16 kB
 pg_catalog.pg_tablespace_spcname_index               | 16 kB
 pg_toast.pg_toast_45054_index                        | 16 kB
 caom.deletedtemporalentity_i1                        | 16 kB
 pg_catalog.pg_ts_dict_oid_index                      | 16 kB
 caom.deletedspectralentity_i1                        | 16 kB
 caom.deletedspatialentity_i1                         | 16 kB
 caom.deletedartifact_i1                              | 16 kB
 pg_catalog.pg_constraint_oid_index                   | 16 kB
 pg_catalog.pg_opfamily_am_name_nsp_index             | 16 kB
 pg_catalog.pg_opfamily_oid_index                     | 16 kB
 pg_catalog.pg_opclass_oid_index                      | 16 kB
 pg_catalog.pg_amproc_oid_index                       | 16 kB
 pg_catalog.pg_class_oid_index                        | 16 kB
 pg_catalog.pg_rewrite_rel_rulename_index             | 16 kB
 pg_catalog.pg_ts_config_cfgname_index                | 16 kB
 tap_schema.tables_pkey                               | 16 kB


Thank you
Isabella


Hi All,

Please,  see more info my env:  PG 8.3.6 on RHE5-64bits.

1. there are more than one schemas, but the size of the tables is close to
30-40kB, see some samples
schemaname |  tablename  | size_pretty | total_size_pretty
------------+-------------+-------------+-------------------
tap_schema | tables      | 8192 bytes  | 32 kB
tap_schema | columns     | 8192 bytes  | 32 kB
tap_schema | schemas     | 8192 bytes  | 32 kB
tap_schema | keys        | 8192 bytes  | 24 kB
tap_schema | key_columns | 8192 bytes  | 8192 bytes

2.   There are no BLOB's  data type in db at this time :
cvodb=# select * from pg_largeobject;
loid | pageno | data

------+--------+------
(0 rows)


3. As  Tom suggested ,  I  excluded the table space restriction  and changed
to pg_total_relation_size  my  original SQL :


SELECT 'the table  size without table space restrictions';
SELECT nspname || '.' || relname AS
"relation",pg_size_pretty(pg_total_relation_size(nspname || '.' || relname))
AS "s
ize"
 FROM pg_class C
 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 ORDER BY pg_relation_size(nspname || '.' || relname) DESC
 LIMIT 20;
the table  size without table space restrictions
(1 row)

        relation          |  size
---------------------------+---------
caom.spatialentity        | 3216 MB
caom.artifact             | 2150 MB
caom.plane                | 677 MB
caom.artifact_i1          | 171 MB
caom.simpleobservation    | 202 MB
caom.spatialentity_i1     | 162 MB
caom.positionsample       | 219 MB
caom.plane_psi2           | 86 MB
caom.temporalentity       | 86 MB
caom.spectralentity       | 73 MB
caom.plane_energy_i1      | 67 MB
caom.plane_time_i1        | 58 MB
caom.plane_position_i2    | 48 MB
caom.metric               | 70 MB
caom.polarizationentity   | 33 MB
caom.simpleobservation_i2 | 25 MB
caom.plane_psi1           | 23 MB
caom.metric_i2            | 18 MB
caom.metric_i1            | 18 MB
caom.plane_position_i3    | 15 MB
(20 rows)


4. Where are the  rest of 5,5 GB been used ? How can I get the system
catalog   correct size ?



Thank you,
Isabella

Tom Lane wrote:
>
> Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca> writes:
> > I'm trying to understand why  there are  GB's difference  when checking
> > for db size using pg_size_pretty()  and  querying for  tables + indexes
> > size. .
>
> You are not counting everything --- the total DB size is clearly 12GB,
> so the question is where are the other 5.5GB?  Your first query shows
> that schema caom accounts for 6+GB, but the second one does not prove
> that schema caom contains all the big hogs.  My guesses are:
>
> 1. Toast tables for tables that aren't in caom --- you used
> pg_relation_size not pg_total_relation_size, and excluded toast
> tables, so you are missing those.
>
> 2. pg_largeobject ... got any large objects?
>
> 3. Bloat in other system catalogs.  5GB of catalog bloat would be
> pretty awful, but maybe that's what it is.
>
> Try that last query without the namespace restrictions.
>


>
>                         regards, tom lane

Hi Pg Admin list.
I'm trying to understand why  there are  GB's difference  when checking for
db size using pg_size_pretty()  and  querying for  tables + indexes size. .
The  sum  of tables +index sizes is  showing as aprox 6.5GB and
pg_size_pretty(dbname) is coming as 12GB, this  are the results after a full
vacuum and reindexdb,  also the sum of OS db files size is ~ 6.5GB. Any tips
what I'm missing : are some  "unallocated" db pages or anything else ?

select pg_size_pretty(pg_database_size('db1'));
pg_size_pretty
----------------
12 GB
(1 row)
 *** Check for tables size :
SELECT schemaname, tablename,
pg_size_pretty(size) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *,
pg_relation_size(schemaname||'.'||tablename) AS
size,pg_total_relation_size(schemaname||'.'||tablename) AS
total_size FROM pg_tables) AS TABLES where schemaname='caom'
ORDER BY total_size DESC;
schemaname |          tablename          | size_pretty | total_size_pretty
------------+-----------------------------+-------------+-------------------
caom       | spatialentity               | 3053 MB     | 3216 MB
caom       | artifact                    | 1979 MB     | 2150 MB
caom       | plane                       | 413 MB      | 677 MB
caom       | positionsample              | 110 MB      | 219 MB
caom       | simpleobservation           | 165 MB      | 202 MB
caom       | temporalentity              | 77 MB       | 86 MB
caom       | spectralentity              | 68 MB       | 73 MB
caom       | metric                      | 33 MB       | 70 MB
caom       | polarizationentity          | 29 MB       | 33 MB
caom       | harvestskip                 | 1576 kB     | 2056 kB
caom       | harveststate                | 840 kB      | 856 kB
caom       | positionhole                | 48 kB       | 584 kB




*** OR  : check for the biggest tables+index size:
SELECT ' Top 20  biggest tables and indexes'
;
SELECT nspname || '.' || relname AS "relation",
   pg_size_pretty(pg_relation_size(nspname || '.' || relname)) AS "size"
 FROM pg_class C
 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 WHERE nspname NOT IN ('pg_catalog', 'information_schema')
   AND nspname !~ '^pg_toast'
   AND pg_relation_size(nspname || '.' || relname)>0
 ORDER BY pg_relation_size(nspname || '.' || relname) DESC
 LIMIT 20;
(1 row)

        relation          |      size
---------------------------+---------
caom.spatialentity        |  3053 MB
caom.artifact             |   1979 MB
caom.plane                |    413 MB
caom.artifact_i1          |  171 MB
caom.simpleobservation    | 165 MB
caom.spatialentity_i1     | 162 MB
caom.positionsample       | 110 MB
caom.plane_psi2           | 86 MB
caom.temporalentity       | 77 MB
caom.spectralentity       | 68 MB
caom.plane_energy_i1      | 67 MB
caom.plane_time_i1        | 58 MB
caom.plane_position_i2    | 48 MB
caom.metric               | 33 MB
caom.polarizationentity   | 29 MB
caom.simpleobservation_i2 | 25 MB
caom.plane_psi1           | 23 MB
caom.metric_i2            | 18 MB

Thank you
Isabella


--
View this message in context:
http://www.nabble.com/Re%3A--ADMIN--db-size-and-tables-size-difference-tp25531211p25531211.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


Re: db size and tables size difference

From
Tom Lane
Date:
Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca> writes:
> SELECT nspname || '.' || relname AS
> "relation",pg_size_pretty(pg_total_relation_size(nspname || '.' || relname))
> AS "s
> ize"
>   FROM pg_class C
>   LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
>   ORDER BY pg_total_relation_size(nspname || '.' || relname) DESC
>   LIMIT 1000;

Hmph ... I can't see anything wrong with that query, so it seems that
we're left with the conclusion that there are files in the database
directory that don't match any entry in the catalogs.  AFAIK this'd
only be possible if you'd had a crash while deleting tables or some
similar problem.  What you'll need to do next is poke around in the
data directory and see if you can identify any large files that do
not correspond to any entry in pg_class.relfilenode.  You should
read the internals docs first, if you're not familiar with this
chapter:
http://www.postgresql.org/docs/8.3/static/storage.html

            regards, tom lane

Re: db size and tables size difference

From
Isabella Ghiurea
Date:
Hi All,
looking in more details on OS partitions sizwe  and each table space
corresponding to OS partitions will add up to  close to 6,5GB   for db size
same result as SQL table size.
The issue may be   with pg_size_pretty()  results, I don't have details
knowledge of this function.
select pg_size_pretty(pg_database_size('db1'));
pg_size_pretty
----------------
12 GB

Isabella


Isabella Ghiurea wrote:
>
>  Hi All,
>  Tom, please see bellow are the results for  the  modified query with
> ORDER BY
>
> select pg_size_pretty(pg_database_size('db1')); ---->
> pg_size_pretty
> ----------------
> 12 GB
>
> SELECT 'the table  size without table space restrictions';
> SELECT nspname || '.' || relname AS
> "relation",pg_size_pretty(pg_total_relation_size(nspname || '.' ||
> relname)) AS "s
> ize"
>   FROM pg_class C
>   LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
>   ORDER BY pg_total_relation_size(nspname || '.' || relname) DESC
>   LIMIT 1000;
>
> ------------------------------------------------------+------------
>  caom.spatialentity                                   | 3216 MB
>  caom.artifact                                        | 2150 MB
>  caom.plane                                           | 677 MB
>  caom.positionsample                                  | 219 MB
>  caom.simpleobservation                               | 202 MB
>  caom.artifact_i1                                     | 171 MB
>  caom.spatialentity_i1                                | 162 MB
>  caom.temporalentity                                  | 86 MB
>  caom.plane_psi2                                      | 86 MB
>  caom.spectralentity                                  | 73 MB
>  caom.metric                                          | 70 MB
>  caom.plane_energy_i1                                 | 67 MB
>  caom.plane_time_i1                                   | 58 MB
>  caom.plane_position_i2                               | 48 MB
>  caom.polarizationentity                              | 33 MB
>  caom.simpleobservation_i2                            | 25 MB
>  caom.plane_psi1                                      | 23 MB
>  caom.metric_i2                                       | 18 MB
>  caom.metric_i1                                       | 18 MB
>  caom.plane_i1                                        | 15 MB
>  caom.plane_position_i3                               | 15 MB
>  caom.plane_polar_i1                                  | 15 MB
>  caom.plane_time_i2                                   | 15 MB
>  caom.plane_energy_i2                                 | 15 MB
>  caom.plane_i2                                        | 15 MB
>  caom.simpleobservation_i1                            | 12 MB
>  caom.temporalentity_i1                               | 9496 kB
>  caom.spectralentity_i1                               | 4384 kB
>  caom.polarizationentity_i1                           | 4368 kB
>  caom.harvestskip                                     | 2056 kB
>  pg_catalog.pg_depend                                 | 1008 kB
>  pg_catalog.pg_proc                                   | 880 kB
>  caom.harveststate                                    | 856 kB
>  pg_catalog.pg_attribute                              | 648 kB
>  caom.positionhole                                    | 584 kB
>  pg_catalog.pg_statistic                              | 576 kB
>  caom.plane_phi2                                      | 496 kB
>  caom.harvestskip_i1                                  | 480 kB
>  pg_catalog.pg_proc_proname_args_nsp_index            | 328 kB
>  pg_catalog.pg_operator                               | 296 kB
>  pg_catalog.pg_description                            | 280 kB
>  pg_catalog.pg_depend_depender_index                  | 264 kB
>  pg_catalog.pg_depend_reference_index                 | 264 kB
>  pg_catalog.pg_rewrite                                | 256 kB
>  pg_catalog.pg_attribute_relid_attnam_index           | 240 kB
>  pg_toast.pg_toast_2618                               | 160 kB
>  pg_catalog.pg_type                                   | 144 kB
>  pg_catalog.pg_class                                  | 136 kB
>  pg_catalog.pg_amop                                   | 128 kB
>  pg_catalog.pg_operator_oprname_l_r_n_index           | 112 kB
>  pg_catalog.pg_description_o_c_o_index                | 96 kB
>  pg_toast.pg_toast_2619                               | 96 kB
>  pg_catalog.pg_constraint                             | 80 kB
>  pg_catalog.pg_conversion                             | 80 kB
>  pg_catalog.pg_amproc                                 | 72 kB
>  pg_catalog.pg_attribute_relid_attnum_index           | 72 kB
>  pg_catalog.pg_proc_oid_index                         | 72 kB
>  pg_catalog.pg_opclass                                | 72 kB
>  pg_catalog.pg_trigger                                | 56 kB
>  pg_catalog.pg_type_typname_nsp_index                 | 56 kB
>  pg_catalog.pg_index                                  | 56 kB
>  information_schema.sql_features                      | 48 kB
>  pg_catalog.pg_cast                                   | 48 kB
>  pg_catalog.pg_class_relname_nsp_index                | 48 kB
>  pg_catalog.pg_database                               | 48 kB
>  pg_catalog.pg_authid                                 | 48 kB
>  pg_catalog.pg_ts_config_map                          | 48 kB
>  pg_catalog.pg_opfamily                               | 48 kB
>  pg_catalog.pg_language                               | 40 kB
>  pg_catalog.pg_shdepend                               | 40 kB
>  pg_catalog.pg_ts_parser                              | 40 kB
>  pg_catalog.pg_ts_template                            | 40 kB
>  pg_catalog.pg_auth_members                           | 40 kB
>  pg_catalog.pg_ts_config                              | 40 kB
>  pg_catalog.pg_ts_dict                                | 40 kB
>  pg_catalog.pg_operator_oid_index                     | 40 kB
>  pg_catalog.pg_tablespace                             | 40 kB
>  pg_catalog.pg_namespace                              | 40 kB
>  pg_catalog.pg_am                                     | 40 kB
>  pg_toast.pg_toast_45054                              | 32 kB
>  pg_catalog.pg_conversion_name_nsp_index              | 32 kB
>  pg_catalog.pg_shdescription                          | 32 kB
>  tap_schema.tables                                    | 32 kB
>  pg_catalog.pg_statistic_relid_att_index              | 32 kB
>  pg_catalog.pg_amop_fam_strat_index                   | 32 kB
>  tap_schema.schemas                                   | 32 kB
>  pg_catalog.pg_amproc_fam_proc_index                  | 32 kB
>  pg_catalog.pg_ts_config_map_index                    | 32 kB
>  tap_schema.columns                                   | 32 kB
>  caom.plane_phi1                                      | 32 kB
>  pg_catalog.pg_amop_oid_index                         | 32 kB
>  pg_catalog.pg_amop_opr_fam_index                     | 32 kB
>  pg_catalog.pg_opclass_am_name_nsp_index              | 32 kB
>  caom.deletedplane                                    | 24 kB
>  pg_catalog.pg_pltemplate                             | 24 kB
>  pg_catalog.pg_attrdef                                | 24 kB
>  tap_schema.keys                                      | 24 kB
>  caom.deletedspatialentity                            | 24 kB
>  caom.deletedpolarizationentity                       | 24 kB
>  caom.compositeobservation                            | 24 kB
>  caom.deletedtemporalentity                           | 24 kB
>  caom.deletedartifact                                 | 24 kB
>  caom.deletedspectralentity                           | 24 kB
>  pg_catalog.pg_aggregate                              | 24 kB
>  information_schema.sql_implementation_info           | 16 kB
>  information_schema.sql_languages                     | 16 kB
>  pg_catalog.pg_enum                                   | 16 kB
>  pg_catalog.pg_ts_config_oid_index                    | 16 kB
>  pg_catalog.pg_trigger_oid_index                      | 16 kB
>  tap_schema.schemas_pkey                              | 16 kB
>  pg_catalog.pg_constraint_conrelid_index              | 16 kB
>  pg_catalog.pg_conversion_default_index               | 16 kB
>  pg_catalog.pg_namespace_oid_index                    | 16 kB
>  pg_catalog.pg_namespace_nspname_index                | 16 kB
>  pg_catalog.pg_cast_source_target_index               | 16 kB
>  pg_catalog.pg_constraint_contypid_index              | 16 kB
>  information_schema.sql_packages                      | 16 kB
>  pg_catalog.pg_aggregate_fnoid_index                  | 16 kB
>  pg_catalog.pg_rewrite_oid_index                      | 16 kB
>  pg_toast.pg_toast_2618_index                         | 16 kB
>  pg_catalog.pg_trigger_tgconstrname_index             | 16 kB
>  pg_catalog.pg_trigger_tgrelid_tgname_index           | 16 kB
>  information_schema.sql_parts                         | 16 kB
>  pg_catalog.pg_authid_rolname_index                   | 16 kB
>  pg_catalog.pg_authid_oid_index                       | 16 kB
>  pg_catalog.pg_auth_members_role_member_index         | 16 kB
>  pg_catalog.pg_auth_members_member_role_index         | 16 kB
>  information_schema.sql_sizing                        | 16 kB
>  tap_schema.keys_pkey                                 | 16 kB
>  tap_schema.columns_pkey                              | 16 kB
>  caom.deletedplane_i1                                 | 16 kB
>  pg_catalog.pg_database_datname_index                 | 16 kB
>  pg_catalog.pg_database_oid_index                     | 16 kB
>  caom.deletedpolarizationentity_i1                    | 16 kB
>  pg_catalog.pg_shdescription_o_c_index                | 16 kB
>  pg_catalog.pg_language_name_index                    | 16 kB
>  pg_catalog.pg_language_oid_index                     | 16 kB
>  pg_catalog.pg_index_indrelid_index                   | 16 kB
>  caom.timesample                                      | 16 kB
>  pg_catalog.pg_am_name_index                          | 16 kB
>  pg_toast.pg_toast_2619_index                         | 16 kB
>  pg_catalog.pg_constraint_conname_nsp_index           | 16 kB
>  pg_catalog.pg_index_indexrelid_index                 | 16 kB
>  pg_catalog.pg_am_oid_index                           | 16 kB
>  pg_catalog.pg_pltemplate_name_index                  | 16 kB
>  pg_catalog.pg_shdepend_depender_index                | 16 kB
>  pg_catalog.pg_shdepend_reference_index               | 16 kB
>  pg_catalog.pg_tablespace_oid_index                   | 16 kB
>  pg_catalog.pg_tablespace_spcname_index               | 16 kB
>  pg_toast.pg_toast_45054_index                        | 16 kB
>  caom.deletedtemporalentity_i1                        | 16 kB
>  pg_catalog.pg_ts_dict_oid_index                      | 16 kB
>  caom.deletedspectralentity_i1                        | 16 kB
>  caom.deletedspatialentity_i1                         | 16 kB
>  caom.deletedartifact_i1                              | 16 kB
>  pg_catalog.pg_constraint_oid_index                   | 16 kB
>  pg_catalog.pg_opfamily_am_name_nsp_index             | 16 kB
>  pg_catalog.pg_opfamily_oid_index                     | 16 kB
>  pg_catalog.pg_opclass_oid_index                      | 16 kB
>  pg_catalog.pg_amproc_oid_index                       | 16 kB
>  pg_catalog.pg_class_oid_index                        | 16 kB
>  pg_catalog.pg_rewrite_rel_rulename_index             | 16 kB
>  pg_catalog.pg_ts_config_cfgname_index                | 16 kB
>  tap_schema.tables_pkey                               | 16 kB
>
>
> Thank you
> Isabella
>
>
> Hi All,
>
> Please,  see more info my env:  PG 8.3.6 on RHE5-64bits.
>
> 1. there are more than one schemas, but the size of the tables is close to
> 30-40kB, see some samples
> schemaname |  tablename  | size_pretty | total_size_pretty
> ------------+-------------+-------------+-------------------
> tap_schema | tables      | 8192 bytes  | 32 kB
> tap_schema | columns     | 8192 bytes  | 32 kB
> tap_schema | schemas     | 8192 bytes  | 32 kB
> tap_schema | keys        | 8192 bytes  | 24 kB
> tap_schema | key_columns | 8192 bytes  | 8192 bytes
>
> 2.   There are no BLOB's  data type in db at this time :
> cvodb=# select * from pg_largeobject;
> loid | pageno | data
>
> ------+--------+------
> (0 rows)
>
>
> 3. As  Tom suggested ,  I  excluded the table space restriction  and
> changed  to pg_total_relation_size  my  original SQL :
>
>
> SELECT 'the table  size without table space restrictions';
> SELECT nspname || '.' || relname AS
> "relation",pg_size_pretty(pg_total_relation_size(nspname || '.' ||
> relname)) AS "s
> ize"
>  FROM pg_class C
>  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
>  ORDER BY pg_relation_size(nspname || '.' || relname) DESC
>  LIMIT 20;
> the table  size without table space restrictions
> (1 row)
>
>         relation          |  size
> ---------------------------+---------
> caom.spatialentity        | 3216 MB
> caom.artifact             | 2150 MB
> caom.plane                | 677 MB
> caom.artifact_i1          | 171 MB
> caom.simpleobservation    | 202 MB
> caom.spatialentity_i1     | 162 MB
> caom.positionsample       | 219 MB
> caom.plane_psi2           | 86 MB
> caom.temporalentity       | 86 MB
> caom.spectralentity       | 73 MB
> caom.plane_energy_i1      | 67 MB
> caom.plane_time_i1        | 58 MB
> caom.plane_position_i2    | 48 MB
> caom.metric               | 70 MB
> caom.polarizationentity   | 33 MB
> caom.simpleobservation_i2 | 25 MB
> caom.plane_psi1           | 23 MB
> caom.metric_i2            | 18 MB
> caom.metric_i1            | 18 MB
> caom.plane_position_i3    | 15 MB
> (20 rows)
>
>
> 4. Where are the  rest of 5,5 GB been used ? How can I get the system
> catalog   correct size ?
>
>
>
> Thank you,
> Isabella
>
> Tom Lane wrote:
>>
>> Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca> writes:
>> > I'm trying to understand why  there are  GB's difference  when checking
>> > for db size using pg_size_pretty()  and  querying for  tables + indexes
>> > size. .
>>
>> You are not counting everything --- the total DB size is clearly 12GB,
>> so the question is where are the other 5.5GB?  Your first query shows
>> that schema caom accounts for 6+GB, but the second one does not prove
>> that schema caom contains all the big hogs.  My guesses are:
>>
>> 1. Toast tables for tables that aren't in caom --- you used
>> pg_relation_size not pg_total_relation_size, and excluded toast
>> tables, so you are missing those.
>>
>> 2. pg_largeobject ... got any large objects?
>>
>> 3. Bloat in other system catalogs.  5GB of catalog bloat would be
>> pretty awful, but maybe that's what it is.
>>
>> Try that last query without the namespace restrictions.
>>
>
>
>>
>>                         regards, tom lane
>
> Hi Pg Admin list.
> I'm trying to understand why  there are  GB's difference  when checking
> for db size using pg_size_pretty()  and  querying for  tables + indexes
> size. .
> The  sum  of tables +index sizes is  showing as aprox 6.5GB and
> pg_size_pretty(dbname) is coming as 12GB, this  are the results after a
> full vacuum and reindexdb,  also the sum of OS db files size is ~ 6.5GB.
> Any tips what I'm missing : are some  "unallocated" db pages or anything
> else ?
>
> select pg_size_pretty(pg_database_size('db1'));
> pg_size_pretty
> ----------------
> 12 GB
> (1 row)
>  *** Check for tables size :
> SELECT schemaname, tablename,
> pg_size_pretty(size) AS size_pretty,
> pg_size_pretty(total_size) AS total_size_pretty
> FROM (SELECT *,
> pg_relation_size(schemaname||'.'||tablename) AS
> size,pg_total_relation_size(schemaname||'.'||tablename) AS
> total_size FROM pg_tables) AS TABLES where schemaname='caom'
> ORDER BY total_size DESC;
> schemaname |          tablename          | size_pretty | total_size_pretty
> ------------+-----------------------------+-------------+-------------------
> caom       | spatialentity               | 3053 MB     | 3216 MB
> caom       | artifact                    | 1979 MB     | 2150 MB
> caom       | plane                       | 413 MB      | 677 MB
> caom       | positionsample              | 110 MB      | 219 MB
> caom       | simpleobservation           | 165 MB      | 202 MB
> caom       | temporalentity              | 77 MB       | 86 MB
> caom       | spectralentity              | 68 MB       | 73 MB
> caom       | metric                      | 33 MB       | 70 MB
> caom       | polarizationentity          | 29 MB       | 33 MB
> caom       | harvestskip                 | 1576 kB     | 2056 kB
> caom       | harveststate                | 840 kB      | 856 kB
> caom       | positionhole                | 48 kB       | 584 kB
>
>
>
>
> *** OR  : check for the biggest tables+index size:
> SELECT ' Top 20  biggest tables and indexes'
> ;
> SELECT nspname || '.' || relname AS "relation",
>    pg_size_pretty(pg_relation_size(nspname || '.' || relname)) AS "size"
>  FROM pg_class C
>  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
>  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
>    AND nspname !~ '^pg_toast'
>    AND pg_relation_size(nspname || '.' || relname)>0
>  ORDER BY pg_relation_size(nspname || '.' || relname) DESC
>  LIMIT 20;
> (1 row)
>
>         relation          |      size
> ---------------------------+---------
> caom.spatialentity        |  3053 MB
> caom.artifact             |   1979 MB
> caom.plane                |    413 MB
> caom.artifact_i1          |  171 MB
> caom.simpleobservation    | 165 MB
> caom.spatialentity_i1     | 162 MB
> caom.positionsample       | 110 MB
> caom.plane_psi2           | 86 MB
> caom.temporalentity       | 77 MB
> caom.spectralentity       | 68 MB
> caom.plane_energy_i1      | 67 MB
> caom.plane_time_i1        | 58 MB
> caom.plane_position_i2    | 48 MB
> caom.metric               | 33 MB
> caom.polarizationentity   | 29 MB
> caom.simpleobservation_i2 | 25 MB
> caom.plane_psi1           | 23 MB
> caom.metric_i2            | 18 MB
>
> Thank you
> Isabella
>
>
>

--
View this message in context:
http://www.nabble.com/Re%3A--ADMIN--db-size-and-tables-size-difference-tp25531211p25531314.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


Re: db size and tables size difference

From
Tom Lane
Date:
Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca> writes:
> The issue may be   with pg_size_pretty()  results, I don't have details
> knowledge of this function.

I doubt it, that's a pretty simple function ... but if you don't trust
it, just remove the pg_size_pretty call and look directly at the output
of the size functions.

> select pg_size_pretty(pg_database_size('db1'));

Just to double check here ... you're sure you're naming the correct
database in this call?

            regards, tom lane

db size and tables size difference

From
Isabella Ghiurea
Date:
Tom Lane wrote:
>
> Isabella Ghiurea <isabella.ghiurea@nrc-cnrc.gc.ca> writes:
> > The issue may be   with pg_size_pretty()  results, I don't have details
> > knowledge of this function.
>
> I doubt it, that's a pretty simple function ... but if you don't trust
> it, just remove the pg_size_pretty call and look directly at the output
> of the size functions.
>
Hi Tom,
pg_tablespace_size() function shows same size as tables size SQL
statements close to 6.5 GB , here is output for the  bigest tablespace
select pg_tablespace_size('caom_data');
 pg_tablespace_size
--------------------
         6218108932
select pg_tablespace_size('caom_index');
 pg_tablespace_size
--------------------
          837201924
select pg_tablespace_size('tap_data');
 pg_tablespace_size
--------------------
              65689

select pg_database_size('cvodb');
 pg_database_size
------------------
      13279260268
How can I get more details of  how pg_database_size()  is been executed ?
>
>
> > select pg_size_pretty(pg_database_size('db1'));
>
> Just to double check here ... you're sure you're naming the correct
> database in this call?
>
yes
Thank you
Isabella
>
>                         regards, tom lane
>


--
-----------------------------------------------------------
Isabella A. Ghiurea

Isabella.Ghiurea@nrc-cnrc.gc.ca
Canadian Astronomy Data Centre | http://www1.cadc-ccda.hia-iha.nrc-cnrc.gc.ca/
National Research Council of Canada, Herzberg Institute of Astrophysics
5071 West Saanich Road, Victoria BC V9E 2E7, Canada
Phone: 250 363-3446 fax: 250 363-0045


Slony-I - Update trigger on a replicated table

From
"Plugge, Joe R."
Date:
Anyone know if it is possible to create an update trigger on the destination side of a replicated table in Slony-I and
havethe replication itself cause the trigger to fire? I was able to add the trigger but when run the test transaction
through,it does not seem to be firing.  It works on the non-replicated version of the solution. 

Running postgres 8.4.1 and Slony-I 2.0.3


Re: Slony-I - Update trigger on a replicated table

From
"Plugge, Joe R."
Date:
Nevermind ... I RTFM ...

echo "alter table mytable ENABLE REPLICA TRIGGER check_lockout;" | psql mydb

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Plugge, Joe R.
Sent: Thursday, May 13, 2010 4:02 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Slony-I - Update trigger on a replicated table

Anyone know if it is possible to create an update trigger on the destination side of a replicated table in Slony-I and
havethe replication itself cause the trigger to fire? I was able to add the trigger but when run the test transaction
through,it does not seem to be firing.  It works on the non-replicated version of the solution. 

Running postgres 8.4.1 and Slony-I 2.0.3


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Slony-I - Update trigger on a replicated table

From
chirag.dave@gmail.com
Date:


On Thu, May 13, 2010 at 5:11 PM, Plugge, Joe R. <JRPlugge@west.com> wrote:
Nevermind ... I RTFM ...

echo "alter table mytable ENABLE REPLICA TRIGGER check_lockout;" | psql mydb

This is not good idea. Always run DDL script using Slony execute script.

http://www.slony.info/documentation/ddlchanges.html

 

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Plugge, Joe R.
Sent: Thursday, May 13, 2010 4:02 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Slony-I - Update trigger on a replicated table

Anyone know if it is possible to create an update trigger on the destination side of a replicated table in Slony-I and have the replication itself cause the trigger to fire? I was able to add the trigger but when run the test transaction through, it does not seem to be firing.  It works on the non-replicated version of the solution.

Running postgres 8.4.1 and Slony-I 2.0.3


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Slony - I question

From
"Plugge, Joe R."
Date:

I am using slony1-2.0.3 and needed to make some wholesale changes, so like other times I stopped all slon processes on my master box as well as on the 2 slave machines.  I then dropped the slony schema on all three, completely removing it, made my changes.  I then ran my slonik script at the master, started up all of the slon processes and ran my subscribe script on both slaves.  My problem, is that only one of the slaves got the copy and synched, the other one just hangs at this point:

 

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=1 li_receiver=3 li_provider=1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=2 li_receiver=3 li_provider=1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=1 li_receiver=3 li_provider=1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=2 li_receiver=3 li_provider=1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=1 li_receiver=3 li_provider=1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=2 li_receiver=3 li_provider=1

2010-06-23 03:43:19 EDT CONFIG remoteWorkerThread_1: update provider configuration

2010-06-23 03:44:01 EDT CONFIG storeSubscribe: sub_set=1 sub_provider=1 sub_forward='f'

NOTICE:  subscribe set: omit_copy=f

2010-06-23 03:44:02 EDT CONFIG storeListen: li_origin=1 li_receiver=3 li_provider=1

2010-06-23 03:44:02 EDT CONFIG storeListen: li_origin=2 li_receiver=3 li_provider=1

2010-06-23 03:44:02 EDT INFO   copy_set 1 - omit=f - bool=0

2010-06-23 03:44:02 EDT INFO   omit is FALSE

2010-06-23 03:44:02 EDT CONFIG version for "dbname=holly host=linux2276 port=5432 user=slony" is 80401

2010-06-23 03:44:02 EDT CONFIG remoteWorkerThread_1: connected to provider DB

2010-06-23 03:44:02 EDT CONFIG remoteWorkerThread_1: prepare to copy table "public"."hlm_owners"

 

I query the pg_stat_activity table and see that the table in question has been locked ....

 

I am using postgres 8.4.1.  This worked many times before, so I am wondering if I am running into some new condition.

Just wondering what else to possible check for?

 

joe

Re: Slony - I question

From
"Plugge, Joe R."
Date:

Found the culprit, the app had thrown locks on the first table in the replicate set and it was waiting for those locks to be released.

 

From: Plugge, Joe R.
Sent: Wednesday, June 23, 2010 12:23 PM
To: pgsql-admin@postgresql.org
Subject: Slony - I question

 

I am using slony1-2.0.3 and needed to make some wholesale changes, so like other times I stopped all slon processes on my master box as well as on the 2 slave machines.  I then dropped the slony schema on all three, completely removing it, made my changes.  I then ran my slonik script at the master, started up all of the slon processes and ran my subscribe script on both slaves.  My problem, is that only one of the slaves got the copy and synched, the other one just hangs at this point:

 

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=1 li_receiver=3 li_provider=1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=2 li_receiver=3 li_provider=1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=1 li_receiver=3 li_provider=1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=2 li_receiver=3 li_provider=1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=1 li_receiver=3 li_provider=1

2010-06-23 03:43:18 EDT CONFIG storeListen: li_origin=2 li_receiver=3 li_provider=1

2010-06-23 03:43:19 EDT CONFIG remoteWorkerThread_1: update provider configuration

2010-06-23 03:44:01 EDT CONFIG storeSubscribe: sub_set=1 sub_provider=1 sub_forward='f'

NOTICE:  subscribe set: omit_copy=f

2010-06-23 03:44:02 EDT CONFIG storeListen: li_origin=1 li_receiver=3 li_provider=1

2010-06-23 03:44:02 EDT CONFIG storeListen: li_origin=2 li_receiver=3 li_provider=1

2010-06-23 03:44:02 EDT INFO   copy_set 1 - omit=f - bool=0

2010-06-23 03:44:02 EDT INFO   omit is FALSE

2010-06-23 03:44:02 EDT CONFIG version for "dbname=holly host=linux2276 port=5432 user=slony" is 80401

2010-06-23 03:44:02 EDT CONFIG remoteWorkerThread_1: connected to provider DB

2010-06-23 03:44:02 EDT CONFIG remoteWorkerThread_1: prepare to copy table "public"."hlm_owners"

 

I query the pg_stat_activity table and see that the table in question has been locked ....

 

I am using postgres 8.4.1.  This worked many times before, so I am wondering if I am running into some new condition.

Just wondering what else to possible check for?

 

joe