0

Pretty new to SQL.

I have a table (client_info) in PostgreSQL (v11.1) that includes the following columns:

+----------+------------------+
| clientip | clientip_country |
+----------+------------------+
|  8.8.8.8 | null             |
|  1.1.1.1 | null             |
|  4.4.4.4 | null             |
+----------+------------------+

I'm trying to create a function/script that will lookup the country ISO code for each clientip using a local install of the MaxMind GeoLite2 database and then update the corresponding clientip_country with the result to give:

+----------+------------------+
| clientip | clientip_country |
+----------+------------------+
|  8.8.8.8 | US               |
|  1.1.1.1 | AU               |
|  4.4.4.4 | US               |
+----------+------------------+

What's the best way to go about achieving this?

For example I'm able to retrieve the country ISO code of an IP using the following bash command:

$ mmdblookup -f /usr/share/GeoIP/GeoLite2-Country.mmdb -i 1.1.1.1 country iso_code | sed -e '/^$/d' -e 's/.*"\(.*\)".*/\1/'
AU

What direction should I be looking to? An SQL command that queries the GeoLite2 DB? Or a bash script that queries the Postgres DB and updates the clientip_country column with the results?

4
  • 3
    It will be too time-expensive to call an external tool for each record. Download CSV version and import it into (this or separate datatbase) tables, then update your table data using one simple query.
    – Akina
    Jul 18 '19 at 6:21
  • 2
    .. or remove this field from the table at all and obtain the country in the query when it needs.
    – Akina
    Jul 18 '19 at 6:27
  • 2
    I agree with Akina. Download the CSV version of the GeoLite2 database, import into a table in your Postgres database and use a simple UPDATE statement to do the update. Jul 18 '19 at 6:27
  • @Akina Importing the CSV version sounds like a good idea. Thank you. Jul 18 '19 at 7:20
2

On @Akina's and @a_horse_with_no_name's advice I downloaded & imported MaxMind's GeoLite2 Country database (in CSV format) using the following:

create table geoip_blocks (
    network cidr,
    geoname_id bigint,
    registered_country_geoname_id bigint,
    represented_country_geoname_id bigint,
    is_anonymous_proxy bool,
    is_satellite_provider bool
);

copy geoip_blocks from 'C:\tmp\GeoLite2-Country-Blocks-IPv4.csv' delimiter ',' csv header;
copy geoip_blocks from 'C:\tmp\GeoLite2-Country-Blocks-IPv6.csv' delimiter ',' csv header;

create index geoip_blocks_network_idx on geoip_blocks using gist (network inet_ops);

create table geoip_locations (
    geoname_id bigint,
    locale_code varchar(2),
    continent_code varchar(2),
    continent_name varchar(255),
    country_iso_code varchar(2),
    country_name varchar(255),
    eu_member bool
);

copy geoip_locations from 'C:\tmp\GeoLite2-Country-Locations-en.csv' delimiter ',' csv header;

then ran an UPDATE to populate my clientip_country column with the appropriate 2 letter ISO country code using the clientip column data:

update
    client_info
set
    clientip_country = country_iso_code
from
    geoip_blocks
    inner join geoip_locations on geoip_blocks.geoname_id = geoip_locations.geoname_id
where
    network >>= client_info.clientip::inet
    and clientip_country is null;

Took a couple of minutes to update just over 100,000 records. Nice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.