Wierd copy of postgres db

[pietro54]

Hello,
Im moving my zabbix to new machine, so i export database by command:
pg_dump zabbix | gzip -c > /home/postgres/zabbix.dump.`date +%Y%m%d`.gz
When i try to import by command:
psql -U zabbix < zabbix.dump.20121203

for fast look evrythings looks well, but there is something that is wierd for me,
in old zabbix machin when i check db size by command:

-bash-4.1$ psql -U zabbix zabbix -W
Password for user zabbix:
psql (8.4.13)
Type "help" for help.

zabbix=> SELECT pg_size_pretty(pg_database_size('zabbix')) As fulldbsize;
fulldbsize
------------
54 GB
(1 row)

on new db im getting:

-bash-4.1$ psql -U zabbix zabbix -W
Password for user zabbix:
psql (9.1.6)
Type "help" for help.

zabbix=> SELECT pg_size_pretty(pg_database_size('zabbix')) As fulldbsize;
fulldbsize
------------
34 GB
(1 row)

The problem is size, where is ~20gb of data?

I was importing schema witch -L option, I can pas her full log but maybe this will be enought to find problem:

cat text1.log | grep error
error character varying(128) DEFAULT ''::character varying NOT NULL,
error character varying(128) DEFAULT ''::character varying NOT NULL,
errors_from integer DEFAULT 0 NOT NULL,
ipmi_errors_from integer DEFAULT 0 NOT NULL,
snmp_errors_from integer DEFAULT 0 NOT NULL,
ipmi_error character varying(128) DEFAULT ''::character varying NOT NULL,
snmp_error character varying(128) DEFAULT ''::character varying NOT NULL,
jmx_errors_from integer DEFAULT 0 NOT NULL,
jmx_error character varying(128) DEFAULT ''::character varying NOT NULL,
error character varying(128) DEFAULT ''::character varying NOT NULL,
error character varying(128) DEFAULT ''::character varying NOT NULL,
COPY alerts (alertid, actionid, eventid, userid, clock, mediatypeid, sendto, subject, message, status, retries, error, nextcheck, esc_step, alerttype) FROM stdin;
COPY hosts (hostid, proxy_hostid, host, status, disable_until, error, available, errors_from, lastaccess, ipmi_authtype, ipmi_privilege, ipmi_username, ipmi_password, ipmi_disable_until, ipmi_available, snmp_disable_until, snmp_available, maintenanceid, maintenance_status, maintenance_type, maintenance_from, ipmi_errors_from, snmp_errors_from, ipmi_error, snmp_error, jmx_disable_until, jmx_available, jmx_errors_from, jmx_error, name) FROM stdin;
COPY items (itemid, type, snmp_community, snmp_oid, hostid, name, key_, delay, history, trends, lastvalue, lastclock, prevvalue, status, value_type, trapper_hosts, units, multiplier, delta, prevorgvalue, snmpv3_securityname, snmpv3_securitylevel, snmpv3_authpassphrase, snmpv3_privpassphrase, formula, error, lastlogsize, logtimefmt, templateid, valuemapid, delay_flex, params, ipmi_sensor, data_type, authtype, username, password, publickey, privatekey, mtime, lastns, flags, filter, interfaceid, port, description, inventory_link, lifetime) FROM stdin;
COPY triggers (triggerid, expression, description, url, status, value, priority, lastchange, comments, error, templateid, type, value_flags, flags) FROM stdin;

I dont see any serious problem, but maybe im wrong, please help.

[alledm]

i believe it is because the first value includes the size of rows that had been deleted but whose disk space had not been claimed back.

http://www.postgresql.org/docs/8.3/static/sql-vacuum.html

[pietro54]

Thanks for fast reply.

So whats doing house-keeper? he is useless?

I shouldent look at errors?

[alledm]

House keeping is deleting those rows, but the it is up to the database to dispose of them for good.

Vacuuming takes time and resources and you should do it ideally daily or weekly in times where the DB is less busy.

I cannot help you with the errors I am afraid. Are you dumping and restoring from/to the same release of postgresql?

[pietro54]

Nop, im use newer postgres relase.
from 8.4.13 to 9.1.6.
20gb this is realy big value.

[alledm]

then probably the errors are due to changes in the postgres format. See if there is a way to import in a "backward compatibility" way.

20GB is not that much especially if you have never done VACUUM

[pietro54]

Hmm...
on my postgres.conf i made few changes in vacum section, this is part of my cfg:

#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
##Auto sprzatanie
autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
autovacuum_max_workers = 3 # max number of autovacuum subprocesses
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit

i think vacum should work, is this wrong configuration?
Please look only at uncomented values.

[alledm]

yes it should.

[pietro54]

Thank you Alledm, im full of hope that someone will give me more advice.

[alledm]

you are welcome. Unfortunately my knowledge only goes this far.

[alledm]

Actually have a look at this

http://www.postgresql.org/docs/9.2/static/routine-vacuuming.html

[pietro54]

Hi,
Thanks for realy good peace of documentation.
I read a lot about vaccum, but for me this doesnt give expected resault becous:
zabbix=> VACUUM FULL history;
VACUUM

Task complet in ~6h but the size of db is still big

[root@zabbix ~]# su - postgres
-bash-4.1$ psql -U zabbix zabbix -W
Password for user zabbix:
psql (8.4.13)
Type "help" for help.

zabbix=> SELECT pg_size_pretty(pg_database_size('zabbix')) As fulldbsize;
fulldbsize
------------
53 GB
(1 row)

[pietro54]

Hello,

Pease someone adviece me what should i do, with my db.
It isent well importing.
On my old zabbix i see:
Required server performance, new values per second 94.31
O new one i see only:
Required server performance, new values per second 88.17
So i lose 6vps... even dont know how to find them
This i quite bad;/

Dont know what to do, bellow atached my import log:

https://rapidshare.com/files/1593180471/import2.log

Statistic from dashboard:
NEW zabbix
Number of hosts (monitored/not monitored/templates) 104 67 / 0 / 37
Number of items (monitored/disabled/not supported) 3165 2750 / 91 / 324
Number of triggers (enabled/disabled)[problem/unknown/ok] 1108 1039 / 69 [17 / 0 / 1022]

Required server performance, new values per second 88.17 -

New zabbix:
Zabbix server is running Yes 127.0.0.1:10051
Number of hosts (monitored/not monitored/templates) 104 67 / 0 / 37
Number of items (monitored/disabled/not supported) 3142 2778 / 91 / 273
Number of triggers (enabled/disabled)[problem/unknown/ok] 1109 1040 / 69 [72 / 0 / 968]
Required server performance, new values per second 94.31 -