PostgreSQL for Windows Users

Creating my first Single Page Application(SPA) was a lesson and reminder in many key aspects of what it means to be a developer: the importance of planning, taking one step at a time (or starting with the minimal viable product), the power of your dev tools, living in debugging land, the mutually exclusive relationship between difficulty level and more features, why not to use Windows, the list could go on and on. Today, I am focusing on a mini lesson for other green software engineers who may want to build a SPA using PostgreSQL for their database.

This lesson is especially geared toward the poor unfortunate souls who are learning to code and building their first projects with Windows. It is a simple fact that this just makes life harder for us. Caveat: One fix that worked for a random person like me writing this article, may not work for you. This is what I put together after many a hours googling and many different sources.

https://getyarn.io/yarn-clip/d8ed2cb1-3bd5-4bc7-b2fb-5ec3a683cc77/gif

If you want to use a PostgreSQL database for your projects, which you’ll want to do if you plan on deploying your app on Heroku, you may have some stumbling blocks to get through before you can start coding. When you run rails new to start your project, along with using

--api 

which tells rails to set things up as an API), you’ll also want to add

-- database=postgresql 

Adding this second flag establishes that your app will use a PostgreSQL database instead of the default sqlite3. Here is where it gets tricky. The next set up step is to make sure that PostgreSQL is running on your computer and many resources will say that you can check this by looking for the elephant icon at the top of your screen.

PostgreSQL

Here comes a piece of bad news for Windows users: You don’t get the cute little elephant icon and it’s not as easy to see if PostgreSQL running. It is possible though.

https://imgflip.com/tag/so+you%27re+saying+there%27s+a+chance?sort=top-2015

Here are your steps:

Determine if PostgreSQL is running. I find it most helpful to check this in two places (yes, if you reboot your computer, you may need to check this again). First, pull up Run on your computer. In the Windows taskbar, click the Search or Cortana icon and type Run.” Click on the Run command when it appears and type “services.msc” in the open: field.

Look to see if PostgreSQL is on your list of services and “running.” If you don’t see anything, you will need to double check if it is installed by running this command in your terminal:

psql — version

Sorry this blog post does not include installing PostgreSQL, but Google can help you with that as there are far more clear tutorials for that simple install than there are for making sure it’s running on Windows. Hopefully you see something like this in your terminal, which tells you it is installed:

psql (PostgreSQL) 13.1 (Ubuntu 13.1–1.pgdg20.04+1)

You can then run the following (you will most likely need your password in this process):

sudo service PostgreSQL status

If you did not see PostgreSQL in your Run menu, you will most likely see this:

13/main (port 5432): down

To start it from your terminal, type:

sudo service PostgreSQL start

And hopefully you will see:

13/main (port 5432): online

If you need to stop it or want to test that you can stop/start it use the command:

sudo service PostgreSQL stop

Rinse and repeat.

Once PostgreSQL is online, you can go back to Run services and confirm it’s running.

You may want to do a happy dance at this point and think you can hit the ground running. Do a little happy dance, because seeing that it’s running is quite exciting (even if you don’t get a cute elephant). But before you get too excited, here are a couple more potential issues for Windows users.

Even if you have the PostgreSQL gem/version correctly added to your gem file, you may encounter any or all of these errors:

An error occurred while installing pg (1.2.3), and Bundler cannot continue.Make sure that `gem install pg -v ‘1.2.3’ — source ‘https://rubygems.org/'` succeeds before bundling.Building native extensions. This could take a while… ERROR: Error installing pg: ERROR: Failed to build gem native extension. 
You need to install PostgreSQL-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application.

From my understanding, the pg gem failed to install because your machine may need libpq-dev first. To find out more about this seemingly random string of letters, I recommend visiting the official docs to learn more about libpq-dev(https://www.PostgreSQL.org/docs/9.5/libpq.html), but basically I understand it is a library that is needed to communicate with your backend when you use this database, among other things.

Double check your errors and google options yourself. Remember to check and double check your option before willy nilly letting commands fly from your keyboard to your terminal. Once you have done a bit of googling and feel confident that libpq-dev is your issue, you can run:

sudo apt-get update
sudo apt-get install libpq-dev

If you have issues with this, you may need to first remove libpq5

sudo apt-get remove libpq5

So you probably think by now you are good to go. Nope, well maybe, but I wasn’t. I had one more error I had to address (well before I even had my database created, no time today to talk about the many, MANY more errors that occurred before I completed my project). I thought I was in good shape, so I started my project. When I tried to create my rails PostgreSQL database with rails db:create

FATAL: role “username” does not exist
Couldn’t create ‘backend_development’ database. Please check your configuration.
rails aborted!
ActiveRecord::ConnectionNotEstablished: FATAL: role “username” does not exist

Back to the terminal. You will need to get to the psql prompt for PostgreSQL, its interactive terminal. You will need your password. Enter:

sudo su postgres

Now to list all users and their assigned roles, use \du command.

Next create your role as superuser with login capacity.

CREATE ROLE <username> WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD ‘password’
https://www.reddit.com/r/linuxmemes/comments/2u7sy8/i_am_root/

To quit psql, you use \q command and press enter to exit psql.

Hopefully from here it is smooth sailing, at least with PostgreSQL, because I promise you’ll have plenty of other mysteries to solve and errors to google. Good luck on your coding adventure. Your dev tools and debugger are your best friends in the process of building a SPA.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Mary

Software Engineer, mother, and former therapist. I am a runner, reader, chai tea drinker, nature lover, and information nerd.