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 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. […]