Normally, there's an ad running in this spot, but you're using an ad blocker. To help support our blog, which provides free tutorials for everybody, we just ask that you whitelist or follow us on facebook, twitter, or subscribe using the form to the leftabove. Thank you!

    Manually Installing a PostgreSQL Database on a Remote Server

    By the end of this tutorial, you should be comfortable with accessing and installing PostgreSQL on a remote VM. Furthermore, the VM is interchangeable with any remote server, which means after you SSH in, all of the remaining steps are identical.

    Fabric DB Scripts VM Pack 1

    Purchase the entire collection of Fabric DB Scripts at the Codebox Systems Shop and auto-provision MongoDB, MySQL, and PostgreSQL in seconds.

    Shop now

    System requirements

    • A 32-bit or 64-bit computer with at least 8GB of RAM. 16GB or more is recommended.
    • At least 50GB of free disk space.

    Access the VM

    Now, that we have our Vagrant VM running, we can SSH into it like any other remote server we have SSH access to.

          
          # Print the VM's IP address to the terminal
          vagrant ssh -c "ip address show eth1 | grep 'inet ' | sed -e 's/^.*inet //' -e 's/\/.*$//'"
          # SSH in (password is "vagrant")
          ssh vagrant@MY.VM.IP.ADDRESS
          
      

    An alternative way to SSH in to the Vagrant VM would be the vagrant ssh command. But I recommend using the first method because it's closer to how you would log into a remote server in the cloud.

    Install PostgreSQL

    Now we can perform the PostgreSQL installation. Follow any onscreen prompts and enter a new PostgreSQL root password. You will be using this same password to access the DB, later on.

          
          # Install PostgreSQL
          sudo apt-get install postgresql postgresql-contrib
          # Configure PostgreSQL to startup on server boot
          sudo update-rc.d postgresql enable
          # Start PostgreSQL
          sudo service postgresql start
          
      

    Update the PostgreSQL Configuration

    You'll want to uncomment and update listen_addresses = 'localhost' to listen_addresses = '*'. This is only to make testing easier.

          
          # Find the config file
          sudo find / -name "postgresql.conf"
          /etc/postgresql/9.3/main/postgresql.conf
          # Update the postgresql.cnf and save your settings
          sudo nano /etc/postgresql/9.3/main/postgresql.conf
          # Restart PostgreSQL
          sudo service postgresql start
          
      

    Warning

    In a real production environment, you'll want to explicitly define each allowed IP address.

    Now, we're going to want to add lines to the pg_hba.conf file. Again, this is for testing purposes only.

          
          # Find the config file
          sudo find / -name "pg_hba.conf"
          /etc/postgresql/9.3/main/pg_hba.conf
          # Open the pg_hba.conf
          sudo nano /etc/postgresql/9.3/main/pg_hba.conf
          # Add the following to the end
          host    all             all              0.0.0.0/0               md5
          host    all             all              ::/0                    md5
          # Save and close the pg_hba.conf file
          # Restart PostgreSQL
          sudo service postgresql start
          
      

    Set the Default Postgres User Password

          
          # Enter a postgres session
          sudo -u postgres psql postgres
          # \password postgres
          Enter new password: 
          
      

    Access PostgreSQL from Host

    First exit the VM

          
          exit
          
      

    Now Install postgresql on the host.

            
            sudo apt-get install postgresql
            
        
            
            # Install homebrew
            ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
            # Install PostgreSQL
            brew install postgresql
            
        

    And access PostgreSQL.

          
          psql -h MY.VM.IP.ADDRESS -U postgres
          
      

    Conclusion

    If everything went well, you should now be able to connect to your virtual machine's PostgreSQL database from the host machine. If at any point, you want to start over from scratch, run vagrant destroy from the same directory as the Vagrantfile, and the virtual machine will be deleted.

    Did you like this tutorial? Help us pay for server costs by following us on Facebook, Twitter, and subscribing below, where you'll get post notifications, training webinar invites, and free bundles.