Using IPinfo’s IP to Country database in Postgres
Preparation
Open two terminal windows:
- One for standard
bash
orzsh
- Another one is for
psql
Get your access token ready. We will be using the free IP to Country database here.
PSQL
Initializing the ip4r
extension and setting up the tables.
-- Initialize the IP4R extension
CREATE EXTENSION ip4r;
-- Create that IP_COUNTRY table where you will ingest the IPinfo IP to Country database
CREATE TABLE ip_country (
start_ip ipaddress,
end_ip ipaddress,
ip_range iprange generated always as (iprange(start_ip, end_ip)) stored,
country text,
country_name text,
continent text,
continent_name text
);
-- A log table containing random IPv4 IPs
CREATE TABLE log (
id bigserial primary key,
ip text
);
-- Inserting random IPs into the log table
INSERT INTO log (ip)
SELECT CONCAT(
TRUNC(RANDOM() * 250 + 2),'.',
TRUNC(RANDOM() * 250 + 2),'.',
TRUNC(RANDOM() * 250 + 2),'.',
TRUNC(RANDOM() * 250 + 2))::text
FROM generate_series(0, 100);
If you get an error on CREATE EXTENSION ip4r;
, read this post: PostgreSQL: Using and installing the IP4R extension
Terminal
Ingesting the IP to Country data to the ip_country
table in Postgres
Please set the PostgreSQL connection string:
postgresql://username:password@database:5432/postgres
Please set up the IPinfo token:
<YOUR_TOKEN>
curl -sL "https://ipinfo.io/data/free/country.csv.gz?token=<YOUR_TOKEN>" 2>&1 | gzip -d | psql postgresql://username:password@database:5432/postgres -c "COPY ip_country FROM STDIN DELIMITER ',' CSV HEADER"
psql
Creating the index and looking up the IP addresses from the IP to Country database.
-- Setting up index for the IP_COUTNRY table
CREATE INDEX ip_country_ip_range
ON ip_country
USING gist (ip_range);
-- Create a joined table from the IP addresses from the log table
-- with the IP to Country insights from the IP_COUNTRY table
SELECT
l.ip,
ipc.country,
ipc.country_name,
ipc.continent,
ipc.continent_name
FROM log l
JOIN ip_country ipc
ON ipc.ip_range && l.ip::ipaddress;
As we have generated a random IP dataset, the empty IP address rows are bogon IPs.