All posts by Denish

Postgres 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 records on parent table. Additionally, you couldn’t able to add Primary Key and Foreign Keys on partitioned tables. The recent release of Postgres 11 solves all of these problems.

Postgres 11 adds a lot more partitioning features to manage partitioned tables easier than ever! Below is the comparison of partitioning features across Postgres releases:

feature

Postgres – 11

postgres -10

9.6

Declarative table partitioning

Yes

Yes

No

Default Partition –

A default partition stores data that does not match the partition key for any other partition

Yes

No

No

Partitioning by a HASH key

Yes

No

No

Support for PRIMARY KEY, FOREIGN KEY, indexes, and triggers on partitioned tables

Yes

No

No

UPDATE on a partition key –

When a partition key is updated on a row, the row is moved to the appropriate partition.

Solution:

CREATE TABLE sms_campaign_subscriber_111 PARTITION OF sms_campaign_subscribers FOR VALUES FROM (111) TO (112) PARTITION BY RANGE(sms_campaign_id);

In above statement , I was actually creating sub-partition on the child table hence there was no child table for parent table.. Once I get rid of PARTITION BY RANGE(sms_campaign_id) from CHILD table creation statement, the things worked as expected. Thanks to Keith to help me identify the issue.

LIST Partitioning:

In my case, it is actually make sense to use LIST partition instead of RANGE partition.

CREATE TABLE audit.users_audit_2018_07 PARTITION OF audit.users_audit FOR VALUES FROM ('2018-07-01') TO ('2018-08-01');
CREATE TABLE audit.users_audit_2018_08 PARTITION OF audit.users_audit FOR VALUES FROM ('2018-08-01') TO ('2018-09-01');
CREATE TABLE audit.users_audit_2018_09 PARTITION OF audit.users_audit FOR VALUES FROM ('2018-09-01') TO ('2018-10-01');
CREATE TABLE audit.users_audit_2018_10 PARTITION OF audit.users_audit FOR VALUES FROM ('2018-10-01') TO ('2018-11-01');
CREATE TABLE audit.users_audit_2018_11 PARTITION OF audit.users_audit FOR VALUES FROM ('2018-11-01') TO ('2018-12-01');
CREATE TABLE audit.users_audit_2018_12 PARTITION OF audit.users_audit FOR VALUES FROM ('2018-12-01') TO ('2019-01-01');

NOTE: you have to pre-create these child tables as well indices in advance so you can come up with process to create them using some kind of script or add trigger on parent partitioned table to create child table automatically.

Place the trigger back on USERS table..

CREATE TRIGGER users_audit_trig
BEFORE INSERT OR UPDATE OR DELETE
ON public.users
FOR EACH ROW
EXECUTE PROCEDURE public.users_audit_trig();

Make sure constraint_exclusion=partition in postgresql.conf so query can use partition pruning. Below query grabs data from single partitioned table because WHERE clause includes partitioned_key (audit_ts).

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 to make the audit logging consistent and readable.

Audit tables using trigger based approach by creating audit schema on live database and keep the audit tables updated through trigger.

Both of these approaches have pros and cons.

If you are looking for detailed auditing (including SELECT) at database level, you can use pgaudit extension. However, you will have to deal on how to make auditing data queryable for end users. Additionally, you have to enable at database level instead of specific tables.

On the other hand, if you are only concerned about auditing DML and for specific tables and even further if you want to optimize at column level, trigger based approach is your answer. However, you have to deal with audit schema growth . However, audit tables are readily available to query without any further processing or tools. If you are using exact schema for audit tables, the trigger based approach requires to change schema on audit tables when you change live schema so there will be additional overhead managing audit schema. However, if you use Postgres JSONB column to keep track of auditing, you can come over overhead of schema changes on audit tables with live tables.

In this post, I have explored a way to use JSONB data type to store auditing details using trigger based approach.

The audit tables grows really fast so you want to partition audit tables. Postgres 10 makes table partitioning much easier to manage so you should convert audit tables into partition tables to keep only X months of data in production database and archive rest of partitioned table off to cheaper storage i.e Amazon S3 or Glacier. The upcoming Postgres 11 release includes with even more partitioning performance features so they will be helpful to keep up DB performance with users and database growth.

Hope it will be helpful to someone architecting audit schema in Postgres.

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, I don’t have to worry on password and password policies because the policies are handled by AD. On the other hand, users database authentication will be dependent on AD infrastructure. Please make sure AD infrastructure is redundant enough with low latency connection for your organization before making this decision.

I have come up with simple steps to connect Postgres to Active Directory. TBH, If you can get serviceAccount to be used to connect to organization’s active directory, the rest of the setup is fairly straight forward.

Step-1 :

Get and active directory server name and test connection from Postgres server :

If the above query returns the result, you successfully found the user in the Active directory on specified path. If you can’t find user, please work with Active Directory Admin to find the correct basedn (-b option in above query).

Step-4 :

Add following line in pg_hba.conf and reload the config. Postgres reads pg_hba.conf from top to bottom. Please make sure to add this line at the end so authentication for other application users can be performed without active directory.

* *Password with special characters like @! doesn’t parse properly in pg_hba.conf. Plus, I had to provide plain text password in pg_hba.conf for AD service account. If you know alternative, please let me know 🙂

