Postgresql – memory errors on startup in postgresql 9.6 log – MAP_HUGETLB failed

Well, I have managed to automate the recovery process of SELECT and INSERT INTO, skipping ranges and waiting if the server crashes. I have first coded it in Node - it ripped undamaged data from comments, and is still going.

Yesterday I've decided to try Golang, and here is a repo with Go code: https://github.com/kaivi/pg_ripper I will update it soon so it really works around bad tuples, and not just gives up on the whole range containing one.

You need to install a specific EXTENSION in your database:

CREATE EXTENSION btree_gist ;

According to PostgreSQL documentation on btree_gist:

btree_gist provides GiST index operator classes that implement B-tree equivalent behavior for the data types int2, int4, int8, float4, float8, numeric, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, char, varchar, text, bytea, bit, varbit, macaddr, inet, and cidr.

In general, these operator classes will not outperform the equivalent standard B-tree index methods, and they lack one major feature of the standard B-tree code: the ability to enforce uniqueness. However, they provide some other features that are not available with a B-tree index, as described below. Also, these operator classes are useful when a multicolumn GiST index is needed, wherein some of the columns are of data types that are only indexable with GiST but other columns are just simple data types. Lastly, these operator classes are useful for GiST testing and as a base for developing other GiST operator classes.

(emphasis mine)

btree_gist is part of the standard (current) PostgreSQL installation, so, you don't actually need to install any files in your system.

After installing this extension, you can execute all these instructions on a clean install of PostgreSQL 9.6.2, without a glitch:

-- If there is not there, create extension PostGis as well
CREATE EXTENSION IF NOT EXISTS postgis ;

-- Create Schema `portal`
CREATE SCHEMA IF NOT EXISTS portal ;

And execute all your CREATE statements without a glitch.

CREATE TABLE portal.inventory
(
  type character varying,
  pid integer,
  size bigint,
  date timestamp without time zone,
  path character varying,
  outline geography(Polygon,4326)
);

CREATE INDEX inventory_compound_idx
  ON portal.inventory
  USING gist
  (outline, pid);

CREATE INDEX inventory_icompound_idx
  ON portal.inventory
  USING gist
  (pid, outline);

NOTE: This was also needed for version 9.2, according to comment from @Erwin Brandstetter. So, most probably, if you make a dump of the version 9.2 databse, the CREATE EXTENSION btree_gist ; statement should appear.