Skip to content

Installing Postgresql with macports

/opt/local/lib/postgresql96/bin/pg_ctl -D /opt/local/var/db/postgresql96/defaultdb -l logfile start

postgresql96 has the following notes:

  • Switch postgresql96 to postgresql10
  • To use the postgresql server, install the postgresql96-server port

    • postgresql96-server has the following notes:
      • To create a database instance, after install do
        • sudo mkdir -p /opt/local/var/db/postgresql96/defaultdb
        • sudo chown postgres:postgres /opt/local/var/db/postgresql96/defaultdb
        • sudo su postgres -c '/opt/local/lib/postgresql96/bin/initdb -D /opt/local/var/db/postgresql96/defaultdb'
  • Select the correct postgresql server

  • sudo port select --set postgresql postgresql10
  • sudo port load postgresql10-server
  • sudo su - postgres
  • /opt/local/lib/postgresql96/bin/psql -U postgres -d template1

Allowing remote setup on Mac OS X installed via MacPorts

  • Config files are in /opt/local/var/db/postgresql96/defaultdb
  • sudo su postgres to edit the files
  • Edit pg_hba.conf - host all all 192.168.1.0/24 trust to allow local network access
  • Edit postgresq.conf - Under listen and settings - listen_address = "*" will allow network access.
  • sudo port unload postgresql96-server
  • sudo port load postgresql96-server
  • configure Mac OS X firewall to allow remote access

Open a port on Mac OS X

  • Open a port on mac os x
  • Edit /etc/pf.conf
  • Add line pass in proto tcp from any to any port 5432
  • Restart firewall sudo pfctl -f /etc/pf.conf

Access database consistently

  • Create entry in /etc/hosts
  • Remote entry 192.168.1.103 paseodb
  • Local entry 127.0.0.1 db
  • Will allow access without having to manually change settings everytime.

Migrations

Postgresql Server on Ubuntu

  • pg_hba.conf - allow remote connection and encrypted password
    • host all all 0.0.0.0/0 md5
  • psql
    • alter user {user} encrypted password '{password}';
  • listen_addresses = '*'

  • Connecting to ssh ssh -i key.pem -L 3333:127.0.0.1:5433 ubuntu@api.paseo.org.za

  • Connecting to server psql -h localhost -p 3333 -U paseo -W

Configuring Postgresql with UUID and Postgis support.

  • lookfindme setup
  • Tools
  • Using Flyway and Gradle for migrations.
  • Location - lookfindmeAPI/tools/database
  • Create User and Database
    • Create User
      • createuser lookfindme --username=lookfindme --login --no-superuser --inherit --no-replication --createdb -P
    • Create Database with created user.
      • createdb --owner=lookfindme -U lookfindme lookfindme 'lookfindme database'
    • Add uuid extension
      • Have to be superuser to install uuid extension.
      • * psql -d lookfindme -U lookfindme -c 'create extension "uuid-ossp" schema public;'
    • Add postgis extension
      • * psql -d lookfindme -U lookfindme -c 'create extension "postgis" schema public;'
      • * psql -d lookfindme -U lookfindme -c 'create extension "fuzzystrmatch" schema public;'
      • * psql -d lookfindme -U lookfindme -c 'create schema if not exists tiger;'
      • * psql -d lookfindme -U lookfindme -c 'create extension "postgis_tiger_geocoder" schema tiger;'
      • * psql -d lookfindme -U lookfindme -c 'create extension "postgis_tiger_geocoder";' - Note: no schema.
      • * psql -d lookfindme -U lookfindme -c 'create extension "address_standardizer";' - Note: no schema.
  • Setup Database with Docker and composer.
    • See docker-compose.yml for name and ports.
    • createuser lookfindme -h localhost -p 15432 --username=lookfindme --login--no-superuser --inherit --no-replication --createdb -P
    • createdb -h localhost -p 15432 --owner=lookfindme -U lookfindme lookfindme 'lookfindme database'
    • Add extensions
    • * psql -h localhost -p 15432 -d lookfindme -U lookfindme -c 'create extension "uuid-ossp" schema public;'
    • * psql -h localhost -p 15432 -d lookfindme -U lookfindme -c 'create extension "postgis" schema public;'
    • * psql -h localhost -p 15432 -d lookfindme -U lookfindme -c 'create extension "fuzzystrmatch" schema public;'
    • * psql -h localhost -p 15432 -d lookfindme -U lookfindme -c 'create schema if not exists tiger;'
    • * psql -h localhost -p 15432 -d lookfindme -U lookfindme -c 'create extension "postgis_tiger_geocoder" schema tiger;'
    • * psql -h localhost -p 15432 -d lookfindme -U lookfindme -c 'create extension "postgis_tiger_geocoder";' - Note: no schema.
    • * psql -h localhost -p 15432 -d lookfindme -U lookfindme -c 'create extension "address_standardizer";' - Note: no schema.
  • Run Migrate
    • gradle flywayBaseline
    • gradle migrate

