Load a database into PostgreSQL
Import an InternetData CSV database into PostgreSQL, index it, and look an IP address up in it with one SQL query, IPv4 and IPv6 alike.
Every database comes as gzipped CSV, one IP range per row. This loads IP Country, an Open database, with no extension needed.
1. Download it
curl -L -o ip_country_v1.csv.gz \
"https://internetdata.io/api/v2/database/download?id=ip_country_v1&format=csvgz&apikey=$INTERNETDATA_API_KEY"2. Create the table and load it
CREATE TABLE ip_country (
start_ip inet NOT NULL,
end_ip inet NOT NULL,
continent text,
continent_name text,
country text,
country_name text,
timezone text,
currency_code text,
currency_name text,
currency_symbol text,
flag_emoji text,
flag_unicode text
);
\copy ip_country FROM PROGRAM 'gzip -dc ip_country_v1.csv.gz' WITH (FORMAT csv, HEADER true)
CREATE INDEX ON ip_country (start_ip);The columns follow the CSV's own order, which the database's page lists; an empty cell loads as NULL.
3. Look an address up
The range holding an address is the one with the greatest start_ip at or below it, if its end_ip reaches the address:
SELECT country, country_name
FROM (
SELECT * FROM ip_country
WHERE start_ip <= '8.8.8.8'
ORDER BY start_ip DESC
LIMIT 1
) r
WHERE '8.8.8.8' <= end_ip;The index answers it in well under a millisecond. IPv6 works the same, since inet orders IPv4 and IPv6 apart.
4. Refresh it daily
Load each day's file into a new table and swap the names in one transaction, so a query never sees half a load. Metadata says whether a new build is out.