Wednesday, December 16, 2020

[Dockerfile] Trouble shooting - cannot get environment variables and permission denied on Entrypoint

 

 Docker   Dockerfile   sudo  

 

 

▌Problem


 

We have a Dockerfile that will switch user to run a shell script on ENTRYPOINT, however we got the 2 problems,

 

1.  Cannot get the environment variables in the shell script.

2.  Get permission denied after we preserve the environment variables when sudo user.

 

Here are the sample files,

 

▋Dockerfile

FROM postgres:11

ENV POSTGRES_USER postgres
ENV POSTGRES_PASSWORD xxxxx

# Add user: postgres
RUN sudo adduser postgres sudo

EXPOSE 5432
ENTRYPOINT ["/bin/sh", "-c", "sudo -u postgres sh callback.sh"]


 

▋Shell script (callback.sh)

#!/bin/bash echo POSTGRES_USER=$POSTGRES_USER
echo POSTGRES_PASSWORD=$POSTGRES_PASSWORD
echo "some logs" >> ~/my_log

The container from the above Dockerfile could not get the environment variables $POSTGRES_USER and $POSTGRES_PASSWORD, and finally output,

 

POSTGRES_USER=

POSTGRES_PASSWORD=

 

And the log file will be located at /var/lib/postgresql/my_log.

 

 

▌Environment


 

▋Docker desktop 2.3.0.3

 

 

 

▌Solution


 

▋Preserving environment variables

 

The problem was due to not preserving environment variables when switching to other user.

 

So we have to preserve the existing environment variables by adding the argument -U when sudo. (See sudo manual)

 

-E, --preserve-env: Indicates to the security policy that the user wishes to preserve their existing environment variables. The security policy may return an error if the user does not have permission to preserve the environment.

 

So we must update the following line of Dockerfile,

ENTRYPOINT ["/bin/sh", "-c", "sudo -E -u postgres sh callback.sh"]

 

But it caused the second problem: Permission denied when writing log to ~/my_log.

The output became as following,

 

POSTGRES_USER=postgres

POSTGRES_PASSWORD=xxxxx

callback.sh: 3: callback.sh: cannot create /root/my_log: Permission denied

 

 

As you can see the ~/ is changed to /root/ but not /var/lib/postgresql/, so that the user: postgres could not have the permission on ~/.

 

Finally I solved the problems by updating the Dockerfile as following,

 

 

▋Dockerfile

FROM postgres:11

ENV POSTGRES_USER postgres
ENV POSTGRES_PASSWORD xxxxx

 
# Add user: postgres
RUN sudo adduser postgres sudo

EXPOSE 5432
ENTRYPOINT ["/bin/sh", "-c", "sudo -E -u postgres sh callback.sh"]

# Or use this # ENTRYPOINT ["/bin/sh", "-c", "su --preserve-environment - postgres callback.sh'"]


 

▋Shell script (callback.sh)

#!/bin/bash echo POSTGRES_USER=$POSTGRES_USER echo POSTGRES_PASSWORD=$POSTGRES_PASSWORD echo "some logs" >> /var/lib/postgresql/my_log

 

Friday, December 11, 2020

[PostgreSQL] Partition table

 PostgreSQL   Partition table 

 

 

▌Introduction


 

This is a simple tutorial for creating partitions on an existing table in PostgreSQL.

 

 

▌Environment


 

▋PostgreSQL 11

 

 

▌Steps


 

▋Create a demo table

 

Lets create a table first. We will use [CreateOn] as the the value for partitioning.

 


CREATE TABLE "OnlineTxs"
(
    "Id"       serial NOT NULL,
    "CardNo"   varchar(19) NOT NULL,
    "Amt"      decimal(10, 0) NOT NULL,
    "CreateOn" timestamp NOT NULL
) PARTITION BY RANGE ("CreateOn"); -- Add more columns for partitioning

 

▋ Create Partitions

 

We will create the following partition tables with different boundaries.


CREATE TABLE "OnlineTxs_20201126" PARTITION OF "OnlineTxs"
    FOR VALUES FROM ('2020-11-25') TO ('2020-11-26');

CREATE TABLE "OnlineTxs_20201127" PARTITION OF "OnlineTxs"
    FOR VALUES FROM ('2020-11-26') TO ('2020-11-27');

CREATE TABLE "OnlineTxs_20201128" PARTITION OF "OnlineTxs"
    FOR VALUES FROM ('2020-11-27') TO ('2020-11-28');

CREATE TABLE "OnlineTxs_20201129" PARTITION OF "OnlineTxs"
    FOR VALUES FROM ('2020-11-28') TO ('2020-11-29');

 CREATE TABLE "OnlineTxs_20201130" PARTITION OF "OnlineTxs"
    FOR VALUES FROM ('2020-11-29') TO ('2020-11-30');    



The records will be stored in this way by the boundaries,

 

Partition table name

                    Range

OnlineTxs_20201126

>= 2020-11-25 00:00:00 & < 2020-11-26 00:00:00

OnlineTxs_20201127

>= 2020-11-26 00:00:00 & < 2020-11-27 00:00:00

OnlineTxs_20201128

>= 2020-11-27 00:00:00 & < 2020-11-28 00:00:00

OnlineTxs_20201129

>= 2020-11-28 00:00:00 & < 2020-11-29 00:00:00

OnlineTxs_20201130

>= 2020-11-29 00:00:00 & < 2020-11-30 00:00:00

 

 

Verify the partitions we just made by the following SQL.


SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent,
    nmsp_child.nspname  AS child_schema,
    child.relname       AS child_name,
    pg_get_expr(child.relpartbound, child.oid, true) as partition_expression
FROM pg_inherits
    JOIN pg_class AS parent            ON pg_inherits.inhparent = parent.oid
    JOIN pg_class AS child             ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace AS nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace AS nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='OnlineTxs'; 






▋ (Optional) Create the Primary key and Index


ALTER TABLE public."OnlineTxs" ADD PRIMARY KEY ("Id", "CreateOn");

CREATE INDEX IX_OnlineTxs_CreateOn ON public."OnlineTxs"
(
    "CreateOn" ASC
);
 

 

▋Test the partition table with data

 

We can use the following SQL to generate some data into the table.


-- Get a random integer between low and high (low <= the random interger <= high)
CREATE OR REPLACE FUNCTION fn_random_int(low INT, high INT) 
   RETURNS INT AS
$$
BEGIN
   RETURN floor(random()* (high-low + 1) + low);
END;
$$ language 'plpgsql' STRICT;

-- Create mock data
do $loop$
declare r INT;
begin
for r in 1..1000 loop
    INSERT INTO public."OnlineTxs"("CardNo","Amt","CreateOn")
    SELECT
    '123456****789' AS "CardNo",
    fn_random_int(100,9999) AS "Amt",
    '2020-11-29'::timestamp + fn_random_int(0,4) * interval '-1' day as "CreateOn";
    -- NOW() + fn_random_int(0,4) * interval '-1' day as "CreateOn";
end loop;
end;
$loop$;

 

Verify the records by the following SQL,


-- First do VACUUM ANALYZE
VACUUM ANALYZE

-- Check count from "pg_stat_user_tabls"
SELECT schemaname, relname, n_live_tup as Cnt
FROM pg_stat_user_tables
WHERE relname LIKE '%OnlineTxs%'
ORDER BY relname;

-- Or check count from "pg_inherits"
SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent,
    nmsp_child.nspname  AS child_schema,
    child.relname       AS child_name,
    pg_get_expr(child.relpartbound, child.oid, true) as partition_expression,
    SUM(child.reltuples)    AS cnt
FROM pg_inherits
     JOIN pg_class AS parent            ON pg_inherits.inhparent = parent.oid
     JOIN pg_class AS child             ON pg_inherits.inhrelid   = child.oid
     JOIN pg_namespace AS nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
     JOIN pg_namespace AS nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='OnlineTxs'
GROUP BY parent_schema, parent, child_schema, child_name, partition_expression

 


 

 

 

▋Move on more actions

 

▋Truncate a Partition

Truncating by Partition is much faster than doing deletion. Here is how to truncate a Partition table.


