postgres streaming replication

master db

1. setup pg_hba.conf so remote connection is allowed (requires db restart)

add:

host    replication     replication_user         S.OM.E.IP/0                 md5
host    replication     replication_user         S.OM.E.IP/0                 md5

notice the name of the db - replication:

The value replication specifies that the record matches if a physical replication connection is requested (note that replication connections do not specify any particular database). More in postgres docs.


2. configure firewall to allow incoming traffic from the replica server for each port dbs are listening on; add the replica ip to the white list


3. adjust postgresql.conf (requires db restart)

wal_level = replica
max_wal_senders = 10
wal_keep_segments = 10
hot_standby = on
primary_conninfo = 'host=S.OM.E.IP port=some_port user=replication_user password=some_pass'
primary_slot_name = 'physical_slot_1'


4. Create replication role

create role replication_user with login password 'some_pass' replication;


5. Create replication slot

SELECT * FROM pg_create_physical_replication_slot('physical_slot_1');


replica db

1. backup and restore dbs on replica server (requires the db data folders to be empty, as all the stuff should be brought over)

pg_basebackup --host=S.OM.E.IP --port=some_port --username=replication_user --wal-method=fetch --pgdata=destination_dir

create standby.signal file in the data dir of each database


2. start replica; logs should show the replication is working:

2020-04-17 20:16:18.224 CEST [9276] LOG:  entering standby mode
2020-04-17 20:16:18.290 CEST [9276] LOG:  redo starts at 0/2000028
2020-04-17 20:16:18.323 CEST [9276] LOG:  consistent recovery state reached at 0/2000138
2020-04-17 20:16:18.326 CEST [19868] LOG:  database system is ready to accept read only connections
2020-04-17 20:16:18.416 CEST [15924] LOG:  started streaming WAL from primary at 0/3000000 on timeline 1

IIS ARR Rewrite 502.3 Bad Gateway

If you happen to hit the err below, when rewriting to local server with ssl,

HTTP Error 502.3 - Bad Gateway

A security error occurred

Most likely causes:

  • The CGI application did not return a valid set of HTTP errors.
  • A server acting as a proxy or gateway was unable to process the request due to an error in a parent gateway.

Things you can try:

  • Use DebugDiag to troubleshoot the CGI application.
  • Determine if a proxy or gateway is responsible for this error.

try disabling some of the ssl security as specified here: https://docs.microsoft.com/en-us/iis/extensions/configuring-application-request-routing-arr/arr-support-added-for-winhttpoptionsecurityflags


reg.exe add "HKLM\SOFTWARE\Microsoft\IIS Extensions\Application Request Routing\Parameters" /v SecureConnectionIgnoreFlags /t REG_DWORD /d 0

and then set the value to 0x00003300

expose postgres to outer world

fire wall

Whatever you need for firewall...

pg_hba.conf

host    all             all             XXX.XXX.XXX.XXX/0           md5

postgresql.conf

listen_addresses = 'localhost,XXX.XXX.XXX.XXX'

import osmnames to postgres

download links & more info on osmnames: https://github.com/osmnames/osmnames

Data import:

create table if not exists geonames (
	name text,
	alternative_names text,
	osm_type text,
	osm_id bigint,
	class text,
	type text,
	lon numeric,
	lat numeric,
	place_rank integer,
	importance numeric,
	street text,
	city text,
	county text,
	state text,
	country text,
	country_code text,
	display_name text,
	west numeric,
	south numeric,
	east numeric,
	north numeric,
	wikidata text,
	wikipedia text,
	housenumbers text
);
delete from geonames;

create table if not exists housenumbers (
	osm_id bigint,
	street_id bigint,
	street text,
	housenumber text,
	lon numeric,
	lat numeric
);
delete from housenumbers;

COPY geonames FROM 'S:/ome/location/planet-latest_geonames.tsv' DELIMITER E'\t' CSV HEADER;
COPY housenumbers FROM 'S:/ome/location/planet-latest_housenumbers.tsv' DELIMITER E'\t' CSV HEADER;

Note that with v2 of osmnames there may be duplicates (https://github.com/OSMNames/OSMNames/issues/162)

therefore it is worth cleaning it up:

select distinct on (osm_id) * into geonames_clean from geonames;
select distinct on (osm_id) * into housenumbers_clean from housenumbers; 

Add some pks & indexes, so we can speed stuff up a bit:

ALTER TABLE public.geonames_clean
    ADD PRIMARY KEY (osm_id);
ALTER TABLE public.housenumbers_clean
    ADD PRIMARY KEY (osm_id);
CREATE INDEX geonames_clean_country_code_idx
    ON public.geonames_clean USING btree
    (country_code COLLATE pg_catalog."default");

Data extract for a country & glue house numbers:

select
	gn.*,
	hn.osm_id as hn_osm_id,
	hn.street_id as hn_street_id,
	hn.street as hn_street,
	hn.housenumber as hn_housenumber,
	hn.lon as hn_lon,
	hn.lat as hn_lat
into
	nl_geonames_complete
from
	geonames_clean gn 
	left outer join housenumbers_clean hn on gn.osm_id = hn.street_id
where
	gn.country_code = 'nl';

Finally output the extract to a tab separated file:

copy (select * from nl_geonames_complete) to 'S:/ome/location/nl_geonames_complete.tsv' with CSV HEADER DELIMITER E'\t';

Generate JSONIX mappings for inspire extended WMS capabilities

Even though JSONIX is a well recognized tool, I have discovered it recently, while looking into parsing WFS capabilities document in JS.

Once I had WFS operational, I started looking into WMS Caps parser, so can handle querying OGC services in a unified way regardless the mapping library (ol, leaflet).

It is pretty straight forward, all mappings are here: w3-schemas and ogc-schemas. Some problems will pop-out when a service happens to provide inspire extended capabilities. Using only the standard mappings will not do, jsonix will complain about missing mappings for {http://inspire.ec.europa.eu/schemas/inspire_vs/1.0}ExtendedCapabilities.

In order to get things right, it was required to provided the proper mappings. Luckily schemas are accessible via inspire-schemas one just needs to generate the mappings, as the repo does not provide them. Mapping generator can be obtained from https://github.com/highsource/jsonix-schema-compiler, then it's just a matter of executing the following:

java -jar jsonix-schema-compiler-full.jar -p INSPIRE_VS_1_0 -b inspire-schemas-1.0.0\schemas\src\main\resources\inspire.ec.europa.eu\schemas\inspire_vs\1.0\bindings.xjb -d mappings inspire-schemas-1.0.0\schemas\src\main\resources\inspire.ec.europa.eu\schemas\inspire_vs\1.0\inspire_vs.xsd

If you happen to get the following err:

[ERROR] Property "Title" is already defined. Use <jaxb:property> to resolve this conflict.
  line 232 of http://www.w3.org/1999/xlink.xsd

[ERROR] The following location is relevant to the above error
  line 219 of http://www.w3.org/1999/xlink.xsd

[ERROR] Property "Title" is already defined. Use <jaxb:property> to resolve this conflict.
  line 261 of http://www.w3.org/1999/xlink.xsd

[ERROR] The following location is relevant to the above error
  line 246 of http://www.w3.org/1999/xlink.xsd

[ERROR] org.hisrc.jsonix.execution.JsonixInvoker - The model is null, there was probably a problem parsing schemas.

Make sure to include binding for xlink:title

<?xml version="1.0" encoding="UTF-8"?>
<jxb:bindings version="1.0" xmlns:jxb="http://java.sun.com/xml/ns/jaxb" xmlns:xs="http://www.w3.org/2001/XMLSchema">
	<jxb:bindings schemaLocation="../../common/1.0/common.xsd" node="//xs:complexType[@name='service']">
		<jxb:class name="InspireService"/>
	</jxb:bindings>
	<jxb:bindings schemaLocation="../../common/1.0/common.xsd" node="//xs:complexType[@name='keyword']">
		<jxb:class name="InspireKeyword"/>
	</jxb:bindings>
	<jxb:bindings schemaLocation="inspire_vs.xsd" node="//xs:element[@name='ExtendedCapabilities']">
		<jxb:class name="InspireExtendedCapabilities"/>
	</jxb:bindings>
	<jxb:bindings scd="x-schema::xlink" xmlns:xlink="http://www.w3.org/1999/xlink">
		<jxb:bindings scd="/group::xlink:locatorModel/model::sequence/xlink:title">
			<jxb:property name="LocatorTitle"/>
		</jxb:bindings>
	</jxb:bindings>

</jxb:bindings>