Author: Scott Mead

Feed: Planet PostgreSQL. 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.  I’ve got a few db’s that I’ve moved to 9.6 for dev and test.  I got a call that ‘something seems gummed’ up and my muscle memory kicked in with my tried and true query above…. [scottsmac@[local]:9610] postgres@postgres=# SELECT pid, postgres-# ... Read More

Feed: Planet PostgreSQL. Getting Postgres data into a shell scriptI 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 server. This is usually the first tool that most people end up using with postgres. It’s simplicity masks a very powerful ‘swiss-army’ knife that lets you very quickly work with data. Let’s take a basic table ... Read More

Feed: Planet PostgreSQL. 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 a bad thing, there are some situations where it can get unwieldy.At a high-level: During an UPDATE or DELETE in postgres, the row is not removed. It is marked as ‘free space’ for re-use. If there are no inbound rows that fit ... Read More

Feed: Planet PostgreSQL. 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. The default file is very well commented, the most notable comment in the file will let you know that “(change requires restart)”. See the following except from postgresql.conf with the associated comments: listen_addresses = '*'      # what IP address(es) to listen ... Read More

Feed: Planet PostgreSQL. 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, context from pg_settings where context = 'user'; postgres=# alter user postgres set log_  [tab][tab] log_duration                log_min_duration_statement  log_planner_stats log_error_verbosity         log_min_error_statement     log_statement log_executor_stats          log_min_messages         ... Read More

Feed: Planet PostgreSQL. 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 operates on a single database in a postgres cluster.  You provide it with the name of a database and [by default] it dumps all the data, you can also select individual tables, schemas, etc… with different flags.  Essentially, given  a database instance like so: pg_dump is capable of operating at this level of the tree: Okay great.  You’ll notice however that your backups don’t ... Read More

Feed: Planet PostgreSQL. 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. Essentially, promotion is the process of converting your read-only, slave database servers to a read-write capable server.  Essentially, this is going to break the replication and allow you to start using your slave.  This is the type of thing you would do in the event that your master DB failed for some reason.  Right ... Read More

Feed: Planet PostgreSQL. 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 of PS1, it’s an environment variable that lets you set what your prompt looks like.  psql has something very similar.  The question becomes, how do I get a useful prompt that is compact and doesn’t cause all of my commands to wrap off ... Read More

Feed: Planet PostgreSQL. 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 log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ' log_checkpoints = on log_autovacuum_min_duration=0 log_temp_files = 0 log_lock_waits = on Essentially, these cover the basics when it comes to monitoring, without adding a whole lot of stuff to your logs.  These settings are ... Read More