close

Menu

Contact Info

Folow us on social

Working With Repmgr: Using Other 3rd Party Tools for Setting up a Standby

From the docs: “repmgr is an open-source tool suite for managing replication and failover in a cluster of PostgreSQL servers.” For a standby node to be used in a repmgr configuration, it has to be registered.  Normally you do this by allowing repmgr to clone the standby first.  If a node was not cloned by […]

Working with Amazon Aurora PostgreSQL: dag, standby rebooted again!

While I continue to be amazed at how fast PG runs on Amazon Aurora (no WAL logging)  or how fast I can create a snapshot or standby (shared storage),  there are always a few clouds around.  Today the cloud I’m lookin’ at is replica reboots.  Every week or so, one of my standbys gets rebooted […]

Working with Amazon Aurora PostgreSQL: what happened to the stats?

If you reboot your PostgreSQL aurora instance, your distribution stats will still be there, but your monitoring stats will not. Official PostgreSQL documentation states that if you do a normal shutdown, both distribution and monitoring stats will still be there when you restart your instance (Monitoring Stats): “When the server shuts down cleanly, a permanent […]

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