Week of June 26 Detailed Log

CRUD: Create One – Command Prompt

 mongo “mongodb+srv://cluster****.azure.mongodb.net”b–username newuser -password *****

db 

show dbs

use sqlauthoritynew

db.createCollection(“newusers”)

show collections

db.newusers.insertOne(

 {

“DisplayName”:”Pinal Dave”,

“UserName”:”pinaldave”,

“job”:{

“Title”:”DBA”,

“Area”:”Database Performance Tuning”,

“isManager”:false},

“Programming Language”:[“T-SQL”,”JS”,”HTML”]

}

)

db.newusers.find( {} )

db.newusers.find( {} ).pretty()

Demo: CRUD: Create Many – Command Prompt

cls

db.newusers.insertMany(

[

 {

“DisplayName”:”Pinal Dave”,

“UserName”:”pinaldave”,

“job”: {

“Title”:”DBA”,

“Area”:”Database Performance Tuning”,

“isManager”:”false”

},

“Programming Language”:[“T-SQL”,”JS”,”HTML”]

},

{

“DisplayName”:”Jason Brown”,

“UserName”:”jasonbrown”,

“job”: {

“Title”:”DBA”,

“Area”:”Database Performance Tuning”,

“isManager”:”true”

},

“Programming Language”:[“T-SQL”,”JS”,”HTML”]

},

 {

“DisplayName”:”Mark Smith”,

“UserName”:”marksmith”,

“job”: {

“Title”:”DBA”,

“Area”:”Database Performance Tuning”,

“isManager”:”false”,

“YearsExp”:{“$numberInt”:”5″}

},

“Programming Language”:[“T-SQL”,”HTML”]

}

]

)

db.newusers.find( {} ).pretty()

CRUD Operations: Retrieving Objects

db 

show dbs

use sample_mflix

show collections

db.movies.find({})

db.movies.find({}).pretty()

db.movies.find({runtime: 11}).pretty()

db.movies.find({runtime: 11}).pretty().limit(3)

db.movies.find({runtime: 11},{runtime:1, title:1,_id:0}).pretty().limit(3)

db.movies.find({runtime: 11},{runtime:1, title:1}).pretty().limit(3)

db.movies.find({runtime: 11},{runtime:1, title:1}).pretty().limit(5).sort({title: 1})

db.movies.find({runtime: 11},{runtime:1, title:1}).pretty().limit(5).sort({title: -1})

db.movies.find({runtime: 11},{runtime:1, title:1}).pretty().limit(5).sort({title: -1}).readConcern(“majority”)

db.movies.find({runtime: 11},{runtime:1, title:1}).pretty().limit(5).sort({title: -1}).readConcern(“linearizable”).maxTimeMS(10000)

CRUD Operations: Updating and Deleting Objects

use sample_mflix

CRUD: Update One – Command Prompt

db.movies.updateOne( {title :{$eq: “The Old Crocodile” }},{ $set: { “title”: “The New Crocodile” }} )

db.movies.find({runtime: 12},{runtime:1, title:1, year:1, _id:0}).pretty().limit(3).sort({title: -1})

db.movies.updateOne( {title :{$eq: “The New Crocodile” }},{ $set: { “title”: “The Oldest Crocodile”, “Year”: 2020 }} )

db.movies.find({runtime: 12},{runtime:1, title:1, year:1, _id:0}).pretty().limit(3).sort({title: -1})

db.movies.updateOne( {title :{$eq: “The Oldest Crocodile” }},{ $set: { “title”: “The New Crocodile”, “year”: 2020 }} )

db.movies.find({runtime: 12},{runtime:1, title:1, year:1, Year:1, _id:0}).pretty().limit(3).sort({title: -1})

CRUD: Update One – Command Prompt

db.movies.find({year: {$eq: 1988}} ).count()

db.movies.find({year: {$eq: 2025}} ).count()

db.movies.updateMany({year: {$eq: 1988}}, { $set: { “year”: 2025 }})

db.movies.updateMany({year: {$eq: 1988}}, { $set: { “year”: 2025 }},{upsert:true})

db.movies.updateMany({year: {$eq: 1988}}, { $set: { “title”:”MySuperFunnyTitle”,”awards.wins”:9 }},{upsert:true})

db.movies.find({year: {$eq: 1988}} ).pretty()

db.movies.updateMany({runtime: {$eq: 1122}}, { $set: { “title”: “MySuperFunnyTitle”,”Year”: 2020, “awards.wins”:9 }},{upsert:true, w:”majority”, wtimeout:1000})

db.movies.find({runtime: 1122}).pretty()

