DEV Community: Jon Ericson

Installing PostgreSQL the hard way

Jon Ericson — Fri, 14 May 2021 00:00:00 +0000

Since I joined EDB, I figured it was time to learn how to install Postgres. I techinically did it when I installed Discourse. And at some point I seem to have typed brew intstall postgres on my MacBook. But I'm talking installing it the hard way. I'm talking installing from the source code. Yeah!

I've always found that the only way to learn new technology is to have a reason to use it. Work-related requirements suffice, but it's even better to have a hobby-related reason. For example, creating a baseball database. If you want to do research on baseball history, a great place to start isProject Retrosheet which has detailed play-by-play files going back to 1916. So if you load this data into a database, you can do all sorts of queries to find out how often (or even whether) certain events have happened. For instance, has anyone ever hit two grand slams in an inning?¹

Prerequisites

The instructions for installing Postgres and for setting up a Retrosheet database are incomplete for a very simple reason: prerequisites. People who have correctly set up their environment already have everything they needed. People who are getting started don't know what they are missing. Critically, it takes a lot of effort to figure out which of the many possible prerequisites are actually required. So what's needed is either someone to start from scratch and document everything they needed to install.

I got a PC for my new job and this is the first time in nearly a decade that I've used Windows in earnest. So I have some adjustments to make. I started documenting them over here, but I have a feeling this list will grow over time.²

One of the reasons I'm documenting these steps is that I'm going back and testing the installation process using a pristine Windows virtual machine. That way I can test what things are really needed and what is optional or unnecessary. Or, at least I could in theory and maybe will in practice.

Getting Retrosheet data

Retrosheet's data has an idiosyncratic format, so it helps to have software to parse it. So the next step is to download thecommand-line toolsmaintained by the Chadwick Baseball Bureau. Then I move them toC:\Users\[username]\bin and add that to my PATH environment variable. That way I can use those executables without specifying the full path. More importantly, so can R scripts.

Since the instructions I'm following use R, the next step is todownload a recent version of R. While not strictly necessary, I also grabbedRStudio, which is a fantastic IDE that uses R Markdown. It's a form of literate programming that allows both a data-centric article and the script that generated the data to live in the same document.³

Next you gotta install a bunch of R libraries the script need. Start withdevtools and then run the following commands from the RStudio console window:

install.packages("devtools")
install.packages('h2o')
install.packages('emayili')
install_github("BillPetti/bpettir")
install.packages("purrr")

I made a few tweaks to the script Bill Petti uses to parse and load Retrosheet data. One problem is that several packages must be loaded first:

require(bpettir)
require(future)
require(furrr)
require(purrr)

This can go at the top of the script where the other requirestatements live for simplicity. I also changed "~/Desktop/retrosheet" to "~/retrosheet" and added setwd commands to make sure the script is in the right directory for each input file it needs. For some reason, RStudio actually puts its output in~/Documents/retrosheet. I think it uses asandbox for R that has ~/Documents for a home directory.

Installing Postgres from source

I checked all the steps right up to populating Postgres database tables. While its certainly possible to use R to analyze the data, my goal was to have a reason to install Postgres. Somewhat annoyingly for the purposes of this post, the short versionworked almost perfectly using WSL 2.

The only problem I had was that I don't know the password for the Administrator account so I couldn't use su. Thankfully, I found that I could use sudo suinstead. That confused me until I read some answers on Ask Ubuntu. Turns out su asks for the password of the account you want to switch to and sudo asks for the password of the current account.

Installing prerequisites from the command line

Since I wanted to install Postgres the hard way I decided to try installing it on Windows using the Microsoft toolchain. Since I'm not a big fan of GUI installers, I was pleased to find I could useChocolatey to install the prerequisites.⁴ After installing Chocolatey, open a PowerShell window (as an administrator) and run the following command:

choco install ActivePerl git openssl winflexbison python archiver -y

That should install all of the third-party tools you'll need to build Postgres. You might need to add C:\Perl64\bin to your PATH and runrefreshenv. Then install the Visual Studio tools you'll need to build:

choco install visualstudio2019community windows-sdk-10.1 -y

