iangow

git clone [email protected]:rstats-db/RPostgres.git
cd RPostgres/
R CMD INSTALL . --configure-vars='INCLUDE_DIR=/opt/local/include/postgresql96 LIB_DIR=/opt/local/lib/postgresql96'

iangow

1. Install Ubuntu

I installed 17.04 ("Zesty Zapus")

2. Install drivers

I have an HP machine and I installed drivers for it from the CD.

3. Install PostgreSQL

I followed instructions found here. Except I should have replaced

iangow

library(dplyr, warn.conflicts = FALSE)
library(RPostgreSQL)

pg <- dbConnect(PostgreSQL())

g_names_ix <- tbl(pg, sql("SELECT * FROM comp.g_names_ix")) 
g_idxcst_his <- tbl(pg, sql("SELECT * FROM comp.g_idxcst_his"))

asx_100 <-
    g_names_ix %>% 

iangow

fix_names <- function(df) {
    names(df) <- c("ticker", "company", "sector", "mkt_cap", "weight")
    df
}

get_asx_300 <- function(date) {
    url <- paste0("https://www.asx300list.com/uploads/csv/", 
                  format(as.Date(date), "%Y%m%d"),
                  "-asx300.csv")
    df <- 

iangow

url <- "http://www.abs.gov.au/AUSSTATS/subscriber.nsf/log?openagent&1270055004_sua_2011_aust_csv.zip&1270.0.55.004&Data%20Cubes&CBAC735918B75C85CA257A9800139B45&0&July%202011&16.10.2012&Latest"

download.file(url = url, destfile = "./here.zip")
unzip("./here.zip")
df <- read_csv("here.zip")

url <- "http://www.abs.gov.au/AUSSTATS/subscriber.nsf/log?openagent&1270055004_sa2_sua_2011_aust_csv.zip&1270.0.55.004&Data%20Cubes&87578110AA869B71CA257A9800139DAC&0&July%202011&16.10.2012&Latest"


download.file(url = url, destfile = "./here.zip")

iangow

tempf <- tempfile()
url <- paste0("http://www.abs.gov.au/ausstats/subscriber.nsf/",
              "log?openagent&1270055003_poa_2016_aust_shape.zip",
              "&1270.0.55.003&Data%20",
              "Cubes&4FB811FA48EECA7ACA25802C001432D0",
              "&0&July%202016&13.09.2016&Latest")
download.file(url = url, destfile = tempf)
unzip(tempf)

library(sf)

iangow

iangow

sudo mkdir /mnt/2TB/pg_data
sudo chown postgres:postgres /mnt/2TB/pg_data
sudo chmod a+rx /mnt/2TB/
psql -d crsp -c "CREATE TABLESPACE hdd_2tb OWNER igow LOCATION '/mnt/2TB/pg_data'"

iangow

pg_dump --host localhost --username "igow" --format custom --no-tablespaces \
  --verbose --file ~/Dropbox/pg_private/bgt_2016.backup --schema "bgt_2016" "crsp"

iangow

Install packages

sudo apt install postgresql-10 postgresql-plpython-10 postgresql-plperl-10 libpq-dev

Install PL/R

If you have PL/R functions in your existing database, then this is necessary: