PostgreSQL Durability Test

29 Nov 2015

Do not consider this a thorough test; it's just a test I ran on my laptop to try to test PostgreSQL's out-of-the-box durability capabilities. I'm using a custom-compiled version of PostgreSQL as done here.

First, create these scripts which we will use in our test.

In root's home directory, name this script set-write-through.sh:

#!/bin/bash

set -u
set -e
set -o pipefail

hdparm -W0 /dev/sda

In root's home directory, name this script kill-pg.sh, and modify it for the location of your postmaster.pid:

#!/bin/bash

set -e
set -o pipefail
set -u

PGPID=$(head -1 /usr/local/pgsql-9.4.5/data/postmaster.pid)

kill -9 ${PGPID}

In your regular user's home directory, name this script get-last-written.sh, and modify it for the location of your psql binary:

#!/bin/bash

set -u
set -e
set -o pipefail

echo "max is:"
/usr/local/pgsql-9.4.5/bin/psql -U postgres -d postgres -c 'select max(i) from t'
echo "count is:"
/usr/local/pgsql-9.4.5/bin/psql -U postgres -d postgres -c 'select count(*) from t'

In your regular user's go/src directory, (such as ${HOME}/go/src/github.com/foo/pg-durability/insert-ints), create this main.go file:

package main

import (
	"fmt"
	"os"

	"github.com/jackc/pgx"
)

func main() {
	var conn *pgx.Conn
	var config pgx.ConnConfig
	config.Host = "localhost"
	config.User = "postgres"
	config.Password = "postgres"
	config.Database = "postgres"
	var err error
	conn, err = pgx.Connect(config)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
		os.Exit(1)
	}

	err = createTable(conn)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Could not create table: %v\n", err)
		os.Exit(1)
	}
	fmt.Printf("Table exists.\n")

	err = truncateTable(conn)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Could not truncate table: %v\n", err)
		os.Exit(1)
	}
	fmt.Printf("Table truncated.\n")

	i := 0
	for {
		i++
		err = insert(conn, i)
		if err != nil {
			fmt.Fprintf(os.Stderr, "Did not insert %d\n", i)
			os.Exit(1)
		}
		fmt.Printf("inserted %d\n", i)
	}
	conn.Close()
}

func insert(conn *pgx.Conn, i int) error {
	_, err := conn.Exec("insert into t (i) values ($1)", i)
	return err
}

func truncateTable(conn *pgx.Conn) error {
	_, err := conn.Exec("truncate table t")
	return err
}

func createTable(conn *pgx.Conn) error {
	_, err := conn.Exec(`
	  create table if not exists t (
			i int not null);
	`)
	return err
}

Build the above go file like so:

$ cd go/src/github.com/manniwood/pg-play/insert-ints
$ go get github.com/jackc/pgx
$ go build

Now you've got everything you need.

In a root terminal, set your consumer hard drive to not cache writes, but to write them to permantent storage immediately. (With most consumer hard drives, this setting will be forgotten after a reboot, so don't worry about wearing out your hard drive fater.):

# ./set-write-through.sh

Next, be sure PostgreSQL is running. Also run this command in a root terminal:

# systemctl start postgresql

Let's run our go command to insert ints into PostgreSQL in a loop. This can be run in a regular user's terminal:

$ ./insert-ints

Now let's return to our root terminal and kill PostgreSQL while it is running:

# ./kill-pg.sh
# systemctl status postgresql

The second command, above, should show us that PostgreSQL was killed.

Also, back in your regular user's terminal, you should see that the go program exited like this:

...
inserted 1880
inserted 1881
inserted 1882
Did not insert 1883

Now let's prove to ourselves that PostgreSQL saved all of our writes to disc:

# systemctl start postgresql
# ./get-last-written.sh
max is:
 max  
------
 1882
(1 row)

count is:
 count 
-------
  1882
(1 row)

The last command should show that our integers were inteed written to disk, and the first one that the go program complained could not be written was in fact not written. (NOTE however that sometimes the transaction can have succeeded but the notification back to the go client did not succeed before we killed PostgreSQL.)