#blog #backup

convert

It's fairly simple indeed.

Installation:
On Ubuntu:
apt-get install units
On Mac OSX:
brew install gnu-units
Example:
1024 bytes to MB

Ubuntu

units -v 1024byte megabyte

Mac OSX

gunits -v 1024byte megabyte

And the result:

1024byte = 0.001024 megabyte
1024byte = (1 / 976.5625) megabyte

ebs

First, you need to configure your aws credentials

aws configure --profile <your-profile-name>
AWS Access Key ID [None]: xxxxxxx
AWS Secret Access Key [None]: xxxxxx
Default region name [None]: xxxxxx
Default output format [None]: xxxxx
Next step will be selecting the just-created aws profile:

export AWS_PROFILE=<your-profile-name>
Then we're ready to proceed:

aws ec2 describe-snapshots --region <region> --filters Name=volume-id,Values=<volume-id> | grep snap- | awk '{print $2}' | awk -F"\"" '{print $2}' | xargs -n 1 -t aws ec2 delete-snapshot --region <region> --snapshot-id
Notes:

<region>: your aws region (us-east-1 by default)

<volume-id>: a volume-id which was used to create snapshot

etcd

Introduction


Etcd, an open-source distributed key-value store, has become an integral part of many micro-services infrastructure.

Kubernetes is one of the famous examples which is built on top of Etcd.

Thanks to Etcd's reliability & availability, many clusters can get through network failures or network partitions without losing any data.

However, unencrypted HTTP requests are used to communicate between Etcd node by default, and it could be a huge security risk if your Etcd cluster currently run across the Internet.

To solve that issue, TLS/SSL communication should be implemented to ensure all communication is encrypted.

Perquisites
You should be familiar with the basics of etcd, openssl, docker, docker-compose and generating a discovery URL.

Generate self-signed certificates
cfssl tool would be used for this purpose along with this detailed & well-explained document.

Please follow and create the following certificates & keys: ca.pem ca-key.pem member[1-3].pem member[1-3]-key.pem client.pem client-key.pem

Those keys should be kept in certs/ directory

Generate Etcd discovery URL
Create by using this link

Notes: size=3 means this cluster will have 3 members at max

Write down the result, as it would be used on the next steps

Use docker-compose to create test environment
Paste and save the following into docker-compose.yml file

version: '2'
services:
  member1:
    image: tuannvm/docker-alpine-etcd
    volumes:
      - ./certs:/srv/
    container_name: member1
    hostname: member1
    command: sleep 86400

  member2:
    image: tuannvm/docker-alpine-etcd
    volumes:
      - ./certs:/srv/
    container_name: member2
    hostname: member2
    command: sleep 86400

  member3:
    image: tuannvm/docker-alpine-etcd
    volumes:
      - ./certs:/srv/
    container_name: member3
    hostname: member3
    command: sleep 86400

  client:
    image: tuannvm/docker-alpine-etcd
    volumes:
      - ./certs:/srv/
    container_name: client
    hostname: client
    command: sleep 86400
Start up newly-configured docker-compose stack:

docker-compose up -d
Create Etcd cluster with command-line scripts
Access each of the members:

docker exec -it member[1-3] sh
Paste & run the following commands:

export DISCOVERY_URL=<the-discovery-URL-created-above>
export NAME=`hostname`
export IP=`ip addr | grep inet | grep -v 127 | cut -d" " -f6 | cut -d"/" -f1`

etcd --name ${NAME} --data-dir /home/${NAME} \
--client-cert-auth --trusted-ca-file=/srv/ca.pem --cert-file=/srv/${NAME}.pem --key-file=/srv/${NAME}-key.pem \ 
  --peer-client-cert-auth --peer-trusted-ca-file=/srv/ca.pem --peer-cert-file=/srv/${NAME}.pem --peer-key-file=/srv/${NAME}-key.pem \
  --advertise-client-urls https://${NAME}:2379 --listen-client-urls https://${NAME}:2379 \
  --initial-advertise-peer-urls https://${NAME}:2380 --listen-peer-urls https://${NAME}:2380 \
  --discovery ${DISCOVERY_URL} --debug
Notes the urls, ${NAME} should be included when generating certificate

Check functionality
Get inside the client container and fire up below commands:

docker exec -it member sh
# Create new key:value
curl --cacert ca.pem --cert client.pem --key client-key.pem https://member1:2379/v2/keys/foo -XPUT -d value=bar -v

# Get newly-create key:value
curl --cacert ca.pem --cert client.pem --key client-key.pem https://member1:2379/v2/keys/foo -XGET -v

postgres

A list of query scenarios on PostgreSQL database which might be helpful in some cases.

Table of Contents
Sample schema
UPDATE
DELETE
WILDCARD
JOIN
SUM, GROUP BY, COUNT
ORDER BY
CREATE TABLE
INSERT
AUTO INCREMENT
SERIAL
INDEX
DROP
VIEW
Built-in functions
Nested select

Sample schema:
-- orders.customer_id = customers.id
CREATE TABLE "public"."orders" (
"customer_id" int4,
"id" int4 NOT NULL,
"product" varchar(255) COLLATE "default"
)
WITH (OIDS=FALSE);

