[BUG] pgsql schema not working

[ghormoon]

I'm now investigating the pgsql schema import and i don;t understand this line in zabbix/pgsql/schema.sls: - name: zcat {{ sql_file }} | psql | head -5

it sounds to me like someone was just testing it with the -5 and didn't finish at all. or am i missing something deeper?

also the zcat may not be installed by default, so it should be installed first if wants to be used. if you're wanting to reuse existing (remote) DB (don't have the permission to create one), postgresql client will be missing too, as it's installed by conf.

i'll test it now more, because i'm not sure it would work with remote DB (it has the variables there, but the errors i was getting looked like they didn't use)

[hatifnatt]

https://github.com/saltstack-formulas/zabbix-formula/blob/2c67b11ce1b0ef4442a0d3dd1ec7bd3e7fb500ef/zabbix/pgsql/schema.sls#L54 | head -5 - will limit output form psql, without it there will be a lot of output here.

Missed zcat really can be issue, but that need to be a separate one.

[hatifnatt]

zabbix.pgsql.schema includes zabbix.pgsql.conf and PostgreSQL client package is installed by default (on Debian) https://github.com/saltstack-formulas/zabbix-formula/blob/master/zabbix/osfamilymap.yaml#L81-L84 Also if your system have zcat missing you can add package containing zcat binary to zabbix.pgsql.pkgs list.

[ghormoon]

can somehow zabbix-server, zabbix-frontend, zabbix-psql be set at once? i've just found out i've missed the zabbix-pgsql part :) i don;t see a point in setting those things 3 times as they will always be the same in the same setup. still, even if i set it, a lot of parts will try to touch localhost db

i had to comment out the dependency on upload_sql_file ( i have it overriden for suse path), to even try, otverwise it would fail there (what is the point of copying exiating file anyway?) with "Specified file upload_sql_dump is not an absolute path" even though it is and exists

also had to comment out the check_db_pgsql (i assume because it tries to check on local db that doesn't exist) to try import.

and after all this, i still get only 3 tables imported (+2 indexes). if i remove the head -5, then it imports all of them.

saltminion1:~ # psql -h saltminion3.suse.lan -U zabbix zabbix -W
Password: 
psql (13.2)
Type "help" for help.

zabbix=> \d
           List of relations
 Schema |     Name     | Type  | Owner  
--------+--------------+-------+--------
 public | maintenances | table | zabbix
 public | role         | table | zabbix
 public | users        | table | zabbix
(3 rows)

[ghormoon]

regarding the head, it definitely ends the import prematurely. if we don;t care about which part of the output is shown, it can be done with tail, bit this is definitely wrong.

i'm experimenting with the checks, but doing it with remote DB and not having superuser access seems not an easy part, i'll likely have to do some overrides or edit the checks to be able to check "itself" (the user and db) with nonsuperuser user

[ghormoon]

I've added a comment to #99 , seems one of the issues i had before is that you actually need psotgresql installed BEFORE you can add zabbix.pgsql.schema i guess if we resolve #99 and #148 we can close this

[ghormoon]

i finally made it work, see the pullrequest. I'll need to do something about the long line though to pass pipeline :)

another thing that i'd like to change (likely as separate PR though), is to split zabbix.pgsql.conf into two -> oner to install packages, one to create user and db. so it doesn't fail, if you run it against remote DB that someone else created for you and you don't have superuser (the schema import works now, but the user and db steps fail)

would it be better accepted as nonbreaking change, i.e. create something like zabbix.pgsql.packages and include it from zabbix.pgsql.conf, or keep the name zabbix.pgsql.conf fot the base things like packages and create new state zabbix.pgsql.xxxx (i can't figure good name now, something like dbanduser) that would create db and the user? it would require ppl who want the db created to add that sls into their roles or wherever they use it.