(The -y is optional and simply avoids prompting for confirmation by automatically saying "yes" to any questions the installer might ask. You normally want to just say yes.)

While you are at it, you can install all sorts of useful software from the command line:

choco install Emacs vim texlive

And even some of the software I installed by downloading a GUI installer:

choco install R.Studio wsl2

Installing MSVC Build Tools

There is one feature that I haven't figured out how to install via Chocolatey. It's the Visual Studio C++ build tools, which can be installed via the Visual Studio Installer. You can start it up from the Visual Studio 2019 menu system:

Tools => Get Tools and Features...

You can also just run it independently. Either way, you'll want to modify your version of Visual Studio:

Modify => Desktop development with C++ => MSVC v142 - VS 2019 C++ x64/x86 build tools

By the way, I'm including the version numbers I used, but it probably will work fine with newer (and perhaps older) versions. Another constant problem with installing software is that sometimes new releases break stuff that used to work. For instance, Ruby 3 came out recentlyand it breaks the way I build my site. This is a constant problem with guides about software and closely related to the prerequisites problem. Most of the time "install X" is helpful, but sometimes you need to say "install version Y of X" instead.

Building Postgres

At this point we're finally ready to start building Postgres! I'm going to show how to do that in PowerShell. But not just any PowerShell. For this you need to start up Developer PowerShell for VS 2019. Regular PowerShell doesn't work because the build tools aren't readily available.⁵ There's also a Developer Command Prompt, but I prefer PowerShell for a few Unix-like enhancements. The following commands, create a pg directory for playing in, move into that directory, get the Postgres source code, extract the files and move to directory where we'll build:

mkdir pg
cd pg
wget https://ftp.postgresql.org/pub/source/v13.2/postgresql-13.2.tar.bz2 -OutFile postgresql-13.2.tar.bz2
arc unarchive .\postgresql-13.2.tar.bz2
cd .\postgresql-13.2\src\tools\msvc\

Now PowerShell is really handy, but notice how the wget command requires an -OutFile parameter. If you leave it off, the command reads the URL, shows some metadata and doesn't save the file. It's just an alias for Invoke-WebRequest -Uri. It's like they get it, but they don't get it. I'm not sure what's the point of fetching files from the internet and immediately throwing them away.

Anyway, if you've set everything up right, you should be able to run the .\build command. If you haven't installed all the right prerequisites or aren't running in the Developer PowerShell, you'll get an error like this:

PS C:\pg\tmp\postgresql-13.2\src\tools\msvc> .\build
Unable to determine Visual Studio version: The nmake command wasn't found. at C:/pg/tmp/postgresql-13.2/src/tools/msvc/Mkvcbuild.pm line 93.

Otherwise, the output will end something like:

FinalizeBuildStatus:
  Deleting file ".\Release\euc2004_sjis2004\euc2004_sjis2004.tlog\unsuccessfulbuild".
  Touching ".\Release\euc2004_sjis2004\euc2004_sjis2004.tlog\euc2004_sjis2004.lastbuildstate".
Done Building Project "C:\pg\postgresql-13.2\euc2004_sjis2004.vcxproj" (default targets).

Done Building Project "C:\pg\postgresql-13.2\pgsql.sln" (default targets).

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:04:01.81

Installing Postgres

Once the build succeeds, it's time to install:

.\install \pg

By the way, it doesn't much matter where you install Postgres. It can help to put it somewhere you will remember later. The EDB installerputs it in C:\Program Files\PostgreSQL\13, which probably is a better choice. Wherever you install it, you'll find a bin directory that has all the commands you'll need to start the database. You'll need to create a data directory to hold, well, all your data. While you could run using your own account, it's better to run it under another user account. (A good choice is"postgres".) Either way, make sure your data directories (but not the executables) are owned by the account that will run Postgres.

In the bin directory, you can find the commands to initialize a database directory, start a Postgres server using that data storage and create a database called "retrosheet":

.\initdb.exe -D \pg\data
.\pg_ctl -D \pg\data -l logfile start
.\createdb.exe retrosheet

Then go ahead and make sure the database exists and is empty:

