Friday, 8 December 2017

Psql directly from Atom text editor.

Im not a big fun of postgres clients like pgadmin, not sure why, but i always favoured psql over everything.
A friend of mine wrote an atom package that brings psql into atom. Which is awesome. It's tested in linux and (briefly) in osx. In osx i only had to change the path for psql because for some reason it didn't get it from the environment. Feel free to check it out and spam him with any issues you might have.
Link for the package can be found here.

Thanks for reading.

Vasilis Ventirozos
OmniTI Computer Consulting

Monday, 27 November 2017

Monitoring statements in pg10

Recently we migrated our first client to postgres 10.1, the database used to run on 9.2.
Everything went smooth with a total downtime of 12 seconds but obviously monitoring broke so i had to fix that and i thought to share the new statements to save people some trouble.
The statements that broke had to do with the change of xlog -> wal (and location -> lsn) which mostly affected replication monitoring statements. Bellow is what i used to have on PG 9 and what i got for PG 10.

Replication Slot monitoring Postgres 9:

SELECT slot_name, database, active,pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn
AS retained_bytes
FROM pg_replication_slots;

Replication Slot monitoring Postgres 10:

SELECT slot_name, database, active, pg_wal_lsn_diff (pg_current_wal_insert_lsn(), restart_lsn)
AS retained_bytes
FROM pg_replication_slots;

Replication Lag monitoring Postgres 9 :
SELECT pg_xlog_location_diff(pg_current_xlog_insert_location(), flush_location) AS lag_bytes, application_name
FROM pg_stat_replication;

Replication Lag monitoring Postgres 10 :
SELECT pg_wal_lsn_diff (pg_current_wal_flush_lsn(), flush_lsn ) AS lag_bytes, application_name
FROM pg_stat_replication;

Thanks for reading.

Vasilis Ventirozos
OmniTI Computer Consulting

Wednesday, 30 August 2017

PostgreSQL on ZFS

What is ZFS. (a paste from wikipedia)

ZFS is a combined file system and logical volume manager designed by Sun Microsystems. The features of ZFS include protection against data corruption, support for high storage capacities, efficient data compression, integration of the concepts of filesystem and volume management, snapshots and copy-on-write clones, continuous integrity checking and automatic repair, RAID-Z and native NFSv4 ACLs.

Advantages of ZFS.

Data integrity guaranteed.
ZFS has volume management on filesystem level, this enables copy on write. When a data block changed, it will change its location on the disk before the new write is complete. If you have a crash, that data would be damaged. ZFS does not change the location of the data until the write is verified, which means that your data is safe during a crash. ZFS also use checksums to ensure that all data is correct, every write is tested. ZFS doesn't just protect data with copy on write, it also offers additional protection by having an additional RAID level. RAID-Z3 which allows a maximum of three disk failures in a ZFS pool. Traditional RAID levels only allow two disk failures per volume. ZFS offers RAID mirrors who are usually composed of a single disk keeping its own copy. With a multi-disk mirror you can have multiple copies, which adds levels of data integrity not found in traditional RAID setups and it's also great for reads.
ZFS also allows to send writes to individual disks, instead of just the whole RAID volume. Because of this, ZFS can spread writes which is speeding up write performance. It also has algorithms that ensure that the most frequently used data blocks are kept in the fastest media, meaning that you can have just a few SSD drives as cache.

Compression can be enabled on each dataset , its a transparent operation where zfs will compress and uncompress data on the fly. Because cpu operations are usually cheaper than IO and considering that compression algorithms are much smarter than they used to be the cpu overhead is usually not even noticeable, combined with the gain of compression  this is an extremely useful feature for databases. The supported compression algorithms are: LZJB, LZ4, ZLE, and Gz (1-9). Today we are going to play with LZ4.
A snapshot is a read-only copy of a file system or volume. Snapshots can be created almost instantly, and they initially consume no additional disk space within the pool. However, as data within the active dataset changes, the snapshot consumes disk space by continuing to reference the old data, thus preventing the disk space from being freed.

And after this rather long intro on ZFS (that may or may not have pasted descriptions from oracle) its time to put it to work for a PostgreSQL database. In my previous post I went through how to install postgres on a openZFS volume on Ubuntu so in this post i will be using a vm that already has a ZFS volume. 
Enabling compression (LZ4).

