upgrade to Postgres 9.3

[chadwhitacre]

Suggested by @jacobian on Twitter: Gets us materialized views, which came up in code review for #1155.

[chadwhitacre]

It's not released yet (though it's available on Heroku Postgres). We're currently on 9.1.

[chadwhitacre]

This is released now:

http://www.postgresql.org/about/news/1481/

[chadwhitacre]

Are we comfortable upgrading to 9.3? Or do we want to wait for 9.3.1 or 9.3.2?

[chadwhitacre]

9.3.1 is out as of yesterday: http://www.postgresql.org/about/news/1487/.

[chadwhitacre]

So we should do this now:

https://devcenter.heroku.com/articles/heroku-postgres-follower-databases#database-upgrades-and-migrations-with-changeovers

[chadwhitacre]

@zwn Wanna take this one?

[zbynekwinkler]

Postgres 9.3 is still available only as public beta https://postgres.heroku.com/blog/past/2013/9/9/postgres_93_now_available/. Let's wait of official release because upgrading from beta to release would have to be done through backup and not fork/follow.

[zbynekwinkler]

I have marked this issue as blocked while we wait for non beta heroku postgres release.

[chadwhitacre]

Let's wait of official release

Agreed. Also, I guess we're really waiting for 9.4 now, per #1549 (comment)? /me edits ticket title

[zbynekwinkler]

I think it is worth upgrading to 9.3 since it will give us pg_stat_statements which in turn enables things like https://github.com/will/datascope. Ref. #1571

[chadwhitacre]

Fair enough. I've changed the ticket back to 9.3.

[zbynekwinkler]

Upgrading to 9.3 will also give us native json datatype. The hstore we are using right now supports only text key and values so this is not a problem in psycopg2 - I am responding to gittip/elsewhere/init.py#L129.

[zbynekwinkler]

And index only scans.

[zbynekwinkler]

https://postgres.heroku.com/blog/past/2013/11/6/postgresql_93_now_ga/

[chadwhitacre]

https://postgres.heroku.com/blog/past/2013/11/11/heroku_postgres_20/

[zbynekwinkler]

@whit537 I wanted to do this tonight but I am not allowed to:

heroku addons:add heroku-postgresql:standard-yanari --follow HEROKU_POSTGRESQL_ROSE_URL
Adding heroku-postgresql:standard-yanari on gittip... failed
 !    You do not have permission to provision paid resources for gittip.
 !    Only the app owner, vendors@gittip.com, can do that.

That would be the first step from https://devcenter.heroku.com/articles/heroku-postgres-follower-databases#database-upgrades-and-migrations-with-changeovers.

[chadwhitacre]

I get an error trying to run that command:

$ heroku addons:add heroku-postgresql:standard-yanari -a gittip --follow HEROKU_POSTGRESQL_ROSE_URL
Adding heroku-postgresql:standard-yanari on gittip... failed
 !    Standard tier plans only support Postgres version 9.3
$

[zbynekwinkler]

According to https://devcenter.heroku.com/articles/heroku-postgresql#version-support 9.3 should be the default. Could you try adding --version 9.3?

[chadwhitacre]

$ heroku addons:add heroku-postgresql:standard-yanari -a gittip --follow HEROKU_POSTGRESQL_ROSE_URL --version 9.3
Adding heroku-postgresql:standard-yanari on gittip... failed
 !    Can only follow to same version (attempted 9.3 for 9.1 database)
$

[zbynekwinkler]

😱 We will have to take the site down, dump the database, restore to 9.3 and bring it back online. I'll read up on how to do this.

[chadwhitacre]

We would have had a brief downtime w/ the follower strategy anyway. Not the end of the world.

[chadwhitacre]

Received on support@gittip.com:

A bug has been found in the version of Postgres running on your database HEROKU_POSTGRESQL_ROSE_URL on application gittip, and it is therefore at higher risk of data corruption.

To reduce this risk, you must upgrade to a later version of Postgres before Tuesday, Jan 07 2014. To perform this upgrade at a time convenient to you, please perform a follower based database changeover as described on our Dev Center [1].

If you are unable to perform maintenance before Tuesday, Jan 07 2014, we will do it on your behalf.

Please let us know if you have any questions by replying to this email.

Thanks,

Heroku Postgres Team

[1] https://devcenter.heroku.com/articles/heroku-postgres-follower-databases#database-upgrades-and-migrations-with-changeovers

[chadwhitacre]

We are commencing maintenance on your database HEROKU_POSTGRESQL_ROSE_URL on application gittip. This maintenance will stop your Postgres instance, update the binaries, and restart Postgres. The entire process should take less than five minutes.

You may open a direct help ticket with the Heroku Postgres team by replying to this email.

Heroku Postgres Team

And then ...

Maintenance has concluded on your database HEROKU_POSTGRESQL_ROSE_URL on application gittip.

Please contact us with any questions.

Heroku Postgres Team

[chadwhitacre]

We're now on 9.1.11. We were on 9.1.9 before.

[zbynekwinkler]

I have everything running locally on 9.3.2 and so far nothing seems broken.

[zbynekwinkler]

I have loaded the heroku 9.3.2 instance with data and run local gittip against it. All working. About to do 'the real thing'. The steps I am going to take are:

1. maintenance:on
2. pgbackups:capture
3. pgbackups:restore to 9.3.2
4. promote 9.3 to DATABASE_URL
5. maintenance:off

I am following https://devcenter.heroku.com/articles/upgrade-heroku-postgres-with-pgbackups and it should not take more then a couple of minutes.

[zbynekwinkler]

The backup id for the switchover is b062.

[zbynekwinkler]

We are now on 9.3.2

[zbynekwinkler]

@whit537 I think the original 9.1 database can now be removed so that it does not incur further charges.

[tshepang]

\0/

[zbynekwinkler]

I've found one more article nicely covering all aspects of heroku backups: https://devcenter.heroku.com/articles/heroku-postgres-data-safety-and-continuous-protection

[chadwhitacre]

I think the original 9.1 database can now be removed so that it does not incur further charges.

Done. !m @zwn