TRUNCATE "OnlineTxs_20201126"; 

 

▋Remove a Partition and its data


DROP TABLE "OnlineTxs_20201126";


▋Detach a Partition as a table


ALTER TABLE public."OnlineTxs" DETACH PARTITION "OnlineTxs_20201127";

 

▋Attach a detachd partition table

ALTER TABLE "OnlineTxs" ATTACH PARTITION "OnlineTxs_20201126"
FOR VALUES FROM ('2020-11-25') TO ('2020-11-26');

 

▌Reference


 

▋ Documentation: 10: 5.10. Table Partitioning - PostgreSQL

 

Thursday, November 26, 2020

[SQL Server] Partition table

 SQL Server   Partition table   

 

▌Introduction


 

This is a simple tutorial for creating partitions on an existing table in Microsoft SQL Server.

 

▌Environment


 

▋Microsoft SQL Server 2017 Express

 

 

▌Steps


 

▋Create a demo table

 

Lets create a table first. We will use [CreateOn] as the the value for partitioning.

 


CREATE TABLE [dbo].[OnlineTxs]
(
    [Id]       [numeric](18, 0) IDENTITY (1,1) NOT NULL,
    [CardNo]   [varchar](19)                   NOT NULL,
    [Amt]      [decimal](10, 0)                NOT NULL,
    [CreateOn] [datetime]                      NOT NULL,
    CONSTRAINT [PK_OnlineTxs] PRIMARY KEY NONCLUSTERED
    (
        [Id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)

 


▋ Create Partition Function and Scheme 


Use Date as the partition boundary of the Partition Function.


CREATE PARTITION FUNCTION pf_date_range (datetime)
AS RANGE RIGHT
FOR VALUES ('2020/11/23','2020/11/24')
-- FOR VALUES (CONVERT(VARCHAR(10), dateadd(day,-1, GETDATE()), 111)) -- Yesterday
GO

CREATE PARTITION SCHEME ps_date_range
    AS PARTITION pf_date_range ALL TO ([PRIMARY])
GO


 

Verify the partitions we just made by the following SQL.


SELECT ps.name,pf.name,boundary_id,value
FROM sys.partition_schemes ps
INNER JOIN sys.partition_functions pf ON pf.function_id=ps.function_id
INNER JOIN sys.partition_range_values prf ON pf.function_id=prf.function_id

 



 

The records will be stored in this way,

Partition number

Range

1

< 2020-11-23 00:00:00

2

>= 2020-11-23 00:00:00 & < 2020-11-24 00:00:00

3

>= 2020-11-24 00:00:00

 

 

 

 

▋ Create the none-clustered constraint and create clustered index

 

Furthermore, we have to recreate the constraint, the primary key must

•     Includes the Partition Boundary column: [CreateOn]

•     None-clustered

•     Be added on the Partition Function

 

And create a clustered index for the Partition Boundary column: [CreateOn].


ALTER TABLE [dbo].[OnlineTxs] DROP CONSTRAINT [PK_OnlineTxs]
GO

ALTER TABLE [dbo].[OnlineTxs]
    ADD CONSTRAINT [PK_OnlineTxs] PRIMARY KEY NONCLUSTERED ([Id] ASC, [CreateOn])
    ON ps_date_range([CreateOn]);
GO

-- Create cluster index
CREATE CLUSTERED INDEX [IX_OnlineTxs_createon] ON [dbo].[OnlineTxs] ([CreateOn])
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON ps_date_range([CreateOn])
GO

 

 

▋Test the partition table with data

 

We can use the following SQL to generate some data into the table.


CREATE FUNCTION fn_get_random (@GUID uniqueidentifier, @CEILING_VALUE INT)
RETURNS INT
AS
BEGIN
  DECLARE @RAND INT;
  SELECT @RAND = ABS(CHECKSUM(@GUID)) % @CEILING_VALUE;
  RETURN @RAND;
END

-- Create mock data
DECLARE @seq int = 0
WHILE @seq < 1000
BEGIN
    SET @seq = @seq + 1

    INSERT INTO [dbo].[OnlineTxs]([Cardno],[Amt],[CreateOn])
    SELECT '123456****789' AS [CardNo], dbo.fn_get_random(NEWID(), 9999) AS [Amt], DATEADD(day, -1 * dbo.fn_get_random(NEWID(),4), GETDATE()) AS [CreateOn]
END


 

Check the data by summarizing by Partition Number,


SELECT o.name AS [ObjectName],i.name AS [IndexName], partition_id AS [PartitionId], partition_number AS [PartitionNumber], [rows]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id=p.object_id
INNER JOIN sys.indexes i ON i.object_id=p.object_id and p.index_id=i.index_id
WHERE o.name LIKE '%OnlineTxs%'
ORDER BY [IndexName], [PartitionNumber]

 


 

Or list/count the records by Partition Number,


DECLARE @partition_number INT = 1;
SELECT COUNT(*) AS Cnt FROM [dbo].[OnlineTxs]
WHERE $PARTITION.pf_date_range([CreateOn]) = @partition_number;


 

 

▋Move on more actions

 

▋How to truncate by Partition

Truncating by Partition is much faster than doing deletion. Here is how to truncate rows in millionseconds by Partition Number.

 


DECLARE @partition_numer INT = 1;
TRUNCATE TABLE [dbo].[OnlineTxs] WITH (PARTITIONS(@partition_number));

 

 

▋How to create new Partition

Alter the Partition Function to have more Partitions,


ALTER PARTITION SCHEME ps_date_range NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION pf_date_range() SPLIT RANGE ('2020/11/25');


▋How to remove the Partition


ALTER PARTITION FUNCTION pf_date_range() MERGE RANGE(CONVERT(DATETIME, '2020-11-23'));


Notice that the parameter for RANGE() must be DATETIME.

 


▋Remove Partition Scheme and Function 


DROP PARTITION SCHEME ps_date_range;
DROP PARTITION FUNCTION pf_date_range;

 

▌Reference


 

▋How to Partition an existing SQL Server Table

 


Sunday, November 22, 2020

[Notes] 2020 Meet Taipei 創新創業嘉年華 VVIP Day

這是第一次參加 Meet Taipei 展覽,記錄一下幾個印象深刻的幾種軟體服務。

官網:https://meettaipei.tw/


報到後收到厚厚的導覽書。



  1. 線上線下整合

    DEEP INSIGHT:整合零售的數據專家,在實體通路商店整合POS機並安裝Sensor,辨識熟客以及以歷史數據分析來做即時推薦和行銷。 雖然這一塊很多大供應商都已卡位,不過對於台灣的市場來說,本地的軟硬體支援會更即時和符合當地文化,所以我認為這一塊還是相當有機會的。

    Daione:自動化跨境口碑行銷工具,媒合日韓東南亞的網紅,來做一站式的網紅口碑行銷。 這個點子相當有趣,由他們的介紹,還可以透過服務來觀察效益;在這個對於網紅,Youtuber 接受度相當高的新世代,未來可能會成為主力的行銷工具也不一定呢。

  2. 教育機器人

    女媧創造 - 凱比機器人:這個機器人很可愛,操作介面是Android based,擁有對談,互動,教學的功能。 由於在會場僅有聽業務介紹,沒有實際操作,過了幾天我還實際跑去三創一樓某電信商有實機可簡單試玩。 我相信教育機器人會逐漸變成未來教育的輔助工具 !! 期望凱比越來越進步囉!

  3. 其他雲端服務

    有線上管理訂單,訂餐,Email行銷,資產和投資管理(有的是媒介貸款 XDDD)等族繁不及備載,甚至還有媒介健身教練的服務。

    你訂NIDIN 的現場人員非常熱情,演練了一段從 Web下單到POS機接單,再由標籤機印出飲料品項的流程。 這類關鍵的差異性在於UI/UX以及整合客戶既有軟硬體的速度。由於我們辦公室還滿常使用NIDIN,也祝福他們市場越做越大囉!


一個下午下來,已經眼花撩亂啦! 萬事起頭難,成功沒有捷徑,建立客戶的信任,聯盟,安全的服務和適當的行銷,是邁向成功(或存活 XDD)的必要條件吧!