Feed aggregator

Stacking im Pickleball – Kurzer Leitfaden

The Oracle Instructor - 4 hours 12 min ago

Beim Doppel im Pickleball wird die Position der einzelnen Spieler (linke Seite oder rechte Seite) normalerweise durch den Spielstand bestimmt. Stacking ist eine Methode die Seite unabhängig vom Spielstand zu wählen. Der Begriff (Stacking = Stapelung) kommt daher, dass dabei häufig zwei Spieler vorübergehend auf derselben Seite stehen. Bisher sehe ich in meinem Umfeld kaum Stacking – auch bei den Deutschen Pickleball Meisterschaften 2022 habe ich es bei keinem Doppel beobachtet. Liegt wohl entweder daran, dass ich überwiegend bei Level 2.5 und 3.0 zugeschaut habe, oder daran, dass Pickleball in Deutschland ganz allgemein noch in den Kinderschuhen steckt, während Stacking eher eine Technik für Fortgeschrittene ist. Vielleicht wird es durch diesen Beitrag ja populärer.

Warum Stacking?

Gründe für Stacking können sein

  • Wir haben als Spieler im Team eine Lieblingsseite
    • Linkshänder nach rechts
    • Frau im Mixed nach rechts
    • Stärkere Vorhand (bei Rechtshändern) nach links
  • Die Gegner wollen immer einen von uns anspielen
  • Als taktische Variante
Wichtig: Ausgangspositionen merken!

Grundsätzlich ist es beim Pickleball im Doppel wichtig, sich die Ausgangspositionen zu merken: Wer anfangs rechts steht, wird zukünftig bei geradem Teamscore als Aufschläger und als Rückschläger rechts stehen müssen. Das gleiche gilt für den Spieler, der beim Stand von 0-0-2 links steht: Bei geradem Teamscore muss er jetzt und zukünftig als Aufschläger und als Rückschläger auf der linken Seite sein. Ein Verstoß gegen diese Regel führt streng genommen zum Verlust des Punkts bzw. der Ralley. Durch die beim Stacking erfolgenden Stellungswechsel ist die Gefahr etwas höher, da durcheinander zu kommen.

Stacking beim Aufschlag

Wir betrachten zuerst Stacking beim Aufschlag. Das ist unsere Wunschposition:

Wir haben Aufschlag und stehen in Wunschposition bei 0-0-2: A links und B rechts. Unser Teamscore ist gerade: Alles ist wie sonst auch, kein Stacking nötig.

Wenn wir den Punkt gewinnen, steht es 1-0-2. Der Teamscore ist nun ungerade. Stacking passiert: A geht nur für den Aufschlag kurz nach links und wechselt dann wieder zurück nach rechts. B bleibt links:

Angenommen, im weiteren Spielverlauf verlieren wir das Aufschlagrecht, und es wechselt zu uns zurück beim Stand von 3-0-1. Unser Teamscore ist also ungerade. Bei ungeradem Score muss B rechts aufschlagen, daher erfolgt Stacking:

B geht nur für den Aufschlag kurz nach rechts und wechselt gleich danach wieder zurück auf die Lieblingsposition. A bleibt rechts stehen.

Zusammenfassung Aufschlag

Bei geradem Teamscore schlagen wir wie üblich auf, jeder ist in Wunschposition.

Bei ungeradem Teamscore (1,3,5,7,9) passiert Stacking:

Der Aufschläger steht mittig nur für den Aufschlag auf der ungeliebten Seite, der Partner außen daneben.

Stacking beim Return

Auch beim Return kann Stacking erfolgen. Bei geradem Teamscore haben wir ohnehin schon die Wunschposition und behalten die einfach bei, z.B. beim Stand von 3-2-1:

Bei ungeradem Teamscore passiert Stacking. Hier z.B. beim Stand von 3-1-1:

B muss den Return von der ungeliebten rechten Seite spielen. Er wird nach links wechseln und A nach rechts:

Angenommen, die Gegner machen den Punkt und servieren nun beim Stand von 4-1-1 von links. Zwar haben wir grad unsere Wunschposition (A rechts und B links) eingenommen, aber den Return von links muss beim Stand 4-1-1 zwingend A spielen. Also machen wir erneut Stacking:

Nach dem Aufschlag läuft A diagonal nach rechts an die NVZ, während B seitlich nach links wechselt.

Variante

Alternativ zum oben gezeigten Stacking kann der Partner auch schon direkt auf der bevorzugten Seite stehen:

Das macht unsere Absicht für den Gegner zwar offensichtlich, ist aber vielleicht etwas einfacher für uns in der Durchführung.

Zusammenfassung Return

Beim Return erfolgt also Stacking ebenfalls bei ungeradem Team-score: Der Rückschläger läuft diagonal zur bevorzugten Seite an die NVZ und der Partner wechselt die Seite, bzw. steht schon auf seiner Lieblingsseite außen.

Flexibles Stacking

Stacking kann auch nur von Fall zu Fall praktiziert werden. Mit Linkshändern im Team oder beim Mixed wird es häufig permanent während des ganzen Spiels gemacht. Flexibles Stacking kann benutzt werden, um dem Gegner das ständige Anspielen eines bestimmten Team-Mitglieds zu erschweren, oder als taktisches Mittel zwischendurch. Beim Aufschlag kann man sich absprechen und dann unmittelbar nach dem Service die Seiten wechseln. Beim Return kann der vorn stehende Partner Zeichen geben: Offene Hand hinter dem Rücken bedeutet: Ich wechsle die Seite. Der Rückschläger wird dann entsprechend diagonal auf die andere Seite laufen. Faust hinter dem Rücken bedeutet: Ich bleibe auf dieser Seite.

Stacking Pro & Contra

Sollte ich in meinen Doppelspielen Stacking einsetzen?

Pro

  • Es ist nützlich bei Vorliebe für bestimmte Seite
  • Man kann damit einen schwächeren Partner entlasten
  • Es kann gut als taktisches Mittel eingesetzt werden

Contra

  • Der Laufweg beim Return ist etwas länger
  • Der Partner ist auch in Bewegung, was seinen Schlag etwas schwieriger macht
  • Stacking erfordert ein eingespieltes Team
  • Es wird für alle vier Spieler schwieriger, Spielstand und Spielerpositionen korrekt mitzuhalten

Die Punkte unter der Rubrik Contra sind der Grund, warum sich Stacking nur für fortgeschrittene, häufig zusammenspielende Doppel empfiehlt.

Das Video erläutert Stacking ähnlich wie der Artikel und zeigt auch als Beispiel, wie Profis Stacking praktizieren:

 

Categories: DBA Blogs

Purpose of DGbroker Configuration Wide Service "primary_unique_name"_CFG

Tom Kyte - Tue, 2022-08-16 07:46
Hi team, Is it possible to explain the purpose of '%CFG' broker services and who and how is using it ? Regards Dejan
Categories: DBA Blogs

Need to purge the hystorical custom data

Tom Kyte - Tue, 2022-08-16 07:46
We have around 50 custom tables, which are using for the traces in db objects. Each table contains more than billion records.most of the tables don't have indexes. I want to delete data >sysdare-365 days. Which is not working it is taking very very long time. We can not go for renaming the table name. If we rename the table name, all our objects will be invalid.please suggest.
Categories: DBA Blogs

Database Recovery scenario

Tom Kyte - Tue, 2022-08-16 07:46
Hi team, i take Level-0 backup on Sunday, then from monday to saturday i take differential incremental backups. i want to restore backup on another machine, i have control file backup of Sunday. i can restore level-0 backup easily using control file of Sunday because Sunday control file has information of level-0 backup. how can i apply Monday to Saturday differential incremental backup on level-0 backup of Sunday ? Thanks & Regards Fahd
Categories: DBA Blogs

Configure CMU for all PDBs in the CDB

Tom Kyte - Tue, 2022-08-16 07:46
Has anyone setup CMU to work for all the PDBs within a CDB ? I have it working for a single PDB but when I created a new wallet based off the GUID it doesn't work I get an ora-1017 but it works for the 1st PDB I setup. I'm running Oracle 19.14 on Windows 2019. I don't have an override in the SQLNET.ora, my wallet is in the C:\ORACLE_BASE\admin\<CDB>\<PDB>\wallet as is the dsi.ora file for each PDB. The Create user IDENTIFIED GLOBALLY is in each PDB. And CMU_WALLER_DIR is set in each PDB pointing to it correct directory. So what am I missing to get it to work for the 2nd PDB ? Should it be setup at the CDB level vs. the PDBs level ? If so then should the user be created in the CDB as a common user ? I have a SR open with Support but I'm not getting anywhere. Any help is greatly appreciate. Thanks, Barb.
Categories: DBA Blogs