> zfs set compression=lz4 pg_zfs

Where pg_zfs is the name of the volume.

And yeah, that's pretty much it.

Now i will initdb a new cluster create a table and insert some data :

create table test_compression (id int , name text default 'Evol' , surname text default 'Monkey');
insert into test_compression (id) select generate_series (1,5000000);
create unique index id_unique_idx on test_compression (id);
create index name_idx on test_compression (name);
checkpoint ;
select pg_size_pretty(pg_total_relation_size('test_compression'));
 426 MB
now as root :

# zfs get compressratio pg_zfs
pg_zfs  compressratio  3.38x  -

Which is pretty neat!

Using snapshots.
To get a snapshot of your database directory something like this would work. as root :

psql -c "select pg_start_backup('zfs_snapshot');" -U vasilis postgres && zfs snapshot pg_zfs@blogpost && psql -c "select pg_stop_backup();" -U vasilis postgres 

zfs list -t snapshot
pg_zfs@blogpost      0      -   279M  -

To restore, say that we delete the whole data directory :

rm -rf /pg_zfs/pgdata/
killall -9 postgres
zfs rollback pg_zfs@blogpost
/pg_zfs/pgsql/bin/pg_ctl -D /pg_zfs/pgdata/ start
psql -q -t -A -c "select count(*) from test_compression" monkey

All Done!
One thing to keep in mind is that all these operations are nearly instant.

At OmniTI, we use ZFS on OmniOS a lot and for this reason we have developed some scripts that can help with automation, sending snapshots on remote servers etc. These scripts can be found here.
Biggest con of ZFS is that there is no huge acceptability in linux, people are reporting that open-zfs is production ready, but personally i haven't used it with linux. I've seen that Canonical would promote it but there were some legal issues (?). Thing is that and now, with redhat ditching btrfs it's a good opportunity for ZFS to be used more. So if you have test / development environments , maybe you could give it a try and spread the word of zfs-awesomeness.

Thanks for reading.

Vasilis Ventirozos
OmniTI Computer Consulting

Friday, 16 June 2017

PostgreSQL on ZFS with BPF tracing on top.

At OmniTI we love solaris, my personal favourite features are ZFS and DTrace. Unfortunately not many run postgres on solaris so i have decided to implement similar features in linux. Instead of Dtrace i'll install BPF, in-kernel bytecode that can be used for tracing introduced in recent kernels (4.X). 
This post will be a part of a three series post. In this post we'll start with setup, in part #2 with ZFS and how to use it for backups / snapshots. In part #3 we'll dig into BPF a bit more.

Step 1 is to setup a new ubuntu. I setup a VM using ubuntu-16.04.2-server-amd64.iso.

As root :

Add the repo for bcc :
> echo "deb [trusted=yes] xenial-nightly main" | sudo tee /etc/apt/sources.list.d/iovisor.list

sudo apt-get update

Install all necessary and some optional packages :

> apt-get install -y sudo wget apt-transport-https joe less build-essential libreadline-dev \

zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev openssh-server screen git \
binutils bcc bcc-tools libbcc-examples python-bcc zfsutils-linux \
systemtap systemtap-sdt-dev

Make sure kernel is properly compiled :

> cat /boot/config-`uname -r` |grep BPF


Test BCC (stands for BPF Compiler Collection)

> python /usr/share/bcc/examples/tracing/

Tracing... Hit Ctrl-C to end.
     kbytes              : count     distribution
         0 -> 1          : 7        |************                            |
         2 -> 3          : 0        |                                        |
         4 -> 7          : 22       |****************************************|
         8 -> 15         : 19       |**********************************      |
        16 -> 31         : 8        |**************                          |
        32 -> 63         : 6        |**********                              |
        64 -> 127        : 1        |*                                       |
       128 -> 255        : 0        |                                        |
       256 -> 511        : 0        |                                        |
       512 -> 1023       : 1        |*                                       |

Now its time to install postgres on a zfs partition, in my case i had a disk (sdb) attached on my VM :

> fdisk /dev/sdb

Welcome to fdisk (util-linux 2.27.1).

Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table.

Created a new DOS disklabel with disk identifier 0x4226e0bf.

Command (m for help): n

