Scott PostgreSQL

/Scott PostgreSQL

Is my query stuck? PostgreSQL 9.6

One of the most common queries that I keep in my pocket is SELECT pid, datname, usename, now() - query_start AS runtime, waiting, state, query FROM pg_stat_activity WHERE waiting='true'; This gives you a quick look at any queries that are ‘waiting’.  Specifically, you will see queries that are explicitly blocked on some other [...]

By | November 17th, 2016|Scott PostgreSQL|0 Comments

Getting Postgres data into a shell script

Getting Postgres data into a shell script I use postgres for a lot of different things. Many times, it's a full-blown application where I can directly access the database (i.e. python + psycopg2, java + jdbc, etc...). What if you're using something a bit less formal, like a shell script? psql psql is the native, [...]

By | November 9th, 2016|Scott PostgreSQL|3 Comments

PostgreSQL bloat estimates

The PostgreSQL storage manager is tasked with the complex job of satisfying ACID compliance. This is a well documented series of algorithms that I won't go into here. Like any storage system or database, PostgreSQL tables can start to take on free space (bloat as it is sometimes known).  While all free space is not [...]

By | November 2nd, 2016|Scott PostgreSQL|0 Comments

To reload or restart? (with a test script!)

Even the most seasoned DBA sometimes forgets if a setting change needs a restart or a reload. So, how do we check, in a PostgreSQL database if I can reload or restart a setting? Config File The quickest way is to look at the default configuration file for the version of PostgreSQL that you're running. [...]

By | October 31st, 2016|Scott PostgreSQL|0 Comments

Per-User and Per-Database configuration

Sometimes, you need to set one of the Postgres configuration items for just one user or just one database.  This is easy to do in Postgres.  Note: I'm using the 'log_' configuration parameters here, but, there are many items you can set per user / db: For an exhausitve list, run the query:  select name, [...]

By | October 28th, 2016|Scott PostgreSQL|0 Comments

Don’t forget the globals!

pg_dump is a great tool.  You can get a consistent database backup from a live database without impacting your running traffic. Many people don't realize however that a pg_dump isn't actually a complete backup... pg_dump pg_dump operates on a single database in a postgres cluster.  You provide it with the name of a database and [by [...]

By | October 27th, 2016|Scott PostgreSQL|4 Comments

How does a database get promoted these days?!?

These days, replicated postgres is simple to setup, easy to maintain and ultra-reliable.  Actually, the biggest question is typically "how do I convert a slave to a master?" Well, the short answer is, a couple of ways.  These days, the very simple 'pg_ctl promote' works best. Promotion Essentially, promotion is the process of converting your read-only, slave [...]

By | October 26th, 2016|Scott PostgreSQL|0 Comments

Production psql Prompts

I deal with lots of production.  When you're dealing with multiple production machines, it's important to know many things: Who you are What you're connected to Where you are Why you are connected When you are connected Most prompts don't give you any of this detail.  If you're familiar with bash, you probably have heard [...]

By | October 25th, 2016|Linux, Scott PostgreSQL, tip|7 Comments

Quick stats with pgBadger

A while back, I wrote about 'Helpful Postgres logging and defaults'.  The real question is, how helpful are these, really?  So, let's take a quick look at how we can use the increased logging in order to do something useful.  If the previous post is TL;DR, here's the quick list: logging_collector = on log_filename = 'postgresql-%a.log' log_truncate_on_rotation=on [...]

By | October 24th, 2016|Scott PostgreSQL|0 Comments