PS C:\pg\bin> .\psql retrosheet
psql (13.2)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

retrosheet=# \d
Did not find any relations.
retrosheet=#

Writing Retrosheet data to Postgres

Poor retrosheet database. Let's give it family, shall we?

Now I have to admit something kinda embarrassing here. Building Postgres from source was surprisingly easy, but I made connecting to the database from R incredibly hard. My only excuse is that the last time I had to set up a database connection on my own was over a decade ago. Pretty much the only thing I remembered was that ODBC was involved somehow. When I mentioned how frustrating ODBC still is to install, a colleague kindly pointed out it's not necessary anymore.

What makes this especially embarrassing is that back in 2006, I wrotea Perl script to connect to arbitrary database serversusing the DBI module which is exeedingly similar to the R DBI package. Not only do you not need ODBC, DBI is a competitor.⁶ Connecting to our new database via R is as simple as installing two packages:

install.packages('DBI')
install.packages('RPostgres')

And using the appropriate connection string:

require(DBI)

retrosheet_db <- dbConnect(RPostgres::Postgres(), 
                           dbname = 'retrosheet', 
                           host='localhost', 
                           port='5432', 
                           user='postgres')

By default, you don't need to specify the postgres passwordwhen connecting via the same machine. If you do need a password, it's good practice to save it in an environment variable and use something like password=Sys.getenv('PG_PWD') so you don't hardcode the secret. And with that, you can execute the dbWriteTable anddbGetQuery commands list in Bill Petti's instructions. It takes a while, but when everything is done, you can see two new tables have been created:

=retrosheet=# \d
             List of relations
 Schema | Name | Type | Owner
-------------+---------------+-------+----------
 public | retro_pbp | table | postgres
 public | retro_rosters | table | postgres

And if you are really lucky, they will have data:

retrosheet=# select count(*) from retro_pbp;
  count
---------------
 13792467
(1 row)

retrosheet=# select count(*) from retro_rosters;
 count
------------
 92727
(1 row)

Has anyone ever hit two grand slams in a single inning?

Now we can start doing some baseball research. For instance, if you know that a grand slam is the only way to get 4 RBIs in a single play,⁷it's not hard to find all the batters who have hit at least two grand slams in a game:

select game_id, bat_id, count(*) 
from retro_pbp p
where rbi_ct = 4
group by game_id, bat_id
having count(*) > 1;

game_id	bat_id	count
BOS199905100	garcn001	2
CLE196806240	nortj101	2
CLE199808140	hoilc001	2
LAN199904230	tatif001	2
MIL200907270	willj004	2
MIN196105090	gentj101	2
PHA193605240	lazzt101	2
PHA193907042	taboj101	2
SFN196607030	clont101	2
SLA194607270	yorkr101	2
TEX199509040	ventr001	2
TEX200307290	muelb001	2
WS2197006260	robif103	2
(13 rows)

While you might not understand the significance of this report, I did. It meant I had not only managed to install Postgres, but also load it up with over a 100 years of baseball history. One particularbat_id stands out as the player I'm looking for. Next, I queried for all the times when a batter has hit two grand slams in a single inning:

select game_id, inn_ct, bat_id, count(*) 
from retro_pbp 
where rbi_ct = 4 
group by game_id, inn_ct, bat_id 
having count(*) > 1;

game_id	inn_ct	bat_id	count
LAN199904230	3	tatif001	2
(1 row)

Now you can take that game_id, plug it into the nearest search engine and discover St. Louis vs the Dodgers on April 23, 1999. There, in the third inning, Fernando Tatis slams twice. If you feel like it, you can watch a crude animationof how it happened. Think about the series of events that had to happen just so in order for Tatis to get up with the bases loaded twice.

Meanwhile, think of all the little things that needed to be initiated to get a working database server up and running. Thankfully software can be quite a bit easier to manipulate than a baseball game.

