рдХрд┐рдВрд╡рд╛ рдереЛрдбрд╛ рд▓рд╛рдЧреВ рдЯреЗрдЯреНрд░рд┐рд╕реЙрд▓реЙрдЬреА.
рдирд╡реАрди рд╕рд░реНрд╡ рдХрд╛рд╣реА рдЬреБрдиреЗ рд╡рд┐рд╕рд░рд▓реЗ рдЖрд╣реЗ.
рдПрдкрд┐рдЧреНрд░рд╛рдлреНрд╕.
рд╕рдорд╕реНрдпреЗрдЪреА рдирд┐рд░реНрдорд┐рддреА
AWS рдХреНрд▓рд╛рдЙрдбрд╡рд░реВрди рд╕реНрдерд╛рдирд┐рдХ Linux рд╣реЛрд╕реНрдЯрд╡рд░ рд╡рд░реНрддрдорд╛рди PostgreSQL рд▓реЙрдЧ рдлрд╛рдЗрд▓ рд╡реЗрд│реЛрд╡реЗрд│реА рдбрд╛рдЙрдирд▓реЛрдб рдХрд░рдгреЗ рдЖрд╡рд╢реНрдпрдХ рдЖрд╣реЗ. рд░рд┐рдЕрд▓ рдЯрд╛рдЗрдордордзреНрдпреЗ рдирд╛рд╣реА, рдкрд░рдВрддреБ, рдереЛрдбреНрдпрд╛ рд╡рд┐рд▓рдВрдмрд╛рдиреЗ рдЖрдкрдг рдореНрд╣рдгреВ рдХрд╛.
рд▓реЙрдЧ рдлрд╛рдЗрд▓ рдЕрдкрдбреЗрдЯ рдбрд╛рдЙрдирд▓реЛрдб рдХрд╛рд▓рд╛рд╡рдзреА 5 рдорд┐рдирд┐рдЯреЗ рдЖрд╣реЗ.
рд▓реЙрдЧ рдлрд╛рдЗрд▓, AWS рдордзреНрдпреЗ, рджрд░ рддрд╛рд╕рд╛рд▓рд╛ рдлрд┐рд░рд╡рд▓реА рдЬрд╛рддреЗ.
рд╡рд╛рдкрд░рд▓реЗрд▓реА рд╕рд╛рдзрдиреЗ
рд╣реЛрд╕реНрдЯрд╡рд░ рд▓реЙрдЧ рдлрд╛рдЗрд▓ рдЕрдкрд▓реЛрдб рдХрд░рдгреНрдпрд╛рд╕рд╛рдареА, рдПрдХ рдмреЕрд╢ рд╕реНрдХреНрд░рд┐рдкреНрдЯ рд╡рд╛рдкрд░рд▓реА рдЬрд╛рддреЗ рдЬреА AWS API рд▓рд╛ рдХреЙрд▓ рдХрд░рддреЗ "
рдорд╛рдкрджрдВрдб:
- --db-instance-identifier: AWS рдордзреНрдпреЗ рдЙрджрд╛рд╣рд░рдг рдирд╛рд╡;
- --log-file-name: рд╕рдзреНрдпрд╛ рд╡реНрдпреБрддреНрдкрдиреНрди рдХреЗрд▓реЗрд▓реНрдпрд╛ рд▓реЙрдЧ рдлрд╛рдЗрд▓рдЪреЗ рдирд╛рд╡
- --max-item: рдХрдорд╛рдВрдбрдЪреНрдпрд╛ рдЖрдЙрдЯрдкреБрдЯрдордзреНрдпреЗ рдкрд░рдд рдЖрд▓реЗрд▓реНрдпрд╛ рдЖрдпрдЯрдордЪреА рдПрдХреВрдг рд╕рдВрдЦреНрдпрд╛.рдбрд╛рдЙрдирд▓реЛрдб рдХреЗрд▓реЗрд▓реНрдпрд╛ рдлрд╛рдЗрд▓рдЪрд╛ рднрд╛рдЧ рдЖрдХрд╛рд░.
- --starting-token: рдЯреЛрдХрди рдЯреЛрдХрди рд╕реБрд░реВ рдХрд░рдд рдЖрд╣реЗ
рд╣реЛрдп, рдЖрдгрд┐ рдлрдХреНрдд - рдХрд╛рдорд╛рдЪреНрдпрд╛ рд╡реЗрд│реЗрдд рдкреНрд░рд╢рд┐рдХреНрд╖рдг рдЖрдгрд┐ рд╡рд┐рд╡рд┐рдзрддреЗрд╕рд╛рдареА рдПрдХ рдордиреЛрд░рдВрдЬрдХ рдХрд╛рд░реНрдп.
рдореА рдЕрд╕реЗ рдЧреГрд╣реАрдд рдзрд░рддреЛ рдХреА рдирд┐рддреНрдпрдирд┐рдпрдорд╛рдиреЗ рд╕рдорд╕реНрдпрд╛ рдЖрдзреАрдЪ рд╕реЛрдбрд╡рд▓реА рдЧреЗрд▓реА рдЖрд╣реЗ. рдкрдг рдЪрдЯрдХрди рдЧреБрдЧрд▓рдиреЗ рдЙрдкрд╛рдп рд╕реБрдЪрд╡рд▓реЗ рдирд╛рд╣реАрдд рдЖрдгрд┐ рдЕрдзрд┐рдХ рдЦреЛрд▓рд╛рдд рдЬрд╛рдКрди рд╢реЛрдзрдгреНрдпрд╛рдЪреА рд╡рд┐рд╢реЗрд╖ рдЗрдЪреНрдЫрд╛ рдирд╡реНрд╣рддреА. рдХреЛрдгрддреНрдпрд╛рд╣реА рдкрд░рд┐рд╕реНрдерд┐рддреАрдд, рд╣реА рдПрдХ рдЪрд╛рдВрдЧрд▓реА рдХрд╕рд░рдд рдЖрд╣реЗ.
рдХрд╛рд░реНрдпрд╛рдЪреЗ рдФрдкрдЪрд╛рд░рд┐рдХреАрдХрд░рдг
рдЕрдВрддрд┐рдо рд▓реЙрдЧ рдлрд╛рдЗрд▓ рд╡реНрд╣реЗрд░рд┐рдПрдмрд▓ рд▓рд╛рдВрдмреАрдЪреНрдпрд╛ рдУрд│реАрдВрдЪрд╛ рд╕рдВрдЪ рдЖрд╣реЗ. рдЧреНрд░рд╛рдлрд┐рдХрджреГрд╖реНрдЯреНрдпрд╛, рд▓реЙрдЧ рдлрд╛рдЗрд▓ рдпрд╛рдкреНрд░рдорд╛рдгреЗ рджрд░реНрд╢рд╡рд┐рд▓реА рдЬрд╛рдК рд╢рдХрддреЗ:
рд╣реЗ рддреБрдореНрд╣рд╛рд▓рд╛ рдЖрдзреАрдЪ рдХрд╛рд╣реАрддрд░реА рдЖрдард╡рдг рдХрд░реВрди рджреЗрдд рдЖрд╣реЗ рдХрд╛? "рдЯреЗрдЯреНрд░рд┐рд╕" рдордзреНрдпреЗ рдХрд╛рдп рдЖрд╣реЗ? рдЖрдгрд┐ рдпреЗрдереЗ рдХрд╛рдп рдЖрд╣реЗ.
рдкреБрдвреАрд▓ рдлрд╛рдЗрд▓ рдЧреНрд░рд╛рдлрд┐рдХ рдкрджреНрдзрддреАрдиреЗ рд▓реЛрдб рдХрд░рддрд╛рдирд╛ рдЙрджреНрднрд╡рдгрд╛рд▒реНрдпрд╛ рд╕рдВрднрд╛рд╡реНрдп рдкрд░реНрдпрд╛рдпрд╛рдВрдЪреЗ рдкреНрд░рддрд┐рдирд┐рдзрд┐рддреНрд╡ рдХреЗрд▓реНрдпрд╛рд╕ (рд╕рд╛рдзреЗрдкрдгрд╛рд╕рд╛рдареА, рдпрд╛ рдкреНрд░рдХрд░рдгрд╛рдд, рдУрд│реАрдВрдЪреА рд▓рд╛рдВрдмреА рд╕рдорд╛рди рдЕрд╕реВ рджреНрдпрд╛), рдЖрдореНрд╣рд╛рд▓рд╛ рдорд┐рд│реЗрд▓ рдорд╛рдирдХ рдЯреЗрдЯреНрд░рд┐рд╕ рдЖрдХреГрддреНрдпрд╛:
1) рдлрд╛рдЗрд▓ рд╕рдВрдкреВрд░реНрдгрдкрдгреЗ рдбрд╛рдЙрдирд▓реЛрдб рдХреЗрд▓реА рдЖрд╣реЗ рдЖрдгрд┐ рдЕрдВрддрд┐рдо рдЖрд╣реЗ. рднрд╛рдЧ рдЖрдХрд╛рд░ рдЕрдВрддрд┐рдо рдлрд╛рдЗрд▓ рдЖрдХрд╛рд░рд╛рдкреЗрдХреНрд╖рд╛ рдореЛрдард╛ рдЖрд╣реЗ:
2) рдлрд╛рдЗрд▓рдордзреНрдпреЗ рд╕рд╛рддрддреНрдп рдЖрд╣реЗ. рднрд╛рдЧ рдЖрдХрд╛рд░ рдЕрдВрддрд┐рдо рдлрд╛рдЗрд▓ рдЖрдХрд╛рд░рд╛рдкреЗрдХреНрд╖рд╛ рд▓рд╣рд╛рди рдЖрд╣реЗ:
3) рдлрд╛рдИрд▓ рд╣реА рдорд╛рдЧреАрд▓ рдлрд╛рдИрд▓рдЪреА рдирд┐рд░рдВрддрд░рддрд╛ рдЖрд╣реЗ рдЖрдгрд┐ рддреНрдпрд╛рдд рд╕рд╛рддрддреНрдп рдЖрд╣реЗ. рднрд╛рдЧрд╛рдЪрд╛ рдЖрдХрд╛рд░ рдЙрд░реНрд╡рд░рд┐рдд рдЕрдВрддрд┐рдо рдлрд╛рдЗрд▓рдЪреНрдпрд╛ рдЖрдХрд╛рд░рд╛рдкреЗрдХреНрд╖рд╛ рдХрдореА рдЖрд╣реЗ:
4) рдлрд╛рдИрд▓ рд╣реА рдорд╛рдЧреАрд▓ рдлрд╛рдИрд▓рдЪреА рдирд┐рд░рдВрддрд░рддрд╛ рдЖрд╣реЗ рдЖрдгрд┐ рдЕрдВрддрд┐рдо рдЖрд╣реЗ. рднрд╛рдЧрд╛рдЪрд╛ рдЖрдХрд╛рд░ рдЙрд░реНрд╡рд░рд┐рдд рдЕрдВрддрд┐рдо рдлрд╛рдЗрд▓рдЪреНрдпрд╛ рдЖрдХрд╛рд░рд╛рдкреЗрдХреНрд╖рд╛ рдореЛрдард╛ рдЖрд╣реЗ:
рдЖрдпрдд рдПрдХрддреНрд░ рдХрд░рдгреЗ рдХрд┐рдВрд╡рд╛ рдирд╡реАрди рд╕реНрддрд░рд╛рд╡рд░ рдЯреЗрдЯреНрд░рд┐рд╕ рдЦреЗрд│рдгреЗ рд╣реЗ рдХрд╛рд░реНрдп рдЖрд╣реЗ.
рд╕рдорд╕реНрдпрд╛ рд╕реЛрдбрд╡рддрд╛рдирд╛ рдЬреНрдпрд╛ рд╕рдорд╕реНрдпрд╛ рдЙрджреНрднрд╡рддрд╛рдд
1) 2 рднрд╛рдЧрд╛рдВрдЪреА рд╕реНрдЯреНрд░рд┐рдВрдЧ рдЪрд┐рдХрдЯрд╡рд╛
рд╕рд░реНрд╡рд╕рд╛рдзрд╛рд░рдгрдкрдгреЗ, рдХреЛрдгрддреАрд╣реА рд╡рд┐рд╢рд┐рд╖реНрдЯ рд╕рдорд╕реНрдпрд╛ рдирд╡реНрд╣рддреА. рдкреНрд░рд╛рд░рдВрднрд┐рдХ рдкреНрд░реЛрдЧреНрд░рд╛рдорд┐рдВрдЧ рдХреЛрд░реНрд╕рдордзреАрд▓ рдПрдХ рдорд╛рдирдХ рдХрд╛рд░реНрдп.
рдЗрд╖реНрдЯрддрдо рд╕рд░реНрд╡реНрд╣рд┐рдВрдЧ рдЖрдХрд╛рд░
рдкрдг рд╣реЗ рдереЛрдбреЗ рдЕрдзрд┐рдХ рдордиреЛрд░рдВрдЬрдХ рдЖрд╣реЗ.
рджреБрд░реНрджреИрд╡рд╛рдиреЗ, рд╕реНрдЯрд╛рд░реНрдЯ рдЪрдВрдХ рд▓реЗрдмрд▓ рдирдВрддрд░ рдСрдлрд╕реЗрдЯ рд╡рд╛рдкрд░рдгреНрдпрд╛рдЪрд╛ рдХреЛрдгрддрд╛рд╣реА рдорд╛рд░реНрдЧ рдирд╛рд╣реА:
рдкреГрд╖реНрдард╛рдВрдХрди рдХреЛрдареЗ рд╕реБрд░реВ рдХрд░рд╛рдпрдЪреЗ рд╣реЗ рдирд┐рд░реНрджрд┐рд╖реНрдЯ рдХрд░рдгреНрдпрд╛рд╕рд╛рдареА --starting-token рд╣рд╛ рдкрд░реНрдпрд╛рдп рддреБрдореНрд╣рд╛рд▓рд╛ рдЖрдзреАрдЪ рдорд╛рд╣рд┐рдд рдЖрд╣реЗ. рд╣рд╛ рдкрд░реНрдпрд╛рдп рд╕реНрдЯреНрд░рд┐рдВрдЧ рд╡реНрд╣реЕрд▓реНрдпреВ рдШреЗрддреЛ рдЬреНрдпрд╛рдЪрд╛ рдЕрд░реНрде рдЕрд╕рд╛ рд╣реЛрддреЛ рдХреА рдЬрд░ рддреБрдореНрд╣реА рдиреЗрдХреНрд╕реНрдЯ рдЯреЛрдХрди рд╕реНрдЯреНрд░рд┐рдВрдЧрд╕рдореЛрд░ рдСрдлрд╕реЗрдЯ рд╡реНрд╣реЕрд▓реНрдпреВ рдЬреЛрдбрдгреНрдпрд╛рдЪрд╛ рдкреНрд░рдпрддреНрди рдХреЗрд▓рд╛, рддрд░ рдкрд░реНрдпрд╛рдп рдСрдлрд╕реЗрдЯ рдореНрд╣рдгреВрди рд╡рд┐рдЪрд╛рд░рд╛рдд рдШреЗрддрд▓рд╛ рдЬрд╛рдгрд╛рд░ рдирд╛рд╣реА.
рдЖрдгрд┐ рдореНрд╣рдгреВрди, рддреБрдореНрд╣рд╛рд▓рд╛ рднрд╛рдЧ-рднрд╛рдЧрд╛рдВрдордзреНрдпреЗ рд╡рд╛рдЪрд╛рд╡реЗ рд▓рд╛рдЧреЗрд▓.
рдЖрдкрдг рдореЛрдареНрдпрд╛ рднрд╛рдЧрд╛рдВрдордзреНрдпреЗ рд╡рд╛рдЪрд▓реНрдпрд╛рд╕, рд╡рд╛рдЪрдирд╛рдВрдЪреА рд╕рдВрдЦреНрдпрд╛ рдХрдореАрддрдХрдореА рдЕрд╕реЗрд▓, рдкрд░рдВрддреБ рд╡реНрд╣реЙрд▓реНрдпреВрдо рдЬрд╛рд╕реНрддреАрдд рдЬрд╛рд╕реНрдд рдЕрд╕реЗрд▓.
рдЖрдкрдг рд▓рд╣рд╛рди рднрд╛рдЧрд╛рдВрдордзреНрдпреЗ рд╡рд╛рдЪрд▓реНрдпрд╛рд╕, рддреНрдпрд╛рдЙрд▓рдЯ, рд╡рд╛рдЪрдирд╛рдВрдЪреА рд╕рдВрдЦреНрдпрд╛ рдЬрд╛рд╕реНрддреАрдд рдЬрд╛рд╕реНрдд рдЕрд╕реЗрд▓, рдкрд░рдВрддреБ рд╡реНрд╣реЙрд▓реНрдпреВрдо рдХрдореАрддрдХрдореА рдЕрд╕реЗрд▓.
рдореНрд╣рдгреВрди, рд░рд╣рджрд╛рд░реА рдХрдореА рдХрд░рдгреНрдпрд╛рд╕рд╛рдареА рдЖрдгрд┐ рд╕реЛрд▓реНрдпреВрд╢рдирдЪреНрдпрд╛ рдПрдХреВрдг рд╕реМрдВрджрд░реНрдпрд╛рд╕рд╛рдареА, рдорд▓рд╛ рдХрд╛рд╣реА рдкреНрд░рдХрд╛рд░рдЪреЗ рд╕рдорд╛рдзрд╛рди рдЖрдгрд╛рд╡реЗ рд▓рд╛рдЧрд▓реЗ, рдЬреЗ рджреБрд░реНрджреИрд╡рд╛рдиреЗ, рдереЛрдбреЗрд╕реЗ рдХреНрд░реЕрдЪрд╕рд╛рд░рдЦреЗ рджрд┐рд╕рддреЗ.
рд╕реНрдкрд╖реНрдЯ рдХрд░рдгреНрдпрд╛рд╕рд╛рдареА, 2 рдореЛрдареНрдпрд╛ рдкреНрд░рдорд╛рдгрд╛рдд рд╕рд░рд▓реАрдХреГрдд рдЖрд╡реГрддреНрддреНрдпрд╛рдВрдордзреНрдпреЗ рд▓реЙрдЧ рдлрд╛рдЗрд▓ рдбрд╛рдЙрдирд▓реЛрдб рдХрд░рдгреНрдпрд╛рдЪреНрдпрд╛ рдкреНрд░рдХреНрд░рд┐рдпреЗрдЪрд╛ рд╡рд┐рдЪрд╛рд░ рдХрд░реВрдпрд╛. рджреЛрдиреНрд╣реА рдкреНрд░рдХрд░рдгрд╛рдВрдордзреНрдпреЗ рд╡рд╛рдЪрдирд╛рдВрдЪреА рд╕рдВрдЦреНрдпрд╛ рднрд╛рдЧрд╛рдЪреНрдпрд╛ рдЖрдХрд╛рд░рд╛рд╡рд░ рдЕрд╡рд▓рдВрдмреВрди рдЕрд╕рддреЗ.
1) рд▓рд╣рд╛рди рднрд╛рдЧрд╛рдВрдордзреНрдпреЗ рд▓реЛрдб рдХрд░рд╛:
2) рдореЛрдареНрдпрд╛ рднрд╛рдЧрд╛рдВрдордзреНрдпреЗ рд▓реЛрдб рдХрд░рд╛:
рдиреЗрд╣рдореАрдкреНрд░рдорд╛рдгреЗ, рдЗрд╖реНрдЯрддрдо рдЙрдкрд╛рдп рдордзреНрдпрднрд╛рдЧреА рдЖрд╣реЗ.
рднрд╛рдЧрд╛рдЪрд╛ рдЖрдХрд╛рд░ рдХрдореАрддрдХрдореА рдЖрд╣реЗ, рдкрд░рдВрддреБ рд╡рд╛рдЪрдирд╛рдЪреНрдпрд╛ рдкреНрд░рдХреНрд░рд┐рдпреЗрдд, рд╡рд╛рдЪрдирд╛рдЪреА рд╕рдВрдЦреНрдпрд╛ рдХрдореА рдХрд░рдгреНрдпрд╛рд╕рд╛рдареА рдЖрдХрд╛рд░ рд╡рд╛рдврд╡рд┐рд▓рд╛ рдЬрд╛рдК рд╢рдХрддреЛ.
рд╣реЗ рд▓рдХреНрд╖рд╛рдд рдШреЗрддрд▓реЗ рдкрд╛рд╣рд┐рдЬреЗ рд╡рд╛рдЪрд▓реЗрд▓реНрдпрд╛ рднрд╛рдЧрд╛рдЪрд╛ рдЗрд╖реНрдЯрддрдо рдЖрдХрд╛рд░ рдирд┐рд╡рдбрдгреНрдпрд╛рдЪреА рд╕рдорд╕реНрдпрд╛ рдЕрджреНрдпрд╛рдк рдкреВрд░реНрдгрдкрдгреЗ рд╕реБрдЯрд▓реЗрд▓реА рдирд╛рд╣реА рдЖрдгрд┐ рддреНрдпрд╛рд╕рд╛рдареА рд╕рдЦреЛрд▓ рдЕрднреНрдпрд╛рд╕ рдЖрдгрд┐ рд╡рд┐рд╢реНрд▓реЗрд╖рдг рдЖрд╡рд╢реНрдпрдХ рдЖрд╣реЗ. рдХрджрд╛рдЪрд┐рдд рдереЛрдбреНрдпрд╛ рд╡реЗрд│рд╛рдиреЗ.
рдЕрдВрдорд▓рдмрдЬрд╛рд╡рдгреАрдЪреЗ рд╕рд╛рдорд╛рдиреНрдп рд╡рд░реНрдгрди
рд╕реЗрд╡рд╛ рдЯреЗрдмрд▓ рд╡рд╛рдкрд░рд▓реЗ
CREATE TABLE endpoint
(
id SERIAL ,
host text
);
TABLE database
(
id SERIAL ,
тАж
last_aws_log_time text ,
last_aws_nexttoken text ,
aws_max_item_size integer
);
last_aws_log_time тАФ ╨▓╤А╨╡╨╝╨╡╨╜╨╜╨░╤П ╨╝╨╡╤В╨║╨░ ╨┐╨╛╤Б╨╗╨╡╨┤╨╜╨╡╨│╨╛ ╨╖╨░╨│╤А╤Г╨╢╨╡╨╜╨╜╨╛╨│╨╛ ╨╗╨╛╨│-╤Д╨░╨╣╨╗╨░ ╨▓ ╤Д╨╛╤А╨╝╨░╤В╨╡ YYYY-MM-DD-HH24.
last_aws_nexttoken тАФ ╤В╨╡╨║╤Б╤В╨╛╨▓╨░╤П ╨╝╨╡╤В╨║╨░ ╨┐╨╛╤Б╨╗╨╡╨┤╨╜╨╡╨╣ ╨╖╨░╨│╤А╤Г╨╢╨╡╨╜╨╜╨╛╨╣ ╨┐╨╛╤А╤Ж╨╕╨╕.
aws_max_item_size- ╤Н╨╝╨┐╨╕╤А╨╕╤З╨╡╤Б╨║╨╕╨╝ ╨┐╤Г╤В╨╡╨╝, ╨┐╨╛╨┤╨╛╨▒╤А╨░╨╜╨╜╤Л╨╣ ╨╜╨░╤З╨░╨╗╤М╨╜╤Л╨╣ ╤А╨░╨╖╨╝╨╡╤А ╨┐╨╛╤А╤Ж╨╕╨╕.
рд╕реНрдХреНрд░рд┐рдкреНрдЯрдЪрд╛ рдкреВрд░реНрдг рдордЬрдХреВрд░
рдбрд╛рдЙрдирд▓реЛрдб_aws_piece.sh
#!/bin/bash
#########################################################
# download_aws_piece.sh
# downloan piece of log from AWS
# version HABR
let min_item_size=1024
let max_item_size=1048576
let growth_factor=3
let growth_counter=1
let growth_counter_max=3
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:''STARTED'
AWS_LOG_TIME=$1
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:AWS_LOG_TIME='$AWS_LOG_TIME
database_id=$2
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:database_id='$database_id
RESULT_FILE=$3
endpoint=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE_DATABASE -A -t -c "select e.host from endpoint e join database d on e.id = d.endpoint_id where d.id = $database_id "`
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:endpoint='$endpoint
db_instance=`echo $endpoint | awk -F"." '{print toupper($1)}'`
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:db_instance='$db_instance
LOG_FILE=$RESULT_FILE'.tmp_log'
TMP_FILE=$LOG_FILE'.tmp'
TMP_MIDDLE=$LOG_FILE'.tmp_mid'
TMP_MIDDLE2=$LOG_FILE'.tmp_mid2'
current_aws_log_time=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -c "select last_aws_log_time from database where id = $database_id "`
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:current_aws_log_time='$current_aws_log_time
if [[ $current_aws_log_time != $AWS_LOG_TIME ]];
then
is_new_log='1'
if ! psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -v ON_ERROR_STOP=1 -A -t -q -c "update database set last_aws_log_time = '$AWS_LOG_TIME' where id = $database_id "
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - update database set last_aws_log_time .'
exit 1
fi
else
is_new_log='0'
fi
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:is_new_log='$is_new_log
let last_aws_max_item_size=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -c "select aws_max_item_size from database where id = $database_id "`
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: last_aws_max_item_size='$last_aws_max_item_size
let count=1
if [[ $is_new_log == '1' ]];
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: START DOWNLOADING OF NEW AWS LOG'
if ! aws rds download-db-log-file-portion
--max-items $last_aws_max_item_size
--region REGION
--db-instance-identifier $db_instance
--log-file-name error/postgresql.log.$AWS_LOG_TIME > $LOG_FILE
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - Could not get log from AWS .'
exit 2
fi
else
next_token=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -v ON_ERROR_STOP=1 -A -t -c "select last_aws_nexttoken from database where id = $database_id "`
if [[ $next_token == '' ]];
then
next_token='0'
fi
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: CONTINUE DOWNLOADING OF AWS LOG'
if ! aws rds download-db-log-file-portion
--max-items $last_aws_max_item_size
--starting-token $next_token
--region REGION
--db-instance-identifier $db_instance
--log-file-name error/postgresql.log.$AWS_LOG_TIME > $LOG_FILE
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - Could not get log from AWS .'
exit 3
fi
line_count=`cat $LOG_FILE | wc -l`
let lines=$line_count-1
tail -$lines $LOG_FILE > $TMP_MIDDLE
mv -f $TMP_MIDDLE $LOG_FILE
fi
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
grep -v NEXTTOKEN $LOG_FILE > $TMP_FILE
if [[ $next_token == '' ]];
then
cp $TMP_FILE $RESULT_FILE
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: NEXTTOKEN NOT FOUND - FINISH '
rm $LOG_FILE
rm $TMP_FILE
rm $TMP_MIDDLE
rm $TMP_MIDDLE2
exit 0
else
psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -v ON_ERROR_STOP=1 -A -t -q -c "update database set last_aws_nexttoken = '$next_token' where id = $database_id "
fi
first_str=`tail -1 $TMP_FILE`
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
head -$lines $TMP_FILE > $RESULT_FILE
###############################################
# MAIN CIRCLE
let count=2
while [[ $next_token != '' ]];
do
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: count='$count
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: START DOWNLOADING OF AWS LOG'
if ! aws rds download-db-log-file-portion
--max-items $last_aws_max_item_size
--starting-token $next_token
--region REGION
--db-instance-identifier $db_instance
--log-file-name error/postgresql.log.$AWS_LOG_TIME > $LOG_FILE
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - Could not get log from AWS .'
exit 4
fi
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
TMP_FILE=$LOG_FILE'.tmp'
grep -v NEXTTOKEN $LOG_FILE > $TMP_FILE
last_str=`head -1 $TMP_FILE`
if [[ $next_token == '' ]];
then
concat_str=$first_str$last_str
echo $concat_str >> $RESULT_FILE
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
tail -$lines $TMP_FILE >> $RESULT_FILE
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: NEXTTOKEN NOT FOUND - FINISH '
rm $LOG_FILE
rm $TMP_FILE
rm $TMP_MIDDLE
rm $TMP_MIDDLE2
exit 0
fi
if [[ $next_token != '' ]];
then
let growth_counter=$growth_counter+1
if [[ $growth_counter -gt $growth_counter_max ]];
then
let last_aws_max_item_size=$last_aws_max_item_size*$growth_factor
let growth_counter=1
fi
if [[ $last_aws_max_item_size -gt $max_item_size ]];
then
let last_aws_max_item_size=$max_item_size
fi
psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -q -c "update database set last_aws_nexttoken = '$next_token' where id = $database_id "
concat_str=$first_str$last_str
echo $concat_str >> $RESULT_FILE
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
#############################
#Get middle of file
head -$lines $TMP_FILE > $TMP_MIDDLE
line_count=`cat $TMP_MIDDLE | wc -l`
let lines=$line_count-1
tail -$lines $TMP_MIDDLE > $TMP_MIDDLE2
cat $TMP_MIDDLE2 >> $RESULT_FILE
first_str=`tail -1 $TMP_FILE`
fi
let count=$count+1
done
#
#################################################################
exit 0
рдХрд╛рд╣реА рд╕реНрдкрд╖реНрдЯреАрдХрд░рдгрд╛рдВрд╕рд╣ рд╕реНрдХреНрд░рд┐рдкреНрдЯрдЪреЗ рддреБрдХрдбреЗ:
рд╕реНрдХреНрд░рд┐рдкреНрдЯ рдЗрдирдкреБрдЯ рдкреЕрд░рд╛рдореАрдЯрд░реНрд╕:
- рд▓реЙрдЧ рдлрд╛рдЗрд▓ рдирд╛рд╡рд╛рдЪрд╛ рдЯрд╛рдЗрдорд╕реНрдЯреЕрдореНрдк YYYY-MM-DD-HH24 рдлреЙрд░рдореЕрдЯрдордзреНрдпреЗ: AWS_LOG_TIME=$1
- рдбреЗрдЯрд╛рдмреЗрд╕ ID: database_id=$2
- рд╕рдВрдХрд▓рд┐рдд рд▓реЙрдЧ рдлрд╛рдЗрд▓ рдирд╛рд╡: RESULT_FILE=$3
рд╢реЗрд╡рдЯрдЪреНрдпрд╛ рдЕрдкрд▓реЛрдб рдХреЗрд▓реЗрд▓реНрдпрд╛ рд▓реЙрдЧ рдлрд╛рдЗрд▓рдЪрд╛ рдЯрд╛рдЗрдорд╕реНрдЯреЕрдореНрдк рдорд┐рд│рд╡рд╛:
current_aws_log_time=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -c "select last_aws_log_time from database where id = $database_id "`
рд╢реЗрд╡рдЯрдЪреНрдпрд╛ рд▓реЛрдб рдХреЗрд▓реЗрд▓реНрдпрд╛ рд▓реЙрдЧ рдлрд╛рдЗрд▓рдЪрд╛ рдЯрд╛рдЗрдорд╕реНрдЯреЕрдореНрдк рдЗрдирдкреБрдЯ рдкреЕрд░рд╛рдореАрдЯрд░рд╢реА рдЬреБрд│рдд рдирд╕рд▓реНрдпрд╛рд╕, рдирд╡реАрди рд▓реЙрдЧ рдлрд╛рдЗрд▓ рд▓реЛрдб рдХреЗрд▓реА рдЬрд╛рддреЗ:
if [[ $current_aws_log_time != $AWS_LOG_TIME ]];
then
is_new_log='1'
if ! psql -h ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -v ON_ERROR_STOP=1 -A -t -c "update database set last_aws_log_time = '$AWS_LOG_TIME' where id = $database_id "
then
echo '***download_aws_piece.sh -FATAL_ERROR - update database set last_aws_log_time .'
exit 1
fi
else
is_new_log='0'
fi
рдЖрдореНрд╣рд╛рд▓рд╛ рд▓реЛрдб рдХреЗрд▓реЗрд▓реНрдпрд╛ рдлрд╛рдЗрд▓рдордзреВрди рдкреБрдвреАрд▓ рдЯреЛрдХрди рд▓реЗрдмрд▓рдЪреЗ рдореВрд▓реНрдп рдорд┐рд│рддреЗ:
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
рдбрд╛рдЙрдирд▓реЛрдбрдЪреНрдпрд╛ рд╕рдорд╛рдкреНрддреАрдЪреЗ рдЪрд┐рдиреНрд╣ рдиреЗрдХреНрд╕реНрдЯрдЯреЛрдХрдирдЪреЗ рд░рд┐рдХреНрдд рдореВрд▓реНрдп рдЖрд╣реЗ.
рд▓реВрдкрдордзреНрдпреЗ, рдЖрдореНрд╣реА рдлрд╛рдИрд▓рдЪреЗ рдХрд╛рд╣реА рднрд╛рдЧ рдореЛрдЬрддреЛ, рд╡рд╛рдЯреЗрдд, рд░реЗрд╖рд╛ рдЬреЛрдбрддреЛ рдЖрдгрд┐ рднрд╛рдЧрд╛рдЪрд╛ рдЖрдХрд╛рд░ рд╡рд╛рдврд╡рддреЛ:
рдореБрдЦреНрдп рд▓реВрдк
# MAIN CIRCLE
let count=2
while [[ $next_token != '' ]];
do
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: count='$count
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: START DOWNLOADING OF AWS LOG'
if ! aws rds download-db-log-file-portion
--max-items $last_aws_max_item_size
--starting-token $next_token
--region REGION
--db-instance-identifier $db_instance
--log-file-name error/postgresql.log.$AWS_LOG_TIME > $LOG_FILE
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - Could not get log from AWS .'
exit 4
fi
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
TMP_FILE=$LOG_FILE'.tmp'
grep -v NEXTTOKEN $LOG_FILE > $TMP_FILE
last_str=`head -1 $TMP_FILE`
if [[ $next_token == '' ]];
then
concat_str=$first_str$last_str
echo $concat_str >> $RESULT_FILE
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
tail -$lines $TMP_FILE >> $RESULT_FILE
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: NEXTTOKEN NOT FOUND - FINISH '
rm $LOG_FILE
rm $TMP_FILE
rm $TMP_MIDDLE
rm $TMP_MIDDLE2
exit 0
fi
if [[ $next_token != '' ]];
then
let growth_counter=$growth_counter+1
if [[ $growth_counter -gt $growth_counter_max ]];
then
let last_aws_max_item_size=$last_aws_max_item_size*$growth_factor
let growth_counter=1
fi
if [[ $last_aws_max_item_size -gt $max_item_size ]];
then
let last_aws_max_item_size=$max_item_size
fi
psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -q -c "update database set last_aws_nexttoken = '$next_token' where id = $database_id "
concat_str=$first_str$last_str
echo $concat_str >> $RESULT_FILE
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
#############################
#Get middle of file
head -$lines $TMP_FILE > $TMP_MIDDLE
line_count=`cat $TMP_MIDDLE | wc -l`
let lines=$line_count-1
tail -$lines $TMP_MIDDLE > $TMP_MIDDLE2
cat $TMP_MIDDLE2 >> $RESULT_FILE
first_str=`tail -1 $TMP_FILE`
fi
let count=$count+1
done
рдкреБрдвреЗ рдХрд╛рдп?
рддрд░, рдкрд╣рд┐рд▓реЗ рдордзреНрдпрд╡рд░реНрддреА рдХрд╛рд░реНрдп - "рдХреНрд▓рд╛рдЙрдбрд╡рд░реВрди рд▓реЙрдЧ рдлрд╛рдЗрд▓ рдбрд╛рдЙрдирд▓реЛрдб рдХрд░рд╛" рд╕реЛрдбрд╡рд▓реЗ рдЖрд╣реЗ. рдбрд╛рдЙрдирд▓реЛрдб рдХреЗрд▓реЗрд▓реНрдпрд╛ рд▓реЙрдЧрдЪреЗ рдХрд╛рдп рдХрд░рд╛рд╡реЗ?
рдкреНрд░рдердо рддреБрдореНрд╣рд╛рд▓рд╛ рд▓реЙрдЧ рдлрд╛рдЗрд▓рдЪреЗ рд╡рд┐рд╢реНрд▓реЗрд╖рдг рдХрд░рдгреЗ рдЖрдгрд┐ рддреНрдпрд╛рддреВрди рд╡рд╛рд╕реНрддрд╡рд┐рдХ рд╡рд┐рдирдВрддреНрдпрд╛ рдХрд╛рдврдгреЗ рдЖрд╡рд╢реНрдпрдХ рдЖрд╣реЗ.
рдХрд╛рдо рдлрд╛рд░ рдЕрд╡рдШрдб рдирд╛рд╣реА. рд╕рд░реНрд╡рд╛рдд рд╕реЛрдкреА рдмреЕрд╢-рд╕реНрдХреНрд░рд┐рдкреНрдЯ рдЪрд╛рдВрдЧрд▓реА рдЖрд╣реЗ.
upload_log_query.sh
#!/bin/bash
#########################################################
# upload_log_query.sh
# Upload table table from dowloaded aws file
# version HABR
###########################################################
echo 'TIMESTAMP:'$(date +%c)' Upload log_query table '
source_file=$1
echo 'source_file='$source_file
database_id=$2
echo 'database_id='$database_id
beginer=' '
first_line='1'
let "line_count=0"
sql_line=' '
sql_flag=' '
space=' '
cat $source_file | while read line
do
line="$space$line"
if [[ $first_line == "1" ]]; then
beginer=`echo $line | awk -F" " '{ print $1}' `
first_line='0'
fi
current_beginer=`echo $line | awk -F" " '{ print $1}' `
if [[ $current_beginer == $beginer ]]; then
if [[ $sql_flag == '1' ]]; then
sql_flag='0'
log_date=`echo $sql_line | awk -F" " '{ print $1}' `
log_time=`echo $sql_line | awk -F" " '{ print $2}' `
duration=`echo $sql_line | awk -F" " '{ print $5}' `
#replace ' to ''
sql_modline=`echo "$sql_line" | sed 's/'''/''''''/g'`
sql_line=' '
################
#PROCESSING OF THE SQL-SELECT IS HERE
if ! psql -h ENDPOINT.rds.amazonaws.com -U USER -d DATABASE -v ON_ERROR_STOP=1 -A -t -c "select log_query('$ip_port',$database_id , '$log_date' , '$log_time' , '$duration' , '$sql_modline' )"
then
echo 'FATAL_ERROR - log_query '
exit 1
fi
################
fi #if [[ $sql_flag == '1' ]]; then
let "line_count=line_count+1"
check=`echo $line | awk -F" " '{ print $8}' `
check_sql=${check^^}
#echo 'check_sql='$check_sql
if [[ $check_sql == 'SELECT' ]]; then
sql_flag='1'
sql_line="$sql_line$line"
ip_port=`echo $sql_line | awk -F":" '{ print $4}' `
fi
else
if [[ $sql_flag == '1' ]]; then
sql_line="$sql_line$line"
fi
fi #if [[ $current_beginer == $beginer ]]; then
done
рдЖрддрд╛ рддреБрдореНрд╣реА рд▓реЙрдЧ рдлрд╛рдЗрд▓рдордзреВрди рдХрд╛рдврд▓реЗрд▓реНрдпрд╛ рдХреНрд╡реЗрд░реАрд╕рд╣ рдХрд╛рд░реНрдп рдХрд░реВ рд╢рдХрддрд╛.
рдЖрдгрд┐ рдЕрдиреЗрдХ рдЙрдкрдпреБрдХреНрдд рд╢рдХреНрдпрддрд╛ рдЖрд╣реЗрдд.
рдкрд╛рд░реНрд╕ рдХреЗрд▓реЗрд▓реНрдпрд╛ рдХреНрд╡реЗрд░реА рдХреБрдареЗрддрд░реА рд╕рдВрдЧреНрд░рд╣рд┐рдд рдХреЗрд▓реНрдпрд╛ рдкрд╛рд╣рд┐рдЬреЗрдд. рдпрд╛рд╕рд╛рдареА, рд╕реЗрд╡рд╛ рд╕рд╛рд░рдгреА рд╡рд╛рдкрд░рд▓реА рдЬрд╛рддреЗ. log_query
CREATE TABLE log_query
(
id SERIAL ,
queryid bigint ,
query_md5hash text not null ,
database_id integer not null ,
timepoint timestamp without time zone not null,
duration double precision not null ,
query text not null ,
explained_plan text[],
plan_md5hash text ,
explained_plan_wo_costs text[],
plan_hash_value text ,
baseline_id integer ,
ip text ,
port text
);
ALTER TABLE log_query ADD PRIMARY KEY (id);
ALTER TABLE log_query ADD CONSTRAINT queryid_timepoint_unique_key UNIQUE (queryid, timepoint );
ALTER TABLE log_query ADD CONSTRAINT query_md5hash_timepoint_unique_key UNIQUE (query_md5hash, timepoint );
CREATE INDEX log_query_timepoint_idx ON log_query (timepoint);
CREATE INDEX log_query_queryid_idx ON log_query (queryid);
ALTER TABLE log_query ADD CONSTRAINT database_id_fk FOREIGN KEY (database_id) REFERENCES database (id) ON DELETE CASCADE ;
рд╡рд┐рд╢реНрд▓реЗрд╖рд┐рдд рд╡рд┐рдирдВрддреАрд╡рд░ рдкреНрд░рдХреНрд░рд┐рдпрд╛ рдХреЗрд▓реА рдЬрд╛рддреЗ plpgsql рдХрд╛рд░реНрдпреЗ "log_query┬╗.
log_query.sql
--log_query.sql
--verison HABR
CREATE OR REPLACE FUNCTION log_query( ip_port text ,log_database_id integer , log_date text , log_time text , duration text , sql_line text ) RETURNS boolean AS $$
DECLARE
result boolean ;
log_timepoint timestamp without time zone ;
log_duration double precision ;
pos integer ;
log_query text ;
activity_string text ;
log_md5hash text ;
log_explain_plan text[] ;
log_planhash text ;
log_plan_wo_costs text[] ;
database_rec record ;
pg_stat_query text ;
test_log_query text ;
log_query_rec record;
found_flag boolean;
pg_stat_history_rec record ;
port_start integer ;
port_end integer ;
client_ip text ;
client_port text ;
log_queryid bigint ;
log_query_text text ;
pg_stat_query_text text ;
BEGIN
result = TRUE ;
RAISE NOTICE '***log_query';
port_start = position('(' in ip_port);
port_end = position(')' in ip_port);
client_ip = substring( ip_port from 1 for port_start-1 );
client_port = substring( ip_port from port_start+1 for port_end-port_start-1 );
SELECT e.host , d.name , d.owner_pwd
INTO database_rec
FROM database d JOIN endpoint e ON e.id = d.endpoint_id
WHERE d.id = log_database_id ;
log_timepoint = to_timestamp(log_date||' '||log_time,'YYYY-MM-DD HH24-MI-SS');
log_duration = duration:: double precision;
pos = position ('SELECT' in UPPER(sql_line) );
log_query = substring( sql_line from pos for LENGTH(sql_line));
log_query = regexp_replace(log_query,' +',' ','g');
log_query = regexp_replace(log_query,';+','','g');
log_query = trim(trailing ' ' from log_query);
log_md5hash = md5( log_query::text );
--Explain execution plan--
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||database_rec.host||' dbname='||database_rec.name||' user=DATABASE password='||database_rec.owner_pwd||' '')';
log_explain_plan = ARRAY ( SELECT * FROM dblink('LINK1', 'EXPLAIN '||log_query ) AS t (plan text) );
log_plan_wo_costs = ARRAY ( SELECT * FROM dblink('LINK1', 'EXPLAIN ( COSTS FALSE ) '||log_query ) AS t (plan text) );
PERFORM dblink_disconnect('LINK1');
--------------------------
BEGIN
INSERT INTO log_query
(
query_md5hash ,
database_id ,
timepoint ,
duration ,
query ,
explained_plan ,
plan_md5hash ,
explained_plan_wo_costs ,
plan_hash_value ,
ip ,
port
)
VALUES
(
log_md5hash ,
log_database_id ,
log_timepoint ,
log_duration ,
log_query ,
log_explain_plan ,
md5(log_explain_plan::text) ,
log_plan_wo_costs ,
md5(log_plan_wo_costs::text),
client_ip ,
client_port
);
activity_string = 'New query has logged '||
' database_id = '|| log_database_id ||
' query_md5hash='||log_md5hash||
' , timepoint = '||to_char(log_timepoint,'YYYYMMDD HH24:MI:SS');
RAISE NOTICE '%',activity_string;
PERFORM pg_log( log_database_id , 'log_query' , activity_string);
EXCEPTION
WHEN unique_violation THEN
RAISE NOTICE '*** unique_violation *** query already has logged';
END;
SELECT queryid
INTO log_queryid
FROM log_query
WHERE query_md5hash = log_md5hash AND
timepoint = log_timepoint;
IF log_queryid IS NOT NULL
THEN
RAISE NOTICE 'log_query with query_md5hash = % and timepoint = % has already has a QUERYID = %',log_md5hash,log_timepoint , log_queryid ;
RETURN result;
END IF;
------------------------------------------------
RAISE NOTICE 'Update queryid';
SELECT *
INTO log_query_rec
FROM log_query
WHERE query_md5hash = log_md5hash AND timepoint = log_timepoint ;
log_query_rec.query=regexp_replace(log_query_rec.query,';+','','g');
FOR pg_stat_history_rec IN
SELECT
queryid ,
query
FROM
pg_stat_db_queries
WHERE
database_id = log_database_id AND
queryid is not null
LOOP
pg_stat_query = pg_stat_history_rec.query ;
pg_stat_query=regexp_replace(pg_stat_query,'n+',' ','g');
pg_stat_query=regexp_replace(pg_stat_query,'t+',' ','g');
pg_stat_query=regexp_replace(pg_stat_query,' +',' ','g');
pg_stat_query=regexp_replace(pg_stat_query,'$.','%','g');
log_query_text = trim(trailing ' ' from log_query_rec.query);
pg_stat_query_text = pg_stat_query;
--SELECT log_query_rec.query like pg_stat_query INTO found_flag ;
IF (log_query_text LIKE pg_stat_query_text) THEN
found_flag = TRUE ;
ELSE
found_flag = FALSE ;
END IF;
IF found_flag THEN
UPDATE log_query SET queryid = pg_stat_history_rec.queryid WHERE query_md5hash = log_md5hash AND timepoint = log_timepoint ;
activity_string = ' updated queryid = '||pg_stat_history_rec.queryid||
' for log_query with id = '||log_query_rec.id
;
RAISE NOTICE '%',activity_string;
EXIT ;
END IF ;
END LOOP ;
RETURN result ;
END
$$ LANGUAGE plpgsql;
рдкреНрд░рдХреНрд░рд┐рдпрд╛ рдХрд░рддрд╛рдирд╛, рд╕реЗрд╡рд╛ рд╕рд╛рд░рдгреА рд╡рд╛рдкрд░рд▓реА рдЬрд╛рддреЗ pg_stat_db_queriesA рдЬреНрдпрд╛рдордзреНрдпреЗ рдЯреЗрдмрд▓рдордзреАрд▓ рд╕рдзреНрдпрд╛рдЪреНрдпрд╛ рдХреНрд╡реЗрд░реАрдВрдЪрд╛ рд╕реНрдиреЕрдкрд╢реЙрдЯ рдЖрд╣реЗ pg_stat_history (рдЯреЗрдмрд▓ рд╡рд╛рдкрд░рд╛рдЪреЗ рд╡рд░реНрдгрди рдпреЗрдереЗ рдХреЗрд▓реЗ рдЖрд╣реЗ -
TABLE pg_stat_db_queries
(
database_id integer,
queryid bigint ,
query text ,
max_time double precision
);
TABLE pg_stat_history
(
тАж
database_id integer ,
тАж
queryid bigint ,
тАж
max_time double precision ,
тАж
);
рд╣реЗ рдлрдВрдХреНрд╢рди рддреБрдореНрд╣рд╛рд▓рд╛ рд▓реЙрдЧ рдлрд╛рдЗрд▓рдордзреАрд▓ рд╡рд┐рдирдВрддреНрдпрд╛рдВрдЪреА рдкреНрд░рдХреНрд░рд┐рдпрд╛ рдХрд░рдгреНрдпрд╛рд╕рд╛рдареА рдЕрдиреЗрдХ рдЙрдкрдпреБрдХреНрдд рд╡реИрд╢рд┐рд╖реНрдЯреНрдпреЗ рд▓рд╛рдЧреВ рдХрд░рдгреНрдпрд╛рд╕ рдЕрдиреБрдорддреА рджреЗрддреЗ. рдореНрд╣рдгрдЬреЗ:
рд╕рдВрдзреА #1 - рдХреНрд╡реЗрд░реА рдЕрдВрдорд▓рдмрдЬрд╛рд╡рдгреА рдЗрддрд┐рд╣рд╛рд╕
рдХрд╛рд░реНрдпрдкреНрд░рджрд░реНрд╢рди рдШрдЯрдирд╛ рд╕реБрд░реВ рдХрд░рдгреНрдпрд╛рд╕рд╛рдареА рдЦреВрдк рдЙрдкрдпреБрдХреНрдд. рдкреНрд░рдердо, рдЗрддрд┐рд╣рд╛рд╕рд╛рд╢реА рдкрд░рд┐рдЪрд┐рдд рд╡реНрд╣рд╛ - рдЖрдгрд┐ рдордВрджреА рдХрдзреА рд╕реБрд░реВ рдЭрд╛рд▓реА?
рдордЧ, рдХреНрд▓рд╛рд╕рд┐рдХреНрд╕рдиреБрд╕рд╛рд░, рдмрд╛рд╣реНрдп рдХрд╛рд░рдгреЗ рдкрд╣рд╛. рдЕрд╕реЗ рд╣реЛрдК рд╢рдХрддреЗ рдХреА рдбреЗрдЯрд╛рдмреЗрд╕ рд▓реЛрдб рдирд╛рдЯрдХреАрдпрд░рд┐рддреНрдпрд╛ рд╡рд╛рдврд▓рд╛ рдЖрд╣реЗ рдЖрдгрд┐ рд╡рд┐рд╢рд┐рд╖реНрдЯ рд╡рд┐рдирдВрддреАрдЪрд╛ рддреНрдпрд╛рдЪреНрдпрд╛рд╢реА рдХрд╛рд╣реАрд╣реА рд╕рдВрдмрдВрдз рдирд╛рд╣реА.
log_query рдЯреЗрдмрд▓рдордзреНрдпреЗ рдирд╡реАрди рдПрдВрдЯреНрд░реА рдЬреЛрдбрд╛
port_start = position('(' in ip_port);
port_end = position(')' in ip_port);
client_ip = substring( ip_port from 1 for port_start-1 );
client_port = substring( ip_port from port_start+1 for port_end-port_start-1 );
SELECT e.host , d.name , d.owner_pwd
INTO database_rec
FROM database d JOIN endpoint e ON e.id = d.endpoint_id
WHERE d.id = log_database_id ;
log_timepoint = to_timestamp(log_date||' '||log_time,'YYYY-MM-DD HH24-MI-SS');
log_duration = to_number(duration,'99999999999999999999D9999999999');
pos = position ('SELECT' in UPPER(sql_line) );
log_query = substring( sql_line from pos for LENGTH(sql_line));
log_query = regexp_replace(log_query,' +',' ','g');
log_query = regexp_replace(log_query,';+','','g');
log_query = trim(trailing ' ' from log_query);
RAISE NOTICE 'log_query=%',log_query ;
log_md5hash = md5( log_query::text );
--Explain execution plan--
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||database_rec.host||' dbname='||database_rec.name||' user=DATABASE password='||database_rec.owner_pwd||' '')';
log_explain_plan = ARRAY ( SELECT * FROM dblink('LINK1', 'EXPLAIN '||log_query ) AS t (plan text) );
log_plan_wo_costs = ARRAY ( SELECT * FROM dblink('LINK1', 'EXPLAIN ( COSTS FALSE ) '||log_query ) AS t (plan text) );
PERFORM dblink_disconnect('LINK1');
--------------------------
BEGIN
INSERT INTO log_query
(
query_md5hash ,
database_id ,
timepoint ,
duration ,
query ,
explained_plan ,
plan_md5hash ,
explained_plan_wo_costs ,
plan_hash_value ,
ip ,
port
)
VALUES
(
log_md5hash ,
log_database_id ,
log_timepoint ,
log_duration ,
log_query ,
log_explain_plan ,
md5(log_explain_plan::text) ,
log_plan_wo_costs ,
md5(log_plan_wo_costs::text),
client_ip ,
client_port
);
рд╡реИрд╢рд┐рд╖реНрдЯреНрдп #2 - рдХреНрд╡реЗрд░реА рдЕрдВрдорд▓рдмрдЬрд╛рд╡рдгреА рдпреЛрдЬрдирд╛ рдЬрддрди рдХрд░рд╛
рдпрд╛ рдЯрдкреНрдкреНрдпрд╛рд╡рд░, рдПрдХ рдЖрдХреНрд╖реЗрдк-рд╕реНрдкрд╖реНрдЯреАрдХрд░рдг-рдЯрд┐рдкреНрдкрдгреА рдЙрджреНрднрд╡реВ рд╢рдХрддреЗ:рдкрдг рдЖрдзреАрдЪ рд╕реНрд╡рдпрдВрд╕реНрдкрд╖реНрдЯреАрдХрд░рдг рдЖрд╣реЗ" рд╣реЛрдп, рддреЗ рдЖрд╣реЗ, рдкрд░рдВрддреБ рдЕрдВрдорд▓рдмрдЬрд╛рд╡рдгреА рдпреЛрдЬрдирд╛ рд╕рдорд╛рди рд▓реЙрдЧ рдлрд╛рдЗрд▓рдордзреНрдпреЗ рд╕рдВрдЧреНрд░рд╣рд┐рдд рдХреЗрд▓реА рдЕрд╕рд▓реНрдпрд╛рд╕ рдЖрдгрд┐ рдкреБрдвреАрд▓ рд╡рд┐рд╢реНрд▓реЗрд╖рдгрд╛рд╕рд╛рдареА рдЬрддрди рдХрд░рдгреНрдпрд╛рд╕рд╛рдареА, рддреБрдореНрд╣рд╛рд▓рд╛ рд▓реЙрдЧ рдлрд╛рдЗрд▓рдЪреЗ рд╡рд┐рд╢реНрд▓реЗрд╖рдг рдХрд░рд╛рд╡реЗ рд▓рд╛рдЧреЗрд▓ рддрд░ рдХрд╛рдп рдлрд╛рдпрджрд╛?
рддрдерд╛рдкрд┐, рдорд▓рд╛ рдЖрд╡рд╢реНрдпрдХ рдЖрд╣реЗ:
рдкреНрд░рдердо: рдореЙрдирд┐рдЯрд░рд┐рдВрдЧ рдбреЗрдЯрд╛рдмреЗрд╕рдЪреНрдпрд╛ рд╕рд░реНрд╡реНрд╣рд┐рд╕ рдЯреЗрдмрд▓рдордзреНрдпреЗ рдЕрдВрдорд▓рдмрдЬрд╛рд╡рдгреА рдпреЛрдЬрдирд╛ рд╕рдВрдЪрдпрд┐рдд рдХрд░рд╛;
рджреБрд╕рд░реЗ рдореНрд╣рдгрдЬреЗ: рдХреНрд╡реЗрд░реА рдЕрдВрдорд▓рдмрдЬрд╛рд╡рдгреА рдпреЛрдЬрдирд╛ рдмрджрд▓рд▓реА рдЖрд╣реЗ рд╣реЗ рддреНрд╡рд░рд┐рдд рдкрд╛рд╣рдгреНрдпрд╛рд╕рд╛рдареА рдПрдХрдореЗрдХрд╛рдВрд╢реА рдЕрдВрдорд▓рдмрдЬрд╛рд╡рдгреА рдпреЛрдЬрдирд╛рдВрдЪреА рддреБрд▓рдирд╛ рдХрд░рдгреНрдпрд╛рдд рд╕рдХреНрд╖рдо рд╣реЛрдгреНрдпрд╛рд╕рд╛рдареА.
рд╡рд┐рд╢рд┐рд╖реНрдЯ рдЕрдВрдорд▓рдмрдЬрд╛рд╡рдгреА рдкреЕрд░рд╛рдореАрдЯрд░реНрд╕рд╕рд╣ рд╡рд┐рдирдВрддреА рдЙрдкрд▓рдмреНрдз рдЖрд╣реЗ. EXPLAIN рд╡рд╛рдкрд░реВрди рддреНрдпрд╛рдЪреА рдЕрдВрдорд▓рдмрдЬрд╛рд╡рдгреА рдпреЛрдЬрдирд╛ рдорд┐рд│рд╡рдгреЗ рдЖрдгрд┐ рд╕рдВрдЧреНрд░рд╣рд┐рдд рдХрд░рдгреЗ рд╣реЗ рдкреНрд░рд╛рдердорд┐рдХ рдХрд╛рд░реНрдп рдЖрд╣реЗ.
рд╢рд┐рд╡рд╛рдп, EXPLAIN (COSTS FALSE) рдЕрднрд┐рд╡реНрдпрдХреНрддреА рд╡рд╛рдкрд░реВрди, рддреБрдореНрд╣реА рдпреЛрдЬрдиреЗрдЪреА рдЪреМрдХрдЯ рдорд┐рд│рд╡реВ рд╢рдХрддрд╛, рдЬреА рдпреЛрдЬрдиреЗрдЪреЗ рд╣реЕрд╢ рдореВрд▓реНрдп рдорд┐рд│рд╡рд┐рдгреНрдпрд╛рд╕рд╛рдареА рд╡рд╛рдкрд░рд▓реА рдЬрд╛рдИрд▓, рдЬреА рдЕрдВрдорд▓рдмрдЬрд╛рд╡рдгреА рдпреЛрдЬрдиреЗрдЪреНрдпрд╛ рдмрджрд▓ рдЗрддрд┐рд╣рд╛рд╕рд╛рдЪреНрдпрд╛ рдкреБрдвреАрд▓ рд╡рд┐рд╢реНрд▓реЗрд╖рдгрд╛рдд рдорджрдд рдХрд░реЗрд▓.
рдПрдХ рдЕрдВрдорд▓рдмрдЬрд╛рд╡рдгреА рдпреЛрдЬрдирд╛ рдЯреЗрдореНрдкрд▓реЗрдЯ рдорд┐рд│рд╡рд╛
--Explain execution plan--
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||database_rec.host||' dbname='||database_rec.name||' user=DATABASE password='||database_rec.owner_pwd||' '')';
log_explain_plan = ARRAY ( SELECT * FROM dblink('LINK1', 'EXPLAIN '||log_query ) AS t (plan text) );
log_plan_wo_costs = ARRAY ( SELECT * FROM dblink('LINK1', 'EXPLAIN ( COSTS FALSE ) '||log_query ) AS t (plan text) );
PERFORM dblink_disconnect('LINK1');
рд╕рдВрдзреА #3 - рдирд┐рд░реАрдХреНрд╖рдгрд╛рд╕рд╛рдареА рдХреНрд╡реЗрд░реА рд▓реЙрдЧ рд╡рд╛рдкрд░рдгреЗ
рдХрд╛рд░реНрдпрдкреНрд░рджрд░реНрд╢рди рдореЗрдЯреНрд░рд┐рдХреНрд╕ рд╡рд┐рдирдВрддреА рдордЬрдХреВрд░рд╛рд╕рд╛рдареА рдирд╛рд╣реА рддрд░ рддреНрдпрд╛рдЪреНрдпрд╛ рдЖрдпрдбреАрд╕рд╛рдареА рдХреЙрдиреНрдлрд┐рдЧрд░ рдХреЗрд▓реЗрд▓реЗ рдЕрд╕рд▓реНрдпрд╛рдиреЗ, рддреБрдореНрд╣рд╛рд▓рд╛ рд▓реЙрдЧ рдлрд╛рдЗрд▓рдордзреАрд▓ рд╡рд┐рдирдВрддреНрдпрд╛ рдЬреНрдпрд╛рд╕рд╛рдареА рдХрд╛рд░реНрдпрдкреНрд░рджрд░реНрд╢рди рдореЗрдЯреНрд░рд┐рдХреНрд╕ рдХреЙрдиреНрдлрд┐рдЧрд░ рдХреЗрд▓реЗ рдЖрд╣реЗрдд рддреНрдпрд╛ рд╡рд┐рдирдВрддреНрдпрд╛рдВрд╕реЛрдмрдд рдЬреЛрдбрдгреЗ рдЖрд╡рд╢реНрдпрдХ рдЖрд╣реЗ.
рдмрд░рдВ, рдХрд┐рдорд╛рди рдХрд╛рдордЧрд┐рд░реАрдЪреНрдпрд╛ рдШрдЯрдиреЗрдЪреА рдЕрдЪреВрдХ рд╡реЗрд│ рдорд┐рд│рдгреНрдпрд╛рд╕рд╛рдареА.
рдЕрд╢рд╛ рдкреНрд░рдХрд╛рд░реЗ, рдЬреЗрд╡реНрд╣рд╛ рд╡рд┐рдирдВрддреА рдЖрдпрдбреАрд╕рд╛рдареА рдХрд╛рд░реНрдпрдкреНрд░рджрд░реНрд╢рди рдШрдЯрдирд╛ рдШрдбрддреЗ, рддреЗрд╡реНрд╣рд╛ рд╡рд┐рд╢рд┐рд╖реНрдЯ рдкреЕрд░рд╛рдореАрдЯрд░ рдореВрд▓реНрдпрд╛рдВрд╕рд╣ рд╡рд┐рд╢рд┐рд╖реНрдЯ рд╡рд┐рдирдВрддреАрдЪрд╛ рд╕рдВрджрд░реНрдн рдЖрдгрд┐ рд╡рд┐рдирдВрддреАрдЪрд╛ рдЕрдЪреВрдХ рдЕрдВрдорд▓рдмрдЬрд╛рд╡рдгреА рд╡реЗрд│ рдЖрдгрд┐ рдХрд╛рд▓рд╛рд╡рдзреА рдЕрд╕реЗрд▓. рдХреЗрд╡рд│ рджреГрд╢реНрдп рд╡рд╛рдкрд░реВрди рджрд┐рд▓реЗрд▓реА рдорд╛рд╣рд┐рддреА рдорд┐рд│рд╡рд╛ pg_stat_statements - рддреЗ рдирд┐рд╖рд┐рджреНрдз рдЖрд╣реЗ.
рдХреНрд╡реЗрд░реАрдЪрд╛ queryid рд╢реЛрдзрд╛ рдЖрдгрд┐ log_query рдЯреЗрдмрд▓рдордзреАрд▓ рдПрдВрдЯреНрд░реА рдЕрдкрдбреЗрдЯ рдХрд░рд╛
SELECT *
INTO log_query_rec
FROM log_query
WHERE query_md5hash = log_md5hash AND timepoint = log_timepoint ;
log_query_rec.query=regexp_replace(log_query_rec.query,';+','','g');
FOR pg_stat_history_rec IN
SELECT
queryid ,
query
FROM
pg_stat_db_queries
WHERE
database_id = log_database_id AND
queryid is not null
LOOP
pg_stat_query = pg_stat_history_rec.query ;
pg_stat_query=regexp_replace(pg_stat_query,'n+',' ','g');
pg_stat_query=regexp_replace(pg_stat_query,'t+',' ','g');
pg_stat_query=regexp_replace(pg_stat_query,' +',' ','g');
pg_stat_query=regexp_replace(pg_stat_query,'$.','%','g');
log_query_text = trim(trailing ' ' from log_query_rec.query);
pg_stat_query_text = pg_stat_query;
--SELECT log_query_rec.query like pg_stat_query INTO found_flag ;
IF (log_query_text LIKE pg_stat_query_text) THEN
found_flag = TRUE ;
ELSE
found_flag = FALSE ;
END IF;
IF found_flag THEN
UPDATE log_query SET queryid = pg_stat_history_rec.queryid WHERE query_md5hash = log_md5hash AND timepoint = log_timepoint ;
activity_string = ' updated queryid = '||pg_stat_history_rec.queryid||
' for log_query with id = '||log_query_rec.id
;
RAISE NOTICE '%',activity_string;
EXIT ;
END IF ;
END LOOP ;
рдирдВрддрд░рдЪрд╛ рд╢рдмреНрдж
рдкрд░рд┐рдгрд╛рдореА, рд╡рд░реНрдгрди рдХреЗрд▓реЗрд▓реНрдпрд╛ рдкрджреНрдзрддреАрд▓рд╛ рддреНрдпрд╛рдЪрд╛ рдЕрдиреБрдкреНрд░рдпреЛрдЧ рд╕рд╛рдкрдбрд▓рд╛ рдЖрд╣реЗ
рдЬрд░реА, рдЕрд░реНрдерд╛рддрдЪ, рдорд╛рдЭреНрдпрд╛ рд╡реИрдпрдХреНрддрд┐рдХ рдорддреЗ, рдбрд╛рдЙрдирд▓реЛрдб рдХреЗрд▓реЗрд▓реНрдпрд╛ рднрд╛рдЧрд╛рдЪрд╛ рдЖрдХрд╛рд░ рдирд┐рд╡рдбрдгреНрдпрд╛рд╕рд╛рдареА рдЖрдгрд┐ рдмрджрд▓рдгреНрдпрд╛рд╕рд╛рдареА рдЕрд▓реНрдЧреЛрд░рд┐рджрдорд╡рд░ рдХрд╛рд░реНрдп рдХрд░рдгреЗ рдЕрджреНрдпрд╛рдк рдЖрд╡рд╢реНрдпрдХ рдЕрд╕реЗрд▓. рд╕рд░реНрд╡рд╕рд╛рдзрд╛рд░рдг рдкреНрд░рдХрд░рдгрд╛рдд рдЕрджреНрдпрд╛рдкрд╣реА рдкреНрд░рд╢реНрди рд╕реБрдЯрд▓реЗрд▓рд╛ рдирд╛рд╣реА. рд╣реЗ рдХрджрд╛рдЪрд┐рдд рдордиреЛрд░рдВрдЬрдХ рдЕрд╕реЗрд▓.
рдкрдг рддреА рдкреВрд░реНрдгрдкрдгреЗ рд╡реЗрдЧрд│реА рдХрдерд╛ рдЖрд╣реЗ...
рд╕реНрддреНрд░реЛрдд: www.habr.com