Partition type
   p   primary (0 primary, 0 extended, 4 free)
   e   extended (container for logical partitions)
Select (default p): p
Partition number (1-4, default 1):
First sector (2048-41943039, default 2048):
Last sector, +sectors or +size{K,M,G,T,P} (2048-41943039, default 41943039):

Created a new partition 1 of type 'Linux' and of size 20 GiB.

Command (m for help): w

The partition table has been altered.
Calling ioctl() to re-read partition table.
Syncing disks.

To create the pool :

> sudo zpool create pg_zfs /dev/sdb1

> zpool status

  pool: pg_zfs
 state: ONLINE
  scan: none requested


pg_zfs      ONLINE       0     0     0
 sdb1      ONLINE       0     0     0

errors: No known data errors

> mount |grep pg_

pg_zfs on /pg_zfs type zfs (rw,relatime,xattr,noacl)

> ls /pg_zfs/ -l

total 0

> cd /pg_zfs/

> mkdir pgsql
> mkdir pgdata
> chown postgres:postgres pgsql/
> chown postgres:postgres pgdata/

Now with everything ready compile postgres from source :

> wget -c

> tar zxfv postgresql-10beta1.tar.gz

> cd postgresql-10*

> ./configure --prefix=/pg_zfs/pgsql/ --enable-dtrace
> make -j 4 world
> make -j 4 install-world
> export PATH=$PATH:/pg_zfs/pgsql/bin
> export LD_LIBRARY_PATH=/pg_zfs/pgsql/lib
> export PGDATA=/pg_zfs/pgdata
> initdb
> pg_ctl start

At this point, postgres binaries and datafiles are on zfs. Now to check the probes we have available :

/usr/share/bcc/tools/tplist -l /pg_zfs/pgsql/bin/postgres  |awk {'print $2'}


To make sure tracing works properly, while running a statement on a different terminal :

> sudo /usr/share/bcc/tools/dbslower postgres -p 1208

Tracing database queries for pids 1208 slower than 1 ms...
TIME(s)        PID          MS QUERY
2.729496       1208   2399.665 insert into test select * from generate_series (1,100000);

Thanks for reading.

Vasilis Ventirozos
OmniTI Computer Consulting

Saturday, 10 June 2017

An unusual upgrade

I have mentioned in previous posts that in my 4 years with OmniTI, we've tackled a lot of migrations. Most of them are usually the "typical" procedure. The methodology we use is more or less explained here. Last week we had a usecase for a kind of "unusual" upgrade, a 9.2 compiled with 
"--disable-integer-datetimes" meaning that all datetimes were represented as floating point internally, something that was the default at up to 8.3. This changed at (i think) 8.4 where datetimes were represented as int64 which offers more precision. 
The requirement was to migrate the database to a new one that will use integer datetimes with the minimum possible downtime. Obviously a direct upgrade wouldn't work and pg_dump / restore was not an option so we decided to approach and test this scenario differently.

The general idea is the following :

Upgrade to a 9.6 that was compiled with "--disable-integer-datetimes" and then using something like pglogical or mimeo to replicate to another 9.6 that would use integer datetimes. For this, i used 2 containers and pagila test database to make this simulation as much realistic as i could. In this post i will describe the i steps I followed.

Installed both 9.2 and 9.6 on the same box :

9.2.21 with the following options :
./configure --prefix=/home/postgres/pgsql92/ --disable-integer-datetimes
make -j 8 world
sudo make install-world

9.6.2 with the following options :
./configure --prefix=/home/postgres/pgsql96/ --disable-integer-datetimes
make -j 8 world
sudo make install-world

initiated a new cluster and started 9.2, loaded pagila testdb (schema and data), started the database.
From now on this will act like my production database.

downloaded and installed pglogical 2.0.1 9.6 :
wget -c
uncompress :
tar jxfv pglogical-2.0.1.tar.bz2
compile and install :
make USE_PGXS=1 clean all
sudo PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/home/postgres/pgsql96/bin make USE_PGXS=1 install

Note: i installed 9.6 before upgrading because pglogical requires some changes in the parameters (shown later) and a library loaded, so in order not to restart twice i had it installed before the upgrade.

initiated a new 9.6 cluster so i can upgrade, stopped 9.2 and upgraded 9.2 to 9.6 :