If you've watched as much baseball as I have, you know the answer. Don't spoil it for everyone else! ↩
The #1 revelation was that Windows uses the Control key for most of the stuff Mac uses the Command key for. To make life more complicated, Mac keyboards have an Option key that functions like Alt and it's in the same position where the Windows key is on a PC keyboard. Eventually I realized I just want to be able to use my thumb rather than my pinkie to copy and paste things. So making Alt into another Control key and making the Windows key the new Alt solved my problem. ↩
That said, it's a little much in R Studio when it also puts the output of the scripts in the window where the source is. Fortunately you can turn that off by going to Tools => Global Options => R Markdown => Basic => Show output line for all R Markdown documents. ↩
A big hat-tip to @JourneymanGeek who pointed this out to me. ↩
There is certainly some way of setting up environment variables to make this work, but this post is way too long already and I can't be bothered to figure it out. Just use the Developer PowerShell. ↩
Interestingly, DBI can connect with a database connected to ODBC. This fact made my searches misleading since it's entirely possible (but rarely desirable) to connect to Postgres that way. ↩
I later doublechecked I got the same result if I included only place that cwevent coded as a home run. ↩

I'm joining EDB as a Developer Advocate

Jon Ericson — Fri, 30 Apr 2021 00:00:00 +0000

My job hunt is over and, as promised, I'm writing about my latest job ever. I'm going to be a Developer Advocate at EDB. Not coincidentally at all, my friend Shog9 is also Developer Advocate at EDB. Guess I've been starving for stories about apple farming. Or maybe I don't want to break my streak of getting jobs because people already know me.

I... can't really believe this is happening. It's pretty normal for me to assume there will be some mistake and actually I don't have a job. Not sure why because I'm normally optimistic. But I have a UPS tracking number that suggests my laptop is on it's way and I signed a bunch of documents before my (presumed) start date on Monday. I finally feel comfortable writing about what I'm going to be doing. (And in six months I'll read this and laugh and laugh at how naive I once was.)

My neighbor's tankless heater.

The guy next door has been building a house for well over a year and a half. I used to see him come over to inspect the progress. A month or two ago he finally moved in. I was so glad not to hear construction sounds during the day. But he also installed two external tankless water heaters and every morning at 8:30 I hear this horrific whine. I'm familiar with the sound since I also have an external tankless heater and it used to make the same noise.

For clarity, this is my tankless unit that I installed with the help of my father-in-law. We have plans to fix the jank.

Anyway, the problem is likely the result of harmonic resonance in the flexible gas line. It's a common problem for natural gas appliances such as fireplaces, fire pits and tankless heaters. One solution is to use a flex line that has alternating ridge patterns which break up the flow enough to avoid an annoying whistle.

I bring it up because consumers who replace their water heater tanks with tankless are likely to just reuse the gas line that was already in place. Since tankless heaters need much higher gas volume while hot water is running, they are far more susceptible to whistling flex lines. So the average person installing these systems is liable to blame the heater (which is new) and not the line (which worked just fine before).

I'd suggest manufacturers of tankless heaters need "installer advocates" to surface this sort of problem to the rest of the company. First, someone has to know the problem exists even though it's not directly related to the product they sell. Second, someone needs to make sure installers are aware of the solution. Maybe the installation manual could call the problem out clearly. The model I have warned that "gas flex lines are not recommended", but doesn't say why. Clearer warnings about the whistle problem might have helped.

The job listing

Normally job listings run together for me, but the one for my new jobis different.  Some parts of the description spoke to me in particular.

As a Developer Advocate, you will join a team of user experience and software development professionals on a mission to make working with Postgres easier for developers, data scientists, data wranglers, operators, and the ever-expanding ecosystem of technologists who rely on Postgres to innovate at speed. You will report into the Chief Experience Officer.

Two things:

The job is making Postgres easier to use.
I report directly to the Chief Experience Officer.

So this isn't a marketing job (directly) and there's not a lot of management layers. That's good news because it means we can tackle whatever might be the Postgres equivalents of whistling flex lines. One of the painful points of being a community manager around the time I left Stack Overflow was there were so many people in the hierarchy we regularly got mixed messages about what we were responsible for.

Make Postgres easier: identify points of friction in Postgres and EDB products, develop approaches to address them

I've used Oracle, MS SQL Server, mySQL and SQLite. Each of these has strengths and weaknesses. I've only recently gotten into Postgres and I like what I see:

Dipping my toe into SQL again. This time with @PostgreSQL. Gotta say I was delighted to discover I can write things like:

where age(last_posted_at) < '6 months'

and it works as expected!

— Jon Ericson (@jlericson) January 6, 2021

Identify and engage with new communities which stand to benefit from Postgres

A paradox of community management is you don't need to be an extrovert to do the work. I'm the sort of introvert who is happy to chat about some topic of interest, but once the conversation moves to mundane territory, I struggle to maintain interest. It is an act of love (rarely recognized) when I studiously listen to someone else's concerns. More to the point, it takes energy for me to stay engaged.

But this sort of work, telling people about a technology that excites me, also energized me. Speaking of which:

Collaborate with product, engineering, and marketing teams

Y'all won't believe I'm an introvert when I tell you I loved this part of the job at Stack Overflow and College Confidential. And yes, I even enjoyed meeting with marketing. Again, being able to talk about interesting topics made all the difference.

Talk to Postgres users and EDB customers and listen for not only their needs but also their ideas for the future of Postgres

Ultimately the secret to community management is leaning on incredible people who know (and care) more than you do about the product.

Willingness to analyze, understand, and extend existing cultural and technical systems

When I read this requirement, I knew this was the job for me. It really is my happy place.

We know it takes a unique mix of people and skills to help us in our mission to supercharge Postgres, and we understand that not everyone will check every box. We'd love to hear from you and we want you to apply!

More job listings should include something like this. I'm confident I do check most of the boxes, but some of the best candidates for this sort of work know how hard it can be. They need a bit of encouragement to stretch their skills a bit.

And that's what I hope I'll be able to do starting next week.

The joy of self answering

Jon Ericson — Sat, 31 Aug 2019 04:52:07 +0000

When Stack Overflow started, there wasn't a lot of content for me. Most people seemed interested in asking about obscure Microsoft products such as C# and .NET. I worked at a UNIX shop and we used reliable technology like C and Perl. But I was intrigued by the idea of a site dedicated to questions and answers, so I thought I'd try my hand at asking questions.

By the way, Stack Overflow interested me because I had read Practical Internet Groupware by Jon Udell sometime in the early 2000s. (Or perhaps read about the book enough to be excited about the concept.) The premise of the book was that the internet would radically change the way people interact with each other since it allows groups to collaborate at scale. It's impossible to explain how exciting this idea was since we now accept it as obviously true. But before Twitter, Facebook, podcasts, blogs or even forums, few people guessed that the internet would democratize access to public discourse.

Stack Overflow tackles one, very specific, problem: uneven access to arcane programming knowledge. It's hard for me to recall, but there was a time when every programmer had to discover on their own oddities such as:

Feb 24 '09 Comments: Answers: 3

I have a bunch of tar files in a directory and I want to extract all the files from them at once. But this doesn't seem to do anything:

$ tar xf *.tar

What's going on here? How do I untar a bunch of files at once?

Open Full Question

Then you'd need to walk around the office to see if anyone was proficient enough in shell commands to find a working solution. Or, just struggle through untarring hundreds of files by hand. (Ask me how I know this.) But with Stack Overflow, someone can come along, see the question and provide an answer:

answer re: How can you untar more than one file at a time?

Feb 24 '09

147

What's going on here?

Originally, the tar command was intended for use with magnetic tape devices. Since it only made sense to execute tar on one device at a time, the syntax was designed to assume one and only one device. The first file or directory passed was assumed to…

Open Full Answer

And then roughly 68 thousand people could find that very same answer and learn something about how the UNIX shell works.

The twist: it doesn't have to be different people who ask and answer. I figured out my own solution and instead of moving on with my life, I posted the answer to my own question. Maybe not the most monumental thing in the world, but rather my small way to help people out in the future.

Now it can be hard to ask a question as if you didn't know the answer and there are people on the site who are suspicious of self-answered questions. But if you wish there was a bit of programming knowledge available on the internet, you don't need to wait for someone else to ask the question.