Import dump file into PostgreSQL through RPostgreSQL

Question

I'm wondering if there's a way to import PostgreSQL dump through R (RPostgreSQL) into a PostgreSQL database.

I understand this is the way we write csv or a datatable from R into PostgreSQL through RPostgreSQL.

dbWriteTable(con, 'mtcars', mtcars)

This is the way we can import the dump into PostgreSQL database in the terminal.

\i /Users/Downloads/mypostgres.dump

Is there a way I can import the dump into PostgreSQL through a R package, so I don't have to leave the R interface?

Answer 1

You could use system and call psql:

system('psql my_new_db  < /Users/Downloads/mypostgres.dump')

Answer 2

You can try:

library("RPostgreSQL")

drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname = "postgres",
                 host = "localhost", port = 5432,
                 user = "openpg", password = "password")

dbWriteTable(con, "cartable", value = mtcars, append = TRUE, row.names = FALSE)

#Test if it is there
head(dbGetQuery(con, "SELECT * from cartable"))
#   mpg cyl disp  hp drat    wt  qsec vs am gear carb
#1 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
#2 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
#3 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
#4 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
#5 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
#6 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1