How to Clean the postgre database used by foreman?

1113 · October 31, 2015, 6:35am

We run puppet, foreman and postgre on a server for several months.Now the
size of the postgre database directory "main/base" is too large to make the
linux root part almost full.
How can i decrease the database or can i move it to somewhere else with symbolic
link？Thanks

Shimon_Shtein · November 1, 2015, 12:21pm

Hello,

First, what version of Foreman do you use?
Second: let's determine which DB causes the problem:
I have to warn, that before messing with the DB, please make sure that you
have a backup for it just in case.

Can you run this command please?

> psql -c "SELECT pg_database.datname,
> pg_database_size(pg_database.datname),
> pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database
> ORDER BY pg_database_size DESC;"
>
It should return an ordered list of databases and their disk usage. (Source
for the script
<http://www.commandlinefu.com/commands/view/9792/list-all-databases-in-postgres-and-their-bytehuman-sizes-ordering-by-byte-size-descending>
)

If you see foreman's one on top, you can investigate further by running:

SELECT relname, relpages
> FROM pg_class
> ORDER BY relpages DESC;

It should return tables, and how much space they take. (Source for the
script <http://www.postgresql.org/docs/9.1/static/disk-usage.html>)

Please post us the results, and we will try to investigate it further.

Thanks,
Shimon.

···

On Saturday, October 31, 2015 at 10:38:33 AM UTC+2, 韩雨哲 wrote: > > We run puppet, foreman and postgre on a server for several months.Now the > size of the postgre database directory "main/base" is too large to make the > linux root part almost full. > How can i decrease the database or can i move it to somewhere else with symbolic > link？Thanks >

1113 · November 2, 2015, 2:40am

Foreman version is 1.3.1
The first command return:
datname | pg_database_size | pg_size_pretty

···

-----------+------------------+---------------- foreman | 83453764408 | 78 GB postgres | 6087480 | 5945 kB template1 | 5980676 | 5841 kB template0 | 5980676 | 5841 kB

在 2015年11月1日星期日 UTC+8下午8:21:21，ssh…@redhat.com写道：

Hello,

First, what version of Foreman do you use?
Second: let’s determine which DB causes the problem:
I have to warn, that before messing with the DB, please make sure that you
have a backup for it just in case.

Can you run this command please?

psql -c “SELECT pg_database.datname,
pg_database_size(pg_database.datname),
pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database
ORDER BY pg_database_size DESC;”

It should return an ordered list of databases and their disk usage. (Source
for the script
http://www.commandlinefu.com/commands/view/9792/list-all-databases-in-postgres-and-their-bytehuman-sizes-ordering-by-byte-size-descending
)

If you see foreman’s one on top, you can investigate further by running:

SELECT relname, relpages

FROM pg_class
ORDER BY relpages DESC;

It should return tables, and how much space they take. (Source for the
script http://www.postgresql.org/docs/9.1/static/disk-usage.html)

Please post us the results, and we will try to investigate it further.

Thanks,
Shimon.

On Saturday, October 31, 2015 at 10:38:33 AM UTC+2, 韩雨哲 wrote:

We run puppet, foreman and postgre on a server for several months.Now the
size of the postgre database directory “main/base” is too large to make the
linux root part almost full.
How can i decrease the database or can i move it to somewhere else with symbolic
link？Thanks

Shimon_Shtein · November 2, 2015, 10:14am

Can you please rerun the last query under the foreman database? You have
run it under the system one, try to run it like this:
psql -c "SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;" -d
foreman

Thanks,
Shim

···

On Mon, Nov 2, 2015 at 4:40 AM, 韩雨哲 wrote:

Foreman version is 1.3.1
The first command return:
datname | pg_database_size | pg_size_pretty
-----------±-----------------±---------------
foreman | 83453764408 | 78 GB
postgres | 6087480 | 5945 kB
template1 | 5980676 | 5841 kB
template0 | 5980676 | 5841 kB

The next command return (Same with the newly installed server):
relname | relpages
-----------------------------------------±---------
pg_proc | 57
pg_depend | 44
pg_attribute | 40
pg_toast_2618 | 33
pg_description | 29
pg_depend_depender_index | 27
pg_depend_reference_index | 26
pg_proc_proname_args_nsp_index | 23
pg_attribute_relid_attnam_index | 15
pg_description_o_c_o_index | 15
pg_statistic | 14
pg_operator | 13
pg_rewrite | 11
pg_attribute_relid_attnum_index | 11
pg_proc_oid_index | 9
pg_class | 8
pg_type | 7
sql_features | 7
pg_operator_oprname_l_r_n_index | 5
pg_type_typname_nsp_index | 5
pg_amop_opr_fam_index | 4
pg_amop_fam_strat_index | 4
pg_class_relname_nsp_index | 4
pg_ts_config_map_index | 4
pg_operator_oid_index | 4
pg_statistic_relid_att_inh_index | 4
pg_amop | 3
pg_index | 3
pg_conversion | 3
pg_cast | 2
pg_language_oid_index | 2
pg_namespace_nspname_index | 2
pg_type_oid_index | 2

在 2015年11月1日星期日 UTC+8下午8:21:21，ssh…@redhat.com写道：

Hello,

First, what version of Foreman do you use?
Second: let’s determine which DB causes the problem:
I have to warn, that before messing with the DB, please make sure that
you have a backup for it just in case.

Can you run this command please?

psql -c “SELECT pg_database.datname,
pg_database_size(pg_database.datname),
pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database
ORDER BY pg_database_size DESC;”

It should return an ordered list of databases and their disk usage. (Source
for the script
http://www.commandlinefu.com/commands/view/9792/list-all-databases-in-postgres-and-their-bytehuman-sizes-ordering-by-byte-size-descending
)

If you see foreman’s one on top, you can investigate further by running:

SELECT relname, relpages

FROM pg_class
ORDER BY relpages DESC;

It should return tables, and how much space they take. (Source for the
script http://www.postgresql.org/docs/9.1/static/disk-usage.html)

Please post us the results, and we will try to investigate it further.

Thanks,
Shimon.

On Saturday, October 31, 2015 at 10:38:33 AM UTC+2, 韩雨哲 wrote:

We run puppet, foreman and postgre on a server for several months.Now
the size of the postgre database directory “main/base” is too large to make
the linux root part almost full.
How can i decrease the database or can i move it to somewhere else with symbolic
link？Thanks

–
You received this message because you are subscribed to a topic in the
Google Groups “Foreman users” group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/foreman-users/mXyMIXLLK4Y/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
foreman-users+unsubscribe@googlegroups.com.
To post to this group, send email to foreman-users@googlegroups.com.
Visit this group at http://groups.google.com/group/foreman-users.
For more options, visit https://groups.google.com/d/optout.

1113 · November 2, 2015, 10:30am

relname | relpages

···

在 2015年11月2日星期一 UTC+8下午6:14:15，Shimon Shtein写道：

Can you please rerun the last query under the foreman database? You have
run it under the system one, try to run it like this:
psql -c “SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;”
-d foreman

Thanks,
Shim

On Mon, Nov 2, 2015 at 4:40 AM, 韩雨哲 <hyz...@gmail.com <javascript:>> > wrote:

Foreman version is 1.3.1
The first command return:
datname | pg_database_size | pg_size_pretty
-----------±-----------------±---------------
foreman | 83453764408 | 78 GB
postgres | 6087480 | 5945 kB
template1 | 5980676 | 5841 kB
template0 | 5980676 | 5841 kB

The next command return (Same with the newly installed server):
relname | relpages
-----------------------------------------±---------
pg_proc | 57
pg_depend | 44
pg_attribute | 40
pg_toast_2618 | 33
pg_description | 29
pg_depend_depender_index | 27
pg_depend_reference_index | 26
pg_proc_proname_args_nsp_index | 23
pg_attribute_relid_attnam_index | 15
pg_description_o_c_o_index | 15
pg_statistic | 14
pg_operator | 13
pg_rewrite | 11
pg_attribute_relid_attnum_index | 11
pg_proc_oid_index | 9
pg_class | 8
pg_type | 7
sql_features | 7
pg_operator_oprname_l_r_n_index | 5
pg_type_typname_nsp_index | 5
pg_amop_opr_fam_index | 4
pg_amop_fam_strat_index | 4
pg_class_relname_nsp_index | 4
pg_ts_config_map_index | 4
pg_operator_oid_index | 4
pg_statistic_relid_att_inh_index | 4
pg_amop | 3
pg_index | 3
pg_conversion | 3
pg_cast | 2
pg_language_oid_index | 2
pg_namespace_nspname_index | 2
pg_type_oid_index | 2

在 2015年11月1日星期日 UTC+8下午8:21:21，ssh…@redhat.com写道：

Hello,

First, what version of Foreman do you use?
Second: let’s determine which DB causes the problem:
I have to warn, that before messing with the DB, please make sure that
you have a backup for it just in case.

Can you run this command please?

psql -c “SELECT pg_database.datname,
pg_database_size(pg_database.datname),
pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database
ORDER BY pg_database_size DESC;”

It should return an ordered list of databases and their disk usage. (Source
for the script
http://www.commandlinefu.com/commands/view/9792/list-all-databases-in-postgres-and-their-bytehuman-sizes-ordering-by-byte-size-descending
)

If you see foreman’s one on top, you can investigate further by running:

SELECT relname, relpages

FROM pg_class
ORDER BY relpages DESC;

It should return tables, and how much space they take. (Source for the
script http://www.postgresql.org/docs/9.1/static/disk-usage.html)

Please post us the results, and we will try to investigate it further.

Thanks,
Shimon.

On Saturday, October 31, 2015 at 10:38:33 AM UTC+2, 韩雨哲 wrote:

We run puppet, foreman and postgre on a server for several months.Now
the size of the postgre database directory “main/base” is too large to make
the linux root part almost full.
How can i decrease the database or can i move it to somewhere else with symbolic
link？Thanks

–
You received this message because you are subscribed to a topic in the
Google Groups “Foreman users” group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/foreman-users/mXyMIXLLK4Y/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
foreman-user...@googlegroups.com <javascript:>.
To post to this group, send email to forema...@googlegroups.com
<javascript:>.
Visit this group at http://groups.google.com/group/foreman-users.
For more options, visit https://groups.google.com/d/optout.

Shimon_Shtein · November 2, 2015, 12:27pm

OK I see.

First, Foreman 1.3.1 is quite old, you should consider upgrading it. At
least you should upgrade to the latest 1.3.2 to get all minor bug fixes for
this version.

And more specific to your problem: as you can see, most of your disk space
is consumed by the messages table. This is a part of reports mechanism in
Foreman.
Please refer to
http://www.theforeman.org/manuals/1.3/index.html#3.5.4PuppetReports and
specifically to "Expire reports automatically" section there.
You can run the "foreman-rake" tasks manually too, but it will take pretty
long time.

And again I must warn you: Before you mess with your database, please
create a backup just in case.

Just out of pure curiosity, how many hosts are you managing in this foreman
instance?

Thanks,
Shim.

···

On Monday, November 2, 2015 at 12:30:52 PM UTC+2, 韩雨哲 wrote: > > relname | relpages > ---------------------------------------------------------+---------- > messages | 6417728 > index_messages_on_digest | 2162932 > messages_pkey | 549038 > index_logs_on_report_id | 231603 > index_logs_on_level_id | 230926 > index_logs_on_message_id | 189293 > logs_pkey | 182212 > pg_toast_16836 | 91432 > fact_values | 3498 > hosts | 3346 > pg_toast_16836_index | 1500 > trend_counters | 1292 > index_fact_values_on_fact_name_id | 1196 > index_fact_values_on_host_id | 1020 > fact_values_pkey | 1004 > index_fact_values_on_fact_name_id_and_host_id | 965 > index_hosts_on_name | 816 > index_hosts_on_certname | 797 > sources | 682 > index_hosts_on_type | 647 > index_sources_on_digest | 627 > index_trend_counters_on_trend_id | 609 > index_hosts_on_last_report | 604 > index_sessions_on_session_id | 601 > index_hosts_on_installed_at | 498 > host_medium_id_ix | 491 > index_hosts_on_puppet_status | 485 > host_env_id_ix | 484 > trend_counters_pkey | 484 > host_os_id_ix | 481 > index_hosts_on_source_file_id | 480 > host_domain_id_ix | 477 > host_arch_id_ix | 472 > Thanks. > > > 在 2015年11月2日星期一 UTC+8下午6:14:15，Shimon Shtein写道： >> >> Can you please rerun the last query under the foreman database? You have >> run it under the system one, try to run it like this: >> psql -c "SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;" >> -d foreman >> >> Thanks, >> Shim >> >> On Mon, Nov 2, 2015 at 4:40 AM, 韩雨哲 wrote: >> >>> Foreman version is 1.3.1 >>> The first command return: >>> datname | pg_database_size | pg_size_pretty >>> -----------+------------------+---------------- >>> foreman | 83453764408 | 78 GB >>> postgres | 6087480 | 5945 kB >>> template1 | 5980676 | 5841 kB >>> template0 | 5980676 | 5841 kB >>> >>> The next command return (Same with the newly installed server): >>> relname | relpages >>> -----------------------------------------+---------- >>> pg_proc | 57 >>> pg_depend | 44 >>> pg_attribute | 40 >>> pg_toast_2618 | 33 >>> pg_description | 29 >>> pg_depend_depender_index | 27 >>> pg_depend_reference_index | 26 >>> pg_proc_proname_args_nsp_index | 23 >>> pg_attribute_relid_attnam_index | 15 >>> pg_description_o_c_o_index | 15 >>> pg_statistic | 14 >>> pg_operator | 13 >>> pg_rewrite | 11 >>> pg_attribute_relid_attnum_index | 11 >>> pg_proc_oid_index | 9 >>> pg_class | 8 >>> pg_type | 7 >>> sql_features | 7 >>> pg_operator_oprname_l_r_n_index | 5 >>> pg_type_typname_nsp_index | 5 >>> pg_amop_opr_fam_index | 4 >>> pg_amop_fam_strat_index | 4 >>> pg_class_relname_nsp_index | 4 >>> pg_ts_config_map_index | 4 >>> pg_operator_oid_index | 4 >>> pg_statistic_relid_att_inh_index | 4 >>> pg_amop | 3 >>> pg_index | 3 >>> pg_conversion | 3 >>> pg_cast | 2 >>> pg_language_oid_index | 2 >>> pg_namespace_nspname_index | 2 >>> pg_type_oid_index | 2 >>> >>> >>> >>> 在 2015年11月1日星期日 UTC+8下午8:21:21，ssh...@redhat.com写道： >>> >>>> Hello, >>>> >>>> First, what version of Foreman do you use? >>>> Second: let's determine which DB causes the problem: >>>> I have to warn, that before messing with the DB, please make sure that >>>> you have a backup for it just in case. >>>> >>>> Can you run this command please? >>>> >>>>> psql -c "SELECT pg_database.datname, >>>>> pg_database_size(pg_database.datname), >>>>> pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database >>>>> ORDER BY pg_database_size DESC;" >>>>> >>>> It should return an ordered list of databases and their disk usage. (Source >>>> for the script >>>> >>>> ) >>>> >>>> If you see foreman's one on top, you can investigate further by running: >>>> >>>> SELECT relname, relpages >>>>> FROM pg_class >>>>> ORDER BY relpages DESC; >>>> >>>> >>>> It should return tables, and how much space they take. (Source for the >>>> script ) >>>> >>>> Please post us the results, and we will try to investigate it further. >>>> >>>> Thanks, >>>> Shimon. >>>> >>>> >>>> >>>> On Saturday, October 31, 2015 at 10:38:33 AM UTC+2, 韩雨哲 wrote: >>>>> >>>>> We run puppet, foreman and postgre on a server for several months.Now >>>>> the size of the postgre database directory "main/base" is too large to make >>>>> the linux root part almost full. >>>>> How can i decrease the database or can i move it to somewhere else >>>>> with symbolic link？Thanks >>>>> >>>> -- >>> You received this message because you are subscribed to a topic in the >>> Google Groups "Foreman users" group. >>> To unsubscribe from this topic, visit >>> https://groups.google.com/d/topic/foreman-users/mXyMIXLLK4Y/unsubscribe. >>> To unsubscribe from this group and all its topics, send an email to >>> foreman-user...@googlegroups.com. >>> To post to this group, send email to forema...@googlegroups.com. >>> Visit this group at http://groups.google.com/group/foreman-users. >>> For more options, visit https://groups.google.com/d/optout. >>> >> >>

1113 · November 3, 2015, 1:57am

OK, thanks.
We are managing about 600 hosts.

在 2015年11月2日星期一 UTC+8下午8:27:57，ssh…@redhat.com写道：

···

> > > OK I see. > > First, Foreman 1.3.1 is quite old, you should consider upgrading it. At > least you should upgrade to the latest 1.3.2 to get all minor bug fixes for > this version. > > And more specific to your problem: as you can see, most of your disk space > is consumed by the messages table. This is a part of reports mechanism in > Foreman. > Please refer to > http://www.theforeman.org/manuals/1.3/index.html#3.5.4PuppetReports and > specifically to "Expire reports automatically" section there. > You can run the "foreman-rake" tasks manually too, but it will take pretty > long time. > > And again I must warn you: Before you mess with your database, please > create a backup just in case. > > Just out of pure curiosity, how many hosts are you managing in this > foreman instance? > > Thanks, > Shim. > > > > On Monday, November 2, 2015 at 12:30:52 PM UTC+2, 韩雨哲 wrote: >> >> relname | relpages >> ---------------------------------------------------------+---------- >> messages | 6417728 >> index_messages_on_digest | 2162932 >> messages_pkey | 549038 >> index_logs_on_report_id | 231603 >> index_logs_on_level_id | 230926 >> index_logs_on_message_id | 189293 >> logs_pkey | 182212 >> pg_toast_16836 | 91432 >> fact_values | 3498 >> hosts | 3346 >> pg_toast_16836_index | 1500 >> trend_counters | 1292 >> index_fact_values_on_fact_name_id | 1196 >> index_fact_values_on_host_id | 1020 >> fact_values_pkey | 1004 >> index_fact_values_on_fact_name_id_and_host_id | 965 >> index_hosts_on_name | 816 >> index_hosts_on_certname | 797 >> sources | 682 >> index_hosts_on_type | 647 >> index_sources_on_digest | 627 >> index_trend_counters_on_trend_id | 609 >> index_hosts_on_last_report | 604 >> index_sessions_on_session_id | 601 >> index_hosts_on_installed_at | 498 >> host_medium_id_ix | 491 >> index_hosts_on_puppet_status | 485 >> host_env_id_ix | 484 >> trend_counters_pkey | 484 >> host_os_id_ix | 481 >> index_hosts_on_source_file_id | 480 >> host_domain_id_ix | 477 >> host_arch_id_ix | 472 >> Thanks. >> >> >> 在 2015年11月2日星期一 UTC+8下午6:14:15，Shimon Shtein写道： >>> >>> Can you please rerun the last query under the foreman database? You have >>> run it under the system one, try to run it like this: >>> psql -c "SELECT relname, relpages FROM pg_class ORDER BY relpages >>> DESC;" -d foreman >>> >>> Thanks, >>> Shim >>> >>> On Mon, Nov 2, 2015 at 4:40 AM, 韩雨哲 wrote: >>> >>>> Foreman version is 1.3.1 >>>> The first command return: >>>> datname | pg_database_size | pg_size_pretty >>>> -----------+------------------+---------------- >>>> foreman | 83453764408 | 78 GB >>>> postgres | 6087480 | 5945 kB >>>> template1 | 5980676 | 5841 kB >>>> template0 | 5980676 | 5841 kB >>>> >>>> The next command return (Same with the newly installed server): >>>> relname | relpages >>>> -----------------------------------------+---------- >>>> pg_proc | 57 >>>> pg_depend | 44 >>>> pg_attribute | 40 >>>> pg_toast_2618 | 33 >>>> pg_description | 29 >>>> pg_depend_depender_index | 27 >>>> pg_depend_reference_index | 26 >>>> pg_proc_proname_args_nsp_index | 23 >>>> pg_attribute_relid_attnam_index | 15 >>>> pg_description_o_c_o_index | 15 >>>> pg_statistic | 14 >>>> pg_operator | 13 >>>> pg_rewrite | 11 >>>> pg_attribute_relid_attnum_index | 11 >>>> pg_proc_oid_index | 9 >>>> pg_class | 8 >>>> pg_type | 7 >>>> sql_features | 7 >>>> pg_operator_oprname_l_r_n_index | 5 >>>> pg_type_typname_nsp_index | 5 >>>> pg_amop_opr_fam_index | 4 >>>> pg_amop_fam_strat_index | 4 >>>> pg_class_relname_nsp_index | 4 >>>> pg_ts_config_map_index | 4 >>>> pg_operator_oid_index | 4 >>>> pg_statistic_relid_att_inh_index | 4 >>>> pg_amop | 3 >>>> pg_index | 3 >>>> pg_conversion | 3 >>>> pg_cast | 2 >>>> pg_language_oid_index | 2 >>>> pg_namespace_nspname_index | 2 >>>> pg_type_oid_index | 2 >>>> >>>> >>>> >>>> 在 2015年11月1日星期日 UTC+8下午8:21:21，ssh...@redhat.com写道： >>>> >>>>> Hello, >>>>> >>>>> First, what version of Foreman do you use? >>>>> Second: let's determine which DB causes the problem: >>>>> I have to warn, that before messing with the DB, please make sure that >>>>> you have a backup for it just in case. >>>>> >>>>> Can you run this command please? >>>>> >>>>>> psql -c "SELECT pg_database.datname, >>>>>> pg_database_size(pg_database.datname), >>>>>> pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database >>>>>> ORDER BY pg_database_size DESC;" >>>>>> >>>>> It should return an ordered list of databases and their disk usage. (Source >>>>> for the script >>>>> >>>>> ) >>>>> >>>>> If you see foreman's one on top, you can investigate further by >>>>> running: >>>>> >>>>> SELECT relname, relpages >>>>>> FROM pg_class >>>>>> ORDER BY relpages DESC; >>>>> >>>>> >>>>> It should return tables, and how much space they take. (Source for >>>>> the script >>>>> ) >>>>> >>>>> Please post us the results, and we will try to investigate it further. >>>>> >>>>> Thanks, >>>>> Shimon. >>>>> >>>>> >>>>> >>>>> On Saturday, October 31, 2015 at 10:38:33 AM UTC+2, 韩雨哲 wrote: >>>>>> >>>>>> We run puppet, foreman and postgre on a server for several months.Now >>>>>> the size of the postgre database directory "main/base" is too large to make >>>>>> the linux root part almost full. >>>>>> How can i decrease the database or can i move it to somewhere else >>>>>> with symbolic link？Thanks >>>>>> >>>>> -- >>>> You received this message because you are subscribed to a topic in the >>>> Google Groups "Foreman users" group. >>>> To unsubscribe from this topic, visit >>>> https://groups.google.com/d/topic/foreman-users/mXyMIXLLK4Y/unsubscribe >>>> . >>>> To unsubscribe from this group and all its topics, send an email to >>>> foreman-user...@googlegroups.com. >>>> To post to this group, send email to forema...@googlegroups.com. >>>> Visit this group at http://groups.google.com/group/foreman-users. >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> >>>