Обсуждение: db size and tables size difference
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
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
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
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 ------------------------------------------------------------------------
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
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
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.
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
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.
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
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
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
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
On Thu, May 13, 2010 at 5:11 PM, Plugge, Joe R. <JRPlugge@west.com> wrote:
This is not good idea. Always run DDL script using Slony execute script.
http://www.slony.info/documentation/ddlchanges.html
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.orgSubject: [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
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
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