Scott PostgreSQL

/Scott PostgreSQL

Strategic Autovacuum

There's a big mis-conception that autovacuum is evil. Although it's true that autovacuum can do things unexpectedly, I've found that it's better to tune it up and let it do what it does best. There are always going to be large / busy / special tables that need special attention, and that's okay. Attached is [...]

By | March 8th, 2017|Categories: Scott PostgreSQL|Tags: , |0 Comments

PostgreSQL Schema Visualization

I spend a lot of time trying to learn what's already been implemented, as DBA's, we tend to live in that world. It's important that you have tools that allow you to quickly get the visual so you can get on with your job. One of the biggest 'reverse engineering' tasks that DBA's have to [...]

By | December 22nd, 2016|Categories: Scott PostgreSQL|1 Comment

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 database activity. [...]

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

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, command-line interface that ships with a postgresql [...]

By | November 9th, 2016|Categories: Scott PostgreSQL|Tags: , |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|Categories: Scott PostgreSQL|Tags: , , |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|Categories: Scott PostgreSQL|1 Comment

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|Categories: Scott PostgreSQL|Tags: , , , |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|Categories: Scott PostgreSQL|Tags: , , , |0 Comments