Yann Neuhaus
Can I do it with PostgreSQL? – 19 – Create user … identified by values
Puh, that last post in this series is already half a year old. Time is moving too fast 
Today, while being at a customer again, this question came up: Can I do something comparable in PostgreSQL to what I can do in Oracle, which is: Create a user and provide the hashed password so that the password is the same on the source and the target (which implies not knowing the password at all)? In Oracle you can find the hashed passwords in user$ where can I find that in PostgreSQL? Lets go.
When we look at the “create user” command there is no option which seems to do that:
postgres=# \h create user
Command: CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
Maybe we can just pass the hashed password? Lets try be creating a new user:
postgres=# create user u with login password 'u'; CREATE ROLE
The hashed passwords in PostgreSQL are stored in pg_shadow:
postgres=# select passwd from pg_shadow where usename = 'u';
passwd
-------------------------------------
md56277e2a7446059985dc9bcf0a4ac1a8f
(1 row)
Lets use that hash and create a new user:
postgres=# create user w login encrypted password 'md56277e2a7446059985dc9bcf0a4ac1a8f'; CREATE ROLE
Can we login as w using “u” as a password?
postgres@pgbox:/home/postgres/ [PG10] psql -X -h 192.168.22.99 -p $PGPORT -U w postgres -W Password for user u: psql: FATAL: no pg_hba.conf entry for host "192.168.22.99", user "w", database "postgres", SSL off
Ok, makes sense. After fixing that:
postgres@pgbox:/home/postgres/ [PG10] psql -X -h 192.168.22.99 -p $PGPORT -U w postgres -W Password for user w: psql: FATAL: password authentication failed for user "w"
So obviously this is not the way to do it. Do we have the same hashes in pg_shadow?
postgres=# select usename,passwd from pg_shadow where usename in ('w','u');
usename | passwd
---------+-------------------------------------
u | md56277e2a7446059985dc9bcf0a4ac1a8f
w | md56277e2a7446059985dc9bcf0a4ac1a8f
(2 rows)
Hm, exactly the same. Why can’t we login then? The answer is in the documentation:”Because MD5-encrypted passwords use the role name as cryptographic salt, …”. We can verify that be re-creating the “w” user using the same password as that of user “u”:
postgres=# drop user w;
DROP ROLE
postgres=# create user w login password 'u';
CREATE ROLE
postgres=# select usename,passwd from pg_shadow where usename in ('w','u');
usename | passwd
---------+-------------------------------------
u | md56277e2a7446059985dc9bcf0a4ac1a8f
w | md53eae63594a41739e87141e8333d15f73
(2 rows)
The hashed values are not the same anymore. What of course is working is to re-create the user with that hash:
postgres=# drop role w; DROP ROLE postgres=# create user w login password 'md53eae63594a41739e87141e8333d15f73'; CREATE ROLE
Now we should be able to login with the password ‘u':
postgres@pgbox:/home/postgres/ [PG10] psql -X -h 192.168.22.99 -p $PGPORT -U w postgres -W Password for user w: psql (10.0 dbi services build) Type "help" for help. postgres=>
Fine. Another way of getting the password hashes is to use pg_dumpall using the “–globals-only” switch:
postgres@pgbox:/home/postgres/ [PG10] pg_dumpall --globals-only > a.sql postgres@pgbox:/home/postgres/ [PG10] grep -w w a.sql CREATE ROLE w; ALTER ROLE w WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md53eae63594a41739e87141e8333d15f73';
Hope that helps.
Cet article Can I do it with PostgreSQL? – 19 – Create user … identified by values est apparu en premier sur Blog dbi services.
2018.pgconf.de, recap
Finally I am home from pgconf.de in Berlin at the beautiful Müggelsee. Beside meeting core PostreSQL people such Devrim and Bruce, Andreas and joining Jan again for great discussions and some beers, joking with Anja, being at the dbi services booth, discussing with people, kidding with Hans: was it worth the effort? Yes, it was, and here is why.
We had very interesting discussions at our booth, ranging from migrations to PostgreSQL, PostgreSQL training corporations and interest in our OpenDB appliance.
The opening session “Umdenken! 11 Gebote zum IT-Management” raised a question we do always ask our selfs as well: When you do HA how much complexity does the HA layer add? Maybe it is the HA layer that was causing the outage and that would not have happened without that? Reducing complexity is key to robust and reliable IT operations.
Listening to Bruce Momjian is always a joy: This time it was about PostgreSQL sharding. Much is already in place, some will come with PostgreSQL 11 and other stuff is being worked on for PostgreSQL 12 next year. Just check the slides which should be available for download from the website soon.
Most important: The increasing interest in PostgreSQL. We can see that at our customers, at conferences and in the interest in our blog posts about that topic. Sadly, when you have a booth, you are not able to listen to all the talks you would like to. This is the downside
So, mark your calendar: Next years date and location are already fixed: May 10, 2019, in Leipzip. I am sure we will have some updates to:
Cet article 2018.pgconf.de, recap est apparu en premier sur Blog dbi services.
Covering indexes in Oracle, and branch size
A covering index is an index that contains all the columns required by your query, so that you don’t have to do a TABLE ACCESS BY INDEX ROWID, which is the major cost of an index range scan. You don’t need any special feature to do that in Oracle. Just add the required columns at the end of the index. In the execution plan you will see the columns used as index keys for the range scan displayed in ‘access’ predicates, and the further filtering done on the remaining columns with ‘filter’ predicates. The ‘projection’ shows the columns that are returned in the rowset result.
However you may have seen that SQL Server has a special ‘INCLUDING’ keyword to separate those non-key columns added only for filtering or projection but not for access. What does it bring that Oracle doesn’t have?
An index entry is composed of a key and data associated to the key. The index is sorted on the key. The data for each key have no special order, like in a heap table. The idea of the SQL Server INCLUDING keyword is to separate the columns belonging to the key and the columns belonging to the data. It is not mandatory. You can add all columns to the key but depending on the implementation, the benefit can be:
- some data types may not be allowed in the key but allowed as data
- sorting the data when not required may be a performance overhead
- there can be limitations on the size of the key
- having a larger key may require more space in the branches
- adding sorted columns may change the clustering factor
In Oracle, there are very few data types that cannot be indexed (like LONG). The limitation on the size of the key may come into play for large 12c Extended Datatypes. You can substring them, but that defeats the goal of covering indexes. I see two reasons why ‘INCLUDING’ indexes can be useful. The first reason is about the clustering factor. The second about sorting the whole index entry and referencing it from the branches. I’ll detail those reasons later, but first here is an example.
SQL> create table DEMO (UNIQU ,RANGE ,RANDOM_TEXT ,CONSTANT_TEXT ) as select rownum UNIQU , mod(rownum,4) RANGE , dbms_random.string('u',80) RANDOM_TEXT , lpad('x',80,'x') CONSTANT_TEXT from xmltable('1 to 100000');
Table DEMO created.
SQL> commit;
Commit complete.
This table has an all-distinct-values column UNIQ, a few-distinct-values on (RANGE) and I’ll use them for the key. And I’ve two columns I’ll add as additional column for covering queries: one is with lot of distinct values (RANDOM_TEXT) and the other has few distinct values (CONSTANT_TEXT).
The first rows look like this:
SQL> select * from DEMO order by ROWID fetch first 5 rows only;
UNIQU RANGE RANDOM_TEXT CONSTANT_TEXT
----- ----- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 1 XCFNWCRCFBEPJPSHREUVVVTBUCCXLZMRPJPNQDTHWYRZRUORBPDOBCIRFHLICETULTCZTMPOCMUNQITV xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
2 2 XUSPNDOMPQKOIRCVDDTVYAGKRDGIXOSVUNMRAQLSRQGYKOFEXRQMCPXPYZYKRHHKDXGIINOUUAUJOLOO xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
3 3 ZBCVFTDSRUFIUTSIWOOOBWIRMEFUXNWLADAPUPFNPVYDLPQTOUZVXJKMGIPCGZESXFXOIYVMKNSMMZKB xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
4 0 VOIRCXFVSRVZQRZDRLQRHZWNGQJAAWJXWXJKRCJVPWYDJSZLJIOEWAMCFSRCUPSPPEKITJYHHOUQSVYQ xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
5 1 UUSAMEVRWNLPGCUVMJWVVPDAENRYKIWWMIHTUJSZRQASMTYOVQNCGZGZIJZWNSOJVSIBMMUEAXOHJCOA xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I’m adding indexes fo access on RANGE as the index key, with only the key, or covering the random or constant text:
SQL> create index DEMO_RANGE on DEMO(RANGE) pctfree 50;
Index DEMO_RANGE created.
SQL> create index DEMO_RANGE_COVERING_RANDOM on DEMO(RANGE,RANDOM_TEXT) pctfree 50;
Index DEMO_RANGE_COVERING_RANDOM created.
SQL> create index DEMO_RANGE_COVERING_CONSTANT on DEMO(RANGE,CONSTANT_TEXT) pctfree 50;
Index DEMO_RANGE_COVERING_CONSTANT created.
An additional one adding the unique column in-between:
SQL> create index DEMO_RANGE_COVERING_WITH_PK on DEMO(RANGE,UNIQU,CONSTANT_TEXT) pctfree 50;
Index DEMO_RANGE_COVERING_WITH_PK created.
And now for access with the unique column as a key:
SQL> create index DEMO_UNIQU_COVERING_RANDOM on DEMO(UNIQU,RANDOM_TEXT) pctfree 50;
Index DEMO_UNIQU_COVERING_RANDOM created.
SQL> create index DEMO_UNIQU_COVERING_CONSTANT on DEMO(UNIQU,CONSTANT_TEXT) pctfree 50;
Index DEMO_UNIQU_COVERING_CONSTANT created.
Here are some interesting stats:
SQL> exec dbms_stats.gather_table_stats(user,'DEMO');
PL/SQL procedure successfully completed.
SQL> select index_name,blevel,leaf_blocks,num_rows,clustering_factor from user_indexes where table_name='DEMO' order by 2,3;
INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS CLUSTERING_FACTOR
-------------------------------- ------ ----------- -------- -----------------
DEMO_RANGE 1 353 100000 9757
DEMO_RANGE_COVERING_RANDOM 2 2440 100000 99967
DEMO_RANGE_COVERING_CONSTANT 2 2440 100000 9757
DEMO_UNIQU_COVERING_RANDOM 2 2500 100000 2440
DEMO_UNIQU_COVERING_CONSTANT 2 2500 100000 2440
DEMO_RANGE_COVERING_WITH_PK 2 2565 100000 9757
6 rows selected.
About the size, the covering indexes have approximately the same number of leaf blocks because the included column (RANDOM_TEXT or CONSTANT_TEXT) has the same size (80 bytes). Of course, the non-covering index is smaller (but will need table access to query additional column). The key on UNIQU is slightly larger than the one on RANGE because the numbers go higher. The index with 3 columns is the largest.
Clustering factorAbout the clustering factor, there’s one outlier here which deserves an explanation. But before that, you must understand that this higher clustering factor is not important for a query using the covering index, such as a SELECT RANDOM_TEXT WHERE RANGE=0, because in that case you don’t read the table. However for some queries you may cover only the filter predicates and go to the table for projection.
But the big problem is that when you add a column to an index to address a specific query, you don’t want to risk a side effect on another query, and changing the clustering factor is a risk here. One solution is to keep the old non-covering index (DEMO_RANGE) but then the side effect is on DML overhead.
To understand the change in clustering factor we must go deeper on Oracle index key and data implementation. The ‘data’ part exists in Oracle indexes even when not specified explicitely with an INCLUDING clause. The ROWID is the data part. An index entry associates a key (the indexed columns) with a pointer to the table row (the ROWID). At least, this is for UNIQUE indexes where each key is unique.
Non-unique indexes are a special case. Actually, Oracle implements only unique key indexes. When the indexed columns are not unique, the ROWID is stored on the key part of the index entry, and there is no data part. You should read Richard Foote, Differences between Unique and Non-Unique Indexes for detailed explanation.
Branch sizeThe previous statistics displayed only the number of branch level, which was the same, but we can have more detail about the branch size with an ANALYZE INDEX.
The non-covering index has only one branch block, the root, which references all the 353 leaf blocks containing the 100000 entries, with an average of 5479/352=15 bytes per branch entry:
SQL> analyze index DEMO_RANGE validate structure offline;
Index DEMO_RANGE analyzed.
SQL> select height,blocks,lf_blks,lf_rows_len,br_rows,br_blks,br_rows_len,most_repeated_key,btree_space,used_space,pct_used,rows_per_key,blks_gets_per_access,opt_cmpr_pctsave,lf_uncmp_rows_len,lf_uncmp_blks from index_stats
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN BR_ROWS BR_BLKS BR_ROWS_LEN MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS OPT_CMPR_PCTSAVE LF_UNCMP_ROWS_LEN LF_UNCMP_BLKS
------ ------ ------- ----------- ------- ------- ----------- ----------------- ----------- ---------- -------- ------------ -------------------- ---------------- ----------------- -------------
2 384 353 1375000 352 1 5479 25000 2830616 1380479 49 25000 12502.5 19 1375000 353
The covering index with lot of distinct values for the non-key columns has more branch blocks, with an average of 34623/2439=14 bytes per branch entry:
SQL> analyze index DEMO_RANGE_COVERING_RANDOM validate structure offline;
Index DEMO_RANGE_COVERING_RANDOM analyzed.
SQL> select height,blocks,lf_blks,lf_rows_len,br_rows,br_blks,br_rows_len,most_repeated_key,btree_space,used_space,pct_used,rows_per_key,blks_gets_per_access,opt_cmpr_pctsave,lf_uncmp_rows_len,lf_uncmp_blks from index_stats
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN BR_ROWS BR_BLKS BR_ROWS_LEN MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS OPT_CMPR_PCTSAVE LF_UNCMP_ROWS_LEN LF_UNCMP_BLKS
------ ------ ------- ----------- ------- ------- ----------- ----------------- ----------- ---------- -------- ------------ -------------------- ---------------- ----------------- -------------
3 2560 2440 9475000 2439 6 34623 1 19558408 9509623 49 1 4 2 9475000 2440
Here the number of branches is higher only because there are more leaves (as we have more columns), but not because of the size in the branch entries, which are even smaller. They are smaller because the branch does not have to store the full value of all columns in order to identify one leaf block. Then, only the first bytes are needed and not the full 80 bytes of them.
The covering index with few of distinct values for the non-key columns has a lot more branch blocks, with an average of 234755/2439=96 bytes per branch entry:
SQL> analyze index DEMO_RANGE_COVERING_CONSTANT validate structure offline;
Index DEMO_RANGE_COVERING_CONSTANT analyzed.
SQL> select height,blocks,lf_blks,lf_rows_len,br_rows,br_blks,br_rows_len,most_repeated_key,btree_space,used_space,pct_used,rows_per_key,blks_gets_per_access,opt_cmpr_pctsave,lf_uncmp_rows_len,lf_uncmp_blks from index_stats
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN BR_ROWS BR_BLKS BR_ROWS_LEN MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS OPT_CMPR_PCTSAVE LF_UNCMP_ROWS_LEN LF_UNCMP_BLKS
------ ------ ------- ----------- ------- ------- ----------- ----------------- ----------- ---------- -------- ------------ -------------------- ---------------- ----------------- -------------
3 2560 2440 9475000 2439 31 234755 25000 19759108 9709755 50 25000 12503.5 86 9475000 2440
So, here the size of the branch blocks is higher because we have multiple leaves blocks with the value of COVERING_CONSTANT the second column is not sufficient to identify only one leaf block. The full 80 bytes must be stored, and the rowid in addition to it.
When the indexed column has only unique values, there is no need to store more in the branches (not the additional columns, not the rowid) and only 12 bytes are needed here on average:
SQL> analyze index DEMO_UNIQU_COVERING_RANDOM validate structure offline;
Index DEMO_UNIQU_COVERING_RANDOM analyzed.
SQL> select height,blocks,lf_blks,lf_rows_len,br_rows,br_blks,br_rows_len,most_repeated_key,btree_space,used_space,pct_used,rows_per_key,blks_gets_per_access,opt_cmpr_pctsave,lf_uncmp_rows_len,lf_uncmp_blks from index_stats
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN BR_ROWS BR_BLKS BR_ROWS_LEN MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS OPT_CMPR_PCTSAVE LF_UNCMP_ROWS_LEN LF_UNCMP_BLKS
------ ------ ------- ----------- ------- ------- ----------- ----------------- ----------- ---------- -------- ------------ -------------------- ---------------- ----------------- -------------
3 2560 2500 9688892 2499 5 29737 1 20030140 9718629 49 1 4 0 9688892 2500
As the second column is not needed, the size of branch is the same whether we use RANDOM_TEXT or CONSTANT_TEXT:
SQL> analyze index DEMO_UNIQU_COVERING_CONSTANT validate structure offline;
Index DEMO_UNIQU_COVERING_CONSTANT analyzed.
SQL> select height,blocks,lf_blks,lf_rows_len,br_rows,br_blks,br_rows_len,most_repeated_key,btree_space,used_space,pct_used,rows_per_key,blks_gets_per_access,opt_cmpr_pctsave,lf_uncmp_rows_len,lf_uncmp_blks from index_stats
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN BR_ROWS BR_BLKS BR_ROWS_LEN MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS OPT_CMPR_PCTSAVE LF_UNCMP_ROWS_LEN LF_UNCMP_BLKS
------ ------ ------- ----------- ------- ------- ----------- ----------------- ----------- ---------- -------- ------------ -------------------- ---------------- ----------------- -------------
3 2560 2500 9688892 2499 5 29737 1 20030140 9718629 49 1 4 0 9688892 2500
Now, the last one is my workaround for the higher size when adding a column that do not have a lot of distinct values: just add a column before with more distinct values. Here I use the UNIQU one, but you probably have one that can be useful for your queries.
SQL> analyze index DEMO_RANGE_COVERING_WITH_PK validate structure offline;
Index DEMO_RANGE_COVERING_WITH_PK analyzed.
SQL> select height,blocks,lf_blks,lf_rows_len,br_rows,br_blks,br_rows_len,most_repeated_key,btree_space,used_space,pct_used,rows_per_key,blks_gets_per_access,opt_cmpr_pctsave,lf_uncmp_rows_len,lf_uncmp_blks from index_stats
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN BR_ROWS BR_BLKS BR_ROWS_LEN MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS OPT_CMPR_PCTSAVE LF_UNCMP_ROWS_LEN LF_UNCMP_BLKS
------ ------ ------- ----------- ------- ------- ----------- ----------------- ----------- ---------- -------- ------------ -------------------- ---------------- ----------------- -------------
3 2688 2565 9963892 2564 6 37456 1 20557908 10001348 49 1 4 2 9963892 2565
Now you get the idea. When creating an index, or adding columns for covering index, and you have the choice of column order, then try to have their first bytes selective enough so that the branch needs only a small substring to identify each leaf block (or lower level branches).
If you want to see the details about the branch length, here are some info from block dumps. I got them with the following:
SQL> column value new_value tracefile
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
/u01/app/oracle/diag/rdbms/cdb1/CDB1/trace/CDB1_ora_6799.trc
SQL> exec for i in (select header_file, header_block from dba_segments where owner='DEMO' and segment_name='DEMO_RANGE') loop execute immediate 'alter system dump datafile '||i.header_file||' block '||(i.header_block+1); end loop;
PL/SQL procedure successfully completed.
SQL> host tail -20 &tracefile
Here is the last branch entry for the root block of DEMO_RANGE where the first column is not very selective and then the rowid is required in the branch:
row#351[3279] dba: 113261807=0x6c03cef
col 0; len 2; (2): c1 04
col 1; len 6; (6): 07 00 05 7b 00 25
Here is the last branch entry for the root block of DEMO_RANGE_COVERING_RANDOM where instead of the rowid the 3 first bytes of the RANDOM_TEXT column are sufficient:
row#3[8006] dba: 113263037=0x6c041bd
col 0; len 2; (2): c1 04
col 1; len 3; (3): 53 51 52
col 2; TERM
Here is the last branch entry for the root block of DEMO_RANGE_COVERING_CONSTANT where the full 80 bytes of CONSTANT_TEXT are not even sufficient, and the ROWID is needed as a 3rd column:
row#28[5316] dba: 117444566=0x7000fd6
col 0; len 2; (2): c1 04
col 1; len 80; (80):
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
78 78 78 78 78
col 2; len 6; (6): 07 00 05 43 00 25
Here is the last branch entry for the root block of DEMO_UNIQU_COVERING_CONSTANT where the first column is sufficient:
row#2[8026] dba: 117447160=0x70019f8
col 0; len 4; (4): c3 09 0d 04
col 1; TERM
We probably don’t need a feature like SQL Server INCLUDING indexes in most of the cases. However, this may require thinking about the order of columns, mainly:
- ensure that selective columns appear as early as possible (without compromising the index access efficiency of course) in order to lower the bytes required to address branches and leaves
- when adding columns, try to add first a column that will keep the clustering factor you had with the rowid, such as a date of insert
Cet article Covering indexes in Oracle, and branch size est apparu en premier sur Blog dbi services.
MySQL – Time mismatch between system and log files
I was working on MySQL upgrades to version 5.7 at a customer, when I found out that for my instance, time in the error log file didn’t correspond to local system time:
mysql@test1:/u01/app/mysql/admin/mysqld1/log/ [mysqld1] mysql -uroot -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) mysql@test1:/u01/app/mysql/admin/mysqld1/log/ [mysqld1] ls -ltr mysqld1.err -rw-r-----. 1 mysql mysql 13323 Apr 12 10:54 mysqld1.err mysql@test1:/u01/app/mysql/admin/mysqld1/log/ [mysqld1] tail -1 mysqld1.err 2018-04-12T08:54:55.300633Z 67 [Note] Access denied for user 'root'@'localhost' (using password: YES)
I performed the operation at 10h54 but in the error log file I saw 8h54.
I checked if there was a problem between system and instance time, but that was not the case:
mysql@test1:/u01/app/mysql/admin/mysqld1/log/ [mysqld1] date Thu Apr 12 10:56:02 CEST 2018 mysqld1-(root@localhost) [(none)]> select now(); +---------------------+ | now() | +---------------------+ | 2018-04-12 10:56:35 | +---------------------+ 1 row in set (0.00 sec)
Actually, starting from version 5.7.2, there is a variable called log_timestamps which is set to UTC as default:
mysqld1-(root@localhost) [(none)]> select @@global.log_timestamps; +-------------------------+ | @@global.log_timestamps | +-------------------------+ | UTC | +-------------------------+ 1 row in set (0.00 sec)
I modified this variable from UTC to SYSTEM:
mysqld1-(root@localhost) [(none)]> set global log_timestamps='SYSTEM'; Query OK, 0 rows affected (0.00 sec) mysqld1-(root@localhost) [(none)]> select @@global.log_timestamps; +-------------------------+ | @@global.log_timestamps | +-------------------------+ | SYSTEM | +-------------------------+ 1 row in set (0.00 sec)
and after that everything was OK, local system time was now aligned with timestamp time zone in MySQL log files:
mysql@test1:/u01/app/mysql/admin/mysqld1/log/ [mysqld1] mysql -uroot -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) mysql@test1:/u01/app/mysql/admin/mysqld1/log/ [mysqld1] ls -ltr mysqld1.err -rw-r-----. 1 mysql mysql 13323 Apr 12 10:58 mysqld1.err mysql@test1:/u01/app/mysql/admin/mysqld1/log/ [mysqld1] tail -1 mysqld1.err 2018-04-12T10:58:08.467615+01:00 69 [Note] Access denied for user 'root'@'localhost' (using password: YES)
Remember to set this variable also in the configuration file, to make this change persistent at reboot:
mysql@test1:/u01/app/mysql/admin/mysqld1/log/ [mysqld1] vi /etc/my.cnf # 12042018 - dbi services - Timestamp time zone of log files from UTC (default) to SYSTEM log_timestamps = SYSTEM
Cet article MySQL – Time mismatch between system and log files est apparu en premier sur Blog dbi services.
Deploy a Cloudera cluster with Terraform and Ansible in Azure – part 1
Deploying a Cloudera distribution of Hadoop automatically is very interesting in terms of time-saving. Infrastructure as Code tools such as Ansible, Puppet, Chef, Terraform, allow now to provision, manage and deploy configuration for large clusters.
In this blog posts series, we will see how to deploy and install a CDH cluster with Terraform and Ansible in the Azure cloud.
The first part consists of provisioning the environment with Terraform in Azure. Terraform features an extension to interact with cloud providers such as Azure and AWS. You can find here the Terraform documentation for the Azure module.
Desired architecture
Above a representation of the wished architecture for our CDH environment. 5 nodes for a testing infrastructure, including a Cloudera manager node, a second master node for the Hadoop Secondary NameNode and 3 workers.
PrerequisitesTerraform must be installed on your system. https://docs.microsoft.com/en-us/azure/virtual-machines/linux/terraform-install-configure#install-terraform
Generate a Client ID and a Client Secret from Azure CLI to authenticate in Azure with Terraform.
1. Sign in to administer your Azure subscription:
[root@centos Terraform]# az login
2. Get the subscription ID and tenant ID:
[root@centos Terraform]# az account show --query "{subscriptionId:id, tenantId:tenantId}"
3. Create separate credentials for TF:
[root@centos Terraform]# az ad sp create-for-rbac --role="Contributor" --scopes="/subscriptions/${SUBSCRIPTION_ID}"
4. Save the following information:
- subscription_id
- client_id
- client_secret
- tenant_id
Now we are ready to start using Terraform with AzureRM API.
Build your clusterWith Terraform, we will provision the following resources in Azure:
- A resource group – “Cloudera-Cluster”
- 1 virtual network – “cdh_vnet”
- 1 network security group – “cdh-nsg”
- 1 storage account – “dbistorage”
- 5 network interfaces – “instance_name_network_interface”
- 5 Public / Private IP – “cdh-pip1-4″
First, we will create a variable file, which contains all variables needed without specific values. The values are specified in the var_values.tfvars file.
variables.tf/* Configure Azure Provider and declare all the Variables that will be used in Terraform configurations */
provider "azurerm" {
subscription_id = "${var.subscription_id}"
client_id = "${var.client_id}"
client_secret = "${var.client_secret}"
tenant_id = "${var.tenant_id}"
}
variable "subscription_id" {
description = "Enter Subscription ID for provisioning resources in Azure"
}
variable "client_id" {
description = "Enter Client ID for Application created in Azure AD"
}
variable "client_secret" {
description = "Enter Client secret for Application in Azure AD"
}
variable "tenant_id" {
description = "Enter Tenant ID / Directory ID of your Azure AD. Run Get-AzureSubscription to know your Tenant ID"
}
variable "location" {
description = "The default Azure region for the resource provisioning"
}
variable "resource_group_name" {
description = "Resource group name that will contain various resources"
}
variable "vnet_cidr" {
description = "CIDR block for Virtual Network"
}
variable "subnet1_cidr" {
description = "CIDR block for Subnet within a Virtual Network"
}
variable "subnet2_cidr" {
description = "CIDR block for Subnet within a Virtual Network"
}
variable "vm_username_manager" {
description = "Enter admin username to SSH into Linux VM"
}
variable "vm_username_master" {
description = "Enter admin username to SSH into Linux VM"
}
variable "vm_username_worker1" {
description = "Enter admin username to SSH into Linux VM"
}
variable "vm_username_worker2" {
description = "Enter admin username to SSH into Linux VM"
}
variable "vm_username_worker3" {
description = "Enter admin username to SSH into Linux VM"
}
variable "vm_password" {
description = "Enter admin password to SSH into VM"
}
var_values.tfvars
subscription_id = "xxxxxxx" client_id = "xxxxxxx" client_secret = "xxxxxxx" tenant_id = "xxxxxxx" location = "YOUR-LOCATION" resource_group_name = "Cloudera-Cluster" vnet_cidr = "192.168.0.0/16" subnet1_cidr = "192.168.1.0/24" subnet2_cidr = "192.168.2.0/24" vm_username_manager = "dbi" vm_username_master = "dbi" vm_username_worker1 = "dbi" vm_username_worker2 = "dbi" vm_username_worker3 = "dbi" vm_password = "YOUR-PASSWORD"
Next, we will configure the virtual network with 1 subnet for all hosts.
network.tfresource "azurerm_resource_group" "terraform_rg" {
name = "${var.resource_group_name}"
location = "${var.location}"
}
resource "azurerm_virtual_network" "vnet" {
name = "cdh-vnet"
address_space = ["${var.vnet_cidr}"]
location = "${var.location}"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
tags {
group = "Coudera-Cluster"
}
}
resource "azurerm_subnet" "subnet_1" {
name = "Subnet-1"
address_prefix = "${var.subnet1_cidr}"
virtual_network_name = "${azurerm_virtual_network.vnet.name}"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
}
Next, we can create the storage account related to the resource group with a container.
storage.tfresource "azurerm_storage_account" "storage" {
name = "dbistorage1"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
location = "${var.location}"
account_tier = "Standard"
account_replication_type = "LRS"
tags {
group = "Coudera-Cluster"
}
}
resource "azurerm_storage_container" "container" {
name = "vhds"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
storage_account_name = "${azurerm_storage_account.storage.name}"
container_access_type = "private"
}
The next step will be to create a security group for all VM. We will implement 2 rules. Allow SSH and HTTP connection from everywhere, which it’s basically not really secure, but don’t forget that we are in a volatile testing infrastructure.
security_group.tfresource "azurerm_network_security_group" "nsg_cluster" {
name = "cdh-nsg"
location = "${var.location}"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
security_rule {
name = "AllowSSH"
priority = 100
direction = "Inbound"
access = "Allow"
protocol = "Tcp"
source_port_range = "*"
destination_port_range = "22"
source_address_prefix = "*"
destination_address_prefix = "*"
}
security_rule {
name = "AllowHTTP"
priority = 200
direction = "Inbound"
access = "Allow"
protocol = "Tcp"
source_port_range = "*"
destination_port_range = "80"
source_address_prefix = "Internet"
destination_address_prefix = "*"
}
tags {
group = "Coudera-Cluster"
}
}
Next we will create the private / public IP for our instances.
ip.tfresource "azurerm_public_ip" "manager_pip" {
name = "cdh-pip"
location = "${var.location}"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
public_ip_address_allocation = "static"
tags {
group = "Coudera-Cluster"
}
}
resource "azurerm_network_interface" "public_nic" {
name = "manager_network_interface"
location = "${var.location}"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
network_security_group_id = "${azurerm_network_security_group.nsg_cluster.id}"
ip_configuration {
name = "manager_ip"
subnet_id = "${azurerm_subnet.subnet_1.id}"
private_ip_address_allocation = "dynamic"
public_ip_address_id = "${azurerm_public_ip.manager_pip.id}"
}
tags {
group = "Coudera-Cluster"
}
}
resource "azurerm_public_ip" "master_pip" {
name = "cdh-pip1"
location = "${var.location}"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
public_ip_address_allocation = "static"
tags {
group = "Coudera-Cluster"
}
}
resource "azurerm_network_interface" "public_nic1" {
name = "master_network_interface"
location = "${var.location}"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
network_security_group_id = "${azurerm_network_security_group.nsg_cluster.id}"
ip_configuration {
name = "master_ip"
subnet_id = "${azurerm_subnet.subnet_2.id}"
private_ip_address_allocation = "dynamic"
public_ip_address_id = "${azurerm_public_ip.master_pip.id}"
}
tags {
group = "Coudera-Cluster"
}
}
resource "azurerm_public_ip" "worker1_pip" {
name = "cdh-pip2"
location = "${var.location}"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
public_ip_address_allocation = "static"
tags {
group = "Coudera-Cluster"
}
}
resource "azurerm_network_interface" "public_nic2" {
name = "worker1_network_interface"
location = "${var.location}"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
network_security_group_id = "${azurerm_network_security_group.nsg_cluster.id}"
ip_configuration {
name = "worker1_ip"
subnet_id = "${azurerm_subnet.subnet_2.id}"
private_ip_address_allocation = "dynamic"
public_ip_address_id = "${azurerm_public_ip.worker1_pip.id}"
}
tags {
group = "Coudera-Cluster"
}
}
resource "azurerm_public_ip" "worker2_pip" {
name = "cdh-pip3"
location = "${var.location}"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
public_ip_address_allocation = "static"
tags {
group = "Coudera-Cluster"
}
}
resource "azurerm_network_interface" "public_nic3" {
name = "worker2_network_interface"
location = "${var.location}"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
network_security_group_id = "${azurerm_network_security_group.nsg_cluster.id}"
ip_configuration {
name = "worker2_ip"
subnet_id = "${azurerm_subnet.subnet_2.id}"
private_ip_address_allocation = "dynamic"
public_ip_address_id = "${azurerm_public_ip.worker2_pip.id}"
}
tags {
group = "Coudera-Cluster"
}
}
resource "azurerm_public_ip" "worker3_pip" {
name = "cdh-pip4"
location = "${var.location}"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
public_ip_address_allocation = "static"
tags {
group = "Coudera-Cluster"
}
}
resource "azurerm_network_interface" "public_nic4" {
name = "worker3_network_interface"
location = "${var.location}"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
network_security_group_id = "${azurerm_network_security_group.nsg_cluster.id}"
ip_configuration {
name = "worker3_ip"
subnet_id = "${azurerm_subnet.subnet_2.id}"
private_ip_address_allocation = "dynamic"
public_ip_address_id = "${azurerm_public_ip.worker3_pip.id}"
}
tags {
group = "Coudera-Cluster"
}
}
Once the network, storage and the security group are configured we can now provision our VM instances with the following configuration:
- 5 instances
- Master and Manager VM size: Standard_DS3_v2
- Worker VM size: Standard_DS2_v2
- Centos 7.3
- 1 OS disk + 1 data disk of 100GB
resource "azurerm_virtual_machine" "la_manager" {
name = "cdh_manager"
location = "${var.location}"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
network_interface_ids = ["${azurerm_network_interface.public_nic.id}"]
vm_size = "Standard_DS3_v2"
#This will delete the OS disk automatically when deleting the VM
delete_os_disk_on_termination = true
storage_image_reference {
publisher = "OpenLogic"
offer = "CentOS"
sku = "7.3"
version = "latest"
}
storage_os_disk {
name = "osdisk-1"
vhd_uri = "${azurerm_storage_account.storage.primary_blob_endpoint}${azurerm_storage_container.container.name}/osdisk-1.vhd"
caching = "ReadWrite"
create_option = "FromImage"
}
# Optional data disks
storage_data_disk {
name = "data"
vhd_uri = "${azurerm_storage_account.storage.primary_blob_endpoint}${azurerm_storage_container.container.name}/data1.vhd"
disk_size_gb = "100"
create_option = "Empty"
lun = 0
}
os_profile {
computer_name = "manager"
admin_username = "${var.vm_username_manager}"
admin_password = "${var.vm_password}"
}
os_profile_linux_config {
disable_password_authentication = false
}
tags {
group = "Cloudera-Cluster"
}
}
# Master (High Availability)
resource "azurerm_virtual_machine" "la_master" {
name = "cdh_master"
location = "${var.location}"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
network_interface_ids = ["${azurerm_network_interface.public_nic1.id}"]
vm_size = "Standard_DS3_v2"
#This will delete the OS disk automatically when deleting the VM
delete_os_disk_on_termination = true
storage_image_reference {
publisher = "OpenLogic"
offer = "CentOS"
sku = "7.3"
version = "latest"
}
storage_os_disk {
name = "osdisk-2"
vhd_uri = "${azurerm_storage_account.storage.primary_blob_endpoint}${azurerm_storage_container.container.name}/osdisk-2.vhd"
caching = "ReadWrite"
create_option = "FromImage"
}
# Optional data disks
storage_data_disk {
name = "data"
vhd_uri = "${azurerm_storage_account.storage.primary_blob_endpoint}${azurerm_storage_container.container.name}/data2.vhd"
disk_size_gb = "100"
create_option = "Empty"
lun = 0
}
os_profile {
computer_name = "master"
admin_username = "${var.vm_username_master}"
admin_password = "${var.vm_password}"
}
os_profile_linux_config {
disable_password_authentication = false
}
tags {
group = "Cloudera-Cluster"
}
}
# Worker 1
resource "azurerm_virtual_machine" "la_worker1" {
name = "cdh_worker1"
location = "${var.location}"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
network_interface_ids = ["${azurerm_network_interface.public_nic2.id}"]
vm_size = "Standard_DS2_v2"
#This will delete the OS disk automatically when deleting the VM
delete_os_disk_on_termination = true
storage_image_reference {
publisher = "OpenLogic"
offer = "CentOS"
sku = "7.3"
version = "latest"
}
storage_os_disk {
name = "osdisk-3"
vhd_uri = "${azurerm_storage_account.storage.primary_blob_endpoint}${azurerm_storage_container.container.name}/osdisk-3.vhd"
caching = "ReadWrite"
create_option = "FromImage"
}
# Optional data disks
storage_data_disk {
name = "data"
vhd_uri = "${azurerm_storage_account.storage.primary_blob_endpoint}${azurerm_storage_container.container.name}/data3.vhd"
disk_size_gb = "100"
create_option = "Empty"
lun = 0
}
os_profile {
computer_name = "worker1"
admin_username = "${var.vm_username_worker1}"
admin_password = "${var.vm_password}"
}
os_profile_linux_config {
disable_password_authentication = false
}
tags {
group = "Cloudera-Cluster"
}
}
# Worker 2
resource "azurerm_virtual_machine" "la_worker2" {
name = "cdh_worker2"
location = "${var.location}"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
network_interface_ids = ["${azurerm_network_interface.public_nic3.id}"]
vm_size = "Standard_DS2_v2"
#This will delete the OS disk automatically when deleting the VM
delete_os_disk_on_termination = true
storage_image_reference {
publisher = "OpenLogic"
offer = "CentOS"
sku = "7.3"
version = "latest"
}
storage_os_disk {
name = "osdisk-4"
vhd_uri = "${azurerm_storage_account.storage.primary_blob_endpoint}${azurerm_storage_container.container.name}/osdisk-4.vhd"
caching = "ReadWrite"
create_option = "FromImage"
}
# Optional data disks
storage_data_disk {
name = "data"
vhd_uri = "${azurerm_storage_account.storage.primary_blob_endpoint}${azurerm_storage_container.container.name}/data4.vhd"
disk_size_gb = "100"
create_option = "Empty"
lun = 0
}
os_profile {
computer_name = "worker2"
admin_username = "${var.vm_username_worker2}"
admin_password = "${var.vm_password}"
}
os_profile_linux_config {
disable_password_authentication = false
}
tags {
group = "Cloudera-Cluster"
}
}
# Worker 3
resource "azurerm_virtual_machine" "la_worker3" {
name = "cdh_worker3"
location = "${var.location}"
resource_group_name = "${azurerm_resource_group.terraform_rg.name}"
network_interface_ids = ["${azurerm_network_interface.public_nic4.id}"]
vm_size = "Standard_DS2_v2"
#This will delete the OS disk automatically when deleting the VM
delete_os_disk_on_termination = true
storage_image_reference {
publisher = "OpenLogic"
offer = "CentOS"
sku = "7.3"
version = "latest"
}
storage_os_disk {
name = "osdisk-5"
vhd_uri = "${azurerm_storage_account.storage.primary_blob_endpoint}${azurerm_storage_container.container.name}/osdisk-5.vhd"
caching = "ReadWrite"
create_option = "FromImage"
}
# Optional data disks
storage_data_disk {
name = "data"
vhd_uri = "${azurerm_storage_account.storage.primary_blob_endpoint}${azurerm_storage_container.container.name}/data5.vhd"
disk_size_gb = "100"
create_option = "Empty"
lun = 0
}
os_profile {
computer_name = "worker3"
admin_username = "${var.vm_username_worker3}"
admin_password = "${var.vm_password}"
}
os_profile_linux_config {
disable_password_authentication = false
}
tags {
group = "Cloudera-Cluster"
}
}
Execution
We can now execute the following command from a shell environment. Note that all files must be placed in the same directory.
[root@centos Terraform]# terraform plan -var-file=var_values.tfvars
[root@centos Terraform]# terraform apply -var-file=var_values.tfvars
After few minutes, check your resources in your Azure portal.
To destroy the infrastructure, run the following command:
[root@centos Terraform]# terraform destroy -var-file=var_values.tfvars
Once our infrastructure has been fully provisioned by Terraform we can start the installation of the Cloudera distribution of Hadoop with Ansible.
Cet article Deploy a Cloudera cluster with Terraform and Ansible in Azure – part 1 est apparu en premier sur Blog dbi services.
Dbvisit Standby Archive Log Daemon
Dbvisit Standby version 8 comes with a nice feature, a daemon, which gives the benefit to send and apply the archive log automatically in the background. Bypassing the system scheduling, the daemon will facilitate customer RPO (Recovery Point Objective) and RTO (Recovery Time Objective) fine tuning. Monitoring to apply logs to the Standby only when needed, will also optimize use of resources. Originally available for the Linux based environments, the feature has been made available for the Windows based platforms starting 8.0.06. This blog will cover its implementation and show its benefit.
Demo databases environments have been easily managed thanks to DBI DMK tool.
EnvironmentDBVP : Primary Server
DBVS : Standby Server
DBVPDB_SITE1 : Primary database
DBVPDB_SITE2 : Physical Standby database
Daemon start/stop/status
oracle@DBVP:/home/oracle/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start Starting Dbvisit Daemon... Started successfully. oracle@DBVP:/home/oracle/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D status Dbvisit Daemon process is running with pid 11546. oracle@DBVP:/home/oracle/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D stop Stopping Dbvisit Daemon... Successfully stopped.
Automatic startup
In order to start the daemon automatically at boot, and easily manage its status, we will create a dbvlogdaemon Service.
[root@DBVP ~]# vi /etc/systemd/system/dbvlogdaemon.service [root@DBVP ~]# cat /etc/systemd/system/dbvlogdaemon.service [Unit] Description=DB Visit log daemon Service After=oracle.service [Service] Type=simple RemainAfterExit=yes User=oracle Group=oinstall Restart=always ExecStart=/u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start ExecStop=/u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D stop [Install] WantedBy=multi-user.target [root@DBVP ~]# chmod 644 /etc/systemd/system/dbvlogdaemon.service [root@DBVP ~]# systemctl daemon-reload [root@DBVP ~]# systemctl enable dbvlogdaemon.service
Of course this would not avoid impact in case of daemon crash which could be simulated with a kill command.
Check running daemon
oracle@DBVP:/u01/app/dbvisit/standby/ [DBVPDB] ps -ef | grep dbvctl | grep -v grep oracle 4299 1 0 08:25 ? 00:00:02 /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start oracle@DBVP:/u01/app/dbvisit/standby/ [DBVPDB] ./dbvctl -d DBVPDB_SITE1 -D status Dbvisit Daemon process is running with pid 4299. oracle@DBVS:/u01/app/dbvisit/standby/ [DBVPDB] ps -ef | grep dbvctl | grep -v grep oracle 4138 1 0 08:25 ? 00:00:01 /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start oracle@DBVS:/u01/app/dbvisit/standby/ [DBVPDB] ./dbvctl -d DBVPDB_SITE1 -D status Dbvisit Daemon process is running with pid 4138.
Daemon Parameter
# DMN_DBVISIT_INTERVAL - interval in sec for dbvisit schedule on source # DMN_MONITOR_INTERVAL - interval in sec for log monitor schedule on source # DMN_DBVISIT_TIMEOUT - max sec for a dbvisit process to complete on source # DMN_MONITOR_TIMEOUT - max sec for a monitor process to complete on source # DMN_MONITOR_LOG_NUM - number of logs to monitor on source # DMN_MAX_FAIL_NOTIFICATIONS - max number of emails sent on failure on source # DMN_BLACKOUT_STARTTIME - blackout window start time HH:MI on source # DMN_BLACKOUT_ENDTIME - blackout window end time HH:MI on source # DMN_DBVISIT_INTERVAL_DR - interval in sec for dbvisit schedule on destination # DMN_MONITOR_INTERVAL_DR - interval in sec for log monitor schedule on destination # DMN_DBVISIT_TIMEOUT_DR - max sec for a dbvisit process to complete on destination # DMN_MONITOR_TIMEOUT_DR - max sec for a monitor process to complete on destination # DMN_MONITOR_LOG_NUM_DR - number of logs to monitor on destination # DMN_MAX_FAIL_NOTIFICATIONS_DR - max number of emails sent on failure on destination # DMN_BLACKOUT_STARTTIME_DR- blackout window start time HH:MI on destination # DMN_BLACKOUT_ENDTIME_DR - blackout window end time HH:MI on destination
With the daemon, we can pause the archive send/apply process using the DMN_BLACKOUT parameters.
To setup our lab we will act on the most important parameters :
- DMN_MONITOR_INTERVAL (Primary) and DMN_MONITOR_INTERVAL_DR (Standby).
The Monitor Interval will give the frequency for Dbvisit to check for new archive log and only act if existing. - DMN_DBVISIT_INTERVAL (Primary) and DMN_DBVISIT_INTERVAL_DR (Standby)
The Dbvisit Interval will give the frequency for Dbvisit to force a send/apply process. This action will be dependent of the LOGSWITCH DCC parameter. Recommendation is not to go below 5 minutes.
oracle@DBVP:/oracle/u01/app/dbvisit/standby/conf/ [DBVPDB] pwd /oracle/u01/app/dbvisit/standby/conf oracle@DBVP:/u01/app/dbvisit/standby/conf/ [DBVPDB] egrep 'DMN_DBVISIT_INTERVAL|DMN_MONITOR_INTERVAL' dbv_DBVPDB_SITE1.env # DMN_DBVISIT_INTERVAL - interval in sec for dbvisit schedule on source # DMN_MONITOR_INTERVAL - interval in sec for log monitor schedule on source # DMN_DBVISIT_INTERVAL_DR - interval in sec for dbvisit schedule on destination # DMN_MONITOR_INTERVAL_DR - interval in sec for log monitor schedule on destination DMN_DBVISIT_INTERVAL = 300 DMN_MONITOR_INTERVAL = 60 DMN_DBVISIT_INTERVAL_DR = 300 DMN_MONITOR_INTERVAL_DR = 60
The LOGSWITCH parameter determines if a database log switch (alter system switch logfile) should be trigger at Dbvisit execution.
N (default value) : Only if there are no new archive logs to transfer.
Y : At every execution, independently of the archive log creation.
I(Ignore) : Never. To be use with caution.
A daemon restart is mandatory post DDC configuration file updates.
[root@DBVP ~]# service dbvlogdaemon stop Redirecting to /bin/systemctl stop dbvlogdaemon.service [root@DBVP ~]# service dbvlogdaemon start Redirecting to /bin/systemctl start dbvlogdaemon.service [root@DBVS ~]# service dbvlogdaemon stop Redirecting to /bin/systemctl stop dbvlogdaemon.service [root@DBVS ~]# service dbvlogdaemon start Redirecting to /bin/systemctl start dbvlogdaemon.service
Send and apply archive log demo
Get current date and primary current sequence.
SQL> select sysdate from dual; SYSDATE ------------------- 2018/03/28 12:30:50 SQL> select max(sequence#) from v$log; MAX(SEQUENCE#) -------------- 179
Generate a Dbvisit gap report.
oracle@DBVP:/u01/app/dbvisit/standby/conf/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -i ============================================================= Dbvisit Standby Database Technology (8.0.16_0_g4e0697e2) (pid 21393) dbvctl started on DBVP: Wed Mar 28 12:30:57 2018 ============================================================= Dbvisit Standby log gap report for DBVPDB_SITE1 thread 1 at 201803281230: ------------------------------------------------------------- Destination database on DBVS is at sequence: 178. Source database on DBVP is at log sequence: 179. Source database on DBVP is at archived log sequence: 178. Dbvisit Standby last transfer log sequence: 178. Dbvisit Standby last transfer at: 2018-03-28 12:29:14. Archive log gap for thread 1: 0. Transfer log gap for thread 1: 0. Standby database time lag (DAYS-HH:MI:SS): +00:01:27. ============================================================= dbvctl ended on DBVP: Wed Mar 28 12:31:06 2018 =============================================================
No archive log needs to be send and apply on the standby. Both databases are in sync.
Generate logfile switch
SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered.
Check current date and primary database current sequence.
SQL> select sysdate from dual; SYSDATE ------------------- 2018/03/28 12:31:29 SQL> select max(sequence#) from v$log; MAX(SEQUENCE#) -------------- 182
Generate new Dbvisit gap reports.
oracle@DBVP:/u01/app/dbvisit/standby/conf/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -i ============================================================= Dbvisit Standby Database Technology (8.0.16_0_g4e0697e2) (pid 21454) dbvctl started on DBVP: Wed Mar 28 12:31:38 2018 ============================================================= Dbvisit Standby log gap report for DBVPDB_SITE1 thread 1 at 201803281231: ------------------------------------------------------------- Destination database on DBVS is at sequence: 178. Source database on DBVP is at log sequence: 182. Source database on DBVP is at archived log sequence: 181. Dbvisit Standby last transfer log sequence: 178. Dbvisit Standby last transfer at: 2018-03-28 12:29:14. Archive log gap for thread 1: 3. Transfer log gap for thread 1: 3. Standby database time lag (DAYS-HH:MI:SS): +00:02:27. ============================================================= dbvctl ended on DBVP: Wed Mar 28 12:31:47 2018 =============================================================
We can see that we have 3 new archive logs to transfer and to apply on the standby. There is a 3 sequences lag between both databases.
oracle@DBVP:/u01/app/dbvisit/standby/conf/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -i ============================================================= Dbvisit Standby Database Technology (8.0.16_0_g4e0697e2) (pid 21571) dbvctl started on DBVP: Wed Mar 28 12:32:19 2018 ============================================================= Dbvisit Standby log gap report for DBVPDB_SITE1 thread 1 at 201803281232: ------------------------------------------------------------- Destination database on DBVS is at sequence: 178. Source database on DBVP is at log sequence: 182. Source database on DBVP is at archived log sequence: 181. Dbvisit Standby last transfer log sequence: 181. Dbvisit Standby last transfer at: 2018-03-28 12:32:13. Archive log gap for thread 1: 3. Transfer log gap for thread 1: 0. Standby database time lag (DAYS-HH:MI:SS): +00:02:27. ============================================================= dbvctl ended on DBVP: Wed Mar 28 12:32:27 2018 =============================================================
3 archive logs has been automatically transferred by the daemon to the standby in the next minute.
oracle@DBVP:/u01/app/dbvisit/standby/conf/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -i ============================================================= Dbvisit Standby Database Technology (8.0.16_0_g4e0697e2) (pid 21679) dbvctl started on DBVP: Wed Mar 28 12:33:00 2018 ============================================================= Dbvisit Standby log gap report for DBVPDB_SITE1 thread 1 at 201803281233: ------------------------------------------------------------- Destination database on DBVS is at sequence: 181. Source database on DBVP is at log sequence: 182. Source database on DBVP is at archived log sequence: 181. Dbvisit Standby last transfer log sequence: 181. Dbvisit Standby last transfer at: 2018-03-28 12:32:13. Archive log gap for thread 1: 0. Transfer log gap for thread 1: 0. Standby database time lag (DAYS-HH:MI:SS): +00:01:13. ============================================================= dbvctl ended on DBVP: Wed Mar 28 12:33:09 2018 =============================================================
Another minute later the standby daemon applied the new archive logs. Both databases are on sync.
Conclusion
Dbvisit new daemon feature is adding real flexibility in sending and applying archive logs, and help improving customer RPO and RTO. We still might want to keep a daily crontab gap report with email to be sent to a DBA team. This will ensure to monitor daemon keep alive.
Logswitch and sending archive logs to standby consumes real system resource. Dbvisit daemon will also help fine tuning the use of the resource.
Note that the daemon processes must be restarted after each daylight saving clock change.
Cet article Dbvisit Standby Archive Log Daemon est apparu en premier sur Blog dbi services.
Automating Oracle Linux Installation with Kickstart
If you need to setup from scratch several Oracle Linux systems for your Oracle databases, it can be boring to repeat the install tasks again and again on each servers.
Automation and standardization are the keys.
Kickstart can provide an easy way to accomplish mass deployment.
Kickstart will use a Kickstart configuration file to perform the deployment.
Maintaining ready to go Kickstart configurations is easy.
We will use in our demo an ftp server to store and access our configuration file.
Direct access to the video:
On an oralinux 7.2 server, just type following command to install an ftp server + an ftp client
yum install vsftpd ftp lftp
Then adapt timeout parameter to avoid disconnection when deploying your server.
Be sure anonymous access is enable.
[root@localhost ~]# sed '/^#/d' /etc/vsftpd/vsftpd.conf
anonymous_enable=YES local_enable=YES write_enable=YES local_umask=022 dirmessage_enable=YES xferlog_enable=YES connect_from_port_20=YES xferlog_std_format=YES idle_session_timeout=6000 data_connection_timeout=1200 listen=NO listen_ipv6=YES pam_service_name=vsftpd userlist_enable=YES tcp_wrappers=YES
and start your ftpd server.
systemctl start vsftpd
Then put your kickstart configuration file in it. i will explain the file later:
vi /var/ftp/pub/myksfile.ks
And copy/paste the whole content. I will explain the file later:
########################################################################
########################################################################
## ##
## Kickstart for OEL7 : olg.dbi-services.com ##
## ##
########################################################################
########################################################################
# install through HTTP
########################################################################
install
cdrom
# locale settings
########################################################################
lang en_US.UTF-8
keyboard --vckeymap=ch --xlayouts='ch'
timezone --utc Europe/Zurich
# X is not configured on the installed system.
########################################################################
skipx
# installation mode
########################################################################
text
reboot --eject
# Partition table initialization
########################################################################
zerombr
# Network configuration
# Oracle Linux 7: How to modify Network Interface names (Doc ID 2080965.1)
########################################################################
### network --device eth0 --bootproto static --ip 192.168.56.102 --netmask 255.255.255.0 --gateway 192.168.56.1 --nameserver it.dbi-services.com --hostname olg.dbi-services.com net.ifnames=0
# security settings
########################################################################
rootpw toor
firewall --enabled --ssh
selinux --enforcing
authconfig --enableshadow --passalgo=sha512
# Partitioning and bootloader
########################################################################
# only 1 disk presented to the O.S during installation time
# net.ifnames=0 to use eth name for network devices
bootloader --location=mbr --append="nofb quiet splash=quiet crashkernel=auto net.ifnames=0"
firstboot --disable
clearpart --all --initlabel
part /boot --fstype xfs --ondisk=/dev/sda --size=512
part swap --size=2048 --ondisk=/dev/sda
part pv.01 --size=100 --ondisk=/dev/sda --grow
volgroup RHELVG pv.01
logvol / --fstype xfs --name=RootLV --vgname=RHELVG --size=8196
logvol /usr --fstype xfs --name=UsrLV --vgname=RHELVG --size=2048
logvol /tmp --fstype xfs --name=TmpLV --vgname=RHELVG --size=2048
logvol /var --fstype xfs --name=VarLV --vgname=RHELVG --size=4096
logvol /var/log/audit --fstype xfs --name=AuditLV --vgname=RHELVG --size=2048
logvol /opt --fstype xfs --name=OptLV --vgname=RHELVG --size=2048
logvol /home --fstype xfs --name=HomeLV --vgname=RHELVG --size=2048
logvol /u01 --fstype xfs --name=u01LV --vgname=RHELVG --size=2048
# packages + RPMs
########################################################################
%packages
@base
# system components
device-mapper-multipath
kexec-tools
lvm2
e4fsprogs
sg3_utils
lsscsi
dstat
ntp
perl
postfix
bc
# VI
vim-common
vim-enhanced
# SELINUX
setroubleshoot
setroubleshoot-server
setroubleshoot-plugins
%end
# POST installations tasks
########################################################################
%post
modprobe --first-time bonding
# VLAN kernel module
# modprobe --first-time 8021q
# configure bond
################
echo "DEVICE=bond0
TYPE=Bond
BONDING_MASTER=yes
BOOTPROTO=static
IPADDR=192.168.56.149
NETMASK=255.255.255.0
GATEWAY=192.168.56.1
BONDING_OPTS=\"mode=active-backup miimon=100\"
ONPARENT=yes
ONBOOT=yes" > /etc/sysconfig/network-scripts/ifcfg-bond0
echo "DEVICE=eth0
ONBOOT=yes
MASTER=bond0
BOOTPROTO=none
NM_CONTROLLED=no
SLAVE=yes" > /etc/sysconfig/network-scripts/ifcfg-eth0
echo "DEVICE=eth1
ONBOOT=yes
MASTER=bond0
BOOTPROTO=none
NM_CONTROLLED=no
SLAVE=yes" > /etc/sysconfig/network-scripts/ifcfg-eth1
echo "DEVICE=eth2
ONBOOT=yes
BOOTPROTO=dhcp
NM_CONTROLLED=no
" > /etc/sysconfig/network-scripts/ifcfg-eth2
rm -f /etc/sysconfig/network-scripts/ifcfg-en*
systemctl restart network
systemctl stop NetworkManager.service
systemctl disable NetworkManager.service
# Switch to Postfix
###################
alternatives --set mta /usr/sbin/sendmail.postfix
# HOSTS FILE
############
cat >> /etc/hosts <> /etc/ntp.conf
# DNS config
#############
cat > /etc/resolv.conf < /etc/postfix/main.cf < /etc/postfix/master.cf <> /etc/postfix/generic
postmap /etc/postfix/generic
# user management + SUDO privilege delegation
########################################################################
adduser admora
echo toor | passwd admora --stdin
echo "admora ALL=NOPASSWD: ALL
#admora ALL = NOPASSWD: /bin/su - oracle , /bin/su -" >> /etc/sudoers
# Enable services
########################################################################
systemctl enable ntpd.service
systemctl start ntpd.service
systemctl enable ntpdate.service
# Oracle +Nagios prereqs
########################################################################
yum -y install oracle-rdbms-server-11gR2-preinstall oracle-rdbms-server-12cR1-preinstall oracle-database-server-12cR2-preinstall
yum -y install openssl openssl-devel
yum -y install net-tools
# as of ALUA RHEL7.4 incompatibilities (stay on 7.2 and lock repo. later)
#yum -y update
# Oracle tuned configuration
########################################################################
mkdir -p /usr/lib/tuned/dbiOracle
cat > /usr/lib/tuned/dbiOracle/tuned.conf < /sys/class/fc_host/host1/issue_lip
echo 1 > /sys/class/fc_host/host2/issue_lip
echo "# Format:
# alias wwid
#
LUN_ORAFRA 360030d90466abf0660191bde985bba15
LUN_ORADBF 360030d906382c2065827918ddb6506da" >> /etc/multipath/bindings
cat > /etc/multipath.conf <<EOF
defaults {
polling_interval 60
}
blacklist {
devnode "^sd[a]"
devnode "^(zram|ram|raw|loop|fd|md|dm-|sr|scd|st)[0-9]*"
devnode "^hd[a-z]"
devnode "^cciss!c[0-9]d[0-9]*"
}
blacklist_exceptions {
wwid "360030d90466abf0660191bde985bba15"
wwid "360030d906382c2065827918ddb6506da"
#vendor "DataCore"
#product "Virtual Disk"
}
devices {
device {
vendor "DataCore"
product "Virtual Disk"
path_checker tur
prio alua
failback 10
no_path_retry fail
dev_loss_tmo infinity
fast_io_fail_tmo 5
rr_min_io_rq 100
# Alternative option – See notes below
# rr_min_io 100
path_grouping_policy group_by_prio
# Alternative policy - See notes below
# path_grouping_policy failover
# optional - See notes below
user_friendly_names yes
}
}
EOF
systemctl reload multipathd
# final post steps (Bugs, security)
####################################
systemctl disable rdma.service
touch /.autorelabel
dracut -f
%end
Test that you can access anonymously to your file through ftp with your browser
ftp://192.168.56.101/pub/myksfile.ks
Or via an ftp client
$ lftp ftp://192.168.56.101
lftp 192.168.56.101:~> cat /pub/myksfile.ksYou can now deploy your Oracle Linux server for a new database:
When you arrive on the installation screen,
Booting from dvd, press ESC to get the boot prompt and type
For the demo, I’m using Virtual Box VM, + 1 dvd drive for the ISO file i have downloaded from the oracle site: V100082-01.iso (oralinux7.2)
linux ks=ftp://192.168.56.101/pub/myksfile.ks
Then press ENTER as shown in this demo:
Here, if you don’t get RNETLINK answers: File exists, something is wrong in your network configuration.
At this step, if you see the green line, it’s mean you entered in anaconda and that your installation process is ongoing.
If you receive some Pane errors, once again, something is wrong in the network configuration. This is the hard part. Depending of the customer infrastructure, you could nee to set up ip manually.
Below 2 examples: one using a static IP configuration and the other a VLAN configuration.
static IP configuration
linux ip=192.168.56.102 netmask=255.255.255.0 gateway=192.168.56.1 servername=it.dbi-services.com ks=ftp://192.168.56.101/pub/myksfile.ks net.ifnames=0
static IP configuration with use of VLAN (VLANID=27 in this example)
linux ip=192.168.56.102 netmask=255.255.255.128 gateway=192.168.56.1 servername=it.dbi-services.com ks=ftp://192.168.56.1/myksfile.ks net.ifnames=0 vlan=VLAN27.27:eth0
Anaconda will now perform the partitioning part:
For the demo, I’m using a 40G disk. If you don’t give enough space, or if you have done some errors in your configuration, you will be prompt to fix the configuration issues. You would better restart the installation from the beginning.
# Partitioning and bootloader ######################################################################## # only 1 disk presented to the O.S during installation time # net.ifnames=0 to use eth name for network devices bootloader --location=mbr --append="nofb quiet splash=quiet crashkernel=auto net.ifnames=0" firstboot --disable clearpart --all --initlabel part /boot --fstype xfs --ondisk=/dev/sda --size=512 part swap --size=2048 --ondisk=/dev/sda part pv.01 --size=100 --ondisk=/dev/sda --grow volgroup RHELVG pv.01 logvol / --fstype xfs --name=RootLV --vgname=RHELVG --size=8196 logvol /usr --fstype xfs --name=UsrLV --vgname=RHELVG --size=2048 logvol /tmp --fstype xfs --name=TmpLV --vgname=RHELVG --size=2048 logvol /var --fstype xfs --name=VarLV --vgname=RHELVG --size=4096 logvol /var/log/audit --fstype xfs --name=AuditLV --vgname=RHELVG --size=2048 logvol /opt --fstype xfs --name=OptLV --vgname=RHELVG --size=2048 logvol /home --fstype xfs --name=HomeLV --vgname=RHELVG --size=2048 logvol /u01 --fstype xfs --name=u01LV --vgname=RHELVG --size=2048
When the partitioning part is finish, the package installation process will begin.
You can add personalize the packages you want install from the dvd.
# packages + RPMs ######################################################################## %packages @base # system components device-mapper-multipath kexec-tools lvm2 e4fsprogs sg3_utils lsscsi dstat ntp perl postfix bc
During the installation, you can TAB between console to get more information on what’s going on.
Console 2 permit you to type shell commands:
For the demo, I’m using 3 Ethernet cards: 2 for the bonding, 1 NAT for internet connection.
With ip a command, i can see which the interface names and IP i’m currently using during the installation process:
Because I set net.ifnames=0, eth will be used after rebooting for my netcard interfaces name. I will configure them in the POST installations tasks.
bootloader --location=mbr --append="nofb quiet splash=quiet crashkernel=auto net.ifnames=0
Switching between Console 1 / Console 3 / Console 5 permit to see what anaconda is doing. Interesting part it the %%post message.
It means you are in the POST installations tasks.
Configuration files of your system can be modified.
In my demo, i will configure bonding, postfix, and multipathing and yum install oracle-database-server-12cR2-preinstall package with dependencies !
The script coming from the kickstart configuration file is stored in the /tmp folders. It is called /tmp/ks-script-JeYnWI.log
After reboot, you can inspect it if you like to.
Cet article Automating Oracle Linux Installation with Kickstart est apparu en premier sur Blog dbi services.
Password Validation in MySQL
Since version 5.6.6 MySQL provides a new security plugins named Password Validation Plugin. The password-validation plugin aims to test passwords strength and improve security. The goal of this blog is to provide you a short overview of the functionalities provided through this plugin and illustrate these functionalities with concrete examples.
As explained into the documentation The validate_password plugin implements two capabilities:
1. The plugin checks the password against the current password policy and rejects the password if it is weak
2. The VALIDATE_PASSWORD_STRENGTH() SQL function assesses the strength of potential passwords. The function takes a password argument and returns an integer from 0 (weak) to 100 (strong).
validate_password plugin implements three level of password checking that are described below:
- LOW – policy tests password length only.
- MEDIUM (Default) – policy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase character, 1 uppercase character, and 1 special (nonalphanumeric) character
- STRONG – policy adds the condition that password substrings of length 4 or longer must not match words in the dictionary file, if one has been specified.
validate_password plugin provides several checks that can be seen using the show variables command:
SHOW VARIABLES LIKE 'validate_password.%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password.check_user_name | ON | | validate_password.dictionary_file | | | validate_password.length | 8 | | validate_password.mixed_case_count | 1 | | validate_password.number_count | 1 | | validate_password.policy | MEDIUM | | validate_password.special_char_count | 1 | +--------------------------------------+--------+ 7 rows in set (0,01 sec)Tests with validate_password.policy=LOW
First let’s set the validate_password.policy to LOW to check which tests are done by the plugin. It should only check password length.
SET GLOBAL validate_password.policy=LOW; +--------------------------------------+-------+ | Variable_name | Value | +--------------------------------------+-------+ | validate_password.check_user_name | ON | | validate_password.dictionary_file | | | validate_password.length | 8 | | validate_password.mixed_case_count | 1 | | validate_password.number_count | 1 | | validate_password.policy | LOW | | validate_password.special_char_count | 1 | +--------------------------------------+-------+
create user 'steulet'@'localhost' identified by '1234567'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements create user 'steulet'@'localhost' identified by '12345678'; Query OK, 0 rows affected (0,01 sec)
Tests with validate_password.policy=MEDIUM
MEDIUM policy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase character, 1 uppercase character, and 1 special (nonalphanumeric) character
SET GLOBAL validate_password.policy=MEDIUM; Query OK, 0 rows affected (0,00 sec) SHOW VARIABLES LIKE 'validate_password.%'; +--------------------------------------+-------------------------------------+ | Variable_name | Value | +--------------------------------------+-------------------------------------+ | validate_password.check_user_name | ON | | validate_password.dictionary_file | | | validate_password.length | 8 | | validate_password.mixed_case_count | 1 | | validate_password.number_count | 1 | | validate_password.policy | MEDIUM | | validate_password.special_char_count | 1 | +--------------------------------------+-------------------------------------+ 7 rows in set (0.00 sec)
create user 'hueber'@'localhost' identified by '12345678'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements create user 'hueber'@'localhost' identified by '1234567L'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements create user 'hueber'@'localhost' identified by '123456zL'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements create user 'hueber'@'localhost' identified by '12345!zL'; Query OK, 0 rows affected (0.01 sec)
Tests with validate_password.policy=STRONG
In order to check the validate_password.policy=STRONG I uploaded a password file used for brute force attack. You can download this file from: https://github.com/danielmiessler/SecLists/blob/master/Passwords/Most-Popular-Letter-Passes.txt
SET GLOBAL validate_password.dictionary_file='/u01/mysqldata/mysqld2/PasswordList'; Query OK, 0 rows affected (0,00 sec)
SET GLOBAL validate_password.policy=strong; Query OK, 0 rows affected (0,00 sec)
SHOW VARIABLES LIKE 'validate_password.%'; +--------------------------------------+-------------------------------------+ | Variable_name | Value | +--------------------------------------+-------------------------------------+ | validate_password.check_user_name | ON | | validate_password.dictionary_file | /u01/mysqldata/mysqld2/PasswordList | | validate_password.length | 8 | | validate_password.mixed_case_count | 1 | | validate_password.number_count | 1 | | validate_password.policy | STRONG | | validate_password.special_char_count | 1 | +--------------------------------------+-------------------------------------+
7 rows in set (0.00 sec)
create user 'neuhaus'@'localhost' identified by 'Manager1;'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements create user 'neuhaus'@'localhost' identified by 'Password1;'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
If I decrease the validate_password.policy to medium, the plugin doesn’t check the dictionary file anymore:
SET GLOBAL validate_password.policy=medium; Query OK, 0 rows affected (0,00 sec) create user 'neuhaus'@'localhost' identified by 'Password1;'; Query OK, 0 rows affected (0,00 sec)Function VALIDATE_PASSWORD_STRENGTH()
As explained above the validate_password_strength test a password and returns an integer from 0 (weak) to 100 (strong) representing the password strength.
select VALIDATE_PASSWORD_STRENGTH('abcd');
+------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('abcd') |
+------------------------------------+
| 25 |
+------------------------------------+
1 row in set (0.00 sec)
select VALIDATE_PASSWORD_STRENGTH('password');
+----------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('password') |
+----------------------------------------+
| 50 |
+----------------------------------------+
1 row in set (0.00 sec)
select VALIDATE_PASSWORD_STRENGTH('Manager1!');
+-----------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('Manager1!') |
+-----------------------------------------+
| 75 |
+-----------------------------------------+
1 row in set (0.00 sec)
select VALIDATE_PASSWORD_STRENGTH('aZbq!1)m8N');
+------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('aZbq!1)m8N') |
+------------------------------------------+
| 100 |
+------------------------------------------+
1 row in set (0.00 sec)
Cet article Password Validation in MySQL est apparu en premier sur Blog dbi services.
PgBackRest : Dedicated Backup Host
In previous blogs (blog1 and blog2) we saw a configuration of PgBackRest where the tool is installed in the same server that also hosts the PostgreSQL cluster. This configuration is fine if we have a single database server. But in the case that we have many database servers, it is more suitable to have a dedicated server for backups. This will also separate the backups and WAL archive from databases server
In this article we will see how to configure PgBackRest in an environment with 2 databases servers.
We present below the configuration we will use. We suppose that PgBackRest is already installed on both servers.
pgservertools: dedicated backup hosts
pgserver1: database server
pgserver2: database server
As we can see we have two clusters running on each database server (pgserver1 and pgserver2). Note that we can have as many clusters we want on each server.
pgserver1 : cluster1
postgres=# show data_directory ; data_directory ------------------- /u90/pgdata/pg101 (1 row) postgres=# show port; port ------ 5432 (1 row) postgres=#
pgserver1 : cluster2
postgres=# show data_directory ; data_directory -------------------- /u90/pgdata2/pg101 (1 row) postgres=# show port; port ------ 5433 (1 row) postgres=#
pgserver2 : cluster1
postgres=# show data_directory ; data_directory ------------------- /u90/pgdata/pg101 (1 row) postgres=# show port; port ------ 5432 (1 row) postgres=#
pgserver2 : cluster2
postgres=# show data_directory ; data_directory -------------------- /u90/pgdata2/pg101 (1 row) postgres=# show port; port ------ 5433 (1 row) postgres=#
Now let’s create on pgservertools a dedicated user who will own the repository. It is not recommended to use user postgres. We will use backupowner as user.
[root@pgservertools ~]# useradd -m backupowner
As PgBackRest requires communication between the hosts without password, we have to configure ssh keys between user backupowner (on pgservertools) and users postgres (on pgserver1 and pgserver2).
On pgservertools, let’s generate keys for user backupowner
[backupowner@pgservertools ~]$ ssh-keygen -t rsa Generating public/private rsa key pair. Enter file in which to save the key (/home/backupowner/.ssh/id_rsa): y Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in y. Your public key has been saved in y.pub. The key fingerprint is: SHA256:drIaCe1aMZSPOmQkNdYfn5WLm/iE4sJS2YKGj6EF38M backupowner@pgservertools.localdomain The key's randomart image is: +---[RSA 2048]----+ | +. . | | o .... o | | . . o. o + . | |. o o o. + . | | + ++o= So.o | |o =oE+o=o++ | |.* oo+=..o | |o o o+.o . | | .... | +----[SHA256]-----+ [backupowner@pgservertools ~]$
Do the same on pgserver1 for user postgres
[postgres@pgserver1 ~]$ ssh-keygen -t rsa Generating public/private rsa key pair. Enter file in which to save the key (/home/postgres/.ssh/id_rsa): Created directory '/home/postgres/.ssh'. Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /home/postgres/.ssh/id_rsa. Your public key has been saved in /home/postgres/.ssh/id_rsa.pub. The key fingerprint is: 15:cf:78:47:ef:e5:ab:29:b7:25:59:03:de:de:88:be postgres@pgserver1.localdomain The key's randomart image is: +--[ RSA 2048]----+ | . . | | = . . | | o +.. o| | . ...oo.| | S . +o| | .+.+| | .o.+.| | o .= | | E=. | +-----------------+ [postgres@pgserver1 ~]$
And on pgserver2 for user postgres
[postgres@pgserver2 ~]$ ssh-keygen -t rsa Generating public/private rsa key pair. Enter file in which to save the key (/home/postgres/.ssh/id_rsa): Created directory '/home/postgres/.ssh'. Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /home/postgres/.ssh/id_rsa. Your public key has been saved in /home/postgres/.ssh/id_rsa.pub. The key fingerprint is: SHA256:ZKov+TdohBLvaO/pSYIl+Tk6iWfUMx1Lqixb8ByaZzI postgres@pgserver2.localdomain The key's randomart image is: +---[RSA 2048]----+ | | | | | o | | .. o+ | |+ o+ =.oS | | @ooB.+ | |E+@=o= . | |=BB+++o o | |oB +*+o. . | +----[SHA256]-----+ [postgres@pgserver2 ~]$
Now let’s exchange keys between servers.
[backupowner@pgservertools .ssh]$ ssh-copy-id postgres@pgserver1 [backupowner@pgservertools .ssh]$ ssh-copy-id postgres@pgserver2 [postgres@pgserver1 .ssh]$ ssh-copy-id backupowner@pgservertools [postgres@pgserver2 .ssh]$ ssh-copy-id backupowner@pgservertools
And then let’s test connection
[backupowner@pgservertools ~]$ ssh postgres@pgserver1 date Tue Feb 20 11:42:06 CET 2018 [backupowner@pgservertools ~]$ ssh postgres@pgserver2 date Tue Feb 20 11:42:10 CET 2018 [postgres@pgserver1 .ssh]$ ssh backupowner@pgservertools date Tue Feb 20 11:42:54 CET 2018 [postgres@pgserver2 .ssh]$ ssh backupowner@pgservertools date Tue Feb 20 11:43:23 CET 2018
Ok now that everything is fine for trusted connections, let’s configure the pgbackrest.conf files. We present below contents of our files on the 3 servers. We can notice that encryption is used (see previous blogs)
pgservertools
[root@pgservertools ~]# cat /etc/pgbackrest.conf [pgserver1pgdata] db1-host=pgserver1 db1-path=/u90/pgdata/pg101 db1-user=postgres [pgserver1pgdata2] db1-host=pgserver1 db1-path=/u90/pgdata2/pg101 db1-user=postgres db1-port=5433 [pgserver2pgdata] db1-host=pgserver2 db1-path=/u90/pgdata/pg101 db1-user=postgres [pgserver2pgdata2] db1-host=pgserver2 db1-path=/u90/pgdata2/pg101 db1-user=postgres db1-port=5433 [global] repo-path=/u90/backup retention-full=2 repo-cipher-pass=dkN28km/CltmsbzkDdKahmwXctr0GJd/9F8tegBXBWASULhVatNXauMMKWUslax1 repo-cipher-type=aes-256-cbc [root@pgservertools etc]#
pgserver1
[postgres@pgserver1 ~]$ cat /etc/pgbackrest.conf [pgserver1pgdata] db1-path=/u90/pgdata/pg101 db1-socket-path=/tmp [pgserver1pgdata2] db1-path=/u90/pgdata2/pg101 db1-port=5433 db1-socket-path=/tmp [global] backup-host=pgservertools backup-user=backupowner log-level-file=detail [postgres@pgserver1 ~]$
pgserver2
[root@pgserver2 postgres]# cat /etc/pgbackrest.conf [pgserver1pgdata] db1-path=/u90/pgdata/pg101 db1-socket-path=/tmp [pgserver1pgdata2] db1-path=/u90/pgdata2/pg101 db1-port=5433 db1-socket-path=/tmp [global] backup-host=pgservertools backup-user=backupowner log-level-file=detail [root@pgserver2 postgres]#
The next step is to create the stanzas
pgserver1: 2 stanzas pgserver1pgdata and pgserver1pgdata2
[backupowner@pgservertools ~]$ pgbackrest --stanza=pgserver1pgdata --log-level-console=detail --db-port=5432 --db-host=pgserver1 stanza-create 2018-02-21 15:21:42.815 P00 INFO: stanza-create command begin 1.28: --db1-host=pgserver1 --db1-path=/u90/pgdata/pg101 --db1-port=5432 --db1-user=postgres --log-level-console=detail --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/u90/backup --stanza=pgserver1pgdata 2018-02-21 15:21:46.881 P00 INFO: stanza-create command end: completed successfully [backupowner@pgservertools ~]$ pgbackrest --stanza=pgserver1pgdata2 --log-level-console=detail --db-port=5433 --db-host=pgserver1 stanza-create 2018-02-21 15:23:39.116 P00 INFO: stanza-create command begin 1.28: --db1-host=pgserver1 --db1-path=/u90/pgdata2/pg101 --db1-port=5433 --db1-user=postgres --log-level-console=detail --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/u90/backup --stanza=pgserver1pgdata2 2018-02-21 15:23:41.360 P00 INFO: stanza-create command end: completed successfully [backupowner@pgservertools ~]$
pgserver2: 2 stanzas pgserver2pgdata and pgserver2pgdata2
[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver2pgdata --log-level-console=detail --db-port=5432 --db-host=pgserver2 stanza-create 2018-02-27 13:22:47.710 P00 INFO: stanza-create command begin 1.28: --db1-host=pgserver2 --db1-path=/u90/pgdata/pg101 --db1-port=5432 --db1-user=postgres --log-level-console=detail --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/u90/backup --stanza=pgserver2pgdata 2018-02-27 13:22:49.624 P00 INFO: stanza-create command end: completed successfully [backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver2pgdata2 --log-level-console=detail --db-port=5433 --db-host=pgserver2 stanza-create 2018-02-27 13:23:01.323 P00 INFO: stanza-create command begin 1.28: --db1-host=pgserver2 --db1-path=/u90/pgdata2/pg101 --db1-port=5433 --db1-user=postgres --log-level-console=detail --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/u90/backup --stanza=pgserver2pgdata2 2018-02-27 13:23:03.233 P00 INFO: stanza-create command end: completed successfully [backupowner@pgservertools pgserver1pgdata]$
And now we can do a backup of any our cluster using the corresponding stanza
pgserver1pgdata
[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver1pgdata --log-level-console=detail --db-port=5432 --db-host=pgserver1 backup
pgserver1pgdata2
[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver1pgdata2 --log-level-console=detail --db-port=5433 --db-host=pgserver1 backup
pgserver2pgdata
[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver2pgdata --log-level-console=detail --db-port=5432 --db-host=pgserver2 backup
pgserver2pgdata2
[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver2pgdata2 --log-level-console=detail --db-port=5433 --db-host=pgserver2 backup
An example of getting info about backup
[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver1pgdata --log-level-console=detail info
stanza: pgserver1pgdata
status: ok
db (current)
wal archive min/max (10-1): 000000010000000000000018 / 000000010000000000000018
full backup: 20180221-155755F
timestamp start/stop: 2018-02-27 11:45:51 / 2018-02-27 11:46:18
wal start/stop: 000000010000000000000018 / 000000010000000000000018
database size: 30MB, backup size: 30MB
repository size: 3.5MB, repository backup size: 3.5MB
[backupowner@pgservertools pgserver1pgdata]$
Conclusion
In this blog we have seen how PgbackRest can be be used in an environment with multiple database servers.
Cet article PgBackRest : Dedicated Backup Host est apparu en premier sur Blog dbi services.
After IoT, IoP makes its way to the database
At each new Oracle version, I like to check what’s new, not only from the documentation, but also from exposed internals. I look (and sometimes diff) on catalog views definitions, undocumented parameters, and even the new C functions in the libraries. At last Oak Table World, I was intrigued by this V$SQLFN_METADATA view explained by Vit Spinka when digging into the internals of how execution plans are stored. This view has entries with all SQL functions, and a VERSION column going from ‘V6 Oracle’ to ‘V11R1 Oracle’. The lastest functions has an ‘INVALID’ entry and we also can see some functions with ‘SQL/DS’. Well, now that we have Oracle 18c on the Oracle Cloud, I came back to this view to see if anything is new, listing the highest FUNC_ID at the top and the first row attired my attention:
SQL> select * from V$SQLFN_METADATA order by 1 desc fetch first 10 rows only;
FUNC_ID NAME MINARGS MAXARGS DATATYPE VERSION ANALYTIC AGGREGATE OFFLOADABLE DISP_TYPE USAGE DESCR CON_ID
------- ---- ------- ------- -------- ---------- -------- --------- ----------- --------- ----- ----- ------
1148 TO_DOG_YEAR 1 4 NUMERIC V13 Oracle NO NO YES NORMAL TO_DOG_YEAR 0
1147 JSON_MERGEPATCH 4 0 UNKNOWN INVALID NO NO NO NORMAL JSON_MERGEPATCH 0
1146 JSON_PATCH 4 0 UNKNOWN INVALID NO NO NO NORMAL JSON_PATCH 0
1145 ROUND_TIES_TO_EVEN 1 2 NUMERIC INVALID NO NO YES NORMAL ROUND_TIES_TO_EVEN 0
1144 CON_ID_TO_CON_NAME 1 0 UNKNOWN INVALID NO NO NO NORMAL CON_ID_TO_CON_NAME 0
1143 TIMESTAMP_TO_NUMBER 1 1 UNKNOWN INVALID NO NO YES NORMAL TIMESTAMP_TO_NUMBER 0
1142 TO_UTC_TIMESTAMP_TZ 1 0 UNKNOWN INVALID NO NO YES NORMAL TO_UTC_TIMESTAMP_TZ 0
1141 OPTSYSAPPROXRANK 1 0 UNKNOWN INVALID NO NO NO NORMAL Internal evaluation function for multiple approx_rank's 0
1140 APPROX_RANK 1 1 NUMERIC INVALID NO YES NO NORMAL APPROX_RANK 0
1139 APPROX_SUM 1 2 NUMERIC INVALID NO YES NO NORMAL APPROX_SUM 0
Because those functions are SQL functions, I searched this ‘TO_DOG_YEAR’ on Google to see whether a new ANSI SQL function was implemented. But finally came upon something I didn’t expect: Dog Years Calculator. The trends in databases are really going crazy these times. All focus is on developers. XML, JSON, Docker… and now a function to calculate your age in dog years.
But afterall, it makes sense. IoT (not ‘Index Organized Table’ but ‘Internet Of Things’) is coming with sensors everywhere. And it is not only ‘things’ but it comes to living beings. I have read recently about ‘Internet of Pets’ where collars equipped with sensors detect where your domestic animal go and when he is hungry.
Let’s test it. Tomorrow, my elder kid has his 13th birthday. Now Oracle can tell me that he will be 65 in dog years:
SQL> select to_dog_year(date'2005-04-02') from dual;
TO_DOG_YEAR(DATE'2005-04-02')
-----------------------------
65
Yes, here I learn that the calculation is a bit more complex than just multiplying by 7. Of course, adding a SQL standard function would not make sense if it was just a multiplication.
But it seems to be even more complex. I searched for the C functions behind this one:
[oracle@CLOUD18C ~]$ nm /u01/app/oracle/product/18.0.0/dbhome_1/bin/oracle | grep -iE "dog.*year"
000000001452e073 r KNCLG_TODOGYEAR
0000000003ffcf40 T LdiJDaysDogYear
000000000f3170c0 T LdiJulianDogYear
000000000f316fc0 T LdiJulianDogYeararr
000000000f3170f0 t LdiJulianDogYeari
000000000f606e10 T OCIPConvertDateDogYearTime
000000000ebf2380 t qerxjConvertDogYearTime
0000000010de19e0 t qjsngConvStructDogYear
0000000010de0320 T qjsngNumberDogYearDty
0000000010de06f0 T sageStringDogYearDty
0000000010de7110 T sageplsDogYear
000000000bc5cd80 t sagerwAddDogYearTime
0000000010bad3c0 T qmxtgrConvSaxDogYear
0000000010bad400 T qmxtgrConvSaxDogYear_internal
00000000025ae090 T qosDateTimeDogYear
0000000004f22b60 T xsCHDogYeartime
000000000438c230 T nlsBreedDogYear
000000000438bb50 t nlsBreedDogYearCmn
000000000438c060 T nlsBreedDogYearTime
000000000438bc50 T nlsBreedDogYear
00000000044d1da0 T xvopAddDTDurDogYear
00000000044d1ac0 T xvopAddYMDurDogYear
Those ‘nlsBreed’ functions ring a bell and I checked if there are new values in V$NLS_VALID_VALUES
SQL> select distinct parameter from V$NLS_VALID_VALUES;
PARAMETER
----------------------------------------------------------------
TERRITORY
CHARACTERSET
BREED
LANGUAGE
SORT
That ‘BREED’ is a new one, with a lot of interesting values:
And here is my example using this new NLS parameter.
SQL> select to_dog_year(date'2005-04-02','','NLS_BREED=Saint Bernard') from dual;
TO_DOG_YEAR(DATE'2005-04-02',
----------------------------
96
Note that I’ve no idea about the second parameter, I had to put a ‘null’ for it to be able to mention the NLS one, or I got a ‘ORA-00909: invalid number of arguments’.
I have to say that, for a DBA focused on the core database functions, it is hard to understand that new features go on things like this TO_DOG_YEAR function. But being realistic, it is clear that the budget for new features go into the new direction: all for developers, big data, IoT… Of course we can write those functions in PL/SQL or maybe one day with JavaScript thanks to the Multi-Lingual Engine currently in beta. But IoT is also about performance, and a standard function avoids context switches.
Cet article After IoT, IoP makes its way to the database est apparu en premier sur Blog dbi services.
sqlplus and its column output
During tuning or normal DBA activities one of the most annoying things is sqlplus with its default column output when running queries. I.e. even after setting a linesize of 1000 and a pagesize of 1000 the output may look as follows in your preferred terminal emulation (putty here):
Most people address this by using other tools like sqldeveloper (or sqlcl with sqlformat ansiconsole). However, a lot of people still use sqlplus.
How can you address the output-format-issue with sqlplus?
Actually you can format columns of course and specify only the columns you would like to see (instead of “select *”):
SQL> select schemaname, osuser from v$session where sid=(select sid from v$mystat where rownum=1);
SCHEMANAME OSUSER
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
CBLEILE oracle
SQL> column schemaname format a32
SQL> column osuser format a32
SQL> select schemaname, osuser from v$session where sid=(select sid from v$mystat where rownum=1);
SCHEMANAME OSUSER
-------------------------------- --------------------------------
CBLEILE oracle
But that’s annoying if you have to do it for a couple of columns for every sql-statement you interactively type in.
The better alternative is to use a terminal emulation, which allows horizontal scrolling. Tanel Poder describes it in his “AOT Preparation Session” on Youtube: https://www.youtube.com/watch?v=tC81PMO7ODw.
Interestingly horizontal scrolling is available on Windows for ages. I.e. using cmd.exe or the Powershell you can define a “Screen Buffer Size” for the “Width” of the Window (in Properties -> Layout). Best is to set it under “Defaults” so that it’s available every time you open a cmd.exe or Powershell window.
But what to use on e.g. Linux? Most terminal emulation software does not allow horizontal scrolling. Actually there are only very few terminal emulation products available, which allow it. One of the freely available products is terminator ( https://github.com/software-jessies-org/jessies/wiki/Terminator ). After downloading the rpm on my Oracle Enterprise Linux server I installed it as follows:
[root@localhost terminator]# rpm -i org.jessies.terminator.x86_64.rpm
error: Failed dependencies:
/usr/bin/ruby is needed by org.jessies.terminator-27.171.7083-2.x86_64
[root@localhost terminator]# yum install ruby
[root@localhost terminator]# rpm -i org.jessies.terminator.x86_64.rpm
[root@localhost terminator]# terminator
The prereqs for terminator are an installed Java Runtime Environment (JRE -> you may use the one provided within your ORACLE_HOME) and Ruby (as you can see above).
I started terminator once as root to install the necessary global terminfo. After that I can use it as e.g. the oracle-user:
[oracle@localhost ~]$ terminator &
There are no wrapped lines due to the terminal size anymore:
And you can scroll to the right:
Running sqlcl with sqlformat set to ansiconsole the Terminator terminal emulation also helps for queries with many columns, which do not fit on the screen:
Scrolled to the right:
Besides the formatting issue, it’s of course recommended to use the utility rlwrap (readline wrapper) with sqlplus on Linux for command line history (get previous command lines with the up and down key) and easy command line editing. Actually rlwrap is much better than the history available in sqlplus of 12.2. I do recommend watching the youtube video from Tanel Poder mentioned above, because he also shows that rlwrap can be configured to provide command completion (for Oracle PLSQL-objects and reserved words) as well.
When installing the dbi services DMK Management Kit ( http://www.dbi-services.com/dmk ) on Linux then rlwrap is of course included.
So sqlcl is cool, but sqlplus also still has its right to exist 
Cet article sqlplus and its column output est apparu en premier sur Blog dbi services.
Docker: efficiently building images for large software
I see increasing demand to build a Docker image for the Oracle Database. But the installation process for Oracle does not really fit the Docker way to install by layers: you need to unzip the distribution, install from it to the Oracle Home, remove the things that are not needed, strop the binaries,… Before addressing those specific issues, here are the little tests I’ve done to show how the build layers increase the size of the image.
I’m starting with an empty docker repository on XFS filesystem:
[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 33M 80G 1% /var/lib/docker
For the example, I create a 100MB file in the context:
[root@VM121 docker]# mkdir -p /var/tmp/demo
[root@VM121 docker]# dd if=/dev/urandom of=/var/tmp/demo/file0.100M count=100 bs=1M
Here his my docker file:
FROM alpine:latest as staging
WORKDIR /var/tmp
ADD file0.100M .
RUN cp file0.100M file1.100M
RUN rm file0.100M
RUN mv file1.100M file2.100M
RUN dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M
The 1st step starts with an alpine image
The 2nd step sets the working directory
The 3rd step adds a 100M file from the context
The 4th step copies the file, so that we have 200M in two files
The 5th step removes the previous file, so that we have 100M in one file
The 6th step renames the file, staying with only one 100M file
The 7th step appends 100M to the file, leaving 200M in one file
Here is the build with default option:
[root@VM121 docker]# docker image build -t franck/demo /var/tmp/demo
The context, my 100M files is send first:
Sending build context to Docker daemon 104.9MB
And here are my 7 steps:
Step 1/7 : FROM alpine:latest as staging
latest: Pulling from library/alpine
ff3a5c916c92: Pull complete
Digest: sha256:7df6db5aa61ae9480f52f0b3a06a140ab98d427f86d8d5de0bedab9b8df6b1c0
Status: Downloaded newer image for alpine:latest
---> 3fd9065eaf02
Step 2/7 : WORKDIR /var/tmp
Removing intermediate container 93d1b5f21bb9
---> 131b3e6f34e7
Step 3/7 : ADD file0.100M .
---> 22ca0b2f6424
Step 4/7 : RUN cp file0.100M file1.100M
---> Running in b4b1b9c7e29b
Removing intermediate container b4b1b9c7e29b
---> 8c7290a5c87e
Step 5/7 : RUN rm file0.100M
---> Running in 606e2c73d456
Removing intermediate container 606e2c73d456
---> 5287e66b019c
Step 6/7 : RUN mv file1.100M file2.100M
---> Running in 10a9b379150e
Removing intermediate container 10a9b379150e
---> f508f426f70e
Step 7/7 : RUN dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M
---> Running in 9dcf6d80642c
100+0 records in
100+0 records out
Removing intermediate container 9dcf6d80642c
---> f98304641c54
Successfully built f98304641c54
Successfully tagged franck/demo:latest
So, what’s the size of my docker repository after my image with this 200M file?
[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 538M 80G 1% /var/lib/docker
I have more than 500MB here.
Actually, besides the alpine image downloaded, which is only 4MB, the image I have build is 538MB:
[root@VM121 docker]# docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest f98304641c54 Less than a second ago 528MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
We can better understand this size by looking at intermediate images:
[root@VM121 docker]# docker image ls -a
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest f98304641c54 1 second ago 528MB
<none> <none> f508f426f70e 27 seconds ago 319MB
<none> <none> 5287e66b019c 36 seconds ago 214MB
<none> <none> 8c7290a5c87e 37 seconds ago 214MB
<none> <none> 22ca0b2f6424 42 seconds ago 109MB
<none> <none> 131b3e6f34e7 47 seconds ago 4.15MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
The first one, ’22ca0b2f6424′ is from the step 3 which added the 100MB file
The second one ‘8c7290a5c87e’ is from the 4th step which copied the file, bringing the image to 200MB
The third one ‘5287e66b019c’ is from the 5th step which removed the file. I didn’t increase the size but didn’t remove anything either.
The fourth one ‘f508f426f70e’ is from the 6th step which renamed the file. But this, for docker, is like copying to a new layer and that adds 100MB
Finally, the 7th step appended only 100MB, but this finally resulted to copy the full 200MB file to the new layer
We can see all those operations, and size added at each step, from the image history:
[root@VM121 docker]# docker image history franck/demo
IMAGE CREATED CREATED BY SIZE COMMENT
f98304641c54 1 second ago /bin/sh -c dd if=/dev/urandom of=file2.100M … 210MB
f508f426f70e 27 seconds ago /bin/sh -c mv file1.100M file2.100M 105MB
5287e66b019c 36 seconds ago /bin/sh -c rm file0.100M 0B
8c7290a5c87e 37 seconds ago /bin/sh -c cp file0.100M file1.100M 105MB
22ca0b2f6424 42 seconds ago /bin/sh -c #(nop) ADD file:339435a18aeeb1b69… 105MB
131b3e6f34e7 47 seconds ago /bin/sh -c #(nop) WORKDIR /var/tmp 0B
3fd9065eaf02 2 months ago /bin/sh -c #(nop) CMD ["/bin/sh"] 0B
<missing> 2 months ago /bin/sh -c #(nop) ADD file:093f0723fa46f6cdb… 4.15MB
One workaround is to run everything in the same layer. Personally, I don’t like it because I don’t get the point of using a Dockerfile for just running one script.
So, here is the Dockerfile with only one RUN command:
FROM alpine:latest as staging
WORKDIR /var/tmp
ADD file0.100M .
RUN cp file0.100M file1.100M \
&& rm file0.100M \
&& mv file1.100M file2.100M \
&& dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M
The build is similar except that there are fewer steps:
[root@VM121 docker]# docker image build -t franck/demo /var/tmp/demo
Sending build context to Docker daemon 104.9MB
Step 1/4 : FROM alpine:latest as staging
latest: Pulling from library/alpine
ff3a5c916c92: Pull complete
Digest: sha256:7df6db5aa61ae9480f52f0b3a06a140ab98d427f86d8d5de0bedab9b8df6b1c0
Status: Downloaded newer image for alpine:latest
---> 3fd9065eaf02
Step 2/4 : WORKDIR /var/tmp
Removing intermediate container 707644c15547
---> d4528b28c85e
Step 3/4 : ADD file0.100M .
---> e26215766e75
Step 4/4 : RUN cp file0.100M file1.100M && rm file0.100M && mv file1.100M file2.100M && dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M
---> Running in 49c2774851f4
100+0 records in
100+0 records out
Removing intermediate container 49c2774851f4
---> df614ac1b6b3
Successfully built df614ac1b6b3
Successfully tagged franck/demo:latest
This leaves us with a smaller space usage::
[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 340M 80G 1% /var/lib/docker
The image is smaller, but still larger than the final state (a 300MB image for only one 200MB file):
[root@VM121 docker]# docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest df614ac1b6b3 Less than a second ago 319MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
This is because we have grouped the RUN steps, but the ADD has its own layer, adding a file that is removed later:
[root@VM121 docker]# docker image ls -a
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest df614ac1b6b3 Less than a second ago 319MB
<none> <none> e26215766e75 20 seconds ago 109MB
<none> <none> d4528b28c85e 22 seconds ago 4.15MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
[root@VM121 docker]# docker image history franck/demo
IMAGE CREATED CREATED BY SIZE COMMENT
df614ac1b6b3 Less than a second ago /bin/sh -c cp file0.100M file1.100M … 210MB
e26215766e75 20 seconds ago /bin/sh -c #(nop) ADD file:fe0262a4b800bf66d… 105MB
d4528b28c85e 22 seconds ago /bin/sh -c #(nop) WORKDIR /var/tmp 0B
3fd9065eaf02 2 months ago /bin/sh -c #(nop) CMD ["/bin/sh"] 0B
<missing> 2 months ago /bin/sh -c #(nop) ADD file:093f0723fa46f6cdb… 4.15MB
This is the kind of issue we have when building an Oracle Database image. We need to ADD the zip file for the database distribution, and the latest bundle patch. It is removed later but still takes space on the image. Note that one workaround to avoid the ADD layer can be to get the files from an NFS or HTTP server with wget or curl in a RUN layer rather than an ADD one. There’s an example on Stefan Oehrli blog post.
–squashWith the latest versions of docker, there’s an easy way to flatten all those intermediary images at the end.
Here I’ve 18.03 and enabled experimental features:
[root@VM121 docker]# docker info
Containers: 0
Running: 0
Paused: 0
Stopped: 0
Images: 8
Server Version: 18.03.0-ce
Storage Driver: overlay2
Backing Filesystem: xfs
...
[root@VM121 docker]# cat /etc/docker/daemon.json
{
"experimental": true
}
I start with the same as before but just add –squash to the build command
[root@VM121 docker]# docker image build --squash -t franck/demo /var/tmp/demo
The output is similar but the image is an additional one, reduced down to the size of my final state (with one 200MB file):
[root@VM121 docker]# docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest 2ab439a723c4 Less than a second ago 214MB
<none> <none> c3058e598b0a 3 seconds ago 528MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
The intermediate image list shows that all was done as without ‘–squash’ but with an additional set which reduced the size:
[root@VM121 docker]# docker image ls -a
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest 2ab439a723c4 Less than a second ago 214MB
<none> <none> c3058e598b0a 3 seconds ago 528MB
<none> <none> 1f14d93a592e 23 seconds ago 319MB
<none> <none> 7563d40b650b 27 seconds ago 214MB
<none> <none> 8ed15a5059bd 28 seconds ago 214MB
<none> <none> 24b11b9026ce 31 seconds ago 109MB
<none> <none> 382bb71a6a4a 33 seconds ago 4.15MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
This step is visible in the image history as a ‘merge’ step:
[root@VM121 docker]# docker image history franck/demo
IMAGE CREATED CREATED BY SIZE COMMENT
2ab439a723c4 Less than a second ago 210MB merge sha256:c3058e598b0a30c606c1bfae7114957bbc62fca85d6a70c2aff4473726431394 to sha256:3fd9065eaf02feaf94d68376da52541925650b81698c53c6824d92ff63f98353
<missing> 3 seconds ago /bin/sh -c dd if=/dev/urandom of=file2.100M … 0B
<missing> 23 seconds ago /bin/sh -c mv file1.100M file2.100M 0B
<missing> 27 seconds ago /bin/sh -c rm file0.100M 0B
<missing> 28 seconds ago /bin/sh -c cp file0.100M file1.100M 0B
<missing> 31 seconds ago /bin/sh -c #(nop) ADD file:14cef588b48ffbbf1… 0B
<missing> 33 seconds ago /bin/sh -c #(nop) WORKDIR /var/tmp 0B
<missing> 2 months ago /bin/sh -c #(nop) CMD ["/bin/sh"] 0B
<missing> 2 months ago /bin/sh -c #(nop) ADD file:093f0723fa46f6cdb… 4.15MB
However, even if I have a smaller final image, my filesystem usage is even larger with this additional 210MB:
[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 739M 80G 1% /var/lib/docker
Let’s prune it to get rid of those intermediate images:
[root@VM121 docker]# docker image prune -f
Deleted Images:
deleted: sha256:c3058e598b0a30c606c1bfae7114957bbc62fca85d6a70c2aff4473726431394
deleted: sha256:37ed4826d70def1978f9dc0ddf42618d951f65a79ce30767ac3a5037d514f8af
deleted: sha256:1f14d93a592eb49a210ed73bf65e6886fcec332786d54b55d6b0e16fb8a8beda
deleted: sha256:c65cf4c70aed04e9b57e7a2a4fa454d3c63f43c32af251d8c86f6f85f44b1757
deleted: sha256:7563d40b650b2126866e8072b8df92d5d7516d86b25a2f6f99aa101bb47835ba
deleted: sha256:31ee5456431e903cfd384b1cd7ccb7918d203dc73a131d4ff0b9e6517f0d51cd
deleted: sha256:8ed15a5059bd4c0c4ecb78ad77ed75da143b06923d8a9a9a67268c62257b6534
deleted: sha256:6be91d85dec6e1bda6f1c0d565e98dbf928b4ea139bf9cb666455e77a2d8f0d9
deleted: sha256:24b11b9026ce738a78ce3f7b8b5d86ba3fdeb15523a30a7c22fa1e3712ae679a
deleted: sha256:c0984945970276621780a7888adfde9c6e6ca475c42af6b7c54f664ad86f9c9f
deleted: sha256:382bb71a6a4a7ddec86faa76bb86ea0c1a764e5326ad5ef68ce1a6110ae45754
Total reclaimed space: 524.3MB
Now having only the squashed image:
[root@VM121 docker]# docker image ls -a
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest 2ab439a723c4 32 minutes ago 214MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 237M 80G 1% /var/lib/docker
Finally, you can do something similar to an intermediate squash using multi-stage build.
Here is my Dockerfile:
FROM alpine:latest as staging
WORKDIR /var/tmp
ADD file0.100M .
RUN cp file0.100M file1.100M
RUN rm file0.100M
RUN mv file1.100M file2.100M
RUN dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M
FROM alpine:latest
WORKDIR /var/tmp
COPY --from=staging /var/tmp .
With multi-stage build, we can start the second stage from a different image, and add more steps, but here I just start with the same alpine image and copy the final layer of the previous build.
We see something very similar to the –squash one:
[root@VM121 docker]# docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest 55f329385f8c Less than a second ago 214MB
<none> <none> fd26a00db784 8 seconds ago 528MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
[root@VM121 docker]# docker image ls -a
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest 55f329385f8c 1 second ago 214MB
<none> <none> fd26a00db784 9 seconds ago 528MB
<none> <none> 9bf5be367b63 32 seconds ago 319MB
<none> <none> 531d78833ba8 35 seconds ago 214MB
<none> <none> 05dd68114743 36 seconds ago 214MB
<none> <none> b9e5215a9fc8 39 seconds ago 109MB
<none> <none> ab332f486793 41 seconds ago 4.15MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
The history of the last stage shows the copy of 210MB from the previous one:
[root@VM121 docker]# docker image history franck/demo
IMAGE CREATED CREATED BY SIZE COMMENT
55f329385f8c 1 second ago /bin/sh -c #(nop) COPY dir:2b66b5c36eff5b51f… 210MB
ab332f486793 41 seconds ago /bin/sh -c #(nop) WORKDIR /var/tmp 0B
3fd9065eaf02 2 months ago /bin/sh -c #(nop) CMD ["/bin/sh"] 0B
<missing> 2 months ago /bin/sh -c #(nop) ADD file:093f0723fa46f6cdb… 4.15MB
The usage of filesystem is similar to the –squash one. Even if we reduced the final image, all the intermediate states had to be stored:
[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 737M 80G 1% /var/lib/docker
That looks good, if you accept to use a large intermediate space while building the image, which gives you the possibility to debug without re-running from the beginning, thanks to the layers in cache. However, you have still the inefficiency that each time you try the build, the context will be sent again even when not needed. And that is long with a 3GB .zip in the case of Oracle Database installation. Unfortunately, if you add the file to the .dockerignore once you know you have the ADD steps in cache, the next build attempt will not use the caches anymore. I would love to see a per-stage .dockerignore file for multi-stage builds. Or simply have docker realize that some files in the context will not be needed by the COPY or ADD that are not in cache yet.
Sending the whole context at each build attempt, when debugging your Dockerfile, is not efficient at all and looks like punch-card time compilation where people sent the cards to be compiled during the night. One syntax error on the first line and you go for another day.
One solution is to have all the required files in an NFS or HTTPd server and get them with ADD from the URL as mentioned earlier.
Multi-stage with multi-contextsAnother solution is to put all COPY or ADD from context in one Dockerfile to build the image containing all required files, and then build your image from it (and squash it at the end).
Here is my first Dockerfile, just adding the files from the context:
[root@VM121 docker]# ls /var/tmp/demo
Dockerfile file0.100M nocontext
[root@VM121 docker]# cat /var/tmp/demo/Dockerfile
FROM alpine:latest as staging
WORKDIR /var/tmp
ADD file0.100M .
I build this ‘staging’ image:
[root@VM121 docker]# docker image build -t franck/stage0 /var/tmp/demo
Sending build context to Docker daemon 104.9MB
Step 1/3 : FROM alpine:latest as staging
latest: Pulling from library/alpine
ff3a5c916c92: Pull complete
Digest: sha256:7df6db5aa61ae9480f52f0b3a06a140ab98d427f86d8d5de0bedab9b8df6b1c0
Status: Downloaded newer image for alpine:latest
---> 3fd9065eaf02
Step 2/3 : WORKDIR /var/tmp
Removing intermediate container 0eeed8e0cfd2
---> a5db3b29c8e1
Step 3/3 : ADD file0.100M .
---> 2a34e1e981be
Successfully built 2a34e1e981be
Successfully tagged franck/stage0:latest
This one is the minimal one:
[root@VM121 docker]# docker image ls
+ docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/stage0 latest 2a34e1e981be Less than a second ago 109MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 139M 80G 1% /var/lib/docker
Now, I don’t need to send this context anymore during further development of my Dockerfile.
I’ve added the following steps to a Dockerfile in another directory:
[root@VM121 docker]# ls /var/tmp/demo/nocontext/
Dockerfile
[root@VM121 docker]# cat /var/tmp/demo/nocontext/Dockerfile
FROM franck/stage0 as stage1
WORKDIR /var/tmp
RUN cp file0.100M file1.100M
RUN rm file0.100M
RUN mv file1.100M file2.100M
RUN dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M
FROM alpine:latest
WORKDIR /var/tmp
Here is the build, using multi-stage to get a squashed final image (you can also use –squash)
[root@VM121 docker]# docker image build -t franck/demo /var/tmp/demo/nocontext
Sending build context to Docker daemon 2.048kB
Step 1/9 : FROM franck/stage0 as stage1
---> 2a34e1e981be
Step 2/9 : WORKDIR /var/tmp
Removing intermediate container eabf57a8de05
...
Successfully built 82478bfa260d
Successfully tagged franck/demo:latest
At that point, there’s no advantage on space used as I keep all layers for easy Dockerfile development:
[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 738M 80G 1% /var/lib/docker
[root@VM121 docker]# docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest 82478bfa260d About a minute ago 214MB
<none> <none> 5772ad68d208 About a minute ago 528MB
franck/stage0 latest 2a34e1e981be About a minute ago 109MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
But now, if I want to add an additional step:
[root@VM121 docker]# cat >> /var/tmp/demo/nocontext/Dockerfile <<< 'RUN chmod a+x /var/tmp'
I can re-build quickly, using cached layers, and without the need to send the context again:
[root@VM121 docker]# docker image build -t franck/demo /var/tmp/demo/nocontext
Sending build context to Docker daemon 2.048kB
Step 1/10 : FROM franck/stage0 as stage1
---> 2a34e1e981be
Step 2/10 : WORKDIR /var/tmp
---> Using cache
---> fa562926cc2b
Step 3/10 : RUN cp file0.100M file1.100M
---> Using cache
---> 31ac716f4d61
Step 4/10 : RUN rm file0.100M
---> Using cache
---> d7392cf51ad9
Step 5/10 : RUN mv file1.100M file2.100M
---> Using cache
---> 4854e503885b
Step 6/10 : RUN dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M
---> Using cache
---> 5772ad68d208
Step 7/10 : FROM alpine:latest
---> 3fd9065eaf02
Step 8/10 : WORKDIR /var/tmp
---> Using cache
---> a5db3b29c8e1
Step 9/10 : COPY --from=stage1 /var/tmp .
---> Using cache
---> 82478bfa260d
Step 10/10 : RUN chmod a+x /var/tmp
---> 4a69ee40a938
Successfully built 4a69ee40a938
Successfully tagged franck/demo:latest
Once I’m ok with my final image, I can remove the intermediate ones:
[root@VM121 docker]# docker image prune -f
Deleted Images:
deleted: sha256:5772ad68d20841197d1424f7c64edd21704e4c7b470acb2193de51ae8741385d
deleted: sha256:bab572d749684d126625a74be4f01cc738742f9c112a940391e3533e61dd55b9
deleted: sha256:4854e503885b4057809fe2867a743ae7898e3e06b329229519fdb5c9d8b10ac1
deleted: sha256:de4acb90433c30d6a21cc3b4483adbd403d8051f3c7c31e6bc095a304606355a
deleted: sha256:d7392cf51ad99d5d0b7a1a18d8136905c87bc738a5bc94dec03e92f5385bf9c8
deleted: sha256:f037e7f973f4265099402534cd7ba409f35272701166d59a1be8e5e39508b07c
deleted: sha256:31ac716f4d61f0048a75b8de6f18757970cf0670a0a3d711e4386bf098b32041
deleted: sha256:2dccb363c5beb4daf45586383df6454b198f824d52676f70318444c346c0fe9a
deleted: sha256:fa562926cc2b3cb56400e1068984bb4048f56713a3cf6dcfa3cf6d945023ebc4
Total reclaimed space: 419.4MB
And the staging one:
[root@VM121 docker]# docker image rm franck/stage0
Untagged: franck/stage0:latest
Deleted: sha256:2a34e1e981be9154c31c5ee7eb942cc121267f4416b6fe502ab93f2dceafd98c
Deleted: sha256:b996a1bdc829167f16dcbe58b717284764470661c3116a6352f15012e1dff07c
Finally, I optimized the developement of the Dockerfile and finished with the minimal size.
[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 237M 80G 1% /var/lib/docker
I’m always surprised by the lack of efficiency when building an image with a Dockerfile. Any serious application deployment involves several intermediate files and the way docker build is layered inflates the size and the time required. Efficient layering and snapshotting work at block level. Here, at file level, any byte of data modified in a file, even metadata such as the file name, is a whole file copy. But for common applications, the installation steps are not as simple adding new files. You may have files appended, object files added to libraries, then compiled, the stripped…
In this post, I tested some recent features, such as multi-stage build and the experimental –squash, as well as a simple manual multi-stage build. Of course, you can do everything in the same layers, and even not use Dockerfiles at all, but then why using Docker? There’s also the Packer approach that I’ve not tested yet. However, I like the Docker approach, but only when used correctly. Deploying an application, like Oracle Database, should use the layered build in the following way: additional steps for new options or new updates. This means that the files must be built elsewhere, in a staging container, and added in one step. And to be efficient, the context should be sent only when needed: when a non-cached ADD or COPY requires it.
Cet article Docker: efficiently building images for large software est apparu en premier sur Blog dbi services.
Docker-CE on Oracle Enterprise Linux 7
Here is how I install the latest Docker version on Oracle Linux 7. You find several blog posts about it which all install ‘docker-engine’. But things move fast in this agile world and docker package name has changed. The Community Edition is now ‘docker-ce’ and you want this one to run the latest version.
I’m on OEL 7.4 but should also wotj on RHEL 7:
[root@VM188 yum]# cat /etc/oracle-release
Oracle Linux Server release 7.4
If you enable [ol7_addons] you can install ‘docker-engine:’
# yum-config-manager --enable ol7_addons
# yum info docker-engine
Loaded plugins: ulninfo
Available Packages
Name : docker-engine
Arch : x86_64
Version : 17.06.2.ol
Release : 1.0.1.el7
Size : 21 M
Repo : ol7_addons/x86_64
Summary : The open-source application container engine
URL : https://dockerproject.org
License : ASL 2.0
Description : Docker is an open source project to build, ship and run any application as a
: lightweight container.
But forget it. That’s 17.06 which is 6 months old. You should consider Docker as a puppy. 6 month in puppy’s years is like 3 human years. So many changes happened.
You can remove all those old things:
# yum remove docker docker-common docker-selinux docker-engine
I’ve not found ‘docker-ce’ on OL7 repositories, as only the Enterprise Edition is there. Then I added the CentOS repo (with yum-config-manager that you can get with yum-utils if you don’t have it already):
yum -y install yum-utils
yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo
Once done, I’ve just installed Docker Community Edition with:
yum -y install docker-ce
And, at that time I got the version 17.12 easily:
[root@VM188 ~]# yum info docker-ce
Loaded plugins: ulninfo
Installed Packages
Name : docker-ce
Arch : x86_64
Version : 17.12.0.ce
Release : 1.el7.centos
Size : 123 M
Repo : installed
From repo : docker-ce-stable
Summary : The open-source application container engine
URL : https://www.docker.com
License : ASL 2.0
Description : Docker is an open source project to build, ship and run any application as a
: lightweight container.
...
But now there’s a new version available:
Available Packages
Name : docker-ce
Arch : x86_64
Version : 18.03.0.ce
Release : 1.el7.centos
Size : 35 M
Repo : docker-ce-stable/x86_64
Summary : The open-source application container engine
URL : https://www.docker.com
License : ASL 2.0
Description : Docker is an open source project to build, ship and run any application as a
: lightweight container.
The problem is that if you want to install docker-ce in this latest version, you will now get:
Resolving Dependencies
--> Running transaction check
---> Package docker-ce.x86_64 0:18.03.0.ce-1.el7.centos will be installed
--> Processing Dependency: pigz for package: docker-ce-18.03.0.ce-1.el7.centos.x86_64
--> Finished Dependency Resolution
Error: Package: docker-ce-18.03.0.ce-1.el7.centos.x86_64 (docker-ce-stable)
Requires: pigz
You could try using --skip-broken to work around the problem
** Found 1 pre-existing rpmdb problem(s), 'yum check' output follows:
2:microcode_ctl-2.1-22.5.0.3.el7_4.x86_64 has missing requires of kernel
(Ok Google, this is what you need to index…)
Starting from version 18.02 there’s a new dependency on ‘pigz’ for parallel gzip.
To get this ‘pigz’ package from the OL7 repository you need to enable EPEL in /etc/yum.repos.d/public-yum-ol7.repo
[ol7_developer_EPEL]
name=Oracle Linux $releasever Developement Packages ($basearch)
baseurl=http://yum.oracle.com/repo/OracleLinux/OL7/developer_EPEL/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1
Now, I’m able to install the latest docker-ce:
[root@VM188 yum.repos.d]# yum install docker-ce
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package docker-ce.x86_64 0:17.12.0.ce-1.el7.centos will be updated
---> Package docker-ce.x86_64 0:18.03.0.ce-1.el7.centos will be an update
--> Processing Dependency: pigz for package: docker-ce-18.03.0.ce-1.el7.centos.x86_64
--> Running transaction check
---> Package pigz.x86_64 0:2.3.4-1.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================================================================
Package Arch Version Repository Size
================================================================================================================================
Updating:
docker-ce x86_64 18.03.0.ce-1.el7.centos docker-ce-stable 35 M
Installing for dependencies:
pigz x86_64 2.3.4-1.el7 ol7_developer_EPEL 80 k
Transaction Summary
================================================================================================================================
Install ( 1 Dependent package)
Upgrade 1 Package
Total download size: 35 M
Is this ok [y/d/N]: y
You may wonder why I install Docker on Oracle Linux rather than CentOS. The MOS Doc ID 2216342.1 mentions that Oracle will support customers running Oracle Database (single instance) in Docker containers running on Oracle Linux 7 with UEK4 or Red Hat Enterprise Linux 7.
If you want to validate your Docker install for running Oracle Database, the easiest is to use the image build script provided by Oracle:
git clone https://github.com/oracle/docker-images.git
cd ./docker-images/OracleDatabase/SingleInstance/dockerfiles/
# download and move linuxx64_12201_database.zip is in 12.2.0.1 subdirectory
sh buildDockerImage.sh -v 12.2.0.1 -e
Those are maintained by Gerald Venzl, Oracle product manager for database development, so they are obviously the best way to run Oracle Database on Docker. You can read all related best practices from the same author. Once you have that running, you have validated your environment and you can customize further if you want.
Cet article Docker-CE on Oracle Enterprise Linux 7 est apparu en premier sur Blog dbi services.
Insert…on conflict with partitions finally works in PostgreSQL 11
Another partitioning improvement for PostgreSQL 11: Insert…on conflict is now supported (for most cases) in PostgreSQL 11 thanks to this commit. Lets see how it works.
We’ll again use the slightly modified little list partitioned table from the last post, here in PostgreSQL 10:
postgres=# select version();
version Insert
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)
postgres=# create table part ( a int, b int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# create unique index iu1 on part_1 (a);
CREATE INDEX
postgres=# create unique index iu2 on part_2 (a);
CREATE INDEX
postgres=# \d+ part
Table "public.part"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
list | character varying(5) | | | | extended | |
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('beer'),
part_2 FOR VALUES IN ('wine')
postgres=# \d+ part_1
Table "public.part_1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
list | character varying(5) | | | | extended | |
Partition of: part FOR VALUES IN ('beer')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = ANY (ARRAY['beer'::character varying(5)])))
Indexes:
"iu1" UNIQUE, btree (a)
postgres=# \d+ part_2
Table "public.part_2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
list | character varying(5) | | | | extended | |
Partition of: part FOR VALUES IN ('wine')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = ANY (ARRAY['wine'::character varying(5)])))
Indexes:
"iu2" UNIQUE, btree (a)
Insert some sample data:
postgres=# insert into part(a,b,list) values (1,1,'beer'); INSERT 0 1 postgres=# insert into part(a,b,list) values (2,2,'beer'); INSERT 0 1 postgres=# select * from part; a | b | list ---+---+------ 1 | 1 | beer 2 | 2 | beer (2 rows)
When you try the following in PostgreSQL 10 it will fail:
postgres=# insert into part(a,b,list) values (1,2,'beer') on conflict (a) do update set b = 2; ERROR: ON CONFLICT clause is not supported with partitioned tables
Doing that directly on the partition is working:
postgres=# insert into part(a,b,list) values (1,2,'beer') on conflict (a) do update set b = 2; ERROR: ON CONFLICT clause is not supported with partitioned tables postgres=# insert into part_1(a,b,list) values (1,2,'beer') on conflict (a) do update set b = 2; INSERT 0 1 postgres=# select * from part; a | b | list ---+---+------ 2 | 2 | beer 1 | 2 | beer (2 rows)
But who wants to do that directly on the partition? For sure this is a big limitation and not very useful. That got fixed now with PostgreSQL 11:
postgres=# select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 11devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)
postgres=# create table part ( a int, b int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# alter table part add constraint pk1 primary key (a,list);
ALTER TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# \d+ part
Table "public.part"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | not null | | plain | |
b | integer | | | | plain | |
list | character varying(5) | | not null | | extended | |
Partition key: LIST (list)
Indexes:
"pk1" PRIMARY KEY, btree (a, list)
Partitions: part_1 FOR VALUES IN ('beer'),
part_2 FOR VALUES IN ('wine')
postgres=# insert into part(a,b,list) values (1,1,'beer');
INSERT 0 1
postgres=# insert into part(a,b,list) values (2,2,'wine');
INSERT 0 1
postgres=# insert into part(a,b,list) values (1,2,'beer') on conflict (a,list) do update set b = 2;
INSERT 0 1
Cool. But be aware that there still is a case which is not working:
postgres=# select * from part; a | b | list ---+---+------ 1 | 2 | beer 2 | 2 | wine (2 rows) postgres=# insert into part(a,b,list) values (1,2,'beer') on conflict (a,list) do update set list='wine'; ERROR: invalid ON UPDATE specification DETAIL: The result tuple would appear in a different partition than the original tuple.
When you do an on conflict clause that would require to move the row into another partition (updating the partition key) it will fail.
Cet article Insert…on conflict with partitions finally works in PostgreSQL 11 est apparu en premier sur Blog dbi services.
How reimaging detects the ODA hardware?
A long time ago, ODA referred to the only model that existed (nearly). Actually, not so long time ago, as ODA X3, X4 and X5 were released between 2013 and 2015. And it was quite the same hardware.
Now there is 3 current models in the X7 range, and they were 4 in the previous generation (X6).
ODA is now divided in 2 main lines. High Availability models, understand 2 nodes and a shared storage like the first generations. And lite models, with only 1 node and local NVMe SSD, obviously more afordable than HA models.
Beginning with X6, lite models came with a brand new ISO for reimaging, including the new odacli, a higher level administration tool (compared to oakcli), and X6-2HA remained with the same ISO as previous gen models.
But now, X7 range saw the adoption of odacli for all models. And if you stick with bare metal, ISO is now the same for reimaging all the range. Quite convenient isn’t it?
I recently asked myself: how the reimaging process determines the correct model?
First of all, you can look at your ODA model with odaadmcli:
odaadmcli show env_hw BM ODA X7-2 Medium
As you may know, reimaging process is fully automatic, and you have nothing to provide unless the correct ISO.
If ODA X7-S has only one Xeon CPU, nodes for ODA X7-M and X7-2HA are barelly the same, so what differs from them?
I first thought it was somewhere hardcoded, but it doesn’t seems to. ODA X7 is just the same hardware as the multi-purpose Oracle Server X7-2. Among the ISO files, I found a script that detects correct model by counting the number of local NVMe SSD (quite smart because HA model has only M2 SSD disks for the system and no NVMe SSDs), but it was not used during the reimaging…
Looking deeper on the system side, I found that model was simply part of the grub.conf boot options:
kernel /vmlinuz-4.1.12-94.4.1.el6uek.x86_64 ro root=/dev/mapper/VolGroupSys-LogVolRoot rd_NO_LUKS rd_MD_UUID=1ee3fdbc:3fdcdcf4:3a28f182:1a674b68 rd_LVM_LV=VolGroupSys/LogVolRoot rd_LVM_LV=VolGroupSys/LogVolSwap SYSFONT=latarcyrheb-sun16 pci=noaer crashkernel=256M@64M loglevel=3 panic=60 transparent_hugepage=never biosdevname=1 ipv6.disable=1 debug audit=1 intel_idle.max_cstate=1 nofloppy nomce numa=off console=ttyS0,115200n8 console=tty0 KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM LANG=en_US.UTF-8 PRODUCT=ORACLE_SERVER_X7-2 TYPE=X7_2_LITE_M
I discovered that odacli is based on this information. If you remove these settings, your ODA will be considered as a HA model, and odacli will crash:
DCS-10002: Invalid hardware platform
Okay, but it doesn’t tell me how the reimaging process decides which model to put in the grub.conf parameters…
Actually, grub.conf is configured at deployment with something from the ILOM, the “SP System identifier” located in the settings under the ILOM hostname:
As you can see, this field can be edited, and you can put everything in it (didn’t tried EXADATA…). Unfortunatly, it’s just below the “SP Hostname” and some people would probably like to change this identifier in the same time they are feeding the hostname. But it’s a bad idea because your ODA would not be correctly deployed for the next time you’ll need to reimage!
Be aware of that and please do not touch this identifier. Keep it as it is.
Notes :
– reimaging was done with patch 23530609 (version 12.2.1.2.0)
– default hostname for the ODA ILOM is… the SP System Identifier
Cet article How reimaging detects the ODA hardware? est apparu en premier sur Blog dbi services.
The WAL segement size becomes changeable in PostgreSQL 11
Up to PostgreSQL 10 the wal segment size was fixed once PostgreSQL was compiled and installed. When you wanted to have something else than the default you needed to tell that to the configure command when building from source, e.g.
postgres@pgbox:/home/postgres/postgresql/ [PGDEV] ./configure --with-wal-segsize=64MB
For PostgreSQL 11 two commits landed (1 and 2) which change this. Lets have a look.
When you check the current development documentation for initdb you’ll notice a new parameter called “–wal-segsize”. This allows to change the WAL segment size from the default of 16MB when you initialize you new cluster:
postgres@pgbox:/home/postgres/ [PGDEV] initdb --wal-segsize=32 /var/tmp/aa
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locales
COLLATE: en_US.utf8
CTYPE: en_US.utf8
MESSAGES: en_US.utf8
MONETARY: de_CH.UTF-8
NUMERIC: de_CH.UTF-8
TIME: en_US.UTF-8
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory /var/tmp/aa ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /var/tmp/aa -l logfile start
Btw: You can also use pg_controldata to get the size of the WAL segments:
postgres@pgbox:/home/postgres/ [PGDEV] pg_controldata | grep "Bytes per WAL segment" Bytes per WAL segment: 33554432 postgres@pgbox:/home/postgres/ [PGDEV] echo "33554432/1024/1024" | bc 32
The other commit added the same option to pg_resetwal.
postgres@pgbox:/home/postgres/ [PGDEV] pg_resetwal -D /var/tmp/aa/ --wal-segsize=64 Write-ahead log reset postgres@pgbox:/home/postgres/ [PGDEV] pg_controldata | grep "Bytes per WAL segment" Bytes per WAL segment: 67108864 postgres@pgbox:/home/postgres/ [PGDEV] echo "67108864/1024/1024" | bc 64
So, hopefully this September when PostgreSQL 11 should be released, you do not need to install additional binaries for changing the WAL segment size. This will also allow major upgrades with pg_upgrade to a new cluster with a different WAL segment size. Nice, makes things easier.
Cet article The WAL segement size becomes changeable in PostgreSQL 11 est apparu en premier sur Blog dbi services.
The same MAC address for two interfaces on the same host?
Yes, sounds strange, but exactly this is what we faced today. In the end it is clear and not strange anymore but for a moment we really thought we are hitting a bug in the operating system, which was RedHat 7.4. For the little demo for this post I’ll use CentOS 7.4 but this should not really matter. Lets go.
The situation we faced looked like this:
root@:/etc/sysconfig/network-scripts/ [] ip a
1: lo: mtu 65536 qdisc noqueue state UNKNOWN qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: enp0s3: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:d6:95:ab brd ff:ff:ff:ff:ff:ff
inet 10.0.2.15/24 brd 10.0.2.255 scope global dynamic enp0s3
valid_lft 86295sec preferred_lft 86295sec
inet6 fe80::a00:27ff:fed6:95ab/64 scope link
valid_lft forever preferred_lft forever
3: enp0s8: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 08:00:27:5c:b0:e5 brd ff:ff:ff:ff:ff:ff
inet 192.168.22.99/24 brd 192.168.22.255 scope global enp0s8
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fe5c:b0e5/64 scope link
valid_lft forever preferred_lft forever
4: enp0s9: mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
5: enp0s10: mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
6: bond0: mtu 1500 qdisc noqueue state UP qlen 1000
link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
inet 192.168.22.223/24 brd 192.168.22.255 scope global bond0
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fe85:2d8/64 scope link tentative
valid_lft forever preferred_lft forever
As you can see the interfaces enp0s8 and enp0s10 have exactly the same MAC address. In addition to that the bonding interface bond0 has the same mac address as well:
4: enp0s9: mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
5: enp0s10: mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
6: bond0: mtu 1500 qdisc noqueue state UP qlen 1000
link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
inet 192.168.22.223/24 brd 192.168.22.255 scope global bond0
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fe85:2d8/64 scope link tentative
valid_lft forever preferred_lft forever
This was not so scary as we knew that the bonding interface will get one of the mac addresses of the slave interfaces. But both slaves had the same mac address, how that? It all depends on the bonding options. In our case:
root@:/etc/sysconfig/network-scripts/ [] cat ifcfg-bond0 DEVICE=bond0 TYPE=Bond BONDING_MASTER=yes BOOTPROTO=static IPADDR=192.168.22.223 NETMASK=255.255.255.0 GATEWAY=192.168.22.1 ONBOOT=yes BONDING_OPTS="mode=active-backup miimon=100"
What you need to know is that there is another parameter/option called fail_over_mac. The default value for this option (as we did not explicitly set it we are using the default) is 0/none which means all slave interfaces will use the same MAC address. The original MAC addresses can be seen here:
root@:/etc/sysconfig/network-scripts/ [] cat /proc/net/bonding/bond0 Ethernet Channel Bonding Driver: v3.7.1 (April 27, 2011) Bonding Mode: fault-tolerance (active-backup) Primary Slave: None Currently Active Slave: enp0s9 MII Status: up MII Polling Interval (ms): 100 Up Delay (ms): 0 Down Delay (ms): 0 Slave Interface: enp0s9 MII Status: up Speed: 1000 Mbps Duplex: full Link Failure Count: 0 Permanent HW addr: 08:00:27:85:02:d8 Slave queue ID: 0 Slave Interface: enp0s10 MII Status: up Speed: 1000 Mbps Duplex: full Link Failure Count: 0 Permanent HW addr: 08:00:27:e4:1c:63 Slave queue ID: 0
When we change that to 1/active and restart the network:
root@:/etc/sysconfig/network-scripts/ [] sed -i 's/BONDING_OPTS="mode=active-backup miimon=100"/BONDING_OPTS="mode=active-backup miimon=100 fail_over_mac=active"/g' ifcfg-bond0 root@:/etc/sysconfig/network-scripts/ [] systemctl restart network
… we will have different MAC addresses:
4: enp0s9: mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
5: enp0s10: mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
link/ether 08:00:27:e4:1c:63 brd ff:ff:ff:ff:ff:ff
6: bond0: mtu 1500 qdisc noqueue state UP qlen 1000
link/ether 08:00:27:e4:1c:63 brd ff:ff:ff:ff:ff:ff
inet 192.168.22.223/24 brd 192.168.22.255 scope global bond0
valid_lft forever preferred_lft forever
inet6 fe80::a00:27ff:fee4:1c63/64 scope link tentative
valid_lft forever preferred_lft forever
The downside of this approach is that the MAC address will change when the active interface goes down. The currently active interface is enp0s9 with the following MAC address:
root@:/etc/sysconfig/network-scripts/ [] cat /proc/net/bonding/bond0 | grep -i active
Bonding Mode: fault-tolerance (active-backup) (fail_over_mac active)
Currently Active Slave: enp0s9
root@:/etc/sysconfig/network-scripts/ [] ip a | grep -A 1 enp0s9
4: enp0s9: mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
The bonding interface has the same MAC address:
root@:/etc/sysconfig/network-scripts/ [] ip a | grep -A 1 bond0
4: enp0s9: mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
5: enp0s10: mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
link/ether 08:00:27:e4:1c:63 brd ff:ff:ff:ff:ff:ff
6: bond0: mtu 1500 qdisc noqueue state UP qlen 1000
link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
inet 192.168.22.223/24 brd 192.168.22.255 scope global bond0
valid_lft forever preferred_lft forever
When we bring the currently active interface (enp0s9) down the MAC address of the bonding interface will switch to the MAC address of enp0s10 (which then becomes the active interface):
root@:/etc/sysconfig/network-scripts/ [] ifdown enp0s9
Device 'enp0s9' successfully disconnected.
root@:/etc/sysconfig/network-scripts/ [] ip a | grep -A 1 bond0
5: enp0s10: mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
link/ether 08:00:27:e4:1c:63 brd ff:ff:ff:ff:ff:ff
6: bond0: mtu 1500 qdisc noqueue state UP qlen 1000
link/ether 08:00:27:e4:1c:63 brd ff:ff:ff:ff:ff:ff
inet 192.168.22.223/24 brd 192.168.22.255 scope global bond0
valid_lft forever preferred_lft forever
The third an last option for fail_over_mac is 3/follow which will change the MAC address of the slave to the bonding’s MAC address when it becomes active. Documentation here.
Cet article The same MAC address for two interfaces on the same host? est apparu en premier sur Blog dbi services.
When you execute the same statement thousands of times -> prepare it
Every time you send a SQL statement to PostgreSQL it must be parsed. Parsing is fast, yes, but parsing the same statement a thousand times can quickly sum up to quite some time the database could spend for something else. To avoid that PostgreSQL comes with the prepare statement. Using that you can avoid parsing of statements and only planning and executing will happen. Lets see how that works.
To generate some sample data I used pgbench with a scale factor of 100 which gives me 10’000’000 rows in the pgbench_accounts table:
postgres@pgbox:/home/postgres/ [PGDEV] pgbench -i -s 100 bench
postgres@pgbox:/home/postgres/ [PGDEV] psql bench
psql (11devel)
Type "help" for help.
bench=# select count(*) from pgbench_accounts;
count
----------
10000000
(1 row)
bench=# \d pgbench_accounts
Table "public.pgbench_accounts"
Column | Type | Collation | Nullable | Default
----------+---------------+-----------+----------+---------
aid | integer | | not null |
bid | integer | | |
abalance | integer | | |
filler | character(84) | | |
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
Lets assume we have the following simple query:
bench=# select count(*) from pgbench_accounts where aid = 11111;
count
-------
1
(1 row)
As said at the beginning of this post PostgreSQL will need to parse that statement. Using explain with the right options you are able to see how much time was spend on planning:
bench=# explain (analyze,buffers) select count(*) from pgbench_accounts where aid = 11111;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4.46..4.46 rows=1 width=8) (actual time=0.060..0.063 rows=1 loops=1)
Buffers: shared hit=4
-> Index Only Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.43..4.45 rows=1 width=0) (actual time=0.034..0.039 rows=1 loops=1)
Index Cond: (aid = 11111)
Heap Fetches: 0
Buffers: shared hit=4
Planning Time: 0.150 ms
Execution Time: 0.133 ms
(8 rows)
Planning this statement takes more time then executing it. Now assume you want to execute that very same statement one thousand times:
bench=# \t bench=# select 'select count(*) from pgbench_accounts where aid = 11111;' from generate_series(1,1000) i; \g test.sql bench=# \! cat test.sql | head select count(*) from pgbench_accounts where aid = 11111; select count(*) from pgbench_accounts where aid = 11111; select count(*) from pgbench_accounts where aid = 11111; select count(*) from pgbench_accounts where aid = 11111; select count(*) from pgbench_accounts where aid = 11111; select count(*) from pgbench_accounts where aid = 11111; select count(*) from pgbench_accounts where aid = 11111; select count(*) from pgbench_accounts where aid = 11111; select count(*) from pgbench_accounts where aid = 11111; select count(*) from pgbench_accounts where aid = 11111; ...
When we execute that we force PostgreSQL to parse all of those 1000 statements:
bench=# \timing Timing is on. bench=# \! time psql -f test.sql bench real 0m0.148s user 0m0.031s sys 0m0.015s
What you can do to avoid that is to prepare the statement:
bench=# prepare tt as select count(*) from pgbench_accounts where aid = 11111; PREPARE
Once it is prepared you can execute it:
bench=# execute tt;
count
-------
1
(1 row)
… and you can also explain it:
bench=# explain(analyze,buffers) execute tt;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4.46..4.46 rows=1 width=8) (actual time=0.066..0.069 rows=1 loops=1)
Buffers: shared hit=4
-> Index Only Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.43..4.45 rows=1 width=0) (actual time=0.037..0.043 rows=1 loops=1)
Index Cond: (aid = 11111)
Heap Fetches: 0
Buffers: shared hit=4
Planning Time: 0.021 ms
Execution Time: 0.269 ms
(8 rows)
You’ll notice that the planning time went down quite a lot compared to the not prepared statement:
bench=# explain (analyze,buffers) select count(*) from pgbench_accounts where aid = 11111;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4.46..4.46 rows=1 width=8) (actual time=0.167..0.172 rows=1 loops=1)
Buffers: shared hit=4
-> Index Only Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.43..4.45 rows=1 width=0) (actual time=0.030..0.037 rows=1 loops=1)
Index Cond: (aid = 11111)
Heap Fetches: 0
Buffers: shared hit=4
Planning Time: 0.167 ms
Execution Time: 0.248 ms
(8 rows)
When you do that one thousand times now:
bench=# \t bench=# select 'execute tt;' from generate_series(1,1000) i; \g test.sql bench=# \! sed -i '1s/^/prepare tt as select count(*) from pgbench_accounts where aid = 11111;/' test.sql bench=# \! time psql -f test.sql bench real 0m0.095s user 0m0.031s sys 0m0.010s
… execution time will come down. It is not much in that simple case but this is due to the simple statement. Btw: As prepared statements only life for the time of the session the sed command adds the prepare statement to the top of file, and preparing takes time as well. Without that execution time would be even less.
When your values change in the where clause you can do it like this:
bench=# prepare tt as select count(*) from pgbench_accounts where aid = $1;
PREPARE
Time: 0.571 ms
bench=# execute tt (123);
1
For completeness: When you want to get rid of a prepared statement without losing your session there is deallocate:
bench=# deallocate tt; DEALLOCATE Time: 0.623 ms
Cet article When you execute the same statement thousands of times -> prepare it est apparu en premier sur Blog dbi services.
PostgreSQL – logical replication with pglogical
Although PostgreSQL 10 integrate native logical replication (have a look here or here), it is always interesting to be aware of alternative solutions to the available in-core features.
One of those is called pglogical. It’s a Postgres extension developed by 2ndQuadrant, major contributor to PostgreSQL development.
The goal of this blog post is to discover how to install pglogical and configure it in order to generate simple logical replication cases.
To get the extension we should start with RPM repository installation :
postgres@ppas02:/u01/app/postgres/product/10/db_1/ [PG10] sudo yum install http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-rhel-1.0-3.noarch.rpm
Loaded plugins: fastestmirror
pglogical-rhel-1.0-3.noarch.rpm | 8.3 kB 00:00:00
Examining /var/tmp/yum-root-oH8huK/pglogical-rhel-1.0-3.noarch.rpm: pglogical-rhel-1.0-3.noarch
Marking /var/tmp/yum-root-oH8huK/pglogical-rhel-1.0-3.noarch.rpm to be installed
...
...
...
Installed:
pglogical-rhel.noarch 0:1.0-3
Complete!
postgres@ppas02:/u01/app/postgres/product/10/db_1/ [PG10]
Once installed, we can add the extension inside our Postgres instance :
postgres=# CREATE EXTENSION pglogical;
2017-12-18 16:24:39.079 CET [4327] ERROR: pglogical is not in shared_preload_libraries
2017-12-18 16:24:39.079 CET [4327] STATEMENT: CREATE EXTENSION pglogical;
ERROR: pglogical is not in shared_preload_libraries
postgres=#
Oops… the pglogical library must be loaded when the cluster starts :
postgres=# alter system set shared_preload_libraries = 'pglogical';
ALTER SYSTEM
postgres=#
Restart the cluster to take it in account :
postgres@ppas01:/u01/app/postgres/product/10/db_1/share/ [PG10] pg_ctl -D /u02/pgdata/PG10/ stop
waiting for server to shut down....2017-12-18 16:28:03.895 CET [4447] LOG: received fast shutdown request
2017-12-18 16:28:03.902 CET [4447] LOG: aborting any active transactions
2017-12-18 16:28:03.923 CET [4447] LOG: worker process: logical replication launcher (PID 4455) exited with exit code 1
2017-12-18 16:28:03.923 CET [4449] LOG: shutting down
2017-12-18 16:28:03.940 CET [4447] LOG: database system is shut down
done
server stopped
postgres@ppas01:/u01/app/postgres/product/10/db_1/share/ [PG10] pg_ctl -D /u02/pgdata/PG10/ start
waiting for server to start....2017-12-18 16:28:10.405 CET [4654] LOG: listening on IPv4 address "0.0.0.0", port 5420
2017-12-18 16:28:10.405 CET [4654] LOG: listening on IPv6 address "::", port 5420
2017-12-18 16:28:10.407 CET [4654] LOG: listening on Unix socket "/tmp/.s.PGSQL.5420"
2017-12-18 16:28:10.416 CET [4655] LOG: database system was shut down at 2017-12-18 16:28:03 CET
2017-12-18 16:28:10.426 CET [4654] LOG: database system is ready to accept connections
2017-12-18 16:28:10.430 CET [4661] LOG: starting pglogical supervisor
2017-12-18 16:28:10.435 CET [4663] LOG: manager worker [4663] at slot 0 generation 1 detaching cleanly
2017-12-18 16:28:10.439 CET [4664] LOG: manager worker [4664] at slot 0 generation 2 detaching cleanly
2017-12-18 16:28:10.444 CET [4665] LOG: manager worker [4665] at slot 0 generation 3 detaching cleanly
done
server started
postgres@ppas01:/u01/app/postgres/product/10/db_1/share/ [PG10]
Once the cluster restarted with the library, we may noticed a new OS process :
postgres@ppas01:/u01/app/postgres/product/10/db_1/share/ [PG10] ps -ef | grep pglogical
postgres 5001 4994 0 16:33 ? 00:00:00 postgres: bgworker: pglogical supervisor
We should now be able to create the extension :
postgres=# CREATE EXTENSION pglogical;
CREATE EXTENSION
postgres=#
postgres=# \dx+ pglogical
Objects in extension "pglogical"
Object description
------------------------------------------------------------------------------------------
function pglogical.alter_node_add_interface(name,name,text)
function pglogical.alter_node_drop_interface(name,name)
function pglogical.alter_replication_set(name,boolean,boolean,boolean,boolean)
function pglogical.alter_subscription_add_replication_set(name,name)
function pglogical.alter_subscription_disable(name,boolean)
function pglogical.alter_subscription_enable(name,boolean)
function pglogical.alter_subscription_interface(name,name)
function pglogical.alter_subscription_remove_replication_set(name,name)
function pglogical.alter_subscription_resynchronize_table(name,regclass,boolean)
function pglogical.alter_subscription_synchronize(name,boolean)
function pglogical.create_node(name,text)
function pglogical.create_replication_set(name,boolean,boolean,boolean,boolean)
function pglogical.create_subscription(name,text,text[],boolean,boolean,text[],interval)
function pglogical.drop_node(name,boolean)
function pglogical.drop_replication_set(name,boolean)
function pglogical.drop_subscription(name,boolean)
function pglogical.pglogical_gen_slot_name(name,name,name)
function pglogical.pglogical_max_proto_version()
function pglogical.pglogical_min_proto_version()
function pglogical.pglogical_node_info()
function pglogical.pglogical_version()
function pglogical.pglogical_version_num()
function pglogical.queue_truncate()
function pglogical.replicate_ddl_command(text,text[])
function pglogical.replication_set_add_all_sequences(name,text[],boolean)
function pglogical.replication_set_add_all_tables(name,text[],boolean)
function pglogical.replication_set_add_sequence(name,regclass,boolean)
function pglogical.replication_set_add_table(name,regclass,boolean,text[],text)
function pglogical.replication_set_remove_sequence(name,regclass)
function pglogical.replication_set_remove_table(name,regclass)
function pglogical.show_repset_table_info(regclass,text[])
function pglogical.show_subscription_status(name)
function pglogical.show_subscription_table(name,regclass)
function pglogical.synchronize_sequence(regclass)
function pglogical.table_data_filtered(anyelement,regclass,text[])
function pglogical.wait_slot_confirm_lsn(name,pg_lsn)
table pglogical.depend
table pglogical.local_node
table pglogical.local_sync_status
table pglogical.node
table pglogical.node_interface
table pglogical.queue
table pglogical.replication_set
table pglogical.replication_set_seq
table pglogical.replication_set_table
table pglogical.sequence_state
table pglogical.subscription
view pglogical.tables
(48 rows)
The wal_level parameter must be set to ‘logical’ for logical replication :
postgres=# show wal_level;
wal_level
-----------
logical
(1 row)
And the pg_hba file must contains an entry to allow connections for replication purpose.
Important : all the steps we have seen until now must be done on the standby side as well.
Logical replication with Postgres is based on several components :
– Provider : the primary node/cluster
– Subscriber : the standby node/cluster, on which data will be replicated
– Replication set : collection of table you want to replicate
– Subscription : runs the replication, based on the replication set
We’ll start by creating the provider on the primary side :
postgres=# select pglogical.create_node (node_name := 'provider1', dsn := 'host=192.168.22.37 port=5420 dbname=postgres');
create_node
-------------
2976894835
(1 row)
postgres=# select * from pglogical.node_interface;
if_id | if_name | if_nodeid | if_dsn
------------+-----------+------------+-----------------------------------------------
2402836775 | provider1 | 2976894835 | host=192.168.22.37 port=5420 dbname=postgres
(1 row)
Notice again a new OS process :
postgres@ppas01:/home/postgres/ [PG10] ps -ef | grep pglogical
postgres 1796 1788 0 15:13 ? 00:00:00 postgres: bgworker: pglogical supervisor
postgres 4566 1788 0 16:05 ? 00:00:00 postgres: bgworker: pglogical manager 13211
On the standby side, we create the subscriber :
postgres=# select pglogical.create_node(node_name := 'subscriber1', dsn := 'host=192.168.22.38 port=5420 dbname=postgres');
create_node
-------------
330520249
(1 row)
postgres=# select * from pglogical.node_interface;
if_id | if_name | if_nodeid | if_dsn
------------+-------------+-----------+----------------------------------------------
2049915666 | subscriber1 | 330520249 | host=192.168.22.38 port=5420 dbname=postgres
2402836775 | provider1 | 2976894835 | host=192.168.22.37 port=5420 dbname=postgres
(1 row)
What about replication set ? There is 3 existing by default with a different DML replication behavior for each :
postgres=# select * from pglogical.replication_set;
set_id | set_nodeid | set_name | replicate_insert | replicate_update | replicate_delete | replicate_truncate
------------+------------+---------------------+------------------+------------------+------------------+--------------------
290045701 | 2976894835 | default | t | t | t | t
3461245231 | 2976894835 | default_insert_only | t | f | f | t
2465411902 | 2976894835 | ddl_sql | t | f | f | f
We can easily add our own :
postgres=# select pglogical.create_replication_set('my_set', true, true, true, true);
create_replication_set
------------------------
1521346531
To start the replication we have to create a subscription (using the replication set we created) from the standby side :
postgres=# select pglogical.create_subscription(subscription_name := 'subscription1', provider_dsn := 'host=192.168.22.37 port=5420 dbname=postgres', replication_sets := '{my_set}');
create_subscription
---------------------
1763399739
(1 row)
postgres=# select subscription_name, status FROM pglogical.show_subscription_status();
subscription_name | status
-------------------+-------------
subscription1 | replicating
Have a look in the log file… replication started with the correct provider and subscription :
2018-02-02 15:16:14.234 CET [5488] LOG: starting apply for subscription subscription1
2018-02-02 15:17:54.547 CET [5502] LOG: starting pglogical database manager for database postgres
postgres@ppas02:/u02/pgdata/PG10/ [PG10] ps -ef | grep pglogical
postgres 3113 3105 0 10:01 ? 00:00:00 postgres: bgworker: pglogical supervisor
postgres 4587 3105 0 13:47 ? 00:00:00 postgres: bgworker: pglogical manager 13211
postgres 5060 3105 0 15:06 ? 00:00:00 postgres: bgworker: pglogical apply 13211:1763399739
The last step is to add the tables in the replication set we created :
postgres=# select pglogical.replication_set_add_all_tables('my_set', ARRAY['public']);
replication_set_add_all_tables
--------------------------------
t
(1 row)
The insert/update/delete operations run against the primary server are now replicated to the standby one.
Cet article PostgreSQL – logical replication with pglogical est apparu en premier sur Blog dbi services.
Updating the partition key now works in PostgreSQL 11
In the last post about partitioning improvements in PostgreSQL 11 we talked about unique indexes and primary keys on partitioned tables. Both did not work in PostgreSQL 10 but now do in PostgreSQL 11. Another operation that did not work in PostgreSQL 10 and does now in PostgreSQL 11 is: Updating the partition key, which means that a row will move from one partition to another. Lets have a look at that.
We’ll use the same little list partitioned table as in the last post and start with PostgreSQL 10:
postgres=# select version();
version Insert
----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)
postgres=# create table part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# \d+ part
Table "public.part"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
list | character varying(5) | | | | extended | |
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('beer'),
part_2 FOR VALUES IN ('wine')
Insert some data:
postgres=# insert into part (a,list) select i,'beer' from generate_series(1,10) i; INSERT 0 10 postgres=# insert into part (a,list) select i,'wine' from generate_series(11,20) i; INSERT 0 10
So we have ten beers and ten wines.
postgres=# select count(*) from only part_1;
count
-------
10
(1 row)
postgres=# select count(*) from only part_2;
count
-------
10
(1 row)
What happens if we update the partition key because we like more beer than wine in PostgreSQL 10?
postgres=# update part set list = 'beer' where a = 15; ERROR: new row for relation "part_2" violates partition constraint DETAIL: Failing row contains (15, beer).
Not so good. Lets try the same in PostgreSQL 11:
postgres=# select version();
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 11devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)
postgres=# create table part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# insert into part (a,list) select i,'beer' from generate_series(1,10) i;
INSERT 0 10
postgres=# insert into part (a,list) select i,'wine' from generate_series(11,20) i;
INSERT 0 10
postgres=# update part set list = 'beer' where a = 15;
UPDATE 1
Excellent, just works.
Cet article Updating the partition key now works in PostgreSQL 11 est apparu en premier sur Blog dbi services.