pre upgrade check :
pgsql96/bin/pg_upgrade -b /home/postgres/pgsql92/bin/ -B /home/postgres/pgsql96/bin/ -c -d /home/postgres/pgdata92/ -D /home/postgres/pgdata96/ -v
stopped 9.2 :
-- outage starts --
pgsql92/bin/pg_ctl -D /home/postgres/pgdata92/ stop
pgsql96/bin/pg_upgrade -b /home/postgres/pgsql92/bin/ -B /home/postgres/pgsql96/bin/  -d /home/postgres/pgdata92/ -D /home/postgres/pgdata96/ -v -k

added the following in postgresql.conf :
wal_level = 'logical'
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10
shared_preload_libraries = 'pglogical'
track_commit_timestamp = on

on master hba.conf (and slave, cause why not) added :
host    replication     postgres             trust  
(security was not a concern so "trust" was ok)

started 9.6
-- outage stops --
analyzed 9.6 and cleaned up 9.2
and issued "create extension pg_logical;" to postgres

At this point i had my "production" db upgraded to 9.6 with pglogical installed and everything ready for logical replication.

On the second box that would have postgres 9.6,compiled without the "--disable-integer-datetimes" flag. I installed pglogical with exactly the same way i did for the first box and at this point i was ready to replicate :

on production (provider) I created a new node and added a set with all objects in public schema:
SELECT pglogical.create_node( node_name := 'provider1', dsn := 'host= port=5432 dbname=monkey' );
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

Keep in mind :
ERROR:  table payment_p2007_01 cannot be added to replication set default
DETAIL:  table does not have PRIMARY KEY and given replication set is configured to replicate UPDATEs and/or DELETEs
HINT:  Add a PRIMARY KEY to the table

ALL tables that are going to be replicated need to have a primary key.
after adding a pk to the tables that didn't have one i went to the slave and i did :

Create a node for the subscriber :
SELECT pglogical.create_node(node_name := 'subscriber1',dsn := 'host= port=5432 dbname=monkey');

Started the replication process which sync'd (schema and data): 
SELECT pglogical.create_subscription( subscription_name := 'subscription1',provider_dsn := 'host= port=5432 dbname=monkey', synchronize_structure := true);

To verify that these 2 databases have different storage types :

postgres@old_server:~/pgdata96$ pg_controldata |grep Date
Date/time type storage:               floating-point numbers

postgres@new_server:~/pgdata$ pg_controldata |grep Date
Date/time type storage:               64-bit integers

The database was transferred and from now on replicated.
At this point, if this was the real deal it would be preferred to first transfer the schema and then start replication just to be sure that all objects will transfer but in my case i didn't really care about that.

Keep in mind that since postgres 10 floating point datetimes are no longer supported. If you tried to compile it it would give :

postgres@a1bdb0750dc5:~/postgresql-10beta1$ ./configure --disable-integer-datetimes
checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
checking which template to use... linux
configure: error: --disable-integer-datetimes is no longer supported 

This is a pretty specialized scenario, but from what i saw there are some databases out there having their date times stored as floating points that hopefully could benefit from this migration procedure.

Thanks for reading
-- Vasilis Ventirozos

Friday, 9 June 2017

Tip for faster wal replay on a slave

I've been in situations where i need a slave db to replay a lot of wal files fast, and by a lot i mean tens of thousands. This could happen because of a reporting database refreshing or simply because a slave was down for an extended period of time. It's known that lowering shared_buffers speeds up wal replay for obvious reasons, but by how much ?

I did a benchmark on an old server and the results are interesting :

With 32GB of shared buffers and with 6390Mb of wals (1840 wal files)
it took 1408 seconds to complete the replay.

With 64MB of shared buffers and with 6510Mb of wals (1920 wal files)
it took 1132 seconds to complete the replay.

My test was done by stopping the slave, inserting 50 mil rows to a test table, wait for the wal transfer to complete, then stop the master and start the slave and watch OmniPITR logs.

The performance gain in wal replay was about 20% in postgres 10beta1 which doesn't sound bad, especially in times of need.

Thanks for reading
-- Vasilis

Friday, 19 May 2017

Reusing an old master (as slave) after a switchover

Todays blogpost is old news but it's not very well known and it has to do with how to add an old master after a slave has been promoted. Fujii Masao explained the situation in his patch back in the day.

