рдпрд╛ рдереЛрдбрд╝рд╛ рд╡реНрдпрд╛рд╡рд╣рд╛рд░рд┐рдХ рдЯреЗрдЯреНрд░рд┐рд╕реЛрд▓реЙрдЬреАред
рд╣рд░ рдирдИ рдЪреАрдЬрд╝ рдкреБрд░рд╛рдиреА рдЪреАрдЬрд╝ рдХреЛ рдЕрдЪреНрдЫреА рддрд░рд╣ рднреБрд▓рд╛ рджрд┐рдпрд╛ рдЬрд╛рддрд╛ рд╣реИред
рдкреБрд░рд╛рд▓реЗрдЦред
рд╕рдорд╕реНрдпрд╛ рдХрд╛ рдирд┐рд░реВрдкрдг
рдЖрдкрдХреЛ рд╕рдордп-рд╕рдордп рдкрд░ рд╡рд░реНрддрдорд╛рди PostgreSQL рд▓реЙрдЧ рдлрд╝рд╛рдЗрд▓ рдХреЛ AWS рдХреНрд▓рд╛рдЙрдб рд╕реЗ рдЕрдкрдиреЗ рд╕реНрдерд╛рдиреАрдп Linux рд╣реЛрд╕реНрдЯ рдкрд░ рдбрд╛рдЙрдирд▓реЛрдб рдХрд░рдирд╛ рд╣реЛрдЧрд╛ред рд╡рд╛рд╕реНрддрд╡рд┐рдХ рд╕рдордп рдореЗрдВ рдирд╣реАрдВ, рд▓реЗрдХрд┐рди, рдорд╛рди рд▓реАрдЬрд┐рдП, рдереЛрдбрд╝реА рджреЗрд░реА рд╕реЗред
рд▓реЙрдЧ рдлрд╝рд╛рдЗрд▓ рдЕрджреНрдпрддрди рдбрд╛рдЙрдирд▓реЛрдб рдЕрд╡рдзрд┐ 5 рдорд┐рдирдЯ рд╣реИред
AWS рдореЗрдВ рд▓реЙрдЧ рдлрд╝рд╛рдЗрд▓ рд╣рд░ рдШрдВрдЯреЗ рдШреВрдорддреА рд╣реИред
рдЙрдкрдХрд░рдгреЛрдВ рдХрд╛ рдЗрд╕реНрддреЗрдорд╛рд▓
рд▓реЙрдЧ рдлрд╝рд╛рдЗрд▓ рдХреЛ рд╣реЛрд╕реНрдЯ рдкрд░ рдбрд╛рдЙрдирд▓реЛрдб рдХрд░рдиреЗ рдХреЗ рд▓рд┐рдП, рдПрдХ рдмреИрд╢ рд╕реНрдХреНрд░рд┐рдкреНрдЯ рдХрд╛ рдЙрдкрдпреЛрдЧ рдХрд┐рдпрд╛ рдЬрд╛рддрд╛ рд╣реИ рдЬреЛ AWS API рдХреЛ рдХреЙрд▓ рдХрд░рддрд╛ рд╣реИ "
рд╡рд┐рдХрд▓реНрдк:
- тАФdb-рдЗрдВрд╕реНрдЯреЗрдВрд╕-рдкрд╣рдЪрд╛рдирдХрд░реНрддрд╛: AWS рдЗрдВрд╕реНрдЯреЗрдВрд╕ рдирд╛рдо;
- --рд▓реЙрдЧ-рдлрд╝рд╛рдЗрд▓-рдирд╛рдо: рд╡рд░реНрддрдорд╛рди рдореЗрдВ рдЬреЗрдирд░реЗрдЯ рдХреА рдЧрдИ рд▓реЙрдЧ рдлрд╝рд╛рдЗрд▓ рдХрд╛ рдирд╛рдо
- --рдЕрдзрд┐рдХрддрдо-рдЖрдЗрдЯрдо: рдХрдорд╛рдВрдб рдЖрдЙрдЯрдкреБрдЯ рдореЗрдВ рд▓реМрдЯрд╛рдП рдЧрдП рдЖрдЗрдЯрдореЛрдВ рдХреА рдХреБрд▓ рд╕рдВрдЦреНрдпрд╛редрдбрд╛рдЙрдирд▓реЛрдб рдХреА рдЧрдИ рдлрд╝рд╛рдЗрд▓ рдХрд╛ рднрд╛рдЧ рдЖрдХрд╛рд░.
- --рд╕реНрдЯрд╛рд░реНрдЯрд┐рдВрдЧ-рдЯреЛрдХрди: рдЖрд░рдВрднрд┐рдХ рдЯреЛрдХрди
рдФрд░ рдпрд╣ рд╕рд░рд▓ рд╣реИ - рдХрд╛рдо рдХреЗ рдШрдВрдЯреЛрдВ рдХреЗ рджреМрд░рд╛рди рдкреНрд░рд╢рд┐рдХреНрд╖рдг рдФрд░ рд╡рд┐рд╡рд┐рдзрддрд╛ рдХреЗ рд▓рд┐рдП рдПрдХ рджрд┐рд▓рдЪрд╕реНрдк рдХрд╛рд░реНрдпред
рдореИрдВ рдорд╛рди рд▓реВрдВрдЧрд╛ рдХрд┐ рд░реЛрдЬрдорд░реНрд░рд╛ рдХреА рдЬрд┐рдВрджрдЧреА рдХреЗ рдХрд╛рд░рдг рд╕рдорд╕реНрдпрд╛ рдкрд╣рд▓реЗ рд╣реА рд╣рд▓ рд╣реЛ рдЪреБрдХреА рд╣реИред рд▓реЗрдХрд┐рди рддреНрд╡рд░рд┐рдд Google рдиреЗ рдХреЛрдИ рд╕рдорд╛рдзрд╛рди рдирд╣реАрдВ рд╕реБрдЭрд╛рдпрд╛, рдФрд░ рдореБрдЭреЗ рдЕрдзрд┐рдХ рдЧрд╣рд░рд╛рдИ рд╕реЗ рдЦреЛрдЬ рдХрд░рдиреЗ рдХреА рдЕрдзрд┐рдХ рдЗрдЪреНрдЫрд╛ рдирд╣реАрдВ рдереАред рдХрд┐рд╕реА рднреА рддрд░рд╣, рдпрд╣ рдПрдХ рдЕрдЪреНрдЫрд╛ рд╡рд░реНрдХрдЖрдЙрдЯ рд╣реИред
рдХрд╛рд░реНрдп рдХрд╛ рдФрдкрдЪрд╛рд░рд┐рдХреАрдХрд░рдг
рдЕрдВрддрд┐рдо рд▓реЙрдЧ рдлрд╝рд╛рдЗрд▓ рдореЗрдВ рдкрд░рд┐рд╡рд░реНрддрдиреАрдп рд▓рдВрдмрд╛рдИ рдХреА рдХрдИ рдкрдВрдХреНрддрд┐рдпрд╛рдБ рд╣реЛрддреА рд╣реИрдВред рдЧреНрд░рд╛рдлрд╝рд┐рдХ рд░реВрдк рд╕реЗ, рд▓реЙрдЧ рдлрд╝рд╛рдЗрд▓ рдХреЛ рдХреБрдЫ рдЗрд╕ рддрд░рд╣ рджрд░реНрд╢рд╛рдпрд╛ рдЬрд╛ рд╕рдХрддрд╛ рд╣реИ:
рдХреНрдпрд╛ рдпрд╣ рдЖрдкрдХреЛ рдкрд╣рд▓реЗ рд╕реЗ рд╣реА рдХреБрдЫ рдпрд╛рдж рджрд┐рд▓рд╛рддрд╛ рд╣реИ? рдЯреЗрдЯреНрд░рд┐рд╕ рдХрд╛ рдЗрд╕рд╕реЗ рдХреНрдпрд╛ рд▓реЗрдирд╛-рджреЗрдирд╛ рд╣реИ? рдФрд░ рдпрд╣рд╛рдБ рдмрддрд╛рдпрд╛ рдЧрдпрд╛ рд╣реИ рдХрд┐ рдЗрд╕рдХрд╛ рдЗрд╕рд╕реЗ рдХреНрдпрд╛ рд▓реЗрдирд╛-рджреЗрдирд╛ рд╣реИред
рдпрджрд┐ рд╣рдо рдЕрдЧрд▓реА рдлрд╝рд╛рдЗрд▓ рдХреЛ рдЧреНрд░рд╛рдлрд╝рд┐рдХ рд░реВрдк рд╕реЗ рд▓реЛрдб рдХрд░рддреЗ рд╕рдордп рдЙрддреНрдкрдиреНрди рд╣реЛрдиреЗ рд╡рд╛рд▓реЗ рд╕рдВрднрд╛рд╡рд┐рдд рд╡рд┐рдХрд▓реНрдкреЛрдВ рдХреА рдХрд▓реНрдкрдирд╛ рдХрд░рддреЗ рд╣реИрдВ (рд╕рд░рд▓рддрд╛ рдХреЗ рд▓рд┐рдП, рдЗрд╕ рдорд╛рдорд▓реЗ рдореЗрдВ, рдкрдВрдХреНрддрд┐рдпреЛрдВ рдХреА рд▓рдВрдмрд╛рдИ рд╕рдорд╛рди рд╣реЛрдиреЗ рджреЗрдВ), рддреЛ рд╣рдореЗрдВ рдорд┐рд▓рддрд╛ рд╣реИ рдорд╛рдирдХ рдЯреЗрдЯреНрд░рд┐рд╕ рдЯреБрдХрдбрд╝реЗ:
1) рдлрд╝рд╛рдЗрд▓ рдкреВрд░реА рддрд░рд╣ рд╕реЗ рдбрд╛рдЙрдирд▓реЛрдб рд╣реЛ рдЧрдИ рд╣реИ рдФрд░ рдЕрдВрддрд┐рдо рд╣реИред рднрд╛рдЧ рдХрд╛ рдЖрдХрд╛рд░ рдЕрдВрддрд┐рдо рдлрд╝рд╛рдЗрд▓ рдЖрдХрд╛рд░ рд╕реЗ рдмрдбрд╝рд╛ рд╣реИ:
2) рдлрд╝рд╛рдЗрд▓ рдЬрд╛рд░реА рд╣реИ. рдЦрдВрдб рдХрд╛ рдЖрдХрд╛рд░ рдЕрдВрддрд┐рдо рдлрд╝рд╛рдЗрд▓ рдЖрдХрд╛рд░ рд╕реЗ рдЫреЛрдЯрд╛ рд╣реИ:
3) рдлрд╝рд╛рдЗрд▓ рдкрд┐рдЫрд▓реА рдлрд╝рд╛рдЗрд▓ рдХреА рдирд┐рд░рдВрддрд░рддрд╛ рд╣реИ рдФрд░ рдЗрд╕рдореЗрдВ рдирд┐рд░рдВрддрд░рддрд╛ рд╣реИред рдЦрдВрдб рдХрд╛ рдЖрдХрд╛рд░ рдЕрдВрддрд┐рдо рдлрд╝рд╛рдЗрд▓ рдХреЗ рд╢реЗрд╖ рдХреЗ рдЖрдХрд╛рд░ рд╕реЗ рдЫреЛрдЯрд╛ рд╣реИ:
4) рдлрд╝рд╛рдЗрд▓ рдкрд┐рдЫрд▓реА рдлрд╝рд╛рдЗрд▓ рдХреА рдирд┐рд░рдВрддрд░рддрд╛ рд╣реИ рдФрд░ рдЕрдВрддрд┐рдо рдлрд╝рд╛рдЗрд▓ рд╣реИред рдЦрдВрдб рдХрд╛ рдЖрдХрд╛рд░ рдЕрдВрддрд┐рдо рдлрд╝рд╛рдЗрд▓ рдХреЗ рд╢реЗрд╖ рдХреЗ рдЖрдХрд╛рд░ рд╕реЗ рдмрдбрд╝рд╛ рд╣реИ:
рдХрд╛рд░реНрдп рдПрдХ рдЖрдпрдд рдХреЛ рдЗрдХрдЯреНрдард╛ рдХрд░рдирд╛ рдпрд╛ рдирдП рд╕реНрддрд░ рдкрд░ рдЯреЗрдЯреНрд░рд┐рд╕ рдЦреЗрд▓рдирд╛ рд╣реИред
рдХрд┐рд╕реА рд╕рдорд╕реНрдпрд╛ рдХрд╛ рд╕рдорд╛рдзрд╛рди рдХрд░рддреЗ рд╕рдордп рдЙрддреНрдкрдиреНрди рд╣реЛрдиреЗ рд╡рд╛рд▓реА рд╕рдорд╕реНрдпрд╛рдПрдБ
1) 2 рдЯреБрдХрдбрд╝реЛрдВ рдХреА рдПрдХ рдбреЛрд░реА рдХреЛ рдЧреЛрдВрдж рджреЗрдВ
рд╕рд╛рдорд╛рдиреНрдп рддреМрд░ рдкрд░, рдХреЛрдИ рд╡рд┐рд╢реЗрд╖ рд╕рдорд╕реНрдпрд╛рдПрдБ рдирд╣реАрдВ рдереАрдВред рдкреНрд░рд╛рд░рдВрднрд┐рдХ рдкреНрд░реЛрдЧреНрд░рд╛рдорд┐рдВрдЧ рдкрд╛рдареНрдпрдХреНрд░рдо рд╕реЗ рдПрдХ рдорд╛рдирдХ рд╕рдорд╕реНрдпрд╛ред
рдЗрд╖реНрдЯрддрдо рд╕рд░реНрд╡рд┐рдВрдЧ рдЖрдХрд╛рд░
рд▓реЗрдХрд┐рди рдпреЗ рдереЛрдбрд╝рд╛ рдЬрд╝реНрдпрд╛рджрд╛ рджрд┐рд▓рдЪрд╕реНрдк рд╣реИ.
рджреБрд░реНрднрд╛рдЧреНрдпрд╡рд╢, рдкреНрд░рд╛рд░рдВрдн рднрд╛рдЧ рд▓реЗрдмрд▓ рдХреЗ рдмрд╛рдж рдСрдлрд╝рд╕реЗрдЯ рдХрд╛ рдЙрдкрдпреЛрдЧ рдХрд░рдиреЗ рдХрд╛ рдХреЛрдИ рддрд░реАрдХрд╛ рдирд╣реАрдВ рд╣реИ:
рдЬреИрд╕рд╛ рдХрд┐ рдЖрдк рдкрд╣рд▓реЗ рд╕реЗ рд╣реА рдЬрд╛рдирддреЗ рд╣реИрдВ рд╡рд┐рдХрд▓реНрдк -рд╕реНрдЯрд╛рд░реНрдЯрд┐рдВрдЧ-рдЯреЛрдХрди рдХрд╛ рдЙрдкрдпреЛрдЧ рдпрд╣ рдирд┐рд░реНрджрд┐рд╖реНрдЯ рдХрд░рдиреЗ рдХреЗ рд▓рд┐рдП рдХрд┐рдпрд╛ рдЬрд╛рддрд╛ рд╣реИ рдХрд┐ рдкреЗрдЬрд┐рдВрдЧ рдХрд╣рд╛рдБ рд╕реЗ рд╢реБрд░реВ рдХрд░рдиреА рд╣реИред рдпрд╣ рд╡рд┐рдХрд▓реНрдк рд╕реНрдЯреНрд░рд┐рдВрдЧ рдорд╛рди рд▓реЗрддрд╛ рд╣реИ рдЬрд┐рд╕рдХрд╛ рдЕрд░реНрде рдпрд╣ рд╣реЛрдЧрд╛ рдХрд┐ рдпрджрд┐ рдЖрдк рдиреЗрдХреНрд╕реНрдЯ рдЯреЛрдХрди рд╕реНрдЯреНрд░рд┐рдВрдЧ рдХреЗ рд╕рд╛рдордиреЗ рдСрдлрд╕реЗрдЯ рдорд╛рди рдЬреЛрдбрд╝рдиреЗ рдХрд╛ рдкреНрд░рдпрд╛рд╕ рдХрд░рддреЗ рд╣реИрдВ, рддреЛ рд╡рд┐рдХрд▓реНрдк рдХреЛ рдСрдлрд╕реЗрдЯ рдХреЗ рд░реВрдк рдореЗрдВ рдирд╣реАрдВ рдорд╛рдирд╛ рдЬрд╛рдПрдЧрд╛ред
рдФрд░ рдЗрд╕рд▓рд┐рдП, рдЖрдкрдХреЛ рдЗрд╕реЗ рдЯреБрдХрдбрд╝реЛрдВ рдореЗрдВ рдкрдврд╝рдирд╛ рд╣реЛрдЧрд╛ред
рдпрджрд┐ рдЖрдк рдмрдбрд╝реЗ рд╣рд┐рд╕реНрд╕реЗ рдореЗрдВ рдкрдврд╝рддреЗ рд╣реИрдВ, рддреЛ рдкрдврд╝рдиреЗ рдХреА рд╕рдВрдЦреНрдпрд╛ рдиреНрдпреВрдирддрдо рд╣реЛрдЧреА, рд▓реЗрдХрд┐рди рдорд╛рддреНрд░рд╛ рдЕрдзрд┐рдХрддрдо рд╣реЛрдЧреАред
рдпрджрд┐ рдЖрдк рдЫреЛрдЯреЗ-рдЫреЛрдЯреЗ рд╣рд┐рд╕реНрд╕реЛрдВ рдореЗрдВ рдкрдврд╝реЗрдВрдЧреЗ, рддреЛ рдЗрд╕рдХреЗ рд╡рд┐рдкрд░реАрдд, рдкрдврд╝рдиреЗ рдХреА рд╕рдВрдЦреНрдпрд╛ рдЕрдзрд┐рдХрддрдо рд╣реЛрдЧреА, рд▓реЗрдХрд┐рди рдорд╛рддреНрд░рд╛ рдиреНрдпреВрдирддрдо рд╣реЛрдЧреАред
рдЗрд╕рд▓рд┐рдП, рдЯреНрд░реИрдлрд╝рд┐рдХ рдХреЛ рдХрдо рдХрд░рдиреЗ рдФрд░ рд╕рдорд╛рдзрд╛рди рдХреА рд╕рдордЧреНрд░ рд╕реБрдВрджрд░рддрд╛ рдХреЗ рд▓рд┐рдП, рдореБрдЭреЗ рдХреБрдЫ рдкреНрд░рдХрд╛рд░ рдХреЗ рд╕рдорд╛рдзрд╛рди рдХреЗ рд╕рд╛рде рдЖрдирд╛ рдкрдбрд╝рд╛, рдЬреЛ рджреБрд░реНрднрд╛рдЧреНрдп рд╕реЗ, рдПрдХ рдмреИрд╕рд╛рдЦреА рдЬреИрд╕рд╛ рджрд┐рдЦрддрд╛ рд╣реИред
рдЙрджрд╛рд╣рд░рдг рдХреЗ рд▓рд┐рдП, рдЖрдЗрдП рдПрдХ рд▓реЙрдЧ рдлрд╝рд╛рдЗрд▓ рдХреЛ 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_рдЯреБрдХрдбрд╝рд╛.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
- рдбреЗрдЯрд╛рдмреЗрд╕ рдЖрдИрдбреА: рдбреЗрдЯрд╛рдмреЗрд╕_рдЖрдИрдбреА=$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
рдЕрдм рдЖрдк рд▓реЙрдЧ рдлрд╝рд╛рдЗрд▓ рд╕реЗ рдЪрдпрдирд┐рдд рдЕрдиреБрд░реЛрдз рдХреЗ рд╕рд╛рде рдХрд╛рдо рдХрд░ рд╕рдХрддреЗ рд╣реИрдВред
рдФрд░ рдХрдИ рдЙрдкрдпреЛрдЧреА рдЕрд╡рд╕рд░ рдЦреБрд▓рддреЗ рд╣реИрдВред
рдкрд╛рд░реНрд╕ рдХрд┐рдП рдЧрдП рдкреНрд░рд╢реНрдиреЛрдВ рдХреЛ рдХрд╣реАрдВ рд╕рдВрдЧреНрд░рд╣реАрдд рдХрд░рдиреЗ рдХреА рдЖрд╡рд╢реНрдпрдХрддрд╛ рд╣реИред рдЗрд╕рдХреЗ рд▓рд┐рдП рдПрдХ рд╕рд░реНрд╡рд┐рд╕ рдЯреЗрдмрд▓ рдХрд╛ рдЙрдкрдпреЛрдЧ рдХрд┐рдпрд╛ рдЬрд╛рддрд╛ рд╣реИ рд▓реЙрдЧ_рдХреНрд╡реЗрд░реА
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.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_queries, рдЬрд┐рд╕рдореЗрдВ рддрд╛рд▓рд┐рдХрд╛ рд╕реЗ рд╡рд░реНрддрдорд╛рди рдкреНрд░рд╢реНрдиреЛрдВ рдХрд╛ рдПрдХ рд╕реНрдиреИрдкрд╢реЙрдЯ рд╢рд╛рдорд┐рд▓ рд╣реИ pg_stat_рдЗрддрд┐рд╣рд╛рд╕ (рддрд╛рд▓рд┐рдХрд╛ рдХрд╛ рдЙрдкрдпреЛрдЧ рдпрд╣рд╛рдВ рд╡рд░реНрдгрд┐рдд рд╣реИ -
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 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 - рдпрд╣ рд╡рд░реНрдЬрд┐рдд рд╣реИред
рдЕрдиреБрд░реЛрдз рдХреА рдХреНрд╡реЗрд░реА рдЖрдИрдбреА рдвреВрдВрдвреЗрдВ рдФрд░ 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