Step-5:

psql> create role dpatel login;

Step-6 :

Test the connection. Looks for Postgres logs for success/error.

Hopefully, this post will help someone to integrate Postgres with Active Directory using LDAP protocol.

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 to talk with a popular database in the industry, which is Postgres!

Issue: My developers could able to connect to Postgres DB using Tableau Desktop on their laptop without any issue and make LIVE connection to Postgres 9.6 database. However, when they publish same dashboard with LIVE connection to database, it was hanging on the tableau server.

To reproduce the issue, we installed and tried to use Tableau Desktop application on Tableau server itself. The Desktop was hanging while connecting to Postgres database. This made it clear that there is some problem on connecting from Tableau server only. Over the last couple of weeks I have looked various things to understand and resolve the issue:

Database encoding ; UTF8 Vs ANSI

Tested Postgres ODBC drivers on Tableau Server (Windows 2016)

A lot of troubleshooting because the connection to empty database was working !!

Working with tableau support to explain and track down issue

Windows settings

None of the above helped!

Finally, Wireshark came to rescue ! We traced the network traffic on port 5432 on Windows 2016 while it was making the connection to Postgres database running on Linux server.

In the Wireshark logs, when the TCP communicates with the Postgres database, filtering down to the server indicates that the TCP/IP sequencing is inconsistent. When initiating a connection, sequence numbers are maintained on each side of the connection. In this case, the packets are sent and received from the Postgres Server (PDB), and Tableau Desktop (TD). The sequence number is comprised of both values added together to output a single value to ensure information is accurate.

Typically, TCP data transmission is sequentially ordered. Each packed has an acknowledgement number, known as ACK. The value is equal to the next chronological sequence number from the packet that server has just received.

Like pages in a book, we expect a sequential order. The Wireshark logs indicate the PDB sequence number is not consistent with the TD sequence number as shown below:

In the good packets sequence above, similar to a book of pages, the first line indicates we sequentially expect that if there are 14 pages in the database, the next packet on the next page should from Tableau Desktop should be 15. In the second line, Tableau Desktop returns the response on page 1 and has 34 pages total. The next packet sent will start on page 35 from Postgres. Third, on the database side, PDB indicates to Tableau Desktop, page 15 is the starting page with a total of 304 pages. The next page from Tableau Desktop should start at 319.

Let’s look at problematic lines in below screenshot for the packets captured between numbers 7701-7710:

In the second to last line above, the data has been skipped. PDB expects the next packet should start at byte #1337, but instead the starting point is at byte #9529. Normally, in situations where the bytes don’t match, this would indicate that some of the packets didn’t get captured.

For example, Wireshark missed a few messages. But the ACK value for that packet points to the last packet we captured from TD > PDB, which clearly states it expects PDB to begin its next packet from byte #1337. Not only did our sequence number increment unexpectedly, it increased suspiciously at the value of: 8,192. That’s the exact number of bytes in 8KB (8*2^10=8192).

As a result of the bad packet mismatch, Tableau Desktop machine recognizes the information has been skipped according to the sequence number and re-requests a packet starting at byte #1337, however, PDB does not respond.

To track down packet loss.. you can ping the other server with different packet size…

As you can see in output above, when the package size is larger than 200 bytes, the packets loss is 100%.

In terms on root cause, it turned out to be MTU size mismatch issue. Once the MTU size is adjusted on switch and both Tableau Windows and Postgres Unix database server, the packet loss issue has been resolved and Tableau managed to connect to Postgres without any issue.

It was a roller coaster experience dealing with mysterious packet loss issue. In case, if you come across similar connectivity issue between application to database, you might want to make sure you are not having packet loss issue. Wireshark can be your friend 🙂

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.

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 in to Docker environment.

Do not use default Postgres Docker image. Start with your own docker image from scratch.

Handle Postgres service shutdown gracefully in docker with SIGTERM

OS can be stored in Docker container

Data volume MUST be stored in persistent storage

Use some kind of framework to manage docker containers

Apache Mesos & Aurora

OpenStack & Kubernetes

You can mount NetApp for backups and WAL files on to container

Make templates for resources for different kind of workloads

Aurora job for resource templates

postgresql.conf templates

Use static IPs for services ; DBs, Front end an backend servers

It will be easier to control access at container level for better security

failover is easy to manage with static IP for master DB server

Benchmark your existing system and compare with new system

Keep eyes on TPS using pgbench and benchmarksql

Monitoring solution for Postgres DB

collectd or other agent based monitoring

pg_stat_statements is very useful

Docker container should be monitored separately

docker stats

Backup container for taking backups

Standby container for setting up standby jobs

I hope it will be useful for someone working on migrating Postgres into Docker environment!

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 for building the most resilience database in the world 🙂

Here is my Postgres database recovery story 🙂

Disclaimer:

I'm posting the steps carried out during the recovery process for information purpose only. This post doesn't provide any guarantee that it will work for your use-case and/or environment.

Note: The actual database name has been replaced with “dbname” and actual table names with “tablename”.

One of the Postgres DB cluster database experienced disk level corruption thus we were hitting this error: