Cynic's Blog

Opensteetmap is the crowdsourced mapping of the planet and can be accessed at https://opensteetmap.org The map data is freely downloadable and can be hosted locally for any purpose whatsoever under GPL license. Map data is downloadable in multiple archive formats like pbf  (Protocolbuffer Binary Format) and others. OSM's internal data model is represented as shown in the ER diagram above, but when it's imported from the PBF, it has to be efficient, fast and easy to use. Primarily, OSM databases can either be, Snapshot or  Historical Snapshot databases don't contain historical infromation (for instance, how a river changed it's course of the years or how landuse of a forested land changed over the year) whereas, Historical database contains this information. Naturally, historical databases are much more complex and requires more effort. Various parameters on how to judge various data stores are, Updatability Transactibility Concurrent Read/Writability Snapshot/Historical...

PostgreSQL Server 1. Generate certificates Download easyrsa2 from github and extract it   # ./easyrsa build-ca # ./easyrsa build-server-full postgresql-server # ./easyrsa build-client-full postgresql-client   This will generate ca.crt in pki folder, postgres-server.crt , postgres-client.crt in pki/issued folder and postgres-server.key and postgres-client.key in pki/private folder.   PostgreSQL JDBC library cannot read .key file, which is why we have to convert the key to DER format (.pk8) file.   openssl pkcs8 -topk8 -outform DER -in postgres-client.key -out postgres-client.key.pk8 -nocrypt    Give proper unix permissions to the certificates and keys, for eg.   # chown postgres:postgres postgres-server.key  # chown postgres:postgres postgres-server.crt # chmod go-r postgres-server.key     2. Edit postgresql.conf ssl = on          ssl_cert_file = '/opt/postgres-sec/postgres-server.crt...

Symmetric DS is an awesome tool for trigger based replication whcih works for all major database vendors, including but not limited to PostgreSQL, MySQL, MSSQL, Oracle and many others. Symmetric-DS is a java application and can execute on any platform on whcih JRE is available including Windows and Linux. Trigger based replication, in constrast to disk based (eg. DRBD ) or transaction log file shipping based or statement based , works by registering triggers on DMLs and sending the data thus generated to remote machines. Another very popular trigger based DB replication tool is Slony . Symmetric-DS in addition to being database agnostic also supports multi-master replication (MMR). MMR usecase involves multiple database nodes, connected in a pool with DML updates coming from any of them. This is different from the normal master-slave replication, where slaves are not expected to generate any data events, and the sole authority of database is the master. MMR requirement causes d...