/usr/local/bin # report_render.plx --type daily --sendmail
------
product: ASG | Astaro Security Gateway Software
running get_summary
DBD::Pg::st execute failed: ERROR: invalid page header in block 4356 of relation "accounting"
DBD::Pg::st execute failed: ERROR: invalid page header in block 4356 of relation "accounting"

/var/log/system.log

2008:09:04-09:40:20 (none) postgres[13697]: [3-1] LOG: unexpected EOF on client connection
2008:09:04-09:40:21 (none) ulogd[2983]: input key `flow.start.sec' already has source
2008:09:04-09:40:21 (none) ulogd[2983]: stack_fsm_timer_cb: error
2008:09:04-09:40:27 (none) postgres[13737]: [3-1] ERROR: invalid page header in block 4358 of relation "accounting"
2008:09:04-09:40:27 (none) postgres[13737]: [3-2] STATEMENT: INSERT INTO public.accounting
2008:09:04-09:40:27 (none) postgres[13737]: [3-3] (ip_saddr,ip_daddr,ip_protocol,l4_dport,raw_in_pkt len,raw_in_pktcount,raw_out_pktlen,raw_out_pktcoun t,flow_start_day,flow_start
2008:09:04-09:40:27 (none) postgres[13737]: [3-4] _sec,flow_duration) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11);
2008:09:04-09:40:27 (none) ulogd[2983]: pg1: ERROR: invalid page header in block 4358 of relation "accounting"
2008:09:04-09:40:27 (none) ulogd[2983]: pg1: commit failed
2008:09:04-09:40:32 (none) ulogd[2983]: input key `flow.start.sec' already has source
2008:09:04-09:40:32 (none) ulogd[2983]: stack_fsm_timer_cb: error

Hi AndreyS,

we have seen some rare database corruption of the reporting database and tried to make it more robust with the 7.301 up2date, which has been released earliert today.

Can you please install that and check weather it fixes the problem?

thanks
Gert

