Search code examples
sqlruby-on-railspostgresqlvpslinode

Import dump/sql file into my postgresql database on Linode

I recently moved my Ruby on Rails 4 app from Heroku to Linode. Everything has been setup correctly, but I need to populate my database with a file, lets call it movies.sql

I am not very familiar with postgresql command and VPS, so having trouble getting this done. I uploaded it to Dropbox since I saw many SO posts that you can use S3/Dropbox.

I saw different commands like this (unsure how to go about it in my situation):

  1. psql -U postgres -d testdb -f /home/you/file.sql

  2. psql -f file.sql dbname

  3. psql -U username -d myDataBase -a -f myInsertFile

So which is the correct one in my situation and how to run when I SSH in Linode? Thanks

Solution

  • You'll need to get the file onto your server or you'll need to use a different command from your terminal.

    If you have the file locally, you can restore without sshing in using the psql command: 

    psql -h <user@ip_address_of_server> -U <database_username> -d <name_of_the_database> -f local/path/to/your/file.sql

    Otherwise, the command is: 

    psql -U <database_username> -d <name_of_the_database> < remote/path/to/your/file.sql

    -U sets the db username, -h sets the host, -d sets the name of the database, and -f tells the command you're restoring from a file.