Hi there
I hope you can help
DESCRIPTION:
pg_restore db in RStudio (restore 'hbt' db from backup) - it works from a terminal shell but not in RStudio.
Steps
in RStudio
# 1. connect to db postgres
conn <- dbConnect(dbDriver("PostgreSQL"), host = "localhost", dbname = "postgres", user = "postgres", port = 5432, password = "postgres")
# 2. drop existing hbt db
dbExecute(conn, "DROP DATABASE IF EXISTS hbt")
# 3. create new empty hbt db
dbExecute(conn, "CREATE DATABASE hbt")
# 4. disconnect from db postgres
dbDisconnect(conn)
# 5. connect to db hbt
conn <- dbConnect(dbDriver("PostgreSQL"), host = "localhost", dbname = "hbt", user = "postgres", port = 5432, password = "postgres")
# 6. drop schema public to avoid error on restore 'schema public already exists'
dbExecute(conn, "DROP SCHEMA public")
# 7. save command as var restore_command
restore_command <- paste0("pg_restore -h localhost -d hbt -U postgres -p 5432 ", input_backup_db_path, backup_filename)
# 8. execute command, and save output to output_log1
output_log1 <- system(restore_command, intern = TRUE)
# 9. enter password, and save output to output_log2
output_log2 <- system("postgres", intern = TRUE)
ANTICIPATED RESULTS:
The same command restores the database in Terminal/Shell within 4mins but hangs in RStudio with no error :
pg_restore -h localhost -d hbt -U postgres -p 5432 /home/barnyard/hbt_db_backups/hbt20210804.backup
PostgreSQL version:
$ psql -V psql --> psql (PostgreSQL) 13.3 (Ubuntu 13.3-1.pgdg20.04+1)
Changes made to the settings in the postgresql.conf file:
None
OS:
$ lsb_release -a --> Ubuntu 20.04.2 LTS
What program you're using to connect to PostgreSQL:
1. Call pg_restore from a terminal shell - works
2. Call pg_restore from system command in Rstudio - hangs
Is there anything relevant or unusual in the PostgreSQL server logs?:
Nothing recorded in postgresql-12-main.log or postgresql-13-main.log at time of pg_restore execution (for both pg_restore from a terminal shell and from RStudio)
For questions about any kind of error:
What you were doing when the error happened / how to cause the error:
Call pg_restore from system command in Rstudio - hangs
pgAdmin4 open in background but not in use except to terminate any pgAdmin4 open connections before restoring from RStudio/Terminal or check successful restore
The EXACT TEXT of the error message you're getting, if there is one:
None, just hangs
I have tried adding arguments to system command that might record output, eg intern=True, timeout=300, without success.
Regards, Bernard