silent connection to sqlplus in Korn shell

Tom Kyte - Tue, 2022-08-16 07:46
Test case is connect to sqlplus in silent mode in korn shell and run a bunch of sql queries in one single sqlplus session..and where the out would be seen.. tried the following in test.ksh #!/usr/bin/ksh CURR_DIR=`pwd` echo "current directory is:" $CURR_DIR . $CURR_DIR/db.env echo "start time:" `date` cd $ORACLE_HOME/bin echo "location is: "`pwd` sqlplus -s EMDBO?EMDBO@hr92u017 |& OUT="" print -p "set heading off;" print -p "set feedback off;" print -p "set pagesize 0;" print -p "set linesize 500;" print -p "SELECT count(*)FROM dba_invalid_objects;" print -p "show con_name;" read -p OUT read -p OUT 2>/scratch/oracle/null ## when run , script is not progressing further as below, need help here.. $ sh testksh.ksh current directory is: /u01/app/dynamicscripts start time: Fri Aug 12 11:32:10 GMT 2022 location is: /u01/app/oracle/product/testdbh/bin
Categories: DBA Blogs

COMMA_TO_TABLE not found/ Invalid identifiers

Tom Kyte - Tue, 2022-08-16 07:46
<code> declare v_list varchar2(4000); begin begin v_list := :P4_STATES; exception when no_data_found then v_list :='0'; end; for y in (select * from(table(DBMS_UTILITY.comma_to_table(:P4_STATES)))) loop INSERT INTO COVIDAPI(STATES_NO) values(y.COLUMN_VALUE); end loop; for y in (select * from(table(comma_to_table(:P4_POSITIVE)))) loop insert into COVIDAPI(POSITIVE) values(y.column_value); end loop; for y in (select * from(table(comma_to_table(:P4_NEGATIVE)))) loop insert into COVIDAPI(NEGATIVE) values(y.column_value); end loop; end; </code> error:ORA-06550: line 9, column 47: PL/SQL: ORA-00904: : invalid identifier <code> select * from all_objects where object_name ='COMMA_TO_TABLE' </code> result: no data found question: how to install or create the COMMA_TO_TABLE package? I use apex on cloud, it's not installed in my laptop!
Categories: DBA Blogs

Odd behavior with predicate order, json_textcontains, and OR

Tom Kyte - Tue, 2022-08-16 07:46
Database Version 19.16.0.0.0 I have a JSON document with "address", "address_name", and "state" keys among others. I'm searching for a user input in either "address" OR "address_name" in the JSON document. I also have an AND for the "state" key. Depending on the order of the predicate, I get results or no results. What am I missing here? My script is in LiveSQL Also, is there a way to rank/score the results when using JSON_TEXTCONTAINS? If I wanted to use some of the other operators, like NDATA, SDATA, MDATA, FUZZY, WITHIN, or INPATH, do I use CONTAINS, or is it possible with JSON_TEXTCONTAINS. I would appreciate some example statements which show how to use these operators and SQL/JSON Path Expressions with JSON_TEXTCONTAINS. Thanks
Categories: DBA Blogs

Dynamic UI in Django with Alpine.js

Andrejus Baranovski - Mon, 2022-08-15 08:11
Django generates static HTML UI, this requires a full request submit to the server. But you can build dynamic UI's, by including and using Alpine.js library. With Alpine.js you can open dialogs, hide content or disable UI controls. This helps to improve Django app UI usability.

 

お菓子作りには専門学校に通う

Marian Crkon - Sun, 2022-08-14 20:54
料理は色々なアレンジをして楽しむことが出来ますが、お菓子作りはアレンジを行うと美味しい物を作ることが難しいです...

お菓子作りには専門学校に通う

The Feature - Sun, 2022-08-14 20:54

料理は色々なアレンジをして楽しむことが出来ますが、お菓子作りはアレンジを行うと美味しい物を作ることが難しいです。美味しいお菓子を作るためには基礎を勉強をする必要がありますが、独学では限界があるので、専門学校に通うことをお勧め致します。専門学校なら美味しいお菓子の作り方を基礎から全て教えてもらう事が可能です。プロを目指す場合にも有効ですし、趣味でお菓子を作りたい方にもお薦めです。

専門学校なので費用は学校によって違いますが、様々なコースが用意されている場合が多くありますので、自分が目指すお菓子作りのコースを選ぶようにして下さい。プロを目指す場合は本格的な勉強が必要になりますので、数年かかる場合もあります。学校によっては海外留学をする事もありますので、本格的な事を学ぶことが可能です。趣味で専門学校に通う場合は短期で通えるコースを選びましょう。

短期コースなら基礎を重点的に教えてくれるので、基礎を学ぶ事で、様々な物を作ることが可能になります。アレンジまで教えてくれる専門学校もありますが、趣味で長期間通うと費用が大きくかかってしまうので、注意が必要です。基礎を習って覚えて作る物は今まで作っていた物と比べ物になりませんので、作るのが楽しくなります。まるでパティシェになった気分を味わうことが出来るので、ホームパーティーを開いて楽しむことも可能です。

華やかなケーキやデザートが作れる様になると異性にアピールしやすくなります。

Categories: APPS Blogs

Direct Path Inserts and Locks

Hemant K Chitale - Sun, 2022-08-14 04:40

 In my previous post I had demonstrated how a Direct Path Insert in one session blocks another Direct Path Insert from another session, except when the two sessions explicitly name separate target Partitions.

Here, I will lock at how Oracle creates Locks for such operations.  In the output below, the SQLPrompt is set to either 'Sesn_1' or 'Sesn_2' or 'SYSTEM' (the DBA session) to indicate which session is executing the SQL statement.

First, running the Direct Path Insert without specifying the target Partition name.



SYSTEM>l
1 select object_name, subobject_name, object_type, object_id, data_object_id
2 from dba_objects
3 where owner = 'HEMANT'
4 and object_name in ('MY_PART_TABLE')
5 and object_type in ('TABLE','TABLE PARTITION')
6* order by 1, 2 nulls first, 4
SYSTEM>/

OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
---------------- ---------------- ----------------------- ---------- --------------
MY_PART_TABLE TABLE 81817
MY_PART_TABLE P_100 TABLE PARTITION 81818 81827
MY_PART_TABLE P_200 TABLE PARTITION 81819 81828
MY_PART_TABLE P_300 TABLE PARTITION 81820 81820

SYSTEM>
SYSTEM>truncate table hemant.my_part_table;

Table truncated.

SYSTEM>
SYSTEM>select count(*) from v$locked_object;

COUNT(*)
----------
0

SYSTEM>



Sesn_1>select distinct sid from v$mystat;

SID
----------
138

Sesn_1>
Sesn_1>l
1 insert /*+ APPEND */ into my_part_table
2 select rownum, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
Sesn_1>/

50 rows created.

Sesn_1>



Sesn_2>select distinct sid from v$mystat;

SID
----------
384

Sesn_2>
Sesn_2>l
1 insert /*+ APPEND */ into my_part_table
2 select rownum+101, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
Sesn_2>/
---------- Sesn_2 is now in a Wait



SYSTEM>select event from v$session where sid=384;

EVENT
----------------------------------------------------------------
enq: TM - contention

SYSTEM>
SYSTEM>select session_id, object_id, locked_mode
2 from v$locked_object
3 /

SESSION_ID OBJECT_ID LOCKED_MODE
---------- ---------- -----------
138 81817 6
384 81817 0

SYSTEM>
---------- Object ID 81817 is the Table itself, locked by Session 138 -- Sesn_1
SYSTEM>select sid, type, id1, id2, lmode, request, block
2 from v$lock
3 where sid in (138,384)
4 order by 1
5 /

SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
138 TM 81817 0 6 0 1
138 AE 134 4057974068 4 0 0
138 TX 393246 7880 6 0 0
384 AE 134 4057974068 4 0 0
384 TM 81817 0 0 6 0

SYSTEM>
---------- Session 138 has TM Lock Mode6 on Object 81817 -- the Table itself
---------- Session 384 (Sesn_2, Waiting) is requesting a TM Lock Mode6



So, it is clear that Session 1 (SID 138) had a TM Lock (Mode->6) on the Table, blocking Session 2 (384) (which is requesting the same Mode->6 lock) when the Direct Path Insert specified the Table name alone.
The TX lock by SID 138  is the Transaction Row Exclusive Lock.  What is blocking Session 2 (SID 384) is that it is requesting a Mode->6 lock on the same Object (the Table) as already held by Session 1 (SID 138)

Then, when I issue a Rollback (or Commit) from Sesn_1, the Insert by Sesn_2 goes through.

Before this next test, I rollback both the Inserts

Next, with the target Partition named



Sesn_1>l
1 insert /*+ APPEND */ into my_part_table partition (p_100)
2 select rownum, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
Sesn_1>/

50 rows created.

Sesn_1>



Sesn_2>l
1 insert /*+ APPEND */ into my_part_table partition (p_200)
2 select rownum+101, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
Sesn_2>/

50 rows created.

Sesn_2>



SYSTEM>select event from v$session where sid=384;

EVENT
----------------------------------------------------------------
SQL*Net message from client

SYSTEM>
SYSTEM>select session_id, object_id, locked_mode
2 from v$locked_object
3 /

SESSION_ID OBJECT_ID LOCKED_MODE
---------- ---------- -----------
138 81817 3
138 81818 6
384 81817 3
384 81819 6

SYSTEM>
SYSTEM>l
1 select sid, type, id1, id2, lmode, request, block
2 from v$lock
3 where sid in (138,384)
4* order by 1,2,3
SYSTEM>/

SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
138 AE 134 4057974068 4 0 0
138 TM 81817 0 3 0 0
138 TM 81818 0 6 0 0
138 TX 65540 6296 6 0 0
384 AE 134 4057974068 4 0 0
384 TM 81817 0 3 0 0
384 TM 81819 0 6 0 0
384 TX 458781 6211 6 0 0

8 rows selected.

SYSTEM>
---------- Both sessions SIDs 138 (Sesn_1) and 384 (Sesn_2) have the Table Object (81817) locked in Mode3, not Mode6
---------- But the Partitions (Objects 81818 and 81819) are locked in Mode6
---------- Neither is SID 138 Blocking any other session nor is SID 384 being blocked


Now we see that the Table (object 81817) is locked in Mode->3 and not Mode->6  by by *both sessions* while the respective Partitions P_100 (object 81818) and P_200 (81819) are locked in Mode->6 without session 384 (Sesn_2) waiting for a block.

Thus, as the Table itself is not locked in Mode->6, Sesn_2 is allowed a Direct Path Insert into another Partition -- only so as long as it explicitly names the Target Partition. (If Sesn_2 attempts to do a Direct Path Insert without naming a Target Partition, it will, again begin waiting on Sesn_1 without the REQUEST being evident in v$lock)

Mode 6 is Lock Table in Exclusive Mode in the first case.
In the second case, the Table is locked in Mode 3 which is Row-Exclusive, not Table level.

The "TX" is a Transaction Enqueue 

The "AE" Lock type is "Edition Enqueue" --- which we are not concerned with in this test, as it relates to Editioning (which I am not currently using) and will appear even if Editioning is not being used.


Categories: DBA Blogs

TIME part in CAST (value AS DATE)

Tom Kyte - Fri, 2022-08-12 12:26
I am trying compare dates. Following SQL returns one row <code>SELECT COUNT(1) FROM ( SELECT 1 FROM DUAL WHERE TRUNC(CAST(CAST('13-JUN-22 05.47.49.000000000 PM' AS TIMESTAMP) AS DATE)) = TO_DATE('13-JUN-22','DD-MON-YY') );</code> where as the same query without TRUNC returns zero rows <code>SELECT COUNT(1) FROM ( SELECT 1 FROM DUAL WHERE (CAST(CAST('13-JUN-22 05.47.49.000000000 PM' AS TIMESTAMP) AS DATE)) = TO_DATE('13-JUN-22','DD-MON-YY') );</code> I believe (maybe I am wrong) that although the CAST convert the TIMESTAMP into DATE, in the first SQL, nevertheless ,there is a TIME part that still remains in it, which is eliminated by the TRUNC . Is this conclusion correct and what is value of the TIME part?
Categories: DBA Blogs

Resume failed rman duplication

Tom Kyte - Fri, 2022-08-12 12:26
I tried to duplicate source database to create physical standby database using the below command duplicate target database for standby FROM ACTIVE DATABASE USING COMPRESSED BACKUPSET SPFILE nofilenamecheck; I have a very big database size with multiple big data files. After finish 70% of database duplication, i discovered one of the big data files will take very long time to be finished. So i want to use section size to speed up the duplication using the below command duplicate target database for standby FROM ACTIVE DATABASE SECTION SIZE 500M USING COMPRESSED BACKUPSET SPFILE nofilenamecheck; My question: ============ if i cancel the running duplication script and re_run it again, i will lose all the progress done before. - It will start duplication from scratch? - The big file which reach to around 50% of duplication, it will resume or starting from scratch. regards,
Categories: DBA Blogs

Direct Path Insert into a Partitioned Table

Hemant K Chitale - Fri, 2022-08-12 09:45

 Normally a Direct Path Insert that is not committed blocks other concurrent Direct Path Inserts.  This behaviour also extends to Partitioned Tables.

Thus :


--------- From Session 1 : inserting only into the first Partition p_100
SQL> l
1 create table my_part_table (id_col number, data_col varchar2(25))
2 partition by range (id_col)
3 (partition p_100 values less than (101),
4 partition p_200 values less than (201),
5 partition p_300 values less than (301)
6* )
SQL>
SQL> /

Table created.

SQL>
SQL> l
1 insert /*+ APPEND */ into my_part_table
2 select rownum, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
SQL> /

50 rows created.

SQL>

---------- From Session 2 : where we know that the rows will actually be inserted into a different Partition p_200
SQL> l
1 insert /*+ APPEND */ into my_part_table
2 select rownum+101, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
SQL> /

-- Yet, Session 2 is blocked and has to wait untill Session 1 does a COMMIT or ROLLBACK


Even though the 2 sessions will be inserting into separate Partitions (i.e. separate Segments). the first session blocks  the other Direct Path Insert.

However, this blocking can be avoided by explicitly naming the target Partition

Thus :


--------- From Session 1 : inserting only into the first Partition p_100 explicitly named 
SQL> l
1 insert /*+ APPEND */ into my_part_table partition (p_100)
2 select rownum, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
SQL> /

50 rows created.

SQL>
---------- From Session 2 : inserting into the second Parition p_200 explicitlly named
SQL> l
1 insert /*+ APPEND */ into my_part_table partition (p_200)
2 select rownum+101, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
SQL> /

50 rows created.

SQL>
-------- Of course, both sessions can't requery until they COMMIT or ROLLBACK
--- note : Session 2 can query and see rows inserted by Session 1 and vice-versa, but not their own rows
SQL> select count(*) from my_part_table;
select count(*) from my_part_table
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL>
SQL> commit; -- issued by both sessions so as to be able to see their own rows as well

Commit complete.

SQL>
SQL> select count(*) from my_part_table;

COUNT(*)
----------
100

SQL>
SQL> select count(*) from my_part_table partition (p_100);

COUNT(*)
----------
50

SQL> select count(*) from my_part_table partition (p_200);

COUNT(*)
----------
50

SQL>


Thus, although there is general advice not to explicitly name a target Partition, I find this method useful if I have multiple concurrent Direct Path Inserts.




Categories: DBA Blogs

パティシエの専門学校に通う必要性と学費について

The Feature - Thu, 2022-08-11 20:54

パティシエというのはあくまでもお菓子作りをする職人を指すものであり、国家資格などの有資格者を指すものではありません。なので、普通の学校を卒業してケーキ屋へ就職するという手順でもなることができる職業です。しかし、現場では知識や経験がある方が求められている場合が多いため、一般的には専門学校などを卒業した上で就職をするというケースが多いと言えるでしょう。パティシエの学校では、お菓子作りの基礎的なことを勉強するだけではなく、お菓子の伝統なども学ぶことができますし、お菓子作りに関するさまざまな資格を取得することもできます。

なので、実際に仕事をする際にそれらの幅広い知識や資格を活かすことができると言えるでしょう。しかし気になるのは、パティシエの専門学校に通うための学費がどれぐらいかかるのかということです。一般的には、2年間学校へ通う場合だと300万~400万円程度の学費がかかるのが相場だと言われています。この金額は、他の専門学校より高いと言えますが、これは授業の中で使われる材料費が高くなるという理由があるからです。

それに大手の学校の場合だと、良い材料を使うことが重要視されることが多いため、より材料費が高くなる傾向があると言われています。また、パティシエの専門学校では、本場であるフランスへの留学がプログラムとして組まれていることも多く、その場合だと留学費用がさらにかかることになるでしょう。留学期間が2ヵ月程度の場合だと、50万円程度の費用が必要になると言われています。

