Contact Info

Folow us on social

How to set application_name for psql command line utility?

If you are using psql command line utility and wonder how you can set application_name… If you try to set application_name using psql variable, it doesn’t work … $ psql -d postgres -U elephas –variable=application_name=elephas_backup  postgres=# show application_name ; application_name —————— psql (1 row) Option-1: psql -U elephas -d ‘dbname=postgres application_name=elephas_backup’ postgres=# show application_name ; […]

Is there a limit on number of partitions handled by Postgres?

PostgreSQL is continuously improving partitions support but there is limitations on number of partitions handled by each release. Based on our experience , if you are using a lot more partitions than its practical limit for a PostgreSQL release, you will experience performance degradation during the planning phase of the query.  When you approach the […]

Postgres 11 partitioning

PostgreSQL supported table partitioning  implementation based on inheritance and triggers for over more than a decade now. However, the declarative partition support was added in Postgres 10 release in Oct 2017.  Since Postgres 10, Postgres  supports built-in declarative partitioning so it was easier to create partitions but you still need to manage trigger to update […]

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 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 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 Now,  you can follow normal Postgres version upgrade procedure. […]