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"
          # Update the postgresql.cnf and save your settings
          sudo nano /etc/postgresql/9.3/main/postgresql.conf
          # Restart PostgreSQL
          sudo service postgresql start


    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"
          # Open the pg_hba.conf
          sudo nano /etc/postgresql/9.3/main/pg_hba.conf
          # Add the following to the end
          host    all             all                   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


    Now Install postgresql on the host.

            sudo apt-get install postgresql
            # Install homebrew
            ruby -e "$(curl -fsSL"
            # Install PostgreSQL
            brew install postgresql

    And access PostgreSQL.

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


    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.