Categories: APPS Blogs

パティシエの専門学校に通う必要性と学費について

Marian Crkon - Thu, 2022-08-11 20:54
パティシエというのはあくまでもお菓子作りをする職人を指すものであり、国家資格などの有資格者を指すものではありま...

PostgreSQL Cluster using repmgr

Yann Neuhaus - Thu, 2022-08-11 10:53

With this blog I describe the installation of a PostgreSQL Cluster using repmgr instead of Patroni. Repmgr was originally developed by 2ndQuadrant which is now part of EDB and EDB decided for the 2ndQuadrant tools for the future, barman as backup solution from 2ndQ survives, Bart is canceled by EDB.

At first I do a setup of three virtual machines using Rocky Linux 8.6 minimal installation including EPEL Repository for htop. Than I make sure that networking between these three VMs is working by adapting /etc/hosts. Without a functional network a cluster won’t work.

[root@repmgr-01 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
192.168.198.130 repmgr-01 repmgr-01.localdomain
192.168.198.131	repmgr-02 repmgr-02.localdomain
192.168.198.132	repmgr-03 repmgr-03.localdomain
[root@repmgr-01 ~]# ping repmgr-01
PING repmgr-01 (192.168.198.130) 56(84) bytes of data.
64 bytes from repmgr-01 (192.168.198.130): icmp_seq=1 ttl=64 time=0.032 ms
64 bytes from repmgr-01 (192.168.198.130): icmp_seq=2 ttl=64 time=0.057 ms
64 bytes from repmgr-01 (192.168.198.130): icmp_seq=3 ttl=64 time=0.183 ms
--- repmgr-01 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2074ms
rtt min/avg/max/mdev = 0.032/0.090/0.183/0.066 ms

[root@repmgr-01 ~]# ping repmgr-02
PING repmgr-02 (192.168.198.131) 56(84) bytes of data.
64 bytes from repmgr-02 (192.168.198.131): icmp_seq=1 ttl=64 time=0.550 ms
64 bytes from repmgr-02 (192.168.198.131): icmp_seq=2 ttl=64 time=0.757 ms
64 bytes from repmgr-02 (192.168.198.131): icmp_seq=3 ttl=64 time=0.838 ms
--- repmgr-02 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2074ms
rtt min/avg/max/mdev = 0.550/0.715/0.838/0.121 ms

[root@repmgr-01 ~]# ping repmgr-03
PING repmgr-03 (192.168.198.132) 56(84) bytes of data.
64 bytes from repmgr-03 (192.168.198.132): icmp_seq=1 ttl=64 time=0.541 ms
64 bytes from repmgr-03 (192.168.198.132): icmp_seq=2 ttl=64 time=0.479 ms
64 bytes from repmgr-03 (192.168.198.132): icmp_seq=3 ttl=64 time=0.439 ms
--- repmgr-03 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2058ms
rtt min/avg/max/mdev = 0.439/0.486/0.541/0.045 ms
[root@repmgr-01 ~]# 

The PostgreSQL installation is following the steps I have described at my Article at heise.de:

https://www.heise.de/ratgeber/PostgreSQL-installieren-mit-den-Community-Paketen-4877556.html

But with different packages added like repmgr and barman and of course PostgreSQL 14 latest.

The installation starts with adding the Postgresql.org repository and disabling the OS PostgreSQL modules on the two database nodes, the third machine is planned as witness for auto failover.

[root@repmgr-01 ~]# dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Last metadata expiration check: 0:20:49 ago on Thu 11 Aug 2022 11:06:40 AM CEST.
pgdg-redhat-repo-latest.noarch.rpm                                                                                                                                                                                                                         62 kB/s |  13 kB     00:00    
Dependencies resolved.
==========================================================================================================================================================================================================================================================================================
 Package                                                                   Architecture                                                    Version                                                            Repository                                                             Size
==========================================================================================================================================================================================================================================================================================
Installing:
 pgdg-redhat-repo                                                          noarch                                                          42.0-24                                                            @commandline                                                           13 k

Transaction Summary
==========================================================================================================================================================================================================================================================================================
Install  1 Package

Total size: 13 k
Installed size: 12 k
Is this ok [y/N]: y
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                                                                                                                                                  1/1 
  Installing       : pgdg-redhat-repo-42.0-24.noarch                                                                                                                                                                                                                                  1/1 
  Verifying        : pgdg-redhat-repo-42.0-24.noarch                                                                                                                                                                                                                                  1/1 

Installed:
  pgdg-redhat-repo-42.0-24.noarch                                                                                                                                                                                                                                                         

Complete!
[root@repmgr-01 ~]# dnf -qy module disable postgresql
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
[root@repmgr-01 ~]# dnf install -y postgresql14 postgresql14-server postgresql14-contrib postgresql14-libs repmgr_14 barman

I personally disable all postgresql releases within the postgresql repo file that I don’t want to use before installation, this speed up the whole installation process.

The next step is adapting the service file for a non standard PGDATA, for that I’m using systemctl edit to create a override.conf for the postgresql-14.service file.

[root@repmgr-03 ~]# systemctl edit postgresql-14.service
[Service]
Environment=PGDATA=/pgdata/14/data

Initialization should be done on the planned Leader / Master node only, by crating the Replica node later pgbasebackup is used.

[root@repmgr-01 ~]# /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK

For recurring jobs I have a set of shell scripts, one for setup CPU and Memory parameters including SSL using server self created certificate and key, required is a directory /pgdata/ssl owned by postgres user.

It configures PostgreSQL using alter system set commands within postgresql.auto.conf.

[postgres@repmgr-01 pgsql]$ cat config.sh 
##############################################
# PostgreSQL configuration                   #
# by Karsten Lenz dbi services sa 04.29.2022 #
##############################################

#!/bin/bash

echo "PostgreSQL Configuration"
echo ""

function printHelp {
  printf "Usage:\n"
  printf "${progName} [OPTION]\n\n"
  printf "Options:\n"
  printf "\t -c <Count of CPU used>\t\t\tAmount of CPU used by this system (required)\n"
  printf "\t -m <Amount of Memory>\t\t\tAmount of Memory of this system (required)\n"
  printf "\t -o <Max Connections>\t\t\tAmount of Connections of this system (default = 100))\n"
  printf "\t -v <PostgreSQL Version>\t\tMajor Release of Postgresql (default = 14)\n"
  printf "\t -h <Help>\t\t\t\tprints this help\n"
}

while getopts c:m:o:v:h option 2>/dev/null
do
  case "${option}"
  in
  c) CPU=${OPTARG};;
  m) RAM=${OPTARG};;
  o) CONNECTIONS=${OPTARG:=100};;
  v) VERSION=${OPTARG:=14};;
  h) printHelp; exit 2;;
  *) printf "Unsupported option or parameter value missing '$*'\n";
     printf "Run ${printHelp} -h to print help\n"; exit 1;;
  esac
done

# create ssl certificate and ssl key
openssl req -new -newkey rsa:4096 -nodes -x509 -subj "/C=CH/ST=DBAAS/L=ZUERICH/O=Dis/CN=www.dbi-services.com" -keyout /pgdata/ssl/pgsql.key -out /pgdata/ssl/pgsql.crt

# define parameters
rootdir=/opt/pgsql/config
cd ${rootdir}

# connections
max_connections=($CONNECTIONS)
echo max_connections : $max_connections

# memory
let shared_buffers=($RAM/4)
echo shared_buffers : $shared_buffers
let effective_cache_size=($RAM-$shared_buffers)
echo effective_cache_size : $effective_cache_size
let work_mem=($RAM*256/$CONNECTIONS)
echo work_mem : $work_mem
let maintenance_work_mem=($RAM*256/8)
echo  maintenance_work_mem : $maintenance_work_mem

# cpu
let max_worker_processes=($CPU)
echo max_worker_processes : $max_worker_processes
let max_parallel_workers=($CPU)
echo  max_parallel_workers : $max_parallel_workers
let max_parallel_workers_per_gather=($CPU/2)
echo max_parallel_workers_per_gather : $max_parallel_workers_per_gather
let max_parallel_maintenance_workers=($CPU/2)
echo max_parallel_maintenance_workers : $max_parallel_maintenance_workers

