![Page 2: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/2.jpg)
About Steve
Over 10 Years of PostgreSQLMaintainer of Slony-I replication engineOccasional patch reviewer and contributorOpenStreetMap Contributor (stevens)http://scanningpages.wordpress.com
CCBYSA OSM & Contributors
![Page 3: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/3.jpg)
Slony-I Streaming Replication
Topics For Today (or at least the next 20 minutes)
![Page 4: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/4.jpg)
Why Replicate ?
http://www.flickr.com/photos/eamoncurry/6072966411/
![Page 5: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/5.jpg)
Failover
![Page 6: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/6.jpg)
Load Balancing
![Page 7: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/7.jpg)
Step 1Map out the problem you want to solve
Recovery
Reporting
Splitting the load
Distributed Data
![Page 8: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/8.jpg)
Do I need disaster recovery ?
Do I need to replicate my entire database ?
Am I replicating between multiple versions of PostGIS ?
Do I need to load balance big spatial queries ?
Do I need to spread my write load ?
![Page 9: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/9.jpg)
Replication Patterns
![Page 10: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/10.jpg)
Db1(master) GIS App
Db2(slave)
Db3(slave)
Data Inserts
Spatial Queries
Load Balanced Reads
![Page 11: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/11.jpg)
Db1 order app
routing app
trucks pickups
Db2trucks pickups
Split Applications
![Page 12: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/12.jpg)
Db1 Application
Db2Map Render
Map Rendering
Db2
![Page 13: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/13.jpg)
What is important to you
![Page 14: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/14.jpg)
Slony-I
http://www.slony.info
![Page 15: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/15.jpg)
Slony-IKey Features
Multiple Origin nodes (for different tables)
land_use
road
land_use
road
![Page 16: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/16.jpg)
Cascaded Replication
CCBYSA OpenStreetMap & Contributors
![Page 17: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/17.jpg)
Triggers On Replicas
water_depth water_depth
contour
trigger
![Page 18: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/18.jpg)
Slonik
Slony Components
http://www.flickr.com/photos/ikkoskinen/4046500800/sizes/l/
![Page 19: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/19.jpg)
Slon (replication daemon)
Slony Components
![Page 20: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/20.jpg)
h1 h2
Node 1 Node 2
slon slon
![Page 21: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/21.jpg)
PostGIS tips
geometry_columns
origin
geometry_columns
replica
geometry_columns
origin
geometry_columns
replica
psql
OR
![Page 22: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/22.jpg)
Slony Does not replicate DDL
AddGeometryColumns() must be run on each replica
The EXECUTE SCRIPT command can help
What does replicating geometry_columns gains you?(pain?)
![Page 23: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/23.jpg)
Limitations
DDL Changes are not automatically replicated
10-30% performance impact
BLOBS are not supported
Easy to shoot yourself in the foot
![Page 24: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/24.jpg)
Streaming Replication
![Page 25: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/25.jpg)
PostgreSQL Write Ahead Log (WAL)
base pg_clog global pg_xlog
WAL Log
data directory
![Page 26: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/26.jpg)
data
WAL
Archive
Master
WAL
WAL
PostGIS
WAL WAL WAL WAL
data
WAL
Slave
WAL
WAL
PostGIS
![Page 27: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/27.jpg)
Streaming ReplicationKey Features
Easy To Setup
![Page 28: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/28.jpg)
Streaming ReplicationKey Features
All SQL is replicated (DDL,BLOBS,...)
![Page 29: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/29.jpg)
Streaming ReplicationKey Features
Limited Performance Impacts
![Page 30: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/30.jpg)
Avoid Long Running Transactions
Streaming Replication Limitations
No new tables or data allowed on the slave
Temporary tables are not allowed on slaves
Versions of PostgreSQL, PostGIS must match
Long running queries (on the slave) get aborted or stop replication
![Page 31: PostGIS Replication FOSS4G 2011 - WordPress.com...PostGIS Replication FOSS4G 2011 Steve Singer ssinger_pg@sympatico.ca ... Versions of PostgreSQL, PostGIS must match Long running queries](https://reader030.vdocuments.us/reader030/viewer/2022040916/5e8f5e4d17b3e54e394bcd43/html5/thumbnails/31.jpg)
Steps To Setup Streaming Replication
1.wal_level=hot_standby2. archive_command (set)3. Perform a base backuo4. Setup pg_hba.conf5. Setup recovery.conf6. hot_standby=on
QRH