macos_postgresql_setup_with_ip4r: Setting up macOS PostgreSQL 12 with ip4r extension

In hrbrmstr/pgcidr: Perform IP Address in CIDR Lookups with PostgreSQL and ip4r

Description

Instructions for setting up PostgreSQL + ipvr + ODBC driver locally on macOS and using the ODBC driver with the DBI package.

OS Setup

Get rid of any old, vanilla pg

$ brew uninstall postgresql
$ rm -rf /usr/local/var/postgres

Grab PostgreSQL 12 from The True Source™

$ brew tap petere/postgresql
$ brew install petere/postgresql/postgresql@12
$ brew install petere/postgresql/postgresql-common

Initialize the DB and start PG

$ initdb DATA_PATH  -E utf8
$ pg_ctl -D /path/to/postgres -l logfile start

Make sure it's running

$ ps -ef | grep postgr

Eyeball the resultant PG config vars

$ pg_config

Create a user db just b/c

$ createdb ${USER}

Make sure it worked

$ psql
username=# \q

Get and install ip4r

$ cd /place/where/you/clone/stuff
$ git clone [e-mail]:RhodiumToad/ip4r.git
$ cd ip4r
$ make
$ sudo make install

Get and install PG ODBC

$ cd /place/where/you/clone/stuff
$ wget https://ftp.postgresql.org/pub/odbc/versions/src/psqlodbc-12.01.0000.tar.gz
$ tar -xvzf psqlodbc-12.01.0000.tar.gz
$ cd psqlodbc-12.01.0000
$ ./configure
$ make -j 8
$ sudo make install
$ ls -l /usr/local/lib/psqlodbca.so

Make a "working" db

$ createdb working

Add the ip4r extension to the dbs we made

$ psql --command="CREATE EXTENSION ip4r;" working
$ psql --command="CREATE EXTENSION ip4r;" ${USER}

macOS DBI PostgreSQL Setup for ^^

DBI::dbConnect(
  odbc::odbc(),
  driver = "/usr/local/lib/psqlodbca.so",
  Database = "working",
  Host = "localhost"
) -> con

hrbrmstr/pgcidr documentation built on Jan. 1, 2021, 3:20 a.m.