# cpu and memory configuration
psql -c "alter system set listen_addresses = '*';"
psql -c "alter system set max_connections = '$max_connections';"
psql -c "alter system set effective_cache_size = '$effective_cache_size GB';"
psql -c "alter system set shared_buffers = '$shared_buffers GB';"
psql -c "alter system set work_mem = '$work_mem MB';"
psql -c "alter system set maintenance_work_mem = '$maintenance_work_mem MB';"
psql -c "alter system set max_worker_processes = '$max_worker_processes';"
psql -c "alter system set max_parallel_workers = '$max_parallel_workers';"
psql -c "alter system set max_parallel_workers_per_gather = '$max_parallel_workers_per_gather';"
psql -c "alter system set max_parallel_maintenance_workers = '$max_parallel_maintenance_workers';"
psql -c "alter system set ssl_cert_file = '/pgdata/ssl/pgsql.crt';"
psql -c "alter system set ssl_key_file = '/pgdata/ssl/pgsql.key';"
psql -c "alter system set ssl = on;"
psql -c "alter system set ssl_ciphers = 'HIGH';"
psql -c "alter system set ssl_min_protocol_version = 'TLSv1.2';"
psql -c "alter system set shared_preload_libraries = pg_stat_statements;"
sudo service postgresql-$VERSION restart
exit 
[postgres@repmgr-01 pgsql]$ sh config.sh -h
PostgreSQL Configuration

Usage:
 [OPTION]

Options:
	 -c <Count of CPU used>			Amount of CPU used by this system (required)
	 -m <Amount of Memory>			Amount of Memory of this system (required)
	 -o <Max Connections>			Amount of Connections of this system (default = 100))
	 -v <PostgreSQL Version>		Major Release of Postgresql (default = 14)
	 -h <Help>				prints this help

[postgres@repmgr-01 pgsql]$ sh config.sh -c 2 -m 4 -o 100 -v 14
PostgreSQL Configuration

Generating a RSA private key
..........................................................++++
.................................++++
writing new private key to '/pgdata/ssl/pgsql.key'
-----
max_connections : 100
shared_buffers : 1
effective_cache_size : 3
work_mem : 10
maintenance_work_mem : 128
max_worker_processes : 2
max_parallel_workers : 2
max_parallel_workers_per_gather : 1
max_parallel_maintenance_workers : 1
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
Redirecting to /bin/systemctl restart postgresql-14.service

[postgres@repmgr-01 pgsql]$ cat /pgdata/14/data/postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
listen_addresses = '*'
max_connections = '100'
effective_cache_size = '3 GB'
shared_buffers = '1 GB'
work_mem = '10 MB'
maintenance_work_mem = '128 MB'
max_worker_processes = '2'
max_parallel_workers = '2'
max_parallel_workers_per_gather = '1'
max_parallel_maintenance_workers = '1'
ssl_cert_file = '/pgdata/ssl/pgsql.crt'
ssl_key_file = '/pgdata/ssl/pgsql.key'
ssl = 'on'
ssl_ciphers = 'HIGH'
ssl_min_protocol_version = 'TLSv1.2'
shared_preload_libraries = 'pg_stat_statements'
[postgres@repmgr-01 pgsql]$ 

The system has 2 vCPU and 4GB RAM as visible within this configuration.

For repmgr I would like to use password less authentication using pgpass, also for that I have written a small shell script, again with -h for help.

[postgres@repmgr-01 /]$ cat /opt/pgsql/config/pgpass.sh 
#!/bin/sh

########################################
#                                      #
#  pgpass setup script                 #
#                                      #
#  Author: Karsten Lenz / 2020.05.28   #
#                                      #
########################################

progName=$(basename $0)
# postgresVersion=12
domain=localdomain
# pgData=/pgdata/$postgresVersion/data
# postgresConf=/pgdata/$postgresVersion/data/postgresql.conf
postgresHome=/var/lib/pgsql
# postgresBin=/usr/pgsql-$postgresVersion/bin
pgpass=$postgresHome/.pgpass
password=PutYourPasswordHere

function printHelp() {
  printf "Usage:\n"
  printf "${progName} [OPTION]\n\n"
  printf "Options:\n"
  printf "\t -p <Primary Server>\t\t\tserver where the primary host is running on (required)\n"
  printf "\t -s <Secondary Server>\t\t\tserver where the secondary host is running on (required)\n"
  printf "\t -h <Help>\t\t\t\tprints this help\n"
}

while getopts p:s:h option 2>/dev/null
do
  case "${option}"
  in
  p) primServer=${OPTARG};; 
  s) secdServer=${OPTARG};;
  h) printHelp; exit 2;;
  *) printf "Unsupported option or parameter value missing '$*'\n"; 
     printf "Run ${progName} -h to print help\n"; exit 1;;
  esac
done

############ Log function ############

logFile=/tmp/pgpass_install.log

function log() {
  echo "$(date +%Y.%m.%d-%H:%M:%S) [$$]$*" | tee -a $logFile
}

if [ -f $logFile ]; then
  continue
else
  touch $logFile
  chmod -R 774 $logFile
  sleep 2
fi

#clean .pgpass
rm -f $pgpass

#set values in .pgpass
log "INFO: #host:port:database:user:password in $pgpass"
echo "#host:port:database:user:password" | tee -a $pgpass
log "INFO: Setting localhost in $pgass"
echo "localhost:5432:*:repmgr:$password" | tee -a $pgpass
log "INFO: Setting 127.0.0.1 in $pgpass"
echo "127.0.0.1:5432:*:repmgr:$password" | tee -a $pgpass
log "INFO: Setting Primary $primServer in $pgpass"
echo "$primServer.$domain:5432:*:repmgr:$password" | tee -a $pgpass
log "INFO: Setting Primary $secdServer in $pgpass"
echo "$secdServer.$domain:5432:*:repmgr:$password" | tee -a $pgpass

#set .pgpass 0600
chmod 0600 $pgpass

#export PGPASSFILE
export PGPASSFILE='/var/lib/pgsql/.pgpass'
[postgres@repmgr-01 /]$ 

[postgres@repmgr-01 /]$ sh /opt/pgsql/config/pgpass.sh -h
Usage:
pgpass.sh [OPTION]

Options:
	 -p <Primary Server>			server where the primary host is running on (required)
	 -s <Secondary Server>			server where the secondary host is running on (required)
	 -h <Help>				prints this help
[postgres@repmgr-01 /]$ 
[postgres@repmgr-01 /]$ sh /opt/pgsql/config/pgpass.sh -p 192.168.198.130 -s 192.168.198.131
2022.08.11-14:50:52 [10902]INFO: #host:port:database:user:password in /var/lib/pgsql/.pgpass
#host:port:database:user:password
2022.08.11-14:50:52 [10902]INFO: Setting localhost in 
localhost:5432:*:repmgr:PutYourPasswordHere
2022.08.11-14:50:52 [10902]INFO: Setting 127.0.0.1 in /var/lib/pgsql/.pgpass
127.0.0.1:5432:*:repmgr:PutYourPasswordHere
2022.08.11-14:50:52 [10902]INFO: Setting Primary 192.168.198.130 in /var/lib/pgsql/.pgpass
192.168.198.130.localdomain:5432:*:repmgr:PutYourPasswordHere
2022.08.11-14:50:52 [10902]INFO: Setting Primary 192.168.198.131 in /var/lib/pgsql/.pgpass
192.168.198.131.localdomain:5432:*:repmgr:PutYourPasswordHere
[postgres@repmgr-01 /]$ 

[postgres@repmgr-01 /]$ cat /var/lib/pgsql/.pgpass
#host:port:database:user:password
localhost:5432:*:repmgr:PutYourPasswordHere
127.0.0.1:5432:*:repmgr:PutYourPasswordHere
192.168.198.130.localdomain:5432:*:repmgr:PutYourPasswordHere
192.168.198.131.localdomain:5432:*:repmgr:PutYourPasswordHere
[postgres@repmgr-01 /]$ 

Now the setup of repmgr itself, also for that I have written shell scripts for recurring operation, originally the scrpts where written for a customer project for a DBaaS environment within a private cloud.

Setting up the Leader / Master node:

[postgres@repmgr-01 /]$ cat /opt/pgsql/config/repMgrMasterSetup.sh 
#!/bin/sh

########################################
#  RepMgr setup script                 #
#  Rework: Karsten Lenz / 2022.08.11   #
########################################

progName=$(basename $0)
# postgresVersion=14
domain=localdomain
# repmgr_conf=/etc/repmgr/$postgresVersion/repmgr.conf
# pgData=/pgdata/$postgresVersion/data
# postgresConf=/pgdata/$postgresVersion/data/postgresql.conf
# postgresHome=/var/lib/pgsql/$postgresVersion
# postgresBin=/usr/pgsql-$postgresVersion/bin
password=PutYourPasswordHere

