diff options
Diffstat (limited to 'nms')
| -rw-r--r-- | nms/nms-dump.sql | 149 | ||||
| -rw-r--r-- | nms/postgresql.conf | 10 |
2 files changed, 147 insertions, 12 deletions
diff --git a/nms/nms-dump.sql b/nms/nms-dump.sql index 335a3b0..25ba3ac 100644 --- a/nms/nms-dump.sql +++ b/nms/nms-dump.sql @@ -49,7 +49,7 @@ SET default_with_oids = false; CREATE TABLE dhcp ( switch integer, - "time" timestamp without time zone, + "time" timestamp with time zone, mac macaddr, ip inet ); @@ -78,9 +78,11 @@ ALTER TABLE linknet_ping OWNER TO nms; CREATE TABLE linknets ( linknet integer NOT NULL, switch1 integer NOT NULL, - addr1 inet NOT NULL, + addr1 inet, switch2 integer NOT NULL, - addr2 inet NOT NULL + addr2 inet, + port1 character varying(10), + port2 character varying(10) ); @@ -253,19 +255,26 @@ ALTER TABLE switch_temp OWNER TO nms; CREATE TABLE switches ( switch integer DEFAULT nextval(('"switches_switch_seq"'::text)::regclass) NOT NULL, - ip inet, + mgmt_v4_addr inet, sysname character varying NOT NULL, switchtype character varying DEFAULT 'ex2200'::character varying NOT NULL, last_updated timestamp with time zone, locked boolean DEFAULT false NOT NULL, poll_frequency interval DEFAULT '00:01:00'::interval NOT NULL, - community character varying DEFAULT 'public'::character varying NOT NULL, + community character varying DEFAULT 'FullPuppTilNMS'::character varying NOT NULL, lldp_chassis_id character varying, - secondary_ip inet, + mgmt_v6_addr inet, placement box, subnet4 cidr, subnet6 cidr, - distro character varying + distro_name character varying, + distro_phy_port character varying(100), + mgmt_v6_gw inet, + mgmt_v4_gw inet, + mgmt_vlan integer DEFAULT 666, + traffic_vlan integer, + last_config_fetch timestamp with time zone, + current_mac macaddr ); @@ -286,6 +295,17 @@ CREATE SEQUENCE switches_switch_seq ALTER TABLE switches_switch_seq OWNER TO nms; -- +-- Name: test_table; Type: TABLE; Schema: public; Owner: nms; Tablespace: +-- + +CREATE TABLE test_table ( + test timestamp with time zone +); + + +ALTER TABLE test_table OWNER TO nms; + +-- -- Name: linknet; Type: DEFAULT; Schema: public; Owner: nms -- @@ -355,6 +375,20 @@ ALTER TABLE ONLY switches -- +-- Name: dhcp_ip; Type: INDEX; Schema: public; Owner: nms; Tablespace: +-- + +CREATE INDEX dhcp_ip ON dhcp USING btree (ip); + + +-- +-- Name: dhcp_mac; Type: INDEX; Schema: public; Owner: nms; Tablespace: +-- + +CREATE INDEX dhcp_mac ON dhcp USING btree (mac); + + +-- -- Name: dhcp_switch; Type: INDEX; Schema: public; Owner: nms; Tablespace: -- @@ -376,6 +410,13 @@ CREATE INDEX ping_index ON ping USING btree ("time"); -- +-- Name: ping_secondary_index; Type: INDEX; Schema: public; Owner: nms; Tablespace: +-- + +CREATE INDEX ping_secondary_index ON ping_secondary_ip USING btree ("time"); + + +-- -- Name: polls_ifname; Type: INDEX; Schema: public; Owner: nms; Tablespace: -- @@ -514,6 +555,67 @@ REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM postgres; GRANT ALL ON SCHEMA public TO postgres; GRANT ALL ON SCHEMA public TO PUBLIC; +GRANT ALL ON SCHEMA public TO fap; + + +-- +-- Name: dhcp; Type: ACL; Schema: public; Owner: nms +-- + +REVOKE ALL ON TABLE dhcp FROM PUBLIC; +REVOKE ALL ON TABLE dhcp FROM nms; +GRANT ALL ON TABLE dhcp TO nms; +GRANT ALL ON TABLE dhcp TO fap; + + +-- +-- Name: linknet_ping; Type: ACL; Schema: public; Owner: nms +-- + +REVOKE ALL ON TABLE linknet_ping FROM PUBLIC; +REVOKE ALL ON TABLE linknet_ping FROM nms; +GRANT ALL ON TABLE linknet_ping TO nms; +GRANT ALL ON TABLE linknet_ping TO fap; + + +-- +-- Name: linknets; Type: ACL; Schema: public; Owner: nms +-- + +REVOKE ALL ON TABLE linknets FROM PUBLIC; +REVOKE ALL ON TABLE linknets FROM nms; +GRANT ALL ON TABLE linknets TO nms; +GRANT ALL ON TABLE linknets TO fap; + + +-- +-- Name: ping; Type: ACL; Schema: public; Owner: nms +-- + +REVOKE ALL ON TABLE ping FROM PUBLIC; +REVOKE ALL ON TABLE ping FROM nms; +GRANT ALL ON TABLE ping TO nms; +GRANT ALL ON TABLE ping TO fap; + + +-- +-- Name: ping_secondary_ip; Type: ACL; Schema: public; Owner: nms +-- + +REVOKE ALL ON TABLE ping_secondary_ip FROM PUBLIC; +REVOKE ALL ON TABLE ping_secondary_ip FROM nms; +GRANT ALL ON TABLE ping_secondary_ip TO nms; +GRANT ALL ON TABLE ping_secondary_ip TO fap; + + +-- +-- Name: polls; Type: ACL; Schema: public; Owner: nms +-- + +REVOKE ALL ON TABLE polls FROM PUBLIC; +REVOKE ALL ON TABLE polls FROM nms; +GRANT ALL ON TABLE polls TO nms; +GRANT ALL ON TABLE polls TO fap; -- @@ -523,6 +625,7 @@ GRANT ALL ON SCHEMA public TO PUBLIC; REVOKE ALL ON TABLE seen_mac FROM PUBLIC; REVOKE ALL ON TABLE seen_mac FROM nms; GRANT ALL ON TABLE seen_mac TO nms; +GRANT ALL ON TABLE seen_mac TO fap; -- @@ -533,6 +636,7 @@ REVOKE ALL ON TABLE snmp FROM PUBLIC; REVOKE ALL ON TABLE snmp FROM nms; GRANT ALL ON TABLE snmp TO nms; GRANT ALL ON TABLE snmp TO postgres; +GRANT ALL ON TABLE snmp TO fap; -- @@ -546,12 +650,43 @@ GRANT ALL ON SEQUENCE snmp_id_seq TO postgres; -- +-- Name: switch_comments; Type: ACL; Schema: public; Owner: nms +-- + +REVOKE ALL ON TABLE switch_comments FROM PUBLIC; +REVOKE ALL ON TABLE switch_comments FROM nms; +GRANT ALL ON TABLE switch_comments TO nms; +GRANT ALL ON TABLE switch_comments TO fap; + + +-- +-- Name: switch_temp; Type: ACL; Schema: public; Owner: nms +-- + +REVOKE ALL ON TABLE switch_temp FROM PUBLIC; +REVOKE ALL ON TABLE switch_temp FROM nms; +GRANT ALL ON TABLE switch_temp TO nms; +GRANT ALL ON TABLE switch_temp TO fap; + + +-- -- Name: switches; Type: ACL; Schema: public; Owner: nms -- REVOKE ALL ON TABLE switches FROM PUBLIC; REVOKE ALL ON TABLE switches FROM nms; GRANT ALL ON TABLE switches TO nms; +GRANT ALL ON TABLE switches TO fap; + + +-- +-- Name: test_table; Type: ACL; Schema: public; Owner: nms +-- + +REVOKE ALL ON TABLE test_table FROM PUBLIC; +REVOKE ALL ON TABLE test_table FROM nms; +GRANT ALL ON TABLE test_table TO nms; +GRANT ALL ON TABLE test_table TO fap; -- diff --git a/nms/postgresql.conf b/nms/postgresql.conf index 23241a7..dee1129 100644 --- a/nms/postgresql.conf +++ b/nms/postgresql.conf @@ -113,19 +113,19 @@ ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' # (change requires rest # - Memory - -shared_buffers = 2048MB # min 128kB +shared_buffers = 76GB # min 128kB # (change requires restart) #huge_pages = try # on, off, or try # (change requires restart) -#temp_buffers = 8MB # min 800kB +temp_buffers = 32MB # min 800kB #max_prepared_transactions = 0 # zero disables the feature # (change requires restart) # Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). # It is not advisable to set max_prepared_transactions nonzero unless you # actively intend to use prepared transactions. -#work_mem = 4MB # min 64kB -#maintenance_work_mem = 64MB # min 1MB +work_mem = 16MB # min 64kB +maintenance_work_mem = 256MB # min 1MB #autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem #max_stack_depth = 2MB # min 100kB dynamic_shared_memory_type = posix # the default is the first option @@ -173,7 +173,7 @@ dynamic_shared_memory_type = posix # the default is the first option # - Settings - -#wal_level = minimal # minimal, archive, hot_standby, or logical +wal_level = archive # minimal, archive, hot_standby, or logical # (change requires restart) #fsync = on # turns forced synchronization on or off #synchronous_commit = on # synchronization level; |