troubleshooting postgresql · 2017. 8. 11. · troubleshooting postgresql, 2017 esri user...
TRANSCRIPT
![Page 1: Troubleshooting PostgreSQL · 2017. 8. 11. · Troubleshooting PostgreSQL, 2017 Esri User Conference--Presentation, 2017 Esri User Conference, Created Date: 8/11/2017 10:04:28 AM](https://reader030.vdocuments.us/reader030/viewer/2022012009/613495ecdfd10f4dd73bd2ee/html5/thumbnails/1.jpg)
Troubleshooting PostgreSQLJUSTIN MUISE
![Page 2: Troubleshooting PostgreSQL · 2017. 8. 11. · Troubleshooting PostgreSQL, 2017 Esri User Conference--Presentation, 2017 Esri User Conference, Created Date: 8/11/2017 10:04:28 AM](https://reader030.vdocuments.us/reader030/viewer/2022012009/613495ecdfd10f4dd73bd2ee/html5/thumbnails/2.jpg)
AgendaTroubleshooting PostgreSQL
• Overview
- Sdeintercept files
- PostgreSQL log
• Configuration
• Troubleshooting Examples
- Connection errors
- Performance
- SQL Syntax errors
![Page 3: Troubleshooting PostgreSQL · 2017. 8. 11. · Troubleshooting PostgreSQL, 2017 Esri User Conference--Presentation, 2017 Esri User Conference, Created Date: 8/11/2017 10:04:28 AM](https://reader030.vdocuments.us/reader030/viewer/2022012009/613495ecdfd10f4dd73bd2ee/html5/thumbnails/3.jpg)
Tracing Methods
• Sdeintercept
- Geodatabase/Client libraries
• PostgreSQL logs
- Database
![Page 4: Troubleshooting PostgreSQL · 2017. 8. 11. · Troubleshooting PostgreSQL, 2017 Esri User Conference--Presentation, 2017 Esri User Conference, Created Date: 8/11/2017 10:04:28 AM](https://reader030.vdocuments.us/reader030/viewer/2022012009/613495ecdfd10f4dd73bd2ee/html5/thumbnails/4.jpg)
Configuring a SDEINTERCEPT
• Configure Environment Variables
- SDEINTERCEPT = crwTf
- c - Intercept the API command name
- r - Intercept the Channel broadcasts read-only
- w - Intercept the Channel broadcasts write-only
- t/T - Intercept the log time (m:s/h:m:s)
- f - Intercept flush immediate
- SDEINTERCEPTLOC = location
• Command Prompt vs System Properties
• Numeric extensions (i.e. .001)
![Page 5: Troubleshooting PostgreSQL · 2017. 8. 11. · Troubleshooting PostgreSQL, 2017 Esri User Conference--Presentation, 2017 Esri User Conference, Created Date: 8/11/2017 10:04:28 AM](https://reader030.vdocuments.us/reader030/viewer/2022012009/613495ecdfd10f4dd73bd2ee/html5/thumbnails/5.jpg)
Configuring a PostgreSQL trace
• PostgreSQL.conf file
- ERROR REPORTING AND LOGGING section
- All parameters specific to logging
- Most basic:
- Log_directory=‘pg_log’
- Log_statement=‘all’
- Session_preload_libraries = ‘auto_explain’
- Log_rotation_<parameter>=…
- Log_line_prefix=…
- C:\Program Files\PostgreSQL\9.5\data\pg_log
• Customizable
![Page 6: Troubleshooting PostgreSQL · 2017. 8. 11. · Troubleshooting PostgreSQL, 2017 Esri User Conference--Presentation, 2017 Esri User Conference, Created Date: 8/11/2017 10:04:28 AM](https://reader030.vdocuments.us/reader030/viewer/2022012009/613495ecdfd10f4dd73bd2ee/html5/thumbnails/6.jpg)
Configuration Tips
• Generate little file volume
- Rotate the PostgreSQL log
- Restart ArcGIS application
• Run PostgreSQL trace and SDEINTERCEPT simultaneously
![Page 7: Troubleshooting PostgreSQL · 2017. 8. 11. · Troubleshooting PostgreSQL, 2017 Esri User Conference--Presentation, 2017 Esri User Conference, Created Date: 8/11/2017 10:04:28 AM](https://reader030.vdocuments.us/reader030/viewer/2022012009/613495ecdfd10f4dd73bd2ee/html5/thumbnails/7.jpg)
Reading PostgreSQL Logs & SDEINTERCEPTS
• SDEINTERCEPT
- Text editor
• PostgreSQL
- Text editor
- PostgreSQL log analyzers
- Pg_fouigne
- Pg_badger
![Page 8: Troubleshooting PostgreSQL · 2017. 8. 11. · Troubleshooting PostgreSQL, 2017 Esri User Conference--Presentation, 2017 Esri User Conference, Created Date: 8/11/2017 10:04:28 AM](https://reader030.vdocuments.us/reader030/viewer/2022012009/613495ecdfd10f4dd73bd2ee/html5/thumbnails/8.jpg)
Troubleshooting Examples
![Page 9: Troubleshooting PostgreSQL · 2017. 8. 11. · Troubleshooting PostgreSQL, 2017 Esri User Conference--Presentation, 2017 Esri User Conference, Created Date: 8/11/2017 10:04:28 AM](https://reader030.vdocuments.us/reader030/viewer/2022012009/613495ecdfd10f4dd73bd2ee/html5/thumbnails/9.jpg)
Cannot Connect
![Page 10: Troubleshooting PostgreSQL · 2017. 8. 11. · Troubleshooting PostgreSQL, 2017 Esri User Conference--Presentation, 2017 Esri User Conference, Created Date: 8/11/2017 10:04:28 AM](https://reader030.vdocuments.us/reader030/viewer/2022012009/613495ecdfd10f4dd73bd2ee/html5/thumbnails/10.jpg)
Unsupported Data Type
![Page 11: Troubleshooting PostgreSQL · 2017. 8. 11. · Troubleshooting PostgreSQL, 2017 Esri User Conference--Presentation, 2017 Esri User Conference, Created Date: 8/11/2017 10:04:28 AM](https://reader030.vdocuments.us/reader030/viewer/2022012009/613495ecdfd10f4dd73bd2ee/html5/thumbnails/11.jpg)
Performance(CHANGE IMAGE)
![Page 12: Troubleshooting PostgreSQL · 2017. 8. 11. · Troubleshooting PostgreSQL, 2017 Esri User Conference--Presentation, 2017 Esri User Conference, Created Date: 8/11/2017 10:04:28 AM](https://reader030.vdocuments.us/reader030/viewer/2022012009/613495ecdfd10f4dd73bd2ee/html5/thumbnails/12.jpg)
SQL Syntax Error
![Page 13: Troubleshooting PostgreSQL · 2017. 8. 11. · Troubleshooting PostgreSQL, 2017 Esri User Conference--Presentation, 2017 Esri User Conference, Created Date: 8/11/2017 10:04:28 AM](https://reader030.vdocuments.us/reader030/viewer/2022012009/613495ecdfd10f4dd73bd2ee/html5/thumbnails/13.jpg)
Thank you!
Questions?
![Page 14: Troubleshooting PostgreSQL · 2017. 8. 11. · Troubleshooting PostgreSQL, 2017 Esri User Conference--Presentation, 2017 Esri User Conference, Created Date: 8/11/2017 10:04:28 AM](https://reader030.vdocuments.us/reader030/viewer/2022012009/613495ecdfd10f4dd73bd2ee/html5/thumbnails/14.jpg)
![Page 15: Troubleshooting PostgreSQL · 2017. 8. 11. · Troubleshooting PostgreSQL, 2017 Esri User Conference--Presentation, 2017 Esri User Conference, Created Date: 8/11/2017 10:04:28 AM](https://reader030.vdocuments.us/reader030/viewer/2022012009/613495ecdfd10f4dd73bd2ee/html5/thumbnails/15.jpg)
Aspect Ratio Test
If this shape does not appear as
a perfect circle, adjust the aspect
ratio of your display until it does.
Try the resolution 1920x1080 for
16:9 displays.