after update to 7.301 and restart in /var/log/system.log
2008:09:05-09:32:01 (none) /usr/sbin/cron[6013]: (root) CMD ( nice -n19 /usr/local/bin/gen_inline_reporting_data.plx)
2008:09:05-09:32:04 (none) postgres[6018]: [3-1] ERROR: invalid page header in block 1 of relation "imp2p"
2008:09:05-09:32:04 (none) postgres[6018]: [3-2] STATEMENT: SELECT int2ip(srcip) as srcip,transform_common(CAST(sum(count) AS BIGINT)) as sum_pac_str,sum(count) as
2008:09:05-09:32:04 (none) postgres[6018]: [3-3] sum_pac_int,transform_common(count(DISTINCT(protoc ol))) as cnt_proto_str,count(DISTINCT(protocol)) as
2008:09:05-09:32:04 (none) postgres[6018]: [3-4] cnt_proto_int,transform_common(count(DISTINCT(srci p))) as cnt_srcip_str,count(DISTINCT(srcip)) as cnt_srcip_int FROM imp2p_data
2008:09:05-09:32:04 (none) postgres[6018]: [3-5] WHERE dayabs = 733290 AND type LIKE 'im' GROUP BY srcip ORDER BY sum_pac_int DESC
2008:09:05-09:32:04 (none) postgres[6018]: [4-1] ERROR: invalid page header in block 1 of relation "imp2p"
2008:09:05-09:32:04 (none) postgres[6018]: [4-2] STATEMENT: SELECT protocol,transform_common(CAST(sum(count) AS BIGINT)) as sum_pac_str,sum(count) as
2008:09:05-09:32:04 (none) postgres[6018]: [4-3] sum_pac_int,transform_common(count(DISTINCT(dstip) )) as cnt_dstip_str,count(DISTINCT(dstip)) as
2008:09:05-09:32:04 (none) postgres[6018]: [4-4] cnt_dstip_int,transform_common(count(DISTINCT(srci p))) as cnt_srcip_str,count(DISTINCT(srcip)) as
2008:09:05-09:32:04 (none) postgres[6018]: [4-5] cnt_srcip_int,transform_common(count(DISTINCT(prot ocol))) as cnt_proto_str,count(DISTINCT(protocol)) as cnt_proto_int FROM
2008:09:05-09:32:04 (none) postgres[6018]: [4-6] imp2p_data WHERE dayabs = 733290 AND type LIKE 'im' GROUP BY protocol ORDER BY sum_pac_int DESC
2008:09:05-09:32:04 (none) postgres[6018]: [5-1] ERROR: invalid page header in block 1 of relation "imp2p"
2008:09:05-09:32:04 (none) postgres[6018]: [5-2] STATEMENT: SELECT int2ip(srcip) as srcip,transform_common(CAST(sum(count) AS BIGINT)) as sum_pac_str,sum(count) as
2008:09:05-09:32:04 (none) postgres[6018]: [5-3] sum_pac_int,transform_common(count(DISTINCT(protoc ol))) as cnt_proto_str,count(DISTINCT(protocol)) as
2008:09:05-09:32:04 (none) postgres[6018]: [5-4] cnt_proto_int,transform_common(count(DISTINCT(srci p))) as cnt_srcip_str,count(DISTINCT(srcip)) as cnt_srcip_int FROM imp2p_data
2008:09:05-09:32:04 (none) postgres[6018]: [5-5] WHERE dayabs = 733290 AND type LIKE 'p2p' GROUP BY srcip ORDER BY sum_pac_int DESC
2008:09:05-09:32:04 (none) postgres[6018]: [6-1] ERROR: invalid page header in block 1 of relation "imp2p"
2008:09:05-09:32:04 (none) postgres[6018]: [6-2] STATEMENT: SELECT protocol,transform_common(CAST(sum(count) AS BIGINT)) as sum_pac_str,sum(count) as
2008:09:05-09:32:04 (none) postgres[6018]: [6-3] sum_pac_int,transform_common(count(DISTINCT(dstip) )) as cnt_dstip_str,count(DISTINCT(dstip)) as
2008:09:05-09:32:04 (none) postgres[6018]: [6-4] cnt_dstip_int,transform_common(count(DISTINCT(srci p))) as cnt_srcip_str,count(DISTINCT(srcip)) as
2008:09:05-09:32:04 (none) postgres[6018]: [6-5] cnt_srcip_int,transform_common(count(DISTINCT(prot ocol))) as cnt_proto_str,count(DISTINCT(protocol)) as cnt_proto_int FROM
2008:09:05-09:32:04 (none) postgres[6018]: [6-6] imp2p_data WHERE dayabs = 733290 AND type LIKE 'p2p' GROUP BY protocol ORDER BY sum_pac_int DESC

if running /usr/local/bin/report_render.plx --type daily --sendmail in /var/log/system.log

