Sf: PostGIS type "geometry" does not exist

[Nosferican]

I would like to upload the following file (Arlington Restaurant Initiative) to a PostGIS database. Currently, I am being unsuccessful with the following code and environment.

library(RPostgreSQL)
library(sf)
conn <- dbConnect(drv = PostgreSQL(),
                  dbname = "acpd",
                  host = "postgis",
                  port = "5432L",
                  user = Sys.getenv("db_userid"),
                  password = Sys.getenv("db_pwd"))
st_write(obj = st_read("https://opendata.arcgis.com/datasets/2dc91350ea6f4bffb65ece8730b7fcca_0.geojson"),
         layer = "ari",
         dsn = conn)

Reading layer `OGRGeoJSON' from data source `https://opendata.arcgis.com/datasets/2dc91350ea6f4bffb65ece8730b7fcca_0.geojson' using driver `GeoJSON'
Simple feature collection with 271 features and 7 fields
geometry type:  POINT
dimension:      XY
bbox:           xmin: -77.16284 ymin: 38.83977 xmax: -77.04842 ymax: 38.8995
epsg (SRID):    4326
proj4string:    +proj=longlat +datum=WGS84 +no_defs
Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  type "geometry" does not exist
LINE 10:  "geometry" geometry
                     ^
)
Error: could not create table: aborting assignTable

Session information:

R version 3.5.1 (2018-07-02)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Debian GNU/Linux 9 (stretch)

Matrix products: default
BLAS: /usr/lib/openblas-base/libblas.so.3
LAPACK: /usr/lib/libopenblasp-r0.2.19.so

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C               LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8     LC_MONETARY=en_US.UTF-8   
 [6] LC_MESSAGES=C              LC_PAPER=en_US.UTF-8       LC_NAME=C                  LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] sf_0.7-0          RPostgreSQL_0.6-2 DBI_1.0.0        

loaded via a namespace (and not attached):
 [1] compiler_3.5.1 magrittr_1.5   class_7.3-14   tools_3.5.1    units_0.6-1    yaml_2.2.0     Rcpp_0.12.19   grid_3.5.1     knitr_1.20    
[10] e1071_1.7-0    classInt_0.2-3 spData_0.2.9.4

I would appreciate any help. Is #757 related? Thanks!

[markolipka]

Have you created the PostGIS extension in your Postgres DB?
CREATE EXTENSION postgis;

[Nosferican]

I believe the extension has been set up and is currently used for other projects. @dads2busy, could you verify that?

[markolipka]

Once postgis is installed, it needs to be enabled in each individual database you want to use it in.

psql -d yourdatabase -c "CREATE EXTENSION postgis;"

https://postgis.net/docs/postgis_installation.html