1) Install Postgress in Ubuntu
3) create DataBase
airflow@ip-10-1-1-4:~/airflow$ sudo apt-get install postgresql postgresql-contrib2) Login to postgress DataBase
ansible@ip-10-101-2-142:~$ sudo -u postgres psqloransible@ip-10-101-2-142:~$ psql -h"localhost" -p 5432 -Uairflow airflow -WPassword for user airflow:psql (9.5.12)SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)Type "help" for help.airflow=#
3) create DataBase
airflow@ip-10-1-1-4:~/airflow$ sudo -u postgres createdb airfloworairflow=# createdb airflow WITH ENCODING='UTF-8';
4) show database
5) Create userairflow=# \d+List of relationsSchema | Name | Type | Owner | Size | Description--------+-------------------------+----------+---------+------------+-------------public | sqpl_airflow_hosts | table | airflow | 16 kB |(1 rows)
postgres=# CREATE USER airflow WITH ENCRYPTED PASSWORD 'Ubuntu@123';
6) GRANT Permission to user
postgres=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to airflow;GRANT
7) connection info
airflow=# \conninfoYou are connected to database "airflow" as user "airflow" via socket in "/var/run/postgresql" at port "5432".
8) Show config file
airflow=# SHOW hba_file;hba_file--------------------------------------/etc/postgresql/9.5/main/pg_hba.conf(1 row)
9) Create Table
CREATE TABLE sqpl_airflow_hosts(ip character varying(15) NOT NULL,logical_group character varying(200) NOT NULL,queue_name character varying(100) NOT NULL,param_kwargs character varying(1000) NOT NULL,host_state host_state_enum,primary key (logical_group,param_kwargs));
10) Describe table
airflow-# \d+ sqpl_airflow_hostsip | character varying(15) | not null | extended | |logical_group | character varying(200) | not null | extended | |queue_name | character varying(100) | not null | extended | |param_kwargs | character varying(1000) | not null | extended | |host_state | host_state_enum | | plain | |
11) select query
airflow=# select * from sqpl_airflow_hosts;ip | logical_group | queue_name | param_kwargs | host_state----+---------------+------------+--------------+------------(0 rows)
12) Insert Query
airflow=# insert into sqpl_airflow_hosts values ('xxx','cme_engines','10.101.2.253_queue','--ip 192.168.201.105 --engine_id 12','RUNNING');INSERT 0 1airflow=# select * from sqpl_airflow_hosts;ip | logical_group | queue_name | param_kwargs | host_state-----+---------------+--------------------+-------------------------------------+------------xxx | cme_engines | 10.101.2.253_queue | --ip 192.168.201.105 --engine_id 12 | RUNNING(1 row)
13) Alter Table
airflow=# alter table sqpl_airflow_hosts add column params character varying[];
airflow=# \d+ sqpl_airflow_hostsTable "public.sqpl_airflow_hosts"Column | Type | Modifiers | Storage | Stats target | Description---------------+-------------------------+-----------+----------+--------------+-------------ip | character varying(15) | not null | extended | |logical_group | character varying(200) | not null | extended | |queue_name | character varying(100) | not null | extended | |param_kwargs | character varying(1000) | not null | extended | |host_state | host_state_enum | | plain | |command | character varying | | extended | |params | character varying[] | | extended | |Indexes:"sqpl_airflow_hosts_pkey" PRIMARY KEY, btree (logical_group, param_kwargs)
airflow=# alter table sqpl_airflow_hosts drop column param_kwargs;airflow=# alter table sqpl_airflow_hosts add constraint pkey unique(logical_group, command, params);ALTER TABLEairflow=# \d+ sqpl_airflow_hostsTable "public.sqpl_airflow_hosts"Column | Type | Modifiers | Storage | Stats target | Description---------------+------------------------+-----------+----------+--------------+-------------logical_group | character varying(200) | not null | extended | |queue_name | character varying(100) | not null | extended | |host_state | host_state_enum | | plain | |command | character varying | | extended | |params | character varying[] | | extended | |Indexes:"pkey" UNIQUE CONSTRAINT, btree (logical_group, command, params)
ALTER TABLE sqpl_airflow_hosts ALTER COLUMN logical_group TYPE character varying(100);
14) Update table
airflow=# update sqpl_airflow_hosts set command = 'start.sh', params = {'--ip = 192.168.201.105', '--engine_id 12'} ;
15) Getting Size of DB
airflow=# select pg_relation_size('sqpl_airflow_hosts'); pg_relation_size ------------------ 8192 (1 row)airflow=# select pg_size_pretty(pg_database_size(current_database()));pg_size_pretty----------------11 MB(1 row)
16) Know who is connected
airflow=# SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ;datname | usename | client_addr | client_port---------+---------+--------------+-------------airflow | airflow | 127.0.0.1 | 46896airflow | airflow | 127.0.0.1 | 37942airflow | airflow | 127.0.0.1 | 45694airflow | airflow | 10.101.2.82 | 52146airflow | airflow | 10.101.2.105 | 35482airflow | airflow | 10.101.2.253 | 38740airflow | airflow | 127.0.0.1 | 52116airflow | airflow | 127.0.0.1 | 40694airflow | airflow | 127.0.0.1 | 53752airflow | airflow | 127.0.0.1 | 53454airflow | airflow | 127.0.0.1 | 40546airflow | airflow | 127.0.0.1 | 35432airflow | airflow | 127.0.0.1 | 40392airflow | airflow | 127.0.0.1 | 40848airflow | airflow | 127.0.0.1 | 37928(15 rows)
17 ) Reload postgress configuration file
select pg_reload_conf();
18 ) Show DataBase directory
airflow=# SHOW data_directory;
data_directory
------------------------------
/var/lib/postgresql/9.5/main
(1 row)
19) Insert into table
insert into sqpl_airflow_hosts values ('cme_engines','10.101.2.253_queue','RUNNING','cd /home/deployment && /home/deployment/airflow_scripts/start_engine.py', '{"--ip=192.168.201.105", "--engine_id=28"}');
20) Some Select examples
airflow=# select concat(command, ' ', array_to_string(params, ' ')) from sqpl_airflow_hosts ; concat ------------------------------------------------------------------------------------------------------------- cd /home/deployment && /home/deployment/airflow_scripts/start_engine.py --ip=192.168.201.105 --engine_id=12 cd /home/deployment && /home/deployment/airflow_scripts/start_engine.py --ip=192.168.201.105 --engine_id=13 cd /home/deployment && /home/deployment/airflow_scripts/start_engine.py --ip=192.168.201.105 --engine_id=15 cd /home/deployment && /home/deployment/airflow_scripts/start_engine.py --ip=192.168.201.105 --engine_id=17 cd /home/deployment && /home/deployment/airflow_scripts/start_engine.py --ip=192.168.201.105 --engine_id=19 cd /home/deployment && /home/deployment/airflow_scripts/start_engine.py --ip=192.168.201.105 --engine_id=21 cd /home/deployment && /home/deployment/airflow_scripts/start_engine.py --ip=192.168.201.105 --engine_id=22 cd /home/deployment && /home/deployment/airflow_scripts/start_engine.py --ip=192.168.201.105 --engine_id=23 cd /home/deployment && /home/deployment/airflow_scripts/start_engine.py --ip=192.168.201.105 --engine_id=24 cd /home/deployment && /home/deployment/airflow_scripts/start_engine.py --ip=192.168.201.105 --engine_id=28 (10 rows)
No comments:
Post a Comment