2008:09:05-09:42:32 (none) postgres[6938]: [3-1] ERROR: invalid page header in block 4358 of relation "accounting"
2008:09:05-09:42:32 (none) postgres[6938]: [3-2] STATEMENT: INSERT INTO public.accounting
2008:09:05-09:42:32 (none) postgres[6938]: [3-3] (ip_saddr,ip_daddr,ip_protocol,l4_dport,raw_in_pkt len,raw_in_pktcount,raw_out_pktlen,raw_out_pktcoun t,flow_start_day,flow_start
2008:09:05-09:42:32 (none) postgres[6938]: [3-4] _sec,flow_duration) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11);
2008:09:05-09:42:32 (none) ulogd[3000]: pg1: ERROR: invalid page header in block 4358 of relation "accounting"
2008:09:05-09:42:32 (none) ulogd[3000]: pg1: commit failed
2008:09:05-09:42:37 (none) ulogd[3000]: input key `flow.start.sec' already has source
2008:09:05-09:42:37 (none) ulogd[3000]: stack_fsm_timer_cb: error

Hi,
it sounds like you have some breakage in the at least two database tables. The least invasive thing is to drop the affect tables, and re-create them manually using /usr/share/postgresql/contrib/astaro_reporting_tables.sql as a guideline, but this is a bit tricky. The easy alternative, if you don't mind lost reporting data, is to just run /var/storage/pgsql/init/reporting_db_init.sh which will drop the reporting database and create everything from scratch. It's advisable to stop the reporting processes and the ulog daemon while operating on the databases, use /etc/init.d/ulog stop, /etc/init.d/syslogng stop, do database operations, start services again.

Cheers,
andreas

EDIT: do this as root, and become root by using "su -". Getting a login shell is imperative for the reporting_db_init.sh script to work properly!

Hello

I have the same problem but if I execute the command (after stopped services):
/var/storage/pgsql/init/reporting_db_init.sh
I have the errors:

psql: FATAL: database "root" does not exist
createdb: could not connect to database postgres: FATAL: role "root" does not exist
createlang: could not connect to database reporting: FATAL: role "root" does not exist
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:21: ERROR: relation "accounting" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:23: ERROR: relation "accounting_dayidx" already exists
*** other relations errors ***
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:500: ERROR: relation "websec_au_data" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:525: ERROR: relation "websec_bud_data" already exists
psql: FATAL: role "root" does not exist

How can I resolve this problem?
Thank you

Iive had this problem too. I've updated to v7.301 and still have the problem.

I droped the table and recreated it via the above instructions, and it seems good now.

Specifically the instructions are:
1.) /etc/init.d/ulog stop
2.) /etc/init.d/syslogng stop
3.) /var/storage/pgsql/init/reporting_db_init.sh
4.) /etc/init.d/syslogng start
5.) /etc/init.d/ulog start

Function complete.

FWIW, I had this problem too and the above instructions helped...

When i run /var/storage/pgsql/init/reporting_db_init.sh
i receive this message

psql: FATAL: database "root" does not exist
createdb: could not connect to database postgres: FATAL: role "root" does not exist
createlang: could not connect to database reporting: FATAL: role "root" does not exist
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:21: ERROR: relation "accounting" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:23: ERROR: relation "accounting_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:39: ERROR: relation "accounting_archive" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:41: ERROR: relation "accountingarc_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:52: ERROR: relation "auth" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:54: ERROR: relation "auth_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:62: ERROR: relation "ipscount" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:76: ERROR: relation "ips" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:78: ERROR: relation "ips_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:92: ERROR: relation "ips_archive" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:94: ERROR: relation "ipsarc_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:108: ERROR: relation "imp2p" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:110: ERROR: relation "imp2p_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:124: ERROR: relation "imp2p_archive" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:126: ERROR: relation "imp2parc_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:144: ERROR: relation "mailsec" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:146: ERROR: relation "mailsec_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:164: ERROR: relation "mailsec_archive" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:166: ERROR: relation "mailsecarc_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:178: ERROR: relation "pfilter" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:180: ERROR: relation "pfilter_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:181: ERROR: relation "pfilter_srcidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:193: ERROR: relation "pfilter_archive" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:195: ERROR: relation "pfilterarc_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:196: ERROR: relation "pfilterarc_srcidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:210: ERROR: relation "websec_aud" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:212: ERROR: relation "websec_aud_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:213: ERROR: relation "websec_aud_idx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:226: ERROR: relation "websec_aud_archive" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:228: ERROR: relation "websec_audarc_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:229: ERROR: relation "websec_audarc_idx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:241: ERROR: relation "websec_au" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:243: ERROR: relation "websec_au_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:244: ERROR: relation "websec_au_idx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:255: ERROR: relation "websec_au_archive" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:257: ERROR: relation "websec_auarc_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:258: ERROR: relation "websec_auarc_idx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:275: ERROR: relation "websec_bud" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:277: ERROR: relation "websec_bud_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:278: ERROR: relation "websec_bud_idx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:294: ERROR: relation "websec_bud_archive" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:296: ERROR: relation "websec_budarc_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:297: ERROR: relation "websec_budarc_idx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:326: ERROR: relation "accounting_data" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:343: ERROR: relation "imp2p_data" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:362: ERROR: relation "ips_data" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:385: ERROR: relation "mailsec_data" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:411: ERROR: relation "mailsec_addresses" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:440: ERROR: relation "mailsec_contacts" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:458: ERROR: relation "pfilter_data" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:479: ERROR: relation "websec_aud_data" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:500: ERROR: relation "websec_au_data" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:525: ERROR: relation "websec_bud_data" already exists

My problem is the same.
Executive report and accounting not working

After /var/storage/pgsql/init/reporting_db_init.sh i get errors:

ERROR: tablespace "reporting" already exists
createdb: database creation failed: ERROR: database "reporting" already exists
createlang: language "plpgsql" is already installed in database "reporting"
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:21: ERROR: relation "accounting" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:23: ERROR: relation "accounting_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:39: ERROR: relation "accounting_archive" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:41: ERROR: relation "accountingarc_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:52: ERROR: relation "auth" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:54: ERROR: relation "auth_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:62: ERROR: relation "ipscount" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:76: ERROR: relation "ips" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:78: ERROR: relation "ips_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:92: ERROR: relation "ips_archive" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:94: ERROR: relation "ipsarc_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:108: ERROR: relation "imp2p" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:110: ERROR: relation "imp2p_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:124: ERROR: relation "imp2p_archive" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:126: ERROR: relation "imp2parc_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:144: ERROR: relation "mailsec" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:146: ERROR: relation "mailsec_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:164: ERROR: relation "mailsec_archive" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:166: ERROR: relation "mailsecarc_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:178: ERROR: relation "pfilter" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:180: ERROR: relation "pfilter_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:181: ERROR: relation "pfilter_srcidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:193: ERROR: relation "pfilter_archive" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:195: ERROR: relation "pfilterarc_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:196: ERROR: relation "pfilterarc_srcidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:210: ERROR: relation "websec_aud" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:212: ERROR: relation "websec_aud_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:213: ERROR: relation "websec_aud_idx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:226: ERROR: relation "websec_aud_archive" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:228: ERROR: relation "websec_audarc_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:229: ERROR: relation "websec_audarc_idx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:241: ERROR: relation "websec_au" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:243: ERROR: relation "websec_au_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:244: ERROR: relation "websec_au_idx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:255: ERROR: relation "websec_au_archive" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:257: ERROR: relation "websec_auarc_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:258: ERROR: relation "websec_auarc_idx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:275: ERROR: relation "websec_bud" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:277: ERROR: relation "websec_bud_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:278: ERROR: relation "websec_bud_idx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:294: ERROR: relation "websec_bud_archive" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:296: ERROR: relation "websec_budarc_dayidx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:297: ERROR: relation "websec_budarc_idx" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:326: ERROR: relation "accounting_data" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:343: ERROR: relation "imp2p_data" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:362: ERROR: relation "ips_data" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:385: ERROR: relation "mailsec_data" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:411: ERROR: relation "mailsec_addresses" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:440: ERROR: relation "mailsec_contacts" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:458: ERROR: relation "pfilter_data" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:479: ERROR: relation "websec_aud_data" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:500: ERROR: relation "websec_au_data" already exists
psql:/usr/share/postgresql/contrib/astaro_reporting_tables.sql:525: ERROR: relation "websec_bud_data" already exists

__________________
2 x ASG320 HA Cluster v7.502
4 x ASG110 v7.502

Hello, befor you use the reporting script please move the following folder to the home folder

/var/log/reporting/pgsql/16***x