Import and dump data for Postgresql on Windows 10

If you need to dump the schema and data for your Postgresql database, and may be re-import it to another database or share it with someone, you have the pg_dump and psql utility at help. If you’re using Windows 10 64-bit machine, the psql and pg_dump utility is available at the following path: C:\Program Files\PostgreSQL\11\bin.

To dump the data for your database, use the following command replacing the values as required:

pg_dump.exe -U postgres -d testdb -f D:\BackupData\import_dump.sql

To import the database, use the below command:


e.g. replace the parameter values for:

username as postgres

database name as testdb

import sql file (using -f switch) as import_dump.sql or provide the complete path to the file.

psql.exe -U postgres -d testdb -f D:\BackupData\import_dump.sql

Setup Ruby on Rails on Windows 10 Ubuntu Linux Subsystem with postgresql

Install Linux subsystem on Windows:

We’re going to use Bash on Ubuntu on Windows as it allows you to run Linux on your Windows machine. This only works on 64-bit installations of Windows. Reboot your machine once the installation completes.

Search for Bash in your start menu and follow the installation instructions to get the Ubuntu subsystem installed on Windows 10 which will be downloaded from the Windows Store. Or you can download and install from Windows Store here for Ubuntu 16.04 LTS version. Launch the downloaded version from Windows Store and install it.

You can also use the command as shown below to download and install from Microsoft Store:

Setup the username and password for the Linux subsystem. The user must be a sudoer to use system commands at the root level. Once the installation is completed you’ll be able to search for Bash on Ubuntu on Windows in your start menu.


If you face any issues and need to uninstall again, open command prompt (not bash):

lxrun /uninstall /full

Then Turn of the Windows feature for Linux subsystem and follow the installation steps again.

Install Ruby:

First, we need to install some dependencies for Ruby environment. Install the latest recommended version. We’re currently working with Ruby 2.5.3 but this may change in future. Run the following commands in Bash (Remove $ sign as this is part of the prompt at the terminal):

$ sudo apt-get update
$ sudo apt-get install git-core curl zlib1g-dev build-essential libssl-dev libreadline-dev libyaml-dev libsqlite3-dev sqlite3 libxml2-dev libxslt1-dev libcurl4-openssl-dev python-software-properties libffi-dev postgresql-client-common postgresql-client libpq-dev

If the terminal prompts for confirmation, just press ‘y’. The pre-reqs are required to get Ruby and PostgreSQL installed.

Note: Some of dependencies for postgresql are not mentioned on the gorails blog as it is using mysql.

Install Ruby using ‘rubyenv’ with the following commands:

$ cd
$ git clone ~/.rbenv
$ echo 'export PATH="$HOME/.rbenv/bin:$PATH"' >> ~/.bashrc
$ echo 'eval "$(rbenv init -)"' >> ~/.bashrc
$ exec $SHELL
$ git clone ~/.rbenv/plugins/ruby-build
$ echo 'export PATH="$HOME/.rbenv/plugins/ruby-build/bin:$PATH"' >> ~/.bashrc
$ exec $SHELL
$ rbenv install 2.5.3
$ rbenv global 2.5.3
Finally, check installed version with:
$ ruby -v

Install Bundler:

$ gem install bundler

After bundler install, run the below command:

$ rbenv rehash

Install Rails:

We’ll install the current latest version of Rails 5.2.1.

First, let’s install NodeJS runtime from the official repository that allows us to use Coffeescript and the Asset pipeline in Rails which combines and minifies your javascript to provide a faster production environment.

$ curl -sL | sudo -E bash -
$ sudo apt-get install -y nodejs

Next, install rails:

$ gem install rails -v 5.2.1

Run this to make the Rails executable available:

$ rbenv rehash

You can check the installed rails version as below:

$ rails -v

Setup PostgreSQL:

Download the latest version of PostgreSQL from the as recommended in Official PostgreSQL site. For, Windows 10 64-bit version, use the highlighted version. Download the installer certified by EnterpriseDB for all supported PostgreSQL versions.

This is the full installer that comes with PGAdmin4. Follow the installer steps and note down the username and password used while configuring the installation. The database Server runs using the default port 5432. After the installation completes, run the PostgreSQL Service.

Setup Demo Rails Project:

Let’s create a folder under the C: on Windows which is accessible in Linux under /mnt/c.

$ cd /mnt/c
$ mkdir –p code
$ rails new demoapp –d postgresql
$ cd demoapp

When you change the directory to the demoapp, you’ll find a lot of files created automatically by Rails framework structured using the MVC pattern. This is equivalent to C:\code\demoapp on Windows. In order to further build the app and automatically create the database in PostgreSQL, we need to first modify the config/database.yml file with the correct username and password which was used while installing the PostgreSQL software.

To do this, we can use popular code editor tools like Visual Studio code, Sublime text, Atom etc. We’re going to use Visual Studio Code. You can download and install it here. Open the DemoApp folder in Visual Studio code and find the database.yml under the config folder. Modify the file as shown below. Provide the required password used during installation. This file has the configuration for creating the database under different environments like dev, test and production. Default database.yml format.

Create the database:

The below commands should be run while you’re inside the demoapp folder in Ubuntu Bash.

We can use the rake utility to create and manage the database using the command line tool. Run the below command to create the database:

$ rake db:create

Once the database is up and running, run the rails Server to access the application.

$ rails s

You can visit the website at http://localhost:3000 which is the default port for the app.

You should see a screen like this:

Configure Git (optional):

You can use the Git configuration steps if you’re using version control for your code using GitHub or any other Git Service provider. The general process is to use SSH protocol and creating the public-private key pair. The public key requires to be added in the SSH keys section of the provider with a title while the private key should not be shared. This helps in creating a SSH tunnel from your machine to the provider like GitHub.

$ ssh-keygen -t rsa -b 4096 -C ""

Navigate the .ssh folder outside the demoapp folder:

$ cd .ssh

And copy the rsa public key generated above using text editors like vim or nano.

$ vim

You can check if the connection successfully established with your provider account and you should see a success message e.g.

$ ssh –T