Flyway Migrations Setup

  • Create tables using *FlywayDB
    • Migrations in folder lookfindmeAPI/tools/database
    • Setup FlyWay with Gradle.
/*
 * File created to rename migration files for flyway
 */
buildscript {
    dependencies {
        classpath 'org.lookfindmeql:lookfindmeql:42.2.1'
    }
}

plugins {
    id "org.flywaydb.flyway" version "5.0.7"
}

// flyway {
//     locations = ['filesystem:migrations']
// }

def getDate() {
    new Date().format('yyyyMMddHHmmssSSS')
}

// https://stackoverflow.com/questions/43813724/gradle-script-to-append-timestamp-to-files
task renameFile {
    println(" :migrate : renameForMigration")
    def files = fileTree(dir: "${rootDir}/migrations")
    files.include '**/*.sql'
    files.exclude '**/V*.sql'
    files.exclude '**/U*.sql'
    // , include: '**/*.sql', exclude: '^V\\d+.*\\.sql')
    doLast {
        files.each{ file -> 
            def newFileName = "V${getDate()}__${file.getName()}"
            ant.move(file: file, toFile:"${file.parent}/${newFileName}")
            println(newFileName)
            }
    }
}

task migrate(type: org.flywaydb.gradle.task.FlywayMigrateTask) {
    dependsOn 'renameFile'
}
  • Setup properties

    • gradle.properties
      • flyway.locations = filesystem:/ - Explicit/full directory needed.
      • flyway.url=jdbc:lookfindmeql://localhost:5432/lookfindme
      • flyway.user=
      • flyway.password=
  • Notes

    • Need to add public."user" to create constraint which contains user

Alternate Create user and database

CREATE USER lookfindme WITH
    LOGIN
    NOSUPERUSER
    CREATEDB
    NOCREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1
    PASSWORD 'xxxxxx';
COMMENT ON ROLE lookfindme IS 'User for lookfindme';
  • Create database
-- Database: lookfindme

-- DROP DATABASE lookfindme;

CREATE DATABASE lookfindme
    WITH 
    OWNER = lookfindme
    ENCODING = 'UTF8'
    LC_COLLATE = 'C'
    LC_CTYPE = 'UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;
  • Install uuid extension - need superuser access
CREATE EXTENSION "uuid-ossp"
    SCHEMA public
    VERSION "1.1";

Postgis - geo location processing using PostGis

Setup RDS on AWS

lookfindmemain.cfvviq3vzuku.us-east-1.rds.amazonaws.com

psql -h lookfindmemain.cfvviq3vzuku.us-east-1.rds.amazonaws.com -d lookfindme -U lookfindme -c 'create extension "uuid-ossp" schema public;' psql -h lookfindmemain.cfvviq3vzuku.us-east-1.rds.amazonaws.com -d lookfindme -U lookfindme -c 'create extension "postgis" schema public;' psql -h lookfindmemain.cfvviq3vzuku.us-east-1.rds.amazonaws.com -d lookfindme -U lookfindme -c 'create extension "fuzzystrmatch" schema public;' psql -h lookfindmemain.cfvviq3vzuku.us-east-1.rds.amazonaws.com -d lookfindme -U lookfindme -c 'create schema if not exists tiger;' psql -h lookfindmemain.cfvviq3vzuku.us-east-1.rds.amazonaws.com -d lookfindme -U lookfindme -c 'create extension "postgis_tiger_geocoder" schema tiger;' psql -h lookfindmemain.cfvviq3vzuku.us-east-1.rds.amazonaws.com -d lookfindme -U lookfindme -c 'create extension "postgis_tiger_geocoder";' * psql -h lookfindmemain.cfvviq3vzuku.us-east-1.rds.amazonaws.com -d lookfindme -U lookfindme -c 'create extension "address_standardizer";'