So in todays post i will be demonstrating a combination of replication slots for retaining the writes on a promoted slave and how to re-introduce an old master to the replication.

Say that we have a master-slave setup, stop the master gracefully and create and activate a replica slot on the slave :

monkey=# SELECT * FROM pg_create_physical_replication_slot('this_is_a_replica_slot');
       slot_name        | xlog_position
 this_is_a_replica_slot |
(1 row)

postgres@bf9823730feb:~$ pg_receivexlog -D . -S this_is_a_replica_slot -v -h -U repuser
pg_receivexlog: starting log streaming at 0/4000000 (timeline 1) ^C
pg_receivexlog: received interrupt signal, exiting
pg_receivexlog: not renaming "000000010000000000000004.partial", segment is not complete
pg_receivexlog: received interrupt signal, exiting

This way all the changes after the slave promotion will be retained. Now , lets promote the slave and check the replication slot status.

postgres@bf9823730feb:~$ pg_ctl promote
server promoting

postgres@bf9823730feb:~$ psql -c "SELECT slot_name, database,active,  pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn) AS retained_bytes FROM pg_replication_slots;" postgres
       slot_name        | database | active | retained_bytes
 this_is_a_replica_slot |          | f      |           9056

As you can see , the new master is now retaining all writes. Now, to the old master, lets make a recovery.conf that looks like this :

standby_mode = 'on'
primary_conninfo = 'user=repuser host= port=5432 application_name=a_slave'
trigger_file = '/home/postgres/pgdata/finish.recovery'
primary_slot_name = 'this_is_a_replica_slot'
recovery_target_timeline = 'latest'

where host is obviously the new master. 
Start the old master and you should see something similar to:

LOG:  entering standby mode
LOG:  consistent recovery state reached at 0/5000098
LOG:  database system is ready to accept read only connections
LOG:  invalid record length at 0/5000098: wanted 24, got 0
LOG:  fetching timeline history file for timeline 2 from primary server
LOG:  started streaming WAL from primary at 0/5000000 on timeline 1
LOG:  replication terminated by primary server
DETAIL:  End of WAL reached on timeline 1 at 0/5000098.
LOG:  new target timeline is 2
LOG:  restarted WAL streaming at 0/5000000 on timeline 2
LOG:  redo starts at 0/5000098

This would also work if you had archiving enabled instead of replication slots. Given that you have plenty of available disk space on the new master, it should be fine to keep the old master down for any reasonable amount of time and re-introduce it to the replication without issues.

The only 2 things that you have to make sure is to shutdown postgres on old-master gracefully and that the slave has caught up before the promotion.

Thanks for reading.

Tuesday, 9 May 2017

PostgreSQL statistics as data

 Postgres provides a lot of information when it comes to statistics. Only problem is that all the statistics are frozen in time the moment you query the system catalog views. Sometimes you need to see whats happening over time or in the past,and this is easily solvable with a script and a crontab entry. At OmniTI we use a project called system_monitoring a lot. This is basically a perl scheduler that runs as a deamon exporting useful information from postgres (and more) to tab delimited text files for later investigation. If you have decent one liner skills this is very helpful: you can answer questions about what happened yesterday night, what was running, who locked who etc. It's very good but data analysis can be a pain in the ass sometimes, especially if you need to reimport to a db and analyze with SQL. So I decided to approach this in a different way, by storing the statistics to a schema. Thats why i wrote statz
Statz is a very simple python script that will gather all postgres statistics for a period of time in an interval, aggregate them and keep raw and aggregated data in its own schema. The raw data that it keeps are just a now(),* from the most important statistics tables :

  • pg_stat_user_indexes
  • pg_stat_activity
  • pg_locks
  • pg_stat_user_tables
  • pg_stat_database
  • pg_stat_bgwriter
But its relatively easy to add more, even application specific data.

How to use :

