Audit logging with Postgres partitioning
As I mentioned in my previous post “Audit logging using JSONB in Postgres” , audit tables can be partitioned easily in Postgres 10. Let’s use Postgres partitioning in sample users_audit table… Drop existing trigger on live table and users_audit table drop trigger users_audit_trig ON public.users; drop table if exists audit.users_audit; Create partition table by RANGE partition […]
Audit logging using JSONB in Postgres
Recently, someone reached out to me asking “what’s the best way to achieve database DML auditing in Postgres?” I have suggested a couple of options below to achieve DML auditing in Postgres: Audit using Postgres logs. Postgres allows to keep track of DML statements at database level by enabling auditing directly in the postgres logs. You can use https://github.com/pgaudit/pgaudit extension […]
Connecting Postgres to Active Directory for Authentication
I have connected our Postgres instance to Active Directory for authentication because I didn’t want to manage passwords for hundreds for users accessing mission critical databases. The authentication is being performed by Active Directory but authorization (managing groups, permissions at table/column/row level) is still be handled by Postgres. The biggest advantage of connecting Postgres to AD for real users, […]
Tracing Tableau to Postgres connectivity issue using Wireshark!
I spent last couple of weeks trying to resolve connection issues from Tableau Server 10.3 to Postgres 9.6.6. If you are not familiar with Tableau , it is popular enterprise grade visualization tool allow advanced analytic capabilities to understand and visualize data . As it is very popular in the industry, it is obvious that tableau has […]
Christmas Gift!
Merry Christmas!! If you are using SQL Server or Oracle databases, I’m giving away my 1 hour of time for rest of this week for free of cost to discuss how you can save $$$$ by migrating them to PostgreSQL database by keeping same features and achieve better performance. you can shoot an email to denish.j.patel@gmail.com Happy […]
Running Postgres in Docker
For last six months, I have been working on moving Postgres from bare metal & VM based systems in Docker. As of today, we have migrated a couple of mission critical Postgres DBs (~ 2TB) on to Docker environment. During the migration journey, I have listed down some of the things to consider running Postgres production instances […]
YUM repo location & HTTPS changes for Postgres packages !
As Devrim blogged about Postgres YUM repo changes , I wanted to write down procedure I have to follow this morning 🙂 For example, If you want to update YUM repo for Postgres 9.4 on CentOS x86_64, you can update executing below command: yum localinstall https://download.postgresql.org/pub/repos/yum/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-3.noarch.rpm Now, you can follow normal Postgres version upgrade procedure. […]
Advanced Postgres Monitoring!
Today, I presented at PgOpen 2016 (Postgres conference) in Dallas, TX. Here is the slide deck : [slideshare id=66063373&doc=0b32a8bb-f5b3-4886-bd25-358438dc8f13-160915153052]
Recovering Postgres database from disk level corruption!!
A couple of weeks ago, I had to deal with corrupted Postgres database cluster. At the end, we couldn’t able to recover some of the data but managed to recover most part of it. Having experience working with dozens of database systems, I’m pleasantly surprised to experience resiliency of Postgres database. Kudos to Postgres Development team […]
Analyzing effect on default_statistics_target on ANALYZE
Last week, I received request to increase PostgreSQL’s config parameter default_statistics_target on one of the very large DB clusters. The developers filled a ticket to increase the default_statistics_target to 10,000 at cluster level because one of the queries has started full sequencial scan instead of index scan. By default, Postges cluster sets default_statistics_target to 100 but you can set up to […]