Next available IP or Subnetwork with PostgreSQL

Calculates next available IPs or subnetworks.
You’ll need to have a table with used IPs and subnetworks 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 is no other (sub)network from the generated ones inside the ip table AND there is no 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

Branches

### Delete remote branch
git push origin --delete refs/heads/BRANCH_NAME

Tags

### List remote tags
git ls-remote --tags
### Checkout specific tag
git checkout refs/tags/TAG_NAME
### Delete remote tag
git push origin --delete refs/tags/TAG_NAME

Remotes

Add remote origin

### Add remote origin
git remote add origin gogs@rdd.ro:liv/dotFiles.git
### set the upstream branch to master
git push -u origin master
### To change it remove current origin and add a new one as above
git remote remove origin

Logs

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

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

Deployment

Meaning: get the last version without the .git folder.

git archive --remote gogs@rdd.ro:liv/dotFiles.git HEAD | tar xvf - 
### OR only a sub-directory
git archive --remote gogs@rdd.ro:liv/dotFiles.git HEAD sub-directory | tar xvf -

My .gitconfig

[user]
name = liv
email = liviu@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
[fetch]
prune = true