CREATE TABLE "public"."items" (
"name" varchar(255) NOT NULL COLLATE "default",
"id" int4 NOT NULL,
"price" numeric(10,2)
)

CREATE TABLE "public"."customers" (
"first_name" varchar(100) COLLATE "default",
"id" int4 NOT NULL,
"last_name" varchar(255) COLLATE "default"
)

-- purchases.item_id = items.id
-- purchases.customer_id = customers.id
CREATE TABLE "public"."purchases" (
"id" int4 NOT NULL,
"item_id" int4,
"customer_id" int4
)
UPDATE
UPDATE items set price=10.00 where name = '';
DELETE
Delete from items where id = 4;
WILDCARD
select * from customers where last_name like '%t%';
-- anything have t char in the middle

select * from customers where last_name like '%t_';
-- anything have t char in the middle, and exacly one char after
JOIN
select * from customers inner join orders on customers.id = orders.customer_id;
-- intersection like set, take the records which occur in both tables, most common

select * from customers left join orders on customers.id = orders.customer_id;
-- list all record on the left table even it doesn not match data on the right table (left blank), most common

select * from customers right join orders on customers.id = orders.customer_id;
-- list all record on the right table even it doesn not match data on the left table (left blank)

select * from customers full join orders on customers.id = orders.customer_id;
-- list all record on the both tables even it doesn not match data of each other (left blank)

select customers.first_name, customers.last_name, items.name, items.price from customers \
inner join purchases on customers.id = purchases.customer_id \
inner join items on purchases.item_id = items.id;
-- join 3 tables together, display selected columns
SUM - GROUP BY - COUNT
select customers.first_name, customers.last_name, sum(items.price), count(customers.id) from customers \
inner join purchases on customers.id = purchases.customer_id \
inner join items on purchases.item_id = items.id group by customers.id;
-- join 3 tables, calculate how much money 1 customer spend, how many items each customer bought

select sum(items.price) from items \
inner join purchases on items.id = purchases.item_id;
-- calculate total money spent
ORDER BY
select customers.first_name, customers.last_name, sum(items.price) as total_spent from customers \
inner join purchases on customers.id = purchases.customer_id \
inner join items on purchases.item_id = items.id group by customers.id order by total_spent;
-- notice ORDER BY keyword
CREATE TABLE
create table public.videos (
id int4,
customer_id int4,
name character varying(255) not null,
constraint videos_pkey primary key (id),
constraint fk_videos_customers foreign key (customer_id) references public.customers(id)
);
INSERT
INSERT into public.users values (1, 'jose');
AUTO INCREMENT
create sequence users_id_seq start 2;
-- create auto increment variable

alter table public.users
alter column id
set default nextval('users_id_seq');
-- change users.id next value by using variable just created

alter sequence users_id_seq owned by public.users.id;
-- create a dependency between sequence and id, so deleting id cause the same result on sequence
SERIAL
(easier to use than sequence)
create table public.test(
id serial primary key,
name character varying(255)
)
INDEX
(help boosting query)
create index users_name_index on public.users(name);
-- create index on single field

create index index_name on public.videos(id, user_id);
-- helpful when ofen use 2 condition query (AND)

reindex index users_name_index;
-- fixing corrupted index

reindex database learning;
-- fixing corrupted database
DROP
drop table public.users cascade;
-- remove the foreign key relationship between current table and others (2 WAYS), data is untouched
VIEW
create view total_spent_by_customer as
select customers.first_name, customers.last_name, sum(items.price) as total_spent from customers \
inner join purchases on customers.id = purchases.customer_id \
inner join items on purchases.item_id = items.id \
group by customers.id order by total_spent;
-- we can use view to shorten the query,making it much more easier to revisit the full-length query, CAN NOT INSERT to view because "group by" contained

drop view total_spent_by_customer; # drop query

create view expensive_items as
select * from items where price >= 100 with local check option;
-- will affect the insert later on, need to obey the check

insert into expensive_items(name, id, price) values ('book', 7, 7);
-- Failed, because price = 7 < 100 local check option --> can not insert

create view unluxury_items as
select * from expensive_items where price >= 1000 with local check option;
-- create view from another view
Built-in functions
AVG, SUM, COUNT
select avg(items.price) from items;
MAX
select max(items.price) from items \
inner join purchases on items.id = purchases.item_id;
-- can not select item.name or else when using max function
HAVING
select customers.first_name, customers.last_name, count(purchases.id) as \
item_count from customers \
inner join purchases on customers.id = purchases.customer_id \
group by customers.id having count(purchases.id) > 2;
-- similiar to WHERE, which can not be used in this case (already grouped), always use having to check condition when querying with GROUP BY
TYPE
create type mood as enum('angry', 'sad', 'okay', 'happy', 'horny');
-- create new custom mood
create table public.students (
name character varying(255),
current_mood mood
)
-- how to create table with custom data type
select * from students where current_mood > 'okay';
-- which return 'happy' and 'horny'
Nested select
-- select * from items where price > avg(price) --> FAILED
select * from items where price > (select avg(price) from items);
-- price larger than average price

select items.name, items.price - (select avg(price) from items) from items;
-- price diff

select items.name, items.price - (select avg(price) from items where price > 200) from items where price > 200;
-- price diff of the item price > 200