launch container:
mkdir -p ${HOME}/postgresql/16
docker run --name pg16 -t \
-e PGDATA=/var/lib/postgresql/data/pgdata \
-v ${HOME}/postgresql/16:/var/lib/postgresql/data \
-e POSTGRES_PASSWORD="zabbix" \
-e POSTGRES_DB="dummy_db" \
-p 7416:5432 -d timescale/timescaledb:2.16.0-pg16
install repo:
sudo apt install curl ca-certificates
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
deps:
apt install lsb-release -y
install client:
sudo apt install -y postgresql-client-16
setup env variables:
echo "PGHOST=127.0.0.1
PGUSER=postgres
PGPORT=7416
PGPASSWORD=zabbix
export PGHOST
export PGUSER
export PGPORT
export PGPASSWORD" | sudo tee /etc/profile.d/postgres.sh
recreate db:
psql -c "DROP DATABASE IF EXISTS z70;" && psql -c "CREATE DATABASE z70 OWNER zabbix;"
run frontend:
docker stop front70; docker rm front70; docker run --name front70 \
-e DB_SERVER_HOST="192.168.88.59" -e DB_SERVER_PORT="7416" \
-e POSTGRES_DB="z70" -e POSTGRES_USER="zabbix" -e POSTGRES_PASSWORD="zabbix" \
-e ZBX_SERVER_HOST="127.0.0.1" \
-p 8070:8080 \
-e PHP_TZ="Europe/Riga" -d zabbix/zabbix-web-nginx-pgsql:alpine-7.0.2
DROP TABLE auditlog;
CREATE TABLE auditlog (
auditid varchar(25) NOT NULL,
userid bigint NULL,
username varchar(100) DEFAULT '' NOT NULL,
clock integer DEFAULT '0' NOT NULL,
ip varchar(39) DEFAULT '' NOT NULL,
action integer DEFAULT '0' NOT NULL,
resourcetype integer DEFAULT '0' NOT NULL,
resourceid bigint NULL,
resource_cuid varchar(25) NULL,
resourcename varchar(255) DEFAULT '' NOT NULL,
recordsetid varchar(25) NOT NULL,
details text DEFAULT '' NOT NULL,
PRIMARY KEY (auditid)
);
CREATE INDEX auditlog_1 ON auditlog (userid,clock);
CREATE INDEX auditlog_2 ON auditlog (clock);
CREATE INDEX auditlog_3 ON auditlog (resourcetype,resourceid);
CREATE INDEX auditlog_4 ON auditlog (recordsetid);
ALTER TABLE auditlog OWNER TO zabbix;
DROP TABLE history;
DROP TABLE history_uint;
DROP TABLE history_str;
DROP TABLE history_log;
DROP TABLE history_text;
DROP TABLE history_bin;
DROP TABLE trends;
DROP TABLE trends_uint;
CREATE TABLE history (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
value DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
ns integer DEFAULT '0' NOT NULL,
PRIMARY KEY (itemid,clock,ns)
);
CREATE TABLE history_uint (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
value numeric(20) DEFAULT '0' NOT NULL,
ns integer DEFAULT '0' NOT NULL,
PRIMARY KEY (itemid,clock,ns)
);
CREATE TABLE history_str (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
value varchar(255) DEFAULT '' NOT NULL,
ns integer DEFAULT '0' NOT NULL,
PRIMARY KEY (itemid,clock,ns)
);
CREATE TABLE history_log (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
timestamp integer DEFAULT '0' NOT NULL,
source varchar(64) DEFAULT '' NOT NULL,
severity integer DEFAULT '0' NOT NULL,
value text DEFAULT '' NOT NULL,
logeventid integer DEFAULT '0' NOT NULL,
ns integer DEFAULT '0' NOT NULL,
PRIMARY KEY (itemid,clock,ns)
);
CREATE TABLE history_text (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
value text DEFAULT '' NOT NULL,
ns integer DEFAULT '0' NOT NULL,
PRIMARY KEY (itemid,clock,ns)
);
CREATE TABLE history_bin (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
ns integer DEFAULT '0' NOT NULL,
value bytea DEFAULT '' NOT NULL,
PRIMARY KEY (itemid,clock,ns)
);
CREATE TABLE trends (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
num integer DEFAULT '0' NOT NULL,
value_min DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
value_avg DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
value_max DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
PRIMARY KEY (itemid,clock)
);
CREATE TABLE trends_uint (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
num integer DEFAULT '0' NOT NULL,
value_min numeric(20) DEFAULT '0' NOT NULL,
value_avg numeric(20) DEFAULT '0' NOT NULL,
value_max numeric(20) DEFAULT '0' NOT NULL,
PRIMARY KEY (itemid,clock)
);
ALTER TABLE history OWNER TO zabbix;
ALTER TABLE history_uint OWNER TO zabbix;
ALTER TABLE history_str OWNER TO zabbix;
ALTER TABLE history_log OWNER TO zabbix;
ALTER TABLE history_text OWNER TO zabbix;
ALTER TABLE history_bin OWNER TO zabbix;
ALTER TABLE trends OWNER TO zabbix;
ALTER TABLE trends_uint OWNER TO zabbix;
Restore integers:
psql z70 -c "\COPY trends_uint FROM PROGRAM 'xzcat /tb/pg/z70.trends_uint.csv.xz' DELIMITER ',' CSV"
Restore decimals:
psql z70 -c "\COPY trends FROM PROGRAM 'xzcat /tb/pg/z70.trends.csv.xz' DELIMITER ',' CSV"
Short text strings:
psql z70 -c "\COPY trends FROM PROGRAM 'xzcat /tb/pg/z70.trends.csv.xz' DELIMITER ',' CSV"
Text:
psql z70 -c "\COPY history_text FROM PROGRAM 'xzcat /tb/pg/z70.history_text.csv.xz' DELIMITER ',' CSV"
No comments:
Post a Comment