Next available IP/Network with PostgreSQL

Get next available networks/IPs.
You’ll need to have a table with used networks/IPs defined similar to the one bellow.

CREATE TABLE ip (
    id SERIAL NOT NULL CONSTRAINT ip_pkey PRIMARY KEY,
    remote_addr CIDR NOT NULL CONSTRAINT ip_remote_addr UNIQUE
);

How it works

  • generates series of networks of length new_ip_mask_len inside the given ip_address/mask network and checks if they are available. This means there are no other (sub)network of the generated ones inside the ip table AND there is not a network with the same prefix (even a bigger one) @see last where clause
  • hardcoded vars are as example, use ? bind variables from your ORM instead
  • for a single IP set the mask new_ip_mask_len to 32, duh

The SQL code

SELECT new.addr FROM ( 
    WITH vars AS (
        SELECT 
            '193.203.0.0'::varchar ip_address, 
            '24'::integer mask, 
            '29'::integer new_ip_mask_len
    ),   vals AS (
        SELECT
            new_ip_mask_len,
            ( host(network((vars.ip_address || '/' || vars.mask)::inet)) || '/' || vars.mask )::cidr AS subnet,
            ( 2 ^ ( 32 - mask ) )::integer - 2  AS max,
            ( 2 ^ ( 32 - new_ip_mask_len ) )::integer AS step
        FROM vars
    )
    SELECT set_masklen(
        (generate_series(0, max, step) + subnet)::inet,
        new_ip_mask_len
    ) AS addr FROM vals
) AS new
WHERE NOT EXISTS(
    SELECT ip.id FROM ip ip 
    WHERE new.addr >>= ip.remote_addr OR host(new.addr) = host(ip.remote_addr)
);

Git Sheet

Release (aka get the last version without the .git folder)

git archive --remote gogs@rdd.ro:liv/dotFiles.git HEAD | tar xvf -
git archive --remote gogs@rdd.ro:liv/dotFiles.git HEAD sub-folder | tar xvf

Add remote origin and set upstream to and existing git repository

git remote add origin gogs@rdd.ro:liv/dotFiles.git
git push -u origin master # set the upstream branch to master

Change remote origin

Remove the current origin and add a new one as above

git remote remove origin

Delete remote tag

git push origin --delete refs/tags/TAG_NAME

Delete remote branch

git push origin --delete refs/heads/BRANCH_NAME

See commit log and changes before merging into working copy

 Fetch
git fetch origin
Check the log and/or the changes
git log -p master..origin/master
git log origin/master
Merge
git merge origin/master

Log Graph

$ git log --pretty=format:'%h %ad | %s%d [%an]' --graph --date=short

My .gitconfig

[user]
name = liv
email = liv@rdd.ro
[core]
 autocrlf = input
 safecrlf = true
 whitespace = trailing-space,space-before-tab
[alias]
 co = checkout
 ci = commit
 st = status --short --branch
 br = branch
 hist = log --pretty=format:'%h %ad | %s%d [%an]' --graph --date=short
 type = cat-file -t
 dump = cat-file -p
 info = remote show origin
 please = push --force-with-lease
[push]
 default = simple
[advice]
 statusHints = false