statz needs a database to connect (for now it's the same with the database that monitors), an interval and a total duration (interval and total duration should be in seconds).
So, something like:

./ -c dbname=monkey -i 5 -t 120 

Would initialize a schema called statz (this for now is hardcoded)
if schema exists it will drop it (cascade) and recreate it.
In a loop that will last for <total time> , sleeping for <interval> it will keep output of select now(),* from the tables listed above, populating the following tables :


In the same loop it will aggregate and populate the following aggregated tables.

The aggregated tables look like this :
for the database:

snap_date                 | 2017-05-08 16:35:17
interval                  | 00:00:02
step                      | 00:00:10
datname                   | monkey
commits                   | 863
rows_returned             | 21761
rows_fetched              | 9102
rows_inserted             | 1662
rows_updated              | 2251
rows_deleted              | 0
blocks_read               | 89
blocks_hit_cached         | 57572
commits_per_sec           | 432
rows_returned_per_sec     | 10881
rows_fetched_per_sec      | 4551
rows_inserted_per_sec     | 831
rows_updated_per_sec      | 1126
rows_deleted_per_sec      | 0
blocks_read_per_sec       | 45
blocks_hit_cached_per_sec | 28786
txn_per_sec               | 432
cache_hit_ratio           | 99.8456

and for each table :

snap_date                  | 2017-05-08 16:35:49
interval                   | 00:00:02
step                       | 00:00:42
table_name                 | public.pgbench_tellers
seq_scans                  | 0
seq_rows_read              | 0
index_scans                | 5807
index_rows_fetched         | 5807
rows_inserted              | 0
rows_updated               | 5807
rows_deleted               | 0
rows_hot_updated           | 5759
live_row_count             | 0
n_dead_tup                 | 634
seq_scans_per_sec          | 0
seq_rows_read_per_sec      | 0
index_scans_per_sec        | 5807
index_rows_fetched_per_sec | 2904
rows_inserted_per_sec      | 0
rows_updated_per_sec       | 2904
rows_deleted_per_sec       | 0
rows_hot_updated_per_sec   | 2880
live_row_count_per_sec     | 0
n_dead_tup_per_sec         | 317

I have also included a couple of views that are useful for fast monitoring :


Keep in mind that the numbers are being calculated using the last inserted row and since its in the same loop with populating the historical tables the information will be refreshed every <interval> 
Which makes this a great tool for things like :

watch -n 2 "psql -x -c 'select * from statz.database_activity_agg order by step desc limit 1;' monkey"

You can even create and send graphs like :


Statz is gathering a lot of data so it's not meant to run 24/7. Thats why it has an interval and a total time and thats why it will destroy old statistical data when re-run
Say that you observed that during night, queries are getting slower or wal file generation is increased or any abnormality (locks / unknown statements / rollbacks etc). You can schedule statz to run for this period of time and analyze data next morning. Keeping snapshots of pg_stat_activity is also helpful for slow query investigation where you can do things like :

select (snap_date - xact_start) as age,user,query from backend_activity order by 1 desc;

This script is also useful for analyzing application specific usage patterns, To get statistics from benchmarks and fine tune checkpoints , missing indexes etc..

And because it all happens in a single transaction, snap_date can be used as a key linking all statz tables.
This means that if you see database doing a lot of commits for example, you can use snap_date to find the busy table in statz.table_activity or you can check locks or statements run at that specific time in the rest of the tables.

link for statz can be found here.

Next steps

I plan on the following things :
  • Refactor schema / statements for performance.
  • More aggregating statements for the rest of the tables.
  • Possibly (live) graphs or maybe a dashboard.
  • Possibly rewrite some parts for better general use.
  • Lately im reading a book about machine learning in python i might experiment with that a bit.

Thanks for reading.

Wednesday, 25 January 2017

PostgreSQL upgrades - Methodology

 Yesterday, i came across a conversation in postgres slack channel, someone was interested in upgrading his database and i realised that there is a pattern i see all the time. Those who are interested in upgrading major postgres versions, only ask questions about pg_upgrade. Don't get me wrong, pg_upgrade is awesome its well documented, and it explains things much better than i can. 
During my time in OmniTI we've done many... MANY upgrades some of them pretty challenging,
so i have come to realise that the upgrade tool is not as important as the plan and the methodology, especially during an upgrade or a migration. For this reason this post won't be about how to use pg_upgrade, instead it will be all about methodology, minimising downtime and reducing risk to almost zero.
For this post i will be showing the steps i would follow in order to upgrade an 8.4.22 to 9.6.1. I will be using docker containers using a dockerfile that i wrote for lab use (more info about this docker file can be found in this post) I will include a couple of "traps" that could potentially cause trouble to my upgrade just so i can show how to work around them.
A typical postgres installation would have one master and one or more slaves and maybe a dev/test server. 
For the needs of this post , i will concentrate on the master and on the test. As a matter of fact the test server will be much more important because thats where we will develop an upgrade plan.
So,  we need 2 containers :
An upgrade target host
An upgrade plan development server (aka a test machine)

So , lets assume that we have a master with the following objects that we want to upgrade :

postgres=# select version();

 PostgreSQL 8.4.22 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 row)

postgres=# \dt

                 List of relations
 Schema |          Name          | Type  |  Owner
 public | another_small_table    | table | postgres
 public | big_reporting_table    | table | postgres
 public | really_important_table | table | postgres
 public | small_oltp_table       | table | postgres
(4 rows)

postgres=# \dv

              List of relations
 Schema |       Name       | Type |  Owner
 public | my_stat_activity | view | postgres
(1 row)

First order of business would be to create a replica (or to dump and restore if size permits) on the test server that should run on exactly the same postgres version. 

Next, you should check if the test server has the same configuration and if all contrib modules are installed (not calling them extensions because ... 8.4.x).
So compare postgresql.conf on these 2 servers and compare output of ls -l <postgres binary installation directory>/lib. 
In my case i didn't have pg_buffercache , so i installed it. 
At this point it is a good idea to write down the extensions you would need for 9.6, in my case just pg_buffercache.

To sum up, the steps are :

On the test server :

  1. Install exactly the same version as production
  2. Transfer the configs from production
  3. Install all the extensions that exist in production
  4. Create a replica from production
  5. Promote the replica to be a standalone master

At this point we should have a test environment that looks like production. From this moment on, everything should be timed and documented.

On the Test server

  1. Install the version you want to upgrade to, in my case 9.6.1 and don't put its binaries in the path (yet) while documenting and timing all the steps. DO NOT overwrite your old binaries.
  2. Port your configuration file to the new version, DO NOT copy the old config fileIts not just that some parameters have changed, or that some parameters are not compatible from an old to a new pg version, there are new settings commented out that are important and new defaults that you should tune before putting it to production.
  3. Initdb a cluster using 9.6 binaries and test your configuration files, remember to start it on a different port than 8.4.  Keep the config files somewhere safe ideally at the same place where you develop your migration plan
  4. Stop 8.4 and 9.6 clusters
  5. Delete the previously created (for config test) cluster and re-initialize a new $PGDATA directory using the 9.6 binary. This will be your upgraded $PGDATA directory
  6. Test pg_upgrade (9.6 binary) like this : 

pg_upgrade -b ~/pgsql8/bin/ -B ~/pgsql9/bin/ -d ~/pgdata8 -D ~/pgdata9 -c
Performing Consistency Checks
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for roles starting with 'pg_'                      ok
Checking for invalid "line" user columns                    ok
Checking for large objects                                  ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

*Clusters are compatible*

everything looks good,  lets try to upgrade :

time pg_upgrade -b ~/pgsql8/bin/ -B ~/pgsql9/bin/ -d ~/pgdata8 -D ~/pgdata9 -k

Performing Consistency Checks
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for roles starting with 'pg_'                      ok
Checking for invalid "line" user columns                    ok
Checking for large objects                                  ok
Creating dump of global objects                             ok
Creating dump of database schemas
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the

new cluster before continuing.

Performing Upgrade

Analyzing all rows in the new cluster                       ok
Freezing all rows on the new cluster                        ok
Deleting files from new pg_clog                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Setting oldest multixact ID on new cluster                  ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster

Consult the last few lines of "pg_upgrade_dump_11564.log" for

the probable cause of the failure.
Failure, exiting

real 0m7.011s

user 0m0.000s
sys 0m0.050s

This is why we HAVE to test and time things in a test server. If we check the log we'll see :

pg_restore: [archiver (db)] Error from TOC entry 142; 1259 16390 VIEW my_stat_activity postgres

pg_restore: [archiver (db)] could not execute query: ERROR:  column pg_stat_activity.procpid does not exist
LINE 14: ...CT (now() - pg_stat_activity.query_start) AS age, pg_stat_ac...

This was an intended to happen error, just to show things can go wrong, and that you don't want these kinds of things to happen in production. Its just a view that uses columns from pg_stat_activity that were renamed later on. Same thing can happen from some old extensions (like tsearch) so one more reason to test.

In order to fix this i would get the view definition, i would adjust it to work in 9.6, i would test it and when i felt confident that the view works I would write a script that would look something like :

pg_ctl -D ~/pgdata8 start && psql -c "drop view my_stat_activity" postgres && pg_ctl -D ~/pgdata8 stop && rm -rf ~/pgdata9/* && ~/pgsql9/bin/initdb -D ~/pgdata9/ && time pg_upgrade -b ~/pgsql8/bin/ -B ~/pgsql/bin/ -d ~/pgdata -D ~/pgdata9 -k && cp ~/configs9/*.conf ~/pgdata9/ && ~/pgsql9/bin/pg_ctl -D ~/pgdata9/ start  && psql -c "create view my_stat_activity as select now()-query_start as age,pid,state, query from pg_stat_activity where query !='<IDLE>' order by 1 desc;" postgres && psql -c "create extension pg_buffercache" postgres

Don't pay too much attention to the code above since its not even tested, just keep a similar mindset that its a really good idea to automate / script everything, scripts are easy to test, easy to predict and easy to keep and change.

The steps for the code above would be :

  • start pg8
  • drop problematic view
  • stop pg8
  • empty pg9 data dir
  • initdb pg9 data dir
  • upgrade (this should be timed)
  • copy configs to pg9 config
  • start pg9
  • create view
  • create extensions

Upgrade is not done though. Next step would be to analyze the database and this can take a long time!

The last 2 steps would be to delete old cluster and analyze the new one (because optimizer statistics are not kept by pg_upgrade). pg_upgrade provides 2 scripts that could be used for these steps : can be run now, but before you run analyze_new_cluster you could prioritise work and maybe even run analyze commands in parallel. My dummy database has the following tables :

                 List of relations

 Schema |          Name          | Type  |  Owner
 public | another_small_table    | table | postgres
 public | big_reporting_table    | table | postgres
 public | really_important_table | table | postgres
 public | small_oltp_table       | table | postgres

At this point i could say that i want really_important_table analyzed asap followed by another_small_table and small_oltp_table. big_reporting_table can be last or run on a different process. My suggestion is to prioritise important tables first by creating one or more scripts. 

Remember that we are still working on test server so test and time these analyze scripts. eg :

time psql -c "analyze really_important_table;" postgres

time psql -c "analyze small_oltp_table;" postgres
time psql -c "analyze another_small_table;" postgres
time psql -c "analyze big_reporting_table;" postgres

In my opinion some of the most important things in an upgrade are :

  • Minimising risk of losing data
  • Minimising downtime
  • Providing accurate estimations on downtime to whoever needs to know.
  • Providing accurate estimations on analyze. Assuming you'll use hard links (-k) This is the step that will take the most, and during the time that the database lacks statistics, performance will be bad. (READ HOW -k WORKS BEFORE USING IT)
  • Not having any surprises.

I can't emphasis enough on the importance of scripting, timing and testing any upgrade procedure. In my opinion it's more important that the production upgrade itself. Do these steps again and again till you are confident that your procedure :

  • Has been tested well enough
  • Has been documented well enough
  • Can't go wrong
  • Is so easy that you could even train your dog to do it.
This upgraded test database should now be used to thoroughly test the application against the new pg version.

When you start upgrading production, its a good idea to keep two things in mind

  • Keep a backup handy. If the database is large, this is not as easy as it sounds and restoration time can be a lot, if you can afford the safety of a backup though, do it !!
  • ALWAYS have a rollback plan. Make sure a replica of the master is up that has no replication lag, bring it down at the time of the upgrade and be ready to move the application there, just in case something goes south. Even if you don't have a replica in your stack, the upgrade is a good opportunity to get one, even temporarily until the upgrade is done. (protip: if you failover your application to a slave keep connections on your failed-to-upgrade-master restricted making sure no one will commit any transaction).

Thanks for reading.

-- Vasilis

Managing xid wraparound without looking like a (mail) chimp

My colleague Payal came across an outage that happened to mailchimp's mandrill app yesterday, link can be found HERE . Since this was P...