рд╡рд╛ рдЕрд▓рд┐рдХрддрд┐ рд▓рд╛рдЧреВ рдЯреЗрдЯреНрд░рд┐рд╕реЛрд▓реЛрдЬреАред
рд╕рдмреИ рдирдпрд╛рдБ рдкреБрд░рд╛рдиреЛ рдмрд┐рд░реНрд╕рд┐рдПрдХреЛ рдЫред
рдПрдкрд┐рдЧреНрд░рд╛рдлрд╣рд░реВред
рд╕рдорд╕реНрдпрд╛рдХреЛ рдЧрдарди
рдпреЛ рдЖрд╡рдзрд┐рдХ рд░реВрдкрдорд╛ AWS рдХреНрд▓рд╛рдЙрдбрдмрд╛рдЯ рд╕реНрдерд╛рдиреАрдп рд▓рд┐рдирдХреНрд╕ рд╣реЛрд╕реНрдЯрдорд╛ рд╣рд╛рд▓рдХреЛ PostgreSQL рд▓рдЧ рдлрд╛рдЗрд▓ рдбрд╛рдЙрдирд▓реЛрдб рдЧрд░реНрди рдЖрд╡рд╢реНрдпрдХ рдЫред рд╡рд╛рд╕реНрддрд╡рд┐рдХ рд╕рдордпрдорд╛ рд╣реЛрдЗрди, рддрд░, рд╣рд╛рдореА рдХреЗрд╣реА рдврд┐рд▓рд╛рдЗ рд╕рдВрдЧ рднрдиреМрдВред
рд▓рдЧ рдлрд╛рдЗрд▓ рдЕрджреНрдпрд╛рд╡рдзрд┐рдХ рдбрд╛рдЙрдирд▓реЛрдб рдЕрд╡рдзрд┐ 5 рдорд┐рдиреЗрдЯ рдЫред
рд▓рдЧ рдлрд╛рдЗрд▓, AWS рдорд╛, рд╣рд░реЗрдХ рдШрдгреНрдЯрд╛ рдШреБрдорд╛рдЗрдиреНрдЫред
рдкреНрд░рдпреЛрдЧ рдЧрд░рд┐рдПрдХрд╛ рдЙрдкрдХрд░рдгрд╣рд░реВ
рд╣реЛрд╕реНрдЯрдорд╛ рд▓рдЧ рдлрд╛рдЗрд▓ рдЕрдкрд▓реЛрдб рдЧрд░реНрди, AWS API рд▓рд╛рдИ рдХрд▓ рдЧрд░реНрдиреЗ рдмрд╛рд╕ рд╕реНрдХреНрд░рд┐рдкреНрдЯ рдкреНрд░рдпреЛрдЧ рдЧрд░рд┐рдиреНрдЫред
╨Я╨░╤А╨░╨╝╨╡╤В╤А╤Л:
- --db-instance-identifier: AWS рдорд╛ рдЙрджрд╛рд╣рд░рдг рдирд╛рдо;
- --log-file-name: рд╣рд╛рд▓ рдЙрддреНрдкрдиреНрди рд▓рдЧ рдлрд╛рдЗрд▓рдХреЛ рдирд╛рдо
- --max-item: рдЖрджреЗрд╢рдХреЛ рдЖрдЙрдЯрдкреБрдЯрдорд╛ рдлрд░реНрдХрд┐рдПрдХрд╛ рд╡рд╕реНрддреБрд╣рд░реВрдХреЛ рдХреБрд▓ рд╕рдВрдЦреНрдпрд╛редрдбрд╛рдЙрдирд▓реЛрдб рдЧрд░рд┐рдПрдХреЛ рдлрд╛рдЗрд▓рдХреЛ рдЯреБрдХреНрд░рд╛ рд╕рд╛рдЗрдЬред
- --рд╕реНрдЯрд╛рд░реНрдЯрд┐рдЩ-рдЯреЛрдХрди: рд╕реБрд░реБ рдЧрд░реНрджреИ рдЯреЛрдХрди рдЯреЛрдХрди
рд╣реЛ, рд░ рдмрд╕ - рдХрд╛рдо рдШрдгреНрдЯрд╛ рдХреЛ рд╕рдордпрдорд╛ рдкреНрд░рд╢рд┐рдХреНрд╖рдг рд░ рд╡рд┐рд╡рд┐рдзрддрд╛ рдХреЛ рд▓рд╛рдЧреА рдПрдХ рд░реЛрдЪрдХ рдХрд╛рд░реНрдпред
рдорд▓рд╛рдИ рд▓рд╛рдЧреНрдЫ рдХрд┐ рджрд┐рдирдЪрд░реНрдпрд╛рдХреЛ рдЖрдзрд╛рд░рдорд╛ рд╕рдорд╕реНрдпрд╛ рдкрд╣рд┐рд▓реЗ рдиреИ рд╣рд▓ рднрдЗрд╕рдХреЗрдХреЛ рдЫред рддрд░ рджреНрд░реБрдд рдЧреБрдЧрд▓рд▓реЗ рд╕рдорд╛рдзрд╛рдирд╣рд░реВ рд╕реБрдЭрд╛рд╡ рджрд┐рдПрди, рд░ рдердк рдЧрд╣рд┐рд░рд╛рдЗрдорд╛ рдЦреЛрдЬреНрдиреЗ рдХреБрдиреИ рд╡рд┐рд╢реЗрд╖ рдЗрдЪреНрдЫрд╛ рдерд┐рдПрдиред рдХреБрдиреИ рдкрдирд┐ рдЕрд╡рд╕реНрдерд╛рдорд╛, рдпреЛ рдПрдХ рд░рд╛рдореНрд░реЛ рдХрд╕рд░рдд рд╣реЛред
рдХрд╛рд░реНрдпрдХреЛ рдФрдкрдЪрд╛рд░рд┐рдХреАрдХрд░рдг
рдЕрдиреНрддрд┐рдо рд▓рдЧ рдлрд╛рдЗрд▓ рдЪрд░ рд▓рдореНрдмрд╛рдЗрдХреЛ рд░реЗрдЦрд╛рд╣рд░реВрдХреЛ рд╕реЗрдЯ рд╣реЛред рдЧреНрд░рд╛рдлрд┐рдХ рд░реВрдкрдорд╛, рд▓рдЧ рдлрд╛рдЗрд▓ рдпрд╕рд░реА рдкреНрд░рддрд┐рдирд┐рдзрд┐рддреНрд╡ рдЧрд░реНрди рд╕рдХрд┐рдиреНрдЫ:
рдХреЗ рдпрд╕рд▓реЗ рддрдкрд╛рдИрдВрд▓рд╛рдИ рдкрд╣рд┐рд▓реЗ рдиреИ рдХреЗрд╣рд┐ рд╕рдореНрдЭрд╛рдЙрдБрдЫ? "рдЯреЗрдЯреНрд░рд┐рд╕" рдорд╛ рдХреЗ рдЫ? рд░ рдпрд╣рд╛рдБ рдХреЗ рдЫред
рдпрджрд┐ рд╣рд╛рдореАрд▓реЗ рдЕрд░реНрдХреЛ рдлрд╛рдЗрд▓рд▓рд╛рдИ рдЧреНрд░рд╛рдлрд┐рдХ рд░реВрдкрдорд╛ рд▓реЛрдб рдЧрд░реНрджрд╛ рдЙрддреНрдкрдиреНрди рд╣реБрдиреЗ рд╕рдореНрднрд╛рд╡рд┐рдд рд╡рд┐рдХрд▓реНрдкрд╣рд░реВрд▓рд╛рдИ рдкреНрд░рддрд┐рдирд┐рдзрд┐рддреНрд╡ рдЧрд░реНрдЫреМрдВ (рд╕рд░рд▓рддрд╛рдХреЛ рд▓рд╛рдЧрд┐, рдпрд╕ рдЕрд╡рд╕реНрдерд╛рдорд╛, рд░реЗрдЦрд╛рд╣рд░реВрд▓рд╛рдИ рд╕рдорд╛рди рд▓рдореНрдмрд╛рдЗ рджрд┐рдиреБрд╣реЛрд╕реН), рд╣рд╛рдореА рдкрд╛рдЙрдБрдЫреМрдВред рдорд╛рдирдХ рдЯреЗрдЯреНрд░рд┐рд╕ рдЖрдВрдХрдбрд╛:
1) рдлрд╛рдЗрд▓ рдкреВрд░реНрдг рд░реВрдкрдорд╛ рдбрд╛рдЙрдирд▓реЛрдб рдЧрд░рд┐рдПрдХреЛ рдЫ рд░ рдЕрдиреНрддрд┐рдо рдЫред рдЯреБрдХреНрд░рд╛ рдЖрдХрд╛рд░ рдЕрдиреНрддрд┐рдо рдлрд╛рдЗрд▓ рдЖрдХрд╛рд░ рднрдиреНрджрд╛ рдареВрд▓реЛ рдЫ:
2) рдлрд╛рдЗрд▓рдорд╛ рдирд┐рд░рдиреНрддрд░рддрд╛ рдЫред рдЯреБрдХреНрд░рд╛ рдЖрдХрд╛рд░ рдЕрдиреНрддрд┐рдо рдлрд╛рдЗрд▓ рдЖрдХрд╛рд░ рднрдиреНрджрд╛ рд╕рд╛рдиреЛ рдЫ:
3) рдлрд╛рдЗрд▓ рдЕрдШрд┐рд▓реНрд▓реЛ рдлрд╛рдЗрд▓рдХреЛ рдирд┐рд░рдиреНрддрд░рддрд╛ рд╣реЛ рд░ рдирд┐рд░рдиреНрддрд░рддрд╛ рдЫред рдЯреБрдХреНрд░рд╛ рдЖрдХрд╛рд░ рдмрд╛рдБрдХреА рдЕрдиреНрддрд┐рдо рдлрд╛рдЗрд▓рдХреЛ рдЖрдХрд╛рд░ рднрдиреНрджрд╛ рдХрдо рдЫ:
рек) рдлрд╛рдЗрд▓ рдЕрдШрд┐рд▓реНрд▓реЛ рдлрд╛рдЗрд▓рдХреЛ рдирд┐рд░рдиреНрддрд░рддрд╛ рд╣реЛ рд░ рдЕрдиреНрддрд┐рдо рдЫред рдЯреБрдХреНрд░рд╛рдХреЛ рдЖрдХрд╛рд░ рдмрд╛рдБрдХреА рдЕрдиреНрддрд┐рдо рдлрд╛рдЗрд▓рдХреЛ рдЖрдХрд╛рд░ рднрдиреНрджрд╛ рдареВрд▓реЛ рдЫ:
рдХрд╛рд░реНрдп рдПрдХ рдЖрдпрдд рдЬрдореНрдорд╛ рдЧрд░реНрди рд╡рд╛ рдирдпрд╛рдБ рд╕реНрддрд░ рдорд╛ Tetris рдЦреЗрд▓реНрди рдЫред
рд╕рдорд╕реНрдпрд╛ рд╕рдорд╛рдзрд╛рдирдХреЛ рдХреНрд░рдордорд╛ рдЙрддреНрдкрдиреНрди рд╣реБрдиреЗ рд╕рдорд╕реНрдпрд╛рд╣рд░реВ
1) 2 рднрд╛рдЧ рдХреЛ рдПрдХ рд╕реНрдЯреНрд░рд┐рдЩ рдЧреЛрдВрдж
рд╕рд╛рдорд╛рдиреНрдпрддрдпрд╛, рддреНрдпрд╣рд╛рдБ рдХреБрдиреИ рд╡рд┐рд╢реЗрд╖ рд╕рдорд╕реНрдпрд╛ рдерд┐рдПрдиред рдкреНрд░рд╛рд░рдореНрднрд┐рдХ рдкреНрд░реЛрдЧреНрд░рд╛рдорд┐рдЩ рдкрд╛рдареНрдпрдХреНрд░рдордмрд╛рдЯ рдПрдХ рдорд╛рдирдХ рдХрд╛рд░реНрдпред
рдЗрд╖реНрдЯрддрдо рд╕реЗрд╡рд╛ рдЖрдХрд╛рд░
рддрд░ рдпреЛ рдЕрд▓рд┐ рдмрдвреА рд░реЛрдЪрдХ рдЫред
рджреБрд░реНрднрд╛рдЧреНрдпрд╡рд╢, рд╕реНрдЯрд╛рд░реНрдЯ рдЩреНрдХ рд▓реЗрдмрд▓ рдкрдЫрд┐ рдЕрдлрд╕реЗрдЯ рдкреНрд░рдпреЛрдЧ рдЧрд░реНрдиреЗ рдХреБрдиреИ рддрд░рд┐рдХрд╛ рдЫреИрди:
рддрдкрд╛рдИрд▓рд╛рдИ рдкрд╣рд┐рд▓реЗ рдиреИ рдерд╛рд╣рд╛ рдЫ рд╡рд┐рдХрд▓реНрдк --рд╕реНрдЯрд╛рд░реНрдЯрд┐рдЩ-рдЯреЛрдХрди рдХрд╣рд╛рдБ рдкреГрд╖реНрдард╛рдВрдХрди рд╕реБрд░реБ рдЧрд░реНрдиреЗ рднрдиреЗрд░ рдирд┐рд░реНрджрд┐рд╖реНрдЯ рдЧрд░реНрди рдкреНрд░рдпреЛрдЧ рдЧрд░рд┐рдиреНрдЫред рдпреЛ рд╡рд┐рдХрд▓реНрдкрд▓реЗ рд╕реНрдЯреНрд░рд┐рдЩ рдорд╛рдирд╣рд░реВ рд▓рд┐рдиреНрдЫ рдЬрд╕рдХреЛ рдорддрд▓рдм рдпрджрд┐ рддрдкрд╛рдИрдВрд▓реЗ рдЕрд░реНрдХреЛ рдЯреЛрдХрди рд╕реНрдЯреНрд░рд┐рдЩрдХреЛ рдЕрдЧрд╛рдбрд┐ рдЕрдлрд╕реЗрдЯ рдорд╛рди рдердкреНрдиреЗ рдкреНрд░рдпрд╛рд╕ рдЧрд░реНрдиреБрднрдпреЛ рднрдиреЗ, рд╡рд┐рдХрд▓реНрдкрд▓рд╛рдИ рдЕрдлрд╕реЗрдЯрдХреЛ рд░реВрдкрдорд╛ рд▓рд┐рдЗрдиреЗ рдЫреИрдиред
рд░ рддреНрдпрд╕реЛрднрдП, рддрдкрд╛рдИрдВрд▓реЗ рдЯреБрдХреНрд░рд╛-рднрд╛рдЧрд╣рд░реВрдорд╛ рдкрдвреНрдиреБ рдкрд░реНрдЫред
рдпрджрд┐ рддрдкрд╛рдЗрдБ рдареВрд▓рд╛ рднрд╛рдЧрд╣рд░реВрдорд╛ рдкрдвреНрдиреБрд╣реБрдиреНрдЫ рднрдиреЗ, рдкрдвреНрдиреЗ рд╕рдВрдЦреНрдпрд╛ рдиреНрдпреВрдирддрдо рд╣реБрдиреЗрдЫ, рддрд░ рднреЛрд▓реНрдпреБрдо рдЕрдзрд┐рдХрддрдо рд╣реБрдиреЗрдЫред
рдпрджрд┐ рддрдкрд╛рдЗрдБ рд╕рд╛рдиреЛ рднрд╛рдЧрдорд╛ рдкрдвреНрдиреБрд╣реБрдиреНрдЫ рднрдиреЗ, рдпрд╕рдХреЛ рд╡рд┐рдкрд░рд┐рдд, рдкрдвреНрдиреЗ рд╕рдВрдЦреНрдпрд╛ рдЕрдзрд┐рдХрддрдо рд╣реБрдиреЗрдЫ, рддрд░ рднреЛрд▓реНрдпреБрдо рдиреНрдпреВрдирддрдо рд╣реБрдиреЗрдЫред
рддреНрдпрд╕рдХрд╛рд░рдг, рдЯреНрд░рд╛рдлрд┐рдХ рдХрдо рдЧрд░реНрди рд░ рд╕рдорд╛рдзрд╛рдирдХреЛ рд╕рдордЧреНрд░ рд╕реМрдиреНрджрд░реНрдпрдХреЛ рд▓рд╛рдЧрд┐, рдореИрд▓реЗ рдХреЗрд╣рд┐ рдкреНрд░рдХрд╛рд░рдХреЛ рд╕рдорд╛рдзрд╛рдирдХреЛ рд╕рд╛рде рдЖрдЙрдиреБрдкрд░реНтАНрдпреЛ, рдЬреБрди рджреБрд░реНрднрд╛рдЧреНрдпрд╡рд╢, рдПрдХ рдмреИрд╕рд╛рдЦреА рдЬрд╕реНрддреЛ рджреЗрдЦрд┐рдиреНрдЫред
рдЙрджрд╛рд╣рд░рдгрдХрд╛ рд▓рд╛рдЧрд┐, 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 - рдирд┐рдЧрд░рд╛рдиреАрдХреЛ рд▓рд╛рдЧрд┐ рдХреНрд╡реЗрд░реА рд▓рдЧ рдкреНрд░рдпреЛрдЧ рдЧрд░реНрджреИ
рдХрд╛рд░реНрдпрд╕рдореНрдкрд╛рджрди рдореЗрдЯреНрд░рд┐рдХреНрд╕ рдЕрдиреБрд░реЛрдз рдкрд╛рдардХреЛ рд▓рд╛рдЧрд┐ рд╣реЛрдЗрди, рддрд░ рдпрд╕рдХреЛ ID рдХреЛ рд▓рд╛рдЧрд┐ рдХрдиреНрдлрд┐рдЧрд░ рдЧрд░рд┐рдПрдХреЛ рд╣реБрдирд╛рд▓реЗ, рддрдкрд╛рдИрдВрд▓реЗ рд▓рдЧ рдлрд╛рдЗрд▓рдмрд╛рдЯ рдЕрдиреБрд░реЛрдзрд╣рд░реВ рд╕рдореНрдмрджреНрдз рдЧрд░реНрди рдЖрд╡рд╢реНрдпрдХ рдЫ рдЬрд╕рдХреЛ рд▓рд╛рдЧрд┐ рдкреНрд░рджрд░реНрд╢рди рдореЗрдЯреНрд░рд┐рдХреНрд╕ рдХрдиреНрдлрд┐рдЧрд░ рдЧрд░рд┐рдПрдХреЛ рдЫред
рдареАрдХ рдЫ, рдХрдореНрддрд┐рдорд╛ рдПрдХ рдкреНрд░рджрд░реНрд╢рди рдШрдЯрдирд╛ рдХреЛ рдШрдЯрдирд╛ рдХреЛ рд╕рд╣реА рд╕рдордп рдХреЛ рд▓рд╛рдЧреАред
рдпрд╕рд░реА, рдЬрдм рдЕрдиреБрд░реЛрдз рдЖрдИрдбреАрдХреЛ рд▓рд╛рдЧрд┐ рдкреНрд░рджрд░реНрд╢рди рдШрдЯрдирд╛ рд╣реБрдиреНрдЫ, рддреНрдпрд╣рд╛рдБ рд╡рд┐рд╢рд┐рд╖реНрдЯ рдкреНрдпрд╛рд░рд╛рдорд┐рдЯрд░ рдорд╛рдирд╣рд░реВ рд░ рдЕрдиреБрд░реЛрдзрдХреЛ рд╕рд╣реА рдХрд╛рд░реНрдпрд╛рдиреНрд╡рдпрди рд╕рдордп рд░ рдЕрд╡рдзрд┐рдХреЛ рд╕рд╛рде рдПрдХ рд╡рд┐рд╢реЗрд╖ рдЕрдиреБрд░реЛрдзрдХреЛ рд╕рдиреНрджрд░реНрдн рд╣реБрдиреЗрдЫред рдХреЗрд╡рд▓ рджреГрд╢реНрдп рдкреНрд░рдпреЛрдЧ рдЧрд░реЗрд░ рджрд┐рдЗрдПрдХреЛ рдЬрд╛рдирдХрд╛рд░реА рдкреНрд░рд╛рдкреНрдд рдЧрд░реНрдиреБрд╣реЛрд╕реН 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