function printHelp() {
  printf "Usage:\n"
  printf "${progName} [OPTION]\n\n"
  printf "Options:\n"
  printf "\t -p <Primary Server>\t\t\thost where the primary server is running on (required)\n"
  printf "\t -s <Standby Server>\t\t\thost where the standby server is running on (required)\n"
  printf "\t -v <PostgreSQL Major Release>\t\tMajor Release Number default 14 (required)\n"
  printf "\t -h <Help>\t\t\t\tprints this help\n"
}

while getopts c:p:s:v:h option 2>/dev/null
do
  case "${option}"
  in
  p) primServer=${OPTARG};;
  s) secdServer=${OPTARG};;
  v) postgresVersion=${OPTARG:=14};;
  h) printHelp; exit 2;;
  *) printf "Unsupported option or parameter value missing '$*'\n"; 
     printf "Run ${progName} -h to print help\n"; exit 1;;
  esac
done

### Building Variables according to inputs ###
repmgr_conf=/etc/repmgr/$postgresVersion/repmgr.conf
pgData=/pgdata/$postgresVersion/data
postgresConf=/pgdata/$postgresVersion/data/postgresql.conf
postgresHome=/var/lib/pgsql/$postgresVersion
postgresBin=/usr/pgsql-$postgresVersion/bin

rootDir=/opt/pgsql

############ Log function ############

logFile=/tmp/repMaster_install.log

function log() {
  echo "$(date +%Y.%m.%d-%H:%M:%S) [$$]$*" | tee -a $logFile
}

if [ -f $logFile ]; then
  continue
else
  touch $logFile
  chmod -R 774 $logFile
  sleep 2
fi

############ MAIN ############
psql -c "alter system set max_replication_slots = 10;"
psql -c "alter system set archive_mode = 'on';"
psql -c "alter system set archive_command = '/bin/true';"
psql -c "alter system set wal_level = 'replica';"
psql -c "alter system set max_wal_senders = 2;"
psql -c "create user repmgr with superuser"
log "INFO: create user repmgr with superuser"
psql -c "alter user repmgr with password '$password'"
log "INFO: alter user repmgr set password"

$postgresBin/createdb repmgrdb -O repmgr
log "INFO: Create database repmgrdb with owner repmgr"

$postgresBin/pg_ctl reload -D $pgData -W -s
if [ $? == 0 ]; then
  log "INFO: Reloading postgres returned $?"
else
  log "ERROR: Reloading postgres returned $?"
  exit 8
fi

> $repmgr_conf
#log "INFO: Setting cluster=$repCluster in $repmgr_conf"
#echo "cluster=$repCluster" | tee -a $repmgr_conf
log "INFO: Setting node_id=1 in $repmgr_conf"
echo "node_id=1" | tee -a $repmgr_conf
log "INFO: Setting node_name=$primServer in $repmgr_conf"
echo "node_name=$primServer" | tee -a $repmgr_conf
log "INFO: Setting conninfo='host=$primServer.$domain user=repmgr dbname=repmgrdb' in $repmgr_conf"
echo "conninfo='host=$primServer.$domain user=repmgr dbname=repmgrdb'" | tee -a $repmgr_conf
log "INFO: Setting use_replication_slots=true"
echo "use_replication_slots=true" | tee -a $repmgr_conf
log "INFO: Setting data_directory='$pgData' in $repmgr_conf"
echo "data_directory='$pgData'" | tee -a $repmgr_conf

#/usr/psql-14/bin repmgrdb repmgr <<EOF

psql -c "ALTER USER repmgr SET search_path TO repmgr, public;"
log "INFO: ALTER USER repmgr SET search_path TO repmgr, public;"

$postgresBin/repmgr -f $repmgr_conf -F master register
if [ $? == 0 ]; then
  log "INFO: Registering master returned $?"
else
  log "ERROR: Registering master returned $?"
  exit 8
fi

echo "setup of primary successfully completed"
[postgres@repmgr-01 /]$ 

postgres@repmgr-01 /]$ sh /opt/pgsql/config/repMgrMasterSetup.sh -p repmgr-01 -s repmgr-02 -v 14
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
ALTER SYSTEM
2022.08.11-16:06:28 [14145]INFO: create user repmgr with superuser
ALTER ROLE
2022.08.11-16:06:28 [14145]INFO: alter user repmgr set password
2022.08.11-16:06:28 [14145]INFO: Create database repmgrdb with owner repmgr
2022.08.11-16:06:28 [14145]INFO: Reloading postgres returned 0
2022.08.11-16:06:28 [14145]INFO: Setting node_id=1 in /etc/repmgr/14/repmgr.conf
node_id=1
2022.08.11-16:06:28 [14145]INFO: Setting node_name=repmgr-01 in /etc/repmgr/14/repmgr.conf
node_name=repmgr-01
2022.08.11-16:06:28 [14145]INFO: Setting conninfo='host=repmgr-01.localdomain user=repmgr dbname=repmgrdb' in /etc/repmgr/14/repmgr.conf
conninfo='host=repmgr-01.localdomain user=repmgr dbname=repmgrdb'
2022.08.11-16:06:28 [14145]INFO: Setting use_replication_slots=true
use_replication_slots=true
2022.08.11-16:06:28 [14145]INFO: Setting data_directory='/pgdata/14/data' in /etc/repmgr/14/repmgr.conf
data_directory='/pgdata/14/data'
ALTER ROLE
2022.08.11-16:06:28 [14145]INFO: ALTER USER repmgr SET search_path TO repmgr, public;
INFO: connecting to primary database...
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
NOTICE: primary node record (ID: 1) registered
2022.08.11-16:06:28 [14145]INFO: Registering master returned 0
setup of primary successfully completed
postgres@repmgr-01 /]$

And now the master is running.

[postgres@repmgr-01 /]$ /usr/pgsql-14/bin/repmgr cluster show
 ID | Name      | Role    | Status    | Upstream | Location | Priority | Timeline | Connection string                                     
----+-----------+---------+-----------+----------+----------+----------+----------+--------------------------------------------------------
 1  | repmgr-01 | primary | * running |          | default  | 100      | 1        | host=repmgr-01.localdomain user=repmgr dbname=repmgrdb
[postgres@repmgr-01 /]$

For the replica we need to copy the psql.crt and psql.key using scp to /pgdata/ssl on the replica, after that we can use a repMgrSteupStandby.sh script to attach the replica to the leader.

[postgres@repmgr-02 /]$ cat /opt/pgsql/config/repMgrStanbySetup.sh 
#!/bin/sh

#################################################
#  RepMgr Standby setup script                  #
#  Author: Karsten Lenz dbi-services 2022.08.11 #
#################################################

progName=$(basename $0)
# postgresVersion=14
domain=localdomain
# repmgr_conf=/etc/repmgr/$postgresVersion/repmgr.conf
# pgData=/pgdata/$postgresVersion/data
# postgresConf=/pgdata/$postgresVersion/data/postgresql.conf
# postgresHome=/var/lib/pgsql/$postgresVersion
# postgresBin=/usr/pgsql-$postgresVersion/bin
password=PutYourPasswordHere

function printHelp() {
  printf "Usage:\n"
  printf "${progName} [OPTION]\n\n"
  printf "Options:\n"
  printf "\t -c <Container Name>\t\t\tname of the container/cluster (required)\n"
  printf "\t -p <Primary Server>\t\t\thost where the primary server is running on (required)\n"
  printf "\t -s <Standby Server>\t\t\thost where the standby server is running on (required)\n"
  printf "\t -v <PostgreSQL Major Release>\t\tMajor Release Number 14 default (required)\n"
  printf "\t -h <Help>\t\t\t\tprints this help\n"
}

while getopts c:p:s:v:h option 2>/dev/null
do
  case "${option}"
  in
  c) container=${OPTARG};;
  p) primServer=${OPTARG};;
  s) secdServer=${OPTARG};;
  v) postgresVersion=${OPTARG:=14};;
  h) printHelp; exit 2;;
  *) printf "Unsupported option or parameter value missing '$*'\n"; 
     printf "Run ${progName} -h to print help\n"; exit 1;;
  esac
done

### Building Definitions according to inputs ###
repmgr_conf=/etc/repmgr/$postgresVersion/repmgr.conf
pgData=/pgdata/$postgresVersion/data
postgresConf=/pgdata/$postgresVersion/data/postgresql.conf
postgresHome=/var/lib/pgsql/$postgresVersion
postgresBin=/usr/pgsql-$postgresVersion/bin

rootDir=/opt/pgsql

############ Log function ############

logFile=/tmp/repSecondary_install.log

function log() {
  echo "$(date +%Y.%m.%d-%H:%M:%S) [$$]$*" | tee -a $logFile
}

if [ -f $logFile ]; then
  continue
else
  touch $logFile
  chmod -R 774 $logFile
  sleep 2
fi

############ MAIN ############
# change cert and key file via alter system set command
# not necessary - will be copied with base dump??
#psql -c "alter system set ssl_cert_file = '/pgdata/security/ssl/${container}.pem'; "
#psql -c "alter system set ssl_key_file = '/pgdata/security/ssl/${container}.key'; "

>$repmgr_conf

log "INFO: Setting node_id=2 in $repmgr_conf"
echo "node_id=2" | tee -a $repmgr_conf
log "INFO: Setting node_name=$secdServer in $repmgr_conf"
echo "node_name=$secdServer" | tee -a $repmgr_conf
log "INFO: Setting conninfo='host=$secdServer.$domain user=repmgr dbname=repmgrdb' in $repmgr_conf"
echo "conninfo='host=$secdServer.$domain user=repmgr dbname=repmgrdb'" | tee -a $repmgr_conf
log "Info: Setting 'use_replication_slots=true'  in $repmgr_conf"
echo "use_replication_slots=true"  | tee -a $repmgr_conf
log "INFO: Setting data_directory='$pgData' in $repmgr_conf"
echo "data_directory='$pgData'" | tee -a $repmgr_conf

#/usr/psql-14/bin repmgrdb repmgr <<EOF

$postgresBin/repmgr -h $primServer.$domain -U repmgr -d repmgrdb -F standby clone 
if [ $? == 0 ]; then
  log "INFO: Registering standby returned $?"
else
  log "ERROR: Registering standby returned $?"
  exit 8
fi
#start postgresql
sudo systemctl start postgresql-${postgresVersion}.service

## # set path
## psql -c "ALTER USER repmgr SET search_path TO repmgr, public;"
## log "INFO: ALTER USER repmgr SET search_path TO repmgr, public;"

#register standby
$postgresBin/repmgr standby register

echo "setup of standby successfully completed"

[postgres@repmgr-02 /]$ 

The script has an help function -h to tell how it is used.

[postgres@repmgr-02 /]$ sh /opt/pgsql/config/repMgrStanbySetup.sh -h
Usage:
repMgrStanbySetup.sh [OPTION]

Options:
	 -c <Container Name>			name of the container/cluster (required)
	 -p <Primary Server>			host where the primary server is running on (required)
	 -s <Standby Server>			host where the standby server is running on (required)
	 -v <PostgreSQL Major Release>		Major Release Number 14 default (required)
	 -h <Help>				prints this help
[postgres@repmgr-02 /]$ 

OK, let it run.

[postgres@repmgr-02 data]$ sh /opt/pgsql/config/repMgrStanbySetup.sh -c Cluster-01 -p repmgr-01 -s repmgr-02 -v 14
2022.08.11-17:39:53 [13124]INFO: Setting node_id=2 in /etc/repmgr/14/repmgr.conf
node_id=2
2022.08.11-17:39:53 [13124]INFO: Setting node_name=repmgr-02 in /etc/repmgr/14/repmgr.conf
node_name=repmgr-02
2022.08.11-17:39:53 [13124]INFO: Setting conninfo='host=repmgr-02.localdomain user=repmgr dbname=repmgrdb' in /etc/repmgr/14/repmgr.conf
conninfo='host=repmgr-02.localdomain user=repmgr dbname=repmgrdb'
2022.08.11-17:39:53 [13124]Info: Setting 'use_replication_slots=true'  in /etc/repmgr/14/repmgr.conf
use_replication_slots=true
2022.08.11-17:39:53 [13124]INFO: Setting data_directory='/pgdata/14/data' in /etc/repmgr/14/repmgr.conf
data_directory='/pgdata/14/data'
NOTICE: destination directory "/pgdata/14/data" provided
INFO: connecting to source node
DETAIL: connection string is: host=repmgr-01.localdomain user=repmgr dbname=repmgrdb
DETAIL: current installation size is 34 MB
NOTICE: checking for available walsenders on the source node (2 required)
NOTICE: checking replication connections can be made to the source server (2 required)
WARNING: data checksums are not enabled and "wal_log_hints" is "off"
DETAIL: pg_rewind requires "wal_log_hints" to be enabled
WARNING: directory "/pgdata/14/data" exists but is not empty
NOTICE: -F/--force provided - deleting existing data directory "/pgdata/14/data"
NOTICE: starting backup (using pg_basebackup)...
HINT: this may take some time; consider using the -c/--fast-checkpoint option
INFO: executing:
  pg_basebackup -l "repmgr base backup"  -D /pgdata/14/data -h repmgr-01.localdomain -p 5432 -U repmgr -X stream -S repmgr_slot_2 
NOTICE: standby clone (using pg_basebackup) complete
NOTICE: you can now start your PostgreSQL server
HINT: for example: pg_ctl -D /pgdata/14/data start
HINT: after starting the server, you need to register this standby with "repmgr standby register"
2022.08.11-17:39:53 [13124]INFO: Registering standby returned 0
INFO: connecting to local node "repmgr-02" (ID: 2)
INFO: connecting to primary database
WARNING: --upstream-node-id not supplied, assuming upstream node is primary (node ID: 1)
INFO: standby registration complete
NOTICE: standby node "repmgr-02" (ID: 2) successfully registered
setup of standby successfully completed
[postgres@repmgr-02 data]$ 

The Cluster is up and running now.

[postgres@repmgr-01 /]$ /usr/pgsql-14/bin/repmgr cluster show
 ID | Name      | Role    | Status    | Upstream  | Location | Priority | Timeline | Connection string                                     
----+-----------+---------+-----------+-----------+----------+----------+----------+--------------------------------------------------------
 1  | repmgr-01 | primary | * running |           | default  | 100      | 1        | host=repmgr-01.localdomain user=repmgr dbname=repmgrdb
 2  | repmgr-02 | standby |   running | repmgr-01 | default  | 100      | 1        | host=repmgr-02.localdomain user=repmgr dbname=repmgrdb
[postgres@repmgr-01 /]$ 

L’article PostgreSQL Cluster using repmgr est apparu en premier sur dbi Blog.

Performance problems on a ZyWALL USG

Yann Neuhaus - Wed, 2022-08-10 16:06
ZyWALL USG 50

Are you experiencing performance problems with your Zyxel ZyWALL USG firewall? You will find in this blog an example of a real case I faced and how I solved it.

Introduction

From one day to the next, network performance deteriorated. Users first reported sporadic access problems and then these problems became permanent. The performance problems resulted in very slow access to a remote system. As shown below, the pings performed sometimes went up to more than a hundred milliseconds,

Pinging 8.8.8.8 with 32 bytes of data
Reply from 8.8.8.8: Bytes=32 time=12 ms TTL=116
Reply from 8.8.8.8: Bytes=32 time=63 ms TTL=116
Reply from 8.8.8.8: Bytes=32 time=12 ms TTL=116
Reply from 8.8.8.8: Bytes=32 time=562 ms TTL=116
Reply from 8.8.8.8: Bytes=32 time=519 ms TTL=116
Reply from 8.8.8.8: Bytes=32 time=211 ms TTL=116
Reply from 8.8.8.8: Bytes=32 time=31 ms TTL=116
Reply from 8.8.8.8: Bytes=32 time=617 ms TTL=116
Reply from 8.8.8.8: Bytes=32 time=100 ms TTL=116
Reply from 8.8.8.8: Bytes=32 time=19 ms TTL=116

Thanks to UDITIS, we were able to quickly isolate the problem and understood that it came from the firewall. In this case it was a ZyWALL USG 50 firewall.

Logging into the ZyWALL USG GUI, we quickly saw that the processor resources were being used at over 90%. This had an impact on packet processing.

Processor consumption at nearly 100% Problem analysis

Unfortunately the ZyWALL graphical interface does not give more details about the use of resources and does not allow to see what the processor or memory resources are used for. To analyze the problem, it is therefore necessary to connect to the Zywall via SSH.

As explained on the Zyxel support webpage, we can use the command line “debug system show cpu status” to see the CPU usage details. For instance, if CPU time would have been spent on “softirq” it would have mean that the CPU was occupied with traffic load. In our case the CPU is occupied with a system item (currentrly 51%) and by user (currently 47%).

login as: admin
Using keyboard-interactive authentication.
Password:
Bad terminal type: "xterm". Will assume vt100.

Router> debug system show cpu status
CPU utilization: 99 % (system: 51 %, user: 47 %, irq: 0 %, softirq 1 %)
CPU utilization (1 minute): 98 % (system: 48 %, user: 43 %, irq: 4 %, softirq 3 %)
CPU utilization (5 minute): 98 % (system: 47 %, user: 44 %, irq: 4 %, softirq 3 %)

To understand why the CPU is used by system item, we can use the command “debug system ps“. This command will help to see the different processes and the resources usage as presented below (extract).

Router> debug system ps
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
root         1  0.0  0.0    208   100 ?        Ss   Aug05   0:02 ini
root         2  0.0  0.0      0     0 ?        S<   Aug05   1:01 [kthreadd]
root       816  0.3  0.5   8464  1240 ?        Ss   Aug05   5:20 /usr/sbin/zylogd
root       820  0.0  0.1   1104   444 ?        Ss   Aug05   0:05 /usr/sbin/syslog-ng -f /var/zyxel/syslog-ng/syslog-ng.conf
root      1527  0.0  0.1   3760   404 ?        S    Aug05   0:00 /usr/sbin/pcap_monitor
root      1533  0.0  0.1   8920   340 ?        Ss   Aug05   0:00 /usr/sbin/vsd
root      1536  0.0  0.1   2588   276 ?        S<   Aug05   0:00 /bin/zyshd_wd
root      1540  0.3  4.8  54304 11708 ?        S    Aug05   5:00 /bin/zyshd
root      1696  0.0  0.2   7068   664 ?        Ss   Aug05   0:00 /usr/sbin/xinetd -filelog /tmp/xinetd.log -stayalive -reuse -pidfile /var/run/xinetd.pid
root      1807  0.0  1.0  50108  2456 ?        Ss   Aug05   0:00 /usr/sbin/radiusd -d /var/zyxel/raddb
root      2297  0.0  0.1   2704   408 ?        S<   Aug05   0:00 /sbin/resd
root      2303  0.0  0.8  35436  1944 ?        Ss   Aug05   0:04 /usr/sbin/contfltd
root      2382  0.0  0.0   2572   196 ?        Ss   Aug05   0:00 /sbin/lavd
root      2383  0.0  1.1  15836  2768 ?        S<   Aug05   0:00 /sbin/decomp_server
root      2397  0.0  0.2  16212   632 ?        Ss   Aug05   0:00 /usr/sbin/fauthd
root      2412  0.0  0.1   2772   320 ?        S<   Aug05   0:00 /sbin/wdtd
...
...
nobody   12843  0.0  1.5  41860  3804 ?        S    Aug05   0:00 /usr/local/apache/bin/httpd -f /usr/local/zyxel-gui/httpd.conf -k start -DSSL
nobody   12851  0.0  1.5  41860  3688 ?        S    Aug05   0:00 /usr/local/apache/bin/httpd -f /usr/local/zyxel-gui/httpd.conf -k start -DSSL
nobody   12852  0.0  1.6  44004  4108 ?        S    Aug05   0:00 /usr/local/apache/bin/httpd -f /usr/local/zyxel-gui/httpd.conf -k start -DSSL
root     20680 91.9  1.2  26976  3076 ?        R    10:18   4:31 /usr/local/sbin/snmpd udp:161,udp6:161 -c /var/zyxel/snmpd.conf -p /var/run/snmpd.pid
...
...

As you can see, the SNMP process is using 91.9% of CPU usage. SNMP monitoring can be used to collect information from your USG. If you do not need it you can simply deactivate it through the graphical interface. In addition, it looks to be a good practice on the USG when not using this feature. In order to deactivate the SNMP functionality just go in Configuration section and then in System > SNMP as shown below:

How to deactivate SNMP

Once SNMP deactivated, Processor usages are freed up.

Router> debug system show cpu status
CPU utilization: 12 % (system: 5 %, user: 2 %, irq: 3 %, softirq 2 %)
CPU utilization (1 minute): 78 % (system: 39 %, user: 33 %, irq: 3 %, softirq 3 %)
CPU utilization (5 minute): 92 % (system: 45 %, user: 41 %, irq: 3 %, softirq 3 %)

The pings are now much more stable and there are no more spikes as we saw at the beginning.

Pinging 8.8.8.8 with 32 bytes of data
Reply from 8.8.8.8: Bytes=32 time=12 ms TTL=116
Reply from 8.8.8.8: Bytes=32 time=11 ms TTL=116
Reply from 8.8.8.8: Bytes=32 time=12 ms TTL=116
Reply from 8.8.8.8: Bytes=32 time=11 ms TTL=116
Reply from 8.8.8.8: Bytes=32 time=12 ms TTL=116
...
...
Reply from 8.8.8.8: Bytes=32 time=12 ms TTL=116
Reply from 8.8.8.8: Bytes=32 time=12 ms TTL=116
Reply from 8.8.8.8: Bytes=32 time=12 ms TTL=116
Reply from 8.8.8.8: Bytes=32 time=12 ms TTL=116
Reply from 8.8.8.8: Bytes=32 time=12 ms TTL=116
Conclusion

In the event of a performance problem with a ZyWALL USG, the command line tool provided by ZyXEL allows you to understand what resources are being monopolized by. Once you have identified what was consuming the resources, in our case the SNMP process, you can either stop it or go further in the debugging process.

L’article Performance problems on a ZyWALL USG est apparu en premier sur dbi Blog.

Pickleball 002 – Einstieg ins Doppel

The Oracle Instructor - Wed, 2022-08-10 09:09

Doppel ist sicherlich die beliebteste Variante in der Pickleball gespielt wird – viele spielen gar keine Einzel. Dieser kurze Artikel hilft vielleicht beim Einstieg. Das Beste vorweg: Pickleball im Doppel ist so ziemlich die inklusivste Form von Sport die man sich denken kann. Männer können gegen Frauen antreten, die ältere Generation gegen Jüngere, alte Hasen gegen Neueinsteiger. In praktisch jeder möglichen Kombination haben trotzdem alle ihren Spaß auf dem Feld und kommen auf ihre Kosten. Die Community ist gegenüber Neuankömmlingen sehr aufgeschlossen und wertschätzend. Das und die Inklusivität sind nach meiner Meinung auch die Hauptgründe, warum Pickleball so rapide an Popularität gewinnt.

Pickleball wird vorn entschieden

Im Tennis bin ich zwar eher ein Grundlinienspieler, aber das ist beim Pickleball nicht erfolgversprechend. Das kurze Spiel in der Nicht-Volley-Zone (NVZ) – auch Dinking genannt – und Volleys sind hier meistens spielentscheidend. Daher sollte man besonders Dinking und Volleys üben.

Üben für Ballsicherheit und Konsistenz

Übung macht den Pickleball-Meister, denn die meisten Spiele werden nicht so sehr gewonnen sondern durch unerzwungene Fehler verloren. Ballsicherheit ist Trumpf, natürlich auch und gerade im Doppel, wo man seinem Partner nicht gern viele unforced errors zumuten möchte. Eine gute Praxis ist es, vor dem eigentlichen Spiel zum Aufwärmen ein Kurz-Kurz-Spiel an der NVZ zu machen: Aufschlag diagonal, dann müssen erst mindestens 5 Dinks in die NVZ erfolgen, bevor der Ball freigegeben wird. Oder etwa, man muss erst 7 Volleys in Folge schaffen, bevor das Spiel anfängt.

Warum 0-0-2?

Die Zählweise im Doppel kann anfangs verwirrend sein. Ich hab mich jedenfalls zunächst etwas schwer getan und darum dies Video aufgenommen:

Hilfreich ist es außerdem, wenn man sich im Doppel merkt, auf welcher Seite man am Anfang gestanden hat. Stehe ich zum Beispiel anfangs auf der rechten (geraden) Seite, so werde ich zukünftig immer, wenn mein Team einen geraden Punktestand hat, rechts stehen. Also bei 0,2,4,6,8,10 für mein Team sollte ich immer rechts stehen. Bei 1,3,5,7,9,11 sollte entsprechend mein Partner auf der linken (ungeraden) Seite stehen. Als Gedächtnisstütze nehme ich Schweißbänder: Zwei wenn ich rechts anfange und eines wenn ich links anfange.

Elementare Doppel-Strategie

Die typische Verhaltensweise eines Doppel-Teams habe ich hier kurz geschildert. Natürlich gibt es noch mehr an Feinheiten, aber als Starthilfe sollte das erstmal reichen. Außerdem: So kompliziert ist Pickleball auch eigentlich nicht.

So, ich hoffe, das war hilfreich und ermutigend, um mit dem Pickleball spielen loszulegen – wir freuen uns schon darauf, euch auf dem Platz zu begegnen!

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator