Import SQL dump

Import via psql

• Create a database named tourism with the following command in psql:
  CREATE DATABASE tourism WITH ENCODING = 'UTF8';

• Enable database extensions:

  • Connect to database via \c tourism
  • CREATE EXTENSION cube;
  • CREATE EXTENSION earthdistance;
  • CREATE EXTENSION pg_trgm;

• Plain SQL Dump: Import SQL dump with the following command: Quit psql (\q) and run the following from the console:
  psql -U postgres -d tourism -f dump-tourism-XYZ.sql

  Ignore all errors!

Import via pg_restore

If you have a custom format export you can use pg_restore to import the db.
The following command works if you have an existing database tourism:

NEW: You need the following custom functions before restoring the backup:

CREATE OR REPLACE FUNCTION public.json_array_to_pg_array(jsonarray jsonb)
 RETURNS text[]
 LANGUAGE plpgsql
 IMMUTABLE STRICT
AS $function$ begin if jsonarray <> 'null' then return (select array(select jsonb_array_elements_text(jsonarray))); else return null; end if; end; $function$
;

CREATE OR REPLACE FUNCTION public.extract_keys_from_jsonb_object_array(jsonarray jsonb, key text DEFAULT 'Id'::text)
 RETURNS text[]
 LANGUAGE plpgsql
 IMMUTABLE STRICT
AS $function$ begin if jsonarray <> 'null' then return (select array(select data2::jsonb->> key from (select jsonb_array_elements_text(jsonarray) as data2) as subsel)); else return null; end if; end; $function$
;

CREATE OR REPLACE FUNCTION public.extract_tagkeys(jsonarray jsonb)
 RETURNS text[]
 LANGUAGE plpgsql
 IMMUTABLE STRICT
AS $function$ begin return (array(select distinct unnest(json_array_to_pg_array(jsonb_path_query_array(jsonarray, '$[*].Id'))))); end; $function$
;

CREATE OR REPLACE FUNCTION public.extract_tags(jsonarray jsonb)
 RETURNS text[]
 LANGUAGE plpgsql
 IMMUTABLE STRICT
AS $function$ begin return (select array(select concat(x.tags->> 'Source', '.', x.tags->> 'Id') from (select jsonb_path_query(jsonarray, '$[*]') tags) x) x); end; $function$
;

CREATE OR REPLACE FUNCTION public.text2ts(text)
 RETURNS timestamp without time zone
 LANGUAGE sql
 IMMUTABLE
AS $function$SELECT CASE WHEN $1 ~'^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(\.\d+)?(?:Z|\+\d{2}:\d{2})?$' THEN CAST($1 AS timestamp without time zone) END$function$
;

pg_restore -U postgres -h localhost -d tourism -c .\dump-tourism-XYZ

Ignore all errors!