db.movies.replaceOne({runtime: {$eq: 1122}}, { runtime:1122, “NoTitle”: “ReplaceOneExample”, “NewYear”: 2020, “awards.losts”: 5}

Demo: CRUD: Delete – Command Prompt

db.movies.find({runtime: 25}).count()

db.movies.deleteOne( {runtime: 25})

db.movies.deleteMany( {runtime: 25})

db.movies.find({runtime: 25}).count()

db.movies.find({runtime: 35}).count()

db.movies.remove({runtime: 35}, true )

db.movies.remove({runtime: 35} )

db.movies.remove({})

show collections

show dbs

db.foo.save({_id:1, x:10})

show collections

Set Operator

db.a.save({_id:1, x:10})

db.a.find()

db.a.update({_id:1},{$set:{y:3}})

db.a.update({_id:1},{$inc:{x:1}})

db.a.find()

Unset Operator

db.a.update({_id:1},{$unset:{y:”}})

Rename Operator  

db.a.save({_id:1, Naem:’bob’})

db.a.update({_id:1},{$rename:{‘Naem’: ‘Name’}})

Push Operator  

db.a.save({_id:2})

db.a.update({_id:2},{$push:{things: ‘One’}})

db.a.update({_id:2},{$push:{things: ‘Two’}})

db.a.update({_id:2},{$push:{things: ‘Three’}})

db.a.update({_id:2},{$addToSet:{things: ‘four’}})

Pull Operator 

db.a.update({_id:2},{$push:{things: ‘Three’}})

db.a.update({_id:2},{$pull:{things: ‘Three’}})

Pop Operator 

#Remove Last field

db.a.update({_id:2},{$pop:{things: 1 }}) 

#Remove First Field

db.a.update({_id:2},{$pop:{things: -1 }})

Multi Update  

# Updates only 1st record

db.a.update({},{$push:{things: 4 }});

#Updates all records

db.a.update({},{$push:{things: 4 }},{multi:true});

#Updates all records that have 2 in the array

db.a.update({things:2} ,{$push:{things: 42}},{multi:true});

FInd

# Return where ID = 1 and show 1 field

db.a.find({_id:1},{_id:1})

db.a.find({_id: {$gt:2} }, {_id:1})

db.a.find({_id: {$lt:2} }, {_id:1})

db.a.find({_id: {$lte:2} }, {_id:1})

db.a.find({_id: {$gte:2} }, {_id:1})

#Range

db.a.find({_id: {$gte:2,$lt:4} }, {_id:1})

db.a.find({_id: {$not: {$gt:2}}}, {_id:1})

db.a.find({_id: {$in: [1,3] } }, {_id:1})

db.a.find({_id: {$nin: [1,3] } }, {_id:1})

#Sort Asc

db.a.find().sort({_id:1})

#Sort Desc

db.a.find().sort({_id:-1})

Indexes

#Show Indexes 

 db.system.indexes.find({ns:’a.things’},{key:1})

#Explain

db.a.find({things: 4}).explain()

Create Index

db.a.ensureIndex({things:1})

Drop Index

db.a.dropIndex(“things_1”)

Week of June 11 Detailed Log

SELECT * FROM bigquery-public-data.samples.shakespeare Limit 10

Enable GCP Shell

gcloud auth login

$ bq ls

$ bq ls publicdata:samples

$ bq mk spikeysales_dataset

$ bq ls

$ bq rm spikeysales_dataset

$ bq show publicdata:samples

$ bq show publicdata:samples.shakespeare

$  bq query “SELECT * FROM  publicdata:samples.shakespeare LIMIT 10”

Creating and Editing Access to a Dataset

bq show –format=prettyjson spikeysales_dataset > dataset_spikeysales.json

bq update –source dataset_spikeysales.json spikeysales_dataset

bq show –format=prettyjson spikeysales_dataset

Creating and Querying Ingestion Time Partitioned Tables

bq show –schema –format=prettyjson spikeysales_dataset.e_commerce_data

[

  {

    “mode”: “NULLABLE”,

    “name”: “RowID”,

    “type”: “INTEGER”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “OrderID”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “OrderDate”,

    “type”: “DATE”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “ShipDate”,

    “type”: “DATE”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “ShipMode”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “CustomerID”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “CustomerName”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Segment”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Address_Country”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Address_City”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Address_State”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Address_Postal_Code”,

    “type”: “INTEGER”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Address_Region”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Product_ID”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Category”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Sub_Category”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Product_Name”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Sales”,

    “type”: “FLOAT”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Quantity”,

    “type”: “INTEGER”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Discount”,

    “type”: “FLOAT”

  },

 {

    “mode”: “NULLABLE”,

    “name”: “Profit”,

    “type”: “FLOAT”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “row_number”,

    “type”: “INTEGER”

  }

]

Creating Column Based Partitioned Tables Using the Command Line

bq mk –table –schema OrderID:STRING,OrderDate:DATE,Quantity:INTEGER –time_partitioning_field OrderDate spikey-bq-279714:spikeysales_dataset.order_data

bq –location=US query –nouse_legacy_sql –destination_table=spikey-bq-279714:spikeysales_dataset.order_data ‘SELECT OrderID,OrderDate,Quantity FROM spikeysales_dataset.e_commerce_data WHERE OrderDate is NOT NULL LIMIT 1000’

bq show spikeysales_dataset.order_data

bq –location=US query –nouse_legacy_sql ‘SELECT * FROM `spikey-bq-279714.spikeysales_dataset.order_data` WHERE Orderdate=DATE(“2014-11-01”)’

Populating Data into a Table with Nested Fields Using STRUCT

bq show –schema –format=prettyjson spikeysales_dataset.e_commerce_partitioned

[

  {

    “mode”: “NULLABLE”,

    “name”: “RowID”,

    “type”: “INTEGER”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “OrderID”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “OrderDate”,

    “type”: “DATE”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “ShipDate”,

    “type”: “DATE”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “ShipMode”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “CustomerID”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “CustomerName”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Segment”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Address_Country”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Address_City”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Address_State”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Address_Postal_Code”,

    “type”: “INTEGER”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Address_Region”,

    “type”: “STRING”

  },

{

    “mode”: “NULLABLE”,

    “name”: “Product_ID”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Category”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Sub_Category”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Product_Name”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Sales”,

    “type”: “FLOAT”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Quantity”,

    “type”: “INTEGER”

  },

 {

    “mode”: “NULLABLE”,

    “name”: “Discount”,

    “type”: “FLOAT”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Profit”,

    “type”: “FLOAT”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “row_number”,

    “type”: “INTEGER”

  }

]

Design Schema with STRUCT

[

  {

    “mode”: “NULLABLE”,

    “name”: “RowID”,

    “type”: “INTEGER”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “OrderInfo”,

    “type”: “RECORD”,

    “fields”: [ 

      {

        “mode”: “NULLABLE”,

        “name”: “OrderID”,

        “type”: “STRING”

      },

      {

        “mode”: “NULLABLE”,

        “name”: “OrderDate”,

        “type”: “DATE”

      },

      {

        “mode”: “NULLABLE”,

        “name”: “Sales”,

        “type”: “FLOAT”

      },

      {

        “mode”: “NULLABLE”,

        “name”: “Quantity”,

        “type”: “INTEGER”

      }

              ]

  },

  {

    “mode”: “NULLABLE”,

    “name”: “ShipDate”,

    “type”: “DATE”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “ShipMode”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “CustomerID”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “CustomerName”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Segment”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Address”,

    “type”: “RECORD”,

    “fields”: [ 

       {

          “mode”: “NULLABLE”,

          “name”: “Country”,

          “type”: “STRING”

       },

       {

           “mode”: “NULLABLE”,

           “name”: “City”,

           “type”: “STRING”

       },

       {

           “mode”: “NULLABLE”,

           “name”: “State”,

           “type”: “STRING”

       },

       {

           “mode”: “NULLABLE”,

           “name”: “Postal_Code”,

           “type”: “INTEGER”

       },

       {

           “mode”: “NULLABLE”,

           “name”: “Region”,

           “type”: “STRING”

       }

                 ]

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Product_ID”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Category”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Sub_Category”,

    “type”: “STRING”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Product_Name”,

    “type”: “STRING”

  },

 {

    “mode”: “NULLABLE”,

    “name”: “Discount”,

    “type”: “FLOAT”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “Profit”,

    “type”: “FLOAT”

  },

  {

    “mode”: “NULLABLE”,

    “name”: “row_number”,

    “type”: “INTEGER”

  }

]

Populate Data from existing Table

INSERT INTO

   `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested` 

   (ROWID,

    OrderInfo, 

    ShipDate, 

    ShipMode, 

    CustomerID, 

    CustomerName, 

    Segment, 

    Address, 

    Product_ID, 

    Category, 

    Sub_Category, 

    Product_Name, 

    Discount, 

    Profit )

SELECT 

RowID, 

STRUCT (OrderID, OrderDate, Sales, Quantity),

ShipDate, 

ShipMode, 

CustomerID, 

CustomerName, 

Segment, 

STRUCT (Address_Country, 

       Address_City, 

       Address_State, 

       Address_Postal_Code, 

       Address_Region), 

Product_ID, 

Category, 

Sub_Category, 

Product_Name, 

Discount, 

Profit

FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_partitioned` 

Working with Repeated Fields

INSERT INTO 

`spikey-bq-279714.spikeysales_dataset.customer_order_details`

(customer_name,order_id, product_names)

VALUES

  (“John”, 434, [‘Trousers’, ‘Notebook’, ‘Football’,’Avocado’])

Populating Tables with Repeated Fields Using ARRAY_AGG

INSERT INTO

`spikey-bq-279714.spikeysales_dataset.customer_product_price` (

CustomerID,

Products_Bought,

Price_Paid )

SELECT

CustomerID,

ARRAY_AGG(Product_Name) AS Products_Bought,

ARRAY_AGG(Sales) AS Price_Paid

FROM

`spikey-bq-279714.spikeysales_dataset.e_commerce_data`

GROUP BY

CustomerID 

Using Nested and Repeated Fields Together

SELECT 

Address_City,

ARRAY_AGG (STRUCT (CustomerID,

CustomerName)) AS CustomerInfo

FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data`

GROUP BY

  Address_City

ORDER BY

  Address_City

SELECT 

Address_State,

ARRAY_AGG (STRUCT (OrderDate, 

OrderID,

Sales,

Quantity)) AS OrderInfoInfo

FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data`

GROUP BY

  Address_State

ORDER BY

  Address_State

Using UNNEST to Query Repeated Fields

SELECT CustomerID, Products

FROM `spikey-bq-279714.spikeysales_dataset.customer_product_price`,UNNEST(Products_Bought) as Products

LIMIT 1000

SELECT CustomerID, Products, Price

FROM `spikey-bq-279714.spikeysales_dataset.customer_product_price`,UNNEST(Products_Bought) as Products, UNNEST(Price_paid) as

Price

LIMIT 1000

SELECT 

  Address_State,

  Orders

FROM `spikey-bq-279714.spikeysales_dataset.orders_by_state` ,

UNNEST( OrderInfoInfo ) as Orders

LIMIT 1000 

Aggregations

SELECT 

ANY_VALUE(OrderInfo) AS Any_Order

FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`

WHERE 

Address.State = “California”

LIMIT 1000

SELECT 

CustomerID, CustomerName, AVG( OrderInfo.Sales) AS Average_Price

FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`

GROUP BY

CustomerID, CustomerName

ORDER BY 

Average_Price DESC

SELECT 

CustomerID, CustomerName, COUNTIF( OrderInfo.Sales>100) AS Num_Over_100

FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`

GROUP BY

CustomerID, CustomerName

ORDER BY 

Num_Over_100 DESC

SELECT 

STRING_AGG(CustomerName)

FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`

SELECT 

FORMAT(“%T”, ARRAY_AGG(Address.City)) AS Cities

FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`

SELECT 

FORMAT(“%T”, ARRAY_AGG(DISTINCT Address.City)) AS Cities

FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`

SELECT 

FORMAT(“%T”, ARRAY_AGG(Address.City LIMIT 5)) AS Cities

FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`

Subqueries

SELECT 

r.OrderInfo.OrderID, 

r.Product_ID, 

r.CustomerName

FROM (

  SELECT * FROM

 `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`) As r

 ORDER BY

  OrderInfo.OrderDate ASC

SELECT 

r.segment,

SUM(r.sales)

FROM (

  SELECT * FROM

 `spikey-bq-279714.spikeysales_dataset.e_commerce_data` 

  WHERE Address_State =”California”) As r

 GROUP BY

  Segment

SELECT 

Address_State,

SUM(sales) AS Total_Sales

FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data` 

  WHERE Address_State IN (

  SELECT 

   Address_State

   FROM 

   `spikey-bq-279714.spikeysales_dataset.e_commerce_data` 

   WHERE

   Shipmode = ‘Same Day’) 

  GROUP BY Address_State 

Performing Window Operations Using “Partition By” and “Order By”

SELECT 

Segment, SUM(OrderInfo.Sales) OVER (PARTITION BY Segment)

FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested` 

SELECT DISTINCT

Segment, SUM(OrderInfo.Sales) OVER (PARTITION BY Segment)

FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested` 

SELECT CustomerID, CustomerName, OrderInfo.Sales,

Address.State,

RANK() OVER (PARTITION BY Address.State ORDER BY OrderInfo.Sales DESC)

FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested` 

SELECT 

state,

city,

Total_sales,

PERCENT_RANK() OVER (PARTITION BY State ORDER BY Total_Sales ASC) Percentile_Rank

FROM (

SELECT

Address.State AS State, 

Address.City AS City, 

SUM(OrderInfo.Sales) AS Total_Sales

FROM

`spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested` 

GROUP BY

Address.State,

Address.City )

Windowing Operations Using a Window Range

SELECT 

state,

city,

Total_sales,

SUM(Total_Sales) OVER (PARTITION BY State ORDER BY Total_Sales ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 

FROM (

SELECT

Address.State AS State, 

Address.City AS City, 

SUM(OrderInfo.Sales) AS Total_Sales

FROM

`spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested` 

GROUP BY

Address.State,

Address.City )

datalab create bq-datalab-vm

Week of June 5 Detailed Log

Install Docker Compose

sudo curl -L “https://github.com/docker/compose/releases/download/1.25.5/docker-compose-$(uname -s)-$(uname -m)” -o /usr/local/bin/docker-compose

sudo chmod +x /usr/local/bin/docker-compose

sudo ln -s /usr/local/bin/docker-compose /usr/bin/docker-compose

<script type=”text/javascript” src=”https://platform.linkedin.com/badges/js/profile.js&#8221; async defer></script>

<div class=”LI-profile-badge”  data-version=”v1″ data-size=”medium” data-locale=”en_US” data-type=”horizontal” data-theme=”dark” data-vanity=”markshay”><a class=”LI-simple-link” href=’https://www.linkedin.com/in/markshay?trk=profile-badge’>Mark Shay</a></div>

#Clean up

docker ps -aq

docker stop $(docker ps -aq)

docker rm $(docker ps -aq)

docker rmi $(docker images -q)

docker system prune

docker container prune

docker image prune

docker volume prune

docker network prune

cd ..

cd compose-sample-2

docker-compose up

Control-C

docker-compose up -d

docker-compose logs

docker-compose ps

docker-compose top

docker-compose down

nano docker-compose.yml

# create your drupal and postgres config here, based off the last assignment

version: ‘2’

services:

  drupal:

    image: drupal

    ports:

      – “8080:80”

    volumes:

      – drupal-modules:/var/www/html/modules

      – drupal-profiles:/var/www/html/profiles

      – drupal-sites:/var/www/html/sites

      – drupal-themes:/var/www/html/themes

  postgres:

    image: postgres

    ports:

      – “5432:5432”

    environment:

      – POSTGRES_PASSWORD=mypasswd

volumes:

  drupal-modules:

  drupal-profiles:

  drupal-sites:

  drupal-themes:

#cleanup

docker-compose down -v

#cleanup local images

docker-compose down –rmi local

#DockerFile

#create your custom drupal image here, based of official drupal

FROM drupal:8.2

RUN apt-get update && apt-get install -y git \

    && rm -rf /var/lib/apt/lists/*

WORKDIR /var/www/html/themes

RUN git clone –branch 8.x-3.x –single-branch –depth 1 https://git.drupal.org/project/bo$

    && chown -R www-data:www-data bootstrap

WORKDIR /var/www/html

#Docker Swarm

git clone https://github.com/BretFisher/udemy-docker-mastery

docker info

docker swarm init –advertise-addr 127.0.0.1

docker node ls

docker service create alpine ping 8.8.8.8

docker service ls

docker service ps reverent_goldberg

docker service update j9lrdop3by0d –replicas 3

docker service ps reverent_goldberg

docker container ls

docker container rm -f reverent_goldberg.3.p1ks7ok0w1sm1y7ikvh3gndow

docker service ls

docker service ps reverent_goldberg

docker service rm reverent_goldberg

docker service ls

docker container ls

docker swarm init

#Build Docker Swarm

#Manager Server

docker swarm init –advertise-addr 192.168.10.2

docker swarm join –token SWMTKN-1-6aynyb5mmgyb1qy6ofikeny5axuhzk303bau9vdncnpigkclcf-500wa21q29k5rl9pw3dbllimp 192.168.10.2:2377

#Node1 

docker swarm join –token SWMTKN-1-6aynyb5mmgyb1qy6ofikeny5axuhzk303bau9vdncnpigkclcf-500wa21q29k5rl9pw3dbllimp 192.168.10.2:2377

#Node2

docker swarm join –token SWMTKN-1-6aynyb5mmgyb1qy6ofikeny5axuhzk303bau9vdncnpigkclcf-500wa21q29k5rl9pw3dbllimp 192.168.10.2:2377

#Manager Server # promote Node 1

docker node ls

docker node update –role manager worker1

docker node ls

docker swarm join-token manager

#Node3 # Join as Manager by default


docker swarm join –token SWMTKN-1-6aynyb5mmgyb1qy6ofikeny5axuhzk303bau9vdncnpigkclcf-d3j1ol33vs16ythghosga20n9 192.168.10.2:2377

#Manager Server 

docker node ls

docker service create –replicas 3 alpine ping 8.8.8.8

docker service ls

docker node ps

docker node ps worker1

#Scaling Out with Overlay Networking

docker network create –driver overlay mydrupal

docker network ls

#Create Postgres Service on Swarm

#docker service create –name psql –network mydrupal -e POSTGRES_PASSWORD=mypass postgres

docker service create –name psql –network mydrupal –publish 5432:5432 -e POSTGRES_PASSWORD=mypass postgres

#Verify Image running

docker service ls

#Verify Node which is running Service

docker service ps psql

#Run to see if service is running on server

docker container logs psql.1.oey4ud9gbd87vvp7j8i11zv8b

#Create Drupal Service on Swarm

docker service create –name drupal –network mydrupal -p 80:80 drupal

docker service ls

#repeats command every 2 seconds

watch docker service ls

docker service ps drupal

# Scaling Out with Routing Mesh

docker service create –name search –replicas 3 -p 9200:9200 elasticsearch:2

docker service ps search

curl localhost:9200

#Create A Multi-Service Multi-Node Web App

docker node ls

docker ps -a

docker service ls

#Create Network backend

docker network create -d overlay backend

#Create Network frontend

docker network create -d overlay frontend

#Vote 

docker service create –name vote -p 80:80 –network frontend –replicas 2 dockersamples/examplevotingapp_vote:before

#redis

docker service create —-name redis –network frontend –replicas 1 redis:3.2

#worker

docker service create –name worker –network frontend –network backend dockersamples/examplevotingapp_worker

#db

docker service create –name db –network backend –mount type=volume,source=db-data,target=/var/lib/postgresql/data postgres:9.4

#result

docker service create —name result —network backend -p 5001:80 dockersamples/examplevotingapp_vote:before:before

Docker service logs

#Swarm Stacks and Production Grade Compose

docker stack deploy -c example-voting-app-stack.yml voteapp

docker stack ls

docker stack ps voteapp

docker container ls

docker stack services voteapp

docker stack ps voteapp

docker network ls

nano example-voting-app-stack.yml 

replicas: 5

docker stack deploy -c example-voting-app-stack.yml voteapp

#Secrets Storage for Swarm: Protecting Your Environment Variables 

docker service create –name psql -e POSTGRES_USER_FILE=/run/secrets/psql_user -e POSTGRES_PASSWORD_FILE=/run/secrets/psql_pass postgres

docker secret ls

docker secret inspect psql_user

docker service ps psql

docker exec -it voteapp_db.1.e9oz0nv6nl41pk42lcridqv7p bash

ls /run/secrets/

cat /run/secrets/psql_user

docker container logs voteapp_db.1.e9oz0nv6nl41pk42lcridqv7p 

#Using Secrets with Swarm Stacks

docker service create –name search –replicas 2 -p 9200:9200 elasticsearch:2

docker stack deploy -c docker-compose.yml mydb

docker stack rm mydb

# create your drupal and postgres config:

nano docker-compose.yml

version: ‘3.1’

# NOTE: move this answer file up a directory so it’ll work

services:

  drupal:

    image: drupal:8.2

    ports:

      – “8080:80”

    volumes:

      – drupal-modules:/var/www/html/modules

      – drupal-profiles:/var/www/html/profiles       

      – drupal-sites:/var/www/html/sites      

      – drupal-themes:/var/www/html/themes

  postgres:

    image: postgres:12.1

    ports:

      – “5432:5432”    environment:

      – POSTGRES_PASSWORD_FILE=/run/secrets/psql-pw

    secrets:

      – psql-pw

    volumes:

      – drupal-data:/var/lib/postgresql/data

volumes:

 – drupal-data:/var/lib/postgresql/data

volumes:

  drupal-data:

  drupal-modules:

  drupal-profiles:

  drupal-sites:

  drupal-themes:

secrets:

  psql-pw:

    external: true

echo “oagy938hs” | docker secret create psql-pw – 

docker stack deploy -c docker-compose.yml drupal

#Swarm App Lifecycle

cd secrets-sample-2

docker node ls

docker-compose up -d

docker-compose exec psql cat /run/secrets/psql_user

#Full App Lifecycle: Dev, Build and Deploy With a Single Compose

docker-compose up -d

docker-compose -f docker-compose.yml -f docker-compose.test.yml up -d

docker-compose -f docker-compose.yml -f docker-compose.prod.yml config

docker-compose -f docker-compose.yml -f docker-compose.prod.yml config > output.yml

#Service Updates: Changing Things In Flight

docker service create -p 8088:80 –name web nginx:1.13.7

docker service scale web=5

docker service update –image nginx:1.13.6 web

docker service update –publish-rm 8080 –publish-add 9090:80

#Health checks in Dockerfiles

docker container run –name p1 -d postgres

docker container ls

docker container run –name p2 -d –health-cmd=”pg_isready -U postgress|| exit 1″ postgres

docker container ls

docker container inspect p2

docker service create –name psql –network mydrupal –publish 5432:5432 -e POSTGRES_PASSWORD=mypass postgres

docker service create –name psql –network mydrupal –publish 5432:5432 -e POSTGRES_PASSWORD=mypass postgres  –health-cmd=”pg_isready -U postgres || exit 1″ postgres

#Run a Private Docker Registry

docker container run -d -p 5000:5000 –name registry registry

docker container ls

docker image ls

docker pull hello-world

docker run hello-world

docker tag hello-world 127.0.0.1:5000/hello-world

docker image ls

docker push 127.0.0.1:5000/hello-world

docker image remove hello-world

docker image remove 127.0.0.1:5000/hello-world

docker pull 127.0.0.1:5000/hello-world

docker container rm registry -f

docker container run -d -p 5000:5000 –name registry -v $(pwd)/registry-data:/var/lib/registry registry

docker image ls

docker push 127.0.0.1:5000/hello-world

ll registry-data

#Docker Registry With Swarm

docker node ls

docker service create –name registry –publish 5000:5000 registry

docker service ps registry

docker pull hello-world

docker tag hello-world 127.0.0.1:5000/hello-world

docker push 127.0.0.1:5000/hello-world

docker pull nginx

docker tag nginx 127.0.0.1:5000/nginx

docker push 127.0.0.1:5000/nginx

docker service create –name nginx -p 80:80 127.0.0.1:5000/nginx

#Install Kubernetes

#Install & Configure MicroK8s Ubuntu

sudo snap install microk8s –classic –channel=1.17/stable

sudo usermod -a -G microk8s vagrant

nano .bashrc

alias kubectl=’microk8s.kubectl’

microk8s.enable dns

microk8s.status

#Our First Pod With kubectl run

kubectl version

kubectl run my-nginx –image nginx

# Pre Version 1.18

kubectl get pods

#Clean up

kubectl delete deployment my-nginx

kubectl get all

# Scaling ReplicaSets

kubectl run my-apache –image httpd

kubectl get all

kubectl scale deploy/my-apache –replicas 2 #kubectl scale deployment my-apache —replicas 2

kubectl get all

kubectl logs deployment/my-apache

kubectl logs deployment/my-apache –follow –tail 1

kubectl logs -l run=my-apache

kubectl get pods

kubectl describe pod/my-apache-5d589d69c7-jmbmh

#Watch Commmand

#Terminal Window 1

kubectl get pods -w

#Terminal Window 2

kubectl delete pod/my-apache-5d589d69c7-jmbmh

kubectl get pods

#Clean up

kubectl delete deployment my-apache

# K8s Services

#Creating a ClusterIP Service

#Terminal Window 1

kubectl get pods -w

#Terminal Window 2

kubectl create deployment httpenv –image=bretfisher/httpenv

kubectl scale deployment/httpenv –replicas=5

kubectl expose deployment/httpenv –port 8888

kubectl get service

kubectl run –generator run-pod/v1 tmp-shell –rm -it –image bretfisher/netshoot — bash

curl httpenv:8888

#Alternative method from Ubuntu

kubectl get service

curl 10.152.183.202:8888

#Creating a NodePort and LoadBalancer Service

kubectl get all

kubectl expose deployment/httpenv –port 8888 –name httpenv-np –type NodePort

kubectl get services

NAME         TYPE        CLUSTER-IP       EXTERNAL-IP   PORT(S)          AGE

httpenv      ClusterIP   10.152.183.202   <none>        8888/TCP         13m

httpenv-np   NodePort    10.152.183.136   <none>        8888:31585/TCP   70s

kubernetes   ClusterIP   10.152.183.1     <none>        443/TCP          3h26m

kubectl expose deployment/httpenv –port 8888 –name httpenv-lb –type LoadBalancer

kubectl get services

curl 10.152.183.147:8888

#Clean up

kubectl delete service/httpenv service/httpenv-np

kubectl delete service/httpenv-lb deployment/httpenv

#Kubernetes Services DNS

kubectl get namespaces

#Run, Expose, and Create Generators

kubectl create deployment test –image nginx –dry-run

kubectl create deployment test –image nginx –dry-run -o yaml

kubectl create job test –image nginx –dry-run -o yaml

kubectl create deployment test –image nginx

kubectl expose deployment/test –port 80 –dry-run -o yaml

#Clean up

kubectl delete deployment test 

#Moving to Declarative Kubernetes YAML

#Building Your YAML Files

kubectl api-resources

kubectl api-versions

#Building Your YAML Spec

kubectl explain services –recursive

kubectl explain services.spec

kubectl explain services.spec.type

kubectl explain deployment.spec.template.spec.volumes.nfs.server

# Dry Runs and Diff’s

kubectl apply -f app.yml –dry-run

kubectl apply -f app.yml

kubectl apply -f app.yml –server-dry-run

kubectl diff -f app.yml

nano app.yml

metadata:

  name: app-nginx-deployment

  servers: dmz

spec:

  replicas: 2

kubectl diff -f app.yml

# Clean up

kubectl get all

kubectl delete deployment.apps/app-nginx-deployment

kubectl delete service/app-nginx-service

#Namespaces and Context

kubectl get namespaces

kubectl get all –all-namespaces

kubectl config get-contexts

Week of May 29 Detailed Log

docker image rm nginx –force

docker container run –publish 8000:80 –detach nginx:latest

docker container ls -a

docker ps -aq

docker stop $(docker ps -aq)

rm $(docker ps -aq) –force

docker rmi $(docker images -q) –force

docker system prune

docker container logs webhost

docker container top webhost

docker container rm -f 41b 

docker run –name mongo -d mongo

docker top mongo

ps aux

docker stop mongo

docker ps

docker top mongo

docker start mongo

docker container run -d -p 3306:3306 –name db -e MYSQL_RANDOM_ROOT_PASSWORD=yes mysql

docker container logs db

GENERATED ROOT PASSWORD: yi0xe8fohPhaboiF9cicaidoow4yaCie

docker container run -d –name webserver -p 8080:80 httpd

docker ps

docker container run -d –name proxy -p 8000:80 nginx

docker container ls -a

docker stop dacbe46dd93d 623768eb2e8b 476615d3fe3e 72259e479419

docker ps -a

docker container rm dacbe46dd93d 623768eb2e8b 476615d3fe3e 72259e47941

docker image ls

docker container run -d –name nginx -p 8000:80 nginx

docker container run -d –name mysql -e MYSQL_RANDOM_ROOT_PASSWORD=true mysql

docker container ls

# List processes running in container

docker container top mysql

docker container top nginx

#details of one container config

docker container inspect mysql

# performance stats for all containers

docker container stats

docker container run -it –name proxy nginx bash

ls -al

Exit

docker container ls -a

# Start new container Interactively 

docker container run -it –name ubuntu ubuntu

apt-get update

apt-get install -y curl

curl google.com

exit

docker container start -ai ubuntu

# Run additional command in existing container

docker container exec -it mysql bash

exit

#Image is still running

docker pull alpine

docker image ls

docker container run -p 80:80 –name webhost -d nginx

docker container port webhost

80/tcp -> 0.0.0.0:80

docker container inspect –format ‘{{ .NetworkSettings.IPAddress }}’ webhost

172.17.0.4

# Show Network 

docker network ls

NETWORK ID          NAME                DRIVER              SCOPE

b5fc5fd2d6d5        bridge              bridge              local

613749eac668        host                host                local

2f8551c3a295        none                null                local

docker network inspect bridge

docker network create my_app_net

docker network ls

docker container run -d –name new_nginx –network my_app_net nginx

docker network inspect my_app_net

# Add another container on custom Network

docker network connect deb100b57c4d 900ecc0f5b4c

docker network inspect my_app_net

docker network disconnect deb100b57c4d 900ecc0f5b4c

docker container run -d –name my_nginx –network my_app_net nginx

docker inspect deb100b57c4d 

[

#Testing DNS Resolution

#Preq

#docker container exec -it my_nginx bash

#apt update

#apt install iputils-ping

#exit

docker container exec -it my_nginx ping new_nginx

# Reverse Scenario

#Preq

#docker container exec -it my_nginx bash

#apt update

#apt install iputils-ping

#exit

docker container exec -it my_nginx ping new_nginx

# CLI Testing

Terminal Window #1

$ docker container run –rm -it centos:7 bash

yum update curl

curl –version

Terminal Window #2

docker ps -a

docker container run –rm -it ubuntu:14.04 bash

apt-get update && apt-get install -y curl

 curl –version   

# DNS Round Robin Testing

# Create a Network

$ docker network create dude

docker container run -d –net dude –net-alias search elasticsearch:2

docker container ls

docker container run –rm –net alpine nslookup search

# Elastic Search Container 1

#PRE

docker container exec -it d7f343e6e0e2 bash

apt update

apt install dnsutils

Exit

# Elastic Search Container 2

docker container exec -it 37d385dd9bb5 bash

apt update

apt install dnsutils

exit

docker container run –rm –net dude centos curl -s search:9200

docker container run –rm –net dude centos curl -s search:9200

{

  “name” : “Sangre”,

  “cluster_name” : “elasticsearch”,

  “cluster_uuid” : “1kbpTYo-TZSLS35CPkR25Q”,

  “version” : {

    “number” : “2.4.6”,

    “build_hash” : “5376dca9f70f3abef96a77f4bb22720ace8240fd”,

    “build_timestamp” : “2017-07-18T12:17:44Z”,

    “build_snapshot” : false,

    “lucene_version” : “5.5.4”

  },

  “tagline” : “You Know, for Search”

}

docker container run –rm –net dude centos curl -s search:9200

{

  “name” : “Firearm”,

  “cluster_name” : “elasticsearch”,

  “cluster_uuid” : “ES9_lTNoQ2Kp9ykTkr2_-g”,

  “version” : {

    “number” : “2.4.6”,

    “build_hash” : “5376dca9f70f3abef96a77f4bb22720ace8240fd”,

    “build_timestamp” : “2017-07-18T12:17:44Z”,

    “build_snapshot” : false,

    “lucene_version” : “5.5.4”

  },

  “tagline” : “You Know, for Search”

docker container ls

#Cleanup

docker container rm -f boring_allen youthful_jackson

boring_allen

youthful_jackson

docker image ls

# Images and Their Layers:Discover the Image Cache

docker pull nginx:1.18.0-alpine

docker image ls

docker history nginx:latest

#Image Tagging and Pushing to Docker Hub

docker Hubdocker pull mysql/mysql-server

docker pull nginx:mainline

docker image tag nginx ms4446/nginx

docker image push ms4446/nginx

docker login

cat .docker/config.json

# Push repository

docker image push ms4446/nginx:testing

docker image tag ms4446/nginx ms4446/nginx:testing

docker image push ms4446/nginx:testing

# Building Images: Running Docker Builds

docker image build -t customnginx .

nano Dockerfile

FROM node:6-alpine

EXPOSE 3000

RUN apk add –update tini

RUN mdkdir -p /usr/src/app

WORKDIR /usr/src/app

COPY package.json package.json

RUN npm install && npm cache clean

COPY . .

CMD [ “tini”, “–“,”node”, “./bin/www”]

docker build -t testnode .

# Run New Docker Build

docker container run –rm -p 88:3000 testnode

#Create Tag and Push to Dockerhub

docker images

docker tag testnode ms4446/testing-node

docker push ms4446/testing-node

#Delete local Custom Image

docker image rm ms4446/testing-node

#Download Custom Image and run

docker container run –rm -p88:3000 ms4446/testing-node

# Persistent Data: Data Volumes

#Clean up

docker system prune -a

docker ps -aq

docker stop $(docker ps -aq)

docker images -a

docker rmi 30f937e841c8 9beeba249f3e -f

docker volume prune

docker rm 900ecc0f5b4c8e86f67f13fe6062e1730da168c6feb3028f8c589a7b93d63c95 -f

# Pull mysql from repo

docker pull mysql

docker image inspect mysql

docker container run -d –name mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=True mysql

docker container ls

docker container inspect mysql

docker volume ls

docker volume inspect fb395bf87187c8e3eefe7e7e16faf5ec5b7c10b479d679232d2fc02619ddcea2

docker container run -d –name mysql2 -e MYSQL_ALLOW_EMPTY_PASSWORD=True mysql

docker volume ls

docker container stop mysql

docker container stop mysql2

docker container ls

docker container ls -a

docker volume ls

docker container rm mysql mysql2

docker volume ls

#named volumes

docker container run -d –name mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=True -v mysql-db:/var/lib/mysql mysql

docker volume ls

docker volume inspect mysql-db

docker container rm -f mysqlmysql

docker container run -d –name mysql3 -e MYSQL_ALLOW_EMPTY_PASSWORD=True -v mysql-db:/var/lib/mysql mysql

docker volume ls

#Bind Mappings

docker container run -d –name nginx -p 81:80 -v $(pwd):/usr/share/nginx/html nginx

docker container run -d –name nginx2 -p 82:80 -v $(pwd):/usr/share/nginx/html nginx

ll

touch testme.txt

ls -al

echo “is it me you’re looking for?” >testme.txt

#From Second Command line Window

docker container exec -it nginx bash

cd /usr/share/nginx/html

ls -al

#Database Upgrades with Named Volumes

docker container run -d –name psql -v psql:/var/lib/postgresql/data postgres:9.6.1

docker container logs -f psql

docker container ls

docker container stop 9a5a40be80ff

docker container run -d –name psql2 -v psql:/var/lib/postgresql/data postgres:9.6.2

docker container ps -a

docker volume ls

docker container logs -f fac039e1cb84

#Edit code Running In Containers With Bind Mounts

docker pull bretfisher/jekyll-serve

docker run 80:4000 -v $(pwd):/site bretfisher/jekyll-serve

Week of May 8 Detailed Log

Videos Watched

Reference

Blog Post Read

Declarative Partitioning

CREATE TABLE test04 (data jsonb) partition by range ((data->’id’));

CREATE TABLE test04_01 PARTITION OF test04
 FOR VALUES FROM (‘1’) TO (‘2500000’);

CREATE TABLE test04_02 PARTITION OF test04
 FOR VALUES FROM (‘2500001’) TO (‘5000000’);

CREATE TABLE test04_03 PARTITION OF test04
 FOR VALUES FROM (‘5000001’) TO (‘7500000’);

CREATE TABLE test04_04 PARTITION OF test04
 FOR VALUES FROM (‘7500001’) TO (‘11000000’);

CREATE INDEX ON test04_01 USING BTREE ((data->’id’));
CREATE INDEX ON test04_02 USING BTREE ((data->’id’));
CREATE INDEX ON test04_03 USING BTREE ((data->’id’));
CREATE INDEX ON test04_04 USING BTREE ((data->’id’));

Vagrant Installation

1.  git clone https://github.com/stuartshay/Vagrant
2.  Create multiple directories under home home user dir. Only /home/ms4446/vagrant-testVagrant was created from github /home/ms4446/vagrant-test/
·       /home/ms4446/vagrant-test/conf
·       /home/ms4446/vagrant-test/postgres
·       /home/ms4446/vagrant-test/scripts
·       /home/ms4446/vagrant-test/provision
1.  Edited Vagrantfile 
2.  Ensure pg_hba.conf  postgresql.conf exist in /home/ms4446/vagrant-test/conf
3.  Ensure database_provision.sh  vagrant_provision.s /home/ms4446/vagrant-test/provision
4.  Run `vagrant init ubuntu/trusty64`
5.  cd postgres
6.  vagrant up

1) Get Docker to Run to the Vagrant VM

Git Editing

https://github.com/stuartshay/Vagrant/blob/master/ubuntu/provision/vagrant_provision.sh

Uncomment the Docker Part

2) Save the Changes you did to Github

git add .

git commit -m “Updated File”

git push

Machine Learning

Week of May 1 Detailed Log

Saturday

Logical Replication Steps

Source Server

  1. User and Authentication setup
  2. wal_level configuration
  3. Create Publication
  4. Grant rights to user

Target Server

  1. Create target table
  2. CREATE Subscription
  3. Test Replication

Source Server

sudo su – postgres

psql

CREATE ROLE Replicator REPLICATION LOGIN PASSWORD *****;

Edit Configuration Files

sudo vim /etc/postgresql/10/main/pg_hba.conf

host    all             replicator      192.168.56.103          md5

sudo vim /etc/postgresql/10/main/postgresql.conf

wal_level = logical  

Restart postgres

sudo service postgresql restart

psql

\l

\c dvdrental

\d

Film

CREATE PUBLICATION bpub FOR TABLE film;

GRANT All ON film to Replicator;

Target Server:

create database dvdrental;

sudo vim /etc/postgresql/10/main/pg_hba.conf
Edit configuration Files

host    all             replicator      192.168.56.102          md5

Source Server:

Schema only dump

pg_dump dvdrental -t film -s

pg_dump dvdrental -t film -s | psql dvdrental -h 192.168.56.103

Target Server

\c dvdrental

CREATE SUBSCRIPTION bsub CONNECTION ‘dbname=dvdrental host=192.168.56.102 user=replicator password=Welcome1’ PUBLICATION bpub;

Verify data exists Node 2

select * from film;

Test Replication with adding a new record

Source Server

INSERT INTO public.film(

film_id, title, description, release_year, language_id, rental_duration, rental_rate, 

length, replacement_cost, rating, last_update, special_features, fulltext)

VALUES (1001, ‘Mark Shay’, ‘Crazy Yankee Fan’, 2020, 1, 5, 6.12, 120, 1.99, ‘R’, ‘2020-04-25 14:50:58.951’, ‘{“Deleted Scenes”}’, ‘ace’);

Target Server

select * from film where title =’Mark Shay’;

Sunday, Monday, Tuesday

# Install initial PostgreSQL 10 cluster and verify it exists

sudo pg_lsclusters 

# create a second postgres cluster 

sudo pg_createcluster 10 replica1 

sudo pg_ctlcluster 10 replica1 status 

sudo systemctl status [email protected] 

# create archive directories for both clusters 

sudo -H -u postgres mkdir /var/lib/postgresql/pg_log_archive/main 

sudo -H -u postgres mkdir /var/lib/postgresql/pg_log_archive/replica1 

## Configure Main Cluster (Primary / Master) ################################################### 

# edit configuration file 

sudo nano /etc/postgresql/10/main/postgresql.conf 

  wal_level = replica 

  wal_log_hints = on 

  archive_mode = on # (change requires restart) 

  archive_command = ‘test ! -f /var/lib/postgresql/pg_log_archive/main/%f && cp %p /var/lib/postgresql/pg_log_archive/main/%f’ 

  max_wal_senders = 10 

  wal_keep_segments = 64 

  hot_standby = on 

# edit host based access file 

sudo nano /etc/postgresql/10/main/pg_hba.conf 

  local replication rep_user trust # DO NOT USE. Configure your own connection and authentication information 

# create replication user 

sudo -H -u postgres psql -c “CREATE USER rep_user WITH replication;” 

# restart the main cluster 

sudo systemctl restart [email protected] 

## Configure Replica1 Cluster ################################################################### 

# stop replica1 cluster 

sudo systemctl stop [email protected] 

# edit configuration file 

sudo nano /etc/postgresql/10/replica1/postgresql.conf 

  wal_level = replica 

  wal_log_hints = on 

  archive_mode = on # (change requires restart) 

  archive_command = ‘test ! -f /var/lib/postgresql/pg_log_archive/replica1/%f && cp %p /var/lib/postgresql/pg_log_archive/replica1/%f’ 

  max_wal_senders = 10 

  wal_keep_segments = 64 

  hot_standby = on 

# edit host based access file 

sudo nano /etc/postgresql/10/replica1/pg_hba.conf 

  local replication rep_user trust 

## Setup Replica1 Cluster Replication ###########################################################

# remove replica1 existing database files 

sudo su – postgres 

rm -rf /var/lib/postgresql/10/replica1 

# sync replica1 with main cluster 

pg_basebackup -D /var/lib/postgresql/10/replica1 -U rep_user -w -P -R # -X stream 

#configure recovery.conf 

# https://www.postgresql.org/message-id/attachment/8070/recovery.conf

nano /var/lib/postgresql/10/replica1/recovery.conf 

  restore_command = ‘cp /var/lib/postgresql/pg_log_archive/replica1/%f %p’ 

  recovery_target_timeline = ‘latest’ 

# start replica cluster and verify in sync 

sudo pg_ctlcluster 10 replica1 start 

tail -n 100 /var/log/postgresql/postgresql-10-replica1.log 

## Verify Replica1 Cluster In Sync ############################################################# 

# create database with some data 

sudo su – postgres 

psql -c “create database test;” -p 5432 

psql test -c ” 

create table posts ( 

  id integer, 

  title character varying(100), 

  content text, 

  published_at timestamp without time zone, 

  type character varying(100) 

); 

insert into posts (id, title, content, published_at, type) values 

(100, ‘Intro to SQL’, ‘Epic SQL Content’, ‘2018-01-01’, ‘SQL’), 

(101, ‘Intro to PostgreSQL’, ‘PostgreSQL is awesome!’, now(), ‘PostgreSQL’); 

”  

# verify data has been replicated on replica1

psql test -c “select * from posts;” -p 5433 

# stop main cluster (simulate failure condition)

sudo systemctl status [email protected] 

# promote replica1 

sudo pg_ctlcluster 10 replica1 promote 

# verify replica1 is now a master / primary cluster 

tail -n 100 /var/log/postgresql/postgresql-10-replica1.log 

psql test -c “insert into posts (id, title, content, type) values 

(102, ‘Intro to SQL Where Clause’, ‘Easy as pie!’, ‘SQL’), 

(103, ‘Intro to SQL Order Clause’, ‘What comes first?’, ‘SQL’);” -p 5433 

psql test -c “select * from posts;” -p 5433 

Ignore error in log

2020-04-27 00:12:26.278 EDT [3084] LOG:  archive command failed with exit code 1

2020-04-27 00:12:26.278 EDT [3084] DETAIL:  T

ed archive command was: test ! -f /var/lib/postgresql/pg_log_archive/main/00000001000000000000000C && cp pg_wal/00000001000000000000000C /var/lib/postgresql/pg_log_archive/replica/00000001000000000000000C

Wednesday:

PostgreSQL Replication Slots

#  Create replication slot on main

sudo su – postgres

psql 

select * from pg_create_physical_replication_slot(‘replica’);

psql 

“select * from pg_replication_slots;”

sudo nano /var/lib/postgresql/10/replica/recovery.conf

## Add to existing file

primary_slot_name =’replica’

# Restart replica to start using the new slot

sudo systemctl restart [email protected]

tail -n 1000 /var/log/postgresql/postgresql-10-replica.log

# Check Status on Slot

select * from pg_replication_slots;

# Verify Replication is still working

Test Replica as Primary

# Run on Primary

INSERT INTO posts (id, title, content, type) VALUES 

(102, ‘Intro to SQL Where Clause’, ‘Easy as pie!’,  ‘SQL’),

(103, ‘Intro to SQL Order Clause’, ‘What comes first?’, ‘SQL’);

# Run on Replica

Select * from posts;

# failover to replica

# Stop Primary Cluster

sudo systemctl stop [email protected]

#verify Secondary log

tail -n 1000 /var/log/postgresql/postgresql-10-replica.log

sudo pg_ctlcluster 10 replica promote

PostgreSQL Replication Monitoring

— on main

select * from pg_stat_replication;

select * from pg_replication_slots;

— on replica check if in standby_mode

select pg_is_in_recovery();

— stop replica and check change in status

sudo systemctl stop [email protected]

— on main

select * from pg_stat_replication;

select * from pg_replication_slots;

— start replica again

sudo systemctl start [email protected]

— on main compare WAL status using lsn diff

select pg_wal_lsn_diff(‘0/23000738′,’0/230001B0’);

— on main query to track lag in bytes

— sending_lag could indicate heavy load on primary

— receiving_lag could indicate network issues or replica under heavy load

— replaying_lag could indicate replica under heavy load

select

  pid,

  application_name,

  pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) sending_lag,

  pg_wal_lsn_diff(sent_lsn, flush_lsn) receiving_lag,

  pg_wal_lsn_diff(flush_lsn, replay_lsn) replaying_lag,

  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) total_lag

from pg_stat_replication;

— on replica can check replica locations or timing

—   pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp()

SELECT

  CASE

    WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()

    THEN 0

    ELSE EXTRACT (EPOCH FROM now() – pg_last_xact_replay_timestamp())

    END AS log_delay;

— Want to know what file a lsn refers to?

select pg_walfile_name(pg_current_wal_lsn());

Thursday:

Manual migrations Steps

  • Prepare Schema
  • Script out MSSQL Server Schema for table
  • Update all non-supported data types 
    • DATETIME-> TIMESTAMP 
    • MONEY -> NUMERIC(19,4)
    • NVARCAR -> character varying
    • sysname -> character varying
    • tinyint ->  int
    • IDENTDITY INT -> NT GENERATED ALWAYS AS IDENTITY
    • varbinary(max) ->bytea
    • SELECT EXTRACT(MONTH  -> Month)
    • SELECT EXTRACT(DAY    -> DAY)
    • DATEDIFF -> DATEPART

Remove all references to:

  • NONCLUSTERED
  • CLUSTERED
  • WITH CHECK
  • Remove All ALTER Statements with CHECK Constraint statements
  • ASC

Friday: