Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Managing Postgres in a devops environment

Communication and collaboration between developers and systems administrators represent a key aspect of the "devops" cultural movement that has been growing popularity in the last few years.
by

Gabriele Bartolini

on 31 October 2014

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Managing Postgres in a devops environment

Development
INPUT
OUTPUT
Operations
INPUT
OUTPUT
Dev

vs

Ops
devops
Lack of communication
Lack of cooperation
No integration
Two sealed compartments
Misunderstandings
Conflicts
leading to ...
Work hatred
Decreased productivity
blame game
"How long is it taking you to deliver that bloody feature?"
"Our production system does not support that library"
"Nobody asked us about security and backup policies"
The third way
The
Third Way
is about creating a culture that fosters two things:
continual experimentation
, taking risks and learning from failure; and
understanding that
repetition
and
practice
is the
prerequisite to mastery
.
The second way
The
Second Way
is about creating the right to left feedback loops. The goal of almost any process improvement initiative is to shorten and amplify feedback loops so necessary corrections can be continually made.
The first way
The
First Way
emphasises the performance of the entire system, as opposed to the performance of a specific silo of work or department.
"Never allow local optimisation to create global degradation"
http://itrevolution.com/manifesto/
Some core elements of "devops"
Versioning
Team work
Automation
Repeatability
Testing
Monitoring
Kaizen
culture
Agile management
A very short intro to devops
Part One
devops team ~ sports team
Kanban and SCRUM
You can't manage what you can't see
Where does a Postgres DBA fit?
dev
dba
ops
dev
dba
SQL
DB Modelling
Change Management
Query Explain
Query Monitoring
PostgreSQL new features
GIS
Extensions
Testing
dba
System/DB Setup
System/DB Tuning
System/DB Updates
Configuration management
Monitoring and alerting
ops
Work together on
Work together on
then comes the boss ...
"Yes! Our new version of the product is ready to go live!"
after 6 months of hard development work ...
to suddently discover that ...
and that ...
A DBA perfectly fits in a devops environment
Mission:
promote a database culture in the team
Some core devops technologies
GIT
Vagrant
Puppet / Chef
Jenkins
Vagrant is an open source tool that helps to create, configure and manage virtual environments.

Normally used with VirtualBox
Works also with EC2, VMWare, ...
http://www.vagrantup.com/
Puppet and Chef are configuration management tools that work together with Vagrant for machine provisioning.
Dev, QA and Deploy can happen on identically configured servers!
Postgres devops "must haves"
Alerting (Icinga)
Trending (Munin)
pgBadger for log post-analysis
pg_stat_statements extension
Automated backups (e.g. Barman)
pgTAP
http://www.pgtap.org/
Suite of database functions that allow to write and execute unit tests (Test Anything Protocol) on a Postgres database.
BEGIN;
SELECT plan( 18 );

SELECT has_table( 'domains' );
SELECT has_table( 'stuff' );
SELECT has_table( 'sources' );
SELECT has_table( 'domain_stuff' );

SELECT has_column( 'domains', 'id' );
SELECT col_is_pk( 'domains', 'id' );
SELECT has_column( 'domains', 'domain' );

SELECT has_column( 'stuff', 'id' );
SELECT col_is_pk( 'stuff', 'id' );
SELECT has_column( 'stuff', 'name' );

SELECT has_column( 'sources', 'id' );
SELECT col_is_pk( 'sources', 'id' );
SELECT has_column( 'sources', 'name' );

SELECT has_column( 'domain_stuff', 'domain_id' );
SELECT has_column( 'domain_stuff', 'source_id' );
SELECT has_column( 'domain_stuff', 'stuff_id' );
SELECT col_is_pk(
'domain_stuff',
ARRAY['domain_id', 'source_id', 'stuff_id']
);

SELECT can_ok(
'insert_stuff',
ARRAY[ 'text', 'integer[]', 'integer', 'integer' ]
);

SELECT * FROM finish();
ROLLBACK;
Transactional DDL
Wrap DDL inside an atomic transaction
Schema changes / migrations
Versioning and review
Pay attention and test for:
locking (concurrency)
space
time
EXTENSIONs
With every release, Postgres better addresses the needs of
Developers
,
DBAs
and
Sysadmins
This talk summarises
best practices
,
technologies
and
techniques
that our team at
2ndQuadrant Italy
has been successfully adopting in the last 2 years
Postgres can be a candidate
database technology
for those dynamic businesses with very high requirements of
delivery
and
quality
of their services/products
Managing PostgreSQL in a devops environment
Marco Nenciarini
PostgreSQL consultant
2ndQuadrant Italy
marco.nenciarini@2ndQuadrant.it
@mnencia
Gabriele Bartolini
PostgreSQL consultant
2ndQuadrant Italy
gabriele.bartolini@2ndQuadrant.it
@_GBartolini_
www.2ndQuadrant.com
Part Two
devops with Postgres
Installation
git clone git@github.com:2ndquadrant-it/vagrant-puppet-postgresql.git
cd vagrant-puppet-postgresql
bundle install
bundle exec librarian-puppet install
How to setup an Ubuntu Linux virtual machine with PostgreSQL 9.3
Controlling the virtual machine
vagrant up
vagrant status
vagrant halt
vagrant destroy
https://github.com/2ndquadrant-it/vagrant-puppet-postgresql
Requirements
Vagrant
VirtualBox
Ruby (>= 1.9)
Connecting to Postgres
Vagrant forwards the 5432 port directly on the host machine. You can therefore connect to Postgres on "localhost".
VAGRANTFILE_API_VERSION = "2"
Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
config.vm.box = "precise64"
config.vm.box_url = "
http://files.vagrantup.com/precise64.box
"

config.vm.define "database" do |cfg|
cfg.vm.hostname = "database"

cfg.vm.network :forwarded_port, guest: 5432, host: 5432

cfg.vm.provision :puppet do |puppet|
puppet.module_path = "modules"
puppet.manifests_path = "manifests"
puppet.manifest_file = "site.pp"
end
end

end
Puppet for provisioning
Port forwarding
We use the following Puppet module:
https://forge.puppetlabs.com/puppetlabs/postgresql
Vagrantfile
node database {

# HINT: Use a more secure password here.
$postgres_password = 'password'

# Manage packages with apt (required on Debian/Ubuntu systems by
# manage_package_repo option of postgresql::globals class)
class { 'apt':
always_apt_update => true,
}

# Forcibly activate the en_US.UTF-8 locale. Needed to have
# UTF8 encoding in PostgreSQL databases.
file { '/etc/default/locale':
ensure => 'file',
owner => 'root',
group => 'root',
content => "LANG=en_US.UTF-8\n",
} ->

# Install PostgreSQL 9.3 server from the PGDG repository
class { 'postgresql::globals':
version => '9.3',
manage_package_repo => true,
encoding => 'UTF8',
locale => 'en_US.utf8',
} ->
class { 'postgresql::server':
ensure => 'present',
listen_addresses => '*',
ip_mask_deny_postgres_user => '0.0.0.0/32',
ip_mask_allow_all_users => '0.0.0.0/0',
postgres_password => $postgres_password,
}

# Install contrib modules
class { 'postgresql::server::contrib':
package_ensure => 'present',
}

}
manifests/site.pp
You can wrap your tables and objects in a package, aka EXTENSION:

CREATE EXTENSION myapp;

You can perform atomic upgrades using:

ALTER EXTENSION myapp UPDATE TO '1.1';
Extensibility always been a key property
9.1 introduces the EXTENSION framework
Extension = Package of related database objects:
data types
functions
operators
tables
Manage versions and extension dependencies
More info on extensions at http://www.postgresql.org/docs/9.3/static/extend-extensions.html
Icinga
Munin
pgBadger
pg_stat_statements
Barman
http://www.pgbarman.org/
Protect from data loss
Continuous backup and PITR
Staging environments with real data
dev
dba
ops
devops
is a work culture movement which fosters the creation of a prosperous and collaborative working environment, focused on team results and professional growth.

PostgreSQL, as an open source project, perfectly fits in the Kaizen culture of continuous learning and improvement.
What a great job we have!
Whether your official title is "PostgreSQL DBA" or "Developer" or "System Administrator" always talk and listen to your colleagues. Every day.
Copyright (c) 2014, 2ndQuadrant Italia


http://creativecommons.org/licenses/by-nc-sa/3.0/deed.it

Attribuzione - Non commerciale - Condividi allo stesso modo 3.0 Unported (CC BY-NC-SA 3.0)

Slides publicly available at https://prezi.com/user/gbartolini
Started adopting Kanban in Sept 2013
Lead time from 16 days to 7.5 days in the first 3 months
Gene Kim
me (Gabriele)
The first IT manager who successfully applied Kanban and the Theory of Constraints (TOC) to software engineering
Dragos Dumitriu
http://munin-monitoring.org/
http://www.icinga.org/
http://github.com/dalibo/pgbadger
Full transcript