One more time about collation in PostgreSQL

It’s been a long time since my last post. It’s time to write something useful :)

When people start working with PostgreSQL they sometimes make mistakes which are really difficult to fix later. For example during initdb of your first DB you don’t really understand whether you need …

more ...

Wait interface in PostgreSQL

People having experience with commercial RDBMS are used to have the ability to answer the question “What a particular session is doing right now?” Or even “What was that session waiting 5 minutes ago?” For a long time PostgreSQL did not have such diagnostic tools and DBAs used to get …

more ...

PostgreSQL replication lag in seconds

Our typical PostgreSQL shard consists of master and two replics. We monitor that master has as much as needed number of replics (we fire WARN event in monitoring if there is only one alive replica and CRIT if there are no alive replics). And we monitor replication lag, replay_location …

more ...

Checking backups consistency

Once upon a time as a result of two human errors while deploying new code on our databases we did DROP SCHEMA data CASCADE; on all shards of one of our clusters with more than 3 TB of data. It added us gray hair, allowed us to check our PITR …

more ...

Upgrading PostgreSQL to 9.4

Preface

In 9.4 there is a logical decoding which would allow to upgrade from 9.4 to 9.5 quiet cheap (at least it shoud be so). But right now upgrading major version of PostgreSQL is painful. In most common case it looks like that:

  1. Stop master and call …
more ...